Editing/reading an existing database in Lazarus.

Forum > Database

(1/1)

Evgenius:
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:

--- Quote from: Evgenius 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!

--- End quote ---

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.

Dzandaa:
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->

Handoko:
@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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---unit Unit1; {$mode objfpc}{$H+} interface uses Classes, SysUtils, Forms, Controls, Graphics, Dialogs, ExtCtrls, StdCtrls, SQLite3Conn, SQLDB; type { TForm1 } TForm1 = class(TForm) btnCreate: TButton; btnUse: TButton; btnShow: TButton; btnShowBack: TButton; btnExact: TButton; btnPartial: TButton; btnAdd: TButton; btnAddBack: TButton; btnSave: TButton; btnDelete: TButton; edtProductID: TEdit; edtProductName: TEdit; edtProductPrice: TEdit; lblProductID: TLabel; lblProductName: TLabel; lblProductPrice: TLabel; mmoAllData: TMemo; pnlAdd: TPanel; pnlHome: TPanel; pnlShow: TPanel; procedure FormCreate(Sender: TObject); procedure btnCreateClick(Sender: TObject); procedure btnUseClick(Sender: TObject); procedure btnShowClick(Sender: TObject); procedure btnExactClick(Sender: TObject); procedure btnPartialClick(Sender: TObject); procedure btnAddClick(Sender: TObject); procedure btnDeleteClick(Sender: TObject); procedure btnAddBackClick(Sender: TObject); procedure btnShowBackClick(Sender: TObject); procedure edtProductIDExit(Sender: TObject); procedure edtProductPriceExit(Sender: TObject); procedure edtProductNameExit(Sender: TObject); procedure btnSaveClick(Sender: TObject); private FActiveDatabaseFile: string; function NoActiveDatabase: Boolean; end; var Form1: TForm1; implementation {$R *.lfm} { TForm1 } procedure TForm1.FormCreate(Sender: TObject);const Mono1 = 'Liberation Mono'; Mono2 = 'FreeMono'; Mono3 = 'Courier New';var S: string;begin FActiveDatabaseFile := ''; Caption := ''; Color := clGreen; Height := 256; Width := 320; pnlHome.BevelOuter := bvNone; pnlHome.Caption := ''; pnlHome.Color := $FFFFDD; pnlShow.Caption := ''; pnlShow.Color := $DDFFDD; pnlAdd.Caption := ''; pnlAdd.Color := $DDFFFF; mmoAllData.ScrollBars := ssAutoBoth; // Find an available monospaced font S := 'default'; if Screen.Fonts.IndexOf(Mono1) >= 0 then S := Mono1 else if Screen.Fonts.IndexOf(Mono2) >= 0 then S := Mono2 else if Screen.Fonts.IndexOf(Mono3) >= 0 then S := Mono3; mmoAllData.Font.Name := S; end; procedure TForm1.btnCreateClick(Sender: TObject);var Connection: TSQLite3Connection; NewFile: TSaveDialog; S: string;begin // Ask for the filename NewFile := TSaveDialog.Create(nil); case NewFile.Execute of True: begin S := NewFile.FileName; NewFile.Free; end; False: begin NewFile.Free; Exit; end; end; if (ExtractFileExt(S)) = '' then S := S + '.sqlite'; if FileExists(S) then begin ShowMessage('File already exists, process aborted.'); Exit; end; FActiveDatabaseFile := S; Caption := ExtractFileName(S); // Create the database and tblProduct Connection := TSQLite3Connection.Create(nil); Connection.DatabaseName := FActiveDatabaseFile; try Connection.CreateDB; Connection.Transaction := TSQLTransaction.Create(nil); Connection.StartTransaction; try Connection.ExecuteDirect( 'CREATE TABLE TBLPRODUCT ' + '(' + ' PRODUCTID INTEGER PRIMARY KEY,' + ' NAME CHAR(16) NOT NULL,' + ' PRICE REAL NOT NULL' + ');' ); Connection.Transaction.Commit; finally Connection.Transaction.Free; end; finally Connection.Free; end; end; procedure TForm1.btnUseClick(Sender: TObject);var UseFile: TOpenDialog; S: string;begin UseFile := TOpenDialog.Create(nil); case UseFile.Execute of True: begin S := UseFile.FileName; UseFile.Free; end; False: begin UseFile.Free; Exit; end; end; if not(ExtractFileExt(S) = '.sqlite') then begin ShowMessage('Only .sqlite files are supported.'); Exit; end; FActiveDatabaseFile := S; Caption := ExtractFileName(S);end; procedure TForm1.btnShowClick(Sender: TObject);var Connection: TSQLite3Connection; Query: TSQLQuery; S: string; i: Integer;begin if NoActiveDatabase then Exit; mmoAllData.Clear; for i := 260 downto 8 do begin pnlShow.Top := i; if Odd(i) then Sleep(1); Application.ProcessMessages; end; // Read, format and show the data Connection := TSQLite3Connection.Create(nil); Connection.DatabaseName := FActiveDatabaseFile; Connection.Transaction := TSQLTransaction.Create(nil); Connection.Open; Connection.StartTransaction; Query := TSQLQuery.Create(nil); Query.Database := Connection; Query.SQL.Text := 'SELECT * FROM TBLPRODUCT;'; try Query.Open; while not(Query.EOF) do begin S := Format('%0:4D %1:-17S %2:7.2F', [Query.FieldByName('PRODUCTID').AsInteger, Query.FieldByName('NAME').AsString, Query.FieldByName('PRICE').AsFloat]); mmoAllData.Lines.Add(S); Query.Next; end; Query.Close; Connection.Transaction.Commit; finally Query.Free; Connection.Transaction.Free; Connection.Free; end; end; procedure TForm1.btnExactClick(Sender: TObject);var Connection: TSQLite3Connection; Query: TSQLQuery; S: string;begin if NoActiveDatabase then Exit; if not(InputQuery('Exact match search', 'Product ID', S)) then Exit; // Do exact match search Connection := TSQLite3Connection.Create(nil); Connection.DatabaseName := FActiveDatabaseFile; Connection.Transaction := TSQLTransaction.Create(nil); Connection.Open; Connection.StartTransaction; Query := TSQLQuery.Create(nil); Query.Database := Connection; Query.SQL.Text := 'SELECT * FROM TBLPRODUCT WHERE PRODUCTID = ''' + S + ''';'; S := ''; try Query.Open; S := 'ProductID = ' + Query.FieldByName('PRODUCTID').AsString + LineEnding + 'Name = ' + Query.FieldByName('NAME').AsString + LineEnding + 'Price = ' + Format('%.2F', [Query.FieldByName('PRICE').AsFloat]); if Query.RecordCount <= 0 then S := 'No result.'; Query.Close; Connection.Transaction.Commit; finally Query.Free; Connection.Transaction.Free; Connection.Free; end; if not(S.IsEmpty) then ShowMessage(S); end; procedure TForm1.btnPartialClick(Sender: TObject);var Connection: TSQLite3Connection; Query: TSQLQuery; S: string; i: Integer;begin if NoActiveDatabase then Exit; if not(InputQuery('Exact match search', 'Name', S)) then Exit; mmoAllData.Clear; for i := 260 downto 8 do begin pnlShow.Top := i; if Odd(i) then Sleep(1); Application.ProcessMessages; end; // Do partial match search Connection := TSQLite3Connection.Create(nil); Connection.DatabaseName := FActiveDatabaseFile; Connection.Transaction := TSQLTransaction.Create(nil); Connection.Open; Connection.StartTransaction; Query := TSQLQuery.Create(nil); Query.Database := Connection; Query.SQL.Text := 'SELECT * FROM TBLPRODUCT WHERE NAME LIKE ' + LineEnding + '''%' + S + '%'';'; try Query.Open; while not(Query.EOF) do begin S := Format('%0:4D %1:-17S %2:7.2F', [Query.FieldByName('PRODUCTID').AsInteger, Query.FieldByName('NAME').AsString, Query.FieldByName('PRICE').AsFloat]); mmoAllData.Lines.Add(S); Query.Next; end; Query.Close; Connection.Transaction.Commit; finally Query.Free; Connection.Transaction.Free; Connection.Free; end; end; procedure TForm1.btnAddClick(Sender: TObject);var i: Integer;begin if NoActiveDatabase then Exit; edtProductID.Text := ''; edtProductName.Text := ''; edtProductPrice.Text := ''; for i := 330 downto 8 do begin pnlAdd.Left := i; if Odd(i) then Sleep(1); Application.ProcessMessages; end;end; procedure TForm1.btnDeleteClick(Sender: TObject);var Connection: TSQLite3Connection; Query: TSQLQuery; S: string; Found: Boolean;begin if NoActiveDatabase then Exit; if not(InputQuery('Delete a record', 'Product ID', S)) then Exit; Connection := TSQLite3Connection.Create(nil); Connection.DatabaseName := FActiveDatabaseFile; Connection.Transaction := TSQLTransaction.Create(nil); Connection.Open; Connection.StartTransaction; Query := TSQLQuery.Create(nil); Query.Database := Connection; // Check existence of the record Query.SQL.Text := 'SELECT * FROM TBLPRODUCT WHERE PRODUCTID = ''' + S + ''';'; Found := False; try Query.Open; if Query.RecordCount > 0 then Found := True; Query.Close; Connection.Transaction.Commit; case Found of True: // Delete the record begin Connection.ExecuteDirect('DELETE FROM TBLPRODUCT WHERE PRODUCTID = ' + '''' + S + ''';'); Connection.Transaction.Commit; end; False: ShowMessage('Item not found.'); end; finally Query.Free; Connection.Transaction.Free; Connection.Free; end; end; procedure TForm1.btnAddBackClick(Sender: TObject);var i: Integer;begin for i := 8 to 330 do begin pnlAdd.Left := i; if Odd(i) then Sleep(1); Application.ProcessMessages; end;end; procedure TForm1.btnShowBackClick(Sender: TObject);var i: Integer;begin for i := 8 to 260 do begin pnlShow.Top := i; if Odd(i) then Sleep(1); Application.ProcessMessages; end;end; procedure TForm1.btnSaveClick(Sender: TObject);var Connection: TSQLConnection; i: Integer; r: Real;begin // Validate ProductID if Length(edtProductID.Text) <= 0 then begin ShowMessage('ProductID cannot be empty.'); Exit; end; if not(TryStrToInt(edtProductID.Text, i)) then begin ShowMessage('Product ID must be number only.'); Exit; end; if (i < 1) or (i > 9999) then begin ShowMessage('Product ID must be >= 1 and <= 9999.'); Exit; end; // Validate ProductName if Length(edtProductName.Text) <= 0 then begin ShowMessage('Name cannot be empty.'); Exit; end; if Length(edtProductName.Text) > 16 then begin ShowMessage('Name can have maximum 16 characters.'); Exit; end; // Validate ProductPrice if not(TryStrToFloat(edtProductPrice.Text, r)) then begin ShowMessage('Price is not valid.'); Exit; end; if (r < 0) or (r > 9999) then begin ShowMessage('Price must be >= 0 and <= 9999.'); Exit; end; // Save the data Connection := TSQLite3Connection.Create(nil); Connection.DatabaseName := FActiveDatabaseFile; Connection.Transaction := TSQLTransaction.Create(nil); Connection.Open; Connection.StartTransaction; try Connection.ExecuteDirect( 'INSERT INTO TBLPRODUCT ' + '(PRODUCTID, NAME, PRICE) ' + 'VALUES (' + '''' + edtProductID.Text + ''', ' + '''' + edtProductName.Text + ''', ' + '''' + edtProductPrice.Text + ''');' ); Connection.Transaction.Commit; finally Connection.Transaction.Free; Connection.Free; end; edtProductID.Text := ''; edtProductName.Text := ''; edtProductPrice.Text := ''; end; procedure TForm1.edtProductIDExit(Sender: TObject);begin edtProductID.Text := Trim(edtProductID.Text);end; procedure TForm1.edtProductPriceExit(Sender: TObject);var Price: Real;begin if not(TryStrToFloat(edtProductPrice.Text, Price)) then Exit; edtProductPrice.Text := Format('%.2F', [Price]);end; procedure TForm1.edtProductNameExit(Sender: TObject);begin edtProductName.Text := Trim(edtProductName.Text);end; function TForm1.NoActiveDatabase: Boolean;begin Result := False; if not(FActiveDatabaseFile.IsEmpty) then Exit; ShowMessage('No active database.' + LineEnding + 'Please create a new database or use an existing one.'); Result := True;end; 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

--- Quote from: Evgenius on November 11, 2023, 11:10:30 pm ---... how do I open an EXISTING database and extract information using queries? Please, help me!

--- End quote ---

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.

Navigation

[0] Message Index