SQLdb Tutorial1

Introduction

This tutorial has the intention to show the usage of the SQLdb Package on the basis of practical examples. It is primarily targeted at newbies. If somebody is looking for basics about databases and SQL, he should read the corresponding books. For this tutorial I use Firebird with the example database employee.fdb.

Thanks to Joost and Michael. Without their help this tutorial probably never has come about.

Requirements

If possible you should use a recent Lazarus version (with FPC 2.2.2 or newer) for this tutorial. If the SQLdb package isn't already installed, you should do it now (Package -> Install/Uninstall packages ... -> SQLDBLaz 1.0).

Furthermore you need Firebird (if possible version 2.0 or newer). The examples assume that the standard settings (e.g user name SYSDBA and password masterkey) were not changed.

Example 1

First you should create a new Lazarus project. To get access to our database we need one TIBConnection, one TSQLTransaction and one TSQLQuery component from the 'SQLdb' tab in the component palette. TIBConnection is an Interbase/Firebird specific connection component. The other two components can be used for all databases that are supported by SQLdb.

To display the data we use a TDBGrid component, which can be found on the 'Data Controls' tab. To connect this component with the database components we need a TDatasource component from the 'Data Access' tab. To trigger the actions we use a TButton from the 'Standard' tab.

Now we have all components needed for the first example. You can enlarge the TDBGrid to have enough space to display all data.

Next we need to connect our components. A very simple way is to use the object inspector, but you can also do this in your source code. Change the 'Transaction' property of IBConnection1 to 'SQLTransaction1'. This causes the 'Database' property of SQLTransaction1 to automatically change to 'IBConnection1'. In the next step you should change the 'Database' property of SQLQuery1 to 'IBConnection1'. Lazarus automatically adds the value for the 'Transaction' property. Now we change the 'Dataset' property of Datasource1 to 'SQLQuery1'. Finally we change the 'Datasource' property of DBGrid1 to 'Datasource1'.

But how can we now show the data from our database on the screen? First we need to inform IBConnection1, where the employee.fdb database is located (usually in the .../examples/empbuild/ subdirectory of your Firebird installation). Again you have the choice, if you want to use the object inspector to assign the path or if you want to do it directly in your source code. We choose to use the object inspector: change the 'DatabaseName' property of IBConnection1 to the path to the employee.fdb file (e.g. C:\Program Files\Firebird\Firebird_2_0\examples\empbuild\EMPLOYEE.FDB). Before the database server grants access to the data, it will check the authorisation via username and password. A serious database application will ask the user for both values, when the application is started, and send these to the server wen connecting. However, to simplify matters we use the object inspector again to hard code these. Change the 'UserName' property to 'SYSDBA' and 'Password' to 'masterkey'. Now check, if all settings so far are correct: set the 'Connected' property to 'True'. If the database path isn't correct or if username or password are wrong, you will get an error message. If the connection was successful, you should cut it now (set it to 'False').

Although the connection was successful, no data was displayed. The reason is simple. We haven't told the database server which data to return: the employee.fdb database contains several tables, and we haven't told Firebird the table we want to see. If you don't know the structure of a database, you can use tools like FlameRobin, to display the contents. Lazarus also provides such a tool - the DataDesktop. You can find it in the /tools/lazdatadesktop/ subdirectory of Lazarus. Open the project lazdatadesktop.lpi and compile it (FPC 2.3.x is currently required) (of course you have saved our example project before).

The DataDesktop in action

Back to our example. We want to display all data from the table 'CUSTOMER'. The SQL instruction for that is:

select * from CUSTOMER

We need to assign this command to the 'SQL' property of SQLQuery1. In the source code of our project this would look like:

SQLQuery1.SQL.Text := 'select * from CUSTOMER';

The SQL instruction must be enclosed by single quotes. You also have the ability to assign the content of another component (e.g. Edit1.Text). This is not always a good idea; see Secure programming (a more advanced text) for details on SQL injection.

When the user clicks on the button on our form, data retrieval should start. Let's start coding this. Double click on Button1. Lazarus then creates the skeleton of the necessary procedure. In our source code we should find the following lines:

<pascal> procedure TForm1.Button1Click(Sender: TObject); begin

end; </pascal>

Between begin and end we must enter the instructions needed to display the data. The 'SQL' property of SQLQuery1 can only be changed, if SQLQuery1 is not active. That's why we close the component first: <pascal> SQLQuery1.Close; </pascal> We can't be sure, if SQLQuery1 still contains any data, so we call the 'Clear' procedure: <pascal> SQLQuery1.SQl.Clear; </pascal> Then we assign our SQL instruction to the 'SQL' property: <pascal> SQLQuery1.SQL.Text := 'select * from CUSTOMER'; </pascal> Now we need to establish the connection to the database, activate the transaction and open the query: <pascal> IBConnection1.Connected := True; SQLTransaction1.Active := True; SQLQuery1.Open; </pascal> You can omit the first two instructions, because they are done automatically by the third instruction (but this will not happen in the contrary case, when you close the connection). If you would now compile the project, you could already see the data from the 'CUSTOMER' table. However, a serious application must take care that all open database connections are properly closed when not needed anymore. Otherwise the secondary effects would not be foreseeable. So, we use the OnClose event of our form (create it with a double click in the object inspector):

