Dveloppement des Applications des Bases de Donnes Chapitre

Développement des Applications des Bases de Données Chapitre 6 Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 1

Survol SQL dans les codes d’application v SQL imbriqué v Curseurs v SQL dynamique v JDBC v SQLJ v Procédures stockées v Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 2

SQL dans les codes d’Application v Les commandes SQL peuvent être appelées à partir d’un programme d’application ( «langage hôte» -- C++, Java, etc). § § v Les instructions SQL peuvent référer à des variables du langage hôte (y compris des variables utilisées pour retourner les statuts). On doit inclure une instruction pour connecter à la base de données appropriée. Deux approches d’intégration existent: § Instructions SQL imbriquées dans le langage hôte ( « Embedded SQL » , SQLJ) § Création d’une API spéciale pour appeler les commandes SQL (JDBC) Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 3

SQL dans les codes d’Application (Suite) v v v Les relations SQL sont des (multi)ensembles de tuples qui n’imposent aucune limite a priori sur le nombre de tuples. Les langages de programmation traditionnels (C, C++, etc. ) n’ont pas une telle structure de données. Ce problème est appelé inadaptation d’impédance ( « impedance mismatch » ). SQL supporte un mécanisme appelé curseur pour résoudre ce problème. Un curseur est un élément additionnel de SQL qui comble le fossé causé par l’inadaptation d’impédance. Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 4

SQL Imbriqué: Variables EXEC SQL BEGIN DECLARE SECTION char c_sname[20]; long c_sid; short c_rating; float c_age; EXEC SQL END DECLARE SECTION v v Deux problèmes avec les variables: (i) correspondance des types (solution: casting/correspondance explicite), et (ii) « impedance mismatch » (solution: le mécanisme de curseur). Deux variables spéciales d’“erreur” (une au moins doit être déclarée): § SQLCODE (long, est négative si une erreur est apparue) § SQLSTATE (char[6], codes prédéfinies pour des erreurs usuelles) Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 5

SQL Imbriqué: Instructions v Approche: instructions SQL imbriquées dans le langage hôte. § préprocesseur convertit les instructions SQL en des appels API. § Ensuite un compilateur régulier prend le relais pour compiler le code. v Éléments du langage: § Pour connecter à une base de données: EXEC SQL CONNECT § Pour exécuter des instructions SQL: EXEC SQL Statement; Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 6

SQL Imbriqué: Instructions (Suite) v Exemple: § Insérer une ligne avec des valeurs issues des variables du langage hôte: EXEC SQL INSERT INTO Sailors VALUES (: c_sname, : c_sid, : c_rating, : c_age); § Commande spéciale pour contrôler les erreurs après des instructions SQL: EXEC SQL WHENEVER [SQLERROR|NOT FOUND] [CONTINUE|GOTO smt] Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 7

Curseurs: Intuition v v On peut déclarer un curseur sur une relation ou une instruction de requête (qui en fait génère une relation). On peut ouvrir un curseur déclaré, et l’utiliser pour puiser (fetch) de manière répétée des tuples et, après, déplacer le curseur, jusqu’à ce que tous les tuples aient été récupérés. § § v v On peut utiliser une clause spéciale, appelée ORDER BY, dans les requêtes accessibles aux curseurs afin de contrôler l’ordre dans le quel les tuples doivent être récupérés. Les attributs mentionnés dans la clause ORDER BY doivent aussi l’être dans la clause SELECT. On peut enfin clore un curseur ouvert. On peut aussi modifier/effacer des lignes en utilisant un curseur. Cependant, INSERT, DELETE, et UPDATE peuvent être utilisés sans curseur. Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 8

Exemple d’Utilisation de Curseur EXEC SQL DECLARE sinfo CURSOR FOR SELECT S. sname FROM Sailors S, Boats B, Reserves R WHERE S. sid=R. sid AND R. bid=B. bid AND ORDER BY S. sname B. color=‘red’ sinfo est le curseur. v Il est illégal de remplacer S. sname par S. sid dans la clause ORDER BY ! (Pourquoi? ) v Peut-on ajouter S. sid à la clause SELECT et remplacer S. sname par S. sid dans la clause ORDER BY ? v Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 9

