On Fri, 17 Feb 2006, DennisCote wrote:
Thomas Chust wrote:
[...]
I'm maintaining bindings to SQLite3 for the CHICKEN Scheme compiler /
interpreter and I got a bug report from a user about the following
problem: Creating a table with the
CREATE TABLE IF NOT EXISTS table_name(...);
syntax or dropping a table with the
DROP TABLE IF EXISTS table_name;
syntax fails with an exception and an error message of "no error" if the
table in question exists already for the CREATE statement or if it doesn't
exist for the DROP statement.
[...]
[...]
I think you have found a real problem, but not where you think.
First, I modified your program to replace the exec macro with a subroutine so
I could debug it more easily.
Then I noticed that your calls were not being executed in the order you
expected. Your first call to printf contain calls sqlite3_prepare and then
sqlite3_errmsg. But C compilers usually push arguments onto the stack from
right to left (especially for vararg functions like printf), so
sqlite3_errmsg was being called before sqlite3_prepare.
Hello,
although my compiler translated it the way I intended it, which may have
something to do with the PowerPC calling convention, I admit that this way
of writing the program was not very clever, as it is unportable.
After fixing this I still got similar errors reported from sqlite3_step. When
stepping through the code I noticed that the real problem was with
sqlite_prepare. It is returning a NULL pointer for the sqlite3_stmt on the
second call. That is why sqlite_step complains. I added code to dump the
statement pointer after sqlite3_prepare.
This is interesting. Thank you for pointing this out to me, I have really
been blind.
[...]
Now we just need to determine why sqlite3_prepare fails for every second
statement.
Well, first sqlite3_prepare does not fail, because it returns success --
it just silently returns an invalid statement handle, which is bad. And
then it doesn't do that for every second statement, it apparently does
that for every statement which involves a IF [NOT] EXISTS clause whose
condition is not met! I think this is clearly not in accordance with the
documentation for sqlite3_prepare, which claims that a NULL statement is
only returned if an error code is returned as well or if the compiled SQL
code contained nothing but comments.
What makes the situation even worse is the following: Usually
sqlite3_prepare can be used to prepare a statement once and execute it
multiple times during the further lifetime of the program. If we now
create a table, then compile a DROP TABLE IF EXISTS ... statement for this
table using sqlite3_prepare (which returns a valid statement handle) and
then sqlite3_step the statement, the table is gone. If we furthermore
sqlite3_reset this statement and sqlite3_step it again, though, we get an
SQLITE_ERROR return code from this second call to sqlite3_step. Statements
involving a IF [NOT] EXISTS clause are thus not reusable, which is a
severe limitation in my eyes.
[...]
cu,
Thomas Chust
