Re: [sqlite] sqlite3.c amalgamation > 64k lines
On 7/10/07, Joe Wilson <[EMAIL PROTECTED]> wrote: --- Ted Mielczarek <[EMAIL PROTECTED]> wrote: > I was wondering if it's possible to strip comment blocks from source > files when generating sqlite3.c? It seems that since it's a generated > file anyway, the comments shouldn't be terribly important. I ran a > quick test on the sqlite-3.4.0 sources, and I got: > wc -l sqlite3*.c > 67479 sqlite3.c > 49585 sqlite3-trimmed.c +1 vote on this. Had the same debug issue on a MIPS GCC target. My source code line counter, http://cloc.sourceforge.net, has an option to remove comments from code. You could use it until the amagamation is delivered without comments. It is a pretty simple process: /tmp> cloc --strip_comments=nc sqlite3.c 1 text file. 1 unique file. Wrote sqlite3.c.nc 0 files ignored. http://cloc.sourceforge.net v 0.80 T=7.0 s (0.1 files/s, 9639.9 lines/s) --- Language files blank comment codescale 3rd gen. equiv --- C 1 3971 21278 42230 x 0.77 = 32517.10 --- SUM: 1 3971 21278 42230 x 0.77 = 32517.10 --- Check that the resulting code is correct by comparing object files produced from source with and without comments: # original with comments: /tmp> gcc -c sqlite3.c ; md5sum sqlite3.o 7da5779ac3becd4e2b1509117872cc6b sqlite3.o # after cloc has removed comments: /tmp> mv sqlite3.c.nc sqlite3.c ; rm sqlite3.o ; gcc -c sqlite3.c ; md5sum sqlite3.o 7da5779ac3becd4e2b1509117872cc6b sqlite3.o /tmp> wc -l sqlite3.c 42230 sqlite3.c -- Al
Re: [sqlite] Fortran 95 Language Bindings
As you suspect, it is straightforward to make a Fortran to SQLite binding. Here's an example with Fortran 77: http://danial.org/sqlite/fortran/ I don't see why an F95 binding would need to be compiler specific. -- Al On 4/14/07, Gary Scott <[EMAIL PROTECTED]> wrote: Hi, this is my first post, so please excuse any etiquette goofs. I'm researching incorporation of SQLite into a project. It would appear relatively straightforward to develop an F95 language binding, however it is likely to be compiler specific (extensions required to adapt calling syntax to C). Is this a suitable place to ask basic C-oriented questions relative to the existing API? Also, it would be my desire to not only map the existing API exactly (as public), but to also create a small additional layer atop the existing API with a slightly higher abstraction level to hide some of the low levelness of the API (pointers, handle passing, etc.). Would anybody be interested in helping or critiquing as I progress? (I haven't decided to do this yet, I'm awaiting corporate approval to use public domain software in my project, which they have refused in the past. This part of the project would be on my own time so as to not involve company ownership issues). -- Gary Scott mailto:[EMAIL PROTECTED] dot net Fortran Library: http://www.fortranlib.com Support the Original G95 Project: http://www.g95.org -OR- Support the GNU GFortran Project: http://gcc.gnu.org/fortran/index.html If you want to do the impossible, don't hire an expert because he knows it can't be done. -- Henry Ford - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] I Need database for some test
Here are SQL statements that will generate a 24 MB database of US baseball information: http://danial.org/sqlite/lampsig/baseball.sql.bz2 The original data came from http://baseball1.info/statistics/lahman52_csv.zip and the schema is explained in http://baseball1.com/statistics/readme52.txt On 3/4/07, lordphoenix <[EMAIL PROTECTED]> wrote: I'm developing an SQlite database administration tool (web site is there : http://litemanager.tuxamily.org) for Linux using Mono and GTK#. If you want test it use svn version the last commit content all features of next release (Available only in french for the moment if someone can help me for translation he is welcome). But in order to do some test I would need some databases. So if you have database with a significant volume of data and tables and you don't mind to send me a copy it would be very great full. I precise I don't care about data in this database I won't keep any of this database after my test and of course I will never send any content to nobody. So if someone can help me . PS Sorry for my bad English I don't speak it very often :) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] optimizing database creation speed
On 7/21/05, Chuck Pahlmeyer - MTI <[EMAIL PROTECTED]> wrote: > I have an application in which I'd like to create a database as > quickly as possible. The application has a batch process at > startup which creates the data. I am using a single transaction > for all of the INSERT statements. I'm also using prepared statements > to alleviate some of the overhead for processing SQL text. Typical > table sizes are on the order of a few million rows. I can get > about 50,000 rows/second on my 3GHz Linux system, but would like to > do better. What are the data types of the columns? For integers and floats I've seen insert speeds of over 300,000 rows/second. One thing that helps a lot is building SQLite with optimization flags that are tweaked to your CPU. http://anchor.homelinux.org/SQLiteTuning shows some good settings for Pentium4, Opteron, and Athlon. That site also gives specs for a system which can do nearly 310,000 inserts/sec for a table having one integer and three floats. Other things to look at: disk drives with large densities (>= 300 GB) and high RPM, different file systems (xfs has proven to be fast but I bet the old ext2 may be faster still), different Linux kernels. I've found a marginal (3%) performance boost by dowloading the beta for GCC v4.1, then building SQLite (again, with all the optimization tweaks) with it instead of GCC 3.x. But that's a lot of hassle for little gain.-- Al
Re: [sqlite] sqlite with FORTRAN
I added to the wiki (http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers) an entry for Fortran. It links to a page on my web site, http://danial.org/sqlite/fortran/, that has an example of a Fortran program that calls C wrapper functions which in turn call functions in the SQLite library. The demo creates a database, inserts values into a table, then does a query. It builds with g77 and gcc. -- Al On 7/8/05, Cornel Gazdaru <[EMAIL PROTECTED]> wrote: > Hi > I am trying to figure out a wrapper to access sqlite from FORTRAN (g77 > and gcc) > I try using "cfrotran.h" but it seems I have problems passing arguments. > Not sure I get the sqlite3 handle correctly > Has anybody tried that before? > Thanks > Cornel > >
Re: [sqlite] beat 120,000 inserts/sec
On Apr 9, 2005 12:43 AM, Andy Lutomirski <[EMAIL PROTECTED]> wrote: > Al Danial wrote: > > The attached C program measures insert performance for populating > > a table with an integer and three random floating point values with > > user defined transaction size. Usage is: > > > > ./sqlite_insert > > All of these are on Gentoo, Athlon 64 3200+, running 64 bit. > > Writing to /tmp, which is ext3 (acl,user_xattr,usrquota) over RAID0 (two > slave drives on different channels): > > $ ./sqlitetest 10 5 > 10 inserts to /tmp/a.db in 0.531 s = 188446.34 inserts/s > $ ./sqlitetest 200 5 > 200 inserts to /tmp/a.db in 11.546 s = 173223.61 inserts/s That's impressive. Clearly a well-implemented RAID0 configuration and fast disks make a huge difference. This will be the direction I'll take. Thanks to everyone who posted performance numbers and machine setup info. Some results were counterintuitive (I'd have guessed SCSI drives would come out on top) but many variables are at work so I won't try to draw too many conclusions. Also thanks to the suggestions to study the pragma's. I did try SYNCHRONOUS=off but that didn't seem to have an effect; I'll study the docs to make sure I've got it right. -- Al
[sqlite] beat 120,000 inserts/sec
A scientific application I work with has clumsy data retrieval
options. I dumped the application's output--integer and floating
point numbers--into an SQLite database and soon after began to
enjoy the power of SQL to pull out interesting results.
The main complaint for making the transfer to SQLite a permanent
part of our solution is the time it takes to insert the numbers
into a database. It takes about a minute to insert 24 million
numbers into three tables. Most database people (including me)
would be thrilled to see this kind of insert performance but
my colleagues are asking if it can be sped up.
The attached C program measures insert performance for populating
a table with an integer and three random floating point values with
user defined transaction size. Usage is:
./sqlite_insert
It writes to the hardcoded database file /tmp/a.db
On my Dell Precision 360, Pentium4 3.0 GHz, 1 GB RAM, IDE disk
drive, ext3, RHEL v3, Linux kernel 2.4.21, I peak out around 121,000
inserts/second using a transaction size of 20,000:
./sqlite_insert 10 2
10 inserts to /tmp/a.db in 0.829 s = 120626.53 inserts/s
Performance drops a bit when I increase the number of rows to
two million (a typical size for my application):
./sqlite_insert 200 5
200 inserts to /tmp/a.db in 17.124 s = 116795.07 inserts/s
What kind of insert performance do you see on your machine? If it
is substantially better than 120 kinserts/s what kind of hardware
do you have? I'm especially interested in how much faster the
code runs on systems with multiple disks in a RAID 0 configuration.
Are there other tricks to speeding insert performance? Is it even
reasonable to ask for more? -- Al
/*
[EMAIL PROTECTED]
# sample build:
gcc -o sqlite_insert sqlite_insert.c \
-L/usr/local/sqlite-3.2.1/lib -lsqlite3 -I/usr/local/sqlite-3.2.1/include
# sample run:
./sqlite_insert 10 5
*/
#include
#include /* RAND_MAX */
#include /* timeval, gettimeofday() */
int sql_begin(sqlite3 *db) { /* {{{1 */
char *errmsg;
if (sqlite3_exec(db, "BEGIN TRANSACTION",
NULL, NULL, ) != SQLITE_OK) {
printf("couldn't begin transaction: %s\n", errmsg);
return 0;
} else {
return 1;
}
} /* 1}}} */
int sql_commit(sqlite3 *db) { /* {{{1 */
char *errmsg;
if (sqlite3_exec(db, "COMMIT TRANSACTION",
NULL, NULL, ) != SQLITE_OK) {
printf("couldn't commit transaction: %s\n", errmsg);
return 0;
} else {
return 1;
}
} /* 1}}} */
float elapsed(struct timeval start, struct timeval end) { /* {{{1 */
return (float) (end.tv_sec - start.tv_sec ) +
((float) (end.tv_usec - start.tv_usec)/100);
} /* 1}}} */
int main(int argc, char *argv[]) {
const char *zLeftover;
#define CMD_SIZE 1000
char rm_command[CMD_SIZE],
*errmsg, *dbfile = "/tmp/a.db";
/* *dbfile = ":memory:" is faster, but not of interest */
intrc, i, N, xact_size, n_this_xact = 0;
double x, y, z;
float delta_T;
struct timeval start_time, end_time;
sqlite3 *db;
sqlite3_stmt *Stmt;
if (argc < 3) {
printf("\nUsage: %s\n\n", argv[0]);
printf("\tInsert rows into a table of an SQLite database\n");
printf("\tusing transaction sizes of .\n");
printf("\tThe table has four columns of numeric data:\n;);
printf("\t field_1 integer\n");
printf("\t field_2 float\n");
printf("\t field_3 float\n");
printf("\t field_4 float\n");
printf("\tThe integer field will have values 1.. while the\n");
printf("\tdouble precision values are random on [-50.0, 50.0]\n");
exit(0);
}
N = atoi(argv[1]);
xact_size = atoi(argv[2]);
snprintf(rm_command, CMD_SIZE-1, "rm -f %s", dbfile);
system(rm_command); /* the database file must not exist before
calling sqlite3_open() and trying to insert */
gettimeofday(_time, 0);
rc = sqlite3_open(dbfile, );
sql_begin(db);
rc = sqlite3_prepare(db, "create table table_name(field_1 integer primary key,"
"field_2 float, "
"field_3 float, "
"field_4 float)",
Re: [sqlite] Test suite fails on AIX using xlc
On Fri, 11 Mar 2005 14:29:44 -0500, Andrew Shakinovsky
<[EMAIL PROTECTED]> wrote:
> Has anyone had any luck compiling SQLite successfully on this platform?
> These are the results from the test suite (with some successful runs
> removed to preserve space).
>
> AIX 5.2 Power4+ 64 bit using xlc 7.0:
> The test aborted at some point before completion.
>
> attach2-4.1.1...
> Expected: [main unlocked temp closed file2 unlocked]
> Got: []
> attach2-4.1.2...
> Expected: [main unlocked temp closed file2 unlocked]
> Got: []
> attach2-4.2... Ok
> attach2-4.2.1...
> Expected: [main shared temp closed file2 unlocked]
> Got: []
> attach2-4.2.2...
> Expected: [main unlocked temp closed file2 unlocked]
> Got: []
> attach2-4.3... Ok
> attach2-4.3.1...
> Expected: [main shared temp closed file2 unlocked]
> Got: []
> attach2-4.3.2...
> Expected: [main unlocked temp closed file2 unlocked]
> Got: []
> attach2-4.4... Ok
> attach2-4.4.1...
> Expected: [main shared temp closed file2 unlocked]
> Got: []
> attach2-4.4.2...
> Expected: [main unlocked temp closed file2 unlocked]
> Got: []
> attach2-4.5... Ok
> attach2-4.5.1...
> Expected: [main shared temp closed file2 unlocked]
> Got: []
> attach2-4.5.2...
> Expected: [main unlocked temp closed file2 reserved]
> Got: []
> attach2-4.6.1... Ok
> attach2-4.6.1.1...
> Expected: [main shared temp closed file2 shared]
> Got: []
> attach2-4.6.1.2...
> Expected: [main unlocked temp closed file2 reserved]
> Got: []
> attach2-4.6.2... Ok
> attach2-4.6.2.1...
> Expected: [main shared temp closed file2 shared]
> Got: []
> attach2-4.6.2.2...
> Expected: [main unlocked temp closed file2 reserved]
> Got: []
> attach2-4.7... Ok
> attach2-4.7.1...
> Expected: [main shared temp closed file2 shared]
> Got: []
> attach2-4.7.2...
> Expected: [main reserved temp closed file2 reserved]
> Got: []
> attach2-4.8... Ok
> attach2-4.8.1...
> Expected: [main shared temp closed file2 shared]
> Got: []
> attach2-4.8.2...
> Expected: [main reserved temp closed file2 reserved]
> Got: []
> attach2-4.9... Ok
> attach2-4.9.1...
> Expected: [main shared temp closed file2 shared]
> Got: []
> attach2-4.9.2...
> Expected: [main reserved temp closed file2 reserved]
> Got: []
> attach2-4.10... Ok
> attach2-4.10.1...
> Expected: [main shared temp closed file2 shared]
> Got: []
> attach2-4.10.2...
> Expected: [main pending temp closed file2 reserved]
> Got: []
> attach2-4.11... Ok
> attach2-4.11.1...
> Expected: [main unlocked temp closed file2 unlocked]
> Got: []
> attach2-4.11.2...
> Expected: [main pending temp closed file2 reserved]
> Got: []
> attach2-4.12...
> Expected: [0 {}]
> Got: [1 {disk I/O error}]
> attach2-4.12.1...
> Expected: [main unlocked temp closed file2 unlocked]
> Got: []
> attach2-4.12.2...
> Expected: [main unlocked temp closed file2 unlocked]
> Got: []
> attach2-4.13... Ok
> attach2-4.14... Ok
> attach2-4.15...
> Expected: [1 2 1 2]
> Got: [1 2]
> attach2-5.1... Ok
> attach2-5.2...
> Error: disk I/O error
> autovacuum-ioerr-2.2.1...
> Error: no such function: randstr
> autovacuum-ioerr-2.2.2... Ok
> autovacuum-ioerr-2.2.3...
> Expected: [1]
> Got: [0]
> autovacuum-ioerr-3.1.1...
> Error: no such function: randstr
> autovacuum-ioerr-3.1.2... Ok
> autovacuum-ioerr-3.1.3...
> Expected: [1]
> Got: [0]
> autovacuum-ioerr-5.1.3...
> Expected: [1]
> Got: [0]
> autovacuum-ioerr-7.2.1... Ok
> autovacuum-ioerr-7.2.2... Ok
> autovacuum-ioerr-7.2.3... Ok
> autovacuum-ioerr2-1.1.1...
> Error: no such function: randstr
> autovacuum-ioerr2-1.1.2... Ok
> autovacuum-ioerr2-1.1.3...
> Expected: [1]
> Got: [0]
> autovacuum-ioerr2-2.1.1...
> Error: no such function: randstr
> autovacuum-ioerr2-2.1.2... Ok
> autovacuum-ioerr2-2.1.3...
> Expected: [1]
> Got: [0]
> autovacuum-ioerr2-3.1.1... Ok
> autovacuum-ioerr2-3.1.2... Ok
> autovacuum-ioerr2-3.1.3... Ok
> autovacuum-ioerr2-4.1.1...
> Error: no such function: randstr
> autovacuum-ioerr2-4.1.2... Ok
> autovacuum-ioerr2-4.1.3...
> Expected: [1]
> Got: [0]
> bigfile-1.1... Ok
> bigfile-1.2...
> Error: file is encrypted or is not a database
> bigfile-1.3...
> Error: file is encrypted or is not a database
> bigfile-1.4...
> Error: file is encrypted or is not a database
> bigfile-1.5...
> Error: file is encrypted or is not a database
> bigfile-1.6...
> Error: file is encrypted or is not a database
> bigfile-1.7...
> Error: file is encrypted or is not a database
> bigfile-1.8...
> Error: file is encrypted or is not a database
> bigfile-1.9...
> Error: file is encrypted or is not a database
> bigfile-1.10...
> Error: file is encrypted or is not a database
> bigfile-1.11...
> Error: file is encrypted or is not a database
> bigfile-1.12...
> Error: file is encrypted or is not a database
> bigfile-1.13...
> Error: file is encrypted or is not a database
> bigfile-1.14...
> Error: file is encrypted or is not a
[sqlite] valgrind and sqlite3 command line tool
I'm using valgrind to help locate memory problems in C code I wrote that uses the SQLite (v3) library. Valgrind returned an unusual message about threads which was odd because I'm not doing multithreading. Backing up a bit I turned valgrind loose on the sqlite3 command line tool. Here's what it returns: /home/al> valgrind --version valgrind-2.2.0 /home/al> valgrind /usr/local/sqlite-3.0.7/bin/sqlite3 ==13527== Memcheck, a memory error detector for x86-linux. ==13527== Copyright (C) 2002-2004, and GNU GPL'd, by Julian Seward et al. ==13527== Using valgrind-2.2.0, a program supervision framework for x86-linux. ==13527== Copyright (C) 2000-2004, and GNU GPL'd, by Julian Seward et al. ==13527== For more details, rerun with: -v ==13527== ==13527== Syscall param sigaction(act) contains uninitialised or unaddressable byte(s) ==13527==at 0x1B9F1025: __libc_sigaction (in /lib/tls/libc-2.3.2.so) ==13527==by 0x1: ??? ==13527== Address 0x52BFC7B0 is on thread 1's stackLoading resources from /home/al/.sqliterc SQLite version 3.0.7 Enter ".help" for instructions ==13527== ==13527== Syscall param sigaction(act) contains uninitialised or unaddressable byte(s) ==13527==at 0x1B9F1025: __libc_sigaction (in /lib/tls/libc-2.3.2.so) ==13527== Address 0x52BFC544 is on thread 1's stack CVSTrac shows a couple of other valgrind related issues http://www.sqlite.org/cvstrac/tktview?tn=856, http://www.sqlite.org/cvstrac/tktview?tn=536,20 but they are resolved. Are the messages valgrind shows above anything to be concerned about? I'm pretty sure the memory problems in my C code are my fault and not a problem with the sqlite library. Still, it would be reassuring so see the sqlite3 command line tool be free of valgrind warnings.-- Al
Re: [sqlite] SQLite on 64-bit unix
Just confirming that the patched code (cvs checkout this morning @ 8 AM PDT) works on the Opteron machine w/Gentoo 2004.1 amd_64 mentioned earlier in this thread. Cool! Only two glitches left: 2 errors out of 14857 tests Failures on these tests: printf-8.1 printf-8.2 the errors are printf-8.1... Error: integer value too large to represent printf-8.2... Error: integer value too large to represent On Wed, 08 Sep 2004 17:15:22 -0400, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > Matt Wilson wrote: > > > > Works with the attached patch. > > The patch is added. Tnx. > > > Oh, forgot to mention that printf tests fail: > > > > They're all failures of this type: > > Expected: [Three integers: -1 17] > > Got: [Three integers: -1 377] > > > > Can you send me the output of running printf.test, please. > It probably isn't important but I'd like to get it right > all the same... > > > > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > >
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?
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
> / \
>
[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
