SQLdb Tutorial1/fr

Introduction

Le présent tutoriel va vous montrer à l'aide d'exemples comment mettre en pratique le SQLdb Package. Il a été conçu pour des débutants. Si vous rechercher de l'aide pour les bases de données et SQL, vous n'êtes pas au bon endroit. C'est Firebird qui est mise en oeuvre ici avec la base de données employee.fdb. Vous pouvez utiliser una autre base. Les modifications nécessaires seront décrites au fur et à mesure.

Si ce texte vous semble long, c'est que beaucoup d'explications sont données pour chacuen des lignes de code nécessaires. Au fond, la quantité de chose à taper n'est pas si grande qu'il y parait. Les développeurs expérimentés peuvent jeter un œil rapide aux instructions, ils comprendront de quoi il retourne. Vous pouvez également vous contenter de la partie qui se termine par Basic example. Vous disposerez d'un programme fonctionnel.

Vous avez, devant les yeux, une traduction de la version anglaise du tutoriel German tutorial de Swen, qui a été enrichie, en particulier après Basic example. Swen souhaite que la version allemande reste tel que. Si c'est une problème pour vous, vous pouvez la renommer et entreprendre une nouvelle traduction allemande.

De Swen: Merci à Joost et Michael, sans qui tout cela n'aurait probablement pas été possible.

Prérequis

Si vous le pouvez, utiliser une version récente de Lazarus version (FPC 2.2.2 ou supérieure). Si le paquet SQLdb n'est pas installé, faites-le maintenant (Package -> Install/Uninstall packages ... -> SQLDBLaz 1.0.1).

Vous devez disposer également d'une base de données relationnelle SQL, comme Firebird (si possible en version 2.0 ou supérieure). Le plus simple est d'utiliser les valeurs par défaut (par ex. utilisateur : SYSDBA et mot de passer masterkey) et la base exemple 'employee'.

Vous pouvez préférer une autre base de données (MySQL, PostgreSQL, Oracle, SQLite ou une autre base interfacée par ODBC) : il vous faudra seulement disposer de la bonne structure base/table (voir ci-dessous) et utiliser le TSQLConnector approprié (voir ci-dessous également). Si les transactions de votre base sont très différentes, merci de documenter la section correspondante. Les notes au sujet de SQLite ont été commencé.

Les biblothèques Firebird sous Windows

