Re: Re: [sqlite] C callback that returns numeric data without conversion from text?
On Tue, Jul 27, 2004 at 10:56:09PM -0700, Al Danial wrote: > Great, that fits the bill perfectly, thanks! > > I know you mentioned the code is incomplete but the usage instructions > don't match the executable's behavior: the third command line argument > (the SQL command, or argv[2]) isn't used by the code. The interactive mode > works great though, and demonstrates the prepare/step/finalize method > very clearly. > > It would be cool if sqlite came with an examples/ subdirectory that > contained this program (or ones like it). Examples can often explain > things so much more easily than text descriptions of functions. >-- Al > > Yes, I was going to use argv[2] for a binary file name, to be used with a sqlite3_bind_blob example. Mike Chirico
Re: Re: [sqlite] C callback that returns numeric data without conversion from text?
Great, that fits the bill perfectly, thanks! I know you mentioned the code is incomplete but the usage instructions don't match the executable's behavior: the third command line argument (the SQL command, or argv[2]) isn't used by the code. The interactive mode works great though, and demonstrates the prepare/step/finalize method very clearly. It would be cool if sqlite came with an examples/ subdirectory that contained this program (or ones like it). Examples can often explain things so much more easily than text descriptions of functions. -- Al On Tue, 27 Jul 2004 10:14:06 -0400, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > On Mon, Jul 26, 2004 at 08:05:17PM -0700, Al Danial wrote: > > Thanks for the clarification. I'll need to study the docs on the three > > part method and slowly figure it out. A working example sure would > > be nice! -- Al > > > > On Mon, 26 Jul 2004 11:21:08 +0100 (BST), Christian Smith > > <[EMAIL PROTECTED]> wrote: > > > Typed data retrieval is not supported by the callback method of execution. > > > > > > You must use the prepare/step/finalize execution method. > > > > > > Check out: > > > http://www.sqlite.org/capi3.html > > > > > > in particular, the sqlite3_step and related functions in section 2.2 > > > "Executing SQL statements." This section gives the functions to retrieve > > > typed data from a row. > > Are you looking for an example of just the 3 part method? I'm working on something > here, which I think gives the jist of it. Still beta, since blob type isn't > finished: > > http://souptonuts.sourceforge.net/code/apiSQLite3b.c.html > > Or, http://prdownloads.sourceforge.net/cpearls/sqlite_examples.tar.gz?download > but look at example "apiSQLite3b.c". > > I'm not sure this is what you want, but maybe it will help. > > Regards, > > Mike Chirico > [EMAIL PROTECTED] >
Re: Re: [sqlite] C callback that returns numeric data without conversion from text?
On Mon, Jul 26, 2004 at 08:05:17PM -0700, Al Danial wrote: > Thanks for the clarification. I'll need to study the docs on the three > part method and slowly figure it out. A working example sure would > be nice! -- Al > > On Mon, 26 Jul 2004 11:21:08 +0100 (BST), Christian Smith > <[EMAIL PROTECTED]> wrote: > > Typed data retrieval is not supported by the callback method of execution. > > > > You must use the prepare/step/finalize execution method. > > > > Check out: > > http://www.sqlite.org/capi3.html > > > > in particular, the sqlite3_step and related functions in section 2.2 > > "Executing SQL statements." This section gives the functions to retrieve > > typed data from a row. Are you looking for an example of just the 3 part method? I'm working on something here, which I think gives the jist of it. Still beta, since blob type isn't finished: http://souptonuts.sourceforge.net/code/apiSQLite3b.c.html Or, http://prdownloads.sourceforge.net/cpearls/sqlite_examples.tar.gz?download but look at example "apiSQLite3b.c". I'm not sure this is what you want, but maybe it will help. Regards, Mike Chirico [EMAIL PROTECTED]
Re: Re: [sqlite] C callback that returns numeric data without conversion from text?
Thanks for the clarification. I'll need to study the docs on the three
part method and slowly figure it out. A working example sure would
be nice! -- Al
On Mon, 26 Jul 2004 11:21:08 +0100 (BST), Christian Smith
<[EMAIL PROTECTED]> wrote:
> Typed data retrieval is not supported by the callback method of execution.
>
> You must use the prepare/step/finalize execution method.
>
> Check out:
> http://www.sqlite.org/capi3.html
>
> in particular, the sqlite3_step and related functions in section 2.2
> "Executing SQL statements." This section gives the functions to retrieve
> typed data from a row.
>
> Sorry, no sample code as I've yet to dive headlong into v3 yet. My work
> for the moment is v2 only.
>
> Christian
>
>
>
> On Sun, 25 Jul 2004, Al Danial wrote:
>
> >Since SQLite v2 was 'typeless', one had to call atoi() and atof() on terms
> >of the array *azArg to convert the text strings returned by a query into
> >integers and doubles.
> >
> >As I understand it SQLite v3 stores integers and doubles in their native
> >binary format so one should be able to get at the numeric data without
> >text string conversions via atoi()/atof(). Does anyone have C code that
> >demonstrates how this could be done?
> >
> >Here's a sample database and corresponding query + callback I'm currently
> >using:
> >
> > create table people ( name text, age integer , lat float, lon float );
> > insert into people values ( 'Alice' , 43 , 1.1 , -3.4e-3 );
> > insert into people values ( 'Bob' , 28 , 5.5 , -3.1e+3 );
> > insert into people values ( 'Cindy' , 21 , 8.8 , 3.2e+5 );
> >
> >The query:
> >
> > rc = sqlite3_exec_printf(sql_db,
> > "select * from people order by age asc "
> > , a1_i1_d2_cb,
> > _data,
> > );
> >
> >The callback:
> >
> > int a1_i1_d2_cb(void *pArg, int nFields, char **azArg, char **azCol) {
> > /* return array of [string, integer, double, double] */
> > callback_data *p = (callback_data*) pArg;
> >
> > if (!azArg)
> > return 0;
> > strncpy(p->a[p->nRows][0], azArg[0], SQL_STR_SIZE);
> > p->i[p->nRows][0] = atoi(azArg[1]);
> > p->x[p->nRows][0] = atof(azArg[2]);
> > p->x[p->nRows][1] = atof(azArg[3]);
> > ++(p->row_index);
> > ++(p->nRows);
> >
> > return 0;
> > }
> >
> >The callback variable 'sql_data' has type 'callback_data' defined like this:
> >
> > #define SQL_BLOCK_SIZE 1000
> > #define SQL_STR_SIZE200
> > #define SQL_MAX_COLUMNS 20
> >
> > typedef struct {
> >sqlite3 *db;/* database handle */
> >FILE *out;/* output file handle*/
> >charnullvalue[SQL_MAX_COLUMNS]; /* string to display for NULL's */
> >charzDbFilename[SQL_STR_SIZE]; /* db filename */
> >int nRows; /* size of a[]/i[]/x[] with valid data */
> >int row_index; /* pointer to current row within table */
> >chara[SQL_BLOCK_SIZE][SQL_MAX_COLUMNS][SQL_STR_SIZE];/* string */
> >int i[SQL_BLOCK_SIZE][SQL_MAX_COLUMNS]; /* integer */
> >double x[SQL_BLOCK_SIZE][SQL_MAX_COLUMNS]; /* double */
> > } callback_data;
> >
> >My questions:
> > How can I populate sql_data.i[] and sql_data.x[] without calling atoi()
> > and atof() on terms of azArg[]?
> > Is there example C code out there that demonstrates the technique?
> >-- Al
> >
>
> --
> /"\
> \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
> X - AGAINST MS ATTACHMENTS
> / \
>
Re: [sqlite] C callback that returns numeric data without conversion from text?
Typed data retrieval is not supported by the callback method of execution.
You must use the prepare/step/finalize execution method.
Check out:
http://www.sqlite.org/capi3.html
in particular, the sqlite3_step and related functions in section 2.2
"Executing SQL statements." This section gives the functions to retrieve
typed data from a row.
Sorry, no sample code as I've yet to dive headlong into v3 yet. My work
for the moment is v2 only.
Christian
On Sun, 25 Jul 2004, Al Danial wrote:
>Since SQLite v2 was 'typeless', one had to call atoi() and atof() on terms
>of the array *azArg to convert the text strings returned by a query into
>integers and doubles.
>
>As I understand it SQLite v3 stores integers and doubles in their native
>binary format so one should be able to get at the numeric data without
>text string conversions via atoi()/atof(). Does anyone have C code that
>demonstrates how this could be done?
>
>Here's a sample database and corresponding query + callback I'm currently
>using:
>
> create table people ( name text, age integer , lat float, lon float );
> insert into people values ( 'Alice' , 43 , 1.1 , -3.4e-3 );
> insert into people values ( 'Bob' , 28 , 5.5 , -3.1e+3 );
> insert into people values ( 'Cindy' , 21 , 8.8 , 3.2e+5 );
>
>The query:
>
> rc = sqlite3_exec_printf(sql_db,
> "select * from people order by age asc "
> , a1_i1_d2_cb,
> _data,
> );
>
>The callback:
>
> int a1_i1_d2_cb(void *pArg, int nFields, char **azArg, char **azCol) {
> /* return array of [string, integer, double, double] */
> callback_data *p = (callback_data*) pArg;
>
> if (!azArg)
> return 0;
> strncpy(p->a[p->nRows][0], azArg[0], SQL_STR_SIZE);
> p->i[p->nRows][0] = atoi(azArg[1]);
> p->x[p->nRows][0] = atof(azArg[2]);
> p->x[p->nRows][1] = atof(azArg[3]);
> ++(p->row_index);
> ++(p->nRows);
>
> return 0;
> }
>
>The callback variable 'sql_data' has type 'callback_data' defined like this:
>
> #define SQL_BLOCK_SIZE 1000
> #define SQL_STR_SIZE200
> #define SQL_MAX_COLUMNS 20
>
> typedef struct {
>sqlite3 *db;/* database handle */
>FILE *out;/* output file handle*/
>charnullvalue[SQL_MAX_COLUMNS]; /* string to display for NULL's */
>charzDbFilename[SQL_STR_SIZE]; /* db filename */
>int nRows; /* size of a[]/i[]/x[] with valid data */
>int row_index; /* pointer to current row within table */
>chara[SQL_BLOCK_SIZE][SQL_MAX_COLUMNS][SQL_STR_SIZE];/* string */
>int i[SQL_BLOCK_SIZE][SQL_MAX_COLUMNS]; /* integer */
>double x[SQL_BLOCK_SIZE][SQL_MAX_COLUMNS]; /* double */
> } callback_data;
>
>My questions:
> How can I populate sql_data.i[] and sql_data.x[] without calling atoi()
> and atof() on terms of azArg[]?
> Is there example C code out there that demonstrates the technique?
>-- Al
>
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
[sqlite] C callback that returns numeric data without conversion from text?
Since SQLite v2 was 'typeless', one had to call atoi() and atof() on terms
of the array *azArg to convert the text strings returned by a query into
integers and doubles.
As I understand it SQLite v3 stores integers and doubles in their native
binary format so one should be able to get at the numeric data without
text string conversions via atoi()/atof(). Does anyone have C code that
demonstrates how this could be done?
Here's a sample database and corresponding query + callback I'm currently
using:
create table people ( name text, age integer , lat float, lon float );
insert into people values ( 'Alice' , 43 , 1.1 , -3.4e-3 );
insert into people values ( 'Bob' , 28 , 5.5 , -3.1e+3 );
insert into people values ( 'Cindy' , 21 , 8.8 , 3.2e+5 );
The query:
rc = sqlite3_exec_printf(sql_db,
"select * from people order by age asc "
, a1_i1_d2_cb,
_data,
);
The callback:
int a1_i1_d2_cb(void *pArg, int nFields, char **azArg, char **azCol) {
/* return array of [string, integer, double, double] */
callback_data *p = (callback_data*) pArg;
if (!azArg)
return 0;
strncpy(p->a[p->nRows][0], azArg[0], SQL_STR_SIZE);
p->i[p->nRows][0] = atoi(azArg[1]);
p->x[p->nRows][0] = atof(azArg[2]);
p->x[p->nRows][1] = atof(azArg[3]);
++(p->row_index);
++(p->nRows);
return 0;
}
The callback variable 'sql_data' has type 'callback_data' defined like this:
#define SQL_BLOCK_SIZE 1000
#define SQL_STR_SIZE200
#define SQL_MAX_COLUMNS 20
typedef struct {
sqlite3 *db;/* database handle */
FILE *out;/* output file handle*/
charnullvalue[SQL_MAX_COLUMNS]; /* string to display for NULL's */
charzDbFilename[SQL_STR_SIZE]; /* db filename */
int nRows; /* size of a[]/i[]/x[] with valid data */
int row_index; /* pointer to current row within table */
chara[SQL_BLOCK_SIZE][SQL_MAX_COLUMNS][SQL_STR_SIZE];/* string */
int i[SQL_BLOCK_SIZE][SQL_MAX_COLUMNS]; /* integer */
double x[SQL_BLOCK_SIZE][SQL_MAX_COLUMNS]; /* double */
} callback_data;
My questions:
How can I populate sql_data.i[] and sql_data.x[] without calling atoi()
and atof() on terms of azArg[]?
Is there example C code out there that demonstrates the technique?
-- Al
