[API] Exécution de requête SQL sous Base

[Bidouille]

Question récurrente sur le forum.

Mais bon, avant de vous précipiter dans une macro, assurez-vous quand même que vous ne pouvez pas faire sans.
Les formulaires sous Base permettent de faire toutes les opérations SQL.
Vous pouvez aussi utiliser une requête ou une vue si vous avez quelque chose de plus complexe.

Bref, l'utilisation d'une macro ne doit être qu'un dernier recours.
Rappelez-vous qu'une macro ne fera jamais plus que se que vous feriez avec du SQL si ce n'est automatiser la tâche.

Sommaire

1. Connexion à la base
2. Avant de partir tête baissée
3. Règles de typographie dans une macro
4. Exécution d'un SELECT par macro
5. Fermez la !
6. Alternative de stockage des requêtes
7. Exécution d'une requête SELECT
8. Requête autre qu'un SELECT
9. Exécution par macro avec paramètres
10. Usage du RowSet

Notez que zBasic facilitera ce travail avec ses fonctions zSQL...

[Bidouille]

Rappel : contrairement aux autres modules, Base est représenté par l'objet ThisDatabaseDocument

Code : Tout sélectionner

sUrl = ThisDatabaseDocument.URL
oDC = CreateUnoService("com.sun.star.sdb.DatabaseContext")
oSrc = oDC.getByName(sUrl)
oDB = oSrc.getConnection("","")
oCnx = oDB.createStatement()

Explication des lignes

1. on récupère d'abord le chemin complet (URL) de notre ODB
2. on appelle le service pour se connecter à la base de données
3. on lui passe le nom de base
4. on donne les identifiant / mot de passe qui sont par défaut vides
5. on récupère un objet pour la connexion obtenue

A partir de là, on peut facilement envoyer du SQL

Attention car ce code est fourni uniquement pour le format natif HSQL.
L'utilisation de Base avec un autre SGBD (MySQL, Oracle, etc) imposera une autre syntaxe notamment pour la ligne 4 et la connexion à la base.

[Bidouille]

Si vous tentez d'exécuter un SELECT...
Assurez-vous que votre requête SQL est correcte et qu'elle renvoie bien des informations.
Pour cela, exécutez là d'abord dans l'interface :

_no_problemo.png

Si vous tentez d'exécuter autre chose tel qu'un INSERT, UPDATE ou autre DELETE...
Assurez-vous que cela ne retourne pas d'erreur.
Pour cela, exécutez la requête dans le dialogue SQL :

no_problemo.gif

[Bidouille]

Votre requête fonctionne, il faut alors ajouter quelques caractères afin de pouvoir la passer.
Exemple :

Code : Tout sélectionner

SELECT test FROM toto WHERE truc = 'x'

Trois règles simples :

1. vous devez encadrer la requête par des guillemets
2. vous devez encadrer chaque champ ou nom de table par des guillemets doubles
3. vous devez ajouter des apostrophes simples pour les variables

Ce qui devient :

Code : Tout sélectionner

sReq = "SELECT ""test"" FROM ""toto"" WHERE ""truc"" = 'x'  "

Dans le cadre d'une apostrophe dans une variable, celle-ci doit être "échappée".
Exemple :

Code : Tout sélectionner

sReq = "SELECT ""test"" FROM ""toto"" WHERE ""truc"" = 'l\'apostrophe'  "

[Bidouille]

N'oubliez pas le code de connexion à la base sans quoi le script ne fonctionnera pas.

Code : Tout sélectionner

sReq = "SELECT ""test"" FROM ""toto"" WHERE ""truc"" = 'x'  "
sRet = oCnx.executeQuery(sReq)
sRet.next
msgbox sRet.getColumns().getByName("test").getString()

[Bidouille]

Code : Tout sélectionner

oCnx.dispose

N'oubliez pas de fermer la connexion

[Bidouille]

Avoir le texte de ses requêtes dans une macro n'est pas chose aisé pour la maintenance.
Il faut en effet trouver la routine et le SQL qui est exécuté. La difficulté est accrue avec la syntaxe particulière pour l'exécution vu précédemment.

