Modèles SQL
Avec DatabaseSpy Enterprise Edition, vous pouvez créer et exécuter des instructions SQL qui contiennent des paramètres (dénommé comme « SQL Templates » par la suite). La seule différence entre les requêtes SQL et les modèles SQL est que la dernière contient des espaces réservés de paramètre. Par exemple, l’instruction SELECT ci-dessous est un modèle qui définit deux paramètres, :color et :size.
SELECT id FROM products WHERE color = :color AND size > :size; |
Vous pouvez définir des paramètres non seulement dans des instructions SELECT, mais aussi dans d’autres types d’instructions (telles que INSERT ou UPDATE). Par exemple, une instruction INSERT avec des paramètres pourraient avoir l’aspect suivant :
INSERT INTO users (first_name,last_name,email,created_at,updated_at) VALUES (:fname,:lname,:email,CURRENT_TIMESTAMP,NULL); |
Les modèles INSERT tels que ceux au-dessus peuvent sensiblement simplifier le transfert d’une table ou d’un mode à un autre (même à travers diverses bases de données). Spécifiquement, quand vous devez peupler une table, de multiples instructions INSERT peuvent être générées en quelques étapes simples depuis le modèle donné, tel que décrit ci-dessous.
Veuillez noter les points suivants :
•Afin d’être reconnu par DatabaseSpy en tant que tel, les paramètres au sein des instructions SQL doivent être précédés par deux points ( : ).
•Les paramètres peuvent substituer les valeurs à l’intérieur des instructions ; toutefois, afin de prévenir des injections SQL, ils peuvent ne pas être utilisés pour substituer des mots-clés SQL réservés ou des identifiants (tels que la colonne ou les noms de table).
•Les paramètres sont traités par défaut comme texte, et les requêtes paramétrables échoueront avec certaines connexions de bases de données, sauf si vous changez explicitement le type de paramètre (voir la section suivante ci-dessous, « Changer le type de paramètre »).
Changer le type de paramètre
Par défaut, les paramètres sont traités comme type text ou varchar, ce qui fait que les requêtes paramétrables échouent avec certains pilotes de base de données (par exemple, avec les connexions ADO.NET ou JDBC au Serveur SQL). Dans de tels cas, vous pouvez explicitement spécifier le type, et, si applicable, la précision, l’échelle ou la taille du paramètre en ajoutant un commentaire immédiatement après le paramètre.
Pour les types de strings, prenez en compte l’exemple suivant :
SELECT [col1], |
Dans la liste de code ci-dessus, le type de paramètre est explicitement défini comme varchar et sa dimension size est définie à max. La longueur length du modificateur peut être utilisée à la place de size.
Pour les types numériques, prenez en compte l’exemple suivant :
SELECT [col1], |
Dans la liste de code ci-dessus, le type de paramètre p1 est explicitement défini comme decimal, la precision (nombre total de chiffres) est défini à 23, et l’échelle scale (nombre de chiffres à droite du point décimal) est défini à 4. Vous pouvez aussi utiliser le nom raccourci prec pour précision. Les deux precision et scale requièrent des valeurs entières.
Ci-dessous, vous trouverez quelques exemples d’attributs qui peuvent suivre un paramètre (assurez-vous d’ajuster les détails requis par votre base de données) :
/*# float */ |
Veuillez noter les points suivants :
•Le caractère # donne explicitement l’ordre à DatabaseSpy de gérer le paramètre correspondant avec le type et la dimension déclarés.
•Le type de données et les attributs qui suivent doivent être séparés par une virgule. Chaque attribut doit également être séparé par une virgule.
•Les attributs autres que prec ou precision, scale, size ou length ne seront pas reconnus. Tous, quelques-uns ou aucun attribut ne sont requis afin d’exécuter la requête paramétrable avec succès, dépendant du pilote de base de données et de combien d’information, il est capable de déterminer automatiquement quand l’instruction est préparée.
Exécuter une requête avec des paramètres
1.Saisissez ou générez une instruction SQL dans l’Éditeur SQL, et assurez-vous qu’elle contienne au moins un paramètre. Par exemple, l’instruction INSERT ci-dessous contient trois paramètres :
2.Cliquez sur Exécuter 
ou sur le bouton Exécuter pour l’édition de données 
dans la barre d’outils de l’Éditeur SQL.
3.Remplissez les valeurs de paramètre requises, par exemple :
Vous pouvez aussi fournir de multiples lignes de paramètre à la requête. À cette fin, cliquez soit sur le bouton Ajouter nouveau 
ou Insérer nouveau 
. Le bouton Ajouter nouveau ajoute une ligne de paramètre après toutes les lignes existantes, tandis que Insérer nouveau insert un paramètre à la position actuelle (par exemple, entre les deux lignes existantes).
4.Cliquez sur OK. DatabaseSpy exécute la requête avec des paramètres fournis et affiche le résultat de l’exécution dans l’onglet Result.
Veuillez noter les points suivants :
•Si l’Éditeur SQL contient de multiples instructions SQL, la boîte de dialogue « Valeurs de paramètre » contient un onglet pour chaque instruction SQL où le paramètre existe. Aussi, de multiples onglets Result sont affichés après l’exécution de la requête (un onglet Result pour chaque instruction contenant des paramètres).
•DatabaseSpy se souvient des 25 dernières valeurs de paramètre saisies dans la boîte de dialogue « Valeurs de paramètre ». Pour utiliser une valeur précédente, sélectionnez-la depuis la liste déroulante pour chaque paramètre.
Enregistrer les requêtes paramétrables comme modèle SQL
Une requête qui contient des paramètres peut être enregistrée pour une utilisation ultérieure, semblable à celle pour les fichiers SQL. Pour enregistrer les contenus de l’Éditeur SQL comme modèle SQL, suivez une des étapes suivantes :
•Dans le menu Fichier, cliquez sur Enregistrer (ou Enregistrer sous).
•Cliquez sur le bouton de la barre d’outils Enregistrer.
•Appuyer sur Ctrl+S.
Les fichiers modèles SQL sont enregistrés avec l’extension .sql, comme les autres scripts SQL. Pour aider à distinguer entre les modèles SQL et d’autres scripts SQL, le projet DatabaseSpy contient un dossier spécifiquement conçu appelé « Modèles SQL ».
Tous les modèles SQL créés pour le projet doivent être dans le dossier « Modèles SQL ». Uniquement si un modèle SQL se trouve à l’intérieur du dossier « Modèle SQL », DatabaseSpy peut l’exécuter avec les valeurs, tel que décrit ci-dessous. |
Exécuter des modèles SQL
Vous pouvez exécuter des modèles SQL par rapport aux ensembles (lignes) de valeurs fournies de manière dynamique. Exécuter un modèle SQL signifie que DatabaseSpy remplace tous les espaces réservés de paramètre trouvés dans le modèle avec des valeurs que vous fournissez, crée une nouvelle instruction SQL pour chaque valeur (ou ligne de valeurs, dépendant du cas), et l’exécute par rapport à la base de données. En conséquence, il est possible de générer et exécuter un très large nombre d’instructions SQL en un clic de bouton.
Prérequis
1.Créez un modèle SQL dans la fenêtre de l’Éditeur SQL. Par exemple, le modèle SQL ci-dessous prend trois paramètres : :fname, :lname, and :email.
INSERT INTO users (first_name,last_name,email,created_at,updated_at) VALUES (:fname,:lname,:email,CURRENT_TIMESTAMP,NULL); |
En alternative à la saisie de l’instruction, vous pouvez aussi cliquer avec la touche de droite dans une table du Navigateur en ligne, générer une instruction INSERT et la modifier pour ajouter les paramètres requis, voir Générer les instructions SQL.
2.Enregistrer le Modèle SQL et ajoutez-le à votre projet de DatabaseSpy. Le modèle SQL doit être à l’intérieur du dossier « Modèles SQL » du projet.
À cette étape, vous pouvez aussi tester si le nouveau modèle SQL exécute avec succès, en l’exécutant avec quelques paramètres saisis manuellement. Pour tester le modèle SQL, cliquez sur Exécuter et saisissez les paramètres requis lorsque vous y êtes invité, voir Exécuter une requête avec des paramètres.
Pour exécuter un modèle avec des valeurs :
1.Connectez-vous à une source de données et extrayez les données de la table qui contiennent les valeurs que vous voulez fournir comme entrée à un modèle SQL (par exemple, cliquez avec la touche de droite dans la table du Navigateur en ligne, et sélectionnez Éditer les données du menu contextuel).
2.Tenez la clé Ctrl appuyée et sélectionnez une ou plusieurs cellules de la grille Résultats. Pour sélectionner de multiples colonnes de données, cliquez sur leur en-tête tout en tenant appuyée la clé Ctrl. Le modèle SQL sera exécuté autant de fois qu’il a de lignes dans la sélection. Notez que le nombre de colonnes sélectionnées doit correspondre au nombre de paramètres que le modèle SQL accepte ; pour cette raison :
oSi le modèle accepte uniquement un paramètre, toutes les cellules sélectionnées (une ou plus de lignes) doivent se trouver dans la même colonne.
oSi le modèle accepte de multiples paramètres, il doit y avoir autant de colonnes sélectionnées qu’il y a de paramètres.
Par exemple, pour le modèle INSERT avec trois paramètres comme celui illustré ci-dessus, la sélection suivante est valide :
Une sélection de multiples lignes qui n’est pas nécessairement consécutive est également valide, par exemple :
3.Cliquez avec la touche de droite sur la sélection et sélectionnez Exécuter le modèle avec des valeurs | <nom du modèle> depuis le menu contextuel. Toutes les instructions générées sont affichées dans l’Éditeur SQL et exécutées.