Unités de recherche & unités de service

Chapitre X Embedded SQL Chapitre X Embedded SQL

X-1 Exemple de chargement de table en fortran : *
X-2 Utilitaire d'aide à la gestion des programmes : dclgen *
X-3 Utilisation du select en embedded SQL : *

X-3-1 Récupération d'un seul résultat *
X-3-2 Récupération de plusieurs résultats, sans autre appel à la base *
X-3-3 Récupération de plusieurs résultats, avec autre appel à la base *

X-3-3-1 définition d'un curseur sur une requête select donnée *
X-3-3-2 calcul de la requête par l'ordre open *
X-3-3-3 transfert de la ligne résultat avec l'ordre fetch *
X-3-3-4 fermeture du curseur *

X-4 Gestion des valeurs "null" *
X-5 Exemple d'édition de tableau croisé : *
X-6 Utilitaires de précompilation et de compilation *
X-7 Exemple de programme fortran avec curseurs *
X-8 Exemples de procédures et de leur appel *
X-9 Exemple de programme C *

Embedded SQL : ordres SQL inclus dans un programme Fortran, C, etc. Les ordres SQL sont indépendants du langage, et commencent tous par les deux mots clefs exec sql.

X-1 Exemple de chargement de table en fortran : C

C déclaration des variables d'erreur et de contrôle propres à Ingres

exec sql include sqlca

C déclaration des variables locales obligatoire dans un bloc begin-end

exec sql begin declare section

integer cdepcom, cpsdc, i

integer*2 cavg, cdept, ctranche

exec sql end declare section

C fin des déclarations

C traitement des conditions d'erreur

exec sql whenever sqlerror stop

C connexion à la base

exec sql connect parcauto

C lecture des variables locales on prévoit d'aller en 2 en fin de fichier

i = 0

1 read(5, *, end=2) cdepcom, cdept, ctranche, cagv

C chargement répété de lignes ; les variables du langage présentes

C dans un ordre SQL sont obligatoirement précédées par ":"

exec sql repeated insert into commune

& (depcom,dept,tranche,agv)

& values

& (:cdepcom,:cdept,:ctranche,:cagv)

i = i + sqlerr(3)

go to 1

C traitement de fin de saisie

2 print *,' on a rentré ', i, ' lignes'

C fermeture et déconnexion de la base

exec sql disconnect

stop

end

Nota : en C, la variable correspondant à sqlerr(3), s'utilise avec sqlca.sqlerrd(2), et rend le nombre de lignes concernées par l'ordre sql (en modification). Pour des données volumineuses, l'ordre sql copy table est beaucoup plus rapide que l'insert, mais, par contre, aucune intégrité n'est vérifiée par copy table.

X-2 Utilitaire d'aide à la gestion des programmes : dclgen dclgen est un utilitaire de déclaration automatique des variables correspondant à la structure d'une table. L'utilitaire, aussi bien en fortran, qu'en C place implicitement les variables dans des structures (extension du fortran 77) et dans un fichier dont le nom est précisé par l'utilisateur. $ dclgen langage nom_base nom_table n_fich n_struct ex :

$ dclgen fortran parc commune commune.incsql commune

cas particulier en fortran : si vous voulez le résultat dans des variables fortran, plutôt que dans une structure, il faut lancer dclgen ainsi :

$ dclgen -f77 fortran nom_base nom_table n_fich let_prefixe Dans le fichier n_fich on trouvera la déclaration individuelle des variables correspondant à la table nom_tab, préfixée par la lettre indiquée par let_prefixe.

Dans le programme, on se sert de ce fichier à l'aide de l'ordre exec sql include, placé dans le bloc de déclaration.

ex :

