SQLdb Tutorial3/fr

Portail de la base de données
Références: · Information générale · Bibliothèques · Types de champ · Contrôles · FAQ · Comment faire en SQL ? · Travailler avec TSQLQuery · Applications de base de données en mémoire · Tutoriels/articles pratiques : · Vue d'ensemble · 0 - Configuration de la base de données · 1 - Commençons · 2 - Edition · 3 - Requêtes · 4 - Modules de données · Référence de programmation SQLdb · Bases de données · Advantage - MySQL - MSSQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos

Vue d'ensemble

Dans ce tutoriel, vous allez apprendre comment :

• Rendre votre application adapté à de multiples bases de données, y compris avec une fiche de connexion.
• Obtenir des données de la base dans des contrôle normaux (au lieu de contrôles sensibles aux données).
• Extraire les données des contrôles et les retourner dans la base de données
• Exécuter des requêtes paramétrées.

Support Multi-database

En utilisant n'importe quelle base de données et une fiche de connexion, vous pouvez prendre en charge plusieurs serveurs de base de données/bibliothèques intégrées compatibles avec SQLDB.

Avantages :

• L'utilisateur/programmeur peut utiliser dynamiquement toute T*Connection de SQLdb, donc il peut choisir entre plusieurs SGBD.

Inconvénients :

• Un SQL plus compliqué sera demandé pour être encore adapté. Chaque SGBD a son propre dialecte ; c'est bien sûr possible d'appeler du SQL spécifique au SGBD, mais cela peut augmenter les problèmes de maintenance.
• Vous ne pouvez pas utiliser les propriétés spécifiques des T*Connection (telles que TIBConnection.Dialect pour définir le dialecte Firebird).

Pour utiliser la prise en charge des bases multiples, à la place de votre TConnection spécifique tel que TIBConnection , utilisez TSQLConnector (et non pas TSQLConnection), qui, à l'exécution du programme, choisit le TConnection spécifique qui doit être utilisé dans sa propriété ConnectorType :

uses
    ...
    var
      Conn: TSQLConnector;
    begin
      Conn:=TSQLConnector.Create(nil);
      try
        // ...actual connector type is determined by this property.
        // Make sure the ChosenConfig.DBType string matches
        // the connectortype (e.g. see the string in the 
        // T*ConnectionDef.TypeName for that connector .
        Conn.ConnectorType:=ChosenConfig.DBType;
        // the rest is as usual:
        Conn.HostName:='DBSERVER';
        Conn.DatabaseName:='bigdatabase.fdb';
        Conn.UserName:='SYSDBA';
        Conn.Password:='masterkey';
        try
          Conn.Open;

Fiche de connexion

Comme indiqué dans SQLdb Tutorial1, un utilisateur devrait se connecter à une base de données via une fiche (ou peut-être un fichier de configuration enregistré de manière sécurisée), et non à travers des accréditations (credentials) dans l'application. Outre les considérations de sécurité, avoir à recompiler l'application dès que les informations de sécurité sur serveur de base de données changent n'est pas une bonne idée.

Dans dbconfiggui.pas , nous implanterons une fiche de connexion qui tire les valeurs par défaut d'un fichier ini, s'il existe. Cela vous permet de configurer une connexion par défaut avec quelques détails (serveur et nom de la base de données) rempli pour les dépliements en entreprise p.ex.

Dans la fiche, l'utilisateur peut ajouter/modifier son nom d'utilisateur et son mot de passe et tester la connexion avant d'aller plus loin.

Nous utilisons une unité dbconfig.pas séparée avec une classe TDBConnectionConfig pour stocker les détails de notre connexion choisie. Cette classe prend en charge la lecture des réglages par défaut du fichier ini.

Ceci permet l'emploi sans fiche de connexion IHM (p.ex. l'exécution d'opérations batch), et permet la réutilisation dans les applications Web.

Cette classe TDBConnectionConfig apparaît dans la fiche de connexion comme propriété Config , afin que le programme principal puisse afficher le formulaire de configuration de manière modale, détecter un clic OK par l'utilisateur et récupérer la configuration sélectionnée avant de fermer le formulaire de configuration.

Fonction de rappel de test de connexion

Pour garder la fiche de connexion flexible (elle pourrait être utilisée avec d'autres couches d'accès aux données comme Zeos), nous implémentons la section test comme une fonction de rappel et laissons le programme principal traiter avec.

La définition dans la fiche de connexion dans dbconfiggui.pas :

type
      TConnectionTestFunction = function(ChosenConfig: TDBConnectionConfig): boolean of object;

