Recent

Author Topic: Straight forward sqlite database  (Read 2807 times)

vdijken

  • New member
  • *
  • Posts: 7
Straight forward sqlite database
« on: November 09, 2021, 11:08:15 am »
I want to use a simple pascal/lazarus program using an sqlite database without using an IDE, forms etc. One of the tables has so much entries that it does not fit into an array and I need to search into it. But I cannot find an example how to simply open a database, write and read to it and close it without using IDE elements. 

Handoko

  • Hero Member
  • *****
  • Posts: 4723
  • My goal: build my own game engine using Lazarus
Re: Straight forward sqlite database
« Reply #1 on: November 09, 2021, 11:55:44 am »

Zvoni

  • Hero Member
  • *****
  • Posts: 1527
Re: Straight forward sqlite database
« Reply #2 on: November 09, 2021, 12:14:25 pm »
Place the sqlite3.dll in the Project-Folder
Code: Pascal  [Select][+][-]
  1. program project1;
  2.  
  3. Uses sysutils, classes, sqldb, db, sqlite3conn;
  4.  
  5. Var
  6.   SQLiteConn:TSQLite3Connection;
  7.   Transaction:TSQLTransaction;
  8.   SQLQuery:TSQLQuery;
  9.  
  10. Const
  11.   SQLCreate:String='CREATE TABLE IF NOT EXISTS `tbl_data` (`ID` INTEGER NOT NULL UNIQUE, `StringData` TEXT, PRIMARY KEY(`ID`))';
  12.   SQLInsert:String='INSERT INTO tbl_data (StringData) VALUES (:ParamData);';
  13.   SQLSelect:String='SELECT * FROM tbl_data WHERE ID=1;';
  14.  
  15. begin
  16.   SQLiteConn:=TSQLite3Connection.Create(nil);
  17.   SQLiteConn.DatabaseName:=GetCurrentDir+'/test.db';
  18.   Transaction:=TSQLTransaction.Create(SQLiteConn);
  19.   Transaction.DataBase:=SQLiteConn;
  20.   SQLQuery:=TSQLQuery.Create(nil);
  21.   SQLQuery.DataBase:=SQLiteConn;
  22.   SQLQuery.Transaction:=Transaction;
  23.   SQLiteConn.Open;
  24.  
  25.   SQLQuery.SQL.Text:=SQLCreate;
  26.   SQLQuery.ExecSQL;
  27.   Transaction.Commit;
  28.  
  29.   SQLQuery.SQL.Text:=SQLInsert;
  30.   SQLQuery.ParamByName('ParamData').AsString := 'My TestString';
  31.   SQLQuery.ExecSQL;
  32.   Transaction.Commit;
  33.  
  34.   SQLQuery.SQL.Text:=SQLSelect;
  35.   SQLQuery.Open;
  36.  
  37.   Writeln('StringData for ID=',SQLQuery.FieldByName('ID').AsString,': ', SQLQuery.FieldByName('StringData').AsString);
  38.  
  39.   SQLQuery.Close;
  40.   SQLQuery.Free;
  41.   Transaction.Free;
  42.   SQLiteConn.Free;
  43.  
  44. end.
  45.  
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

ezlage

  • New Member
  • *
  • Posts: 32
  • Silence is the perfect beat!
    • GitHub
Re: Straight forward sqlite database
« Reply #3 on: November 09, 2021, 12:46:34 pm »
I want to use a simple pascal/lazarus program using an sqlite database without using an IDE, forms etc. One of the tables has so much entries that it does not fit into an array and I need to search into it. But I cannot find an example how to simply open a database, write and read to it and close it without using IDE elements.

Try this https://sqlitebrowser.org/ or the SQLite official command line tool, available here https://www.sqlite.org/download.html. Using them you can open and search SQLite database files.

JanRoza

  • Hero Member
  • *****
  • Posts: 643
    • http://www.silentwings.nl
Re: Straight forward sqlite database
« Reply #4 on: November 09, 2021, 02:36:56 pm »
Or study this demo project which I once wrote for a Dutch computer magazine.
There are some things in it I might do differently today but it shows you a lot of the possibilities in working with SQLite.
OS: Windows 10 (64 bit) / Linux Mint (64 bit)
Laz: Lazarus 2.2.4 FPC 3.2.2
       CodeTyphon 8.0 FPC 3.3.1

cdbc

  • Sr. Member
  • ****
  • Posts: 399
    • http://www.cdbc.dk
Re: Straight forward sqlite database
« Reply #5 on: November 09, 2021, 07:50:21 pm »
Hi
Maybe this can be of use....: https://github.com/cdbc-dk/bc_rtl/blob/master/bc_litedb.pas
Regards Benny
If it ain't broke, don't fix it ;)

 

TinyPortal © 2005-2018