Accéder à une base de données ODBC avec une édition Personnelle de Delphi

I-A. Introduction▲

I-B. Qu'est-ce que l'ODBC ?▲

I-C. Documentation ODBC▲

I-D. Interface ODBC utilisée▲

Il existe deux méthodes pour se connecter à une source ODBC. La première à l'aide du gestionnaire de sources ODBC de Windows (manuellement ou par programmation), la seconde en indiquant le nom et l'emplacement de la base de données.

II-A. Accès au gestionnaire de sources ODBC (manuel)▲

II-B. Paramétrage du gestionnaire de sources ODBC par programmation▲

Pour manipuler vos données, vous devez d'abord établir une connexion à votre base.

La première chose à faire est de demander un accès au gestionnaire ODBC par la réservation d'un handle d'environnement et de tester si la demande a été autorisée ou pas :

var
// Variables pour la connexion ODBC
    hEnv: SQLHENV;
    RetCode: SQLRETURN;
begin
// Allocation d'un Handle d'environnement
    RetCode := SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, Henv);
    if SQLSucceeded(retcode) then
    // OK
    Else
    // Échec

On déclare donc une variable hEnv de type SQLHENV. Cette variable sera ensuite utilisée pour réaliser une connexion à une source de données.

Pour tester si l'allocation a été réalisée ou pas, on déclare également une variable RetCode de type SQLRETURN. Cette variable sera testée à l'aide de la fonction SQLSucceeded() qui retourne True ou False selon le cas.

Pour réserver notre handle d'environnement, nous utilisons la fonction SQLAllocHandle() en lui fournissant trois paramètres :

• SQL_HANDLE_ENV : le type de handle que l'on souhaite ;
• SQL_NULL_HANDLE : notre handle ne dépend pas d'un handle 'parent' ;
• hEnv : notre variable.

Si la connexion au gestionnaire ODBC s'est bien réalisée, nous devons lui indiquer dans quelle version majeure de l'ODBC nous voulons travailler :

// Fixe la version de l'ODBC
    RetCode := SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, SQLPOINTER(SQL_OV_ODBC3), 0);
    if SQLSucceeded(retcode) then
    // OK
    Else
    // Échec

La fonction SQLSetEnvAttr() permet de réaliser cette opération en nous retournant toujours le résultat dans notre variable RetCode. Une erreur peut survenir si, par exemple, l'utilisateur utilise une version 2 de l'ODBC alors que nous souhaitons travailler en version 3.

Le premier paramètre passé est notre handle d'environnement, suivi de variables indiquant notre paramétrage. Pour plus d'informations sur cette fonction, reportez-vous à la documentation MSDN.

Pour réaliser la connexion à notre base de données, nous devons à nouveau utiliser SQLAllocHandle() pour réserver, cette fois, un handle de connexion :

var
// Variables pour la connexion ODBC
    ...
    hDbc: SQLHDBC;
    ...
begin
    ...
// Allocation d'un Handle de connexion
    RetCode := SQLAllocHandle(SQL_HANDLE_DBC, hEnv, HDbc);
    if sqlsucceeded(retcode) then
    begin
    // OK
    Else
    // Échec d'allocation du handle de connexion

On déclare une variable hdbc de type SQLHDBC puis nous appelons la fonction avec les trois paramètres suivants :

• SQL_HANDLE_DBC : le type de handle que l'on souhaite réserver ;
• hEnv : le handle d'environnement réservé précédemment ;
• hDbc : notre variable.

Si vous accédez à plusieurs bases de données, vous pouvez créer plusieurs handle de connexion (hDbc1, hDbc2, etc.) en utilisant le même handle d'environnement (hEnv).

Vient ensuite le moment tant attendu, la connexion à notre base de données :

1re méthode : en utilisant le gestionnaire de sources ODBC :

// Se connecte à la source ODBC 'NorthWind' 
    RetCode := SQLConnect(hDbc, pchar('NorthWind'), SQL_NTS,  
               pchar('sa'), SQL_NTS, pchar(''), SQL_NTS);
    if sqlsucceeded(retcode) then
    // OK
    Else
    // Échec de connexion