La fiche principale doit implémenter une fonction avec la même signature pour gérer la requête de test depuis la fiche de configuration.

La fonction de rappel prend l'objet config transmis par la fiche de configuration et l'utilise pour construire une connexion avec le type de base de données choisie. Il essaie simplement de se connecter au serveur ; si il réussit, le résultat de la fonction prend la valeur True, sinon le résultat reste False.

Parce que des essais de connexion de base de données à es serveurs inexistant peut avoir de longs temps morts (timeout), nous indiquons à l'utilisateur qu'il doit attendre en mettant le sablier comme curseur de souris.

uses
    ...
    dbconfig, dbconfiggui
    ...
    procedure TForm1.FormCreate(Sender: TObject);
      LoginForm:=TDBConfigForm.Create(self);
      try
        // The test button on dbconfiggui will link to this procedure:
        ... this links the callback in ''dbconfiggui.pas'' to the ConnectionTest function here...
        LoginForm.ConnectionTestCallback:=@ConnectionTest;
    ...
    function TForm1.ConnectionTest(ChosenConfig: TDBConnectionConfig): boolean;
    // Callback function that uses the info in dbconfiggui to test a connection
    // and return the result of the test to dbconfiggui
    var
      // Generic database connector...
      Conn: TSQLConnector;
    begin
      result:=false;
      Conn:=TSQLConnector.Create(nil);
      Screen.Cursor:=crHourglass;
      try
        // ...actual connector type is determined by this property.
        // Make sure the ChosenConfig.DBType string matches
        // the connectortype (e.g. see the string in the
        // T*ConnectionDef.TypeName for that connector .
        Conn.ConnectorType:=ChosenConfig.DBType;
        Conn.HostName:=ChosenConfig.DBHost;
        Conn.DatabaseName:=ChosenConfig.DBPath;
        Conn.UserName:=ChosenConfig.DBUser;
        Conn.Password:=ChosenConfig.DBPassword;
        try
          Conn.Open;
          result:=Conn.Connected;
        except
          // Result is already false
        end;
        Conn.Close;
      finally
        Screen.Cursor:=crDefault;
        Conn.Free;
      end;
    end;

Finalement, le code dans dbconfiggui.pas qui appelle réellement la fonction de rappel est relie au bouton de Test. Il test si la fonction de rappel est affectée (pour éviter les plantages), pour la complétude, contrôlez aussi qu'il y a un objet de configuration valide et puis appelez la fonction de rappel :

...
    TDBConfigForm = class(TForm)
    ...
      private
        FConnectionTestFunction: TConnectionTestFunction;
      public
        property ConnectionTestCallback: TConnectionTestFunction write FConnectionTestFunction;
    ...
    procedure TDBConfigForm.TestButtonClick(Sender: TObject);
    begin
      // Call callback with settings, let it figure out if connection succeeded and
      // get test result back
      if assigned(FConnectionTestFunction) and assigned(FConnectionConfig) then
        if FConnectionTestFunction(FConnectionConfig) then
          showmessage('Connection test succeeded.')
        else
          showmessage('Connection test failed.')
      else
        showmessage('Error: connection test code has not been implemented.');
    end;

Ajouts/modifications

Possibles ajouts/modifications pour la fiche de connexion :

• Ajouter les arguments de ligne de commande pour dbconfig pour pré-charger les valeurs par défaut adaptées, ainsi le programme peut être utilisé dans des scripts batch, raccourcis, etc...
• Ajout d'une combobox "Sélection de profil" dans la fiche de connexion : utiliser de multiples profils dans le fichier ini qui spécifient le type de base de données et les détais de connexion.
• Masquer la combobox type de base de données quand celle-ci est unique.
• Masquer les nom de connexion/mot de passe quand vous êtes sûr qu'une base de données incorporée est sélectionnée.
• Ajouter la prise en charge du numéro de port ou du nom d'instance avec le connecteur MS SQL Server.
• Ajouter la prise en charge de l'authentification de confiance (trusted authentication) pour la prise en charge des SGBD qui la supporte (Firebird, MS SQL) : désactiver les contrôles nom de connexion/mot de passe.
• Si une base de donnée incorporée est sélectionnée mais que le fichier n'existe pas : Montrer une boîte de confirmation et créer la base de données.
• Créer une version en ligne de commande (Text UI) de la fiche de connexion (p.ex. en utilisant la bibliothèque Curses) pour les applications en mode console.

Mettre à jour cet article/le code serait chaleureusement apprécié.

Obtention de données de la base dans des contrôles non liés aux données