Curseur: Syntaxe v Syntaxe générale pour déclarer les curseurs: DECLARE cursorname [INSENSITIVE] [SCROLL] CURSOR [WITH HOLD] FOR query [ORDER BY order-item-list] [FOR READ ONLY | FOR UPDATE] v INSENSITIVE: une copie privée du curseur est faite. SCROLL: permet plus de flexibilité pour l’opération FETCH. WITH HOLD: curseur n’est pas fermé quand la transaction est validée. v Variante de la commande UPDATE pour les curseurs: UPDATE Sailors SET S. age = S. age+10 WHERE CURRENT OF sinfo /* Modifie la valeur courante du curseur sinfo */ Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 10

Imbriquer SQL dans C: Exemple char SQLSTATE[6]; EXEC SQL BEGIN DECLARE SECTION char c_sname[20]; short c_minrating; float c_age; EXEC SQL END DECLARE SECTION c_minrating = random(); EXEC SQL DECLARE sinfo CURSOR FOR SELECT S. sname, S. age FROM Sailors S WHERE S. rating > : c_minrating ORDER BY S. sname; EXEC SQL OPEN sinfo; do { EXEC SQL FETCH sinfo INTO : c_sname, : c_age; printf(“%s is %d years oldn”, c_sname, c_age); } while (SQLSTATE != ‘ 02000’); EXEC SQL CLOSE sinfo; Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 11

SQL Dynamique v v Les instructions SQL imbriquées sont connues par le compilateur à l’avance ( «compile time» ). Il y a des situations exigeant l’exécution d’instructions SQL en passant ( « on-the-fly » / «run time» )! Deux commandes principales à ce sujet: PREPARE sql_cmd FROM sql_string EXECUTE sql_cmd Exemple: char c_sqlstring[]= {“DELETE FROM Sailors WHERE raiting>5”}; EXEC SQL PREPARE delete. Command FROM : c_sqlstring; EXEC SQL EXECUTE delete. Command; Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 12

APIs en Lieu et Place de l’Imbrication v v v Au lieu de modifier le compilateur, ajouter des bibliothèques avec des instructions d’appel des bases de données (API). Interface spéciale standard: procédures/objets Passer les instructions SQL (en forme de chaînes de caractères) à partir du langage hôte, et présenter les ensembles de résultats dans une manière compréhensible pour le langage hôte. Exemples: ODBC; JDBC (Sun Corporation: API Java) JDBC est supposé être indépendant de tout SGBD § § Un pilote (“driver”) reçoit les instructions d’appel et les traduit dans le code spécifique du SGBD. La base de données peut se trouver à un autre bout du réseau. Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 13

JDBC: Architecture v Quatre composantes architecturales: § Application (initie et termine des connexions, soumet des instructions SQL) § Gestionnaire des pilotes (‘’Driver manager’’) (choisit -‘’loads’’-- un driver JDBC) § Driver (connecte à la source des données, transmet des requêtes et retourne/traduit les résultats et les codes d’erreur) § Source des données (exécute les instructions SQL) Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 14

JDBC: Types de Drivers Pont (’’Bridge’’): § Traduit les commandes SQL en une API non propre. Exemple: le pont JDBC-ODBC. Le code pour les drivers ODBC et JDBC doivent être présents sur chaque client. Traduction directe en une API propre via un driver hors Java: § Traduit les commandes SQL en une API propre de la source des données. Besoin d’un code binaire spécifique à chaque système d’opération sur chaque client. Pont sur réseau (‘’network bridge’’): § Les commandes sont envoyées via le réseau à un module intermédiaire (‘’middleware’’) qui est un serveur parlant à la source des données. Seul besoin: un driver JDBC sur chaque client. Traduction directe en une API propre via un driver Java: § Convertit les instructions d’appel JDBC directement dans le protocole de réseau que le SGBD utilise. Besoin d’un driver Java spécifique à chaque SGBD sur chaque client. Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 15

Classes et Interface pour JDBC Étapes pour soumettre une requête: 1. 2. 3. Choisir le driver JDBC (via un gestionnaire) Connecter à la source de données Exécuter les instructions SQL Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 16

Gestionnaire des Pilotes JDBC Tous les drivers sont gérés par la classe Driver. Manager v Choix d’un driver JDBC (3 voies possibles dont): v § Dans le code Java: Class. for. Name(“oracle/jdbc. driver. Oracledriver”); Retourne l’objet de la classe driver au vu de son nom complet. § En démarrant une application Java (à la ligne des commandes): -Djdbc. drivers=oracle/jdbc. driver § Instanciation explicite d’un driver Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 17