exec sql include 'commune.incsql' Toutes les colonnes de la table commune auront un équivalent dans le programme, contenu dans une structure de nom commune_, avec un record commune, déclaré sur ce type de structure (dans le cas de l'exemple dclgen donné ci-dessus).

Cette structure permettra d'échanger des données entre le programme et la base de données.

L'ordre exec sql include permet l'insertion de tout fichier de source embedded sql en tout point d'un programme.

X-3 Utilisation du select en embedded SQL :

X-3-1 Récupération d'un seul résultat

Un select seul, ne peut transférer dans les variables locales qu'une seule ligne ; sa syntaxe dans ce cas est la suivante : exec sql select exp, {exp} into :var, {:var} from ... ex : exec sql select count(ident) into :compt from foyer where an = 84 X-3-2 Récupération de plusieurs résultats, sans autre appel à la base Si le select crée plusieurs lignes résultats, il y a deux possibilités. Soit le select sert uniquement à faire des calculs locaux, sans appel à la base de données entre deux lectures de lignes du résultat du select, alors la syntaxe est la suivante : exec sql select exp, {exp} into :var, {:var} from ...

exec sql begin

/* toute séquence de programmation du langage hôte */

/* sans appel à la base de données */

exec sql end

Attention, rien ne doit séparer la fin de l'ordre select du début de bloc 'begin', ni commentaire, ni ordre du langage hôte. Cette forme de select est la forme la plus efficace lorsqu'elle est possible. Un exemple est donné plus bas. X-3-3 Récupération de plusieurs résultats, avec autre appel à la base Si un appel à la base de donnée est fait pour au moins une des lignes résultats du select, il faut alors utiliser une déclaration différente, qui utilise ce que Ingres appelle des curseurs. Il faut :

déclarer préalablement un curseur.
ouvrir le curseur avec open
dans une boucle, utiliser fetch pour "lire" ligne par ligne le select
hors boucle fermer le curseur avec close

X-3-3-1 définition d'un curseur sur une requête select donnée

exec sql declare n_curs cursor for select ...

X-3-3-2 calcul de la requête par l'ordre open

Cet ordre calcule la requête, et positionne le curseur 'juste avant' la première ligne résultat. exec sql open n_curs

X-3-3-3 transfert de la ligne résultat avec l'ordre fetch

exec sql fetch n_curs into :var1 {, :vari} | :struct

Cet ordre est à placer dans la boucle de traitement, il faut noter que le caractère ":" est obligatoire devant le nom des variables (ou structures) locales du programme. Le nombre (et type) de variables (ou structure), doit correspondre exactement à la demande du select. X-3-3-4 fermeture du curseur

exec sql close n_curs

X-4 Gestion des valeurs "null" Il est toujours possible, lorsque certaines colonnes des tables utilisées dans une application embedded peuvent prendre la valeur null, de les gérer. En fortran il faut déclarer des indicateurs sous forme de variables ou de tableaux (pour les structures) de type fortran integer*2, et de type short en C ; par exemple : integer*2 manq1, manq2, manq3 Dans l'ordre sql où l'on voudra transférer (recevoir ou envoyer) une valeur pouvant être "null", on fera suivre chaque nom de colonne par sa variable indicateur de null : insert into personne (nom, prenom, age, adresse)

values (:nom, :prenom, :age:manq1, :adresse:manq2)

On suppose ici que age et adresse de la table personne peuvent prendre la valeur null, si l'on veut qu'il en soit ainsi, les variables indicatrices doivent avoir la valeur -1, peu importe alors la valeur contenue dans la variable associée. Dans le cas contraire, si l'on reçoit (de Ingres) une valeur non "null", alors l'indicateur sera à 0, et l'on chargera également une valeur non "null" dans une colonne si l'indicateur est à 0. La valeur de l'indicateur est donc prioritaire par rapport à celle de la variable.

X-5 Exemple d'édition de tableau croisé : C

C programme de sortie de tableau croisé

exec sql include sqlca

C l'ordre include ci-dessous suppose que l'on a utilisé dclgen

C avec l'option -f77 avec pour préfixe la lettre c

exec sql begin declare section

exec sql include 'commune.incsql'

integer i, j, icomt, itab(100,9)

exec sql end declare section

C Lorsque les lignes résultats seront épuisées, on passe à 2

exec sql whenever not found go to 2

exec sql connect parc

C1 -- déclaration de select

exec sql select dept, tranche, count(depcom)

& into :cdept, :ctranche, :cnb

& from commune

& group by dept, tranche

exec sql begin

C boucle de traitement (tout code sauf ordre sql)

itab(cdept, ctranche) = cnb

exec sql end

C 2 -- fin d'usage du select

exec sql disconnect

C on passe à l'édition du tableau

print (6,10) ((itab(i,j),

& i=91,94) j= 3,8)

10 format (4i6)

stop

end

Attention : aucune ligne de code ou de commentaire ne doit séparer l'ordre select du début de bloc begin-end. Aucun appel à la base de données ne peut être fait dans le bloc begin-end.

La version ci-dessus est de loin la plus efficace ; ci-dessous, vous trouverez ce qu'aurait été, entre les commentaires C1 et C2, la programmation avec déclaration de curseur :

C1 -- déclaration du curseur

exec sql declare cmpt cursor for

& select dept, tranche, count(depcom)

& from commune

& group by dept, tranche

C ouverture du curseur

exec sql open cmpt

C début de boucle infinie

1 continue

exec sql fetch cmpt into :cdept, :ctranche,

& :icomt

itab(cdept, ctranche) = icomt

go to 1

2 continue

exec sql close cmpt

C2 -- le curseur est fermé

X-6 Utilitaires de précompilation et de compilation Les pseudo-programmes fortran ou C ainsi créés doivent être précompilés au niveau Unix par l'utilitaire Ingres esqlf (esqlc pour C)

le fichier programme doit être suffixé par .sf (pour fortran, .sc pour C)

$ esqlf tabcom.sf esqlf crée un programme source fortran de nom tabcom.f

Il faut alors compiler et lier l'objet à la bibliothèque de sous-programmes fournie par Ingres, avec l'utilitaire du CIR "femb" :

$ femb tabcom.f qui crée le fichier exécutable tabcom

(utiliser cemb pour un programme C)

femb et cemb sont des produits CIR, non renseignés dans les documentations Ingres.

X-7 Exemple de programme fortran avec curseurs C

C ceci est la copie d'un véritable programme utilisateur

C On a des demandes de voyages dans demande,

C entre coo (origine) et cod (destination),

C à une date déterminée.

C On regarde l'existence de trains entre coo et cod

C dans od2, puis l'on regarde, si ces trains

C existent soit la veille, le jour, ou le lendemain

C de la date déterminée

C dans fcalt pour les trains trouves dans od2

exec sql include sqlca

exec sql begin declare section

integer*2 hd, ic, dep, jd, md, arr, ha, ja, ma, ar

integer*2 oho, ohd, dist, g1, g2, sens

integer num, coo, cod, tabcol(2)

integer como, comd, go, gd, tab1, tab2, numf,circ(2)

exec sql end declare section

C Quand un curseur a balayé toutes les lignes d'un select,

C on va à l'étiquette 100

exec sql whenever not found goto 100

C En cas d'erreur sql, on appelle le sous-programme

C utilisateur erring

exec sql whenever sqlerror call erring

open(10, file = 'erreurs1')

C ouverture de la base

exec sql connect baseod

C numf numéro de voyageur, on arrêtera la recherche à

C ce numéro

numf = 11904

C déclaration du curseur pour recherche des trains

exec sql declare demande cursor for select

1 d.num, d.coo, d.cod, d.ar, d.dep, d.hd, d.jd,

2 d.md, d.arr, d.ha, d.ja, d.ma,

3 o.como, o.comd, o.go, o.gd, o.ho, o.hd, o.d,

4 o.tab1,

5 o.g1, o.tab2, o.g2, o.sens

6 from demande d, od2 o where

7 o.como = d.coo and

8 o.comd = d.cod and

9 num between 11900 and :numf

0 order by num;

C déclaration du curseur pour recherche dans le calendrier

C des trains

exec sql declare fcal cursor for

1 select tabcol, int4( right(trim(left(cal,

2 :dep +1)),3))

3 from fcalt

4 where tabcol = :tab1 or tabcol = :tab2

C ouverture du curseur de recherche d'existence de train

exec sql open demande

50 continue

C boucle sur le curseur demande, récupération dans les

C variables (autant et de même type que celles demandées

C dans le select)

exec sql fetch demande into :num, :coo, :cod,

1 :ar, :dep,

2 :hd, :jd, :md, :arr, :ha, :ja, :ma,

3 :como, :comd, :go, :gd, :oho, :ohd, :dist,

4 :tab1, :g1, :tab2, :g2, :sens

print *,'num=',num

if(tab1.eq.50002114.or.tab2.eq.50002114)goto 50

C pour chaque voyageur, on ouvre/ferme le curseur fcal

exec sql open fcal

do 10 i=1,2

C boucle sur le curseur fcal et récupération

C dans des variables

exec sql fetch fcal into :tabcol(i),:circ(i)

10 continue

write (10,2000) tabcol(1),circ(1),tabcol(2),circ(2)

2000 format(2(i10,i4))

ic = circ(1) + circ(2)

do 30 i = 1, 3

idix = 10**(i-1)

if( mod( ic/idix, 10).eq.2) go to 31

30 continue

go to 32

31 continue

C les trains intéressants sont stockes dans result,

C avec la demande associée

exec sql insert into result values(

1 :num, :como, :comd, :go, :gd, :oho, :ohd, :dist,

2 :tab1, :g1, :tab2, :g2 ,:sens, :dep, :hd, :jd,

3 :md, :arr, :ha, :ja, :ma, :ic)

print *, 'insert dans result'

32 continue

C important, fermeture de fcal avant de passer au

C voyageur suivant

exec sql close fcal

goto 50

100 continue

C si l'un des deux curseurs ne rend plus de recherche,

C on arrive ici

print *, 'juste apres 100'

exec sql close fcal

print *, 'num = ', num, 'tab1 = ', tab1, 'tab2 = ', tab2

write (10,3000) num,tab1,tab2

3000 format (3i10)

exec sql close demande

C Placer des commit aussi souvent que possible

C quand il y a des modifs

C dans une base, mais attention, il ferme tous les

C curseurs ouverts

exec sql commit

exec sql disconnect

stop

end

subroutine erring

C gère l'impression des messages d'erreur que peut

C fournir un ordre sql

exec sql include sqlca

exec sql begin declare section

character * 256 mess

exec sql end declare section

exec sql whenever sqlerror continue

exec sql inquire_sql (:mess = ERRORTEXT)

print *, 'sqlcod = ',sqlcod

print *, mess

return

end

X-8 Exemples de procédures et de leur appel Les procédures ont été décrites dans le chapitre SQL (§27), en voici, en embedded sql, une autre application.

La procédure décrite ci-dessous a 4 arguments ; cette procédure est à créer en interactif (généralement) dans isql. L'expression sur les dates calcule si la valeur de la colonne date (dans la table approchants) est comprise entre l'argument date plus ou moins 30 minutes ; le nombre de lignes trouvées est retourné par la variable iirowcount implicite dans les procédures. Cette valeur sera récupérée (voir source plus loin) dans la variable nbaset.

Remarque : l'argument date n'est pas de type date, on utilise donc la fonction de conversion date(). Évidemment l'argument date doit avoir un bon format de présentation pour cette fonction.

create procedure insapproch (

org char(1), dep char(3), com char(3), date char(17)) as

begin

insert into approchants

select * from caracteristiques

where org = :org and

dep = :dep and

com = :com and

abs(interval('min', date - date(:date))) <= 30;

return :iirowcount;

end;

Autre exemple de procédure avec insertion ; attention ! dans les requêtes, les arguments sont toujours précédés d'un ":" create procedure inslieux(

retrix integer, num integer) as

