Re: [sqlite] Error 21, library routine called out of sequence
Gé
Thanks for the advise, I will alter my program.
Do you know which compilers do need this treatment? (I am using GCC 3.3.3).
Regards,
Ben
Gé Weijers wrote:
On an almost pedantic note:
declare the variable as:
static volatile sig_atomic_t bKilled = 0;
volatile keeps the compiler from caching the value in a register, and
not noticing its change,
and sig_atomic_t is an integer type guaranteed to be written in one
instruction.
Some processors can only write certain integer sizes atomically, chars
may need read and write cycles and are not atomic.
alternatively you can mask the signal before you read 'bKilled'.
Gé
On Jul 8, 2005, at 7:50 AM, Ben Clewett wrote:
Derrell,
Thanks for the idea and the excellent coding example.
This works perfectly, thank!
Regards,
Ben.
[EMAIL PROTECTED] wrote:
Ben Clewett [EMAIL PROTECTED] writes:
Dear SQLite,
I am running a sequence of inserts:
BEGIN
INSERT INTO table ...
INSERT INTO table ...
INSERT INTO table ...
INSERT INTO table ...
INSERT INTO table ...
I am catching the 'kill -1' signal (aka CTRL-C) and executing a
final query:
COMMIT
When I execute the 'COMMIT' I get:
library routine called out of sequence
Every other query command after this returns the same.
My guess is the interrupt is kicking in during SQLite completion of
the previous query. Therefore SQLite is half way through something
when this occurs.
Can any person suggest a possible solution as I am out of options.
For instance, some command to tidy up SQLite so that the next
statement does not fail. Otherwise I loose all my inserted data :)
Instead of issuing the COMMIT from the signal handler, set a global
flag in
the signal handler which you check in your main code. If the flag
has been
set, then COMMIT and exit.
You can do something akin to this (untested code):
-
-
static int bKilled = 0;
static void
sigHandler(int signum)
{
if (signum == SIGTERM)
{
bKilled = 1;
}
}
static void
doInserts()
{
char ** ppQueries;
char * queries[] =
{
INSERT INTO table ...,
INSERT INTO table ...,
INSERT INTO table ...,
NULL
};
/* Start a transaction */
issueQuery(BEGIN;);
/* For each query... */
for (ppQueries = queries; ppQueries != NULL; ppQueries++)
{
/* Issue the query */
issueQuery(*ppQueries);
/* If we've been signaled, exit loop */
if (bKilled)
{
break;
}
}
/*
* Commit the transaction.
*
* Note that signal could have occurred *before* the BEGIN.
You'll need
* to handle that case as well (or ignore the error from COMMIT)
*/
issueQuery(COMMIT;);
}
--
Gé Weijers
e-mail: [EMAIL PROTECTED]
[sqlite] beginnings with sqlite
Hi all, I am new in the world sqlite. I was on the site and I looked at how to make to begin thanks to the link quick start. I thus compiled the sources of sqlite 3.2.2. I have to create a data base test.db. I have to create a table personne and I added data there. I have write the small program given in quick start, I had to add much source files given with sqlite 3.2.2 so that the program is compiled completely. When I launch the program with XCode, I receives an error: SQL error: No such table: personne I gave like parameters: test.db select * from personne When I launch the program with the Terminal, if I write: ./quickStart test.db select * from personne, all occurs well, I receives the data contained in the table personne. But if I write ./quickStart test select * from personne, therefore I the extention in the name of the data base does not put, I receives the same error as for XCode: SQL error: No such table: personne Why the program doesn't go with XCode whereas I give him the good parameters as with the Terminal? Thank you in advance, Denis Lurkin.
[sqlite] Possible enhancement to SQL logic
Dear SQLite, I have found a possible area where the SQL logic of SQLite is not as good as other DBMS. Like MySQL or PostgreSQL. It's a simple thing, so I am showing it as a candidate for work. (If I could submit a patch I would :) If you create a table with two indexes: CREATE TABLE a ( f0 INTEGER PRIMARY KEY, f1 INTEGER, f2 INTEGER ); CREATE INDEX a_f1 ON a (f1); CREATE INDEX a_f2 ON a (f2); Now execute a query using both the indexes: SELECT * FROM a WHERE f1 = 1 OR f2 = 1; This query will not use the indexes. The query will increment through every row in the table. This is obviously very slow. As I mensioned, MySQL and PostgreSQL will use the indexes here, and therefore return the result considerably faster. I can use the 'UNION' to get the result I am after, so it's not a show stopper. I hope this is of interest to somebody. Regards, Ben Clewett.
Re: [sqlite] Possible enhancement to SQL logic
Try adding another index: CREATE INDEX a_f1_f2 ON a (f1, f2); Does that help? Aaron Burghardt On Jul 11, 2005, at 6:07 AM, Ben Clewett wrote: Dear SQLite, I have found a possible area where the SQL logic of SQLite is not as good as other DBMS. Like MySQL or PostgreSQL. It's a simple thing, so I am showing it as a candidate for work. (If I could submit a patch I would :) If you create a table with two indexes: CREATE TABLE a ( f0 INTEGER PRIMARY KEY, f1 INTEGER, f2 INTEGER ); CREATE INDEX a_f1 ON a (f1); CREATE INDEX a_f2 ON a (f2); Now execute a query using both the indexes: SELECT * FROM a WHERE f1 = 1 OR f2 = 1; This query will not use the indexes. The query will increment through every row in the table. This is obviously very slow. As I mensioned, MySQL and PostgreSQL will use the indexes here, and therefore return the result considerably faster. I can use the 'UNION' to get the result I am after, so it's not a show stopper. I hope this is of interest to somebody. Regards, Ben Clewett.
Re: [sqlite] Possible enhancement to SQL logic
On Mon, 2005-07-11 at 11:07 +0100, Ben Clewett wrote: If you create a table with two indexes: CREATE TABLE a ( f0 INTEGER PRIMARY KEY, f1 INTEGER, f2 INTEGER ); CREATE INDEX a_f1 ON a (f1); CREATE INDEX a_f2 ON a (f2); Now execute a query using both the indexes: SELECT * FROM a WHERE f1 = 1 OR f2 = 1; MySQL and PostgreSQL will use the indexes here, and therefore return the result considerably faster. Really? I would be very interested to know what query plan MySQL and PostgreSQL use in this example. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Possible enhancement to SQL logic
Won't work unfortunately. Currently indices are never used if the WHERE clause has an OR expression in it. --- Aaron Burghardt [EMAIL PROTECTED] wrote: Try adding another index: CREATE INDEX a_f1_f2 ON a (f1, f2); Does that help? Aaron Burghardt On Jul 11, 2005, at 6:07 AM, Ben Clewett wrote: Dear SQLite, I have found a possible area where the SQL logic of SQLite is not as good as other DBMS. Like MySQL or PostgreSQL. It's a simple thing, so I am showing it as a candidate for work. (If I could submit a patch I would :) If you create a table with two indexes: CREATE TABLE a ( f0 INTEGER PRIMARY KEY, f1 INTEGER, f2 INTEGER ); CREATE INDEX a_f1 ON a (f1); CREATE INDEX a_f2 ON a (f2); Now execute a query using both the indexes: SELECT * FROM a WHERE f1 = 1 OR f2 = 1; This query will not use the indexes. The query will increment through every row in the table. This is obviously very slow. As I mensioned, MySQL and PostgreSQL will use the indexes here, and therefore return the result considerably faster. I can use the 'UNION' to get the result I am after, so it's not a show stopper. I hope this is of interest to somebody. Regards, Ben Clewett. Sell on Yahoo! Auctions no fees. Bid on great items. http://auctions.yahoo.com/
[sqlite] limiting database size
I would like to know if there is a way to limit a sqlite database and then be able to change this limit later on?
RE: [sqlite] Possible enhancement to SQL logic
Is that statement correct Richard? I assumed that something clever was going on with OR's such that under the bonnet they got translated into UNION statements or similar. Steve -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] rg]On Behalf Of Dan Kennedy Sent: 11 July 2005 11:49 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Possible enhancement to SQL logic Won't work unfortunately. Currently indices are never used if the WHERE clause has an OR expression in it. --- Aaron Burghardt [EMAIL PROTECTED] wrote: Try adding another index: CREATE INDEX a_f1_f2 ON a (f1, f2); Does that help? Aaron Burghardt On Jul 11, 2005, at 6:07 AM, Ben Clewett wrote: Dear SQLite, I have found a possible area where the SQL logic of SQLite is not as good as other DBMS. Like MySQL or PostgreSQL. It's a simple thing, so I am showing it as a candidate for work. (If I could submit a patch I would :) If you create a table with two indexes: CREATE TABLE a ( f0 INTEGER PRIMARY KEY, f1 INTEGER, f2 INTEGER ); CREATE INDEX a_f1 ON a (f1); CREATE INDEX a_f2 ON a (f2); Now execute a query using both the indexes: SELECT * FROM a WHERE f1 = 1 OR f2 = 1; This query will not use the indexes. The query will increment through every row in the table. This is obviously very slow. As I mensioned, MySQL and PostgreSQL will use the indexes here, and therefore return the result considerably faster. I can use the 'UNION' to get the result I am after, so it's not a show stopper. I hope this is of interest to somebody. Regards, Ben Clewett. Sell on Yahoo! Auctions no fees. Bid on great items. http://auctions.yahoo.com/
[sqlite] Problem with sqlite3_prepare
I have a problem with SQLite locking when trying to allocate memory. I
don't know whether this is my problem, or something else. But the
problem occurs inside SQLite.
A backtrack shows:
/usr/local/lib/libsqlite3.so.0(sqlite3ParserAlloc+0x26) [0x4013d1c6]
/usr/local/lib/libsqlite3.so.0(sqlite3RunParser+0xa3) [0x40146033]
/usr/local/lib/libsqlite3.so.0(sqlite3_prepare+0x159) [0x40134fd9]
Which locks in this function, in the file parse.c:
void *sqlite3ParserAlloc(void *(*mallocProc)(size_t)){
yyParser *pParser;
pParser = (yyParser*)(*mallocProc)( (size_t)sizeof(yyParser) ); // *
if( pParser ){
pParser-yyidx = -1;
}
return pParser;
}
* = locking line of code. During the 'malloc' stage, which is I think
passed into this function.
I have tried malloc in my own code, which will happily malloc up to
gigabytes in size.
This occurs after several calls to sqlite3_prepare, say 22 calls. In
each case I call 'sqlite3_finalize'. During a lock, the code waits
indefinitely and has to be killed. Called by Apache.
I am sure this is something I have done. But I do not know what would
effect the ability or SQLite to malloc memory.
If there are any users who have experienced this and may know what I can
do, I would be really glad to hear.
Kind regards,
Ben Clewett.
Apologies if this is wholly irrelevant.
Re: [sqlite] Possible enhancement to SQL logic
D. Richard Hipp wrote: MySQL and PostgreSQL will use the indexes here, and therefore return the result considerably faster. Really? I would be very interested to know what query plan MySQL and PostgreSQL use in this example. It looks like I didn't look before leaping. MySQL does do a sequential search through all rows in table with same example. But PostgreSQL does use indexes for the same example. Regards, Ben.
Re: [sqlite] Possible enhancement to SQL logic
Ben Clewett [EMAIL PROTECTED] writes: D. Richard Hipp wrote: MySQL and PostgreSQL will use the indexes here, and therefore return the result considerably faster. Really? I would be very interested to know what query plan MySQL and PostgreSQL use in this example. It looks like I didn't look before leaping. MySQL does do a sequential search through all rows in table with same example. But PostgreSQL does use indexes for the same example. You can get the PostgreSQL query plan by issuing this query: EXPLAIN SELECT * FROM a WHERE f1 = 1 OR f2 = 1; The output from that command is what Dr. Hipp requested. Derrell
Re: [sqlite] Possible enhancement to SQL logic
[EMAIL PROTECTED] wrote: Ben Clewett [EMAIL PROTECTED] writes: D. Richard Hipp wrote: MySQL and PostgreSQL will use the indexes here, and therefore return the result considerably faster. Really? I would be very interested to know what query plan MySQL and PostgreSQL use in this example. It looks like I didn't look before leaping. MySQL does do a sequential search through all rows in table with same example. But PostgreSQL does use indexes for the same example. You can get the PostgreSQL query plan by issuing this query: EXPLAIN SELECT * FROM a WHERE f1 = 1 OR f2 = 1; The output from that command is what Dr. Hipp requested. Derrell Ok, From MySQL with 310606 rows: ++-+---+--+---+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+--++-+ | 1 | SIMPLE | a | ALL | a_f1,a_f2 | NULL |NULL | NULL | 310606 | Using where | ++-+---+--+---+--+-+--++-+ From PSql with 2534 rows: QUERY PLAN - Index Scan using a_f1, a_f2 on a (cost=0.00..39.86 rows=1003 width=12) Index Cond: ((f1 = 1) OR (f2 = 1)) Regards,
RE: [sqlite] Possible enhancement to SQL logic
On Mon, 2005-07-11 at 14:20 +0100, Steve O'Hara wrote: Is that statement correct Richard? I assumed that something clever was going on with OR's such that under the bonnet they got translated into UNION statements or similar. SQLite does not currently optimizer ORs in any way. If you put ORs in your WHERE clause, no indices will be used. Work is underway to improve on this somewhat. If you say something like: SELECT * FROM a WHERE f1=5 OR f1=11; Then future versions of SQLite will use an index on a(f1). Indices are not currently used unless you say: SELECT * FROM a WHERE f1 IN (5,11); The optimization currently under development is to automatically translate queries of the first form into queries of the second so that indices will be used. Optimizing queries like this: SELECT * FROM a WHERE f1=5 OR f2=11; where two different columns are used in the OR clause is more problematic. MySQL does not do this I have found. I do not know about PostgreSQL - if somebody with PostgreSQL installed could email the EXPLAIN output of such a query, I would appreciate it. One might consider evaluating the query like this: SELECT * FROM a WHERE f1=5 UNION SELECT * FROM a WHERE f2=11; Or like this: SELECT * FROM a WHERE f1=5 UNION ALL SELECT * FROM a WHERE f2=11; But neither of those are equivalent to what we want. The only way I see to do this is to rewrite the original query as follows: SELECT * FROM a WHERE rowid IN (SELECT rowid FROM a WHERE f1=5 UNION SELECT rowid FROM a WHERE f2=11); Perhaps SQLite will do such rewriting automatically someday, but probably not in the near term. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Does SQLite have a fulltext search like MySQL?
On 7/5/2005 at 17:48 Michael Grice wrote: If not, are there plans to add this? I'm just a NOOB, looking to see what all SQLite can do for me, before I go too far down this road. I was on vacation last week... For full text search I find the lucene/clucene http://lucene.apache.org/ http://sourceforge.net/projects/clucene/ to be good solutions. The licenses isn't quite as easy as sqlite, but they should be good enough for everyone. They don't do sql, but most people who want full text search want a google like interface, which is easy (almost trivial) to provide. For structured data sqlite is much better. For unstructured text lucene is the best I have found.
[sqlite] Error Messages
Sorry to bombard with so much email. A small request for later version of SQLite. Would it be possible to change this error message: logic error or missing database To: logic error, missing database, database read-only, or database directory read-only. I hope this is not unreasonable, it took me a long time to work out why I was getting this message. Kind regards, Ben Clewett.
Re: [sqlite] limiting database size
On 7/11/05, Ramon [EMAIL PROTECTED] wrote: I would like to know if there is a way to limit a sqlite database and then be able to change this limit later on? Stop inserting when it's full?
RE: [sqlite] Binding a column name?
Wait - what if AUTOVACUUM is set on the database, and I'm the only one doing inserts/deletes? Will I still need to sqlite3_prepare() my statements again if auto-vacuum is on? -Dave -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Sunday, July 10, 2005 4:12 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Binding a column name? On Sun, 2005-07-10 at 00:23 -0700, Brown, Dave wrote: That is what I do. But that also means I have to call sqlite_prepare() each time, instead of just once. I was originally hoping I could prepare() once and just bind. If another thread or process VACUUMs the database or creates a new table or makes any other structure changes to the database file, all of your prepared statements will be invalided and you will have to rerun sqlite3_prepare(). Since you generally have no control over when another process might VACUUM the database, you should always be prepared to rerun sqlite3_prepare() if necessary. This is true even if you are only running your SQL statement once and then finalizing it because another process might VACUUM and invalidate your statement in the very brief window of time between your calls to sqlite3_prepare() and sqlite3_step(). Your best bet it to use a wrapper class of some sort that automates the task of rerunning sqlite3_prepare() when necessary. -- D. Richard Hipp [EMAIL PROTECTED]
RE: [sqlite] Binding a column name?
-Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Sunday, July 10, 2005 6:01 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Binding a column name? On Sun, 2005-07-10 at 15:34 -0700, Tim McDaniel wrote: If another thread or process VACUUMs the database or creates a new table or makes any other structure changes to the database file, all of your prepared statements will be invalided and you will have to rerun sqlite3_prepare(). Since you generally have no control over when another process might VACUUM the database, you should always be prepared to rerun sqlite3_prepare() if necessary. This is true even if you are only running your SQL statement once and then finalizing it because another process might VACUUM and invalidate your statement in the very brief window of time between your calls to sqlite3_prepare() and sqlite3_step(). Your best bet it to use a wrapper class of some sort that automates the task of rerunning sqlite3_prepare() when necessary. Does sqlite store the SQL text passed into sqlite3_prepare? If not, then I assume this means that any time we use sqlite3_prepare, we should cache the SQL text in the wrapper in case we need to re-prepare it. Yes. The wrapper needs to keep the SQL text because SQLite does not. Along the same line, I suppose we have to cache all the bound parameters, since they will have to re-bound as well. You can do that. Or you can keep the old prepared statement around until after the new one is ready, then use the sqlite3_transfer_bindings() API to transfer all your bindings from the old to the new, then finalize the old. Is it possible to get the SQLITE_SCHEMA error after the first sqlite3_step call, while iterating throw the rows? No. SQLITE_SCHEMA will always appear immediately or not at all. -- Is there any advantage to using sqlite3_expired() vs just sqlite3_step(), since you have to check for SQLITE_SCHEMA anyway?
RE: [sqlite] Binding a column name?
On Mon, 2005-07-11 at 10:32 -0700, Brown, Dave wrote: Wait - what if AUTOVACUUM is set on the database, and I'm the only one doing inserts/deletes? Will I still need to sqlite3_prepare() my statements again if auto-vacuum is on? AUTOVACUUM does *not* invalidate prepared statements - ever. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] limiting database size
On 7/11/05, Ramon [EMAIL PROTECTED] wrote: I would like to know if there is a way to limit a sqlite database and then be able to change this limit later on? Stop inserting when it's full? I want to put a limit like 10mb then later on e able to change this database limit to 15mb if I want to, is this possible?
[sqlite] Ticket 923 status (missing quotes in 2.8.15 .dump...)
Hello, I recently had some troubles converting a database from v2.8.0 to v3.08 per the following commands (under Windows XP): sqlite ddg53.db .dump ddg53.dump sqlite3 ddg53.db3 .read ddg53.dump The dump of the 2.8.0 database did not enclose varchar fields containing numbers with leading zeros in single quotes. As a result of this, the import into 3.08 dropped the leading zeros on those fields. This is exactly the problem identified in ticket number 923. Does anyone have a status on this? Thank you. Shawn M. Downey MPR Associates 632 Plank Road, Suite 110 Clifton Park, NY 12065 518-371-3983 x3 (work) 860-508-5015 (cell)
Re: [sqlite] limiting database size
I want to put a limit like 10mb then later on e able to change this database limit to 15mb if I want to, is this possible? You could use auto_vacuum (so the data file shrinks when data is deleted) and check the file size so you know when it is full. l8r Sean
Re: [sqlite] limiting database size
sqlite is file-based. use your filesystems quota functionality. On 7/11/05, Ramon [EMAIL PROTECTED] wrote: On 7/11/05, Ramon [EMAIL PROTECTED] wrote: I would like to know if there is a way to limit a sqlite database and then be able to change this limit later on? Stop inserting when it's full? I want to put a limit like 10mb then later on e able to change this database limit to 15mb if I want to, is this possible? -- Cory Nelson http://www.int64.org
Re: [sqlite] beginnings with sqlite
When I launch the program with the Terminal, if I write: ./quickStart test.db select * from personne, all occurs well, I receives the data contained in the table personne. But if I write ./quickStart test select * from personne, therefore I the extention in the name of the data base does not put, I receives the same error as for XCode: SQL error: No such table: personne My guess is you now have 2 databases One called test.db containing the table personne And one called test containing no tables. SQlite doesn't care much about the file extension, it doesn't really have one it defaults to if you don't supply one Make sure your XCode loads test.db and see if that work
[sqlite] update statements inside select...case inside triggers
Hi All, I'm trying to do the following: CREATE TRIGGER trigger_name AFTER UPDATE ON table BEGIN SELECT CASE WHEN a_condition THEN UPDATE another_table SET a_field=a_value WHERE yet_another_condition WHEN another_conditon THEN UPDATE another_table SET a_field=another_value WHERE yet_another_condition END; END When I try to create the trigger it returns an error before update (or similar - sorry do not have access to my PC at the moment to get the exact error). Can sqlite3 do the above? I have worked around it using: CREATE TRIGGER... BEGIN UPDATE (SELECT CASE...) WHERE (SELECT CASE...); END But it is harder to read and perhaps less efficient. Thanks in advance, Si.
