Intégration de requêtes SQL dans les programmes

Table des matières

Intégration de requêtes SQL dans les programmes. 1

Table des matières. 1

1      Note relative aux droits d’auteur 1

2      Introduction. 1

3      Résolution du cas en  Delphi ou BC++ Builder 2

3.1       L’Objet Requête. 2

3.2       Une requête porte sur une base de données. 2

3.3       Une requête est capable de s’exécuter. 2

3.4       Une requête dispose d’une propriété SQL. 2

3.5       Autres méthodes de la classe TQuery. 2

3.6       Voici enfin le code Delphi de la fonction. 2

4      La même fonction en Javascript ASP. 4

5      La même fonction en PHP mysql 4

6      Une procédure stockée Interbase. 5

7      Une procédure stockée PLSQL (ORACLE) 5

8      En Algorithmique: 6

8.1       Un nouveau  type. 7

8.2       Les Primitives: 7

8.3       Primitives déjà vues avec les fichiers. 8

9      Tableau d’implémentation des concepts dans les langages : 8

10        Exercices : 9

10.1     Noms et prénoms des trois plus vieux employés. 9

10.2     Transformation d’un vieux fichier en une belle table toute neuve. 9

10.3     Ce qui pourrait être une évaluation. 9

 

1         Note relative aux droits d’auteur 

Tout ceci est garanti libre de tout droit. C’est réutilisable, et modifiable à souhait. Vous n’avez même pas besoin de citer l’auteur.

Par contre, toute amélioration m’intéresse fortement .

Merci d’avance .

mailto:gilles.thomassin@wanadoo.fr

Le site WEB : www.nexen.net/bts_info_gap

 

 

2         Introduction

Pré-requis :

 

                       

Préambule pour les étudiants étourdis : Les requêtes SQL ne fonctionnent que sur des tables d’une base de données. Inutile donc de penser s’en servir sur des tableaux ou des fichiers.

 

Nous verrons successivement comment intégrer des requêtes SQL dans DELPHI et C++ BUILDER,dans des pages ASP, dans des pages PHP, dans des procédures stockées INTERBASE, et enfin dans des fonctions PLSQL packagées dans des bases ORACLE.

Après avoir essayé de retrouver les points communs entre ces différents langages, nous établirons une liste de nouvelles PRIMITIVES qui nous permettront d’écrire nos algorithmes . Pour cela nous nous appuierons sur le cas suivant :

 

Le cas support

Il s’agit de trouver la somme des 3 plus grosses factures d’un client dont le numéro est saisi par l’utilisateur.

Voici la relation « Facture » extraite du M.L.D.R.

 

