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

[Andy C. <and...@bt...>]

Hi Rene,


   You're right, I'm using SQLite!
I can see my mistake now in assuming that the two methods below would be OK
because I was reading the results from libsql's buffer and not from SQLite.
Of course, not the case with Execute (versus Query).

In my test tables, I have named the id's differently, but in the final
database, the track_id will be consistent across tables as one single
table would have too many columns to be comfortable to handle.

Thanks again, very much appreciated.
I'll look at rewriting this shortly.


Rgs,
Andy




Sunday, March 12, 2006, 9:35:42 AM, you wrote:

> 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
>>  
>>



> -------------------------------------------------------
> 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