Recent

Author Topic: How to programmatically connect to SQLite ?  (Read 334 times)

engine32

  • New Member
  • *
  • Posts: 19
How to programmatically connect to SQLite ?
« on: November 15, 2022, 02:35:08 am »
  Hello,

  I am working on an application with four worker threads that needs access to the SQLite database. I read online that I should not use a single connection established with visual component in the main thread but use one connection per thread. I am looking for some advice of how to establish the connection to the database and then read / write 
 to it from individual threads.

  Thank you.

Leledumbo

  • Hero Member
  • *****
  • Posts: 8522
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: How to programmatically connect to SQLite ?
« Reply #1 on: November 15, 2022, 02:45:54 am »
Code: Pascal  [Select][+][-]
  1. uses
  2.   db, sqldb, sqlite3conn;
  3. var
  4.   Conn: TSQLConnector;
  5.   Query: TSQLQuery;
  6.   F: TField;
  7. begin
  8.   Conn := TSQLConnector.Create(nil);
  9.   with Conn do begin
  10.     ConnectorType := 'SQLite3';
  11.     HostName := ''; // unread by underlying TSQLite3Connection
  12.     DatabaseName := '/path/to/sqlite/db/file.sqlite';
  13.     UserName := ''; // unread by underlying TSQLite3Connection
  14.     Password := ''; // this is still read, if you use the encrypted db feature
  15.     Transaction := TSQLTransaction.Create(Conn);
  16.   end;
  17.  
  18.   Query := TSQLQuery.Create(nil);
  19.   Query.DataBase := Conn;
  20.  
  21.   Query.SQL.Text := 'select * from tables'; // make your own query
  22.   Query.Open;
  23.   while not Query.EOF do begin
  24.     for F in Query.Fields do begin
  25.       Write(F.FieldName,' = ');
  26.       if F.IsNull then WriteLn('NULL') else WriteLn(F.Value);
  27.     end;
  28.     WriteLn;
  29.     Query.Next;
  30.   end;
  31.   Query.Close;
  32.   Query.Free;
  33.   Conn.Free;
  34. end.
  35.  
  36.  
« Last Edit: November 15, 2022, 09:55:25 am by Leledumbo »

Zvoni

  • Hero Member
  • *****
  • Posts: 1591
Re: How to programmatically connect to SQLite ?
« Reply #2 on: November 15, 2022, 08:05:54 am »
uses
  db, sqldb, mysql56conn;
*snipp*
  with Conn do begin
    ConnectorType := 'SQLite3';
*snipp*
?!?!???!??!??!
Especially for SQLite don't forget to set the proper OpenFlags, and, IIRC, to set Journal_mode-Pragma to WAL
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Leledumbo

  • Hero Member
  • *****
  • Posts: 8522
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: How to programmatically connect to SQLite ?
« Reply #3 on: November 15, 2022, 09:55:03 am »
uses
  db, sqldb, mysql56conn;
*snipp*
  with Conn do begin
    ConnectorType := 'SQLite3';
*snipp*
?!?!???!??!??!
Especially for SQLite don't forget to set the proper OpenFlags, and, IIRC, to set Journal_mode-Pragma to WAL
Oops, forgot to change that. My original example code was indeed to connect with MySQL 5.6 or MariaDB 10.0 equivalent.

 

TinyPortal © 2005-2018