menu
  Home  ==>  articles  ==>  bdd  ==>  interbase  ==>  interbase_tutorial   

Tutorial Interbase - John COLIBRI.

1 - Introduction

Ce tutorial va vous indiquer comment utiliser le Serveur Interbase fourni avec Delphi. Ce tutorial a plusieurs objectifs:
  • vous présenter intégralement comment réaliser des applications utilisant des bases de données Sql en Delphi. Tous les exemples ont été codés et les sources sont téléchargeables.
  • effectuer toutes les opérations en Delphi, plutôt que de présenter une série d'outils annexes dont la présentation alongerait l'exposé
  • insister sur les points qui posent problème (création d'une base, chaîne de connection, modification de dbGrid) plutôt que sur les composants visuels assez intuitifs au demeurant
  • présenter les composants réellement utilisés pour gérer des tables Sql (des tIbQuery) plutôt que des composants génériques (tIbTable) dont les traitements automatiques masquent le fonctionnement réel d'un Serveur Sql
  • limiter au maximum le nombre de composants utilisés pour manipuler les données du Serveur sans perdre en fonctionnalité
Si après ce premier contact vous souhaitez approfondir vos connaissances, nous vous proposons soit des formations ou des prestations de programmation et d'assistance.

Nous nous adressons pour ce tutorial à un programmeur Delphi:

  • ayant une idée élémentaire de Delphi: Palette, Inspecteur, OnClick. Tout le reste sera expliqué
  • n'ayant pas nécessairement de connaissance SQL. Le fonctionnement des requêtes et leur syntaxe sera présenté


2 - Installation

2.1 - Principe

Le logiciel Interbase comporte essentiellement deux parties:
  • le programme Serveur (le moteur), qui en général est placé sur un PC distant
  • le programme Client, qui dialogue avec les programme applicatifs (Delphi ou autre).
Il faut donc installer les deux parties (sur la même machine ou sur des PC séparés).

Interbase peut fonctionner selon deux modes:

  • le mode dit "local", pour lequel le moteur et le programme client sont sur le même PC. La machine n'a pas besoin d'avoir les couches réseau (TCP\IP, Novell ou autre), ni de carte réseau (carte Ethernet ou liaison série). Le programme applicatif communique avec le Client, qui appelle directement les routines du serveur (sans passer par des appels TCP\IP)

  • le mode Client Serveur
    • les communications entre la DLL client et le Serveur passent par les couches réseau de Windows: il faut que ces couches réseau soient installées (même si le Serveur et le Client sont sur le même PC)
    • le programme applicatif devra désigner le serveur en utilisant l'adresse IP du serveur.

Dans les deux cas, l'installation se fait en utilisant l'installateur du CD Delphi (ou du CD Interbase). Nous présenterons l'instalation à partir du CD Delphi.



2.2 - Installation avec Delphi

Lors de l'installation de Delphi, Installshield propose d'installer aussi Interbase.

Vous pouvez répondre oui et Interbase sera installé

Si vous avez répondu "Non", voici comment procéder:



2.3 - Installation du Mode Local

Pour installer Interbase en mode local:
   insérez le CD Delphi
   sélectionnez "Interbase Desktop Edition"
   répondez oui à toutes les questions
   vous pouvez vérifier que
  • le répertoire "c:\Program Files\Borland\" contient bien un sous-répertoire "Interbase"
  • le répertoire "c:\Windows\SYSTEM\" contient bien la DLL client "gds32.dll"
  • le menu démarrer propose à présent une option "Interbase" avec "Interbase Server Manager". Cliquez sur ce choix, et le gestionnaire du serveur apparaît:




2.4 - Installation du Mode Distant

Installez le Serveur sur le PC serveur, en procédant comme ci-dessus, mais sélectionnez "Interbase 6 Server". La partie Client installée sur le PC du Serveur sera utilisée pour les logiciels de gestion du Serveur

Installez le Client sur le PC client:
   sélectionnez "Interbase 6 Server"
   après les dialogues de copyright ou autres apparaîtra:

   sélectionnez "Interbase Client"


2.5 - Suppression d'Interbase

Pour supprimer Interbase:
   ouvrez le Serveur Manager et cliquez "Stop" (pour arrêter le moteur)
   ouvrez le panneau de configuration, sélectionnez "Ajout / Suppression de logiciel", sélectionnez "Interbase" et cliquez Oui partout


3 - Créer la Base

3.1 - SQL et la création de bases

Pour créer une base, il faut, pour tous les moteurs, utiliser des outils spéciaux, le langage SQL ne contenant pas de requête spécifique pour cette opération

Pour Interbase, ce sont des primitives de l'API native du moteur qui permettent cette création. Elle n'est pas possible depuis les composants générique d'accès aux données (tTable, tQuery). En revanche, Jeff Overcash a ajouté à tIbDatabase la méthode qui utilise les API Interbase natifs et permet la création de la base.

Les éléments à fournir sont les suivants;

  • le chemin DOS où sera placé le fichier .GDB
  • le dialecte Interbase à utiliser. En gros, le dialecte détermine le traitement de certains types de données Interbase (en dialecte 3 nous pouvons utiliser des Integer 64 bits et des tTimeStamps, minuscule ou majuscule sont reconnus etc). Nous choisirons le dialecte 3 (le plus récent).
  • le nom d'utilisateur et le mot de passe du Serveur. Ce sont les noms du serveur par défaut (la base n'étant pas encore créée, elle)
  • des paramètres d'optimisation, tels que la taille des pages du cache
Plus précisément:
  • le chemin est placé dans tIbDatabase.DataBaseName
  • le dialecte est placé dans tIbDatabase.SqlDialect
  • le nom d'utilisateur et le mot de passe, ainsi que les autres paramètres sont placés dans la propriété fourre-tout Params
  • la méthode de création est simplement:
          tIbDatabase.CreateDataBase



3.2 - Création de la base