https://upload.wikimedia.org/wikipedia/commons/d/d8/Nuvola_apps_ktip.png Remarque: Before starting this section, please make sure you have set up the sample employee database as specified in SQLdb Tutorial0#Requirements

In previous tutorials, data-bound controls were covered: special controls such as the TDBGrid that can bind its contents to a TDataSource, get updates from that source and send user edits back.

It is also possible to programmatically retrieve database content and fill any kind of control (or variable) with that content. As an example, we will look at filling a stringgrid with some salary details for the sample employee database table.

On the main form, let's add a TStringGrid and retrieve the data (e.g. via a procedure LoadSalaryGrid called in the OnCreate event):

// Load from DB
        try
          if not FConn.Connected then
            FConn.Open;
          if not FConn.Connected then
          begin
            ShowMessage('Error connecting to the database. Aborting data loading.');
            exit;
          end;
    
          // Lowest salary
          // Note: we would like to only retrieve 1 row, but unfortunately the SQL
          // used differs for various dbs. As we'll deal with db dependent SQL later
          // in the tutorial, we leave this for now.
          // MS SQL: 'select top 1 '...
          FQuery.SQL.Text:='select ' +
            '    e.first_name, ' +
            '    e.last_name, ' +
            '    e.salary ' +
            'from employee e ' +
            'order by e.salary asc ';
            // ISO SQL+Firebird SQL: add
            //'rows 1 '; here and below... won't work on e.g. PostgreSQL though
          FTran.StartTransaction;
          FQuery.Open;
          SalaryGrid.Cells[1,1]:=FQuery.Fields[0].AsString;  // i.e. Cells[Col,Row]
          SalaryGrid.Cells[2,1]:=FQuery.Fields[1].AsString;
          SalaryGrid.Cells[3,1]:=FQuery.Fields[2].AsString;
          FQuery.Close;
          // Always commit(retain) an opened transaction, even if only reading
          // this will allow updates by others to be seen when reading again
          FTran.Commit;
    ...
          end;
        except
          on D: EDatabaseError do
          begin
            MessageDlg('Error', 'A database error has occurred. Technical error message: ' +
              D.Message, mtError, [mbOK], 0);
          end;
        end;

Things to note: we catch database errors using try..except. You'll notice we forgot to roll back the transaction in case of errors - which is left as an exercise to the reader.

We Open the query object, thereby asking FQuery to query the database via its SQL statement. Once this is done, we're on the first row of data. We simply assume there is data now; this is actually a programming error: it would be tidier to check for FQuery.EOF being true (or FQuery.RecordCount being >0).

Next, we retrieve the data from the first row of results. If we wanted to move to the next row, we'd use FQuery.Next , but that is not necessary here. We put the results in the stringgrid, giving the lowest salary in the list. A similar approach can be taken for the highest salary.

Adaptations du SQL aux divers SGBD

As we noticed above, various databases support various versions of SQL (either in addition to or in contradiction to the official ISO SQL standards). Fortunately, you can customize your application based on which DB it ends up using, which will be demonstrated by getting the standard deviation of the employees' salaries - built into e.g. PostgreSQL SQL but not available by default in e.g. Firebird.

In our LoadSalaryGrid procedure, we'll use the SQL for PostgreSQL and build a code solution for all other databases. First detect which database is loaded, below the other lines add:

...
      SalaryGrid.Cells[3,2]:=FQuery.Fields[2].AsString;
      FQuery.Close;
      // Always commit(retain) an opened transaction, even if only reading
      FTran.Commit;
    //end of existing code
    
      if FConn.ConnectorType = 'PostGreSQL' then
      begin
        // For PostgreSQL, use a native SQL solution:
        FQuery.SQL.Text:='select stddev_pop(salary) from employee ';
        FTran.StartTransaction;
        FQuery.Open;
        if not FQuery.EOF then
          SalaryGrid.Cells[3,3]:=FQuery.Fields[0].AsString;
        FQuery.Close;
        // Always commit(retain) an opened transaction, even if only reading
        FTran.Commit;
      end
      else
      begin
        // For other database, use the code approach:
        ....see below...
      end;

Notice the use of ConnectorType ; the string used must match exactly. We also properly check for empty results from the query (which might happen if the employee table is empty).

... now let's implement a code-based solution for other databases that do not support standard deviation:

