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]