Nous allons créer une base contenant les stages offerts par l'Institut Pascal. Nous placerons cette base dans le répertoire "..\data\" (situé au même niveau que le .EXE. Le .ZIP téléchargeable créera ce répertoire automatiquement). Le nom du fichier sera "Institut_Pascal.GDB".

Pour créer la base:
   créez une nouvelle application et appelez-la "p_ib_create_base"
   sélectionnez dans la page "Interbase" de la Palette le composant tIbTransaction:

et posez ce composant sur la tForm
   sélectionnez de même une tIbDatabase

et placez-la sur la tForm
   initialisez IbDatabase.DefaultTransaction vers IbTransaction1
   utilisez un tButton pour créer la base:
  • placez un tButton, nommez-le create_base_, et créez sa méthode OnClick
  • voici le code de cette méthode:

        procedure TForm1.create_database_Click(Sender: TObject);
          begin
            with IbDatabase1 do
            begin
              DatabaseName:= '..\data\Institut_Pascal.gdb';
              SqlDialect:= 3;

              Params.Add('USER "SYSDBA"');
              Params.Add('PASSWORD "masterkey"');
              Params.Add('PAGE_SIZE 4096');

              CreateDatabase;
            end; // with IbDataBase1
          end; // create_database_Click

   compilez et exécutez
   cliquez Button1
   le répertoire ..\data\ contient bien le fichier "Institut_Pascal.GDB" dont la taille est d'environ 580 K
Notez que:

  • si le fichier formation.gdb existait déjà il faut tester sa présence (FileExists) et l'effacer (Erase)
  • si nous souhaitons répéter la création, il faudrait purger les Params avant tout ajout par Add (Params.Clear)
Vous pouvez télécharger ce projet "ib_create_base.zip".



3.3 - Connection à une base

tIbDatabase est utilisé par la suite pour assurer les échanges entre notre application et la base que nous venons de créer.

Ce composant doit être initialisé avec:

  • dans DataBaseName, la chaîne de connection qui est
    • si nous travaillons en local, le chemin:
              c:\programs\interbase\data\Institut_Pascal.gdb
      ou
              ..\data\Institut_Pascal.gdb
    • l'URL du PC qui héberge le Serveur:
              127.0.0.1\ c:\programs\interbase\data\Institut_Pascal.gdb
              HostName\ c:\programs\interbase\data\Institut_Pascal.gdb
              www.jcolibri.com\ c:\programs\interbase\data\Institut_Pascal.gdb
  • dans Params, les paramètres propres à Interbase. Dans notre cas, les paramètres par défaut du Serveur, plus le nom d'utilisateur et le mot de passe.
Une fois les propriétés initialisées, nous pouvons tester la connection en basculant tIbDatabase.Connected sur True.

A titre de vérification:
   posez un second tIbDataBase sur la forme
   initialisez IbDatabase2.DatabaseName avec
        ..\data\Institut_Pascal.gdb
   cliquez deux fois sur IbDatabase2 pour ouvrir l'éditeur de tIbDatabase
   Delphi présente le dialogue suivant:
   sélectionnez "User Name" et tapez SYSDBA
   sélectionnez "Password" et tapez masterkey
   supprimez la coche de "Login Prompt"
   le résultat est:
   fermez l'éditeur
   sélectionnez Connected et basculez sa valeur sur True
   au bout d'un "petit" instant, la valeur bascule sur True


Notez que:

  • la connection est le test IMPERATIF pour vérifier que nous pouvons travailler en Interbase. Nous recommandons d'effectuer cette connection systématiquement avant de poser des tonnes de composants sur la tForm ou d'écrire des milliers de lignes
  • si nous connectons la base en mode conception, Delphi ouvrira aussi la base lorsque l'exécution sera lancée. Ces deux connections sont comptabilisés par le gestionnaire de licences Interbase comme 2 connections séparées (ce sont bien deux processus Windows). Si vous travaillez en utilisant la version Delphi d'Interbase, il vaut mieux fermer la connection en mode conception, puis la rouvrir lors de l'exécution par:
            tIbDatabase.Open;

4 - Créer une Table

4.1 - Principe

Nous allons créer une table contenant pour chaque formation:
  • un code (par exemple 8)
  • un nom (par exemple "Delphi Interbase")
  • un prix (par exemple 1.400)
Pour cela nous devons envoyer une requête en langage SQL vers le Serveur Interbase.

La syntaxe de cette requête est:

 CREATE TABLE formations
     (f_numero INTEGER, f_nom CHARACTER(11), f_jours INTEGER, f_prix NUMERIC(5, 2) )

Il suffit donc de choisir un nom de table, et le nom de chaque colonne avec son type.

Parmi les types autorisés par Interbase citons:

  • INTEGER pour les valeurs entières 32 bits
  • SMALLINT pour les valeurs entières 16 bits
  • NUMERIC(decimales, précision) pour une valeur numérique flottante
  • DATE pour une date
  • CHARACTER(taille) pour des caractères