FACTURE=(no_fact, date_fact, montant_fact, #no_cli_fact);

 

3         Résolution du cas en  Delphi ou BC++ Builder

3.1      L’Objet Requête

DELPHI propose une classe TQUERY disposant de toutes les propriétés et méthodes nécessaires à l’incorporation de requête dans les programmes.

Une requête sera donc une Instance de la classe TQuery .

Elle est donc déclarée ainsi :

Var Marequete :Tquery ;

et construite ainsi:

MaRequete :=Tquery.create(owner )

 où le owner est le composant propriétaire  de la requête.

Hors sujet :

Le composant propriétaire est celui qui est chargé de détruire l’objet. (libérer la mémoire réservée à l’objet). Lorsqu’il est détruit, il détruit tous les composants dont il est responsable. 

3.2      Une requête porte sur une base de données

La requête est rattachée à la base de donnée sur laquelle elle porte grâce à sa propriété « DatabaseName »

La valeur de la propriété DatabaseName est :

1.      soit un nom de Source de donnée défini à l’aide de l’administrateur ODBC

2.      soit le nom complet d’un répertoire s’il s’agit d’une base de donnée du type DBASE ou Paradox

3.      Soit enfin un nom d’alias défini grâce à l’administrateur du Borland Database Engine.

Nous opterons ici pour la première solution.

MaRequete.DatabaseName :=’DSN_gescom’ ;

3.3      Une requête est capable de s’exécuter.

MaRequete.Execsql ;

 

 

 

3.4      Une requête dispose d’une propriété SQL

C’est elle qui contient la ou les lignes de la requête. Elle est du type Tstrings (tableau de chaînes)

Il est possible de vider ce tableau par la méthode « Clear ».

MaRequete.SQL.Clear ;

Et on y rajoute des lignes comme ceci:

MaRequete.SQL.Add(‘SELECT * FROM client’);

MaRequete.SQL.Add(‘WHERE no_cli=12;’);

 

3.5      Autres méthodes de la classe TQuery

 

Une requête dispose d’une fonction EOFqui renvoie vrai lorsque le dernier enregistrement de la requête est atteint.

 

Elle dispose aussi des méthodes FIRST, LAST, NEXT, PRIOR qui permettent de se déplacer dans l’ensemble des lignes obtenues (ensemble résultat).

 

3.6      Voici enfin le code Delphi de la fonction

Cette fonction renvoie donc la somme des montants des 3 plus grosses factures d’un client dont le numéro est passé en paramètre.

Le principe est le suivant :

On demande la liste des factures du client triées par montant décroissant. Il ne reste plus ensuite qu’à lire les 3 premières lignes et à renvoyer la somme des montants.

Pour la requête : on peut éventuellement la paramétrer, le paramètre est alors précédé du caractère ‘ :’.

 

Rappel : La fonction IntToStr renvoie une chaine contenant la traduction en chaîne de l’entier reçu en paramètre.

 

Function somme_3_plus_grosses_factures(NumeroCli :integer) :real ;

Var

  MaRequete:Tquery;

Begin

  MaRequete:=Tquery.Create(self);

  MaRequete.DatabaseName:=’dsn_gescom’;

  MaRequete.Sql.Clear;

 

//Les deux options ci-dessous sont valables

 

//option 1 : Requête simple

 

  Marequete.Sql.Add(‘Select montant_fact from Facture where no_cli_fact=’+
  IntToStr(NumeroCli) +’order by montant_fact desc;’);

 

//option 2 : Requête paramétrée

 

  Marequete.Sql.Add(‘Select montant_fact from Facture where no_cli_fact = :         LeNumeroDuClient order by montant_fact desc;’);

  Marequete.parambyname(‘LeNumeroDuClient’):=NumeroCli;

 

  MaRequete.Open;

  I:=1;

  Somme:=0;

  While (not (MaRequete.Eof )) and (I<=3) do

  Begin

   Somme:=Somme+MaRequete.FieldByName(‘montant_fact’).AsFloat;

   Ou Somme:=Somme+MaRequete[‘montant_fact’];   (version>=3.0 )

  Ou Somme:=Somme+MaRequete.fields[0].AsFloat;  

   Marequete.Next ;                 //on se place sur l’enregistrement suivant

  End;

  MaRequete.Close;                 //fermeture de la requête

  result :=somme ;                   //renvoi du résultat

end;

 

Nota :
De même qu’il existe une méthode Next il existe:

MaRequete.First  qui se place sur le premier enregistrement.

MaRequete .Last qui se place sur le dernier enregistrement.

MaRequete .Prior qui se place sur le précédent enregistrement.

 

Sous BC++ Builder la classe Tquery reste la même seule la syntaxe pascal est à traduire en c++.

Remplacement de := par =

Reemplacement de = par ==

Remplacement de Begin par {

Remplacement de End par }

Remplacement de And par &&

Remplacement de Not par !

 function Somme3PlusGrosses(no_cli : integer) :integer devient int Somme3PlusGrosses(int no_cli)

Disparition du mot clef VAR

Remplacement du  « . » par « !’ »

 

 

4         La même fonction en Javascript ASP

 Ce script est exécuté par le serveur HTTP et non par le navigateur du poste client.

ASP est un langage propriétaire interprété par IIS livré avec Windows NT ou par PWS livré avec Windows 98.

Le groupe de travail sur le langage PERL est en train de travailler sur un rapprochement du langage PERL avec l’ASP, le langage ainsi défini s’appelle Perl_ASP.

 

remarques :

 les variables en Javascript sont non typées

La syntaxe du Javascript est proche du C

 

 

 <% function somme_des_3_plus_grosses_factures(no_cli) ;

//renvoie la somme des trois + grosses factures du client dont le numéro est passé en //paramètre

{

MaConnexion=Server.Createobject("ADODB.Connection")

            ChaineDeConnexion="DSN=dsn_oracle_scott;UID=Georges;PWD=elini01;"

             MaConnexion.open(ChaineDeConnexion)

            MaRequete=" Select montant_fact from Facture where no_cli_fact="+no_cli+"order by montant_fact desc;"

             ListeDesEnregistrements = MaConnexion.Execute(MaRequete)

            I=1;

            Somme=0;

while ((! ListeDesEnregistrements.eof) && (I<=3)) do

{

  Somme= Somme+ ListeDesEnregistrements("montant_fact");

  I++;

  ListeDesEnregistrements.MoveNext()

}

            return (Somme)

}%>

 

5         La même fonction en PHP mysql

Les scripts PHP sont exécutés par le serveur HTTP et non par le poste client.

PHP tourne avec Apache dans l’environnement UNIX ou Windows NT.

PHP est un langage libre.

Apache est un serveur libre.

 

remarques :

 les variables en PHP sont non typées elles sont précédées d’un $

 La syntaxe du PHP est proche du C

 

< ? Function Somme3PlusGrosses($nNo_Cli)

  mysql_connect("nom du serveur","rolland","monmotdepasse");

  $result = mysql_db_query("nom de la base","SELECT montant_fact FROM Facture WHERE no_cli_fact=$No_Cli ORDER BY montant_fact DESC");

 

   /* tant que l'on n'est pas en fin de requête */

   $Somme=0;

   $Compteur=0;

   while (($enreg = mysql_fetch_row ($result))&& ($compteur<3))

   {

      $Montant = $enr[0];

      $Somme += $montant ;

      $Compteur ++ ;

    };

    return($Somme)

?>

 

nota :

$Somme += $montant ; Û $Somme=$Somme+$Montant

$Compteur++ Û $Compteur=$Compteur+1

Un script PHP est écrit entre < ? et ?>

 

6         Une procédure stockée Interbase

INTERBASE est un puissant SGBDR passé en Open Source en 1999.

Conforme à la norme SQL 92 il supporte : triggers, procédures stockées, langage de définition de contraintes, intégrités référentielles etc.

Disponible en téléchargement chez www.borland.com .

 

Il tourne sur Unix et sur Windows. Des drivers ODBC sont fournis pour les clients Windows.

Remarques :

Les variables doivent être déclarées.

Les blocs de code sont définis entre BEGIN et END

Le bloc FOR SELECT …DO extrait des lignes  comme le ferait un SELECT mais les traite une par une , les place dans la variable citée après INTO  et exécute les instructions situées aprés le DO.

SUSPEND suspend l’exécution de la procédure et retourne la ou  les valeurs à l’application appelante.

 

Voici la procédure :

 

CREATE PROCEDURE Somme3PlusGrosses(NoCli  INTEGER)

            RETURNS (Total INTEGER)

DECLARE VARIABLE Montant INTEGER;

DECLARE VARIABLE Somme INTEGER;

DECLARE VARIABLE I SMALLINT;

As

BEGIN

            Somme=0;

            I=0;

            FOR SELECT Montant_Fact  FROM Facture WHERE No_Cli_Fact= :NoCli

ORDER BY Montant_Fact DESC INTO :Montant

             Do

              BEGIN

                          Somme=Somme+Montant ;

                           I=I+1 ;

                           If (I=3) THEN SUSPEND;

              END

END

 

Remarque :

Ici une seule valeur est retournée puisque I ne passe à 3 qu’une seule fois. Attention, s’il n’y a pas 3 lignes, rien ne sera renvoyé.

Il est donc nécessaire de rajouter après la boucle le code suivant :

If (I <3) THEN SUSPEND ;

 

On remarque aussi que toutes les factures du client vont être lues. Il y a donc beaucoup de lectures inutiles.

7         Une procédure stockée PLSQL (ORACLE)

Oracle est le SGBDR de référence, mais son prix interdit souvent son utilisation.

Il est disponible pour évaluation dans sa version 8.

Il tourne sur beaucoup de systèmes d’exploitation.

Disponible en téléchargement chez www.oracle.com.

 

Remarques :

Les variables doivent être déclarées.

Les blocs de code sont définis entre BEGIN et END ;

L’affectation s’effectue par « := »

On voit apparaître des curseurs qu’il faut ouvrir (open).

Pour lire une ligne dans le curseur il est nécessaire d’effectuer un FETCH INTO

Une boucle se matérialise entre LOOP et END LOOP.

%FOUND renvoie faux quand on est en fin de curseur.

 

Voici la fonction:

 

CREATE FUNCTION Somme3PlusGrosses(NoCli IN  INTEGER)

            RETURN  INTEGER

IS

DECLARE

 Montant INTEGER;

Somme INTEGER;

 I SMALLINT;

CURSOR MonCurseur IS

            SELECT montant_fact FROM Facture WHERE No_Cli_Fact= NoCli

ORDER BY Montant_Fact DESC;

 

BEGIN

            Somme:=0;

            I:=0;

            OPEN MonCurseur;

            FETCH MonCurseur INTO Montant   ;

            WHILE (MonCurseur%FOUND) and (I<3)

            LOOP

Somme=Somme+Montant ;

FETCH MonCurseur INTO Montant   ;

                        I=I+1 ;

            END LOOP;

            RETURN(Somme);             

END ;

 

8         En Algorithmique:

Idées :

-Déclaration d’un type enregistrement définissant la structure des lignes renvoyées

-Déclaration d’une table capable de contenir l’ensemble des lignes renvoyées.

-La table créée est alors manipulable comme un fichier séquentiel avec toutefois la différence suivante : On  pourra remonter d’un enregistrement.

 

MaTable ¬ ExecuterSqlF(cdc,requete)

Où la chaine de connection spécifie: l’utilisateur, son mot de passe et la base de donnée sur laquelle porte la requête.

 

 

 

 

 

Voici enfin l’algorithme :

 

Fonction somme_3_1eres_factures(numero_cli :entier) : réel ;

Types

Tenreg= enregistrement

                          Montant_fact :réel

            Fin Tenreg

 

Variables

 

    ResultatRequete:Table de Tenreg

    MonEnreg : Tenreg

    ChaineDeConnection, Base, Utilisateur, MotDePasse :Chaînes 

 

Début

Base!’dsn_gescom’

Utilisateur!’Georges’

MotDePasse!’elini01’

 

ChainedeConnection ! ‘DSN=’+Base+’UID=’+Utilisateur+’PWD=’+MotDePasse

 

TableResultatRequete ! ExecuterSQLF(ChaineDeConnection, ‘Select montant_fact from Facture where no_cli_fact=’+ EntierVersChaîne(numero_cli)+’;’)

 

I!1;

Somme!0;

 

Tantque  (non (fin (TableResultatRequete )) and (I<=3) faire

   Lire(TableResultatRequete, MonEnreg)

   Somme:=Somme+ MonEnreg.montant_fact);

   Positionner(TableResultatRequete ,suivant

 FTQ

 

  Fermer(TableResultatRequete)

  Retourner(Somme)

Fin

 

8.1      Un nouveau  type

 Table de …

Equivalent à Fichier de sauf que ce n’est pas un fichier mais une Table

(ensemble d’enregistrements issus d’une base de donnée).

 

8.2      Les Primitives:

 

Nous avons besoin de  nouvelles primitives :

 

  1. Fonction ExecuterSQLF (ChainedeConnection :chaine, TexteDeLaRequete :chaine) : Table
    Cette
    Fonction renvoie donc un ensemble d’enregistrement appelé TABLE (et non pas Fichier)
    Elle nécessite 2 paramètres :
    - La Chaîne de connection qui est de la forme
    DSN=nom de la source de donnée ;UID=nomde l’utilisateur ; PWD=mot de passe de l’utilisateur
    - La requête SQL : une requête conforme au standard SQL. Du genre « select * from dept ; »

 

  1. Procédure ExecuterSQLP (ChainedeConnection :chaine, TexteDeLaRequete :chaine) 
    Cette procédure exécute une requête qui ne renvoie pas de résultat.
    donc toute requête SQL qui ne commence pas par SELECT.

 

8.3      Primitives déjà vues avec les fichiers

1) Fonction Fin(MaTable :Table) :booléen

Renvoie vrai si le pointeur est à la fin de la table et faux sinon

 

2) Procédure Lire(UneTable :Table, var UnEnreg : enregistrement)

