We have a problem that is obviously to do with our failing to understand
sqlite properly. Using a test program (enclosed) we create a table in a
new database and then wait (for use input). If we then open the same
sqlite database using sqlite3 and create another table in the database
when we continue with our program it fails to insert data into the table
it has created. Now, we know that schema changes invalidate any
previously prepared statements but we always prepare and execute (step)
our statements in one go. So what should our program do prior to
preparing a statement to ensure that its view of the database schema is
correct?
If we use sqlite3_exec instead it all works as this has a built in retry
for the step failing and then the finalize returning SQLITE_SCHEMA. I
thought this was to cater for the case of preparing before the schema
change and then stepping afterwards, the documented failure, but it also
covers our case.
I raised a bug at sqlite.org, ticket 1936, but have been told that I
should post to the list to receive support. Obviously, we are missing
something very basic here or .....
Our problem occurs in a multi-threaded environment but we have minimized
the code in the attached example to remove any threading issues.
code follows:
/* Prepare, execute, finalize as an atomic operation */
void execute(struct sqlite3 *db, const char *sql)
{
int status = 0;
struct sqlite3_stmt *stmt;
const char *tail = 0;
fprintf(stdout, "%s\n", sql);
if((status = sqlite3_prepare(db, sql, strlen(sql), &stmt,
&tail)) != SQLITE_OK)
{
fprintf(stderr, "sqlite3_prepare: %d\n", status);
}
status = sqlite3_step(stmt);
if(status != SQLITE_DONE)
{
fprintf(stderr, "sqlite3_step: %d, %s\n", status,
sqlite3_errmsg(db));
}
if((status = sqlite3_finalize(stmt)) != SQLITE_OK)
{
fprintf(stderr, "sqlite3_finalize: %d, %s\n", status,
sqlite3_errmsg(db));
}
}
int main(int ac, char* av[])
{
int status = 0;
struct sqlite3 *db = 0;
char buffer[256];
if((status = sqlite3_open("test.db", &db)) != SQLITE_OK)
{
fprintf(stderr, "sqlite3_open: %d\n", status);
}
sprintf(buffer, "create table %s (i int)", av[1]);
execute(db, buffer);
puts("type <CR> to continue:");
getchar();
sprintf(buffer, "insert into %s values (4)", av[1]);
execute(db, buffer);
if((status = sqlite3_close(db)) != SQLITE_OK)
{
fprintf(stderr, "sqlite3_close: %d\n", status);
}
return 0;
}
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
