[sqlite] possible bug in lockBtree
I think there might be a bug in this code in lockBtree:
if( sqlite3pager_pagecount(pBt->pPager)>0 ){
u8 *page1 = pPage1->aData;
if( memcmp(page1, zMagicHeader, 16)!=0 ){
goto page1_init_failed;
The problem is if you are trying to open a non-sqlite file that is
smaller than pageSize. I believe in that case sqlite3pager_pagecount
will return 0, which means that the code above that tests the magic
header won't execute. At which point SQLite thinks the file is a
sqlite database and use it. If the file is larger than pageSize, then
it is correctly identified as not-a-database.
RE: [sqlite] Binding a column name?
On Sun, 2005-07-10 at 15:34 -0700, Tim McDaniel wrote: > > > > If another thread or process VACUUMs the database or creates > > a new table or makes any other structure changes to the > > database file, all of your prepared statements will be > > invalided and you will have to rerun sqlite3_prepare(). > > Since you generally have no control over when another process > > might VACUUM the database, you should always be prepared to > > rerun sqlite3_prepare() if necessary. This is true even if > > you are only running your SQL statement once and then > > finalizing it because another process might VACUUM and > > invalidate your statement in the very brief window of time > > between your calls to sqlite3_prepare() and sqlite3_step(). > > > > Your best bet it to use a wrapper class of some sort that > > automates the task of rerunning sqlite3_prepare() when necessary. > > > > Does sqlite store the SQL text passed into sqlite3_prepare? > If not, then I assume this means that any time we use sqlite3_prepare, > we should cache the SQL text "in the wrapper" in case we need to > re-prepare it. Yes. The wrapper needs to keep the SQL text because SQLite does not. > Along the same line, I suppose we have to cache all the bound > parameters, since they will have to re-bound as well. You can do that. Or you can keep the old prepared statement around until after the new one is ready, then use the sqlite3_transfer_bindings() API to transfer all your bindings from the old to the new, then finalize the old. > > Is it possible to get the SQLITE_SCHEMA error after the first > sqlite3_step call, while iterating throw the rows? > No. SQLITE_SCHEMA will always appear immediately or not at all. -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] Binding a column name?
> -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Sunday, July 10, 2005 6:12 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Binding a column name? > > On Sun, 2005-07-10 at 00:23 -0700, Brown, Dave wrote: > > That is what I do. But that also means I have to call > sqlite_prepare() > > each time, instead of just once. I was originally hoping I could > > prepare() once and just bind. > > > > If another thread or process VACUUMs the database or creates > a new table or makes any other structure changes to the > database file, all of your prepared statements will be > invalided and you will have to rerun sqlite3_prepare(). > Since you generally have no control over when another process > might VACUUM the database, you should always be prepared to > rerun sqlite3_prepare() if necessary. This is true even if > you are only running your SQL statement once and then > finalizing it because another process might VACUUM and > invalidate your statement in the very brief window of time > between your calls to sqlite3_prepare() and sqlite3_step(). > > Your best bet it to use a wrapper class of some sort that > automates the task of rerunning sqlite3_prepare() when necessary. > Does sqlite store the SQL text passed into sqlite3_prepare? If not, then I assume this means that any time we use sqlite3_prepare, we should cache the SQL text "in the wrapper" in case we need to re-prepare it. Along the same line, I suppose we have to cache all the bound parameters, since they will have to re-bound as well. Is it possible to get the SQLITE_SCHEMA error after the first sqlite3_step call, while iterating throw the rows? Tim
Re: [sqlite] file db from memory db
On Sun, 2005-07-10 at 12:21 -0400, [EMAIL PROTECTED] wrote: > IIRC, you can't create a table in an attached DB. That's true for SQLite 2.8. But beginning with SQLite 3.0 the restriction is removed and you can create new tables in an attached database. -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] Binding a column name?
Yes, actually I'm doing that already. Thanks! -Dave -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Sunday, July 10, 2005 4:12 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Binding a column name? On Sun, 2005-07-10 at 00:23 -0700, Brown, Dave wrote: > That is what I do. But that also means I have to call sqlite_prepare() > each time, instead of just once. I was originally hoping I could > prepare() once and just bind. > If another thread or process VACUUMs the database or creates a new table or makes any other structure changes to the database file, all of your prepared statements will be invalided and you will have to rerun sqlite3_prepare(). Since you generally have no control over when another process might VACUUM the database, you should always be prepared to rerun sqlite3_prepare() if necessary. This is true even if you are only running your SQL statement once and then finalizing it because another process might VACUUM and invalidate your statement in the very brief window of time between your calls to sqlite3_prepare() and sqlite3_step(). Your best bet it to use a wrapper class of some sort that automates the task of rerunning sqlite3_prepare() when necessary. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] file db from memory db
Cornel Gazdaru <[EMAIL PROTECTED]> writes: > I try copying a table from a :memory: data base to a file data base, but > could not make it work. > Anybody has any suggestions? > Thanks > > I tried something like: > > open a data base :memory: > create table MYTABLE (...) > ... > > attach 'filename.db' as FileDB > create table FileDB.MYTABLE as select * from MYTABLE > detach FileDB IIRC, you can't create a table in an attached DB. Instead, you can create an empty table MYTABLE in filename.db first, then do similarly to what you were previously: open a data base :memory: create table MYTABLE (...) ... attach 'filename.db' as FileDB insert into FileDB.MYTABLE select * from MAIN.MYTABLE detach FileDB
[sqlite] file db from memory db
I try copying a table from a :memory: data base to a file data base, but could not make it work. Anybody has any suggestions? Thanks I tried something like: open a data base :memory: create table MYTABLE (...) ... attach 'filename.db' as FileDB create table FileDB.MYTABLE as select * from MYTABLE detach FileDB
RE: [sqlite] Binding a column name?
On Sun, 2005-07-10 at 00:23 -0700, Brown, Dave wrote: > That is what I do. But that also means I have to call sqlite_prepare() each > time, instead of just once. I was originally hoping I could prepare() once > and just bind. > If another thread or process VACUUMs the database or creates a new table or makes any other structure changes to the database file, all of your prepared statements will be invalided and you will have to rerun sqlite3_prepare(). Since you generally have no control over when another process might VACUUM the database, you should always be prepared to rerun sqlite3_prepare() if necessary. This is true even if you are only running your SQL statement once and then finalizing it because another process might VACUUM and invalidate your statement in the very brief window of time between your calls to sqlite3_prepare() and sqlite3_step(). Your best bet it to use a wrapper class of some sort that automates the task of rerunning sqlite3_prepare() when necessary. -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] Binding a column name?
That is what I do. But that also means I have to call sqlite_prepare() each time, instead of just once. I was originally hoping I could prepare() once and just bind. -Dave -Original Message- From: Eugene Wee [mailto:[EMAIL PROTECTED] Sent: Sunday, July 10, 2005 12:18 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Binding a column name? Hi, Why not construct the SQL statement dynamically in the C/C++ code? That way your statement(s) would have variable column names before compilation. Eugene Wee Brown, Dave wrote: > Actually I doubt it can - since without the column name it can't > create the prepared statement byte code, right? > > -Dave > > -Original Message- > From: Brown, Dave [mailto:[EMAIL PROTECTED] > Sent: Saturday, July 09, 2005 8:46 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Binding a column name? > > > Is it possible for a bind variable to be a column name? I'd like to > make a query which is: > > select from MyTable; > > and I'd like the column_name to be a bind variable. This doesn't work > using the straight sqlite3_bind_text() call on the statement "select ? > from MyTable;", which treats the column name as text and not part of > the compiled statement. > > -Dave > >
Re: [sqlite] Binding a column name?
Hi, Why not construct the SQL statement dynamically in the C/C++ code? That way your statement(s) would have variable column names before compilation. Eugene Wee Brown, Dave wrote: Actually I doubt it can - since without the column name it can't create the prepared statement byte code, right? -Dave -Original Message- From: Brown, Dave [mailto:[EMAIL PROTECTED] Sent: Saturday, July 09, 2005 8:46 PM To: sqlite-users@sqlite.org Subject: [sqlite] Binding a column name? Is it possible for a bind variable to be a column name? I'd like to make a query which is: select from MyTable; and I'd like the column_name to be a bind variable. This doesn't work using the straight sqlite3_bind_text() call on the statement "select ? from MyTable;", which treats the column name as text and not part of the compiled statement. -Dave
RE: [sqlite] Binding a column name?
> Actually I doubt it can - since without the column name it can't create the > prepared statement byte code, right? Right. It can't be done. > > Is it possible for a bind variable to be a column name? I'd like to make a > query which is: > > select from MyTable; > > and I'd like the column_name to be a bind variable. This doesn't work using > the straight sqlite3_bind_text() call on the statement "select ? from > MyTable;", which treats the column name as text and not part of the compiled > statement. > > -Dave > > Sell on Yahoo! Auctions no fees. Bid on great items. http://auctions.yahoo.com/
