On 5/11/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
"Vivien Malerba" <[EMAIL PROTECTED]> wrote:
> Hi!
>
> In a single process, I open two connections (C1 and C2) to the same
> database (this is actually a corner case which could happen) and the
> following sequence of operations fail:
> 1- on C1 execute "CREATE table actor (...)" => Ok
> 2- on C1 execute "SELECT * FROM actor" => Ok
> 3- on C2 execute "SELECT * FROM actor" => error because table "actor"
> does not exist.
>
When C2 goes to parse the SQL in statement 3, it does not know
that the database schema has changed because it has not attempted
to access the database file. Thus it does not know that the new
table exists.
To fix this, you have to get C2 to access the database so that
it will reread and reparse the schema and thus discover the new
table. Perhaps something like this:
2.5- on C2 execute "SELECT 1 FROM sqlite_master LIMIT 1"
This seems to work, see test case (uncomment line 68, 69 to make it
work, remove the Test.db before each run).
Thanks!
Vivien
#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
static int
execute_sql (sqlite3 *db, const char *sql)
{
int status;
sqlite3_stmt *stmt = NULL;
int retval = 0;
printf ("== %p SQL: %s\n", db, sql);
status = sqlite3_prepare_v2 (db, sql, -1, &stmt, NULL);
if (status != SQLITE_OK)
printf (" ERROR preparing statement: %s\n", sqlite3_errmsg (db));
else {
status = sqlite3_step (stmt);
if ((status != SQLITE_OK) && (status != SQLITE_DONE) && (status != SQLITE_ROW))
printf (" ERROR executing statement: %s\n", sqlite3_errmsg (db));
else {
retval = 1;
printf (" Ok\n");
}
}
if (stmt)
sqlite3_finalize (stmt);
return retval;
}
int
main(int argc, char **argv){
char *dbname = "Test.db";
sqlite3 *db1, *db2;
int rc;
rc = sqlite3_open(dbname, &db1);
if (rc) {
printf ("Can't open database: %s\n", sqlite3_errmsg(db1));
exit(1);
}
rc = sqlite3_open(dbname, &db2);
if (rc) {
printf ("Can't open database: %s\n", sqlite3_errmsg(db2));
exit(1);
}
printf ("DB1: %p\nDB2: %p\n", db1, db2);
if (! execute_sql (db1, " CREATE TABLE if not exists actor (\
actor_id INTEGER PRIMARY KEY, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, \
last_update TIMESTAMP NOT NULL);"))
exit (1);
if (! execute_sql (db1, "SELECT * FROM actor"))
exit (1);
if (! execute_sql (db2, "SELECT * FROM actor"))
exit (1);
if (! execute_sql (db1, " CREATE TABLE if not exists othertable (\
othertable_id INTEGER PRIMARY KEY, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, \
last_update TIMESTAMP NOT NULL);"))
exit (1);
if (! execute_sql (db1, "SELECT * FROM othertable"))
exit (1);
/*if (! execute_sql (db2, "SELECT 1 FROM sqlite_master LIMIT 1"))
exit (1);*/
if (! execute_sql (db2, "SELECT * FROM othertable"))
exit (1);
sqlite3_close(db1);
sqlite3_close(db2);
return 0;
}
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