Pour envoyer cette requête vers le Serveur:
  • nous utilisons un tIbDataBase qui assurera la connection vers le Serveur
  • nous utilisons un tIbQuery
    • nous le relions à tIbDatabase
    • nous plaçons la requête SQL dans sa propriété IbQuery.SQL (via l'Inspecteur ou en code)
    • nous appelons tIbQuery.ExecSql
  • la création n'est visible par tout le monde que si la transaction qui est utilisée pour la création de la table est confirmée


4.2 - Utilisation de SQL

La requête à envoyer au Serveur est placée dans tIbQuery.Sql. tIbQuery.Sql est un descendant de tStrings. Nous pouvons donc utiliser, par exemple:

          IbQuery1.Sql.Add('CREATE TABLE formations (f_numero INTEGER, f_nom CHARACTER(11))');

Pour construire la requête:

  • nous pouvons utiliser Add pour ajouter une ligne de requête, Text pour affecter une requête, Clear pour purger tout texte antérieur, ou même LoadFromFile pour lire un fichier .txt contenant la requête:
          IbQuery1.Sql.LoadFromFile('cree_formation.txt');

La mise en page n'a aucune importance pour le Serveur: la requête peut être répartie en plusieurs lignes:

          tIbQuery.Sql.Add('CREATE TABLE');
          tIbQuery.Sql.Add('  formations ');
          tIbQuery.Sql.Add('  (f_numero INTEGER, f_nom CHARACTER(11))');
  • nous pouvons entrer la requête en utilisant l'Inspecteur d'Objet. Si nous cliquons sur Sql, Delphi affiche:

    et vous pouvez taper manuellement la requête dans la partie "SQL"

  • nous pouvons aussi créer la String en plusieurs étapes par toutes les primitives de String telles que la concaténation, Insert, le test par Pos ou autre. Par exemple:
          l_requete:= 'CREATE TABLE '+ Edit1.Text+ ' (';
          l_requete:= l_requete+ Edit2.Text+ ')';
          tIbQuery.Sql.Add(l_requete);



4.3 - Comment ça Marche

Au niveau fonctionnement:
  • lorsque nous construisons la requête par IbQuery.Sql.Add, ce texte de requête est à ce stade une tStrings en mémoire de notre PC.

    C'est une tStrings comme n'importe quelle autre. Ce qui explique qu'aucune vérification de syntaxe n'est effectuée.

  • Lorsque nous exécutons:

                 IbQuery1.ExecSql;

    alors:

    • la requête est envoyée au Serveur via le Client
    • le Serveur traite la requête et:
      • crée la table si la requête est correcte:

      • retourne une erreur transformée par Delphi en exception en cas de problème
Notez que l'envoi de toute requête qui modifie des données du Serveur (et la création d'une nouvelle table est bien une modification) ne peut se faire que par du code (PAS en basculant IbQuery1.Active sur True en mode conception)



4.4 - Les Transactions

En Interbase, toutes les requêtes sont créées dans le cadre d'une transaction. Les transaction sont un mécanisme qui garantit que plusieurs sont réalisées en entier ou annulées. L'archétype est le transfert bancaire: si nous débitons DUPOND pour créditer MARTIN, le système doit garantir que soit les deux modifications sont réalisée ou aucune ne l'est.

En Delphi, les composants de bases de données utilisent par défaut des transactions transparentes pour le programmeur, mais nous pouvons gérer les transactions nous-même. C'est ce qui est recommandé pour Interbase.

En InterbaseExpress, nous utilisons un composant tIbTransaction que nous connectons à tIbDatabase.DefaultTransaction. Les primitives sont:

  • tIbTransaction.StartTransaction pour démarrer une nouvelle transaction avant une ou plusieurs opérations
  • tIbTransaction.Commit pour confirmer la suite d'opération, ou tIbTransaction.RollBack pour tout annuler.
  • StartTransaction et Commit ou RollBack sont souvent utilisés dans TRY EXCEPT:
          TRY
            IbTransaction1.StartTransaction;
            débite DUPOND
            crédite MARTIN
            IbTransaction1.Commit;
          EXCEPT
            IbTransaction1.RollBack;
          END;

Nous ne pouvons appeler IbTransaction1.StartTransaction si la transaction attachée à IbTransaction1 est fermée (committed ou Rolledback). Pour savoir si une transaction est active, nous pouvons utiliser la fonction InTransaction. Ainsi:

          IF NOT IbTransaction1.InTransaction
            THEN IbTransaction1.StartTransaction;