La fonction SQLConnect() attend les paramètres suivants :

• HDbc : notre handle de connexion ;
• Pchar('') : le nom de notre source de données ODBC ;
• SQL_NTS ;
• Pchar('') : le nom d'utilisateur de notre base de données ;
• SQL_NTS ;
• Pchar('') : le mot de passe de l'utilisateur ;
• SQL_NTS.

2e méthode : en indiquant directement le nom de la base :

Var
    ...
    sChaine : String;
    iChaine_Len : Integer;
    siRet : smallInt;
begin     
    ...
// Se connecte à la base "bd1.mdb" en utilisant le driver ACCESS
    iChaine_Len := 256;
    sChaine := StringOfChar(' ',iChaine_Len);
    RetCode := SQLDriverConnect(hDbc, Application.Handle,
               pchar('DRIVER=Microsoft Access Driver (*.mdb); DBQ=' +
               ExtractFilePath(Application.ExeName)+ 'bd1.mdb'),
               SQL_NTS, PChar(sChaine), iChaine_Len, siRet,              
               SQL_DRIVER_NOPROMPT);
    if sqlsucceeded(retcode) then
    // OK
    Else
    // Échec

Cette méthode est un peu plus complexe que la précédente, mais évite un paramétrage du gestionnaire ODBC.

En premier, nous déclarons trois variables sChaine, iChaine_Len et siRet. Nous affectons à iChaine_Len la valeur 256 qui correspondra à la longueur maximale de la chaine de caractères stockée dans sChaine. Ensuite, nous initialisons sChaine avec des espaces pour y effacer les caractères parasites (StringOfChar()).

L'appel de la fonction SQLDriverConnect() se fait avec les paramètres suivants :

• HDbc : notre handle de connexion ;
• Application.handle : handle Windows de notre programme ;
• Pchar('') : chaine de connexion (voir plus bas) ;
• SQL_NTS ;
• Pchar(sChaine) : notre variable sChaine qui recevra la chaine de connexion complète en cas de réussite ;
• iChaine_Len : longueur maximale initialisée pour notre variable sChaine ;
• siRet : variable qui contiendra la longueur de la chaine retournée dans sChaine ;
• SQL_DRIVER_NOPROMPT : pas de fenêtre de connexion si les paramètres sont suffisants pour se connecter à la base de données.

La chaine de connexion contient les paramètres nécessaires pour que le gestionnaire ODBC puisse réaliser correctement la connexion. Il faut indiquer au minimum « DRIVER=[Nom du driver ODBC];DBQ=[Nom de la base de données] ». D'autres paramètres peuvent être ajoutés, comme le nom d'utilisateur, le mot de passe, etc. Reportez-vous à la documentation MSDN pour plus d'informations.

La variable sChaine contiendra, en cas de succès, la chaine de connexion complète. C'est-à-dire les arguments que nous avons passés (DRIVER et DBQ) plus les paramètres par défaut valables pour le type de pilote choisi.

Et voilà, nous venons de réaliser entièrement notre connexion à une base de données !

Lire et écrire dans une base de données s'effectue en utilisant des requêtes SQL qui sont envoyées au gestionnaire ODBC qui va les traduire, à l'aide du pilote, dans le langage de la base de données.

Comme exemple de base de données, nous allons faire simple en imaginant une table nommée « Employes » qui contient uniquement deux champs « Prenom » et « Nom » de type texte pour y stocker les noms du personnel d'une société.

Tout d'abord, réservons un traditionnel handle qui sera cette fois un handle de résultat (statement en anglais) :

var
// Variables pour la connexion ODBC
    ...
    hStmt: SQLSTMT;
    ...
begin
    ...
// Allocation d'un Handle de résultat
    RetCode := SQLAllocHandle(SQL_HANDLE_STMT, hDbc, hStmt);
    if sqlsucceeded(retcode) then
    begin
    // OK
    Else
    // Échec d'allocation du handle de résultat

