Re: [sqlite] Increasing performance of joins with a group by clause?
John Machin wrote: On 19/08/2007 4:01 AM, Jef Driesen wrote: Suppose I have two related tables: CREATE TABLE events ( id INTEGER PRIMARY KEY NOT NULL, place_id INTEGER ); CREATE TABLE places ( id INTEGER PRIMARY KEY NOT NULL, name TEXT ); INSERT INTO places (id, name) VALUES (1, 'Place A'); INSERT INTO places (id, name) VALUES (2, 'Place B'); INSERT INTO places (id, name) VALUES (3, 'Place C'); INSERT INTO events (id, place_id) VALUES (1, 1); INSERT INTO events (id, place_id) VALUES (2, 2); INSERT INTO events (id, place_id) VALUES (3, 1); Now, I want to count the number of 'events' at every 'place'. I started with a simple join and a group by clause to perform the count: SELECT name, count (*) AS count FROM events LEFT JOIN places ON places.id = events.place_id GROUP BY events.place_id; name|count Place A|2 Place B|1 It executes very fast (because the join can take advantage of the primary key) but does not produce the desired result. As you can see, not all places are included in the result: So I changed swapped the tables in the join: SELECT name, count (events.place_id) AS count FROM places LEFT JOIN events ON events.place_id = places.id GROUP BY places.id; name|count Place A|2 Place B|1 Place C|1 Folks are dumb where I come from; can someone please explain how this could be correct? The INSERT statements create 2 events at place_id 1 (A), 1 event at place_id 2 (B) and *ZERO* events at place_id 3 (C). Please pardon me if this is strange behaviour (I'm new to this mailing list) but I actually *ran* the OP's code, with this result: C:\junk>sqlite3 junk.sq3 SQLite version 3.4.2 Enter ".help" for instructions sqlite> .read outer_join_query.sql --- query 1 --- Place A|2 Place B|1 --- query 2 --- Place A|2 Place B|1 Place C|0 --- query 3 --- Place A|2 Place B|1 Place C|0 The results in my original post are indeed wrong! Initially I started with some random insert statements for the events table, but they happened to be a bad choice to illustrate my problem. So I changed them, but forgot to update the results for the second query... - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Increasing performance of joins with a group by clause?
Suppose I have two related tables: CREATE TABLE events ( id INTEGER PRIMARY KEY NOT NULL, place_id INTEGER ); CREATE TABLE places ( id INTEGER PRIMARY KEY NOT NULL, name TEXT ); INSERT INTO places (id, name) VALUES (1, 'Place A'); INSERT INTO places (id, name) VALUES (2, 'Place B'); INSERT INTO places (id, name) VALUES (3, 'Place C'); INSERT INTO events (id, place_id) VALUES (1, 1); INSERT INTO events (id, place_id) VALUES (2, 2); INSERT INTO events (id, place_id) VALUES (3, 1); Now, I want to count the number of 'events' at every 'place'. I started with a simple join and a group by clause to perform the count: SELECT name, count (*) AS count FROM events LEFT JOIN places ON places.id = events.place_id GROUP BY events.place_id; name|count Place A|2 Place B|1 It executes very fast (because the join can take advantage of the primary key) but does not produce the desired result. As you can see, not all places are included in the result: So I changed swapped the tables in the join: SELECT name, count (events.place_id) AS count FROM places LEFT JOIN events ON events.place_id = places.id GROUP BY places.id; name|count Place A|2 Place B|1 Place C|1 With this query, the correct result is returned, but the query runs much slower. Probably because the join cannot use the primary key (according to 'explain query plan', it is used for the sorting of the group by clause now). Adding an index improves the speed CREATE INDEX events_idx_place_id ON events (place_id); Is there a difference between using a primary key or another index for a join? And what is the advantage (if there is one) over not using a join at all: SELECT name, (SELECT count (*) FROM events WHERE events.place_id = places.id) AS count FROM places; - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] When to use SQLITE_STATIC and SQLITE_TRANSIENT?
When using sqlite3_bind_text() (or a similar function) with SQLITE_STATIC, how long does the pointer have to remain valid? As long as the sqlite3_stmt is not finalized? It is clear to me the following will cause no problem: sqlite3_bind_text (stmt, column, "some text", -1, SQLITE_STATIC); But what about this: void some_func (const char* text) { sqlite3_stmt *stmt = 0; sqlite3_prepare_v2 (db, sql, -1, &stmt, NULL); sqlite3_bind_text (stmt, column, text, -1, SQLITE_STATIC); sqlite3_step (stmt); ... sqlite3_finalize (stmt); } In this case the pointer remains unchanged as long as the statement is valid, but it can change afterwards. Is that a problem and should I use SQLITE_TRANSIENT here? - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Can an autogenerated integer primary key become negative or zero?
Suppose I have a simple table with an integer primary key: CREATE TABLE table ( id INTEGER PRIMARY KEY NOT NULL, name TEXT ); and insert values without supplying a value for the primary key: INSERT INTO table (name) VALUES (@name); Is it guaranteed that the primary key is always positive? In my application code, I would like to reserve negative values (and possibly zero) for special meanings, but that is only possible if they never appear in the database. In the documentation [1], I read the primary key is a signed integer, so it can hold negative numbers. But the autoincrement algorithm starts counting from 1 and thus the primary key should never become negative or zero. Or is that not true? [1] http://www.sqlite.org/autoinc.html - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: sqlite_finalize() releases resources?
Dan Kennedy wrote: On Wed, 2007-07-04 at 09:58 +0200, Jef Driesen wrote: Igor Tandetnik wrote: Mario Figueiredo wrote: I'm a tad bit confused with sqlite3_finalize() usage when the query fails. As it is, I'm assuming it releases resources and I use it in this context: rc = sqlite3_prepare_v2(/* ... */); if (rc != SQLITE_OK) { sqlite3_finalize(stmt); /* ... */ } This doesn't make any sense. If prepare fails, you do not have a valid statement handle to call finalize on. Are you sure about that? The documentation for sqlite3_prepare_v2 says: Igor is, as usual, correct. The situation in 3.4.0 is that if sqlite3_prepare() returns other than SQLITE_OK, you are guaranteed that *ppStmt is set to NULL. You may call sqlite3_finalize() on this if you wish - it's a no-op. Historically, it may have been that *ppStmt was sometimes left uninitialized if an error occured (hence the "may" in the docs). What do you mean with uninitialized? Leaving the pointer unchanged, or pointing to some memory that is already freed or still needs to be freed? This is important if you need to support older versions. I suppose you mean the first one, but I'm asking anyway just to be sure. In my code, I always initialize pointers to NULL, so this case would not cause any problems at all. The second case is a completely different story of course. Anyway, the code I'm using should be fine in all cases: sqlite3_stmt *stmt = 0; #if SQLITE_VERSION_NUMBER >= 3003009 int rc = sqlite3_prepare_v2 (db, sql, nbytes, &stmt, tail); #else int rc = sqlite3_prepare (db, sql, nbytes, &stmt, tail); #endif if (rc != SQLITE_OK && stmt != 0) { sqlite3_finalize (stmt); stmt = 0; } This is in contrast to sqlite3_open(). You must call sqlite3_close(), even if sqlite3_open() returned an error code. I know. I had a topic on that a few months ago [1]. [1] http://www.mail-archive.com/sqlite-users@sqlite.org/msg21324.html - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: sqlite_finalize() releases resources?
Igor Tandetnik wrote: Mario Figueiredo wrote: I'm a tad bit confused with sqlite3_finalize() usage when the query fails. As it is, I'm assuming it releases resources and I use it in this context: rc = sqlite3_prepare_v2(/* ... */); if (rc != SQLITE_OK) { sqlite3_finalize(stmt); /* ... */ } This doesn't make any sense. If prepare fails, you do not have a valid statement handle to call finalize on. Igor Tandetnik Are you sure about that? The documentation for sqlite3_prepare_v2 says: *ppStmt is left pointing to a compiled SQL statement structure that can be executed using sqlite3_step(). Or if there is an error, *ppStmt may be set to NULL. If the input text contained no SQL (if the input is and empty string or a comment) then *ppStmt is set to NULL. The calling procedure is responsible for deleting the compiled SQL statement using sqlite3_finalize() after it has finished with it. I also thought this means the statement has to be deleted with sqlite3_finalize, even when sqlite3_prepare_v2 failed (except for the case where NULL is returned). Notice the "may be set to NULL" in the documentation. Doesn't a non-NULL value indicate some memory was allocated and thus need to be freed? - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: sqlite3_reset (or sqlite3_finalize) and error reporting?
Scott McDonald wrote: Jef Driesen wrote: Scott McDonald wrote: Jef Driesen wrote: I have some questions on the usage of sqlite3_reset (or sqlite3_finalize) after sqlite3_step. In the legacy interface I use sqlite3_reset after sqlite3_step to obtain a more specific error code for SQLITE_ERROR (to be able to detect schema errors and automatically reprepare the statement, like the v2 interface does): int myprepare(sqlite3* db, const char* sql, int nbytes, sqlite3_stmt** stmt, const char** tail) { #ifdef USE_LEGACY int rc = sqlite3_prepare (db, sql, nbytes, stmt, tail); #else int rc = sqlite3_prepare_v2 (db, sql, nbytes, stmt, tail); #endif if (rc != SQLITE_OK && *stmt != 0) { sqlite3_finalize (*stmt); *stmt = 0; } } int mystep(sqlite3_stmt* stmt) { int rc = sqlite3_step (stmt); #ifdef USE_LEGACY if (rc == SQLITE_ERROR) rc = sqlite3_reset (stmt); #endif return rc; } This works well, but now I also want to report an appropriate error message to the user (by throwing an exception). But I'm having some problems with that. In some cases, the correct errcode and errmsg (from the sqlite3_errcode and sqlite3_errmsg functions) can be obtained directly after calling sqlite3_step, but sometimes sqlite3_reset is required because sqlite3_step only returns a generic error. My idea was now to always use sqlite3_reset (see example results below): int mystep(sqlite3_stmt* stmt) { int rc = sqlite3_step (stmt); if (rc != SQLITE_DONE && rc != SQLITE_ROW) rc = sqlite3_reset (stmt); return rc; } This also makes my code behave the same for both the legacy and the v2 interface, since I have to use sqlite3_reset anyway in the legacy interface (except for the few return codes that are reported directly). This works well in most cases, but as you can see from the results below, I can't get a correct error message for SQLITE_MISUSE. Now my questions are: 1. How do I retrieve the errmsg for SQLITE_MISUSE? Are there any other codes with this problem? Maybe it's worth adding a function to obtain the errmsg from an error code (e.g. not only the most recent one). 2. Is it normal that sometimes the rc value is different from the errcode (and its associated errmsg)? The documentation for sqlite3_errcode seems to suggest that this should not happen. Sample output (in the format "function: rc, errcode, errmsg") for a few errors: SQLITE_CONSTRAINT (legacy) sqlite3_step: 1, 1, SQL logic error or missing database sqlite3_reset: 19, 19, column is not unique SQLITE_CONSTRAINT (v2) sqlite3_step: 19, 1, SQL logic error or missing database sqlite3_reset: 19, 19, column is not unique SQLITE_BUSY (legacy and v2) sqlite3_step: 5, 5, database is locked sqlite3_reset: 5, 5, database is locked SQLITE_MISUSE (legacy and v2) sqlite3_step: 21, 0, not an error sqlite3_reset: 0, 0, not an error I was getting similar results, for me I noticed this behavior with a CONSTRAINT failure was getting a 19 return code but in my logging for this I use the error code and error message API and was getting 1 and "SQL logic or missing database" or something like that. On the "sqlite_finalize" I would get a 19 return code and in my logging would get a 19 error code and error message of "PRIMARY KEY must be unique" - this is what I would expect after calling "sqlite_step" not after calling "sqlite_finalize" - this is like the legacy behavior you mentioned, etc. In the v2 interface, the return code is always reported immediately, without the need to call sqlite3_reset or sqlite3_finalize. This is explained in the documentation. So far no problem, but I noticed the functions sqlite3_errcode and sqlite3_errmsg are always behaving as they did with the legacy interface. The correct error code and error message is only returned after calling sqlite3_reset or sqlite3_finalize. But this is something that is *NOT* mentioned in the documentation at all. As it turns out I'm not the only one who finds this really confusing, so this should really be added to the documentation. I noticed you took it a couple of steps further and analyzed other possible "prepared statement" errors in this area, nice work... Just wondering if you got any resolution on this as it doesn't feel "clean" to me - I would like my logging statements to actually provide some useful information when the error occurs, etc. You can get the useful error information from sqlite3_step after calling sqlite3_reset (no matter which interface you used to prepare the statement). But as I found out, this does not work for SQLITE_MISUSE (and maybe others?). In this case I never get an appropriate error message (I always get "not an error") and the error code is even lost after calling sqlite3_reset! I'm not aware of a solution for this problem. Thanks Jef for responding.
[sqlite] Re: sqlite3_reset (or sqlite3_finalize) and error reporting?
Scott McDonald wrote: Jef Driesen wrote: I have some questions on the usage of sqlite3_reset (or sqlite3_finalize) after sqlite3_step. In the legacy interface I use sqlite3_reset after sqlite3_step to obtain a more specific error code for SQLITE_ERROR (to be able to detect schema errors and automatically reprepare the statement, like the v2 interface does): int myprepare(sqlite3* db, const char* sql, int nbytes, sqlite3_stmt** stmt, const char** tail) { #ifdef USE_LEGACY int rc = sqlite3_prepare (db, sql, nbytes, stmt, tail); #else int rc = sqlite3_prepare_v2 (db, sql, nbytes, stmt, tail); #endif if (rc != SQLITE_OK && *stmt != 0) { sqlite3_finalize (*stmt); *stmt = 0; } } int mystep(sqlite3_stmt* stmt) { int rc = sqlite3_step (stmt); #ifdef USE_LEGACY if (rc == SQLITE_ERROR) rc = sqlite3_reset (stmt); #endif return rc; } This works well, but now I also want to report an appropriate error message to the user (by throwing an exception). But I'm having some problems with that. In some cases, the correct errcode and errmsg (from the sqlite3_errcode and sqlite3_errmsg functions) can be obtained directly after calling sqlite3_step, but sometimes sqlite3_reset is required because sqlite3_step only returns a generic error. My idea was now to always use sqlite3_reset (see example results below): int mystep(sqlite3_stmt* stmt) { int rc = sqlite3_step (stmt); if (rc != SQLITE_DONE && rc != SQLITE_ROW) rc = sqlite3_reset (stmt); return rc; } This also makes my code behave the same for both the legacy and the v2 interface, since I have to use sqlite3_reset anyway in the legacy interface (except for the few return codes that are reported directly). This works well in most cases, but as you can see from the results below, I can't get a correct error message for SQLITE_MISUSE. Now my questions are: 1. How do I retrieve the errmsg for SQLITE_MISUSE? Are there any other codes with this problem? Maybe it's worth adding a function to obtain the errmsg from an error code (e.g. not only the most recent one). 2. Is it normal that sometimes the rc value is different from the errcode (and its associated errmsg)? The documentation for sqlite3_errcode seems to suggest that this should not happen. Sample output (in the format "function: rc, errcode, errmsg") for a few errors: SQLITE_CONSTRAINT (legacy) sqlite3_step: 1, 1, SQL logic error or missing database sqlite3_reset: 19, 19, column is not unique SQLITE_CONSTRAINT (v2) sqlite3_step: 19, 1, SQL logic error or missing database sqlite3_reset: 19, 19, column is not unique SQLITE_BUSY (legacy and v2) sqlite3_step: 5, 5, database is locked sqlite3_reset: 5, 5, database is locked SQLITE_MISUSE (legacy and v2) sqlite3_step: 21, 0, not an error sqlite3_reset: 0, 0, not an error I was getting similar results, for me I noticed this behavior with a CONSTRAINT failure was getting a 19 return code but in my logging for this I use the error code and error message API and was getting 1 and "SQL logic or missing database" or something like that. On the "sqlite_finalize" I would get a 19 return code and in my logging would get a 19 error code and error message of "PRIMARY KEY must be unique" - this is what I would expect after calling "sqlite_step" not after calling "sqlite_finalize" - this is like the legacy behavior you mentioned, etc. In the v2 interface, the return code is always reported immediately, without the need to call sqlite3_reset or sqlite3_finalize. This is explained in the documentation. So far no problem, but I noticed the functions sqlite3_errcode and sqlite3_errmsg are always behaving as they did with the legacy interface. The correct error code and error message is only returned after calling sqlite3_reset or sqlite3_finalize. But this is something that is *NOT* mentioned in the documentation at all. As it turns out I'm not the only one who finds this really confusing, so this should really be added to the documentation. I noticed you took it a couple of steps further and analyzed other possible "prepared statement" errors in this area, nice work... Just wondering if you got any resolution on this as it doesn't feel "clean" to me - I would like my logging statements to actually provide some useful information when the error occurs, etc. You can get the useful error information from sqlite3_step after calling sqlite3_reset (no matter which interface you used to prepare the statement). But as I found out, this does not work for SQLITE_MISUSE (and maybe others?). In this case I never get an appropriate error message (I always get "not an error") and the error code is even lost after calling sqlite3_reset! I'm not aware of a solution for this problem. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Can I execute queries from an sqlite3_update_hook callback function?
Igor Tandetnik wrote: Jef Driesen wrote: I'm trying to execute a query from the callback function that is registered with sqlite3_update_hook. But sqlite3_prepare_v2 always returns SQLITE_MISUSE. Is it not allowed to execute queries from the callback function? I'm was trying to use the callback function as a notification mechanism to update my GUI. Whenever I receive a notification on an insert/delete, I want to retrieve the new/modified row and update the displayed data. Post yourself a message from inside the hook, update UI from that message's handler. Most UI frameworks I know of have a concept of a message or event queue to which you can post user-defined events. I'm using gtk+ (actually the C++ bindings gtkmm) and tried to use g_idle_add from inside the hook. That approach seems to work in my first tests, but I was wondering if there is a possibility the idle callback is executed before sqlite is finished with the query? - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Can I execute queries from an sqlite3_update_hook callback function?
I'm trying to execute a query from the callback function that is registered with sqlite3_update_hook. But sqlite3_prepare_v2 always returns SQLITE_MISUSE. Is it not allowed to execute queries from the callback function? I'm was trying to use the callback function as a notification mechanism to update my GUI. Whenever I receive a notification on an insert/delete, I want to retrieve the new/modified row and update the displayed data. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Some questions on hierarchical data (nested set model)
A. Pagaltzis wrote: * Jef Driesen <[EMAIL PROTECTED]> [2007-04-11 16:10]: I managed to solve this problem now. I had to write my solution (see below) in pseudo sql code (e.g some extra non-sql code was required) because sqlite does not support stored procedures. It think it is doable to incorporate the IF/THEN/ELSE inside the sql query, but I didn't try to do that. thanks for posting that. I rewrote your code a little because single-letter variable names make code hard to read; this version should make it more obvious what’s actually being computed. Also, I made the conditionals more restrictive, so that the code will not silently mangle data if you ask it to move a node onto itself or under one of its own descendants. IF @src_lft < @dst_lft AND @src_lft < @dst_rgt THEN direction = 1; affected_lft = @src_lft; displaced_lft = @src_rgt + 1; displaced_rgt = @dst_rgt - 1; affected_rgt = @dst_rgt - 1; ELSIF @src_lft > @dst_lft THEN direction = -1; affected_lft = @dst_rgt; displaced_lft = @dst_rgt; displaced_rgt = @src_lft - 1; affected_rgt = @src_rgt; ELSE THROW "Illegal move" END IF; src_move_offset = @direction * (@displaced_rgt - @displaced_lft + 1); displace_width = [EMAIL PROTECTED] * (@src_rgt - @src_lft + 1); UPDATE tree SET lft = CASE WHEN lft BETWEEN @src_lft AND @src_rgt THEN lft + @src_move_offset ELSE lft + @displace_width END WHERE lft BETWEEN @affected_lft AND @affected_rgt; UPDATE tree SET rgt = CASE WHEN rgt BETWEEN @src_lft AND @src_rgt THEN rgt + @src_move_offset ELSE rgt + @displace_width END WHERE rgt BETWEEN @affected_lft AND @affected_rgt; Your version is indeed easier to understand then mine. (I started mine from a drawing on paper. And at that time, I had no idea about the meaning of the final variables, so I used a letter for each new variable.) Now that we are talking about names, affected_lft/rgt and displaced_lft/rgt do not always correspond to lft and rgt values. A better choice would be 'first' and 'last'. But what's in a name ;-) Anyway that's not the reason why I'm writing this. I think your conditionals are incorrect (and mine were incorrect too). It is best explained with an example image [1]. Moving "Plasma" (lft=7, rgt=8) under "Televisions" (lft=2, rgt=9) should do nothing, since it is already in place, but your code attempts to move it in the wrong direction! [1] http://dev.mysql.com/tech-resources/articles/hierarchical-data.html If I didn't make a mistake, there are 3 special cases: A. dst is inside src (dst_rgt between src_lft and src_rgt): move is illegal B. src and dst are equal (src_lft = dst_lft and src_rgt = dst_rgt): move is not strictly illegal, but more like a null operation. C. src is a direct child of dst: technically the src is already in place and this is also a null operation. But this situation is difficult to detect properly (without additional queries). And performing the move anyway has the nice effect that src is moved to the end of the children of dst. That makes the behavior consistent with the idea of a delete followed by an insert. The only exception is when src is already at the end (src_rgt = dst_rgt-1), which is easy to detect. This means I now have: IF @dst= @src THEN RETURN; END IF; IF @dst_rgt = @src_rgt + 1 THEN RETURN; END IF; IF @dst_rgt BETWEEN @src_lft AND @src_rgt THEN THROW "Illegal move"; END IF; IF @src_rgt < @dst_rgt THEN direction = 1; affected_lft = @src_lft; displaced_lft = @src_rgt + 1; displaced_rgt = @dst_rgt - 1; affected_rgt = @dst_rgt - 1; ELSE direction = -1; affected_lft = @dst_rgt; displaced_lft = @dst_rgt; displaced_rgt = @src_lft - 1; affected_rgt = @src_rgt; END IF; And the rest remains the same. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite3_reset (or sqlite3_finalize) and error reporting?
I have some questions on the usage of sqlite3_reset (or sqlite3_finalize) after sqlite3_step. In the legacy interface I use sqlite3_reset after sqlite3_step to obtain a more specific error code for SQLITE_ERROR (to be able to detect schema errors and automatically reprepare the statement, like the v2 interface does): int myprepare(sqlite3* db, const char* sql, int nbytes, sqlite3_stmt** stmt, const char** tail) { #ifdef USE_LEGACY int rc = sqlite3_prepare (db, sql, nbytes, stmt, tail); #else int rc = sqlite3_prepare_v2 (db, sql, nbytes, stmt, tail); #endif if (rc != SQLITE_OK && *stmt != 0) { sqlite3_finalize (*stmt); *stmt = 0; } } int mystep(sqlite3_stmt* stmt) { int rc = sqlite3_step (stmt); #ifdef USE_LEGACY if (rc == SQLITE_ERROR) rc = sqlite3_reset (stmt); #endif return rc; } This works well, but now I also want to report an appropriate error message to the user (by throwing an exception). But I'm having some problems with that. In some cases, the correct errcode and errmsg (from the sqlite3_errcode and sqlite3_errmsg functions) can be obtained directly after calling sqlite3_step, but sometimes sqlite3_reset is required because sqlite3_step only returns a generic error. My idea was now to always use sqlite3_reset (see example results below): int mystep(sqlite3_stmt* stmt) { int rc = sqlite3_step (stmt); if (rc != SQLITE_DONE && rc != SQLITE_ROW) rc = sqlite3_reset (stmt); return rc; } This also makes my code behave the same for both the legacy and the v2 interface, since I have to use sqlite3_reset anyway in the legacy interface (except for the few return codes that are reported directly). This works well in most cases, but as you can see from the results below, I can't get a correct error message for SQLITE_MISUSE. Now my questions are: 1. How do I retrieve the errmsg for SQLITE_MISUSE? Are there any other codes with this problem? Maybe it's worth adding a function to obtain the errmsg from an error code (e.g. not only the most recent one). 2. Is it normal that sometimes the rc value is different from the errcode (and its associated errmsg)? The documentation for sqlite3_errcode seems to suggest that this should not happen. Sample output (in the format "function: rc, errcode, errmsg") for a few errors: SQLITE_CONSTRAINT (legacy) sqlite3_step: 1, 1, SQL logic error or missing database sqlite3_reset: 19, 19, column is not unique SQLITE_CONSTRAINT (v2) sqlite3_step: 19, 1, SQL logic error or missing database sqlite3_reset: 19, 19, column is not unique SQLITE_BUSY (legacy and v2) sqlite3_step: 5, 5, database is locked sqlite3_reset: 5, 5, database is locked SQLITE_MISUSE (legacy and v2) sqlite3_step: 21, 0, not an error sqlite3_reset: 0, 0, not an error - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: SQLite and nested transactions
Dennis Cote wrote: Jef Driesen wrote: I can give you the example of an application using sqlite as the on-disk file format. As mentioned on the sqlite website [1], the traditional File/Open operation does an sqlite3_open() and executes a BEGIN TRANSACTION. File/Save does a COMMIT followed by another BEGIN TRANSACTION. That would be the parent transaction. Imagine now the application needs to execute a group of sql statements that needs to be atomic. Some examples that come to my mind are importing data, re-arranging existing data,... To guarantee the entire operation is atomic, I want to group them in a child transaction. But when this operation fails for some reason (because of invalid data, a violated constraint,...), only the child transaction needs to rollback. Because changes prior to this child transaction should remain intact and the application can still continue because the database remains in a clean state. Without nested transactions, I have to make a compromise by: (a) not using a parent transaction and loosing the File/Save feature. (b) not using a child transaction and running into the risk of leaving inconsistent data in the database after an error or having to throw away all changes after an error. [1] http://www.sqlite.org/whentouse.html While this is another example of where a savepoint mechanism could be useful, it is not necessary. Another solution to your dilemma is given in the second paragraph of the application file format description on that web page. You use the database to store an undo log. You can then undo the changes made since the beginning of your "pseudo transaction" in the event of a error during a multiple statement change. Since you are probably going to have an undo/redo mechanism anyway, this adds little or no additional work. You simply mark all statements in a "pseudo transaction" with the same transaction number in the undo log, and if an error occurs you undo all the statements already logged with that transaction number. Of course there are also other ways of handling this such as swapping files on open and save rather than using transactions, so that real transactions can be used to update the active file atomically. It is certainly possible to do what I described without nested transactions. But those solutions are far more complicated and less elegant than using nested transactions. For instance the undolog approach needs three triggers (insert/delete/update) for every single table, a method to maintain a "pseudo transaction level", executing the items in the undolog and removing them afterwards,... With nested transaction only two SQL statements are required: BEGIN and COMMIT (or ROLLBACK in the case of a problem). Very simple and thus less chance of making mistakes. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: SQLite and nested transactions
Dennis Cote wrote: [EMAIL PROTECTED] wrote: It appears that my requirements are to be able to do the following: BEGIN parent; insert into t values ('a'); BEGIN child; insert into t values ('b'); insert into t values ('c'); ROLLBACK child; // child aborts insert into t values ('d'); COMMIT parent; As a result of this sequence, the table should have two new rows with values 'a' and 'd', but not 'b' and 'c'. Can you explain why your application is rolling back the child transaction? If the above is really how your application works (and I don't think it is), then the exact same result can always be achieved with the simpler sequence: BEGIN; insert into t values ('a'); insert into t values ('d'); COMMIT; You don't need to bother inserting b and c if you are going to undo those insertions with a static rollback. I can give you the example of an application using sqlite as the on-disk file format. As mentioned on the sqlite website [1], the traditional File/Open operation does an sqlite3_open() and executes a BEGIN TRANSACTION. File/Save does a COMMIT followed by another BEGIN TRANSACTION. That would be the parent transaction. Imagine now the application needs to execute a group of sql statements that needs to be atomic. Some examples that come to my mind are importing data, re-arranging existing data,... To guarantee the entire operation is atomic, I want to group them in a child transaction. But when this operation fails for some reason (because of invalid data, a violated constraint,...), only the child transaction needs to rollback. Because changes prior to this child transaction should remain intact and the application can still continue because the database remains in a clean state. Without nested transactions, I have to make a compromise by: (a) not using a parent transaction and loosing the File/Save feature. (b) not using a child transaction and running into the risk of leaving inconsistent data in the database after an error or having to throw away all changes after an error. [1] http://www.sqlite.org/whentouse.html - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Some questions on hierarchical data (nested set model)
Jef Driesen wrote: I want to store a tree in an sqlite database. My first choice was the adjacency list model: CREATE TABLE tree ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, parent_id INTEGER ); But this method requires multiple queries to display the entire tree (or a subtree) in my GUI (a gtk+ treeview). Because childs can only be added to the treeview if all its parents are already added. But then I found some resources on the nested set model [1,2]: CREATE TABLE tree ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, lft INTEGER, rgt INTEGER ); Retrieving a (sub)tree can be done with only one sql query, at the expense of more complex queries to add or remove rows. Because all lft and rgt values to the right of the node have to be modified. [1] http://www.sitepoint.com/article/hierarchical-data-database [2] http://dev.mysql.com/tech-resources/articles/hierarchical-data.html I start to understand this model, but I still have some questions: Q3. How do I move a node (or subtree)? In the adjacency list model, this is extremely easy by pointing the parent_id to another node. But I don't know how to do that in the nested set model. I managed to solve this problem now. I had to write my solution (see below) in pseudo sql code (e.g some extra non-sql code was required) because sqlite does not support stored procedures. It think it is doable to incorporate the IF/THEN/ELSE inside the sql query, but I didn't try to do that. a = @source_lft; b = @source_rgt; IF @source_lft < @target_lft THEN c = @b + 1; d = @target_rgt - 1; v = (@d - @c + 1) = @target_rgt - (@b + 1); w = -(@b - @a + 1); e = @a; f = @d; ELSE c = @target_rgt; d = @a - 1; v = -(@d - @c + 1) = @target_rgt - @a w = (@b - @a + 1); e = @c; f = @b; END IF; UPDATE tree SET lft = lft + CASE WHEN lft BETWEEN @a AND @b THEN @v /* Move the subtree up/down */ ELSE @w /* Make room for the subtree under the new parent */ END WHERE lft BETWEEN @e AND @f; UPDATE tree SET rgt = rgt + CASE WHEN rgt BETWEEN @a AND @b THEN @v /* Move the subtree up/down */ ELSE @w /* Make room for the subtree under the new parent */ END WHERE rgt BETWEEN @e AND @f; - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Some questions on hierarchical data (nested set model)
A. Pagaltzis wrote: Hi Jef, * Jef Driesen [2007-04-06 11:20]: Q1. Which is more efficient? Two simple queries or one self join? I have seen two different types of queries to retrieve a tree. The first one uses two very simple queries: SELECT lft, rgt FROM tree WHERE name = @name; SELECT * FROM tree WHERE lft BETWEEN @lft AND @rgt ORDER BY lft ASC; The first query is only required to retrieve the lft and rgt values of the node. The other type uses a self join (which I assume is more expensive), but no extra query is required: SELECT node.* FROM tree AS node, tree AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.name = @name ORDER BY node.lft; Which type of query is more efficient? I’d say just measure it. Another way to write this, possibly cheaper than the full-monty join in your second query, is to join on a single-row subquery: SELECT child.* FROM tree AS child, (SELECT lft, rgt FROM tree WHERE name = @name) AS boundary WHERE child.lft BETWEEN boundary.lft AND boundary.rgt ORDER BY child.lft ASC; However, this could actually be a disimprovement. As always, if you guess at the performance of any piece of code, you are guaranteed to be wrong; profile, profile, profile and profile again. Personally, I prefer this variant over the join you showed simply because I find it much more obvious what’s going on. I know profiling is the only way to be 100% sure about performance. But i was only asking some more general advice, since I have little experience with sql(ite) an thus no idea what type of queries are more expensive than others. I'll test with your variant too. Q3. How do I move a node (or subtree)? In the adjacency list model, this is extremely easy by pointing the parent_id to another node. But I don't know how to do that in the nested set model. This is pretty complex. I wrote a procedure once to move a single node: If the node should... -- ...become a sibling to the left of the target node: SELECT lft FROM categories WHERE name = @target_name -- ...become a sibling to the right of the target node: SELECT rgt + 1 FROM categories WHERE name = @target_name -- ...become the first child of the target node: SELECT lft + 1 FROM categories WHERE name = @target_name -- ...become the last child of the target node: SELECT rgt FROM categories WHERE name = @target_name Now you have your new `lft` value. With it, you can perform the desired update. The fourth option is what I want. First, you make room for the node at the target location: UPDATE tree SET rgt = rgt + 2 WHERE rgt >= @lft ORDER BY rgt DESC UPDATE tree SET lft = lft + 2 WHERE lft >= @lft ORDER BY lft DESC Note that I had to split this up in two separate queries because I have UNIQUE constraints on `lft` and `rgt` and MySQL failed half-way into the query if any one row failed the constraint; very annoying. The ORDER BY clauses are necessary to keep MySQL from tripping over itself. I noticed this too. But sqlite does not seem to support the ORDER BY when doing an update. I assume that most other database engines would be able to check constraints only at the end of a transaction. After all, Celko writes this update as a single query with CASEs. Hopefully I’ll be able to do have the query that way on Postgres once I’m done with the migration. Anyway, after all that, you can finally move the desired node to the space at the target: UPDATE tree SET lft = @lft, rgt = @lft + 1 WHERE name = @source_name However, as it should be pretty obvious, this only moves a single node – the subtree below this node does not tag along for the journey. Due to the nature of nested sets, it becomes re-parented to the parent of the moved node. I need to be able to move the entire subtree for my application (e.g. drag and drop in a treeview). I have always meant to go back and change the queries as necessary to move an entire subtree, but I’ve yet to get around to it. Basically, what would be necessary is: • Change from a fixed amount of 2 when making room at the target location (which is enough to make room for a single node), to instead be the difference between the `lft` and `rgt` values of the source node. • Modify the WHERE clause and calculations in the final UPDATE so it moves an entire tree, not just a single node. It shouldn’t be hard, it just takes a bit of concentration to get all the cogs in the queries just so. I'm already working on a version to move an entire subtree and it's almost finished. In the meantime, I also found an implementation for postgres [1]. The math is a little different (because they move to the first child of the target node, instead of the last child), but the idea is the same. It also made me realize the math is different depending on the direction of the move. I also found another article [2] that has some pseudo code for moving a subtree. [1] http://archives.postgresql.org/
[sqlite] Re: Some questions on hierarchical data (nested set model)
Dennis Cote wrote: Jef Driesen wrote: I want to store a tree in an sqlite database. My first choice was the adjacency list model: CREATE TABLE tree ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, parent_id INTEGER ); But this method requires multiple queries to display the entire tree (or a subtree) in my GUI (a gtk+ treeview). Because childs can only be added to the treeview if all its parents are already added. But then I found some resources on the nested set model [1,2]: CREATE TABLE tree ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, lft INTEGER, rgt INTEGER ); Retrieving a (sub)tree can be done with only one sql query, at the expense of more complex queries to add or remove rows. Because all lft and rgt values to the right of the node have to be modified. [1] http://www.sitepoint.com/article/hierarchical-data-database [2] http://dev.mysql.com/tech-resources/articles/hierarchical-data.html I have found an augmented adjacency list which stores a path through the tree to each node to be very effective. I posted a sample implementation on the list previously at http://article.gmane.org/gmane.comp.db.sqlite.general/17286/match=tree (follow the nabble link for more context). I'll take a look at it... I start to understand this model, but I still have some questions (especially Q3): Q1. Which is more efficient? Two simple queries or one self join? I have seen two different types of queries to retrieve a tree. The first one uses two very simple queries: SELECT lft, rgt FROM tree WHERE name = @name; SELECT * FROM tree WHERE lft BETWEEN @lft AND @rgt ORDER BY lft ASC; The first query is only required to retrieve the lft and rgt values of the node. The other type uses a self join (which I assume is more expensive), but no extra query is required: SELECT node.* FROM tree AS node, tree AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.name = @name ORDER BY node.lft; Which type of query is more efficient? Retrieving the path to a node is very similar: SELECT * FROM tree WHERE lft <= @lft AND rgt >= @rgt ORDER BY lft ASC; or SELECT parent.* FROM tree AS node, tree AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.name = @name ORDER BY parent.lft; There is probably not a lot of difference assuming your calling sqlite from an efficient programming language. I ssupect the join may be slightly faster, but you would have to measure both cases to find out for sure. I'm using the C/C++ interface. I know I have to measure to be really sure, but since I'm not very experienced with SQL, I have no idea which types of queries are usually faster than others. My first thought was that selecting a subset of a table (1st SQL statement) would be more simple and thus faster than doing the join (2nd SQL statement). But I could be wrong of course. Q2. Which indices should I use to make my queries more efficient? I think your best bet would be a compound index on lft and rgt. Why would a compound index be better than a single index? I thought a compound index is mainly used to order by the second column (rgt) only when the first column (lft) has duplicates. But the lft (and rgt) columns are unique in the nested set model. Q3. How do I move a node (or subtree)? In the adjacency list model, this is extremely easy by pointing the parent_id to another node. But I don't know how to do that in the nested set model. I have no idea. I think I found a way to do it. But it's not finished yet. Q4. sqlite parameter binding for multiple queries? For some operations (like deleting a node) I need multiple queries: DELETE FROM tree WHERE lft BETWEEN @lft AND @rgt; UPDATE tree SET rgt = rgt - (@rgt - @lft + 1) WHERE rgt > @rgt; UPDATE tree SET lft = lft - (@rgt - @lft + 1) WHERE lft > @rgt; and they all need the same parameters (@lft and @rgt). Do I have to prepare each statement separately and bind the parameters every time? Or is it possible to bind the parameters only once (because the values remain the same) and execute all the queries at once. I think this is not possible, but I could be wrong. You will need to bind the parameters to each prepared statement. Can I use sqlite3_transfer_bindings here? The website only mentions the case of preparing the same SQL multiple times. But my SQL statements are different. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Some questions on hierarchical data (nested set model)
I want to store a tree in an sqlite database. My first choice was the adjacency list model: CREATE TABLE tree ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, parent_id INTEGER ); But this method requires multiple queries to display the entire tree (or a subtree) in my GUI (a gtk+ treeview). Because childs can only be added to the treeview if all its parents are already added. But then I found some resources on the nested set model [1,2]: CREATE TABLE tree ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, lft INTEGER, rgt INTEGER ); Retrieving a (sub)tree can be done with only one sql query, at the expense of more complex queries to add or remove rows. Because all lft and rgt values to the right of the node have to be modified. [1] http://www.sitepoint.com/article/hierarchical-data-database [2] http://dev.mysql.com/tech-resources/articles/hierarchical-data.html I start to understand this model, but I still have some questions (especially Q3): Q1. Which is more efficient? Two simple queries or one self join? I have seen two different types of queries to retrieve a tree. The first one uses two very simple queries: SELECT lft, rgt FROM tree WHERE name = @name; SELECT * FROM tree WHERE lft BETWEEN @lft AND @rgt ORDER BY lft ASC; The first query is only required to retrieve the lft and rgt values of the node. The other type uses a self join (which I assume is more expensive), but no extra query is required: SELECT node.* FROM tree AS node, tree AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.name = @name ORDER BY node.lft; Which type of query is more efficient? Retrieving the path to a node is very similar: SELECT * FROM tree WHERE lft <= @lft AND rgt >= @rgt ORDER BY lft ASC; or SELECT parent.* FROM tree AS node, tree AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.name = @name ORDER BY parent.lft; Q2. Which indices should I use to make my queries more efficient? Q3. How do I move a node (or subtree)? In the adjacency list model, this is extremely easy by pointing the parent_id to another node. But I don't know how to do that in the nested set model. Q4. sqlite parameter binding for multiple queries? For some operations (like deleting a node) I need multiple queries: DELETE FROM tree WHERE lft BETWEEN @lft AND @rgt; UPDATE tree SET rgt = rgt - (@rgt - @lft + 1) WHERE rgt > @rgt; UPDATE tree SET lft = lft - (@rgt - @lft + 1) WHERE lft > @rgt; and they all need the same parameters (@lft and @rgt). Do I have to prepare each statement separately and bind the parameters every time? Or is it possible to bind the parameters only once (because the values remain the same) and execute all the queries at once. I think this is not possible, but I could be wrong. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: sqlite3_update_hook and transactions
Dan Kennedy wrote: On Mon, 2007-03-12 at 10:51 +0100, Jef Driesen wrote: I was planning to use the sqlite3_update_hook function to notify my GUI about changes. The idea was that every part of the GUI can update itself when a database change is detected. But during testing, I encountered some problems with this approach together with transactions. When I group some SQL statements inside a transaction (for performance or because they have to succeed or fail all together), the callback function is executed for every statement: BEGIN statement 1 -> callback function called statement 2 -> callback function called COMMIT But if the COMMIT is replaced with a ROLLBACK (for instance when an error is detected), the callback functions are still called and the GUI is updated with data that is not actually written to the database. Any ideas on how to prevent this from happening? Accumulate updates in a custom data structure (list or something) each time the update_hook() callback is invoked. Also register callbacks with sqlite3_commit_hook() and sqlite3_rollback_hook(). When the commit_hook() callback is invoked, update the GUI. When either the commit or rollback hooks are invoked, reset the data structure to empty. I think that approach should work. But the function sqlite3_commit_hook is marked experimental in the documentation. What does that means? Is it safe to rely on it? - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite3_update_hook and transactions
I was planning to use the sqlite3_update_hook function to notify my GUI about changes. The idea was that every part of the GUI can update itself when a database change is detected. But during testing, I encountered some problems with this approach together with transactions. When I group some SQL statements inside a transaction (for performance or because they have to succeed or fail all together), the callback function is executed for every statement: BEGIN statement 1 -> callback function called statement 2 -> callback function called COMMIT But if the COMMIT is replaced with a ROLLBACK (for instance when an error is detected), the callback functions are still called and the GUI is updated with data that is not actually written to the database. Any ideas on how to prevent this from happening? - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Do I need to use sqlite3_close after a failed sqlite3_open?
Jef Driesen wrote: [EMAIL PROTECTED] wrote: Jef Driesen wrote: I did. sqlite3_close is called automatically for SQLITE_NOMEM, but not for other cases. So I guess sqlite3_close is still needed. But then it shouldn't return an error, or am I wrong? I don't think any error other than SQLITE_NOMEM is possible for sqlite3_open(). Are you seeing some other kind of error come up? I get SQLITE_CANTOPEN for a non-existing file (and no write permissions to create it). Using sqlite3_close immediately afterwards returns the same value. And sqlite3_errcode returns SQLITE_MISUSE. I think this indicates there is definitely something wrong here. Either the documentation is incorrect (with regards to the usage of sqlite3_close after a failed sqlite3_open), or there is a bug in sqlite3_open/close. I'm using sqlite version 3.3.5 (Ubuntu Edgy package) if that matters. I can only think of one reason why the sqlite3* handle is not released automatically after a failure. If the handle is freed and set to null (like is done for sqlite3_prepare), it's not possible to retrieve more information about the error by means of the sqlite3_errcode and sqlite3_errmsg functions. SQLITE_NOMEM is the exception here, because a null handle is also treated as SQLITE_NOMEM by the error functions. Releasing the handle has no effect on the error reporting in this particular case. _ Did you know that Windows Live Messenger is accesible on your mobile as from now? http://get.live.com/messenger/mobile - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Do I need to use sqlite3_close after a failed sqlite3_open?
Jef Driesen wrote: Do I need to use sqlite3_close if the call to sqlite3_open indicated an error? The documentation for sqlite3_open says "An sqlite3* handle is returned in *ppDb, even if an error occurs." So I assumed the answer is yes. But if I try this code (on a non-existing file and no write permissions): int rc_o = sqlite3_open (filename, &db); if (rc_o != SQLITE_OK) { printf("ERROR: %i, %i, %s\n", rc_o, sqlite3_errcode(db), sqlite3_errmsg(db)); int rc_c = sqlite3_close (db); if (rc_c != SQLITE_OK) { printf("ERROR: %i, %i, %s\n", rc_c, sqlite3_errcode(db), sqlite3_errmsg(db)); } } I get: ERROR: SQLITE_CANTOPEN, SQLITE_CANTOPEN, unable to open database file ERROR: SQLITE_CANTOPEN, SQLITE_MISUSE, library routine called out of sequence Shouldn't sqlite3_close return SQLITE_OK? And why is the rc_c different from the return value of the sqlite3_errcode function? Isn't that function supposed to return the error code of the last sqlite3_* function? Which is sqlite3_close in my code. I made a mistake in my real code. (I checked rc_o in the second comparison, instead of rc_c.) sqlite3_close does return SQLITE_OK in my example. But the error code obtained from sqlite3_errcode returns a different value (SQLITE_MISUSE), which seems to indicate that calling sqlite3_close was not needed after all. I think this is still wrong. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Do I need to use sqlite3_close after a failed sqlite3_open?
[EMAIL PROTECTED] wrote: "Jef Driesen" <[EMAIL PROTECTED]> wrote: I did. sqlite3_close is called automatically for SQLITE_NOMEM, but not for other cases. So I guess sqlite3_close is still needed. But then it shouldn't return an error, or am I wrong? I don't think any error other than SQLITE_NOMEM is possible for sqlite3_open(). Are you seeing some other kind of error come up? I get SQLITE_CANTOPEN for a non-existing file (and no write permissions to create it). Using sqlite3_close immediately afterwards returns the same value. And sqlite3_errcode returns SQLITE_MISUSE. I think this indicates there is definitely something wrong here. Either the documentation is incorrect (with regards to the usage of sqlite3_close after a failed sqlite3_open), or there is a bug in sqlite3_open/close. I'm using sqlite version 3.3.5 (Ubuntu Edgy package) if that matters. _ Did you know that Windows Live Messenger is accesible on your mobile as from now? http://get.live.com/messenger/mobile - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Do I need to use sqlite3_close after a failed sqlite3_open?
Jay Sprenkle wrote: On 1/24/07, Jef Driesen <[EMAIL PROTECTED]> wrote: Do I need to use sqlite3_close if the call to sqlite3_open indicated an error? The documentation for sqlite3_open says "An sqlite3* handle is returned in *ppDb, even if an error occurs." So I assumed the answer is yes. I never do, since if open fails I assumed the handle wasn't valid. Almost never is still possible... It almost never happens so the consequences of being wrong are pretty small. Did you look at the source for sqlite_open()? I did. sqlite3_close is called automatically for SQLITE_NOMEM, but not for other cases. So I guess sqlite3_close is still needed. But then it shouldn't return an error, or am I wrong? _ All things trendy for Windows Live Messenger ... http://entertainment.msn.be/funwithmessenger - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Do I need to use sqlite3_close after a failed sqlite3_open?
Do I need to use sqlite3_close if the call to sqlite3_open indicated an error? The documentation for sqlite3_open says "An sqlite3* handle is returned in *ppDb, even if an error occurs." So I assumed the answer is yes. But if I try this code (on a non-existing file and no write permissions): int rc_o = sqlite3_open (filename, &db); if (rc_o != SQLITE_OK) { printf("ERROR: %i, %i, %s\n", rc_o, sqlite3_errcode(db), sqlite3_errmsg(db)); int rc_c = sqlite3_close (db); if (rc_c != SQLITE_OK) { printf("ERROR: %i, %i, %s\n", rc_c, sqlite3_errcode(db), sqlite3_errmsg(db)); } } I get: ERROR: SQLITE_CANTOPEN, SQLITE_CANTOPEN, unable to open database file ERROR: SQLITE_CANTOPEN, SQLITE_MISUSE, library routine called out of sequence Shouldn't sqlite3_close return SQLITE_OK? And why is the rc_c different from the return value of the sqlite3_errcode function? Isn't that function supposed to return the error code of the last sqlite3_* function? Which is sqlite3_close in my code. _ Who is the sweetheart of the Japanese and always holds something in his hands? Live Search knows! How about you? http://search.live.com/images/results.aspx?q=Manneken%20pis&FORM=QBIR - To unsubscribe, send email to [EMAIL PROTECTED] -