Que se passerait-il si nous réalisons le traitement sans cette IbTransaction:

  • pendant l'exécution de notre application d'autres applications (l'explorateur de bases de données, un autre de nos exe) ne "verraient" pas la table
  • un autre IbQuery de notre propre application pourrait ne pas voir la table
  • lorsque l'EXE sera fermé, la transaction sera automatiquement fermée
Le plus simple est donc d'utiliser Commit.



4.5 - L'application

Pour créer notre table
   créez une nouvelle application et appelez-la "ib_create_table"
   placez un tIbDataBase sur la Forme. Cliquez deux fois sur IbDataBase1, renseignez "local", "DataBase", "User Name" "Pass Word" et "Login Prompt":

Vérifiez la connection en basculant IbDataBase.Connected sur True, puis fermez la connection (pour éviter de monopoliser un utilisateur). La connection sera ouverte avant l'envoi de la requête

   placez un tIbTransaction sur la Forme, et reliez IbDatabase1.DefaultTransaction
   placez un tIbQuery sur la tForme

   sélectionnez sa propriété DataBaseName et initialisez-la à

    IbDataBase1

   placez un tButton sur la Forme et créez sa méthode OnClick. Placez-y les instructions de création:

    procedure TForm1.create_table_Click(Sender: TObject);
      begin
        with IbQuery1 do
        begin
          Close;

          with Sql do
          begin
            Clear;
            Add('CREATE TABLE formations');
            Add('  ('
                +'    f_numero INTEGER,');
            Add('    f_nom CHAR(9),');
            Add('   )');
          end; // with Sql

          Try
            IbDatabase1.Open;
            if ibtransaction1.InTransaction
              then ibTransaction1.Commit;
            ibtransaction1.StartTransaction;
            ExecSql;
            ibtransaction1.Commit;
          except
            on e: Exception do
              display('pb_create '+ e.Message);
          end; // try ... except
        end; // with IbQuery1
      end; // create_table_Click

   compilez, exécutez, et cliquez le bouton


Vous pouvez télécharger le sources du projet "ib_create_table.zip".



4.6 - Vérifier la création

Nous pouvons vérifier que la création a été effectuée en utilisant l'explorateur de bases de données Delphi, ou en lisant les données ce cette table dans notre application.

Pour lire les données il suffit d'envoyer la requête

SELECT * FROM formations

au moteur. Nous verrons cette requête SELECT en détail plus bas, mais voici comment procéder pour notre test:
   ajoutez un second tIbQuery sur la tForme
   sélectionnez sa propriété DataBaseName et initialisez-la à

    IbDataBase1

   placez un second tButton sur la Forme et placez-y la requête de lecture:

    procedure TForm1.select_Click(Sender: TObject);
      begin
        with IbQuery2 do
        begin
          Close;

          with Sql do
          begin
            Clear;
            Add('SELECT * FROM formations');

            Try
              Open;
              display('ok');
            except
              On e: Exception do
                display('not_there '+ e.Message);
            end;
          end; // with Sql
        end; // with IbQuery2
      end; // select_Click

   compilez, exécutez, et cliquez le bouton


4.7 - Effacer une table

Pour supprimer une Table, il faut exécuter la requête:

DROP TABLE formations

Donc:
   ajoutez un troisième tIbQuery sur la tForme
   sélectionnez sa propriété DataBaseName et initialisez-la à

     IbDataBase1

   placez un autre tButton sur la Forme et placez-y la requête de suppression:

    procedure TForm1.drop_table_Click(Sender: TObject);
      begin
        IbDatabase1.Open;

        with IbQuery3 do
        begin
          Close;

          with Sql do
          begin
            Clear;
            Add('DROP TABLE formations');
          end;

          Try
            if ibtransaction1.InTransaction
              then ibTransaction1.Commit;
            ibtransaction1.StartTransaction;
            ExecSql;
            ibtransaction1.Commit;
            display('  ok');
          except
            on e: exception do
               display('pb_drop '+ e.Message);
          end;
        end; // with IbQuery3
      end; // drop_table_Click

   compilez, exécutez, et cliquez le bouton
Notez que:
  • nous avons utilisé 3 IbQuery séparés. Nous aurions aussi bien pu utiliser le même
  • nous aurions même pu utiliser un IbSql. Pour ce tutorial que nous souhaitons minimal, nous avons préféré un IbQuery qui permet à la fois la modification de données du Serveur (CREATE, DROP) et la lecture de données depuis le Serveur (SELECT)
  • notez toutefois, que pour envoyer une requête de modification il faut utiliser tIbQuery.ExecSql, et pour la lecture c'est tIbQuery.Open.

    tIbQuery.Open est équivalent à tIbQuery1.Active:= True (mais pas à ExecSql). Comme l'Inspecteur propose Active (pour pouvoir positionner ses éléments visuels), nous pouvons ouvrir une Table depuis l'Inspecteur, mais nous ne pouvons pas créer de Table en mode conception (il faut exécuter du code)



4.8 - Automatisation de la création

Si nous avons plusieurs tables à créer, le codage en dur se révèle très vite fastidieux. Or il est très simple de paramétrer une procédure générique, et d'appeler cette procédure en lisant les paramètres depuis un fichier.

Une solution est de placer sur disque les requêtes SQL et de lire et exécuter ces requêtes les unes après les autres.

Nous avons préféré utiliser une définition plus schématique qui est analysée par la procédure de lecture.

Voici notre schéma que nous avons tapé dans NotePad et placé dans le fichier "schema_simple.txt":

formations
      f_numero INTEGER
      f_nom CHAR(23)
      f_jours INTEGER
      f_prix NUMERIC(12, 2)
dates
      d_formation INTEGER
      d_date DATE
      d_ville INTEGER
villes
      v_numero INTEGER
      v_nom CHAR(30)

Nous avons simplement fourni

  • le nom de chaque table, à la marge
  • le nom des champs et leur type Interbase, indenté de deux espaces
On peut difficilement faire plus simple !

Notre programme va alors

  • lire le fichier
  • générer la requête pour créer chaque table
Et:
  • la création de la table est réalisée par une classe dont voici la définition:

        type c_create_ib_table= class(c_basic_object)
                                  m_table_name: String;
                                  m_c_ib_query_ref: tIbQuery;
                                  m_c_fields: tStringList;
                                  m_exec_sql: Boolean;

                                  Constructor create_ib_table(p_name, p_table_name: String;
                                      p_c_ib_query: tIbQuery);
                                  procedure drop_table;
                                  procedure create_table;
                                  procedure display_sql;
                                  Destructor Destroy; Override;
                                end; // c_create_ib_table

  • la méthode de création de la table est la suivante:

        procedure c_create_ib_table.create_table;
          var l_field_index: Integer;
          begin
            drop_table;

            with m_c_ib_query_ref, Sql do
            begin
              display(m_table_name);
              Clear;

              Add('CREATE TABLE '+ m_table_name+ ' (');
              for l_field_index:= 0 to m_c_fields.Count- 2 do
                Add(m_c_fields[l_field_index]+ ',');
              Add(m_c_fields[m_c_fields.Count- 1]+ ' )');

              if m_exec_sql
                then
                  try
                    if DataBase.DefaultTransaction.InTransaction
                      then DataBase.DefaultTransaction.Commit;
                    DataBase.DefaultTransaction.StartTransaction;
                    ExecSql;
                    DataBase.DefaultTransaction.Commit;

                    display('  ok '+ m_table_name);
                  except
                    on e: Exception do
                       display('  *** pb_create '+ e.Message);
                  end // try ... Except
                else display('did_not_ask_to_create');
            end; // with m_c_ib_query_ref, Sql
          end; // create_table

  • cette méthode utilise la liste de définition des champs qui est chargée à partir du fichier par la procédure suivante

        procedure TForm1.create_script_Click(Sender: TObject);
          var l_list_index: Integer;
              l_line, l_trimmed_line: String;
              l_c_create_ib_table: c_create_ib_table;
              l_table_index: Integer;
          begin
            with tStringList.Create do
            begin
              LoadFromFile(k_script_path+ k_script_name);
              l_c_create_ib_table:= Nil;

              l_table_index:= 0;

              for l_list_index:= 0 to Count- 1 do
              begin
                l_line:= Strings[l_list_index];
                l_trimmed_line:= Trim(l_line);
                if l_trimmed_line<> ''
                  then begin
                      if l_trimmed_line= l_line
                        then begin
                            if Assigned(l_c_create_ib_table)
                              then begin
                                  l_c_create_ib_table.create_table;
                                  l_c_create_ib_table.Free;
                                  Inc(l_table_index);
                                end;

                            display_line;
                            l_c_create_ib_table:= 
                                c_create_ib_table.create_ib_table('', l_line, IbQuery1);
                          end
                        else begin
                            l_c_create_ib_table.m_c_fields.Add(l_trimmed_line);
                          end;
                    end;
              end; // for l_list_index

              // -- the last table 
              if Assigned(l_c_create_ib_table)
                then begin
                    l_c_create_ib_table.create_table;
                    l_c_create_ib_table.Free;
                  end;

              Free;
            end; // with tStringList
          end; // create_script_Click

Notez que:
  • la procédure c_create_ib_table.create_table commence par appeler la procédure c_create_ib_table.drop_table. Pour la première création, ceci est inutile, mais si vous créez une seconde fois la base, il faut d'abord effacer la table, sinon le Serveur refuse la création.
  • mais dans le cas de la première création, DROP TABLE provoque aussi une exception. C'est pourquoi cette instruction est placée dans un TRY ... EXCEPT. Cette exception va interrompre l'exécution. Pour poursuivre la création:
    • vous pouvez taper F9 pour passer outre
    • vous pouvez placer Delphi en mode "ne pas s'arrêter en cas d'exceptions" en suprimant la coche de la CheckBox "stop on Delphi Exceptions" située dans:
      Tools | Debugger | Language Exceptions
    C'est le mode préféré lorsque l'on utilise beaucoup de bases de données, compte tenu des nombreuses exceptions provoquées à tous les niveaux (Serveur Sql, pilote, BDE ou dBExpress, Ibx etc).


Vous pouvez télécharger le source du projet "ib_create_tables_with_script.zip".



5 - Ajouter des Données

5.1 - Ajout simple

Ajoutons un enregistrement pour le stage

    3, Interbase Delphi

L'instruction SQL est:

 INSERT INTO formations
     (f_numero, f_nom)
     VALUES (3, 'Interbase Delphi')

L'ajout d'enregistrement va modifier les données du Serveur, donc nous utiliserons tIbQuery.ExecSql.

De façon détaillée:
   créez une nouvelle application et nommez-la "ib_insert_data"
   placez un tIbTransaction sur la Forme
   placez un tIbDataBase sur la Forme. Cliquez deux fois sur IbDataBase1, renseignez "local", "DataBase", "User Name" "Pass Word" et "Login Prompt".
Vérifiez la connection en basculant IbDataBase1.Connected sur True, puis fermez la connection.
Sélectionnez DefaultTransaction et initialisez-la à IbTransaction1
   placez un tIbQuery sur la tForme
   sélectionnez sa propriété DataBaseName et initialisez-la à IbDataBase1
   placez un tButton sur la Forme et créez sa méthode OnClick. Placez-y les instructions d'ajout:

    procedure TForm1.insert_Click(Sender: TObject);
      begin
        IbDatabase1.Open;

        with IbQuery1 do
        begin
          Close;

          with Sql do
          begin
            Clear;
            Add('INSERT INTO formations');
            Add('  (f_numero, f_nom)');
            Add('  VALUES (8, ''Delphi InterBase'')');
          end; // with Sql

          Try
            if not IbTransaction1.InTransaction
              then IbTransaction1.StartTransaction;
            ExecSql;
            IbTransaction1.Commit;
          except
            on e: Exception do
              display('pb_insert '+ e.Message);
          end; // Try...Except
        end; // with IbQuery1
      end; // insert_Click

   compilez, exécutez, et cliquez le bouton


Vous pouvez télécharger les sources du projet "ib_insert_data.zip".



5.2 - Type CHARACTER

Pour spécifier les valeurs CHARACTER, Sql exige que la chaîne soit entourée de guillemets. Suivant les Serveurs, il faut utiliser un guillemet simple ou double:

 VALUES (3, 'Interbase Delphi')

ou

 VALUES (3, "Interbase Delphi")

De plus si notre valeur est nichée dans une String Pascal, il faut dédoubler les guillemets

IbQuery1.Sql.Add('  VALUES (3, ''Interbase Delphi'')');

Pour simplifier cet imbroglio de guillemets, Delphi propose la méthode QuotedStr:

IbQuery1.Sql.Add('  VALUES (3, '+ QuotedStr('Interbase')+ ')');



5.3 - Type NUMERIC

Pour les valeurs numériques avec décimales, nous devons batailler avec les points et les virgules:
  • aux US, la partie entière est séparée de la partie décimale par un point:

        3.1415

    alors qu'en Europe nous disons

        3,1415

  • les valeurs que nous tapons en PASCAL doivent donc respecter la syntaxe US

  • mais si nous utilisons des primitives de conversion interface utilisateur <=> Delphi, Delphi lit les paramètres de localisation dans la base de registre Windows. Pour ma machine il est dit que le séparateur décimal est la virgule. Par conséquent les fonctions telles que:

        FloatToStr

    attendent une virgule

    Nous pouvons imposer le séparateur à utiliser en spécifiant:

DecimalSeparator:= '.';

La règle est donc simple:

  • les valeurs Delphi (Double ou autre) utilisent le point '.'
  • les composants visuels (tEdit etc) et les primitives de conversion (FloatToStr) utilisent la virgule ','


5.4 - Type DATE

Un problème similaire intervient pour les dates:
  • Sql utilise le format US "année mois jour". EXCLUSIVEMENT
  • suivant le type de primitive, Delphi utilisera un format US ou Européanisé (via le panneau de configuration Windows, ou des primitives de formatage)
  • le séparateur doit être un slash / (et non pas un tiret ou autre)

         2004/03/29

  • les dates doivent être entourée de guillemets:

         '2004/03/29'

    car sinon Delphi effectue une division et calcule un Double, assimilé à un tDateTime, et traduit en une date

En supposant que nous souhaitions fournir la date du 29 Mars 2004, nous pouvons utiliser:

 INSERT INTO dates
     (d_numero, d_date)
     VALUES (3, '2004/03/29')

et:

IbQuery1.Sql.Add('INSERT INTO dates');
IbQuery1.Sql.Add('  (d_numero, d_date');
IbQuery1.Sql.Add('  VALUES (3, ''2004/03/29'')');



5.5 - Automatisation de l'Ajout

Les valeurs à insérer ont été figées dans notre code. Nous pouvons automatiser cet ajout
  • soit par des scripts
  • soit par une procédure amplement paramétrée
  • soit en mode interactif.
Nous allons présenter ici l'utilisation d'une procédure.

En fait il n'y a rien de nouveau par rapport à la technique ci-dessus, sauf que

  • la chaîne de la requête est construite en fonction de paramètres de la procédure
  • la procédure appelante envoie les paramètres requis
Voici un exemple de procédure générique d'ajout:

    procedure insert_generic(p_number: Integer; p_name: String; p_days: Integer; p_cost: Double);
      begin
        with Form1, IbQuery1 do
        begin
          Close;

          with Sql do
          begin
            Clear;
            Add('INSERT INTO formations');
            Add('  (f_numero, f_nom, f_jours, f_prix)');
            DecimalSeparator:= '.';
            Add('  VALUES ('+ IntToStr(p_number)+ ', '+ QuotedStr(p_name)
                + ', '+ IntToStr(p_days)+ ', '+ FloatToStr(p_cost)+ ')');
            DecimalSeparator:= ',';
          end;

          Try
            if not IbTransaction1.InTransaction
              then IbTransaction1.StartTransaction;
            ExecSql;
            IbTransaction1.Commit;
            display('  ok');
          except
            on e: Exception do
              display('  pb_insert '+ e.Message);
          end;
        end; // with IbQuery1
      end; // insert_generic

Et voici un exemple de procédure appelante

    procedure TForm1.insert_batch_Click(Sender: TObject);
      begin
        IbDatabase1.Open;

        insert_generic(1, 'Initiation Delphi', 3, 1400.40);
        insert_generic(2, 'Bases de Données Delphi', 3, 1.400);
        insert_generic(3, 'Interbase Delphi', 3, 1.400);
        insert_generic(4, 'Composants Delphi', 3, 1.400);
        insert_generic(5, 'UML Delphi', 3, 1.400);
        insert_generic(4, 'Initiation Pascal', 4, 1.900);
      end; // insert_batch_Click

Notez que:

  • la procédure appelante pourrait aussi bien lire ses donnée d'une autre source (un fichier FILE OF, un fichier ASCII ("comma separates values" ou autre), un autre table (Oracle, Sql Serveur ou même une autre table Interbase...)
  • le paramètres p_cost est de type Double:
    • la procédure appelante envoie une valeur littérale avec un point décimal

             insert_generic(2, 'Bases de Données Delphi', 3, 1.400);

    • la procédure appelée utilise FloatToStr, qui attend une virgule décimale. Nous forçons donc temporairement l'utilisation du point:

                  DecimalSeparator:= '.';
                  Add('  VALUES ('+ IntToStr(p_number)+ ... + FloatToStr(p_cost)+ ')');
                  DecimalSeparator:= ',';



6 - Lire et Afficher

6.1 - 5.1- Principe

Pour afficher un enregistrement, nous devons d'abord récupérer ses valeurs du Serveur.

Pour lire les données contenues dans une table, SQL utilise l'instruction SELECT. Par exemple:

 SELECT f_numero, f_nom
     FROM formations

Lorsque le Serveur reçoit cette requête:

  • il vérifie sa syntaxe
  • il construit une table contenant les valeurs demandées
  • ces données sont envoyées au Client
Plus concrètement:
   créez une nouvelle application et appelez-la "ib_select"
   placez un tIbTransaction sur la Forme
   placez un tIbDataBase sur la Forme. Cliquez deux fois sur IbDataBase1, renseignez "local", "DataBase", "User Name" "Pass Word" et "Login Prompt".
Vérifiez la connection en basculant IbDataBase1.Connected sur True, puis fermez la connection.
Sélectionnez DefaultTransaction et initialisez-la à IbTransaction1
   placez un tIbQuery sur la tForme
   sélectionnez sa propriété DataBaseName et initialisez-la à IbDataBase1
   placez un tButton sur la Forme et créez sa méthode OnClick. Placez-y les instructions de lecture:

    procedure TForm1.select_Click(Sender: TObject);
      begin
        IbDataBase1.Open;   
        with IbQuery1 do
        begin
          Close;

          with Sql do
          begin
            Clear;
            Add('Select * from formations');
            Open;
          end; // with Sql  
        end; // with IbQuery1
      end; // select_Click

   compilez, exécutez, et cliquez le bouton


Vous pouvez télécharger le source du projet "ib_select.zip".



6.2 - Comment ça Marche

Lorsque nous exécutons IbQuery1.Open:
  • Delphi envoie la requête SELECT au Serveur
  • celui-ci construit une table résultat, en utilisant, bien sûr, la table FORMATIONS, mais aussi éventuellement des tables annexes (index, contraintes etc.).

  • cette table résultat est envoyée via le réseau au Client Interbase. Celui ci la transmet ensuite à l'application. Delphi place alors ce résultat dans un tampon mémoire associé à tIbQuery:



6.3 - Affichage

Une fois que IbQuery a récupéré les données nous pouvons les afficher ou les traiter en mémoire.

Pour afficher les données nous utilisons:

  • un tDataSource qui récupère les données du tIbQuery
  • un ou plusieurs composants d'affichage relié au tDataSource. Par exemple un tDbGrid.
Par conséquent:
   sélectionnez dans la page "Data Access" de la Palette le composant tDataSource:

et placez le sur la Forme

   sélectionnez dans la page "Data Controls" de la Palette le composant tdbGrid:

et placez le sur la Forme

   sélectionnez sa propriété DataSource et donnez-lui la valeur DataSource1

   compilez, exécutez, et cliquez le bouton qui lance la requête


6.4 - Affichage en mode conception

Nous pouvons aussi lancer la requête de lecture en mode conception. Il faut pour cela que:
  • tIbDatabase soit correctement initialisé
  • tIbQuery soit relié à tIbDatabase
  • la propriété tIbQuery.Sql soit initialisée en utilisant l'Inspecteur
  • la requête soit envoyée en basculant tIbQuery.Active sur True
Donc:
   sélectionnez IbQuery1
   cliquez sur sa propriété Sql
   Delphi ouvre l'éditeur de Sql. A présent les tables créées sont affichées:

   sélectionnez la table "formation"
   les colonnes de cette table sont affichées

   sélectionnez les colonnes que vous souhaitez afficher (ou "*")

   fermez l'éditeur de requête
   sélectionnez Active, et basculez sa valeur sur True
   Delphi affiche les valeurs de la table


6.5 - SELECT

 SELECT est la seule instruction de lecture de données du Serveur. Sa structure générale est:

 SELECT colonnes
      FROM tables
      WHERE conditions

et:

  • colonnes indique quelles colonnes nous voulons voir figurer dans le résultat. Nous pouvons
    • citer explicitement les colonnes souhaitées, dans l'ordre qui nous convient:

       SELECT f_nom, f_numero
            FROM formations

      Nous pouvons ne demander qu'une partie des colonnes (projection)

       SELECT f_nom
            FROM formations

      L'abréviation "*" permet de désigner "toutes les colonnes"

       SELECT *
            FROM formations

    • effectuer des sommes, des moyennes (agrégats)

       SELECT COUNT(*)
            FROM formations

      le résultat est alors une table d'une ligne, une colonne avec le nombre de formations

  • tables contient le noms des tables à utiliser pour calculer le résultat. Par exemple:

     SELECT *
          FROM formations, dates
          WHERE f_nom='UML ET DELPHI'

  • conditions permet
    • de ne retenir que certaines lignes du résultat. Par exemple

       SELECT *
            FROM dates
            WHERE f_date> '2004/05/01'

    • de trier le résultat:

       SELECT *
            FROM dates
            ORDER BY f_date

Ce qu'il faut bien comprendre est que:
  • SELECT calcule un résultat A PARTIR de tables, mais que le résultat n'EST PAS les tables. Lorsque nous demandons une somme ou une moyenne, par exemple, SELECT retourne un nombre.
  • Certes, "SELECT * FROM formations" retourne bien les mêmes données que celle de la table sur disque "formations". Mais ce n'est qu'un instantané réalisé lorsque la requête a été lancée. Il s'agit d'une copie. Si un autre utilisateur ajoute une nouvelle formation la copie que nous avons dans le cache attaché à IbQuery ne le reflète pas
  • pour rafraîchir notre cache, la SEULE façon de faire est de relancer la requête (en fermant et réouvrant tIbQuery)


6.6 - Changement de requête

Notre exemple présente une lecture dans laquelle l'utilisateur tape sa requête dans un tEdit. Rien de bien particulier, sauf que ceci permet d'explorer facilement une table (sans utiliser l'Explorateur de bases de données).



6.7 - Requête paramétrée

Chaque fois que nous souhaitons une information, nous devons exécuter un SELECT.

Lorsque la requête est complexe (plusieurs tables, sous-requête, agrégats etc) le Serveur essaye d'optimiser le calcul du résultat. Cette optimisation peut être longue.

Si nous devons exécuter la requête plusieurs fois de suite, Sql offre la possibilité de scinder la requête en deux:

  • envoyer le squelette de la requête contenant des paramètres
  • fournir la valeur des paramètres et demander le résultat
Dans le premier temps, le Serveur calcule sa méthode d'accès, mais n'exécute rien car il lui manque la valeur des paramètres. Lorsque le Serveur reçoit les paramètres, il lance le calcul en utilisant son plan d'évaluation, et retourne le résultat. Et ceci autant de fois que le squelette de la requête ne change pas.

Pour effectuer ce travail en deux temps:

  • nous rédigeons une requête qui contient des paramètres. En Delphi, ces paramètres sont désignés par un identificateur précédé de ":". Par exemple

     SELECT *
          FROM dates
          WHERE f_date= : ma_date

    Notez que:

    • l'identificateur n'a pas besoin d'avoir le même nom que la colonne
    • les paramètres peuvent seulement apparaître dans la clause WHERE (pas dans les champs ou pour le nom des tables)
    • ":" et ma_date sont collés (pas d'espace entre les deux)
  • la requête est envoyée vers le Serveur par

              IbQuery1.Prepare;

  • lorsque nous souhaitons obtenir un résultat
    • nous initialisons la valeur des paramètres en utilisant tIbQuery.Params

                IbQuery1.Params[0].AsString:= '2004/03/01';

      ou

                IbQuery1.ParamByName('ma_date').AsString:= Edit3.Text;

    • nous lançons la requête par:

                IbQuery1.Open;



En pratique:
   créez une nouvelle application et appelez-la "p_ib_parametrized"
   placez un tIbTransaction sur la Forme
   placez un tIbDataBase sur la Forme. Cliquez deux fois sur IbDataBase1, renseignez "local", "DataBase", "User Name" "Pass Word" et "Login Prompt".
Vérifiez la connection en basculant IbDataBase1.Connected sur True, puis fermez la connection.
Sélectionnez DefaultTransaction et initialisez-la à IbTransaction1
   placez un tIbQuery sur la tForme
   sélectionnez sa propriété DataBaseName et initialisez-la à IbDataBase1
   placez un tButton sur la Forme et créez sa méthode OnClick. Placez-y les instructions de préparation:

    procedure TForm1.prepare_Click(Sender: TObject);
      begin
        with IbQuery1 do
        begin
          Close;

          with Sql do
          begin
            Clear;
            Add('SELECT * FROM formations WHERE f_nom= :le_nom');
          end; // with Sql

          Prepare;
        end; // with IbQuery1
      end; // prepare_Click

   placez un tButton sur la Forme et créez sa méthode OnClick. Placez-y les instructions qui initialisent les paramètres et ouvrent la table:

    procedure TForm1.close_params_open_Click(Sender: TObject);
      begin
        with IbQuery1 do
        begin
          // -- close in case has done a previous request
          Close;

          // -- set the parameter values
          with Sql do
            ParamByName('le_nom').AsString:= parameter_edit_.Text;

          // -- send the parameter to the database
          Open;
        end; // with IbQuery1
      end; // close_params_open_Click

   compilez, exécutez, et cliquez le bouton qui lance la requête


Vous pouvez télécharger le source du projet "ib_parametrized.zip".



7 - Modifier des Données

7.1 - Principe

Supposons que notre table FORMATIONS contienne les valeurs suivantes:

Pour changer le libellé "Interbase Delphi" en "Interbase" nous utilisons une requête UPDATE:

 UPDATE formations
     SET f_nom= 'Interbase'
     WHERE f_numero= 8

Cette requête qui modifie les données du Serveur est envoyée vers le Serveur en utilisant tIbQuery1.ExecSql



En détail:
   créez une nouvelle application "p_update_data"
   placez un tIbTransaction sur la Forme
   placez un tIbDataBase sur la Forme. Cliquez deux fois sur IbDataBase1, renseignez "local", "DataBase", "User Name" "Pass Word" et "Login Prompt".
Vérifiez la connection en basculant IbDataBase1.Connected sur True, puis fermez la connection.
Sélectionnez DefaultTransaction et initialisez-la à IbTransaction1
   placez un tIbQuery sur la tForme
   sélectionnez sa propriété DataBaseName et initialisez-la à IbDataBase1
   placez un tButton sur la Forme et créez sa méthode OnClick. Placez-y les instructions de modification:

    procedure TForm1.Button1Click(Sender: TObject);
      begin
        with IbQuery1 do
        begin
          Close;

          with Sql do
          begin
            Clear;
            Add('UPDATE formations SET f_nom=''Interbase'' where f_numero= 3');
          end; // with Sql

          Try
            ExecSql;
          except
            on e:exception do
              display('  *** pb_update'+ e.Message );
          end; // Try...Except
        end; // with IbQuery1
      end; // Button1Click

   compilez, exécutez, et cliquez le bouton


Vous pouvez télécharge le source du projet ib_update_data.zip".



7.2 - La clause WHERE

Dans l'instruction précédente nous avons spécifié la valeur f_numero à l'aide de WHERE.

Que se passerait-il si nous envoyons:

 UPDATE formations
     SET f_nom= 'Interbase'

Eh bien le Serveur modifierait le nom de TOUS les enregistrements.

Si nous souhaitons limiter les modifications à certaines lignes, il est impératif:

  • que chaque ligne de chaque table ait un identificateur unique pour que nous puissions désigner cette ligne en cas de modification
  • que nous indiquions dans UPDATE les lignes à modifier
Si en revanche nous souhaitons modifier plusieurs lignes à la fois, nous pouvons :
  • omettre WHERE. Par exemple, pour passer tous les noms en majuscule:

     UPDATE formations
         SET f_nom= UPPER(f_nom)

  • changer plusieurs lignes satisfaisant une condition. Pour effectuer une réduction promotionnelle de 5% sur les prix inférieurs à 1400 euros :

     UPDATE formations
         SET f_prix= f_prix* 0.95
         WHERE WHERE f_prix<= 1400.00

    Toutefois:

    • ne répétez pas cette requête de réduction trop souvent: il faut bien que je mange un peu !
    • nous faisons des conditions tarifaires particulières (groupes, formations intra, étudiant, particulier, réinsertion...), que nous sommes prêts à vous présenter si vous me téléphonez directement à l'Institut Pascal au 01.42.83.69.36.
Il est donc fondamental de bien comprendre qu'un UPDATE sans clause WHERE porte sur tous les enregistrements.



7.3 - UPDATE et AFFICHAGE

Ce fonctionnement de UPDATE est particulièrement important lorsque nous modifions un enregistrement affiché à l'écran dans un dbGrid: nous pensons que Interbase sait que nous "modifions l'enregistrement courant". Or ce concept d'enregistrement courant n'existe pas en mode SQL:
  • le dbGrid affiche le résultat obtenu par une requête SELECT
  • or SELECT calcule un résultat "instantané". Nous avons vu que SELECT peut supprimer des colonnes (projection), retirer des lignes (filtrage), effectuer des moyennes ou calculer un résultat à partir de plusieurs tables.

    Le résultat du calcul est envoyé par le Serveur vers le Client, et le Serveur ne garde plus aucune trace de ce qu'il a envoyé. Un autre utilisateur peut même modifier les tables sans que notre Client en soit informé

  • par conséquent toute modification du résultat ne pourait être que local.
  • en fait par défaut le résultat est non-modifiable (ReadOnly). Et si nous essayons de taper une nouvelle valeur au clavier, Delphi refuse de modifier le champ du dbGrid.
La seule façon de modifier une valeur affichée de façon interactive est de déclencher une requête UPDATE en utilisant un composant tIbUpdateSql. Le plus simple pour synchroniser les modifications est d'utiliser un composant tIbUpdateSql.



7.4 - tIbUpdateSql

Ce composant a pour vocation de contenir la requête SQL qui doit être exécutée lorsque le tIbQuery doit être modifié. Cette requête est placée dans tIbUpdaateSql.ModifySql.

Automatisons par exemple la mise à jour de f_nom lorsque nous tapons une valeur dans un tDbGrid:
   créez une nouvelle application et appelez-la "p_update_sql"
   placez un tIbTransaction sur la Forme
   placez un tIbDataBase sur la Forme. Cliquez deux fois sur IbDataBase1, renseignez "local", "DataBase", "User Name" "Pass Word" et "Login Prompt".
Vérifiez la connection en basculant IbDataBase1.Connected sur True, puis fermez la connection.
Sélectionnez DefaultTransaction