Il est tout à fait possible d'utiliser le volet Requêtes dans Base :

capture.png

Cela a l'avantage de :

1. pouvoir nommer sa requête avec un nom plus parlant
2. rédiger plus naturellement le SQL sans avoir à respecter la syntaxe imposée dans une macro
3. maintenir facilement chaque requête en cas de modification

[Bidouille]

Comme nous l'avons vu au paragraphe B, créons notre requête dans l'éditeur.
Vous pouvez bien sûr utiliser les 3 modes à votre disposition (ébauche, assistant ou SQL direct).

Par exemple, un SELECT pour l'échéance de clients après 2019 :

capture.png

La requête s'appelle logiquement "Echeance apres 2019"

La macro exécutant cette requête sera :

Code : Tout sélectionner

   oReq = oDB.Queries.getByName("Echeance apres 2019")
   sReq = oReq.Command
   sRet = oCnx.executeQuery(sReq)
   sRet.next
   msgbox sRet.getColumns().getByName("Nom").getString()

NB : n'oubliez pas d'ajouter le code pour la connexion à la base et la déconnexion (§ A et E).

[Bidouille]

L'éditeur en mode SQL direct refuse tout autre requête qu'un SELECT.
Il y a d'ailleurs la demande d'amélioration sur laquelle je vous invite à voter.

capture.png

L'astuce dans ce cas, consiste à ne pas faire vérifier le code en cliquant sur le bouton "Exécuter directement l'instruction SQL" :

capture.png

Seul bémol, vous ne pourrez jamais tester le résultat de ce type de requête.
Soyez donc très prudent lors de l'exécution en utilisant des copies de votre base.

La macro pour l'exécution d'une requête de mise à jour (UPDATE) ou d'effacement (DELETE) :

Code : Tout sélectionner

   oReq = oDB.Queries.getByName("Nom de la requete")
   sReq = oReq.Command
   oCnx.executeUpdate(sReq)

[Bidouille]

Comme au paragraphe D, on peut également préparer une requête avec paramètres.
Reprenons notre exemple précédent :

Code : Tout sélectionner

UPDATE "Client" SET "Valeur" = ? WHERE "Nom" LIKE ?

La méthode prepareStatement permet d'insérer le SQL comme suit :

Code : Tout sélectionner

sSQL = "UPDATE ""Client"" SET ""Valeur"" = ? WHERE ""Nom"" LIKE ?"
oReq = oDB.prepareStatement(sSQL)
oReq.setLong(1, oCtrlNum.Value)
oReq.setString(2, oCtrlNom.Text)
sReq = oReq.Command
oCnx.executeUpdate(sReq)

Pour la récupération des valeurs d'un contrôle de formulaire, reportez-vous sur ce fil.

[Bidouille]

On peut également stocker les informations dans un objet de type RowSet.
C'est un un jeu de tuples qui permet de naviguer indifféremment dans plusieurs sources d'un ODB (table, requête ou SQL direct).
Il permet notamment de contourner le bogue 61869 du ResultSet qui ne permet qu'une lecture à la suite des tuples sans possibilité de revenir en arrière.

Ainsi dans l'exemple ci-dessous, on rapatrie le contenu de la table Client.
On le lit pour en savoir le nombre d'enregistrement.
Puis on revient au début pour lire le 1er tuple.

Code : Tout sélectionner

oJeu = createUnoService("com.sun.star.sdb.RowSet")
sTable = "Client" 'nom de la table
with oJeu
   .activeConnection = oDB
   .CommandType = com.sun.star.sdb.CommandType.TABLE
   .Command = sTable
   .execute
   .afterLast 'aller à la fin
   if .isAfterLast then
      msgbox "Nombre de lignes dans " & sTable & " : " & .RowCount
   endif
   .beforeFirst 'revenir au début
   if .isFirst then
      .next
      msgbox .getColumns().getByName("nom").getString()
   endif
   .dispose 'fermeture
end with