Étiquette : Bases de données

[SQL] Rechercher du texte dans un BLOB

[SQL] Rechercher du texte dans un BLOB

En SQL il est possible de rechercher du texte dans une colonne de type BLOB. Attention que cette méthode s’applique à la base de données Oracle. On utilise des méthodes internes qui permettent de convertir le terme cherché en données brutes et effectuer une recherche de caractères dans la colonne.

Voici le code à utiliser :

SELECT *
FROM t1
WHERE DBMS_LOB.INSTR (
   t1.champ_blob, 
   UTL_RAW.cast_to_raw (
     'Search text'
   ), 1, 1 
) > 0

Explications:

  • Le premier paramètre de la méthode « instr » est le champ BLOB dans lequel on veut effectuer la recherche.
  • L’appel à UTL_RAW.cast_to_raw en tant que second paramètre, permet de convertir la chaine de recherche en données brutes (RAW)
  • Le troisième paramètre de la méthode est l’indice à partir du quel on cherche.
  • Le quatrième paramètre indique quelle occurrence on souhaite (dans l’exemple on récupère la première)
  • Enfin le résultat est un la position de l’occurrence trouvée. Dans notre cas on vérifie qu’elle est supérieure à 0 juste pour récupérer tout ce qui correspond au terme de recherche

C’est plutôt pratique quand on stocke par exemple du XML ou du JSON dans un champ de type BLOB.

[SQL] L’opérateur (+) d’Oracle

[SQL] L’opérateur (+) d’Oracle

Je pensais avoir vu beaucoup de choses mais n’ayant pas beaucoup travaillé avec Oracle ces dernières années (en fait, pour être honnête : pas du tout), je n’avais jamais rencontré cette syntaxe qui peut paraitre étrange la toute première fois… Que fait cet opérateur plus dans les conditions des jointures ? Perturbant… Cela implique de se renseigner un peu.

Après quelques recherches sur internet j’apprends qu’il s’agit en fait d’un opérateur (+) pour indiquer le type de la jointure externe : gauche, ou droite. Les anciennes versions du SGBD (antérieures à la 9i) ne supportant pas la syntaxe de la norme ANSI, la solution était donc d’utiliser ce fameux signe (+). Celui-ci doit être spécifié dans la clause WHERE, de la manière suivante.

SELECT * FROM A, B 
WHERE A.column = B.column(+)

On récupère donc les enregistrements de A qui correspondent à B par rapport à la condition de la jointure, ainsi que les records qui n’ont pas de correspondance dans la table B. C’est donc une jointure externe gauche que nous avons là (LEFT OUTER JOIN). Cela équivaut à écrire

SELECT * FROM A 
LEFT OUTER JOIN B ON A.column = B.column

Vous l’aurez compris, si on place l’opérateur au niveau de la colonne de A dans la condition, cela devient une jointure externe droite (RIGHT OUTER JOIN).

Notez qu’Oracle déconseille fortement l’utilisation de cet opérateur. En effet il est conservé pour des questions de compatibilité. Il est toutefois loin d’être pratique, surtout du point de vue de la lisibilité. De plus comme la syntaxe prévue par la norme est désormais supportée, il n’y a plus aucune raison de ne employer celle-ci.

Et si vous avez oublié comment fonctionnent les jointures, voici une piqure de rappel.

[SQL] Concaténer en une seule chaine par ligne

[SQL] Concaténer en une seule chaine par ligne

Il existe, en MySQL, une fonction qui s’appelle GROUP_CONCAT et qui permet de concaténer – d’agréger – plusieurs éléments récupérés depuis une seule table en vue d’obtenir une seule chaine de caractères, par ligne de résultat. Cela permet, par exemple, de récupérer les informations d’un livre, avec les auteurs séparés par des « , », et ce en une seule ligne de résultat. L’exemple repris dans la documentation de MySQL permet d’afficher le nom des étudiants avec leurs résultats aux différents tests.

SELECT student_name, GROUP_CONCAT(test_score) 
FROM student 
GROUP BY student_name;

Avec DB2 c’est plus délicat et selon la version de la base de données, il faudra utiliser une fonction différente. Par exemple, dans les versions 9.2 et supérieures (corrigez-moi si je fais erreur), il existe la fonction LISTAGG qui reçoit en paramètre le champ pour lequel on veut concaténer les valeurs, et le séparateur.La même fonction – LISTAGG – existe dans le SGBD Oracle, à partir de la version 11g. En ce qui concerne les versions inférieures on peut se dépanner avec une procédure stockée.

