[yunqa.de] Re: SQLITE_BUSY ERROR
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Thu, 09 Oct 2008 17:07:14 +0200
DonAndSue wrote:
>I have one database connection and 2 queries. Do you have to finalize one
>query before you can make another ?
You can open as many SELECT queries on the same database connection as you
like. But you must close / finalize ALL statements (a query always corresponds
to a DISQLite3 statement) before you close the database connection. You can
even reuse the prepared detail statement (see attached example project).
>The First Query is SELECT * FROM Table1 WHERE Key = MasterKey. Then in the
>GetColumns of the Rave Report I pickup my key for the Details Table. I then
>Call the The SQL for the Details Which is SELECT * FROM Table2 Where Key =
>:myKey after setting the param. Then when the details are done this query is
>finalized and i go back to the master query for the next line. If their are no
>more records in the Master Query then that query is finalized.
Your description reads all right and should work without problems.
>I just tried it and found out that if I only have one record in the master
>table it does not get the error. Are you saying I should have 2 different
>database connections ? ( one for each query )
A single database connection is sufficient for running two select queries, as
the demo in the attach shows. I am not sure what you are doing wrong. Are you
by any chance running an INSERT or UPDATE while a SELECT statement is still
open?
If everything else fails, can you provide some code to reproduce the problem?
Ralf
{ DISQLite3 to show how to run a master detail query on a single database
connection.
Visit the DISQLite3 Internet site for latest information and updates:
http://www.yunqa.de/delphi/
Copyright (c) 2005-2008 Ralf Junker, The Delphi Inspiration <delphi@xxxxxxxx>
------------------------------------------------------------------------------ }
program DISQLite3_Master_Detail_Query;
{$I DI.inc}
{$I DISQLite3.inc}
{$APPTYPE CONSOLE}
uses
FastMM4,
SysUtils,
DISQLite3Api,
DISQLite3Database;
//------------------------------------------------------------------------------
var
DB: TDISQLite3Database;
MasterStmt, DetailStmt: TDISQLite3Statement;
begin
{ Disable FPU exceptions. No need to restore, setting is process specific. }
Set8087CW($133F);
try
DB := TDISQLite3Database.Create(nil);
try
{ Create tables and fill with example data. }
DB.DatabaseName := 'test.db3';
DB.CreateDatabase;
DB.Execute('CREATE TABLE Countries (ID INTEGER PRIMARY KEY, Name TEXT);');
DB.Execute('INSERT INTO Countries (ID, Name) VALUES (1, ''France'');');
DB.Execute('INSERT INTO Countries (ID, Name) VALUES (2, ''Germany'');');
DB.Execute('INSERT INTO Countries (ID, Name) VALUES (3, ''Italy'');');
DB.Execute('CREATE TABLE Cities (ID INTEGER PRIMARY KEY, CountryID
INTEGER, Name TEXT);');
DB.Execute('INSERT INTO Cities (CountryID, Name) VALUES (1, ''Paris'');');
DB.Execute('INSERT INTO Cities (CountryID, Name) VALUES (1,
''Marseille'');');
DB.Execute('INSERT INTO Cities (CountryID, Name) VALUES (1, ''Lyon'');');
DB.Execute('INSERT INTO Cities (CountryID, Name) VALUES (2,
''Berlin'');');
DB.Execute('INSERT INTO Cities (CountryID, Name) VALUES (2,
''Hamburg'');');
DB.Execute('INSERT INTO Cities (CountryID, Name) VALUES (2,
''Munich'');');
DB.Execute('INSERT INTO Cities (CountryID, Name) VALUES (3, ''Rome'');');
DB.Execute('INSERT INTO Cities (CountryID, Name) VALUES (3, ''Milan'');');
DB.Execute('INSERT INTO Cities (CountryID, Name) VALUES (3,
''Naples'');');
{ Create master and detail statements and loop through results. }
MasterStmt := DB.Prepare('SELECT ID, Name FROM Countries;');
DetailStmt := DB.Prepare('SELECT Name FROM Cities WHERE CountryID = ?;');
try
while MasterStmt.Step = SQLITE_ROW do
begin
WriteLn(MasterStmt.Column_Str16(1));
try
DetailStmt.Bind_Int(1, MasterStmt.Column_Int(0));
while DetailStmt.Step = SQLITE_ROW do
WriteLn(' ', DetailStmt.Column_Str16(0));
finally
DetailStmt.Reset;
end;
end;
finally
DetailStmt.Free;
MasterStmt.Free;
end;
finally
DB.Free;
end;
except
on e: Exception do
WriteLn(e.Message);
end;
WriteLn;
WriteLn('Done - Press ENTER to Exit');
ReadLn;
end.
Other related posts:
