Straight forward sqlite database

[vdijken]

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]

Maybe this can help:
https://wiki.freepascal.org/SqlDBHowto

[Zvoni]

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.  

[ezlage]

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]

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.

[cdbc]

Hi
Maybe this can be of use....: https://github.com/cdbc-dk/bc_rtl/blob/master/bc_litedb.pas
Regards Benny