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>
> > >>
> > >
> > >
> >
> >
>
--
---
You a Gamer? If you're near Kansas City:
Conquest 36
https://events.reddawn.net
The Castles of Dereth Calendar: a tour of the art and architecture of
Asheron's Call
http://www.lulu.com/content/77264