On déclare une variable hStmt de type SQLHSTMT puis nous appelons la fonction avec les trois paramètres suivants :

• SQL_HANDLE_STMT : le type de handle que l'on souhaite réserver ;
• hDbc : le handle de connexion réservé précédemment ;
• hStmt : notre variable.

Chaque handle de résultat permet d'exécuter une requête SQL et, le cas échéant, d'en extraire le résultat.

Si vous avez besoin d'exécuter plusieurs requêtes en même temps, vous pouvez créer plusieurs handle de résultat (hStmt1, hStmt2, etc.) en utilisant le même handle de connexion (hDbc).

Une fois notre handle réservé, nous pouvons envoyer notre requête à la base de données :

var
    ...
    sSQL : String;
begin     
    ...
// Requête SQL à exécuter
    sSQL := 'Select * from Employes order by Nom';
// Lance la requête
    RetCode := SQLExecDirect(hStmt, Pchar(sSQL), SQL_NTS);
    if sqlsucceeded(retcode) then
    // OK
    Else
    // Échec de la requête SQL

Pour plus de clarté dans le code, la requête SQL sera stockée dans une variable nommée sSQL de type String.

L'exécution d'une requête s'effectue à l'aide de la fonction SQLExecDirect() qui a besoin des paramètres suivants :

• hStmt : le handle de résultat réservé pour cette requête ;
• pChar(sSQL) : texte de la requête à exécuter ;
• SQL_NTS.

Si la requête a pu être exécutée par la base de données, SQLSucceeded() retourna True.

La requête Select précédente nous retourne un jeu d'enregistrements avec le prénom et le nom des employés, classé par le Nom.

Nous afficherons dans un contrôle TListBox le contenu de la 2e colonne (Nom) pour tous les employés :

Var
    ...
    sChaine : String;
    iChaine_Len : Integer;
    iRet : Integer;
begin     
    ...
    iChaine_Len := 50 ;
    sChaine := StringOfChar(' ',iChaine_Len);
// Traite tous les enregistrements 1 à 1
    while (SQLSucceeded(SQLFetch(hstmt))) do
    begin
    // Retourne la 2e colonne
        SQLGetData(hstmt, 2, SQL_C_CHAR, PChar(sChaine), iChaine_Len, iRet);
    // Ajoute le résultat au contrôle TListBox     
        ListBox.Items.Append(Copy(sChaine, 1, iRet));
    end ; 
// libère le handle de résultat réservé
    SQLFreeHandle(SQL_HANDLE_STMT, hStmt);

Nous allons avoir besoin des variables suivantes :

• sChaine pour lire le contenu du champ Nom ;
• iChaine_Len pour indiquer la longueur de texte maximale à lire ;
• iRet pour savoir combien de caractères ont été lus et retournés dans sChaine.

Dans notre base, le nom ne peut excéder 50 caractères, nous affectons donc 50 à iChaine_Len puis initialisons sChaine avec autant d'espaces.

Lorsqu'une requête Select est exécutée, elle ne se positionne sur aucun enregistrement. Pour faire défiler les enregistrements, il faut utiliser la fonction SQLFetch() qui retourne une valeur pouvant être traitée par SQLSucceeded() et qui indique True si un enregistrement a été lu ou False dans le cas contraire (pas d'enregistrement retourné ou dernier enregistrement dépassé). SQLFetch() n'attend qu'un seul paramètre : le handle de résultat (hStmt).

Une fois notre enregistrement lu, il faut extraire la colonne (champ) qui nous intéresse, on utilise pour ça la fonction SQLGetData() qui attend en paramètres :