begin

insert into lieux (

retrix, rout, cft, cadm, loc, nrte, ind, typr, pr,

circ, nvoi, pcyc, prof, tplan, surf, lcs, ltpc, lbau,

ltro, lacc, cr1, cr2, marq, etat, atac, atpr, ouv)

select

:retrix, rout, cft, cadm, loc, nrte, ind, typr, pr,

circ, nvoi, pcyc, prof, tplan, surf, lcs, ltpc, lbau,

ltro, lacc, cr1, cr2, marq, etat, atac, atpr, ouv

from lieuxacc

where num = :num;

end;

-------------------------------- C

C extrait du programme fortran où ces procédures

C sont utilisées

C on récupère dans nbaset le nb de lignes insérées

exec sql execute procedure insapproch (org = :org,

+ dep = :dep, com = :com, date = :date) into :nbaset

call prerr(iprint, 'insert into approchant')

C .... et plus loin dans le programme .....

C insertion des lieux

exec sql execute procedure inslieux (retrix =:retrix,

+ num=:num)

call prerr(iprint, 'inslieux ')

go to 2001

end

X-9 Exemple de programme C Exemple : recherche dans la table "arcs" d'une base; on a exécuté au préalable la commande dclgen pour générer une structure correspondant à cette table : $ dclgen c mabase arcs arcs.h arcs Voici le source du programme embedded C : /* FICHIER messai.sc */