<pascal> procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction); begin

end; </pascal>

To close the connection we use the reverse order compared to our opening code: <pascal> SQLQuery1.Close; SQLTransaction1.Active := False; IBConnection1.Connected := False; </pascal>

Summary

Up to now we have learned, how to connect to a Firebird database using the SQLdb package and how to display the content of a table on the screen.

Enhancement of example 1

If you followed the previous steps, then your code should look like: <delphi> unit Unit1;

{$mode objfpc}{$H+}

interface

uses

 Classes, SysUtils, LResources, Forms, Controls, Graphics, Dialogs,
 IBConnection, sqldb, DBGrids, db, StdCtrls;

type

 { TForm1 }

 TForm1 = class(TForm)
   Button1: TButton;
   Datasource1: TDatasource;
   DBGrid1: TDBGrid;
   IBConnection1: TIBConnection;
   SQLQuery1: TSQLQuery;
   SQLTransaction1: TSQLTransaction;
   procedure Button1Click(Sender: TObject);
   procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
 private
   { private declarations }
 public
   { public declarations }
 end; 

var

 Form1: TForm1; 

implementation

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject); begin

 SQLQuery1.Close;
 SQLQuery1.SQL.Clear;
 SQLQuery1.SQL.Text:= 'select * from CUSTOMER';
 IBConnection1.Connected:= True;
 SQLTransaction1.Active:= True;
 SQLQuery1.Open;

end;

procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction); begin

 SQLQuery1.Close;
 SQLTransaction1.Active:= False;
 IBConnection1.Connected:= False;

end;

initialization

 {$I unit1.lrs}

end. </delphi>

But you will need the complete content of a table in very unusual cases. Let's assume, that only the customers from the USA should be displayed. Therefor the SQL instruction would look like:

select * from CUSTOMER where COUNTRY = 'USA'

But for two reasons we will not use this instruction for our example application: First there is a problem with the usage of the single quote. The compiler would interpret the quote before USA as a closing quote (the first quote is before the select from...) and so the SQL instruction would become invalid. The second and more important reason is the fact, that during the design time of the application it is not known, which constraint later should be considered. We don't want to limit the flexibility of the user. For that purpose we replace at first 'USA' by a placeholder:

select * from CUSTOMER where COUNTRY = :COUNTRY

The placeholder is marked by the leading colon. To allow the user to enter a value for the filter, we place a TEdit component (tab 'Standard' in the component palette) on our form. Delete the value of the 'Text' property. With the 'Params' property of TSQLQuery we can now replace the placeholder with the text, which was entered in TEdit:

SQLQuery1.Params.ParamByName('COUNTRY').AsString := Edit1.Text;

The parameter can be specified by his position and by his name. The last primarily should improve the readability of the source code. Overall the procedure should now look like: <delphi> procedure TForm1.Button1Click(Sender: TObject); begin

 SQLQuery1.Close;
 SQLQuery1.SQL.Clear;
 SQLQuery1.SQL.Text:= 'select * from CUSTOMER where COUNTRY = :COUNTRY';
 SQLQuery1.Params.ParamByName('COUNTRY').AsString := Edit1.Text;
 IBConnection1.Connected:= True;
 SQLTransaction1.Active:= True;
 SQLQuery1.Open;

end; </delphi> Now you can play around a bit with the settings. If you enter a value, that is not stored in the database, then a empty table is shown. But also more serious problems can appear. Because client and server are usually spatially separated, it's often not clear at first sight why a problem occurred. Was the server shut down or has somebody unplugged the connector? The access to a database should therefore always be integrated in a try ... except and/or try ... finally loop. Only that way is it secured, that database errors can be handled and the user isn't left out in the rain. A rudimental routine for our example application could look as follows: <delphi> begin

 try
   SQLQuery1.Close;
   ...
   SQLQuery1.Open;
 except
   on EDatabaseError do
   begin
     MessageDlg('Error','A database error has occured.',mtError,[mbOK],0);
     Edit1.Text:=;
   end;
 end;

end; </delphi> But here only a simple message is displayed.

See also

• Firebird in action: another Firebird tutorial/how to.
• SQLdb Package: information about the SQLdb package
• SQLdb Programming Reference: an overview of the interaction of the SQLdb database components
• SqlDBHowto/nl: informationen about the usage of the SQLdb package (Dutch language)
• Working With TSQLQuery: information about TSQLQuery