SQLite multiple connections in existing code
lazarus / 16-Databases

----------------------------------------------------------------------
lainz · 2018-12-26 22:25

Hi, in the application I'm working to stablish a connection with
SQLite is done in several places like this example procedure:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
 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.