/* programme d'exemple en C pour lire les temps de

parcours dans la table "arcs" et mesurer les temps

cpu et residents pris par cette lecture */

/*le programme est appelé en donnant le nom de la base en paramètre */

/* exemple : messai mabase */

#include <stdio.h>

/* pour récupérer les codes d'erreurs */

exec sql include sqlca;

main(arg_count,arg_vector)

unsigned arg_count;

char *arg_vector[];

{

/* déclarations des variables locales du programme */

exec sql begin declare section;

exec sql include "arcs.h";

/* le fichier arcs.h, créé par la commande dclgen,

contient la description de la table "arcs"

sous forme d'une structure C de ce type :*/

struct arcs_ {

long numarc;

long norig;

long ndest;

long longueur;

long temps;

} arcs;

int time, timea, etime, etimea;

int nba;

int tparc[5000];

char basename[30];

exec sql end declare section;

/*fin des déclarations */

nba=0;

/* lecture du nom de base dans "basename" */

sscanf(arg_vector[1],"%s",basename);

exec sql connect :basename; /*connexion a la base */

/* insertion du code pour interroger les variables _cpu_ms

et _et_sec qui vont donner les temps cpu et elapsed */

exec sql select int4(dbmsinfo('_cpu_ms')) into :time;

timea=time;

exec sql select int4(dbmsinfo('_et_sec')) into :etime;

etimea=etime;

/* question sql: on va chercher, par une boucle, tous les

tuples de la table "arcs", ranger les valeurs dans la

structure arcs et prendre le temps de parcours dans un

tableau tparc */

exec sql select * into :arcs

from arcs;

exec sql begin;

tparc[nba]=arcs.temps;

nba++;

exec sql end;

if(sqlca.sqlcode!=0) {

printf("erreur %d\n",sqlca.sqlcode);

}

/* code pour rechercher les nouvelles valeurs de temps après la requête */

exec sql select int4(dbmsinfo('_cpu_ms')) into :time;

exec sql select int4(dbmsinfo('_et_sec')) into :etime;

/* on imprime les valeurs de temps mesurées */

printf("temps cpu %d ms\n",time-timea);

printf("temps elapsed %d s\n",etime-etimea);

/* déconnexion/

exec sql disconnect ;

printf("nb arcs %d\n",nba);

}

précompilation du programme $ esqlc messai.sc

ESQL messai.sc:

compilation et édition de liens du programme $ cemb messai.c exécution $ messai mabase

temps cpu 50 ms

temps elapsed 0 s

nb arcs 1950