D

1
Développement des Applications des Bases de
Données

• Chapitre 6

2
Survol

• SQL dans les codes dapplication
• SQL imbriqué
• Curseurs
• SQL dynamique
• JDBC
• SQLJ
• Procédures stockées

3
SQL dans les codes dApplication

• Les commandes SQL peuvent être appelées à partir
  dun programme dapplication (langage hôte --
  C, Java, etc).
• 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 dintégration existent
• Instructions SQL imbriquées dans le langage hôte
  ( Embedded SQL , SQLJ)
• Création dune API spéciale pour appeler les
  commandes SQL (JDBC)

4
SQL dans les codes dApplication (Suite)

• Les relations SQL sont des (multi)ensembles de
  tuples qui nimposent aucune limite a priori sur
  le nombre de tuples. Les langages de
  programmation traditionnels (C, C, etc.) nont
  pas une telle structure de données. Ce problème
  est appelé inadaptation dimpé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 linadaptation
  dimpédance.

5
SQL Imbriqué Variables

• EXEC SQL BEGIN DECLARE SECTION
• char c_sname20
• long c_sid
• short c_rating
• float c_age
• EXEC SQL END DECLARE SECTION
• Deux problèmes avec les variables
• correspondance des types (solution
  casting/correspondance explicite)
• inadaptation dimpédance (solution le mécanisme
  de curseur)
• Deux variables spéciales derreur (une au moins
  doit être déclarée)
• SQLCODE (long, est négative si une erreur est
  apparue)
• SQLSTATE (char6, codes prédéfinies pour des
  erreurs usuelles)

6
SQL Imbriqué Instructions

• Approche instructions SQL imbriquées dans le
  langage hôte.
• Un préprocesseur convertit les instructions SQL
  en des appels API.
• Ensuite un compilateur régulier prend le relais
  pour compiler le code.
• Éléments du langage
• Pour connecter à une base de donnéesEXEC SQL
  CONNECT
• Pour exécuter des instructions SQLEXEC SQL
  Statement

7
SQL Imbriqué Instructions (Suite)

• Exemple
• Insérer un tuple formé 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 SQLERRORNOT FOUND
• CONTINUEGOTO smt

8
Curseurs Intuition

• 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 lutiliser
  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.
• On peut utiliser une clause spéciale, appelée
  ORDER BY, dans les requêtes accessibles aux
  curseurs afin de contrôler lordre 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.

9
Exemple dUtilisation de Curseur
EXEC SQL DECLARE sinfo CURSOR FOR SELECT
S.sname FROM Sailors S, Boats B, Reserves
R WHERE S.sidR.sid AND R.bidB.bid AND
B.colorred ORDER BY S.sname

• sinfo est le curseur.
• Il est illégal de remplacer S.sname par S.sid
  dans la clause ORDER BY !
• On peut ajouter S.sid à la clause SELECT et
  remplacer S.sname par S.sid dans la clause ORDER
  BY.

10
Curseur Syntaxe

• 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
• INSENSITIVE une copie privée du curseur est
  faite.
• SCROLL permet plus de flexibilité pour
  lopération FETCH.
• WITH HOLD curseur nest pas fermé quand la
  transaction est validée.
• Variante de la commande UPDATE pour les
  curseurs
• UPDATE Sailors
• SET S.age S.age10
• WHERE CURRENT OF sinfo
• / Modifie la valeur courante
  du curseur sinfo /

11
Imbriquer SQL dans C Exemple

• char SQLSTATE6
• EXEC SQL BEGIN DECLARE SECTION
• char c_sname20 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 gt 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 old\n, c_sname, c_age)
• while (SQLSTATE ! 02000)
• EXEC SQL CLOSE sinfo

12
SQL Dynamique

• Les instructions SQL imbriquées sont connues par
  le compilateur à lavance (compile time).
• Il y a des situations exigeant lexécution
  dinstructions 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
  raitinggt5
• EXEC SQL PREPARE deleteCommand FROM
  c_sqlstring
• EXEC SQL EXECUTE deleteCommand

13
APIs en Lieu et Place de lImbrication

• Au lieu de modifier le compilateur, ajouter des
  bibliothèques avec des instructions dappel 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
  dappel et les traduit dans le code spécifique du
  SGBD.
• La base de données peut se trouver à un autre
  bout du réseau.

14
JDBC Architecture

• 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 derreur)
• Source des données (exécute les instructions SQL)

15
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 dun code binaire
  spécifique à chaque système dopé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 dappel JDBC
  directement dans le protocole de réseau que le
  SGBD utilise. Besoin dun driver Java spécifique
  à chaque SGBD sur chaque client.