// For other databases, use the code approach:
      // 1. Get average of values
      FQuery.SQL.Text:='select avg(salary) from employee ';
      FQuery.Open;
      if FQuery.EOF then
        SalaryGrid.Cells[3,3]:='No data'
      else
      begin
        Average:=FQuery.Fields[0].AsFloat;
        FQuery.Close;
        // 2. For each value, calculate the square of (value-average), and add it up
        FQuery.SQL.Text:='select salary from employee where salary is not null ';
        FQuery.Open;
        while not FQuery.EOF do
        begin
          DifferencesSquared:=DifferencesSquared+Sqr(FQuery.Fields[0].AsFloat-Average);
          Count:=Count+1;
          FQuery.Next;
        end;
        // 3. Now calculate the average "squared difference" and take the square root
        if Count>0 then //avoid division by 0
          SalaryGrid.Cells[3,3]:=FloatToStr(Sqrt(DifferencesSquared/Count))
        else
          SalaryGrid.Cells[3,3]:='No data'; 
      end;
      FQuery.Close;

Note that we use FQuery.EOF to check for empty data (and avoid division by zero errors etc). The loop shows how to:

• retrieve a database value into a variable
• use FQuery.Next to move to the next record
• properly check if the query dataset has hit the last record, then stop retrieving data.

The resulting screen should show something like this - note the use of a decimal comma - while your computer may show a decimal point depending on your locale:

Repenser notre salaire plus élévé/plus bas

This section gives some more useful details on SQL but is not required to work through for the rest of the tutorial

Now we know how to deal with detecting various database connections, we can adjust the SQL that gets the lowest and highest salary as well to make use of db specific functionality.

An example: this would work for MS SQL Server by limiting the number of returned rows to just the first:

select top 1 
    e.first_name, e.last_name, e.salary 
    from employee e
    order by e.salary asc

to get the lowest salary.

This efficiently returns one record. Other databases use other syntax, such as the ISO ROWS 1. The diligent SQL student will soon learn not to miss out that important part and request entire large recordsets just for one required record!

Let's briefly examine other ways to achieve the same thing, that are worth knowing.

Another way to retrieve the record(s) with the minimum salary would be :

SELECT e.first_name, e.last_name, e.salary FROM employee e WHERE e.salary=(SELECT min(salary) FROM employee)

SQL students would greatly benefit from researching Common Table Expressions.

A CTE allows a virtual temporary table to be used in a following expression, allowing you to clearly code some very complex queries that otherwise may not be possible. Knowing about CTEs will catapult you ahead of colleagues who have never heard of them! For example the above may be rewritten (example in Microsoft SQL Server syntax) as :

WITH TheMinimum as
    (
      SELECT min(salary) as MinimumPay FROM Employee
    )
    SELECT e.first_name, e.last_name, e.salary FROM Employee e WHERE e.salary=(SELECT MinimumPay FROM TheMinimum)

Several such temporary tables may be chained together, each using the results from the previous tables. You can treat these virtual tables as though they were real tables in the database, using JOINs to link recordsets together. And it can be very useful for quick tests using hardcoded data - this can be run without any database connection :

WITH TestEmployee as
    (
      SELECT 'Fred' as first_name, 'Bloggs' as last_name, 10500 as salary
       UNION 
      SELECT 'Joe' as first_name, 'Public' as last_name, 10000 as salary
       UNION 
      SELECT 'Mike' as first_name, 'Mouse' as last_name, 11000 as salary
    ),
     TheMinimum as
    (
      SELECT min(salary) as MinimumPay FROM TestEmployee
    )
    SELECT e.first_name, e.last_name, e.salary FROM TestEmployee e WHERE e.salary=(SELECT MinimumPay FROM TheMinimum)

You can end up with quite long strings for the code of such SQL queries, but it is only one query and may be called from anywhere where you are limited to a simple single expression - it can be useful to answer complex queries without resorting to functions or stored procedures.

Obtention de données hors des contrôles normaux dans la base de données

Previously, we have seen:

• how to let SQLDB update the database with data-bound controls (earlier tutorials)
• how to get data out of the database using queries (the section above)

You can also execute SQL to get arbitrary data back into the database via code. This allows you to use variables or controls that have no db aware equivalent such as sliders or custom controls to enter data into the database, at the expense of a bit more coding.

As an example, we are going to allow the user to change the lowest and highest salary in the stringgrid.

For ease of editing, set the grid's Options /goEditing to true; then assign the procedure below to the OnValidate event for the grid, which will be called every time a user has finished updating the grid.

Requête paramétrée

The following code also demonstrates how to use parameterized queries to avoid SQL injection, fiddling with quoting for string values, date formatting, etc.

As you can see in the code, you can name your parameters whatever you wish and prefix them with : in the SQL. In code, you can set/get their values by _< somequery>.Params.ParamByName('<thename>').As'<variabletype>'; the code demonstrates _.AsFloat and.AsString .