Cette procédure lit l’enregistrement courant de la table et le recopie dans l’enregistrement passé en paramètre.
(hors sujet : les valeurs de ce paramètre sont modifiées il est donc passé en tant que variable et non en tant que valeur).

 

3) Procedure Positionner(MaTable :Table, {début,fin,suivant,précédent})

   Cette Procédure place le pointeur de MaTable sur la position spécifiée.

   Attention, on ne peut pas faire d’accès direct.

 

4) Fonction Taille(MaTable) :Table

Cette fonction renvoie le nombre d’enregistrement de la table.

 

5) Procédure Fermer(MaTable) :Table

Cette procédure libère la connexion avec la base de données.

 

9         Tableau d’implémentation des concepts dans les langages :

 

CONCEPTS

DELPHI

ASP

PHP

INTERBASE

ORACLE

Connection

Identification de la base, du user et de son mot de passe

*

*

*

*

*

Obtention d’un ensemble résultat

(ouverture)

*

*

*

*

*

Aller au premier enregistrement

*

*

En ouvrant

En ouvrant

En ouvrant

Aller à l’enregistrement suivant

*

*

*

*

*

Aller à l’enregistrement précédent

*

*

non

non

non

Aller au dernier enregistrement

*

*

non

non

non

Indicateur de fin

