That's part of the problem I had been having...
1) You have to loop through the resultset twice, once to put it in memory, the other to process it in your application.
2) The sqlite3_prepare() and sqlite3_finalize() both should be in a loop as well right? Since they can throw SQLITE_BUSY and SQLITE_SCHEMA themselves. So that should be a nested loop.
So we now have about 25-30 lines of code including a nested loop so to step through a *relatively* simple resultset.
I'm not complaining, just wanted to share my observation :)
PS. What if sqlite *optionally* placed the resultset in memory for the user. Then they would they have to worry about BUSY or SCHEMA errors whilst stepping through it?
- Kervin
Jay Sprenkle wrote:
I prepared the statement and executed it. Preparing doesn't use callbacks and prevents sql injection attacks. I used a C++ class to store the query results in an STL container.
brief C/C++ pseudocode:
string sql = "select * from blah"; sqlite3_open(); sqlite3_prepare( sql ); bool Loop = true; int retry = 0; while ( loop && retry < 10 ) switch ( sqlite3_step() ) { // if database busy wait for a short time // to see if it becomes available case SQLITE_BUSY: case SQLITE_LOCKED: retry++; break; case SQLITE_ROW: // get results retry = 0; p = sqlite3_column_text(); in_memory_list.push_back( p ); break; case SQLITE_DONE: Loop = false; break; default: string str = "Cannot execute sql: " + sql + ", Error: " + sqlite3_errmsg(db); throw str.c_str(); break; } // clean up when finished sqlite3_finalize();
//process your list here
sqlite3_close( db );
On 4/20/05, Thomas Briggs <[EMAIL PROTECTED]> wrote:
What APIs are you guys using to retrieve results and execute the subsequent updates? Are you using prepare/step or sqlite3_exec with a callback to retrieve the results? Would it be possible for you to post more detailed pseudo-code? Conceptually I think we're all on the same page; I think what we're unclear about is exactly how you're trying to accomplish this.
Thanks -Tom
-----Original Message----- From: Ben Clewett [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 20, 2005 3:32 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Locking Methods
This is exactly my problem. My version is 3.1.6. The error is SQLITE_LOCKED.
Ben
Jay Sprenkle wrote:
I had the same trouble he did. Here's what I did that doesn't work:
select * from a into result; foreach row in result ' this fails: update b set col = a.value; next
But based on what I read here it's supposed to do this.
On 4/19/05, Gerry Blanchette <[EMAIL PROTECTED]> wrote:
Could you please elaborate your scenario? I tried a test myself but am afraid I may not have
interpreted your test
case properly.
I have 2 tables, fred and bob, each with 10000 rows. I
select a column
from fred and bind the value obtained from sqlite3_column_int to an
update statement that operates on bob. I loop over fred via sqlite3_step, where each iteration successfully updates the
row in bob.
Both tables exist in the same DB, accessed via the same sqlite3 *.
Have I misinterpreted your scenario somehow, as this works for me?
Thank you for helping clear this up for me.
-- Gerry Blanchette
-----Original Message----- From: Ben Clewett [mailto:[EMAIL PROTECTED] Sent: Monday, April 18, 2005 4:50 AM To: sqlite-users@sqlite.org Subject: [sqlite] Locking Methods
<snip> I am experiencing problems with the locking. Because SQLite uses database locking this forces two major problems:
- I can't read through a record set and use the data to
execute updates.
For instance, some parsing exercise which cannot be
completed using a
single SQL command. I have to store all the data locally,
get to the
end of the query, then execute and update statements.
Ben Clewett. </snip>
