RE: [sqlite] Binding a column name?
On Mon, 2005-07-11 at 10:32 -0700, Brown, Dave wrote: > Wait - what if AUTOVACUUM is set on the database, and I'm the only one doing > inserts/deletes? Will I still need to sqlite3_prepare() my statements again > if auto-vacuum is on? > AUTOVACUUM does *not* invalidate prepared statements - ever. -- 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:01 PM > To: sqlite-users@sqlite.org > Subject: 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. > -- Is there any advantage to using sqlite3_expired() vs just sqlite3_step(), since you have to check for SQLITE_SCHEMA anyway?
RE: [sqlite] Binding a column name?
Wait - what if AUTOVACUUM is set on the database, and I'm the only one doing inserts/deletes? Will I still need to sqlite3_prepare() my statements again if auto-vacuum is on? -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] 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] 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] 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/
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? -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