*

*

*

*

*

Fonction fournissant le nombre de ligne de l’ensemble résultat

*

*

*

?

*

Accès direct à une ligne de l’ensemble résultat

non

non

non

non

non


10   Exercices :

10.1Noms et prénoms des trois plus vieux employés

Ecrire un algorithme qui affiche les noms, prénoms et services (nom des) des 3 employés les mieux payés.

L’extrait du MLDR :

EMP=(no_emp, nom_emp, pnom_emp, ddn_emp,salaire_emp #no_service)

SERVICE=(no_service,nom_service,#no_chef)

10.2Transformation d’un vieux fichier en une belle table toute neuve

Ecrire un algorithme qui transfert un vieux fichier texte séquentiel dans une table de la base de donnée :

Voici un listing du vieux fichier :

 

Dupond

Joel

3 rue des fraises

Dubois

Nicolas

5 place Rideau

Dubosc

Philipe

13 Av des Moineaux

Il y avait 500 contacts donc 1500 lignes dans le fichier

dans une ligne on trouve soit un nom soit un prénom soit une adresse. Les trois premières lignes correspondent au contact n°1, les trois suivantes au contact n°2…

 

N’ayant pas envie de saisir les 1500 lignes vous avez bien sûr préféré écrire un programme qui va réaliser le transfert.

Vous êtes libres de donner la structure voulue à la table CONTACT

 

 

10.3Ce qui pourrait être une évaluation

 

Durée : 1h30.

 

Soient :le fichier fic_commercial: (fichier en accés direct)

No_com

Date_com

No_prod

Prix unitaire

qte

C115

20/09/1999

P18

120

15

C18

18/10/1999

P30

142

5

C115

20/09/1999

P12

250

8

C110

17/09/1999

P8

99

13

 

 

 

 

 

 

et la table Commande.

 

NoCom

DateCom

MontantCom

C115

20/09/1999

3800

C18

18/10/1999

710

C110

17/09/1999

1287

 

 

 

Travail à réaliser :

1) Effectuez les déclarations relatives au fichier « Fic_commercial »

2)Ecrire l’algorithme qui à partir du fichier « Fic_commercial » (préalablement rempli) garnit la table « Commande »

3) Ecrire l’algorithme qui supprime  une commande dont le numéro est saisi par l’utilisateur