/
Collation SQL sur champs (412)

Collation SQL sur champs (412)

Owned by Bertrand Littel
29/11/2024
4 min read

Le dictionnaire de données DHSD en version Harmony 412 permet de donner une valeur de collation SQL spécifique a un champ. Attention cependant au requêtes additionnelles (widget KPI ou recordSQL) qui peuvent être impactées

Rappel collation SQL

La collation SQL correspond à la manière dont les chaînes de caractères sont traitées dans la distinction des caractères tels que les accents, les majuscules, les minuscules et les autres caractères pris en charge et les règles de tri pour les données que la base de données est capable de gérer.

Par exemple le fait qu’une chaîne soit CASE SENSITIVE (=sensible à la casse) fait que le moteur SQL traite différemment : là et la et La et Là et LA. Alors qu’en CASE INSENSITIVE (=non sensible à la casse) verra un seul cas pour les 5 précédents, par exemple dans une comparaison.

  • case SENSITIVE => là est différent de la

  • case INSENSITIVE => là équivaut à la ou LA

 

Pourquoi permettre une collation différente sur un champ

La base de données a une collation par défaut, appliquée aux champs contenant des caractères.

Divalto ERP utilise historiquement le mode CI AS (=Case Insensitive Accent Sensitive) donc “LA” équivaut à “La” mais n'équivaut pas à “Là” car l’accent est pris en compte comme différence mais pas la majuscule.

L’inconvénient est que lors d’une recherche (par powersearch ou une simple recherche par libellé) on souhaite en général avoir un résultat qui ne tient pas compte des accents. Pour obtenir ce résultat, la couche métier ERP fait la comparaison en passant par les majuscules, ce qui se traduit par un UPPER au niveau de la requête SQL.

Ainsi, on ne compare pas les chaînes directement mais par exemple UPPER(“Là”) et UPPER(“LA”), donc la majuscule de la chaîne, dans le but que la recherche donne les deux valeurs en résultat.

Cette technique impose une écriture de requêtes SQL plus difficile, avec des règles strictes à suivre (il faut pense au UPPER), et donne plus de travail au moteur SQL qui doit faire les transformations en majuscules.

Permettre une collation CI AI (=Accent Insensitive) sur les champs qui servent à de la recherche texte uniquement permet de faciliter l'écriture des requêtes et d'accélèrer le traitement SQL

Champ avec collation forcée

XWIN permet, à partir d’Harmony 412, d’indiquer la collation Collation CI_AI sur un champ spécifique, pour bénéficier donc d’une écriture simplifiée de la requête et d’un gain de performance à l’exécution des requêtes SQL.

Une nouvelle propriété sur les champs d’un dictionnaire DHSD permet d’indiquer Collation CI_AI = OUI (par défaut c’est NON) par la case à cocher dans les propriétés du champ

image-20240916-082127.png

 

Les outils et services (XPSQL, XLANSERVER, RECORDSQL) tiennent compte de cette nouvelle propriété pour les opérations habituelles (synchronisation principalement)

L’instruction DDInterface délivre cette propriété dans <FlagSQL_CI_AI>

Exemple d’application de collate CI AI dans un create table

image-20240916-083716.png

 Exemple de modification de requête SQL

AVANT ON POUVAIT ECRIRE CECI CASE Like_DesAbr(CHAR desAbr) UPPER(EART.DesAbr) LIKE UPPER(desAbr) MAINTENANT, SI DesAbr A UN COLLATE CI AI CASE Like_DesAbr(CHAR desAbr) EART.Des LIKE desAbr

Attention aux impacts en montée de version - instruction ‘collate’

Impacts de la collation sur un champ

En effet le moteur SQL ne supporte pas traiter des champs de collation différente dans une opération de requête SQL.

Ainsi les instructions suivants

  • COALESCE

  • CASE WHEN

  • CONCAT ou autre instruction de concaténation

  • opérateurs de comparaison de chaînes

PROVOQUENT UNE ERREUR SQL SI LA COLLATION EST DIFFERENTE ENTRE LES CHAINES

 

Ainsi, une requête SQL par exemple de Widget KPI qui fonctionnait en version ERP 10.11 Runtime 411 et qui concaténait par exemple un libellé et un code, peut donner une erreur.

Il convient donc de passer en revue les requêtes en surcharge ou ajoutées

Comment corriger - mot clé instruction ‘collate’

Exemple : dans le cas suivant (extrait d’un recordSql pour powersearch)

'Affaire ' ||RTRIM(PRJAP.affaire)||' - '||RTRIM(PRJAP.Lib80)||' - '||RTRIM(COALESCE(Client.Nom, Fournisseur.Nom, TiersInterne.Nom,'-')) as Sujet(256)

provoque une erreur SQL!, car tous les champs ‘code’ (affaire, nom) ont une collation ‘par défaut’ CI AS, mais la concaténation par || avec le PRJAP.Lib80, qui lui dans cet exemple bénéficie d’une collation sur le champ CI AI, donne pour le moteur SQL des collations différentes.

Cette situation se corrige avec mot clé “COLLATE” suivi du nom de la collation Latin1_General_CI_AI

Donc ici on ajoute COLLATE Latin1_General_CI_AI après le nom du champ

'Affaire ' ||RTRIM(PRJAP.affaire)||' - '||RTRIM(PRJAP.Lib80 COLLATE Latin1_General_CI_AI)||' - '||RTRIM(COALESCE(Client.Nom, Fournisseur.Nom, TiersInterne.Nom,'-')) as Sujet(256)