16
Classes et Interface pour JDBC

• Étapes pour soumettre une requête
• Choisir le driver JDBC (via un gestionnaire)
• Connecter à la source de données
• Exécuter les instructions SQL

17
Gestionnaire des Pilotes JDBC

• Tous les drivers sont gérés par la classe
  DriverManager
• Choix dun driver JDBC (3 voies possibles dont)
• Dans le code JavaClass.forName(oracle/jdbc.driv
  er.Oracledriver)
• Retourne lobjet de la classe driver au vu de
  son nom complet.
• En démarrant une application Java (à la ligne des
  commandes)-Djdbc.driversoracle/jdbc.driver
• Instanciation explicite dun driver

18
Connexions JDBC

• Linteraction avec une source de données se fait
  par lentremise dune session qui est démarrée
  par la création dun objet de type
  Connection. Chaque session est démarrée via
  un URL JDBC.
• URL JDBC(i.e. un URL utilisant le protocole
  JDBC)
• jdbcltsousprotocolegtltautresParametresgt
• Exemple Etablir une connexion à une BD Oracle
  précise
• String urljdbcoraclewww.bookstore.com3083
• connexion con
• try
• con DriverManager.getconnexion(url,usedId,passw
  ord)
• catch SQLException excpt

19
Interface pour les Connexions

• public int getTransactionIsolation() et void
  setTransactionIsolation(int level)Détermine le
  degré disolation de la connexion en cours.
• public Boolean getReadOnly() et
• void setReadOnly(boolean b)Spécifie si les
  transactions dans la connexion sont pour lecture
  seulement (read-only).
• public boolean getAutoCommit() et void
  setAutoCommit(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 isClosed()Vérifie si la connexion
  est encore ouverte.

20
Exécution des Instructions SQL

• 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)
• PreparedStatement (instructions semi statiques)
• CallableStatement (procédures stockées)
• La classe PreparedStatementgénère dynamiquement
  des instructions SQL précompilées et
  paramétrisées
• Structure fixe
• Valeurs des paramètres déterminées pendant
  lexécution

21
Exécution des Instructions SQL (Suite)

• String sqlINSERT INTO Sailors VALUES(?,?,?,?)
• PreparedStatment pstmtcon.prepareStatement(sql)
• pstmt.clearParameters()
• pstmt.setInt(1,sid) // Supposons que sid,
  sname,
• pstmt.setString(2,sname) // sont des vars Java
  avec
• pstmt.setInt(3, rating) // des valeurs à
  insérer.
• pstmt.setFloat(4,age)
• // Savons que aucune ligne nest
  retournées,
• // doù nous utilisons executeUpdate()
• int numRows pstmt.executeUpdate()

22
Ensembles des Resultats

• PreparedStatement.executeUpdate retourne
  seulement le nombre de ligne affectées.
• PreparedStatement.executeQuery retourne les
  donnees, repris dans un objet ResultSet (un
  curseur)
• ResultSet rspstmt.executeQuery(sql)
• // rs est un curseur next() puise la prochaine
  ligne
• While (rs.next())
• // traiter les données

23
Ensemble des Resultats (Suite)

• Un ResultSet est un curseur très puissant
• previous() reculer dune ligne
• absolute(int num) avancer à la ligne dont le
  numéro dordre est indiqué
• relative (int num) avancer ou reculer selon la
  valeur indiquée
• first() et last()

24
Correspondance entre les Types de Données Java
vs SQL
SQL Type Java class ResultSet get method
BIT Boolean getBoolean()
CHAR String getString()
VARCHAR String getString()
DOUBLE Double getDouble()
FLOAT Double getDouble()
INTEGER Integer getInt()
REAL Double getFloat()
DATE java.sql.Date getDate()
TIME java.sql.Time getTime()
TIMESTAMP java.sql.TimeStamp getTimestamp()
25
JDBC Exceptions et Avertissements

• java.sql émet des exceptions SQLException si une
  erreur apparaît.
• SQLWarning est une sousclasse de SQLException
  (pas du tout émis mais leur existence doit être
  explicitement testé si on veut les voir)
• Méthodes dans la classe SQLException
  getMessage(), getSQLState(), getErrorCode(),
  getNextException(),

26
JDBC Exceptions et Avertissements (Suite)

• try
• stmtcon.createStatement()
• warningcon.getWarnings()
• while(warning ! null)
• // traiter le SQLWarnings
• warning warning.getNextWarning()
• con.clearWarnings()
• stmt.executeUpdate(queryString)
• warning con.getWarnings()
• //end try
• catch( SQLException SQLe)
• // traiter lexception