Re: [sqlite] resource temporarily unavailable after sqlite3_open()
D. Richard Hipp , On 8/11/2009 16:39: > program is using LinuxThreads or NPTL for its threading. (SQLite has > to know which is used because there are serious bugs in LinuxThreads > that SQLite has to work around.) So pthreads gets used once, by > SQLite, even if you don't do any threading in your application. > Probably something about that pthread_create() call is messing up the > exec. > > If you app does not use threads, then by all means compile with > SQLITE_THREADSAFE=0 because that makes SQLite run faster. > Would it help to know, where threading is enabled in the library, if pthread_create() fails? Is there a function that can tell me the status? Or, is there a switch that will force sqlite to use native thread library instead? Fortunately my app isn't forking out SqLite tasks. It does fork itself to run as a daemon, tho. All my tests were done *before *forking. I tried the same test cases after the fork and got the same results. /m ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] resource temporarily unavailable after sqlite3_open()
D. Richard Hipp , On 8/11/2009 16:02: > > Perhaps pthreads is going goofy. Please recompile with - > DSQLITE_THREADSAFE=0 and see if that helps. > BINGO. env CC="gcc-cris -mlinux -isystem $EROOT/include"CPP="gcc-cris -mlinux -E -isystem $EROOT/include" CXX="g++-cris -mlinux -xc++ -isystem $EROOT/include" LDFLAGS="-L$EROOT/lib" OBJCOPY="objcopy-cris" LD="ld-cris -mcrislinux" AR="ar-cris" RANLIB="ranlib-cris" NM="nm-cris" STRIP="strip-cris" ./configure --host=cris-axis-linux-gnu --prefix=/AEMDEV/83+/devboard-R2_10/target/cris-axis-linux-gnu --enable-static=yes --enable-shared=yes --disable-threadsafe --disable-tcl Works like a charm. How can I help find the issue with my test case? /m ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] resource temporarily unavailable after sqlite3_open()
Yes, tried that. No change in result. Assuming sqlite does a |F_GETFD| then restores when done. I built in some testing to see if the file descriptor was being mangled, and cannot detect any difference. Test: flags = fcntl(fd[0], F_GETFL, 0); char *cFlagMsg; cFlagMsg=calloc(255,sizeof(char)); snprintf(cFlagMsg,255,"%s","FLAGS: "); if (flags & O_NONBLOCK) strcat(cFlagMsg,"O_NONBLOCK "); if (flags & O_APPEND) strcat(cFlagMsg,"O_APPEND "); if (flags & O_DSYNC) strcat(cFlagMsg,"O_DSYNC "); if (flags & O_RSYNC) strcat(cFlagMsg,"O_RSYNC "); if (flags & O_SYNC) strcat(cFlagMsg,"O_SYNC "); Results: // before change to O_NONBLOCK GETFL Flags= 0 // after change to O_NONBLOCK GETFL Flags= 2048 FLAGS: O_NONBLOCK I inserted this code in place of my original calls to open and close: sqlite3 *db; char *zErrMsg = 0; int rc; rc = sqlite3_open("/etc/solarwave/aem.db", ); if( rc ) { WriteSyslogMessage("Can't open database: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } rc = sqlite3_exec(db, "SELECT * FROM dbsensors", NULL, 0, ); if( rc!=SQLITE_OK ) { WriteSyslogMessage("SQL error: %s\n", zErrMsg); sqlite3_free(zErrMsg); } sqlite3_close(db); (Note: WriteSyslogMessage() issued a system("logger 'string'"); call) Same issue. -1 returned during read(). /m D. Richard Hipp , On 8/11/2009 15:38: > On Aug 11, 2009, at 2:53 PM, Mark Richards wrote: > > >> Environment: >> Linux axis 2.6.19 #9 PREEMPT Mon Apr 6 15:44:03 EDT 2009 cris >> unknown >> >> Sqlite: >> Sqlite: sqlite-3.6.14 >>./configure --host=cris-axis-linux-gnu >> --prefix=/AEMDEV/83+/devboard-R2_10/target/cris-axis-linux-gnu >> --enable-static=yes --enable-shared=yes --disable-dynamic-extensions >> >> Application: >> My application runs against the shared library built as above. >> >> Code in my application fails after calling sqlite3_open(). >> >> This synopsis is of a function designed to spawn a shell, execute a >> command, and read back the result via a pipe of stdout. It works >> fine, >> until sqlite3_open() is called anywhere PRIOR. >> >> prior sqlite3_open() call: >> sqlite3 *dbf; >> sqlite3_open("/path/to/my.db",); >> >> >> fflush(stdout); >> pipe(fd); >> pid = fork(); >> if (pid == 0) >> { >> dup2(fd[1], STDOUT_FILENO); >> dup2(fd[1], STDERR_FILENO); >> close(fd[0]); >> > Have you tried called sqlite3_close() here to see if that helps? All > of SQLite's file descriptors are FD_CLOEXEC, but who knows > > >> execl("/bin/sh", "sh", "-c", "echo 123", 0); >> } >> if (-1 == (flags = fcntl(fd[0], F_GETFL, 0))) >> flags = 0; >> fcntl(fd[0], F_SETFL, flags | O_NONBLOCK); >> >> .. within a loop: >> >> got=read(fd[0], buf, sizeof(buf)); >> if (got>-1) >> { >> snprintf(cValue,4,"%s",buf); >> break; >> } >> // got ==-1 >> > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] resource temporarily unavailable after sqlite3_open()
I also (just) tried 3.6.17. Same issue. /m Mark Richards , On 8/11/2009 14:53: > Environment: > Linux axis 2.6.19 #9 PREEMPT Mon Apr 6 15:44:03 EDT 2009 cris unknown > > Sqlite: > Sqlite: sqlite-3.6.14 > ./configure --host=cris-axis-linux-gnu >--prefix=/AEMDEV/83+/devboard-R2_10/target/cris-axis-linux-gnu > --enable-static=yes --enable-shared=yes --disable-dynamic-extensions > > Application: > My application runs against the shared library built as above. > > Code in my application fails after calling sqlite3_open(). > > This synopsis is of a function designed to spawn a shell, execute a > command, and read back the result via a pipe of stdout. It works fine, > until sqlite3_open() is called anywhere PRIOR. > > prior sqlite3_open() call: > sqlite3 *dbf; > sqlite3_open("/path/to/my.db",); > > > fflush(stdout); > pipe(fd); > pid = fork(); > if (pid == 0) > { > dup2(fd[1], STDOUT_FILENO); > dup2(fd[1], STDERR_FILENO); > close(fd[0]); > execl("/bin/sh", "sh", "-c", "echo 123", 0); > } > if (-1 == (flags = fcntl(fd[0], F_GETFL, 0))) > flags = 0; > fcntl(fd[0], F_SETFL, flags | O_NONBLOCK); > > .. within a loop: > > got=read(fd[0], buf, sizeof(buf)); > if (got>-1) > { > snprintf(cValue,4,"%s",buf); > break; > } > // got ==-1 > > > > When sqlite3_open() is called as above, got returns -1 (forever). -1 > returning from a NONBLOCK read is accepted behaviour, but pretty quickly > the read() should give some data. Instead it returns -1 each time. (If > I allow fd[0] to be in blocking mode, the read() never returns). > > Thinking that perhaps the > > A mangled stdout came to mind, but this does not appear to be the case. > But maybe sqlite_open() or other internals diddle with stdout? > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] resource temporarily unavailable after sqlite3_open()
Environment: Linux axis 2.6.19 #9 PREEMPT Mon Apr 6 15:44:03 EDT 2009 cris unknown Sqlite: Sqlite: sqlite-3.6.14 ./configure --host=cris-axis-linux-gnu --prefix=/AEMDEV/83+/devboard-R2_10/target/cris-axis-linux-gnu --enable-static=yes --enable-shared=yes --disable-dynamic-extensions Application: My application runs against the shared library built as above. Code in my application fails after calling sqlite3_open(). This synopsis is of a function designed to spawn a shell, execute a command, and read back the result via a pipe of stdout. It works fine, until sqlite3_open() is called anywhere PRIOR. prior sqlite3_open() call: sqlite3 *dbf; sqlite3_open("/path/to/my.db", ); fflush(stdout); pipe(fd); pid = fork(); if (pid == 0) { dup2(fd[1], STDOUT_FILENO); dup2(fd[1], STDERR_FILENO); close(fd[0]); execl("/bin/sh", "sh", "-c", "echo 123", 0); } if (-1 == (flags = fcntl(fd[0], F_GETFL, 0))) flags = 0; fcntl(fd[0], F_SETFL, flags | O_NONBLOCK); .. within a loop: got=read(fd[0], buf, sizeof(buf)); if (got>-1) { snprintf(cValue,4,"%s",buf); break; } // got ==-1 When sqlite3_open() is called as above, got returns -1 (forever). -1 returning from a NONBLOCK read is accepted behaviour, but pretty quickly the read() should give some data. Instead it returns -1 each time. (If I allow fd[0] to be in blocking mode, the read() never returns). Thinking that perhaps the A mangled stdout came to mind, but this does not appear to be the case. But maybe sqlite_open() or other internals diddle with stdout? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite.org needs online forms
john s wolter wrote: SQLite.org in my opinion, needs to have online community forms. I first used majordomo ten years ago which is like the list manager being used for sqlite-users@sqlite.org but in today's Internet it can be mistaken for SPAM. I do not know if SQLite would qualify because of the Public Domain license but maybe sourceforge or the likes of freshmeat or other FOSS have forms for all their hosted projects. That would allow browsing of prior support issues and make it easier for users to benefit from that collected knowledege. Let us all know how you react to this idea. You mean "forums"? This is an old and tired argument. Please save the messages and search on them (I do this) or use google. /m - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] how do i generate a uniqueidentifier ?
Chase wrote: how do i generate a uniqueidentifier ? Define a column as follows: {fieldname} INTEGER NOT NULL PRIMARY KEY eg: CREATE TABLE hardware_types (record_key INTEGER NOT NULL PRIMARY KEY,hardware_key INTEGER default 0); - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: create or update question
Eric S. Johansson wrote: if the insert fails (i.e. record exists), it triggers an exception which I use to trigger an update. I get many more updates than inserts of course but I haven't figured out how to trigger an exception on update if the record doesn't exist. From the FWIW dept, would an update be attempted if a record doesn't exist? Perhaps test to see if the record exists first, then choose the applicable query? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] migrating from mysql
Frederick Grim wrote: Howdy all, So I am wondering if anyone has run into this problem. I am trying to move from mysql to sqlite and the unique constraint from primary keys is not allowing me to insert my sql that I dumped from the mysql database. I have a bunch of join tables so I really can't set the id field to null and let sqlite renumber the keys. I guess I could dump each table into a separate .csv file or something and use the import pragma but I am hoping there is just so pragma I can give it for it not to enforce key constraints through the import. Can I do this? I have run into this, or it into me, but not cross-server and not with the complexity of joined tables. I think the principles are the same. My guess is that you have existing records in the target table with keys that are identical to those you are trying to import (you said "insert my sql that I dumped from the mysql database"). Since you rely on these keys to join other tables, I can imagine that there's a possibility that anything you do will mangle the associations that exist. Someone here might have a quick and easy way and I'd love to read it. If you can easily re-build the joins, one option would be to use an INSERT INTO [table]( {with a field list that excludes your key field}) VALUES(..) or UPDATE [table] SET {with a field list that excludes your key field} as applicable. A little more work than just importing. Another option is to build a process that reads the data from the source and populates it according to the existing constraints. You might also consider nulling all your master table source record key fields and import these (new keys will be assigned), and then use a process to re-build the joins with the other tables. If it were my project, I'd see a few hours of programming in the near term. Knowing me, I'd also back up everything. /m - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Emergency Bail Out
Rich Shepard wrote: Yesterday I trapped myself in sqlite3 and could not exit the application. I was running the SQLite shell in a virtual console and inadvertently entered ',e' instead of '.e' to exit. Cue the theme from 'Jaws.' I was trapped and could not escape. Not, that is, until I went to another vc and killed the process. The next time my fingers get ahead of my eyes and brain, I'd like to use a more elegant method of recovering from my mis-typed command. What do I do? CONTROL D Rich - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Abuse of the SQLite website
[EMAIL PROTECTED] wrote: Thoughts anyone? Are there less drastic measures that might be taken to prevent this kind of abuse? Headers can always be forged as to browser and OS. Attackers will eventually figure it out and then you're back to the drawing board again. As tempting as this solution is, it probably won't help in the longer term. Cutting off access to a specific IP requires manual maintenance. There are automated solutions as were pointed out, but these can become cumbersome to maintain and I have read somewhere (sorry, can't give you a reference to it) that piles of iptables rules can cause a slow-down in iptables processing. Returning a link that expires in an email to the requester is a method that works nicely. It does require some programming and maintenance, but would be a nice gatekeeper. I doubt that there would be any privacy concerns. The email address would be used to simply route the url and then be discarded. /m - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Retrieve Table Structure
[EMAIL PROTECTED] wrote: What is the easiest way to retrieve the structure of a table? The only thing i have found so far is by parsing the `sqlite_master`.`sql` which seems to be too much coding. I want them as (char** column_names), (char **column_types) or something similar. thanks in advance Ivailo Karamanolev .schema {tablename} sqlite3 .help - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] database image malformed
Dan Kennedy wrote: On Tue, 2007-01-09 at 22:28 -0500, Mark Richards wrote: Using sqlite in our embedded device has offered tremendous capabilities and very conveniently, and I thank the developers and enthusiasts who continue to further this excellent project. I've had one issue that I cannot explain and would ask for some input. sqlite 3.1.3 linux kernel 2.6.12 cris-axis-linux-gnu One field system began to issue "database disk image is malformed" for reasons that I cannot yet explain. I ran a PRAGMA integrity_check, which told me: sqlite> PRAGMA integrity_check; *** in database main *** Main freelist: 1 of 1 pages missing from overflow list starting at 0 Page 46 is never used rowid 1355980 missing from index timestamp rowid 1356049 missing from index timestamp ... rowid 1356108 missing from index timestamp wrong # of entries in index timestamp wrong # of entries in index dataid sqlite> VACUUM failed with the same "database disk image is malformed". Attempts at deleting all records from the broken table failed. In the end, I was able to repair the database by bringing it down to my workstation and loading it in the SQLite Administrator windows GUI and executing Database: cleanup. My database is built using the following PRAGMA statements: pragmaPRAGMA auto_vacuum = 1; pragmaPRAGMA count_changes = 1; pragmaPRAGMA empty_result_callbacks = 1; pragmaPRAGMA legacy_file_format = OFF; pragmaPRAGMA synchronous = OFF; With pragma synchronous set to "OFF", if the device lost power or the operating system crashed in the middle of a transaction database corruption can occur. That may well be it. The box reboots every 24 hours but when it does it uses a stable copy of the database and all the "operating" data is replaced. However the stable copy is updated every hour by purging records older than a certain timeframe, performing a VACUUM, and then overwriting the stable copy. Looking at the cron scheduling I see that both events have an opportunity to collide and may well have done so particularly since the table that issued errors is the same one that is updated during this process. I wish there were a way to change these PRAGMA settings on a built table. Apparently one has to start from scratch. 1) are there any tools available in sqlite3 that will help me find the cause of this type of issue? 2) does anyone know what did SQLite Administrator do that VACUUM didn't? The integrity check shows problems with index structures only - so maybe SQLite Administrator issued queries that never used an index. Although that doesn't explain why the VACUUM failed, I would of thought the same reasoning would apply. Maybe it ran out of space in the file-system or something? No, there's plenty of room on the partition. I'll ask the author of SQLite Administrator about it. 3) since auto_vacuum is ON, I still need to do a VACUUM every so often. Any ideas why? A vacuum recreates an entire database, more or less ensuring that records are packed into database pages with very little wasted space. By contrast, auto-vacuum mode automatically shrinks the file whenever one or more pages are completely empty. So in an auto-vacuum database file there are never empty pages but the packing of records may be sub-optimal. Hence it is possible (even likely) that a VACUUM operation will reduce the size of an auto-vacuum database a bit. The btree layer tries to keep every page at least 2/3 full during regular balancing, so I would guess a VACUUM could shrink an auto-vacuum database by at most 33%. Almost certainly less. Does this match up with what you're seeing? Ran one this morning against the database. Prior to VACUUM: 233472 bytes After VACUUM: 168960 bytes That's about 28% difference. Matches your spec. Thank you! /m - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] database image malformed
Using sqlite in our embedded device has offered tremendous capabilities and very conveniently, and I thank the developers and enthusiasts who continue to further this excellent project. I've had one issue that I cannot explain and would ask for some input. sqlite 3.1.3 linux kernel 2.6.12 cris-axis-linux-gnu One field system began to issue "database disk image is malformed" for reasons that I cannot yet explain. I ran a PRAGMA integrity_check, which told me: sqlite> PRAGMA integrity_check; *** in database main *** Main freelist: 1 of 1 pages missing from overflow list starting at 0 Page 46 is never used rowid 1355980 missing from index timestamp rowid 1356049 missing from index timestamp ... rowid 1356108 missing from index timestamp wrong # of entries in index timestamp wrong # of entries in index dataid sqlite> VACUUM failed with the same "database disk image is malformed". Attempts at deleting all records from the broken table failed. In the end, I was able to repair the database by bringing it down to my workstation and loading it in the SQLite Administrator windows GUI and executing Database: cleanup. My database is built using the following PRAGMA statements: pragmaPRAGMA auto_vacuum = 1; pragmaPRAGMA count_changes = 1; pragmaPRAGMA empty_result_callbacks = 1; pragmaPRAGMA legacy_file_format = OFF; pragmaPRAGMA synchronous = OFF; 1) are there any tools available in sqlite3 that will help me find the cause of this type of issue? 2) does anyone know what did SQLite Administrator do that VACUUM didn't? 3) since auto_vacuum is ON, I still need to do a VACUUM every so often. Any ideas why? /mark richards - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Why is VACUUM so slow?
Nemanja Corlija wrote: I have a db with one table that has a text primary key and 16 text columns in total. After importing data from CSV file db had 5M rows and file size was 833MB. After some big DELETEs db had around 3M rows and 500MB after "VACUUMing". Running VACUUM for more then an hour filled new db with ~300MB worth of data. I then aborted that and did something like this: PRAGMA page_size=4096; PRAGMA synchronous=OFF; PRAGMA cache_size=10; PRAGMA temp_store=MEMORY; CREATE TABLE tbl1(same structure as in original db); ATTACH "original.db3" AS old; INSERT INTO main.tbl1 SELECT * FROM old.tbl1; This finished in less then 15 minutes. Timings are obviously very rough here, but time difference is obvious. Except for page_size, VACUUM had same PRAGMAs applied. Isn't what I did manually above very similar to what VACUUM does behind the scenes? If so, why is there such a big difference? One observation though, while VACUUM seemed to be completely I/O bound. INSERT seemed to be CPU bound, which is not surprising given that it was doing uniqueness check for PRIMARY KEY all over again. I guess VACUUM doesn't have to do that since its copying from existing db that already had uniqueness enforced. This was on Win2000, SQLite 3.3.7 via sqlite3.exe. Has anyone experienced similar performance difference? Yes. I had to build some code to export the data, rebuild the database, and import. This wasn't a big deal to run on a daily basis and has saved the horrors of losing the database entirely which, on the embedded platform that it runs upon, could not be recovered in situ due to memory constraints. In the end, switching to sqlite3 and using the auto vacuum pragma has resolved the problem. /mark richards -- "I'm convinced that the universe has been created by someone with a particularly vile sense of humor. I would like to propose a theory of "intelligent malicious asshole design." - anon - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Can anyone recommend some ISAM db to me?
By "the parsing" do you mean to say it is difficult to read the result of a query? Yes, I think that can become quite complicated. But there are some good recipes for doing this, including the simple one in the documentation. You can do a lot with simple SQL statements. Before throwing SQLite overboard, why don't you explain your problem and see if someone here might help. /m Sarah wrote: > Hi, all > After trying SQLite on my embedded platform, I feel that it's a little too > complicated and time-consuming to my platform, especially the parsing. > So, could someone recommend several ISAM ones to me?(I'm a newbie of > database*^_^*) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite under linux
To use the library, either in shared or static mode, you must first build it successfully and then install it, Please tell us your build procedure and any errors you got. /m Lloyd wrote: then I tried to "make" the file, but it failed, any how I got the necessary sqlite3.h file. But still I don't know from where to download the sqlite library and how to install it in my machine. Awaiting for your kind reply. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Query returns all rows
Hmm.. I'm so used to doing this via php's sqlite interface: SELECT * FROM blah WHERE id = "12345"; "form" in double quotes is an alternative way to refer to FORM column - it is _not_ a string literal. So your query condition is WHERE FORM=FORM, which is of course always true (except maybe when FORM is NULL). A string literal should be enclosed in single quotes, as in 'form'. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query returns all rows
I would tend to avoid mixed case and never use all upper case to specify anything (other than when defining a constant perhaps). Your use of case between "FORM" and "Form" as example. Also the use of the term "Type" for a fieldname (or variable) may be stretching reserved words a bit. For that matter, I'd suspect "FORM" or "Form" as well. Note your query says FORM but the fieldname is Form. I bet something got munged as above to produce this. Your table also has no primary key. Could this be an index problem? Did you define one? What data did you populate in the fields? Or, there must be something obvious I've overlooked. /m David Champagne wrote: If I have a table defined in the following manner CREATE TABLE License (Form varchar(256), Type int, NbOcc int) and then I execute a query SELECT * FROM License WHERE FORM = "form"; I get all rows returned, even though I only want the rows where the column FORM contains the string "form". - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: sqlite3_get_table and non-strings
>>I can't help but notice that verb field is declared as TEXT(80) in the >>database, but char[16] in your structure. I couldn't help notice that you noticed. :) >>If you mean an instance of a structure, then of course you can >> allocate one on the heap. Yes, that's what I meant to say. Thanks for leading me in the proper direction. /m - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: sqlite3_get_table and non-strings
> Do you expect sqlite3_get_table to look at char*** pointer and somehow > guess that it's a structure, and figure out types of individual > fields? How would you go about implementing something like that? Ha! Good question. I suppose this has already been broached when the design of sqlite3_get_table was addressed. My first thought would be to pass sqlite3_get_table a structure that represents the table in field-order. ie: CREATE TABLE sequence (seq_nr INTEGER NOT NULL PRIMARY KEY, seq_family INTEGER, enable INTEGER, verb TEXT(80)); typedef struct _sequence { int seq_nr; int seq_family; int enable; char text[16]; } sequence[10]; Then when each row is parsed each column is populated into the associated structure element based on the field type of the column. sqlite ought to know that as it's likely an internal value. I've already written a function like this that works with sqlite_exec to a callback. Packaging it in sqlite3_get_table would simplify some tasks but clearly wouldn't offer a generic solution (you'd have to create a structure for each table you want to get, and make sure that your SELECT statement will generate the columns in the order of your structure). In C is there such a thing as a structure created dynamically (at runtime)? -- I'll have a look at sqlite3_prepare, sqlite3_step and sqlite3_column_ /m - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite3_get_table and non-strings
Although it does not appear to be mentioned in the documentation, is it correct to assume that sqlite3_get_table can only handle string datatypes? In a particular problem I am working a query is made to return an integer and three string fields. The issue may have more to do with the wrapper I am using (e_sqlite.c) which defines a structure to hold the data with a char fieldtype for each column. It does this in a loop following the call: recordset[i][j] = (char *) malloc( (strlen(result[count]) + 1) ); strcpy(recordset[i][j], result[count]); So the result data is assumed to always be a string type. Is sqlite3_get_table intended to work properly if it is I passed a structure that represents the data I want it to return? It would be great if someone might point me to an example of sqlite3_get_table that works with different field types. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] legacy_file_format
That's the problem! Thank you for pointing it out. /m [EMAIL PROTECTED] wrote: Version 3.3.7 creates (by default) a database file that can be read or written by any version of SQLite back to version 3.0.0. There is no need to do the "PRAGMA legacy_file_format=ON". That is now the default. But you are trying to read the database with SQLite version 2.8.17, which is earlier (and vastly different) from version 3.0.0. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] legacy_file_format
With sqlite version 3.3.7 if I create a new database using: if (sqlite3_open("/var/tmp/solarwave/aem.db", )!=0) { printf("Cannot open db\n"); return(false); } and then issue the following: snprintf(query, QUERY_SIZE, "%s", "PRAGMA legacy_file_format = ON"); nResult=sqlite3_exec(db, query, NULL, NULL, ); snprintf(query, QUERY_SIZE, "%s", "PRAGMA auto_vacuum = 1"); nResult=sqlite3_exec(db, query, NULL, NULL, ); snprintf(query, QUERY_SIZE, "%s", "PRAGMA empty_result_callbacks = 1"); nResult=sqlite3_exec(db, query, NULL, NULL, ); snprintf(query, QUERY_SIZE, "%s", "PRAGMA synchronous = NORMAL"); nResult=sqlite3_exec(db, query, NULL, NULL, ); Everything works fine - within the application. However I have a php/sqlite combo that also talks to the same database. When it does, I get Warning: sqlite_open(): file is encrypted or is not a database in /mnt/flash/runtime/exec/dumprecords.php on line 46 Doesn't PRAGMA legacy_file_format = ON cover this? The docs state: When this flag is on, new SQLite databases are created in a file format that is readable and writable by all versions of SQLite going back to 3.0.0. When the flag is off, new databases are created using the latest file format which might to be readable or writable by older versions of SQLite. Now since the docs say "When this flag is ON, new SQLite databases are created in a file format that is readable...", I wondered if this meant that I have to set the PRAGMA *before* creating the db? How is that possible?? Or maybe this PRAGMA is broken? /m - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] A lillte help adding sqlite to a c program
I don't know how to work string types in c++, but it looks like you need to initialize an integer t=0, or replace sql[t] with sql[0] perhaps? /m Lloyd Thomas wrote: Jay, Thanks for your reply. I gave it a try with and got a few errors. as follows --- logger.cpp:609: error: invalid operands of types `const char[80]' and `char[4]' to binary `operator+' logger.cpp:615: error: `t' was not declared in this scope logger.cpp:615: warning: unused variable 't' logger.cpp:634: error: jump to case label logger.cpp:631: error: crosses initialization of `std::string test2' logger.cpp:628: error: crosses initialization of `std::string test1' logger.cpp:637: error: jump to case label logger.cpp:631: error: crosses initialization of `std::string test2' logger.cpp:628: error: crosses initialization of `std::string test1' logger.cpp:638: error: jump to case label logger.cpp:631: error: crosses initialization of `std::string test2' logger.cpp:628: error: crosses initialization of `std::string test1' logger.cpp:639: error: jump to case label logger.cpp:631: error: crosses initialization of `std::string test2' logger.cpp:628: error: crosses initialization of `std::string test1' logger.cpp:641: error: `t' was not declared in this scope logger.cpp:641: warning: unused variable 't' logger.cpp:664: error: jump to case label logger.cpp:621: error: crosses initialization of `bool Loop' logger.cpp:634: warning: destructor needed for `test2' logger.cpp:634: warning: where case label appears here logger.cpp:634: warning: (enclose actions of previous case statements requiring destructors in their own scope.) logger.cpp:637: warning: destructor needed for `test2' logger.cpp:637: warning: where case label appears here logger.cpp:638: warning: destructor needed for `test2' logger.cpp:638: warning: where case label appears here logger.cpp:639: warning: destructor needed for `test2' logger.cpp:639: warning: where case label appears here make: *** [logger.o] Error 1 -- line 609 = sql = "insert into call_data (direction, call_time, dest, trunk_no, file_name)values('"+details.inout+"','"+details.statime+"','"+details.cidn+"'"+details.channel+"','"+details.filename+"')"; - Original Message - From: "Jay Sprenkle" <[EMAIL PROTECTED]> To:Sent: Saturday, September 09, 2006 11:16 PM Subject: Re: [sqlite] A lillte help adding sqlite to a c program On 9/9/06, Lloyd Thomas <[EMAIL PROTECTED]> wrote: I know nothing of C++ and therefore need a lilte help editing a C++ app to insert some records into a database. here's an example to read from a database. If you build the sql like you're doing and you use it on the web you leave yourself open to sql injection attacks. Using the bind() method eliminates that vulnerability. Something to consider. Jay Here's some example code: sqlite3*db; // connect to database if ( sqlite3_open( "test.db", ) ) throw "Can't open database"; char* sql; sql = "SELECT one.test1, two.test2" " FROM one" " INNER JOIN two ON one.id = two.id" ; sqlite3_stmt* pStmt; if ( sqlite3_prepare( db, sql, strlen(sql), , NULL ) != SQLITE_OK ) { string str = "Cannot prepare sql: "; str += sql[t]; str += ", Error: "; str += sqlite3_errmsg(db); throw str.c_str(); } bool Loop = true; while ( Loop ) switch ( sqlite3_step( pStmt ) ) { case SQLITE_ROW: // retrieve the results char* p = (char *) sqlite3_column_text( pStmt, 0 ); string test1 = string( p ? p : "" ); p = (char *) sqlite3_column_text( pStmt, 1 ); string test2 = string( p ? p : "" ); break; case SQLITE_DONE: Loop = false; break; case SQLITE_BUSY: case SQLITE_LOCKED: default: string str = "Cannot execute sql: "; str += sql[t]; str += ", Error: "; str += sqlite3_errmsg(db); throw str.c_str(); break; } // clean up when finished sqlite3_finalize( pStmt ); sqlite3_close( db ); -- SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] A lillte help adding sqlite to a c program
Maybe. Here's what I do (in c). DoSql() is a wrapper function. I've included a callback function (and associated structure) below it. The call is made thusly: static char cData[1024]; char query[255]; snprintf(query, QUERY_SIZE, "SELECT * FROM inikeys WHERE inisection = \"%s\" AND inikey = \"%s\" ",section,key); if (!DoSql(query, _inirecord_callback, nDoSqlTimeout)) { FORMAT_TRACE(ERR_DEBUG,"aem.db temporarily locked by another process. Cannot continue"); return(""); } int DoSql(char *query, int (*callback) (), int nRetrySeconds) { int nResult=0; nSQLRetry=0; char *cError; db=sqlite_open("/var/tmp/solarwave/aem.db", 0, NULL); sqlite_busy_timeout(db,1000); printf("DoSql gets query=->%s<-\n",query); while (nSQLRetry<=nRetrySeconds) { nResult=sqlite_exec(db, query, callback, NULL, ); if (nResult==SQLITE_BUSY || nResult==SQLITE_LOCKED) { if (nSQLRetry==0) printf("Sleeping "); else printf("."); sleep(1); ++nSQLRetry; continue; } if (nSQLRetry>0) printf("\n"); break; } if (nResult==SQLITE_BUSY || nResult==SQLITE_LOCKED) { FORMAT_TRACE(ERR_CRIT,"DoSql: aem.db temporarily locked by another process. Cannot continue"); free(cError); sqlite_close(db); return(false); } if (!(nResult==0)) { FORMAT_TRACE(ERR_CRIT,"DoSql: query %s returned error %s. Cannot continue",query, cError); free(cError); sqlite_close(db); return(false); } free(cError); sqlite_close(db); return(true); } struct _inirecord { int record_key; char inisection[30]; char inikey[30]; char iniline[255]; } inirecord[1]; int sql_inirecord_callback(void *args, int numCols, char **results, char **columnNames) { int i; for (i=0; i
Re: [sqlite] Error handling.
Looks like you're not alone... http://www.gatago.com/comp/databases/20027474.html /m - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] concurrency version 2.8.17
That's the ticket.. and it looks like sqlite_busy_timeout() may be my answer. Many thanks! /m [EMAIL PROTECTED] wrote: Look in the www directory. There are TCL script which output HTML that goes (or went) onto the website. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -