Recent

Author Topic: Editing/reading an existing database in Lazarus.  (Read 4851 times)

Evgenius

  • Newbie
  • Posts: 1
Editing/reading an existing database in Lazarus.
« on: November 11, 2023, 11:10:30 pm »
In my simple program I have an array of records which represent products. The records include prices, quantities, etc. All this information is being input with read(). However, I want to automatize the input of products’ price using a database. Namely, I have a sqlite3 database with names and corresponding prices of products. So when I input product name with read(), I want to connect to this database, get the corresponding products’s price and input it into my record. In the web I found many ways of creating new databases, but how do I open an EXISTING database and extract information using queries? Please, help me!

dsiders

  • Hero Member
  • *****
  • Posts: 1013
Re: Editing/reading an existing database in Lazarus.
« Reply #1 on: November 12, 2023, 03:41:52 am »
In my simple program I have an array of records which represent products. The records include prices, quantities, etc. All this information is being input with read(). However, I want to automatize the input of products’ price using a database. Namely, I have a sqlite3 database with names and corresponding prices of products. So when I input product name with read(), I want to connect to this database, get the corresponding products’s price and input it into my record. In the web I found many ways of creating new databases, but how do I open an EXISTING database and extract information using queries? Please, help me!

FPC has demo programs in source/packages/fcl-db/examples.
Lazarus has UI-based demos in  examples/database/.
The wiki has more information at https://wiki.freepascal.org/SQLite.
Preview Lazarus 3.99 documentation at: https://dsiders.gitlab.io/lazdocsnext

Dzandaa

  • Full Member
  • ***
  • Posts: 201
  • From C# to Lazarus
Re: Editing/reading an existing database in Lazarus.
« Reply #2 on: November 12, 2023, 01:35:07 pm »
Hi,

I've created a little template to create a SQLite DB Skeleton.

I's not very fancy, but working.

Dezip it and put the folder "CreateDB" in your Templates directory (Tools->Project templates option) then in Lazarus:
File->New project from template->SQLite DB application

Fill the Dialog, with name and Path of your new program, main unit, search unit (data unit) main form, data form and Table name.

It's just a "contacts" DB with Firstname, Street, etc...

Not very clean, but good for an example

Hope this help.

B->


Dzandaa

Handoko

  • Hero Member
  • *****
  • Posts: 5103
  • My goal: build my own game engine using Lazarus
Re: Editing/reading an existing database in Lazarus.
« Reply #3 on: November 12, 2023, 04:14:49 pm »
@Dzandaa
Nice work, thank you for sharing it.

----------

Dzandaa showed a fully working sqlite demo, which used TDBNavigator and other drag-and drop visual components. There is always more than one way to do the same thing. Here I want to show how it can be done fully using code (not drag-and-drop approach) and I tried to keep global variables usage as little as possible, so readers can follow the flow of the program easier.

Code: Pascal  [Select][+][-]
  1. unit Unit1;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.   Classes, SysUtils, Forms, Controls, Graphics, Dialogs, ExtCtrls,
  9.   StdCtrls, SQLite3Conn, SQLDB;
  10.  
  11. type
  12.  
  13.   { TForm1 }
  14.  
  15.   TForm1 = class(TForm)
  16.     btnCreate: TButton;
  17.     btnUse: TButton;
  18.     btnShow: TButton;
  19.     btnShowBack: TButton;
  20.     btnExact: TButton;
  21.     btnPartial: TButton;
  22.     btnAdd: TButton;
  23.     btnAddBack: TButton;
  24.     btnSave: TButton;
  25.     btnDelete: TButton;
  26.     edtProductID: TEdit;
  27.     edtProductName: TEdit;
  28.     edtProductPrice: TEdit;
  29.     lblProductID: TLabel;
  30.     lblProductName: TLabel;
  31.     lblProductPrice: TLabel;
  32.     mmoAllData: TMemo;
  33.     pnlAdd: TPanel;
  34.     pnlHome: TPanel;
  35.     pnlShow: TPanel;
  36.     procedure FormCreate(Sender: TObject);
  37.     procedure btnCreateClick(Sender: TObject);
  38.     procedure btnUseClick(Sender: TObject);
  39.     procedure btnShowClick(Sender: TObject);
  40.     procedure btnExactClick(Sender: TObject);
  41.     procedure btnPartialClick(Sender: TObject);
  42.     procedure btnAddClick(Sender: TObject);
  43.     procedure btnDeleteClick(Sender: TObject);
  44.     procedure btnAddBackClick(Sender: TObject);
  45.     procedure btnShowBackClick(Sender: TObject);
  46.     procedure edtProductIDExit(Sender: TObject);
  47.     procedure edtProductPriceExit(Sender: TObject);
  48.     procedure edtProductNameExit(Sender: TObject);
  49.     procedure btnSaveClick(Sender: TObject);
  50.   private
  51.     FActiveDatabaseFile: string;
  52.     function NoActiveDatabase: Boolean;
  53.   end;
  54.  
  55. var
  56.   Form1: TForm1;
  57.  
  58. implementation
  59.  
  60. {$R *.lfm}
  61.  
  62. { TForm1 }
  63.  
  64. procedure TForm1.FormCreate(Sender: TObject);
  65. const
  66.   Mono1 = 'Liberation Mono';
  67.   Mono2 = 'FreeMono';
  68.   Mono3 = 'Courier New';
  69. var
  70.   S: string;
  71. begin
  72.  
  73.   FActiveDatabaseFile   := '';
  74.   Caption               := '';
  75.   Color                 := clGreen;
  76.   Height                := 256;
  77.   Width                 := 320;
  78.   pnlHome.BevelOuter    := bvNone;
  79.   pnlHome.Caption       := '';
  80.   pnlHome.Color         := $FFFFDD;
  81.   pnlShow.Caption       := '';
  82.   pnlShow.Color         := $DDFFDD;
  83.   pnlAdd.Caption        := '';
  84.   pnlAdd.Color          := $DDFFFF;
  85.   mmoAllData.ScrollBars := ssAutoBoth;
  86.  
  87.   // Find an available monospaced font
  88.   S := 'default';
  89.   if Screen.Fonts.IndexOf(Mono1) >= 0 then
  90.     S := Mono1
  91.   else
  92.     if Screen.Fonts.IndexOf(Mono2) >= 0 then
  93.       S := Mono2
  94.     else
  95.       if Screen.Fonts.IndexOf(Mono3) >= 0 then
  96.         S := Mono3;
  97.   mmoAllData.Font.Name := S;
  98.  
  99. end;
  100.  
  101. procedure TForm1.btnCreateClick(Sender: TObject);
  102. var
  103.   Connection: TSQLite3Connection;
  104.   NewFile:    TSaveDialog;
  105.   S:          string;
  106. begin
  107.  
  108.   // Ask for the filename
  109.   NewFile := TSaveDialog.Create(nil);
  110.   case NewFile.Execute of
  111.     True:
  112.       begin
  113.         S := NewFile.FileName;
  114.         NewFile.Free;
  115.       end;
  116.     False:
  117.       begin
  118.         NewFile.Free;
  119.         Exit;
  120.       end;
  121.   end;
  122.   if (ExtractFileExt(S)) = '' then
  123.     S := S + '.sqlite';
  124.   if FileExists(S) then
  125.   begin
  126.     ShowMessage('File already exists, process aborted.');
  127.     Exit;
  128.   end;
  129.   FActiveDatabaseFile := S;
  130.   Caption             := ExtractFileName(S);
  131.  
  132.   // Create the database and tblProduct
  133.   Connection              := TSQLite3Connection.Create(nil);
  134.   Connection.DatabaseName := FActiveDatabaseFile;
  135.   try
  136.     Connection.CreateDB;
  137.     Connection.Transaction := TSQLTransaction.Create(nil);
  138.     Connection.StartTransaction;
  139.     try
  140.       Connection.ExecuteDirect(
  141.         'CREATE TABLE TBLPRODUCT '         +
  142.         '('                                +
  143.         '  PRODUCTID INTEGER PRIMARY KEY,' +
  144.         '  NAME CHAR(16) NOT NULL,'        +
  145.         '  PRICE REAL NOT NULL'            +
  146.         ');'
  147.         );
  148.       Connection.Transaction.Commit;
  149.     finally
  150.       Connection.Transaction.Free;
  151.     end;
  152.   finally
  153.     Connection.Free;
  154.   end;
  155.  
  156. end;
  157.  
  158. procedure TForm1.btnUseClick(Sender: TObject);
  159. var
  160.   UseFile: TOpenDialog;
  161.   S:       string;
  162. begin
  163.   UseFile := TOpenDialog.Create(nil);
  164.   case UseFile.Execute of
  165.     True:
  166.       begin
  167.         S := UseFile.FileName;
  168.         UseFile.Free;
  169.       end;
  170.     False:
  171.       begin
  172.         UseFile.Free;
  173.         Exit;
  174.       end;
  175.   end;
  176.   if not(ExtractFileExt(S) = '.sqlite') then
  177.   begin
  178.     ShowMessage('Only .sqlite files are supported.');
  179.     Exit;
  180.   end;
  181.   FActiveDatabaseFile := S;
  182.   Caption             := ExtractFileName(S);
  183. end;
  184.  
  185. procedure TForm1.btnShowClick(Sender: TObject);
  186. var
  187.   Connection: TSQLite3Connection;
  188.   Query:      TSQLQuery;
  189.   S:          string;
  190.   i:          Integer;
  191. begin
  192.  
  193.   if NoActiveDatabase then Exit;
  194.  
  195.   mmoAllData.Clear;
  196.   for i := 260 downto 8 do
  197.   begin
  198.     pnlShow.Top := i;
  199.     if Odd(i) then Sleep(1);
  200.     Application.ProcessMessages;
  201.   end;
  202.  
  203.   // Read, format and show the data
  204.   Connection              := TSQLite3Connection.Create(nil);
  205.   Connection.DatabaseName := FActiveDatabaseFile;
  206.   Connection.Transaction  := TSQLTransaction.Create(nil);
  207.   Connection.Open;
  208.   Connection.StartTransaction;
  209.   Query          := TSQLQuery.Create(nil);
  210.   Query.Database := Connection;
  211.   Query.SQL.Text := 'SELECT * FROM TBLPRODUCT;';
  212.   try
  213.     Query.Open;
  214.     while not(Query.EOF) do
  215.     begin
  216.       S := Format('%0:4D %1:-17S %2:7.2F',
  217.              [Query.FieldByName('PRODUCTID').AsInteger,
  218.              Query.FieldByName('NAME').AsString,
  219.              Query.FieldByName('PRICE').AsFloat]);
  220.       mmoAllData.Lines.Add(S);
  221.       Query.Next;
  222.     end;
  223.     Query.Close;
  224.     Connection.Transaction.Commit;
  225.   finally
  226.     Query.Free;
  227.     Connection.Transaction.Free;
  228.     Connection.Free;
  229.   end;
  230.  
  231. end;
  232.  
  233. procedure TForm1.btnExactClick(Sender: TObject);
  234. var
  235.   Connection: TSQLite3Connection;
  236.   Query:      TSQLQuery;
  237.   S:          string;
  238. begin
  239.  
  240.   if NoActiveDatabase then Exit;
  241.   if not(InputQuery('Exact match search', 'Product ID', S)) then Exit;
  242.  
  243.   // Do exact match search
  244.   Connection              := TSQLite3Connection.Create(nil);
  245.   Connection.DatabaseName := FActiveDatabaseFile;
  246.   Connection.Transaction  := TSQLTransaction.Create(nil);
  247.   Connection.Open;
  248.   Connection.StartTransaction;
  249.   Query          := TSQLQuery.Create(nil);
  250.   Query.Database := Connection;
  251.   Query.SQL.Text := 'SELECT * FROM TBLPRODUCT WHERE PRODUCTID = ''' + S + ''';';
  252.   S              := '';
  253.   try
  254.     Query.Open;
  255.     S := 'ProductID = ' + Query.FieldByName('PRODUCTID').AsString + LineEnding +
  256.          'Name = ' +  Query.FieldByName('NAME').AsString + LineEnding +
  257.          'Price = ' + Format('%.2F', [Query.FieldByName('PRICE').AsFloat]);
  258.     if Query.RecordCount <= 0 then
  259.       S := 'No result.';
  260.     Query.Close;
  261.     Connection.Transaction.Commit;
  262.   finally
  263.     Query.Free;
  264.     Connection.Transaction.Free;
  265.     Connection.Free;
  266.   end;
  267.   if not(S.IsEmpty) then
  268.     ShowMessage(S);
  269.  
  270. end;
  271.  
  272. procedure TForm1.btnPartialClick(Sender: TObject);
  273. var
  274.   Connection: TSQLite3Connection;
  275.   Query:      TSQLQuery;
  276.   S:          string;
  277.   i:          Integer;
  278. begin
  279.  
  280.   if NoActiveDatabase then Exit;
  281.   if not(InputQuery('Exact match search', 'Name', S)) then Exit;
  282.  
  283.   mmoAllData.Clear;
  284.   for i := 260 downto 8 do
  285.   begin
  286.     pnlShow.Top := i;
  287.     if Odd(i) then Sleep(1);
  288.     Application.ProcessMessages;
  289.   end;
  290.  
  291.   // Do partial match search
  292.   Connection              := TSQLite3Connection.Create(nil);
  293.   Connection.DatabaseName := FActiveDatabaseFile;
  294.   Connection.Transaction  := TSQLTransaction.Create(nil);
  295.   Connection.Open;
  296.   Connection.StartTransaction;
  297.   Query          := TSQLQuery.Create(nil);
  298.   Query.Database := Connection;
  299.   Query.SQL.Text := 'SELECT * FROM TBLPRODUCT WHERE NAME LIKE ' + LineEnding +
  300.                     '''%' + S + '%'';';
  301.   try
  302.     Query.Open;
  303.     while not(Query.EOF) do
  304.     begin
  305.       S := Format('%0:4D %1:-17S %2:7.2F',
  306.              [Query.FieldByName('PRODUCTID').AsInteger,
  307.              Query.FieldByName('NAME').AsString,
  308.              Query.FieldByName('PRICE').AsFloat]);
  309.       mmoAllData.Lines.Add(S);
  310.       Query.Next;
  311.     end;
  312.     Query.Close;
  313.     Connection.Transaction.Commit;
  314.   finally
  315.     Query.Free;
  316.     Connection.Transaction.Free;
  317.     Connection.Free;
  318.   end;
  319.  
  320. end;
  321.  
  322. procedure TForm1.btnAddClick(Sender: TObject);
  323. var
  324.   i: Integer;
  325. begin
  326.   if NoActiveDatabase then Exit;
  327.   edtProductID.Text    := '';
  328.   edtProductName.Text  := '';
  329.   edtProductPrice.Text := '';
  330.   for i := 330 downto 8 do
  331.   begin
  332.     pnlAdd.Left := i;
  333.     if Odd(i) then Sleep(1);
  334.     Application.ProcessMessages;
  335.   end;
  336. end;
  337.  
  338. procedure TForm1.btnDeleteClick(Sender: TObject);
  339. var
  340.   Connection: TSQLite3Connection;
  341.   Query:      TSQLQuery;
  342.   S:          string;
  343.   Found:      Boolean;
  344. begin
  345.  
  346.   if NoActiveDatabase then Exit;
  347.   if not(InputQuery('Delete a record', 'Product ID', S)) then Exit;
  348.  
  349.   Connection              := TSQLite3Connection.Create(nil);
  350.   Connection.DatabaseName := FActiveDatabaseFile;
  351.   Connection.Transaction  := TSQLTransaction.Create(nil);
  352.   Connection.Open;
  353.   Connection.StartTransaction;
  354.   Query          := TSQLQuery.Create(nil);
  355.   Query.Database := Connection;
  356.  
  357.   // Check existence of the record
  358.   Query.SQL.Text := 'SELECT * FROM TBLPRODUCT WHERE PRODUCTID = ''' + S + ''';';
  359.   Found          := False;
  360.   try
  361.     Query.Open;
  362.     if Query.RecordCount > 0 then
  363.       Found := True;
  364.     Query.Close;
  365.     Connection.Transaction.Commit;
  366.     case Found of
  367.       True: // Delete the record
  368.         begin
  369.           Connection.ExecuteDirect('DELETE FROM TBLPRODUCT WHERE PRODUCTID = ' +
  370.                                    '''' + S + ''';');
  371.           Connection.Transaction.Commit;
  372.         end;
  373.       False:
  374.         ShowMessage('Item not found.');
  375.     end;
  376.   finally
  377.     Query.Free;
  378.     Connection.Transaction.Free;
  379.     Connection.Free;
  380.   end;
  381.  
  382. end;
  383.  
  384. procedure TForm1.btnAddBackClick(Sender: TObject);
  385. var
  386.   i: Integer;
  387. begin
  388.   for i := 8 to 330 do
  389.   begin
  390.     pnlAdd.Left := i;
  391.     if Odd(i) then Sleep(1);
  392.     Application.ProcessMessages;
  393.   end;
  394. end;
  395.  
  396. procedure TForm1.btnShowBackClick(Sender: TObject);
  397. var
  398.   i: Integer;
  399. begin
  400.   for i := 8 to 260 do
  401.   begin
  402.     pnlShow.Top := i;
  403.     if Odd(i) then Sleep(1);
  404.     Application.ProcessMessages;
  405.   end;
  406. end;
  407.  
  408. procedure TForm1.btnSaveClick(Sender: TObject);
  409. var
  410.   Connection: TSQLConnection;
  411.   i:          Integer;
  412.   r:          Real;
  413. begin
  414.  
  415.   // Validate ProductID
  416.   if Length(edtProductID.Text) <= 0 then
  417.   begin
  418.     ShowMessage('ProductID cannot be empty.');
  419.     Exit;
  420.   end;
  421.   if not(TryStrToInt(edtProductID.Text, i)) then
  422.   begin
  423.     ShowMessage('Product ID must be number only.');
  424.     Exit;
  425.   end;
  426.   if (i < 1) or (i > 9999) then
  427.   begin
  428.     ShowMessage('Product ID must be >= 1 and <= 9999.');
  429.     Exit;
  430.   end;
  431.  
  432.   // Validate ProductName
  433.   if Length(edtProductName.Text) <= 0 then
  434.   begin
  435.     ShowMessage('Name cannot be empty.');
  436.     Exit;
  437.   end;
  438.   if Length(edtProductName.Text) > 16 then
  439.   begin
  440.     ShowMessage('Name can have maximum 16 characters.');
  441.     Exit;
  442.   end;
  443.  
  444.   // Validate ProductPrice
  445.   if not(TryStrToFloat(edtProductPrice.Text, r)) then
  446.   begin
  447.     ShowMessage('Price is not valid.');
  448.     Exit;
  449.   end;
  450.   if (r < 0) or (r > 9999) then
  451.   begin
  452.     ShowMessage('Price must be >= 0 and <= 9999.');
  453.     Exit;
  454.   end;
  455.  
  456.   // Save the data
  457.   Connection              := TSQLite3Connection.Create(nil);
  458.   Connection.DatabaseName := FActiveDatabaseFile;
  459.   Connection.Transaction  := TSQLTransaction.Create(nil);
  460.   Connection.Open;
  461.   Connection.StartTransaction;
  462.   try
  463.     Connection.ExecuteDirect(
  464.       'INSERT INTO TBLPRODUCT '            +
  465.       '(PRODUCTID, NAME, PRICE) '          +
  466.       'VALUES ('                           +
  467.       '''' + edtProductID.Text    + ''', ' +
  468.       '''' + edtProductName.Text  + ''', ' +
  469.       '''' + edtProductPrice.Text + ''');'
  470.       );
  471.     Connection.Transaction.Commit;
  472.   finally
  473.     Connection.Transaction.Free;
  474.     Connection.Free;
  475.   end;
  476.  
  477.   edtProductID.Text    := '';
  478.   edtProductName.Text  := '';
  479.   edtProductPrice.Text := '';
  480.  
  481. end;
  482.  
  483. procedure TForm1.edtProductIDExit(Sender: TObject);
  484. begin
  485.   edtProductID.Text := Trim(edtProductID.Text);
  486. end;
  487.  
  488. procedure TForm1.edtProductPriceExit(Sender: TObject);
  489. var
  490.   Price: Real;
  491. begin
  492.   if not(TryStrToFloat(edtProductPrice.Text, Price)) then Exit;
  493.    edtProductPrice.Text := Format('%.2F', [Price]);
  494. end;
  495.  
  496. procedure TForm1.edtProductNameExit(Sender: TObject);
  497. begin
  498.   edtProductName.Text := Trim(edtProductName.Text);
  499. end;
  500.  
  501. function TForm1.NoActiveDatabase: Boolean;
  502. begin
  503.   Result := False;
  504.   if not(FActiveDatabaseFile.IsEmpty) then Exit;
  505.   ShowMessage('No active database.' + LineEnding +
  506.               'Please create a new database or use an existing one.');
  507.   Result := True;
  508. end;
  509.  
  510. end.

  • Exact match search, see line #233.
  • Partial match search, see line #272.
  • "Garbage in, garbage out" ... it is always good to validate user inputs before saving them, see line #415 ... #454.
  • Format function is very useful, see line #216.
  • Simple way to find a monospace font, works on Ubuntu, Win7 and WinXP. See line #87 ... #96.
  • Simple animation effect, see line #388 ... #393.
For anyone who wants to learn Sqlite or SQL, I recommend these:
https://wiki.freepascal.org/SQLite
https://wiki.freepascal.org/SqlDBHowto
https://www.w3schools.com/sql/default.asp

... how do I open an EXISTING database and extract information using queries? Please, help me!

For database operations that do not return data, you use TConnection.ExecuteDirect, for example see line #140. For database operations that give data in return you use TQuery.SQL.Text, for example see line #211.

To read the related field of the return data, you use:
- TQuery.FieldByName(FieldName).AsString
- TQuery.FieldByName(FieldName).AsInteger
- TQuery.FieldByName(FieldName).AsFloat
- TQuery.FieldByName(FieldName).AsByte
- TQuery.FieldByName(FieldName).AsBoolean
- TQuery.FieldByName(FieldName).AsInteger

Before you can read the record you must call TQuery.Open, see line #213. For result that has several records, you can check it by using TQuery.EOF, see line #214. To move the the next record you call TQuery.Next, see line #221.

You have to prepare connection and transaction properly before starting any database operation, for example see line #457 ... #461. And if the operation need to use TQuery, you have create the query and link it to the database before using it, see line #209 .. #210. And don't forget to free all the objects after the operation, see line #223 .. #228.
« Last Edit: November 13, 2023, 07:59:16 am by Handoko »

 

TinyPortal © 2005-2018