Connexions JDBC L’interaction avec une source de données se fait par l’entremise d’une session qui est démarrée par la création d’un objet de type ‘’Connection’’. Chaque session est démarrée via un URL JDBC. v URL JDBC(i. e. un URL utilisant le protocole JDBC): jdbc: <sousprotocole>: <autres. Parametres> Exemple: Etablir une connexion à une BD Oracle précise: String url=“jdbc: oracle: www. bookstore. com: 3083”; connexion con; try{ con = Driver. Manager. getconnexion(url, used. Id, password); } catch SQLException excpt { …} Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 18

Interface pour les Connexions v v public int get. Transaction. Isolation() et void set. Transaction. Isolation(int level) Détermine le degré d’isolation de la connexion en cours. public Boolean get. Read. Only() et void set. Read. Only(boolean b) Spécifie si les transactions dans la connexion sont pour lecture seulement (‘’read-only’’). public boolean get. Auto. Commit() et void set. Auto. Commit(boolean b) Si autocommit est vrai, chaque instruction SQL est considérée comme une transaction autonome. La transaction est validée par commit(), ou abandonnée par rollback(). public boolean is. Closed() Vérifie si la connexion est encore ouverte. Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 19

Exécution des Instructions SQL v Trois manières différentes de le faire (i. e. en utilisant 3 classes appropriées pour des objets de type ‘’statement’’): § Statement (permet des requêtes avec soit des instructions SQL statiques soit dynamiques) § Prepared. Statement (instructions semi statiques) § Callable. Statement (procédures stockées) v La classe Prepared. Statement: génère dynamiquement des instructions SQL précompilées et paramétrisées: § Structure fixe § Valeurs des paramètres déterminées pendant l’exécution Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 20

Exécution des Instructions SQL (Suite) String sql=“INSERT INTO Sailors VALUES(? , ? , ? )”; Prepared. Statment pstmt=con. prepare. Statement(sql); pstmt. clear. Parameters(); pstmt. set. Int(1, sid); // Supposons que sid, sname, … pstmt. set. String(2, sname); // sont des vars Java avec pstmt. set. Int(3, rating); // des valeurs à insérer. pstmt. set. Float(4, age); // Savons que aucune ligne n’est retournées, // d’où nous utilisons execute. Update() int num. Rows = pstmt. execute. Update(); Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 21

Ensembles des Resultats v v Prepared. Statement. execute. Update retourne seulement le nombre de ligne affectées. Prepared. Statement. execute. Query retourne les donnees, repris dans un objet Result. Set (un curseur) Result. Set rs=pstmt. execute. Query(sql); // rs est un curseur; next() puise la prochaine ligne While (rs. next()) { // traiter les données } Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 22

Ensemble des Resultats (Suite) Un Result. Set est un curseur très puissant: v previous(): reculer d’une ligne v absolute(int num): avancer à la ligne dont le numéro d’ordre est indiqué v relative (int num): avancer ou reculer selon la valeur indiquée v first() et last() Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 23

Correspondance entre les Types de Données: Java vs SQL Type BIT CHAR VARCHAR Java class Boolean String Result. Set get method get. Boolean() get. String() DOUBLE FLOAT INTEGER REAL DATE TIMESTAMP Double Integer Double java. sql. Date java. sql. Time. Stamp get. Double() get. Int() get. Float() get. Date() get. Timestamp() Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 24

JDBC: Exceptions et Avertissements java. sql émet des exceptions SQLException si une erreur apparaît. v SQLWarning est une sousclasse de SQLException; (pas du tout émis mais leur existence doit être explicitement testé si on veut les voir) v Méthodes dans la classe SQLException: get. Message(), get. SQLState(), get. Error. Code(), get. Next. Exception(), … v Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 25

JDBC: Exceptions et Avertissements (Suite) try { stmt=con. create. Statement(); warning=con. get. Warnings(); while(warning != null) { // traiter le SQLWarnings; warning = warning. get. Next. Warning(): } con. clear. Warnings(); stmt. execute. Update(query. String); warning = con. get. Warnings(); … } //end try catch( SQLException SQLe) { // traiter l’exception } Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 26