Sur AS/400, il a fallu trouver une alternative. Il faut donc utiliser des fonctions XML pour arriver à un résultat similaire, et si on le souhaite, utiliser la fonction REPLACE directement dans la requête pour modifier la chaîne résultante. C’est un peu plus lourd pour la lisibilité globale de la requête.

SELECT pc.ean, pc.libelle, 
	(SELECT 
		XMLSERIALIZE( 
			XMLAGG( 
				XMLELEMENT(NAME x, trim(auteur_nom)) 
			) AS VARCHAR(1024) CCSID 500 
		) 
	FROM auteurs 
	JOIN art_auteurs on pc.ean = art_auteurs.ean 
	AND art_auteurs.idaut = auteurs.idaut
	) AS liste_auteurs 
FROM articles AS pc

On utilise 3 fonctions au total :

  • XMLSERIALIZE : va retourner une expression XML sérialisée, dans le type donné. Le paramètre correspond à l’expression XML. Lors des différents essais l’erreur SQL0332 s’est produite. Il faut faire attention à l’encodage par rapport au client, d’où l’utilisation de la clause CCSID dans l’exemple. Plus d’informations dans la documentation IBM.
  • XMLAGG : retourne une séquence XML avec un objet pour chaque valeur non-nulle contenue dans un ensemble de valeurs XML Le paramètre, dans l’exemple, est donc l’ensemble d’éléments « nom d’auteur ». En savoir plus.
  • XMLELEMENT : renvoie un champ sous forme d’élément XML. Dans l’exemple, le nom de l’auteur sera transformé en NOM. Le premier paramètre correspond au nom de l’attribut, le second au champ à transformer. En savoir plus.

Il suffit ensuite, soit dans votre programme soit directement dans la requête, de modifier la chaine pour y mettre des « , » ou tout autre caractère de votre choix. Bon développement !

[SQL] Insertion multiple

[SQL] Insertion multiple

Dans de nombreuses bases de données, il existe la possibilité d’effectuer une insertion multiple avec une seule requête SQL. La syntaxe suivante montre comment écrire la requête pour ajouter deux enregistrements.

INSERT INTO ma_table (col1, col2, ..., col n) 
VALUES 
  (val1, 'val2',..., val n),
  (val4, 'val5',..., val z)

La même requête en omettant le nom des colonnes.

INSERT ALL INTO ma_table (col1, col2,..., col n) 
VALUES (val1, 'val2',..., val n) 
INTO ma_table (col1, col2,..., col n) 
VALUES (val4, 'val5',..., val z)

Compatibilité

Disponible sur (liste non exhaustive)

  • MySQL.
  • DB2 (idem sur OS/400).
  • HFSQL (à partir de la version 20).
  • PostgreSQL.
  • SQL Server 2008 et supérieur.
  • (…)

Si vous utilisez la base de données Oracle, la syntaxe est différente.

INSERT ALL INTO ma_table (col1, col2,..., col n) 
VALUES (val1, 'val2',..., val n)
INTO ma_table (col1, col2,..., col n) 
VALUES (val4, 'val5',..., val z)

Si vous utilisez une ancienne version de SQL Server (l’astuce suivante peut aussi être adaptée pour une base SQLite ou même Firebird).

INSERT INTO [schema.]MaTable (ID, Nom) 
SELECT 200, 'Vincent' 
UNION ALL 
SELECT 300, 'Cyril' 
UNION ALL 
SELECT 400, 'Rudi'

Avec Firebird 2.0 et supérieur on peut exécuter sous forme de bloc (voir la documentation officielle). N’hésitez pas à partager la syntaxe pour effectuer la même opération dans des bases de données différentes !

[SQL] Mémo – Modifier le type d’une colonne

[SQL] Mémo – Modifier le type d’une colonne

Pour modifier le type d’une colonne dans une table DB2/400, vous pouvez utiliser la syntaxe ALTER TABLE … ALTER COLUMN. Il faudra cependant spécifier une clause supplémentaire : SET DATA TYPE. Cela vous permettra de changer la taille par exemple, ou bien passer d’un entier sur 4 octets à un entier sur 8. Exemple :

ALTER TABLE 'biblio.table' ALTER COLUMN email 
SET DATA TYPE varchar(50);

Dans cet exemple nous modifions la colonne qui contient l’e-mail en indiquant qu’on souhaite une chaine de caractères à taille variable d’un maximum de 50 caractères. Dans SQL Server, cela donnerait quelque chose comme ceci :

ALTER TABLE 'schema.table' ALTER COLUMN email varchar(50);

Et dans Oracle ou même MySQL :

ALTER TABLE 'schema.table' MODIFIY COLUMN email varchar(50);

Bon développement !