Difference between revisions of "SQLdb Tutorial1"

From Lazarus wiki
Jump to navigationJump to search
m (Translation of a German snippet)
Line 150: Line 150:
 
</delphi>
 
</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.
 
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 serious problems can be appear. Da bei einer Client-Server-Anwendung üblicherweise Client und Server räumlich getrennt sind, ist meist nicht auf den ersten Blick ersichtlich, warum es zu einem Problem gekommen ist. 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 it is 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:
+
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>
 
<delphi>
 
begin
 
begin

Revision as of 11:24, 13 September 2010

Template:SQLdb Tutorial

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 (Components -> Installierte Packages einrichten ... -> SQLDBLaz 1.0). Furthermore you need Firebird (if possible version 2.0 or newer). The examples assume, that the standard settings (SYSDBA and masterkey) were not changed.

Example 1

First you should create a new Lazarus project. For the access to the database we need one TIBConnection, one TSQLTransaction and one TSQLQuery component from the 'SQLdb' tab in the component palette. The first component is specified for the access to Interbase or Firebird databases. The other two components can be used for all databases, which 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, that we need 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 also can do the connections in your source code. Change the 'Transaction' property of IBConnection1 to 'SQLTransaction1'. This cause also, that the 'Database' property of SQLTransaction1 is automatically changed 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 database employee.fdb 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 is granting the access to the data, he will check the authorisation via username and password. A serious database application will ask the user for both values, when the application is started, to forward them to the server in the appropriate moment. To simplify matters we use the object inspector again. Change the 'UserName' property to 'SYSDBA' and 'Password' to 'masterkey'. To check, if all settings so far are correct, we can set the 'Connected' property to 'True'. If the 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 to 'False').

Although the connection was successful, there were no data displayed. The reason is simple. We haven't told to the database server, which data he should return. The database employee.fdb contains several tables. If you don't know the structure of a database, you can use tools like FlameRobin, to display the contents. Even Lazarus 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

This instruction we have to assign 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 an other component (e.g. Edit1.Text).

The request of the data should start, when the user clicks on the button. With a double click on Button1 Lazarus 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 now enter the instructions, which are 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:

SQLQuery1.Close;

Because we can't be sure, if SQLQuery1 still contains any data, we call the 'Clear' procedure:

SQLQuery1.SQl.Clear;

Then we assign our SQL instruction to the 'SQL' property:

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

Now we need to establish the connection to the database, activating the transcation and open the query:

IBConnection1.Connected := True;
SQLTransaction1.Active := True;
SQLQuery1.Open;

You can leave the first two instructions, because they are done automatically by the third instruction (but this will not happen in the contrary case, when you cut the connection). If you would now compile the project, you could already see the data from the table 'CUSTOMER'. But a serious application will take care at the latest, when the application is closed, that all open connections to the database will be closed. Else the secondary effects would not be foreseeable. 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 contrary oder as during the opening:

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

Summary

Until 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