• hStmt : le handle de résultat de notre requête Select ;
• x : le N de la colonne qui nous intéresse (à partir de 1) ;
• SQL_C_CHAR : le type de valeur lue (ici il s'agit de texte) ;
• Pchar(sChaine) : la variable devant recevoir le résultat ;
• iChaine_Len : la longueur maximale à retourner (Nb de caractères pour du texte) ;
• iRet : variable qui contiendra le nombre de caractères réellement lus.

Il ne nous reste plus qu'à extraire de sChaine le nombre de caractères lus à l'aide de l'instruction copy().

Nous n'avons plus qu'à répéter ces instructions tant que SQLFetch() s'exécute correctement pour parcourir tous les enregistrements retournés par notre requête Select.

Une fois l'opération terminée, il ne nous reste plus qu'à libérer le handle de résultat réservé à l'aide de l'instruction SQLFreeHandle() (voir plus bas l'utilisation de cette fonction).

L'utilisation de requêtes SQL permet de réaliser la plupart des opérations nécessaires pour manipuler les données d'une base. Cependant, vous ne pourrez pas bâtir une requête si vous souhaitez, par exemple, stocker une image dans un champ binaire (blob ou OLE sous Access).

VI-A. Écriture dans la base▲

Update Employes set Photo = ? where lastname = 'Dupond'

var
    ...
    sChaine : String;
    iChaine_Len : Integer;
    iRet : Integer;
    imgStream: tmemorystream;
    iLenStream: integer;
    pStream: Pointer;begin
begin
    ...
    RetCode := SQLPrepare(hstmt, PChar('Update Employes set Photo = ? 
where lastname = ''Dupond'''), SQL_NTS);
    Try
        imgstream := TMemoryStream.Create;
        image1.Picture.Bitmap.SaveToStream(imgStream);
        iLenStream := imgstream.Size;
        pStream := imgstream.Memory;
        RetCode := SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_LONGVARBINARY, iLenStream, 0, pStream, 0, iLenStream);
        if sqlsucceeded(retcode) then
        begin
            RetCode := SQLExecute(hstmt);
            if not sqlsucceeded(retcode) then
                raise ESQLerror.CreateDiag(SQL_HANDLE_STMT, hstmt, RetCode);
        end
        else
            raise ESQLerror.CreateDiag(SQL_HANDLE_STMT, hstmt, RetCode);
        end;
    Finally
        imgstream.Free;
    end;
end;

VI-B. Lecture des données▲

var
  ...
  sRetour: array[0..64000] of char;
  iRet:    integer;
  RetCode: SQLReturn;
  msSteam: tMemoryStream;
begin
  ...
  msStream := tMemoryStream.Create;
  repeat
    RetCode := SQLGetData(hstmt, 1, SQL_C_BINARY, @sRetour[0],
      sizeof(sRetour), iRet);
    if (SQLSucceeded(RetCode)) and (iRet > 0) then
      msStream.WriteBuffer(sRetour, min(iRet, sizeof(sRetour)));
  until RetCode <> SQL_SUCCESS_WITH_INFO;
  msStream.seek(0, soFromBeginning);
  image1.Picture.Bitmap.LoadFromStream(msStream);
end;

Une fois que vous avez fini de travailler sur une source de données, il faut se déconnecter à l'aide de l'instruction SQLDisconnect() qui n'attend comme paramètre que le handle de connexion (hDbc) :

SQLDisconnect(hDbc) ;

Ce n'est qu'une fois la source déconnectée que vous pourrez libérer le handle de connexion.

Une fois qu'un handle n'est plus utile, il faut le libérer à l'aide de l'instruction SQLFreeHandle() qui attend deux paramètres : le type de handle et le handle lui-même.

Dans l'ordre de libération, nous avons donc :

SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv);

Si un code d'erreur est retourné (SQLSucceeded() retourne False) vous pouvez générer une exception à l'aide de l'instruction ESQLerror.CreateDiag() qui a besoin des paramètres Type de handle, handle et numéro d'erreur :

ESQLerror.CreateDiag(SQL_HANDLE_STMT, hStmt, RetCode)
ESQLerror.CreateDiag(SQL_HANDLE_DBC, hDbc, RetCode)
ESQLerror.CreateDiag(SQL_HANDLE_ENV, hEnv, RetCode)

Les informations décrites dans ce chapitre sont provisoires. Des composants seront bientôt disponibles afin de manipuler encore plus facilement des bases de données via ODBC.

La fonction SQLGetData() n'est pas très pratique à utiliser pour extraire autre chose que du texte, et même dans ce cas cela impose plusieurs lignes de code. Pour vous simplifier la vie, l'archive ZIP contient une unité nommée « codbc.pas » qui contient les fonctions suivantes :

SQLGetFloat(hstmt: SQLHSTMT; const iColonne: Integer; var fValeur: Extended): SQLReturn;

SQLGetString(hstmt: SQLHSTMT; const iColonne: Integer; var Chaine: string): SQLReturn;

SQLGetInteger(hstmt: SQLHSTMT; const iColonne: Integer; var iValeur: Integer): SQLReturn;

SQLGetBoolean(hstmt: SQLHSTMT; const iColonne: Integer; var bValeur: Boolean): SQLReturn;

SQLGetStringDate(hstmt: SQLHSTMT; const iColonne: Integer; var Chaine: string): SQLReturn;

Chaque fonction attend le même nombre de paramètres. Les deux premiers sont le handle de résultat (hStmt) et le N de la colonne (champ) à lire. Le 3e paramètre est la variable devant contenir le résultat et qui est donc de type différent pour chaque fonction.

Pour les données binaires, deux fonctions existent :

SQLSendStreamToParam(hstmt: SQLHSTMT; const iParam: integer; var msStream: tMemoryStream; var iLength : SQLLEN): SQLReturn;

SQLGetStream(hstmt: SQLHSTMT; const iColonne: integer; var msStream: tMemoryStream): SQLReturn;

La 1re permet d'affecter un flux tMemoryStream comme paramètre d'une requête SQL en lui fournissant en paramètre le flux et la longueur de ce dernier.

La seconde permet de lire des données binaires et de stocker le résultat dans un flux mémoire initialisé préalablement. Une fois le flux constitué, un appel de tMemoryStream.seek(0, soFromBeginning) est réalisé pour positionner le flux à son début automatiquement.

Cette unité contient également la fonction suivante qui permet de charger un tStringList avec toutes les valeurs d'une colonne (de type texte) pour un jeu de résultat complet :

SQLGetStrings(hstmt: SQLHSTMT; const iColonne: Integer; var Liste: TStringList): SQLReturn;

Nous aurions donc pu utiliser cette fonction dans notre exemple de lecture de requête Select au lieu de la boucle While…

Les informations décrites dans ce chapitre sont provisoires. Des composants seront bientôt disponibles afin de manipuler encore plus facilement des bases de données via ODBC.

L'unité codbc.pas contient également trois fonctions afin de vous simplifier la vie pour lister le nom des colonnes d'une table ou d'une requête, ainsi que de connaître le type de chaque colonne :

SQLGetColumnsName(hdbc: SQLHDBC; const sTable: string; var Liste: TStringList): SQLReturn;

Vous permet de stocker dans un tStringList le nom de toutes les colonnes d'une table. La fonction attend les paramètres : handle de connexion (hDbc), nom de la table et la variable tStringList devant recevoir le résultat.

SQLGetSQLColumnsName(hdbc: SQLHDBC; const sSQL: string; var Liste: TStringList): SQLReturn;

Même fonction que SQLGetColumsName(), mais pour le résultat d'une requête au lieu d'une table. Il faut donc lui fournir le texte de la requête à la place du nom de la table.

SQLGetColumnsTypeName(hdbc: SQLHDBC; const sTable: string; var Liste: TStringList): SQLReturn;

Cette dernière fonction vous retourne le type de chaque champ d'une table dans un tStringList. Les paramètres à indiquer sont : handle de connexion (hDbc), nom de la table et la variable tStringList devant recevoir le résultat.

Giovanny Temgoua, Thierry Aim, Laurent Dardenne pour leurs remarques, suggestions, motivation ;-), etc.