Ce chapitre décrit le pacquage SQL intégré (ou embarqué) pour PostgreSQL ECPG. Il est compatible avec les langages C et C++ et a été développé par Linus Tolke et Michael Meskes.
Un programme SQL intégré est en fait un programme ordinaire, dans notre cas un programme en langage C, dans lequel nous insérons des commandes SQL incluses dans des sections spécialement marquées. Ainsi les instructions Embedded SQL commencent par les mots EXEC SQL et se terminent par un point-virgule (« ; »). Pour générer l’exécutable, le code source est d’abord traduit par le préprocesseur SQL qui convertit les sections SQL en code source C ou C++, après quoi il peut être compilé de manière classique.
Le SQL intégré présente des avantages par rapport à d’autres méthodes pour prendre en compte des commandes SQL dans du code C. Par exemple, le passage des informations de et vers les variables du programme C est entièrement pris en charge. Ensuite, le code SQL du programme est vérifié syntaxiquement au moment de la précompilation. Enfin, le SQL intégré en C est spécifié dans le standard SQL et supporté par de nombreux systèmes de bases de données SQL. L’implémentation PostgreSQL est conçue pour correspondre à ce standard autant que possible, afin de rendre le code facilement portable vers des SGBD autre que PostgreSQL.
Comme alternative au SQL intégré, on peut citer l’utilisation d’une API (Application Programming Interface) permettant au programme de communiquer directement avec le SGBD via des fonctions fournies par l’API. Dans ce cas de figure, il n’y a pas de précompilation à effectuer. Se référer à la documentation PostgreSQL [28] pour plus d’information à ce sujet : Chapitre 27. libpq – Bibliothèque C.
Quelque soit le langage utilisé (C, Java, PHP, etc.), pour pouvoir effectuer un traitement sur une base de données, il faut respecter les étapes suivantes :
Nous allons voir dans cette section comment ouvrir et fermer une connexion, et nous verrons dans les sections suivantes comment effectuer des traitements.
La connexion à une base de données se fait en utilisant l’instruction suivante :
EXEC SQL CONNECT TO cible [AS nom_connexion] [USER utilisateur];
La cible cible peut être spécifiée de l’une des façons suivantes :
nom_base[@nom_hôte ][:port] ;tcp:postgresql://nom_hôte [:port ] [/nom_base][? options] ;unix:postgresql://nom_hôte[: port][/nom_base ][? options] ;En pratique, utiliser une chaîne littérale (entre guillemets simples) ou une variable de référence génère moins d’erreurs. La cible de connexion DEFAULT initie une connexion sur la base de données par défaut avec l’utilisateur par défaut. Aucun nom d’utilisateur ou nom de connexion ne pourrait être spécifié isolément dans ce cas.
Il existe également différentes façons de préciser l’utilisateur utilisateur :
nom_utilisateurnom_utilisateur/ mot_de_passenom_utilisateur IDENTIFIED BY mot_de_passenom_utilisateur USING mot_de_passenom_utilisateur et mot_de_passe peuvent être un identificateur SQL, une chaîne SQL littérale ou une référence à une variable de type caractère.
nom_connexion est utilisé pour gérer plusieurs connexions dans un même programme. Il peut être omis si un programme n’utilise qu’une seule connexion. La dernière connexion ouverte devient la connexion courante, utilisée par défaut lorsqu’une instruction SQL est à exécuter.
Pour fermer une connexion, utilisez l’instruction suivante :
EXEC SQL DISCONNECT [connexion];
Le paramètre connexion peut prendre l’une des valeurs suivantes :
Si aucun nom de connexion n’est spécifié, c’est la connexion courante qui est fermée. Il est préférable de toujours fermer explicitement chaque connexion ouverte.
Toute commande SQL, incluse dans des sections spécialement marquées, peut être exécutée à l’intérieur d’une application SQL intégré. Ces sections se présentent toujours de la manière suivante :
EXEC SQL instructions_SQL ;
Dans le mode par défaut, les instructions ne sont validées que lorsque EXEC SQL COMMIT est exécuté. L’interface SQL intégré supporte aussi la validation automatique des transactions via l’instruction EXEC SQL SET AUTOCOMMIT TO ON. Dans ce cas, chaque commande est automatiquement validée. Ce mode peut être explicitement désactivé en utilisant EXEC SQL SET AUTOCOMMIT TO OFF.
Voici un exemple permettant de créer une table :
EXEC SQL create table individu ( num_individu integer primary key,
nom varchar(64), prenom varchar(64) );
EXEC SQL COMMIT;
La transmission de données entre le programme C et le serveur de base de données est particulièrement simple en SQL intégré. En effet, il est possible d’utiliser une variable C, dans une instruction SQL, simplement en la préfixant par le caractère deux-points (« : »). Par exemple, pour insérer une ligne dans la table individu on peut écrire :
EXEC SQL INSERT INTO individu VALUES (:var_num, 'Poustopol', :var_prenom);
Cette instruction fait référence à deux variables C nommées var_num et var_prenom et utilise aussi une chaîne littérale SQL (’Poustopol’) pour illustrer que vous n’êtes pas restreint à utiliser un type de données plutôt qu’un autre.
Dans l’environnement SQL, nous appelons les références à des variables C des variables hôtes.
Les variables hôtes sont des variables de langage C identifiées auprès du préprocesseur SQL. Ainsi, pour être définies, les variables hôtes doivent être placées dans une section de déclaration, comme suit :
EXEC SQL BEGIN DECLARE SECTION; declarations_des_variables_C EXEC SQL END DECLARE SECTION;
Vous pouvez avoir autant de sections de déclaration dans un programme que vous le souhaitez.
Les variables hôtes peuvent remplacer les constantes dans n’importe quelle instruction SQL. Lorsque le serveur de base de données exécute la commande, il utilise la valeur de la variable hôte. Notez toutefois qu’une variable hôte ne peut pas remplacer un nom de table ou de colonne. Comme nous l’avons déjà dit, dans une instruction SQL, le nom de la variable est précédé du signe deux-points (« : ») pour le distinguer d’autres identificateurs admis dans l’instruction.
Les initialisations sur les variables sont admises dans une section de déclaration. Les sections de déclarations sont traitées comme des variables C normales dans le fichier de sortie du précompilateur. Il ne faut donc pas les redéfinir en dehors des sections de déclaration. Les variables qui n’ont pas pour but d’être utilisées dans des commandes SQL peuvent être normalement déclarées en dehors des sections de déclaration.
Les variables en langage C ont leur portée normale au sein du bloc dans lequel elles sont définies. Toutefois, le préprocesseur SQL n’analyse pas le code en langage C. Par conséquent, il ne respecte pas les blocs C. Aussi, pour le préprocesseur SQL, les variables hôtes sont globales : il n’est pas possible que deux de ces variables portent le même nom.
Seul un nombre limité de types de données du langage C est supporté pour les variables hôtes. En outre, certains types de variable hôte n’ont pas de type correspondant en langage C. Dans ce cas, des macros prédéfinies peuvent être utilisées pour déclarer les variables hôtes. Par exemple, le type prédéfini VARCHAR est la structure adéquate pour interfacer des données SQL de type varchar. Une déclaration comme
VARCHAR var[180];
est en fait convertie par le préprocesseur en une structure :
struct varchar_var { int len; char arr[180]; } var;
Dans le cas d’une requête de ligne unique, c’est à dire qui n’extrait pas plus d’une ligne de la base de données, les valeurs renvoyées peuvent être stockées directement dans des variables hôtes. Cependant, contrairement au langage C ou C++, le SQL est un langage ensembliste : une requête peut très bien retourner plus d’une ligne. Dans ce cas, il faut faire appel à la notion de curseur que nous abordons dans la section 4.11.7.
Dans le cas d’une requête de ligne unique, une nouvelle clause INTO est intercalée entre la clause SELECT et la clause FROM. La clause INTO contient une liste de variables hôtes destinée à recevoir la valeur de chacune des colonnes mentionnées dans la clause SELECT. Le nombre de variables hôtes doit être identique au nombre de colonnes de la clause SELECT. Les variables hôtes peuvent être accompagnées de variables indicateur afin de prendre en compte les résultats NULL (cf. section 4.11.5).
Lors de l’exécution de l’instruction SELECT, le serveur de base de données récupère les résultats et les place dans les variables hôtes. Si le résultat de la requête contient plusieurs lignes, le serveur renvoie une erreur. Si la requête n’aboutit pas à la sélection d’une ligne, un avertissement est renvoyé. Les erreurs et les avertissements sont renvoyés dans la structure SQLCA, comme décrit dans la section 4.11.6.
Par exemple, en reprenons la base de données de la séance de travaux pratiques 4.4 et une requête que nous avons déjà rencontrée section 4.7.2 : « nombre de fois que chacun des films a été projeté ». Nous pouvons récupérer les résultats de cette requête de ligne unique dans des variables hôtes de la manière suivante :
EXEC SQL BEGIN DECLARE SECTION; VARCHAR titre[128]; int id_film; int nb_proj; EXEC SQL END DECLARE SECTION; EXEC SQL SELECT num_film, titre, COUNT(*) INTO :id_film, :titre, :nb_proj FROM film NATURAL JOIN projection GROUP BY num_film, titre;
Les variables indicateur sont des variables en langage C qui fournissent des informations complémentaires pour les opérations de lecture ou d’insertion de données. Il existe plusieurs types d’utilisation pour ces variables.
Une variable indicateur est une variable hôte de type int suivant immédiatement une variable hôte normale dans une instruction SQL.
Dans les données SQL, la valeur NULL représente un attribut inconnu ou une information non applicable. Il ne faut pas confondre la valeur NULL de SQL avec la constante du langage C qui porte le même nom (NULL). Cette dernière représente un pointeur non initialisé, incorrect ou ne pointant pas vers un contenu valide de zone mémoire.
La valeur NULL n’équivaut à aucune autre valeur du type défini pour les colonnes. Ainsi, si une valeur NULL est lue dans la base de données et qu’aucune variable indicateur n’est fournie, une erreur est générée (SQLE_NO_INDICATOR). Pour transmettre des valeurs NULL à la base de données ou en recevoir des résultats NULL, des variables hôtes d’un type particulier sont requises : les variables indicateur.
Par exemple, dans l’exemple précédent, une erreur est générée si, pour une raison quelconque, le titre du film n’existe pas et que sa valeur est NULL. Pour s’affranchir de ce problème, on utilise une variable indicateur de la manière suivante :
EXEC SQL BEGIN DECLARE SECTION; VARCHAR titre[128]; int id_film; int nb_proj; int val_ind; EXEC SQL END DECLARE SECTION; EXEC SQL SELECT num_film, titre, COUNT(*) INTO :id_film, :titre :val_ind, :nb_proj FROM film NATURAL JOIN projection GROUP BY num_film, titre;
Dans cet exemple, la variable indicateur val_ind vaudra zéro si la valeur retournée n’est pas NULL et elle sera négative si la valeur est NULL. Si la valeur de l’indicateur est positive, cela signifie que la valeur retournée n’est pas NULL mais que la chaîne a été tronquée pour tenir dans la variable hôte.
L’instruction WHENEVER est une méthode simple pour intercepter les erreurs, les avertissements et les conditions exceptionnelles rencontrés par la base de données lors du traitement d’instructions SQL. Elle consiste à configurer une action spécifique à exécuter à chaque fois qu’une condition particulière survient. Cette opération s’effectue de la manière suivante :
EXEC SQL WHENEVER condition action;
Le paramètre condition peut prendre une des valeurs suivantes :
Le paramètre action peut avoir une des valeurs suivantes :
Le standard SQL ne définit que les actions CONTINUE et GOTO ou GO TO.
L’instruction WHENEVER peut être insérée en un endroit quelconque d’un programme SQL intégré. Cette instruction indique au préprocesseur de générer du code après chaque instruction SQL. L’effet de cette instruction reste actif pour toutes les instructions en SQL intégré situées entre la ligne de l’instruction WHENEVER et l’instruction WHENEVER suivante contenant la même condition condition d’erreur, ou jusqu’à la fin du fichier source.
Les conditions d’erreur sont fonction du positionnement dans le fichier source de langage C et non du moment où l’instruction est exécutée.
Cette instruction est fournie pour vous faciliter le développement de programmes simples. Il est plus rigoureux de contrôler les conditions d’erreur en vérifiant directement le champ sqlcode de la zone SQLCA (cf. section suivante). Dans ce cas, l’instruction WHENEVER est inutile. En fait, l’instruction WHENEVER se contente de demander au préprocesseur de générer un test if ( SQLCODE ) après chaque instruction SQL.
La zone de communication SQL (SQLCA) est une zone de mémoire qui permet, pour chaque demande adressée à la base de données, de communiquer des statistiques et de signaler des erreurs. En consultant la zone SQLCA, vous pouvez tester un code d’erreur spécifique. Un code d’erreur s’affiche dans les champs sqlcode et sqlstate lorsqu’une requête adressée à la base de données provoque une erreur. Une variable SQLCA globale (sqlca) est définie dans la bibliothèque d’interface, elle a la structure suivante :
struct {
char sqlcaid[8];
long sqlabc;
long sqlcode;
struct {
int sqlerrml;
char sqlerrmc[70];
} sqlerrm;
char sqlerrp[8];
long sqlerrd[6];
char sqlwarn[8];
char sqlstate[5];
} sqlca;
SQLCA couvre à la fois les avertissements et les erreurs. Si plusieurs avertissements ou erreurs surviennent lors de l’exécution d’une instruction, alors sqlca ne contient que les informations relatives à la dernière. Si aucune erreur ne survient dans la dernière instruction SQL, sqlca.sqlcode vaut 0 et sqlca.sqlstate vaut "00000". Si un avertissement ou une erreur a eu lieu, alors sqlca.sqlcode sera négatif et sqlca.sqlstate sera différent de "00000".
Les champs sqlca.sqlstate et sqlca.sqlcode sont deux schémas différents fournissant des codes d’erreur. Les deux sont spécifiés dans le standard SQL mais sqlcode est indiqué comme obsolète dans l’édition de 1992 du standard et a été supprimé dans celle de 1999. Du coup, les nouvelles applications sont fortement encouragées à utiliser sqlstate.
Lorsque vous exécutez une requête dans une application, le jeu de résultats est constitué d’un certain nombre de lignes. En général, vous ne connaissez pas le nombre de lignes que l’application recevra avant d’exécuter la requête. Les curseurs constituent un moyen de gérer les jeux de résultats d’une requête à lignes multiples.
Les curseurs vous permettent de naviguer dans les résultats d’une requête et d’effectuer des insertions, des mises à jour et des suppressions de données sous-jacentes en tout point d’un jeu de résultats.
Pour gérer un curseur vous devez respecter les étapes suivantes :
EXEC SQL DECLARE nom_curseur CURSOR FOR requête_select ;
EXEC SQL OPEN nom_curseur ;
FETCH [ [ NEXT | PRIOR | FIRST | LAST | { ABSOLUTE | RELATIVE } nombre ]
{ FROM | IN } ] nom_curseur
INTO liste_variables
EXEC SQL CLOSE nom_curseur ;
Lors de son ouverture, un curseur est placé avant la première ligne. Par défaut, les curseurs sont automatiquement refermés à la fin d’une transaction.
Voici un exemple utilisant la commande FETCH :
EXEC SQL BEGIN DECLARE SECTION;
int v1;
VARCHAR v2;
EXEC SQL END DECLARE SECTION;
...
EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test;
EXEC SQL OPEN foo;
while (...) {
EXEC SQL FETCH NEXT FROM foo INTO :v1, :v2;
...
}
Pour inclure un fichier externe SQL intégré dans votre programme, utilisez la commande :
EXEC SQL INCLUDE nom_fichier;
Cette commande indique au préprocesseur du SQL intégré de chercher un fichier nommé nom_fichier.h, de traiter et de l’inclure dans le fichier C généré. Du coup, les instructions SQL intégré du fichier inclus sont gérées correctement.
En utilisant la directive classique
#include <nom_fichier.h>
le fichier nom_fichier.h ne serait pas sujet au pré-traitement des commandes SQL. Naturellement, vous pouvez continuer à utiliser la directive #include pour inclure d’autres fichiers d’en-tête.
La première étape consiste à traduire les sections SQL intégré en code source C, c’est-à-dire en appels de fonctions de la librairie libecpg. Cette étape est assurée par le préprocesseur appelé ecpg qui est inclus dans une installation standard de PostgreSQL. Les programmes SQL intégré sont nommés typiquement avec une extension .pgc. Si vous avez un fichier programme nommé prog.pgc, vous pouvez le passer au préprocesseur par la simple commande :
ecpg prog1.pgc
Cette étape permet de créer le fichier prog.c. Si vos fichiers en entrée ne suivent pas le modèle de nommage suggéré, vous pouvez spécifier le fichier de sortie explicitement en utilisant l’option -o.
Le fichier traité par le préprocesseur peut alors être compilé de façon classique, par exemple :
cc -c prog.c
Cette étape permet de créer le fichier prog.o. Les fichiers sources en C générés incluent les fichiers d’en-tête provenant de l’installation de PostgreSQL. Si vous avez installé PostgreSQL à un emplacement qui n’est pas parcouru par défaut, vous devez ajouter une option comme -I/usr/local/pgsql/include sur la ligne de commande de la compilation.
Vous devez enfin lier le programme avec la bibliothèque libecpg qui contient les fonctions nécessaires. Ces fonctions récupèrent l’information provenant des arguments, exécutent la commande SQL en utilisant l’interface libpq et placent le résultat dans les arguments spécifiés pour la sortie. Pour lier un programme SQL intégré, vous devez donc inclure la bibliothèque libecpg :
cc -o monprog prog.o -lecpg
De nouveau, vous pourriez avoir besoin d’ajouter une option comme -L/usr/local/pgsql/lib sur la ligne de commande.
Voici un exemple complet qui effectue les opérations suivantes :
#include <stdio.h>
// ____ pour gérer les erreurs
EXEC SQL INCLUDE sqlca;
// ____ Définition des variables hôtes
EXEC SQL BEGIN DECLARE SECTION;
char var_nom[256];
char var_prenom[256];
int var_num;
EXEC SQL END DECLARE SECTION;
int main(void){
// ____ Ouverture de la connexion à la base de données
EXEC SQL CONNECT TO nom_base@aquanux;
if(sqlca.sqlcode) {
printf("erreur %s\n",sqlca.sqlerrm.sqlerrmc);
exit(0);
}
printf(" connexion réussie \n");
// ____ Utilisation d'un curseur pour afficher le contenu de la table individu
EXEC SQL DECLARE curseur_individu CURSOR FOR
SELECT num_individu, nom, prenom FROM individu;
EXEC SQL OPEN curseur_individu;
// Boucle d'affichage
while(SQLCODE==0) {
EXEC SQL FETCH FROM curseur_individu INTO :var_num, :var_nom, :var_prenom;
printf("L'individu %d est %s %s\n", var_num, var_prenom, var_nom);
}
EXEC SQL CLOSE curseur_individu;
// ____ Fermeture de connexion
printf(" Déconnexion \n");
EXEC SQL DISCONNECT;
return 0;
}
En supposant que ce programme est enregistré dans un fichier nommé prog.pgc, l’exécutable est obtenu de la manière suivante :
ecpg prog.pgc cc -c prog.c cc -o prog prog.o -lecpg