Hi, in the application I'm working to stablish a connection with SQLite is done in several places like this example procedure:
procedure CrearConexion(var SQLite3Connection: TSQLite3Connection);
var bHayUnError:boolean;
sErrMsg: string;
begin
bHayUnError:=false;
SQLite3Connection:=TSQLite3Connection.create(nil);
// Previene el error: Database connect string (DatabaseName) not filled in!
if dmConnection.gsDatabaseName = '' then
exit;
SQLite3Connection.DatabaseName := dmConnection.gsDatabaseName;
SQLite3Connection.Password:= '*************';
//SQLite3Connection.Params.Add('read_only');
repeat
try
SQLite3Connection.close;
SQLite3Connection.Open;
ExecSQL3(SQLite3Connection,'PRAGMA temp_store = 2;');
ExecSQL3(SQLite3Connection,'PRAGMA journal_mode=WAL;');
except on e:exception do
begin
sErrMsg:=e.Message;
bHayUnError:=not (Pos('DATABASE IS LOCKED',UpperCase(sErrMsg))>=1);
sleep(50);
end;
end;
until (SQLite3Connection.Connected) or (bHayUnError);
if bHayUnError then
begin
DebugLog(LocalPOS.Usuario, 'CrearConexion', 'ERROR ' + sErrMsg);
raise exception.Create('Error al conectar BD: '+sErrMsg);
end;
end;
As you can see, the Open method of the connection (or query in other cases) is surrounded in a repeat .. untill block. This is done because the application can use somewhere else the connection.
Another short example:
result:=true;
sqlqry:= tsqlQuery.Create(nil);
try//finally
try
SQLThreadTransaction:= TSQLTransaction.Create(nil);
if not Assigned(SQLite3InitialConnection) then
begin
CrearConexion(SQLite3ThreadConnection);
SQLThreadTransaction.DataBase := SQLite3ThreadConnection;
sqlqry.DataBase := SQLite3ThreadConnection;
sqlqry.Transaction:=SQLThreadTransaction;
end
else
begin
SQLThreadTransaction.DataBase := SQLite3InitialConnection;
sqlqry.DataBase := SQLite3InitialConnection;
sqlqry.Transaction:=SQLInitialTransaction;
end;
with sqlqry do
begin
Close;
SQL.Text:='SELECT COUNT(1) FROM '+sTabla+' WHERE insertedon IS NULL OR updatedon IS NULL';
try
bHayUnError:=false;bOk:=false;
repeat
try
Open;
bOk:=true;
except on e:exception do
begin
sErrMsg:=e.Message;
bHayUnError:=not (Pos('DATABASE IS LOCKED',UpperCase(sErrMsg))>=1);
sqlqry.close;
sleep(50);
end;
end;
until (bOk) or (bHayUnError);
... more code ...
As you can see, again between repeat .. until.
The application is just object oriented in the UI, and most of it is based on standalone functions and procedures and prefixed with 'g' the globals as in the first example.
The thing is, the repeat.. until thing must be removed to don't block the application when for example we're reading or saving to the DB.
There's a better way to determine that:
- There is something running with SQLite DB
- Wait until it finishes
- If there's success opening the query continue doint the thing the query needs to do
In a way that's using procedures or functions, and globals are allowed as well. Not classes because it will imply to refactor the entire application.