Étiquette : SQL

Vincent Lecomte
[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 !

[.NET] Accès à DB2 UDB iSeries + DataGridView

[.NET] Accès à DB2 UDB iSeries + DataGridView

On retrouve dans .NET les mêmes possibilités qu’en Java afin de se connecter à une base de données DB2 UDB sur un AS/400. En effet, lorsqu’on installe IBM i Access sur le PC, un kit de développement est inclus. Dedans on retrouve les assemblages nécessaires pour développer en .NET. Dans le menu démarrer vous devriez retrouver, dans le dossier IBM i, l’aide “Boite à outils de programmation“.

Start Menu > Programs > IBM i Access for Windows > Boîte à outils de programmation.

Intégrer l’assemblage dans le projet

Pour effectuer cette opération il faut ouvrir l’explorateur de solutions, effectuer un clic droit sur la catégorie “Références” et choisir l’option “Ajouter une référence” dans le menu contextuel.

Ensuite, il faut cliquer sur “Extensions” et chercher après “IBM for DB2 i .NET Provider“. On peut s’aider du champ de recherche afin de filtrer les résultats. Une fois celui-ci repéré il faut le cocher puis cliquer sur OK.

Lorsqu’on a terminé, si on déroule l’arborescence, on pourra constater qu’un nouvel élément est venu rejoindre les références existantes, à savoir “IBM.Data.DB2.iSeries“. On va dès lors pouvoir utiliser les différents objets de l’API.

Connexion et requête

Tout d’abord, il faudra indiquer, à l’aide de la directive “using“, qu’on souhaite utiliser des objets et types de l’espace de noms “IBM.Data.DB2.iSeries” sans avoir à le qualifier à nouveau. Pour information nous programmons ici en C#.

using IBM.Data.DB2.iSeries;

Ensuite on va, dans le code d’un bouton par exemple, initialiser la connexion, récupérer des données dans une table, puis fermer cette connexion. On va aussi afficher le nom du job démarré sur la machine AS/400.

C’est en instanciant un objet de la classe “iDB2Connection” qu’on va pouvoir se connecter. Pour cela, on devra renseigner dans le paramètre du constructeur une chaine de connexion (ConnectionString). Pour savoir quelles sont les propriétés qu’on peut renseigner, consultez l’aide ou la documentation en ligne.

iDB2Connection cn2 = new iDB2Connection("DataSource=ADR_IP" +
  ";UserID=USER;Password=PASSWD;Naming=SQL"); 
cn2.Open();

Dans l’exemple ci-dessus, on a spécifié :

  • DataSource : le nom ou l’adresse du serveur à contacter.
  • UserID : le nom d’utilisateur avec lequel on veut se connecter.
  • Password : le mot de passe associé.
  • Naming : indique si on veut utiliser les conventions de nommage SQL.

Ensuite on a utilisé la fonction “Open()” de l’objet créé afin d’ouvrir explicitement la connexion. L’étape suivante consiste à créer une “commande” de type “select” qui sera liée à la connexion précédente.

iDB2Command myCmd = cn2.CreateCommand(); 
myCmd.CommandText = "SELECT * FROM myLib.myTable";

La propriété “CommandText” contiendra le code SQL de la requête à exécuter. On va remplir ce qu’on appelle un “DataGridView“. C’est un contrôle personnalisable qui permet d’afficher assez facilement un tableau, lié à une source de données, ou non.

iDB2DataAdapter da = new iDB2DataAdapter(myCmd);
DataTable ds = new DataTable(); da.Fill(ds);
dataGridView1.DataSource = ds;

Pour cela on va utiliser deux autres types de variables, un “iDB2DataAdapter” et un “DataTable“. Le second est une représentation des données de la table. Le premier quant à lui va servir de “pont” entre la base de données et notre objet “DataTable” en question.

  • On crée donc une instance de iDB2DataAdapter en passant en paramètre la commande.
  • On crée un objet DataTable, sans spécifier de paramètre au constructeur.
  • On utilise la fonctionne “Fill()” de notre objet iDB2DataAdapter avec en paramètre, l’objet DataTable.
  • Ensuite on lie cet objet DataTable à notre contrôle grâce à la propriété DataSource.
  • Il n’y a rien à faire ; le tableau apparaitra automatiquement.

Voilà ce que ça donne à l’exécution.

Et bien sûr il ne faudra pas oublier de se déconnecter de la base de données une fois que c’est terminé (tout dépend de ce que vous souhaitez faire ensuite).

Afficher le nom du job

Si on veut afficher le nom du job qui a été créé pour la connexion en cours, on peut par exemple écrire le code suivant.

MessageBox.Show(cn2.JobName);

Cela affichera, dans une boite de dialogue, son nom complet (travail, utilisateur et numéro).

Déconnexion

C’est très simple ! Il suffit d’appeler la fonction “Close()” de l’objet Connexion créé au début de ce billet. Cela aura pour effet de mettre fin au job créé sur l’AS/400.

[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] Convertir une colonne Date/Heure

[SQL] Convertir une colonne Date/Heure

Dans SQL Server, il est possible de convertir en date courte ou en chaine de caractères une colonne de type DATETIME. On peut utiliser la fonction CONVERT() ou encore la fonction CAST(). Dans cet exemple, nous utilisons la première qui prend en paramètre le type de données de destination ainsi que sa longueur (facultative), l’expression à convertir et enfin le style qui spécifie comment doit être traduite l’expression.

SELECT CONVERT(VARCHAR(10), [ma_colonne], 112) 
FROM table

La requête va renvoyer des dates au format ISO “AAAAMMJJ” soit l’année, le mois et le jour. Si on regarde la documentation, c’est grâce à la valeur 112 spécifiée pour le style que l’on obtient ce résultat. On peut donc facilement personnaliser la valeur retournée par la requête. Cela peut également être très pratique dans une condition.

[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 !

[SQL] 80040e57 : Arithmetic Overflow SQL Server

[SQL] 80040e57 : Arithmetic Overflow SQL Server

Lorsque vous effectuez une insertion dans une colonne avec une valeur numérique trop grande ou lorsque vous effectuez par exemple une somme impliquant une ou plusieurs colonnes numérique dont le résultat dépasse le type imposé (par exemple, le résultat obtenu dépasse l’entier), vous obtenez l’exception 80040e57 avec par exemple le message d’erreur suivant : “Arithmetic overflow error converting expression to data type int”.

Par exemple, supposons la requête suivante :

SELECT b.Cart, SUM(a.QTE * b.POIDS) 
FROM Commande a JOIN Article b 
ON a.cart = b.cart 
WHERE ... 
GROUP BY ...

On sait que la colonne QTE et la colonne POIDS sont au format entier (int). Si le résultat de la somme pour l’article vient à dépasser la limite maximale définie par le type entier, une erreur sera renvoyée. Une solution consiste donc à convertir chaque colonne dans un autre type (exemple: bigint). Cela donnerait par exemple :

SELECT b.Cart, SUM( CAST(a.QTE AS BIGINT) 
  * CAST(b.POIDS AS BIGINT)) 
FROM Commande a JOIN Article b 
ON a.cart = b.cart 
WHERE ... GROUP BY ...

Dans le cas d’une opération d’insertion ou de mise à jour, il faudra peut-être envisager de modifier le type de la colonne destination ou restreindre les valeurs possibles qui peuvent être stockées dans la table. Pour obtenir davantage d’informations consultez la base de connaissances Microsoft Technet (MS163363).

[SQL] Mémo – Dates dans DB2 UDB for iSeries

[SQL] Mémo – Dates dans DB2 UDB for iSeries

Sur l’AS/400, lorsqu’on utilise des champs de type date, il faut parfois utiliser certaines fonctions de conversion ou du moins utiliser une chaine de caractères d’un format spécifique. Il existe aussi un tas de fonctions qui permettent par exemple de récupérer le jour de la semaine, le libellé du mois, ou bien une représentation sous forme d’entier.

Fonction “Date”

Il est possible de passer une chaine de caractères à la fonction “Date” pour retourner une variable du bon type. Dans l’exemple qui suit, on utilise la table système pour obtenir une seule ligne de résultat. La chaine de caractère passée en paramètre est au format “MM/DD/YYYY”.

SELECT DATE('04/10/2014') FROM sysibm/sysdummy1;

Ce qui permet d’obtenir à l’écran “10/04/2014”. On peut également convertir ce résultat au format “YYYY-MM-DD” grâce à la fonction CHAR. Celle-ci prend en paramètre une variable ou un champ de type date ainsi qu’un paramètre facultatif supplémentaire étant le type (ex : ISO, USA).

SELECT CHAR(DATE('04/10/2014'),iso) FROM sysibm/sysdummy1;

Cette requête nous retourne alors la date “2014-04-10”, soit le 10 avril : c’est ce que nous cherchions effectivement à faire. En utilisant le format “usa” au lieu d’”iso”, nous aurions obtenu exactement le même résultat que la chaine passée en paramètre de la fonction “DATE”.

Une chaine dans la condition

Il est également possible de récupérer des résultats d’une table en comparant la date à une chaine de caractères, pour autant qu’elle soit au bon format. En pratique, on pourrait directement utiliser les fonctions CHAR et DATE, mais il est également possible de réaliser ceci :

SELECT * FROM MA_TABLE WHERE CH_DAT = '2014-04-10';

Dans l’exemple ci-dessus, nous possédons une table nommée “MA_TABLE” contenant plusieurs champs dont un de type date, “CH_DAT”. Ce champ n’est donc pas un numérique, qui est pourtant couramment utilisé pour stocker les dates au format YYYYMMDD.

Récupérer le jour, le mois, l’année

Comme on le sait, les champs de type date imposent d’effectuer des conversions, mais permettent tout de même de récupérer des données aisément sans avoir à découper une quelconque chaine de caractères. Inutile de se battre avec la fonction SUBSTRING pour récupérer le jour ou le mois.

SELECT DAY(CH_DAT) FROM MA_TABLE; 
SELECT MONTH(CH_DAT) FROM MA_TABLE; 
SELECT YEAR(CH_DAT) FROM MA_TABLE;

En supposant que le champ “CH_DAT” soit bien de type date, nous récupérons alors les jours, les mois ou les années pour chaque ligne de la table. Depuis la V5R3, il est également possible de récupérer le libellé du mois (ex : Janvier, Février, etc), avec la fonction MONTHNAME.

SELECT MONTHNAME(CURRENT DATE) FROM SYSIBM/SYSDUMMY1;

Dans l’exemple ci-dessus, nous utilisons “CURRENT DATE” qui renvoie la date système. Du coup, avec la requête ci-dessus, nous récupérons la valeur “Avril”, ce qui est bien correct puisque nous sommes bien le 30 du mois lors de l’exécution de la requête.

D’autres fonctions utiles

Il existe bien entendu des fonctions qui permettent notamment de renvoyer le jour de la semaine avec DAYOFWEEK, qui considère que le dimanche est le premier jour de la semaine, et DAYOFWEEK_ISO, qui considère que c’est le lundi le 1er. La fonction DAYS renvoie une interprétation de la date en entier, ce qui permet également de calculer le nombre de jours qui se sont écoulés entre deux dates.

La fonction JULIAN_DAY permet de renvoyer le nombre de jours entre le 1er janvier 4712 avant J.C. jusqu’à la date renseignée en paramètre. Enfin on peut aussi utiliser WEEK ou WEEK_ISO pour obtenir le numéro de la semaine, sans avoir à effectuer de calcul particulier…

[SQL] Réaliser des “upserts” sur DB2/400

[SQL] Réaliser des “upserts” sur DB2/400

Beaucoup de SGBD proposent des requêtes plutôt simples pour réaliser ce qu’on appelle couramment des “upserts” (ou encore “insert-or-update). Cela permet donc d’effectuer une mise à jour si l’insertion échoue. Dans DB2/400, il faut utiliser une requête “MERGE INTO”.

La documentation d’IBM, en plus de détailler chaque clause, propose également des exemples concrets. Le principe est simple : la requête se base sur une condition de recherche. Il faut alors prévoir le cas où la recherche a abouti et le cas contraire.

Syntaxe

Exemple

MERGE INTO FPRXML p 
USING TABLE(
 VALUES(
  CAST (? AS CHAR(13)), 
  CAST (? AS INTEGER)
 )
) s(FEAN13, FCASKU)
ON (p.FEAN13 = s.FEAN13 
AND P.FCASKU = s.FCASKU)
WHEN NOT MATCHED THEN INSERT (FCACAR, FCASKU, FEAN13)
VALUES (?, ?, ?)
WHEN MATCHED THEN UPDATE SET FPXFNC = ?

Comme on le voit dans l’exemple ci-dessus, la condition de recherche peut être simplement un ensemble de valeurs, qui constituent alors une table. Une jointure est alors effectuée sur la table dans laquelle on veut faire les mises à jour (p.x = s.x). Notez que l’on peut aussi utiliser directement une table (ex : USING matable) ou bien une requête de sélection.