Actually I just discovered that sqlite4delphi uses exactly the technique that I want. It parses the original query to extract the table name then constructs a new update statement. Turns out it isn't too hard.
Regards, Chris. > -----Original Message----- > From: Chris Waters [mailto:[EMAIL PROTECTED] > Sent: Sunday, April 18, 2004 1:33 PM > To: [EMAIL PROTECTED] > Subject: RE: [sqlite] Updating with a Cursor > > > Hi, > > I am replacing a non-SQL database engine and so I'm trying to > match the existing DB API structure as closely as possible to > minimize the required code restructuring. > > The type of structure I am aiming for is like Oracle's PL/SQL: > > cursor c_f is > select a,b from f where length(b) = 5 for update; > v_a f.a%type; > v_b f.b%type; > begin > open c_f; > loop > fetch c_f into v_a, v_b; > exit when c_f%notfound; > update f set a=v_a*v_a where current of c_f; > end loop; > > I believe that the 'where current of c_f' part of the update > statement is simply translated into 'where rowid = current_rowid' > which is the most efficient way of doing an update. At least for > Oracle this is the efficient way to do an update on a single record. > > Regards, > > Chris. > > > -----Original Message----- > > From: Andrew Piskorski [mailto:[EMAIL PROTECTED] > > Sent: Sunday, April 18, 2004 12:04 PM > > To: [EMAIL PROTECTED] > > Cc: Chris Waters > > Subject: Re: [sqlite] Updating with a Cursor > > > > > > On Sun, Apr 18, 2004 at 11:04:14AM -0700, Chris Waters wrote: > > > > > The idea is that I can iterate through a set of records with > the cursor, > > > updating as I go. The challenge is working out how to implement the > > > UpdateRow() function. I am happy to accept the costraint that > > the query must > > > > > Implementing another layer of SQL parsing is less than ideal > > since I have > > > found that the parser already dominates sqlite performance > for my tests > > > (in-memory DB with many different queries using indexes). > > > > Why do you want to write special C cursor code rather than just using > > a single SQL update statement? Performance? If so, maybe it would be > > better to create a way to just cache and re-use the SQL query plan, > > rather than throwing out the use of SQL altogether? > > > > -- > > Andrew Piskorski <[EMAIL PROTECTED]> > > http://www.piskorski.com/ > > > > --------------------------------------------------------------------- > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
