Re: [libsql] Advice on SQL DELETEs inside a SELECT result loop

[<re...@du...>]

Hi,

[Assuming you use SQLite]
You appear to delete records from the same table as you are fetching 
from (i.e. have an open SQLite VM handle). Allowing this would lead to 
inconsistent queries.

The error in libsql is that you don't get an error message (as you say). 
I will look into this issue. Just fetch the rows in advance (using 
FormatQuery) and then delete them. Also, it appears to me that you mix 
stats_id and track_id?

regards,

Rene

Andy Chandler wrote:

>Hi,
>
>I've come across what I expect is operator error on my part, so I was
>hoping for some quick advice as to the direction I should be going. The SQL task I'm performing I suspect can
>be re-written as a compound SQL statement, but while I learn SQL fully, I'm performing it in a couple of stages in Delphi.
>This is where I have a query.....
>
>If I do a "SELECT track_id FROM main WHERE checked='N'" and store the resulting list of numbers in an array or list, then
>perform the delete command "DELETE FROM main WHERE track_id=%d", using this list of numbers, this works fine with no problems.
>
>But if I try to write it all in one loop, it always fails to perform the DELETE commands.
>No errors reported and it still runs down the full list of results inside the loop. 
>Both ways below fail in the same way..... (maybe the odd typo as I'm writing partially from memory)
>
>
>
>Using one DB object, but 2 result sets
>--------------------------------------
>
>rs1 := TResultSet.Create (Ldb);
>rs2 := TResultSet.Create (Ldb);
>Ldb.StartTransaction;
>rs1.FormatExecute('SELECT track_id FROM main WHERE checked=%s',['N']);
>while rs1.FetchRow do begin
>   rs2.FormatQuery('DELETE FROM mp3_stats WHERE stats_id=%d',[temp]);
>   rs2.FormatQuery('DELETE FROM main WHERE track_id=%d',[temp]);
>end;
>Ldb.Commit;
>rs2.free;
>rs1.free;
>
>
>
>
>Using two DB objects 
>--------------------
>Ldb2.StartTransaction;
>handle:=Ldb.FormatExecute('SELECT track_id FROM main WHERE checked=%s',['N']);
>while Ldb.FetchRow(handle,row) do begin
>   temp:=row['track_id'];
>   Ldb2.FormatQuery('DELETE FROM mp3_stats WHERE stats_id=%d',[temp]);
>   Ldb2.FormatQuery('DELETE FROM main WHERE track_id=%d',[temp]);
>end;
>Ldb.FreeResult(handle);
>Ldb2.Commit;
>
>
>
>
>Who'd like be first in line to call me a lemon ;-)
>Thanks all.
>
>
>
>-------------------------------------------------------
>This SF.Net email is sponsored by xPML, a groundbreaking scripting language
>that extends applications into web and mobile media. Attend the live webcast
>and join the prime developer group breaking into this new coding territory!
>http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
>_______________________________________________
>libsql-discussion mailing list
>lib...@li...
>https://lists.sourceforge.net/lists/listinfo/libsql-discussion
>  
>