2008/8/8 Igor Tandetnik <[EMAIL PROTECTED]> > "Daniel Hellsson" > <[EMAIL PROTECTED]> wrote in > message news:[EMAIL PROTECTED]<[EMAIL PROTECTED]> > > I am writing unit tests for my database functions. > > In one test I'm opening the database. > > Then I open the database again with a new pointer. > > With that pointer I create the table with > > > > "CREATE TABLE IF NOT EXISTS foo \ > > ( id INTEGER PRIMARY KEY, \ > > bar_id INTEGER, \ > > hoopy INTEGER, \ > > frood_id INTEGER )" > > > > then I close that connection (with sqlite3_close) and with the old > > pointer I do an sqlite3_prepare_v2 with > > > > "SELECT frood_id,hoopy FROM ingredients WHERE bar_id=1" > > > > This is where I get an SQLITE_SCHEMA error (from prepare) with the > > error message: table 'foo' does not exist. > > What is the relation between 'foo' and 'ingredients' ? The statement > never mentions foo, as far as I can tell. > > Assuming it's a typo, this is a known issue. SQLite caches the database > schema when opening the database, and re-reads it every time it detects > the schema has changed. But sqlite3_prepare* works off of the cached > schema: it would be too expensive to actually read the file on disk > every time a statement is prepared. > > So changes to the schema made on a different connection aren't visible > until the database file is read by any statement. If you want to make > sure you pick up the latest changes, run something like "select * from > sqlite_master where 0;" > > Igor Tandetnik > > Sorry for the confusion. I was trying to obfuscate what I was doing (this is a commercial non-open-source project). :-)
Ingredients was supposed to be 'foo', so it is indeed a typo. of sorts. :) Ah, ok, so this is a known problem. It was surprising to me since everything else worked so nicely and only this one thing did not work. I will probably have to rewrite my tests so that they open new connections to the database instead of using existing connections like they do now. It is only for convenience that I do that now. A thousand thanks to you for your quick reply! /Daniel _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