Sous Windows, il vous faudra les DLLs Firebird, éventuellement dans votre répertoire système, mais de préférence dans le répertoire de votre projet (pendant le développement, dans l'IDE) et dans le répertoire de sortie où est produit l'exécutable (pour faire tourner le programme compilé). Vous pouvez trouver ces DLLs sur cette page : Firebird Embedded 2.5 [1]. Décompressez les fichiers dans votre répertoire applicatif :

fbembed.dll
firebird.msg
ib_util.dll
icudt30.dll
icuin30.dll
icuuc30.dll
Microsoft.VC80.CRT.manifest
msvcp80.dll
msvcr80.dll

Renommez fbembed.dll en fbclient.dll (c'est le nom ordinaire du client Firebird). La DLL fournie peut également faire fonction de client ordinaire Firebird.

Assurez-vous que la base de données employee.fdb se trouve bien dans le répertoire de votre projet.

Enfin, recompilez une fois votre projet (même vide) pour créer le répertoire de sortie et y copier les DLLs.

Les bases de données Firebird sur les autres systèmes

Sous Linux/OSX, il vous faut également les bibliothèques partagées du client Firebird. Sous Linux, vous pouvez utiliser l'utilitaire de votre distribution pour installer les paquets, comme sous Debian : <bash> aptitude install libfbclient2 </bash> ou <bash> sudo apt-get -yqq install libfbclient2 </bash>

Si Firebird ou employee.fdb ne sont pas installés

Si vous ne disposez pas de la base de données exemple 'employee' ou si vous mettez en œuvre une autre base de données, voici ce dont la table dont vous devrez disposer pour notre exemple :

CREATE TABLE CUSTOMER
(
  CUST_NO INTEGER NOT NULL,
  CUSTOMER VARCHAR(25) NOT NULL,
  CITY VARCHAR(25),
  COUNTRY VARCHAR(15),
  CONSTRAINT INTEG_60 PRIMARY KEY (CUST_NO)
);

Voici un peu de données pour qu'on voit quelque chose :

INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('1', 'Lapinou software', 'Lanion', 'France');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2', 'VC Technologies', 'Dallas', 'USA');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('3', 'Klämpfl, Van Canneyt and Co.', 'Boston', 'USA');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('4', 'Felipe Bank', 'Manchester', 'England');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('5', 'Joost Systems, LTD.', 'Central Hong Kong', 'Hong Kong');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('6', 'Van der Voort Int.', 'Ottawa', 'Canada');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('7', 'Mrs. Mauvais', 'Pebble Beach', 'USA');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('8', 'Asinine Vacation Rentals', 'Lihue', 'USA');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('9', 'Fax', 'Turtle Island', 'Fiji');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('10', 'FPC Corporation', 'Tokyo', 'Japan');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('11', 'Dynamic Intelligence Corp', 'Zurich', 'Switzerland');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('12', '3D-Pad Corp.', 'Paris', 'France');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('13', 'Swen Export, Ltd.', 'Milan', 'Italy');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('14', 'Graeme Consulting', 'Brussels', 'Belgium');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('15', 'Klenin Inc.', 'Den Haag', 'Netherlands');

Merci de créer la base, la table et d'insérer les données dans votre environnement.

SQLite

Si vous utilisez SQLite, vous pouvez créer la base susmentionnée dans le répertoire de votre projet en lançant le code suivant :

sqlite employee.sqlite

Il suffit maintenant de copier/coller les instructions 'CREATE TABLE' et 'INSERT' ci-dessus. Pour tester si les données sont correctement entrée, utilisez la requête :

select * from customer;

Terminer la session à l'aide de la commande : .quit Un fichier du nom de 'employee.sqlite' devrait être disponible dans le répertoire de votre projet.

Assurez-vous que les bibliothèques sqlite (dll sous Windows et so sous Linux) sont installées (Par ex. sous Windows, 'sqlite3.dll' devrait se trouver dans le répertoire de votre projet).

Compilez une fois votre projet, même vide, pour que soit créer le répertoire de sortie et, sous Windows, copier les DLLs et la base 'employee.sqlite' dans ce répertoire.

PostgreSQL

La présente section suppose que vous utilisez un serveur Linux et le shell. Des étapes similaires aboutiront au même résultat sous Windows avec un outil graphique comme pgadmin. Connectez-vous à votre serveur et basculez sous le compte postgres :

su - postgres -c psql # Démarrage immédiat de l'interpréteur SQL psql

Create a user for the database and the tables:

CREATE USER employee WITH PASSWORD 'hellopassword'; -- Bien sûr, adaptez le mot de passe à votre convenance
-- Un message comme 'CREATE ROLE' devrait sanctionner votre succès.
-- Pour changer votre mot de passe a posteriori, utilisez quelque chose comme :
-- alter user employee with password '<newpasswordhere>';
-- Supprimons la date d'expiration du mot de passe. Ce n'est pas indispensable :
ALTER USER employee VALID UNTIL 'infinity'; --password never expires
-- Maintenant, ajoutons un peu de sécurité,
-- interdisons à l'utilisateur de créer une base de données ou d'autres comptes utilisateurs :
ALTER USER employee NOCREATEDB NOCREATEUSER; -- Restriction de la création d'objet
-- La réponse devrait être quelque chose comme 'ALTER ROLE'.
-- Création de notre base de données :
CREATE DATABASE employee;
-- Réponse : 'CREATE DATABASE'.
-- Assignons tous les droits sur la base funambol à l'utilisateur 'employee' :
GRANT ALL PRIVILEGES ON DATABASE employee TO employee; -- Allocation des droits
-- La réponse devrait être quelque chose comme 'GRANT'.
-- Nous créons la tbla à l'aide du type de données 'serial', numérotation automatique :
CREATE TABLE customer
(
  cust_no serial NOT NULL,
  customer character varying(25) NOT NULL,
  city character varying(25),
  country character varying(15),
  CONSTRAINT integ_60 PRIMARY KEY (cust_no )
);
-- Maintenant collons les lignes INSERT ci-dessus. Vérifions la saisie avec :
SELECT * FROM customer;
-- Sortons de là :
\q

Maintenant, nous devrions être sous le shell, connecté en tant que utilisateur postgres.

Si votre serveur se trouve sur une machine distante, vérifiez la bon accès réseau à cette machine. Voyez les détails dans la documentation postgresql, mais quelque chose comme ça devrait marcher :

# Nous utilisons 'nano' comme éditeur, mais vous pouvez le remplacer par ce que vous voulez (comme vim, emacs, joe...) et le numéro de la version de postgres dépend de '/etc/postgresql/8.4/main/pg_hba.conf'.

Vérifier la présence d'une ligne qui ressemble à ceci (Remplacer 192.168.0.1 par l'étendue d'adresses IP de votre réseau local.

# Permettre l'acceès du réseau local en utilisant les mots de passe encodés md5 : host all all 192.168.0.1/24 md5

Ou plus restritif :

# Permettre seulement l'accès à la base 'employee' à l'utilisateur 'employee' : host employee employee 192.168.0.1/24 md5

Si vous n'avez pas de ligne de ce type, ajoutez-la à la fin et sortez de votre éditeur. Pour plus d'information, reportez-vous à la documentation PostgreSQL.

Rechargez les paramètres de PostgreSQL :

 
psql

Puis

 
SELECT pg_reload_conf(); --reload settings...
-- ...and exit back to shell:
\q

Essayez le vous connecter à PostgreSQL.

Note : Par défaut, PostgreSQL tente d'utiliser un socket qui ne permet pas l'usage d'un mot de passe. Pour circonvenir cette propension, nous spécifions le nom de l'hôte pour forcer une connexion TCP/IP :

psql -h 127.0.0.1 -d employee -U employee -W # Connexion par TCP/IP. Précisez votre mot de passe.

Assurez-vous que les bibliothèques (dll sous WIndows et so sous Linus) sont bien installées - Par ex. sous Windows, 'sqlite3.dll' devrait se trouver dans le répertoire de votre projet.

Compiler une fois votre projet, même vide, de façon à créer le répertoire de sortir et, sous Windows, copier les dlls dans ce répertoire.

Exemple Basic

Projet et composants

D'abord, il vous faut créer un nouveau projet Lazarus.

Pour établir la connexion à votre base, vous avez besoin de une TIBConnection, une TSQLTransaction et un composant TSQLQuery que vous trouverez dans l'onglet 'SQLdb' de la palette de composants.

TIBConnection est me composant de connexion spécifique à Interbase/Firebird (Firebird est un fork d'Interbase). Si vous utilisez une base de données différente, remplacer ce composant par le composant approprié dans ce même onglet. Par exemple, une TSQLite3Connection pour une base SQLite, une PQConnection pour PostgreSQL. Les explications sur l'installation des bibliothèques d'accès à ces bases dépassent l'ambition de ce tutoriel et devrait être trouvées ailleurs (dans Databases par exemple).

Click on the TIBConnection (or equivalent connection component) on your form, and in the Object Inspector, change the name to DBConnection. This will simplify the rest of the tutorial when using different databases. It's also generally a good idea to name your components for something useful in your program (e.g. MainframeDBConnection) so you know what it stands for.

The other two components, TSQLTransaction and TSQLQuery, 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 to the database components we need a TDatasource component from the 'Data Access' tab.

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

Link the components

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 DBConnection to 'SQLTransaction1'. This causes the Database property of SQLTransaction1 to automatically change to 'DBConnection'.

Then, change the Database property of SQLQuery1 to 'DBConnection'. Lazarus automatically adds the value for the 'Transaction' property.

Next, change the Dataset property of Datasource1 to 'SQLQuery1'.

Finally we change the Datasource property of DBGrid1 to 'Datasource1'.

We now end up with a connection that links its default transaction to a transaction component. The transaction component links its database property to the connection object. These two components are enough to connect and execute instructions, but not enough to show queries. For this, SQLQuery component is used, which points to the database (and links to its default transaction). With the SQLQuery, we'll later on retrieve data and post it back to the database.

Finally, the datasource component, which is linked to the query component, is a sort of place holder. It keeps track of where in the query dataset we are and the GUI components are linked to that so they all show the same record.

If this is gibberish to you, don't despair: with just some more work, we'll be able to show our first data.

Connecting to the database

How can we now show the data from our database on the screen? First we need to tell DBConnection where the employee.fdb database is located (usually in the .../examples/empbuild/ subdirectory of your Firebird installation). Again you have the choice: you can use the object inspector to assign the path or do it directly in your source code.

We choose to use the object inspector: set the DBConnection 'HostName' property to the Firebird server name or IP adddress. Use localhost if a Firebird server is running on your development machine; use a blank value if you use the embedded Firebird client. Change the DatabaseName property of DBConnection to the path to the employee.fdb file on the database server (e.g. C:\Program Files\Firebird\Firebird_2_0\examples\empbuild\EMPLOYEE.FDB on a Windows machine).

Before the database server grants access to the data, it will check 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 when connecting.

However, for now, to simplify matters, we use the object inspector again to hard code these. Change the 'UserName' property to 'SYSDBA' and 'Password' to 'masterkey' (of course, adjust if your database installation has a different username/password).

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 'Connected' to 'False').

PostgreSQL

The situation with PostgreSQL is very similar to that on Firebird. The database name does not have a path - you just specify a name part (e.g. 'employee'). PostgreSQL has no embedded mode, so you need to fill out the HostName property for the connection test to work.

SQLite

For SQLite, you can leave the 'HostName', 'UserName', and 'Password' properties empty. Set the 'DatabaseName' to the name of your SQLite file, e.g. employee.sqlite. Note: sqlite will create the database specified if it doesn't exist, so be careful here.

You should now have something like the following screenshot - todo: this screenshot is actually further along, we only have a button now:

Form and components set up

Choosing what data to show

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. Save our project and then open the project lazdatadesktop.lpi and compile it.

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.

Let's add a TButton from the 'Standard' tab on the form. When the user clicks on the button, data retrieval should start. We will need some code for this. Double click on Button1. Lazarus then creates the skeleton of the necessary procedure. In our source code we should find the following lines:

procedure TForm1.Button1Click(Sender: TObject);
begin

end;

Between begin and end we must enter the instructions needed to display the data.... obviuosly that will be something to do with SQLQuery1..

The 'SQL' property of SQLQuery1 can only be changed, if SQLQuery1 is not active. That's why we close the component first:

SQLQuery1.Close;

Then we assign our SQL instruction to the 'SQL' property, overwriting any previous SQL commands:

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

Now we need to establish the connection to the database, activate the transaction and open the query:

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

You can omit the first two instructions, because they are done automatically by the third instruction. If you compile the project at this point, you could already see the data from the 'CUSTOMER' table.

However, a serious application must make sure 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):

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

end;

To close the connection we use the reverse order compared to our opening code:

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

Summary

Up to now we have learned how to connect to a database using the SQLdb package and how to display the contents of a table on the screen. If you want to add more functionality such as editing, please continue with SQLdb Tutorial2

If you followed the previous steps, then your code should look like:

unit Unit1; 

{$mode objfpc}{$H+}

interface

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

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    Datasource1: TDatasource;
    DBGrid1: TDBGrid;
    DBConnection: 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

{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
begin
  SQLQuery1.Close;
  SQLQuery1.SQL.Text:= 'select * from CUSTOMER';
  DBConnection.Connected:= True;
  SQLTransaction1.Active:= True;
  SQLQuery1.Open;
end;

procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin
  SQLQuery1.Close;
  SQLTransaction1.Active:= False;
  DBConnection.Connected:= False;
end;

end.

See also

• SQLdb Tutorial2: Second part of this tutorial, showing editing, inserting etc.
• Lazarus Database Overview: Information about the databases that Lazarus supports. Links to database-specific notes.
• SQLdb Package: information about the SQLdb package
• SQLdb Programming Reference: an overview of the interaction of the SQLdb database components
• SqlDBHowto: information about using the SQLdb package
• Working With TSQLQuery: information about TSQLQuery