Page 1 sur 1

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

Posté : 14 mars 2016 15:59
par Bidouille
Question récurrente sur le forum. :roll:

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

A. Connexion à la base

Posté : 14 mars 2016 16:02
par 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.

Avant de partir tête baissée

Posté : 14 mars 2016 16:40
par 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
_no_problemo.png (92.99 Kio) Vu 40325 fois


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
no_problemo.gif (95.97 Kio) Vu 32105 fois

C. Règles de typographie dans une macro

Posté : 14 mars 2016 16:50
par 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'  "

D. Exécution d'un SELECT par macro

Posté : 14 mars 2016 16:57
par 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()

E. Fermez la !

Posté : 14 mars 2016 17:00
par Bidouille

Code : Tout sélectionner

oCnx.dispose

N'oubliez pas de fermer la connexion :)

F. Alternative de stockage des requêtes

Posté : 29 nov. 2019 04:24
par 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
capture.png (34.46 Kio) Vu 19499 fois

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

G. Exécution d'une requête SELECT

Posté : 29 nov. 2019 04:25
par 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
capture.png (58.25 Kio) Vu 19497 fois

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).

H. Requête autre qu'un SELECT

Posté : 29 nov. 2019 04:26
par 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
capture.png (42.88 Kio) Vu 19496 fois


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
capture.png (33.87 Kio) Vu 19496 fois


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)