Parameterized queries are especially useful (and can be much faster) if you run the same query, only with different parameters, in a loop (think e.g. bulk loading of data).

Continuing with our example: after having set up the query SQL and parameters, the transaction is started (and later on committed) as usual, then the query is run by calling ExecSQL (which does not return a result set; if the SQL statement were e.g. a SELECT or INSERT...RETURNING that does return data, you would use Open as in the examples above):

procedure TForm1.SalaryGridValidateEntry(sender: TObject; aCol, aRow: Integer;
      const OldValue: string; var NewValue: String);
    begin
      // Only these cells have min and max salary:
      if (aCol=3) and ((aRow=1) or (aRow=2)) then
      begin
        // Allow updates to min and max salary if positive numerical data is entered
        if StrToFloatDef(NewValue,-1)>0 then
        begin
          // Storing the primary key in e.g. a hidden cell in the grid and using that in our
          // update query would be cleaner, but we can do it the hard way as well:
          FQuery.SQL.Text:='update employee set salary=:newsalary '+
            ' where first_name=:firstname and last_name=:lastname and salary=:salary ';
          FQuery.Params.ParamByName('newsalary').AsFloat:=StrToFloatDef(NewValue,0);
          FQuery.Params.ParamByName('firstname').AsString:=SalaryGrid.Cells[1,aRow];
          FQuery.Params.ParamByName('lastname').AsString:=SalaryGrid.Cells[2,aRow];
          FQuery.Params.ParamByName('salary').AsFloat:=StrToFloatDef(OldValue,0);
          FTran.StartTransaction;
          FQuery.ExecSQL;
          FTran.Commit;
          LoadSalaryGrid; //reload standard deviation
        end
        else
        begin
          // Notify user that his input was wrong... he'll be wondering otherwise:
          Showmessage('Invalid salary entered.');
          NewValue:=OldValue;
        end;
      end
      else
      begin
        // Silently discard edits to any other cells
        NewValue:=OldValue;
      end;
    end;

Note how we forgot to add a try..except block to this code to nicely catch database errors and display a sensible error message. If you are running the Firebird sample EMPLOYEE database for this tutorial, try to change the salary to a very low value (say 1) and see what happens.

Finally, while this example showed an UPDATE SQL query, you could just as well run INSERT queries to insert new data programmatically. Also, you can use parameters in any kind of SQL query (SELECT, UPDATE, etc) as long as you use them for fields, not for table/view/procedure names.

Résumé

Ce tutoriel a expliqué :

• Comment coder pour de multiples types de base de données.
• Comment utiliser un fiche de connexion pour découpler votre programme la configuration de l'accès à la base de données.
• comment récupérer et mettre à jour vos données par la programmation.

Code

Depuis Novembre 2012, le code peur être trouvé dans $(lazarusdir)examples/database/sqldbtutorial3.

Si vous avez une version plus ancienne (p.ex. Lazarus 1.0.2), vous pouvez aussi télécharger le code via le site web SVN de Lazarus.

Voir aussi

• SQLdb Tutorial0 : Instructions pour configurer les tables et les données d'exemple pour la série de tutoriels.
• SQLdb Tutorial1 : 1re partie de la série de tutoriels sur les bases de données, montrant comment configurer une grille pour afficher les données.
• SQLdb Tutorial2 : 2e partie de la série de tutoriels, montrant l'édition, l'insertion, etc.
• SQLdb Tutorial3 : 3e partie de la série de tutoriels, montrant comment développer pour des bases de données multiples et utiliser une fiche de connexion.
• SQLdb Tutorial4 : 4e partie de la série de tutoriels, montrant comment utiliser les modules de données.
• Vue d'ensemble des bases de données pour Lazarus : Information sur les bases de données supportées par Lazarus. Redirige vers des notes spécifiques à certaines bases de données.
• Paquet SQLdb : Information sur le paquetage SQLdb.
• Référence de programmation SQLdb : Une vue d'ensemble de l'interaction entre les composants de bases de données SQLdb.
• SqlDBHowto : Information sur l'utilisation du paquet SQLdb.
• Travailler avec TSQLQuery : Informations sur TSQLQuery.
• utilisation des paramètres

From the Free Pascal wiki, page "SQLdb Tutorial3/fr", as the Internet Archive kept it on 2025-03-15, last changed on 2020-02-27. Copyright stays with the author who wrote it, according to the old wiki's Licensing page. If you have concerns about this material, message the admin.