Discussion :

[Liliyana]

Bonjour à tous,

Je travaille actuellement sur SSIS. Ma base source est sur Oracle et celle de destination est Sql Server.
J'essaie de faire un hash md5 pour comparer les données dans mon import dans SSIS.
En cherchant, j'ai trouvé cette formule pour slqServer

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select upper(substring(sys.fn_sqlvarbasetostr(hashbytes('MD5','A')),3,32));

et pour Oracle

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
select rawtohex(
    DBMS_CRYPTO.Hash (
        UTL_I18N.STRING_TO_RAW ('A', 'AL32UTF8'),
        2)
    ) from dual;

ou ça qui donne la même chose

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select  DBMS_OBFUSCATION_TOOLKIT.md5(input => UTL_I18N.STRING_TO_RAW ('A')) from dual;

En faisant des tests, je rencontre rapidement des différences.
Sur le premier champs, j'ai bien la même chose des 2 côtés.
Mais dès que je concatène 2 champs (QTY et LIV_CODE pour le test), je ne trouve pas la même chose.
LIV_CODE étant null dans mon exemple, je décide de remplacer par une valeur au cas où Oracle et SqlServer ne gère pas les NULL de la même manière.

Ce qui est le plus incompréhensible pour moi, c’est lorsque je fais le test en corrigeant à la mano.
Autant sur Oracle, j’obtiens le même résultat. Sur Sql Server, j’ai 2 résultats différent pour , a priori, une valeur identique.

Ci-dessous l’exemple.


Voici le script sur Oracle :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
  SELECT cmd_id, lig_id, qty
      ,liv_code
      ,  DBMS_OBFUSCATION_TOOLKIT.md5(input => UTL_I18N.STRING_TO_RAW (qty||'|'|| nvl(liv_code,'X'))) H1
      ,  DBMS_OBFUSCATION_TOOLKIT.md5(input => UTL_I18N.STRING_TO_RAW (qty||'|X')) H2
  FROM TB_COMMANDES where cmd_id=2313970 and lig_id=857

cmd_id	lig_id	qty	liv_code	H1	H2
2313970	857	-1		CB8173CA768212AE770B6D9833ED2C74	CB8173CA768212AE770B6D9833ED2C74

et la requete sur Sql Server

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
SELECT [cmd_id]
     , [lig_id]
     , [qty]
     , [liv_code]
     , UPPER(SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', CONCAT([qty], '|', ISNULL([liv_code], 'X')))), 3, 32)) AS Hv1
     , UPPER(SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', CONCAT([qty], '|X'))), 3, 32)) Hv2
FROM [dbo].[TB_COMMANDES]
WHERE cmd_id = 2313970
      AND lig_id = 857;

cmd_id	lig_id	qty	liv_code	Hv1	Hv2
2313970	857	-1	NULL	E53FEEE3565B1CCE325B2CF63504EA1B	CB8173CA768212AE770B6D9833ED2C74

J'espère avoir été claire.
Si vous pourriez m’éclairer sur les subtilités de Sql Server, je vous en serai très reconnaissante 😊.
Merci d’avance.

[SQLpro]

Tout dépend de la collation utilisée… Démonstration :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT HASHBYTES('MD5', 'Tête' COLLATE French_CI_AS)

=> 0x54890FF92A7F79D785429965728E62F6

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT HASHBYTES('MD5', 'Tête' COLLATE SQL_Latin1_General_CP1250_CI_AS)

=> 0x60CA44BD3E9C29E48B9B803454ACB861

Problème Oracle ne supporte pas la norme SQL qui propose le principe des collations depuis l'origine. Il travaille avec l'imbitable NLS...

Vous devez donc vous renseigner sur le jeu de caractères utilisé par votre base Oracle et trouver la collation équivalente dans SQL Server, parmi les 4000 collations disponibles :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM sys.fn_helpcollations()

A +

[Liliyana]

Merci beaucoup SQLpro .

Je vais creuser un peu cette histoire de collation.
Mais je ne comprends toujours pas pour quoi au sein même de SQL server je n'ai pas le même résultat sur une même valeur en entrée en principe.

[SQLpro]

Merci beaucoup SQLpro .

Je vais creuser un peu cette histoire de collation.
Mais je ne comprends toujours pas pour quoi au sein même de SQL server je n'ai pas le même résultat sur une même valeur en entrée en principe.

Vous importez des données d'Oracle…. SSIS ne va pas transformer magiquement les octets transmis par Oracle en octets sympa pour SQL Server !!!!

L'informatique avec boule de cristal n'est pas encore au point… !

A +

[Liliyana]

ça j'ai bien compris

Mais pour mon test ci dessous, je ne comprends pas pourquoi je ne trouve pas le même résultats. Et là il n'y a pas d'histoire d'oracle.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT [cmd_id]
     , [lig_id]
     , [qty]
     , [liv_code]
     , UPPER(SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', CONCAT([qty], '|', ISNULL([liv_code], 'X')))), 3, 32)) AS Hv1
     , UPPER(SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', CONCAT([qty], '|X'))), 3, 32)) Hv2
FROM [dbo].[TB_COMMANDES]
WHERE cmd_id = 2313970
      AND lig_id = 857;

cmd_id 	lig_id 	qty 	liv_code 	Hv1 	Hv2
2313970 	857 	-1 	NULL 	E53FEEE3565B1CCE325B2CF63504EA1B 	CB8173CA768212AE770B6D9833ED2C74

[Liliyana]

Je viens de créer une table test avec une ligne de données rentrée à la mano donc sans import de oracle et j'ai exactement le même souci.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
create table test
(qty numeric(10) not null,
 liv nchar(6))

Voici la ligne de test inséré

qty	liv
-1	NULL

et voici la requete avec différents tests

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
SELECT 
       UPPER(SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', CONCAT([qty], '|', ISNULL([liv], 'X')))), 3, 32)) AS Hv1, 
       UPPER(SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', CONCAT([qty], '|X'))), 3, 32)) Hv2, 
       UPPER(SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', TRIM(CAST([qty] AS NVARCHAR) + '|' + ISNULL([liv], 'X')))), 3, 32)) Hv3, 
       UPPER(SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', '-1|X')), 3, 32)) AS Hv4
FROM [dbo].test;

Hv2	Hv1	Hv3	Hv4
CB8173CA768212AE770B6D9833ED2C74	CF03E9BE3CE0A0A9FD461A3F0C440F30	7644E0C8B3F113A6A7385466EA2717C0	CB8173CA768212AE770B6D9833ED2C74

[hmira]

Bonjour,

Sauf erreur de ma part, le Hash MD5 sous SQL Server ou tout autre système, dépend aussi de l'encodage des caractères et donc le Hash MD5 ne sera pas la même pour une valeur d'une colonne de type VARCHAR comparé au Hash MD5 d'une valeur d'une colonne de type NVARCHAR (UNICODE), et ce, même si les 2 valeurs sont "identiques".

Vous pouvez analyser et comprarez avec le résultat de la requête ci-dessous où tout est en NVARCHAR (UNICODE).

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT 
       UPPER(SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', CONCAT([qty], N'|', ISNULL([liv], N'X')))), 3, 32)) AS Hv1, 
       UPPER(SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', CONCAT([qty], N'|X'))), 3, 32)) Hv2, 
       UPPER(SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', TRIM(CAST([qty] AS NVARCHAR) + N'|' + ISNULL([liv], N'X')))), 3, 32)) Hv3, 
       UPPER(SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', N'-1|X')), 3, 32)) AS Hv4
FROM [dbo].test;

Je crois qu'il y a beaucoup de paramètres qui rentrent en jeux ; Collation telle que évoquée par SQLPro, Encodage Unicode vs UTF8 (sous ORACLE), etc.

A+

[Liliyana]

Bonjour Hmira,

Merci pour ces informations. J'y vois un peu plus clair.
Je vais essayer de les croiser toutes en priant pour que je vois le bout de mon problème.

[SQLpro]

Vous utilisez du CHAR (ou NCHAR) donc une longueur fixe. Le hash s'applique sur tous les caractères y compris les blancs résiduels inhérents au type de longueur fixe… Il tient compte aussi du type ASCII ou UNICODE et de la collation...


Démo :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT 
       CONCAT([qty], '|', ISNULL([liv], 'X'))                    + '*' AS Hv1, 
       CONCAT([qty], '|X')                                       + '*' AS Hv2, 
       TRIM(CAST([qty] AS NVARCHAR) + '|' + ISNULL([liv], 'X'))  + '*' AS Hv3, 
       '-1|X'                                                    + '*' AS Hv4
FROM [dbo].test;

Résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Hv1             Hv2       Hv3      Hv4
--------------- --------- -------- -----
-1|X     *      -1|X*     -1|X*    -1|X*

La première colonne ne contient pas le même sémantique que les autres colonnes, du fait des blancs.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT 
       DATALENGTH(CONCAT([qty], '|', ISNULL([liv], 'X')))                     AS Hv1, 
       DATALENGTH(CONCAT([qty], '|X'))                                        AS Hv2, 
       DATALENGTH(TRIM(CAST([qty] AS NVARCHAR) + '|' + ISNULL([liv], 'X')))   AS Hv3, 
       DATALENGTH('-1|X')                                                     AS Hv4
FROM [dbo].test;

Comme vous le constatez la longueur en nombre d'octets n'est pas la même pour toutes les colonnes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Hv1         Hv2         Hv3         Hv4
----------- ----------- ----------- -----------
18          4           8           4

Qu'en est-il des types ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT 
       SQL_VARIANT_PROPERTY(CONCAT([qty], '|', ISNULL([liv], 'X')), 'BaseType')                     AS Hv1, 
       SQL_VARIANT_PROPERTY(CONCAT([qty], '|X'), 'BaseType')                                         AS Hv2, 
       SQL_VARIANT_PROPERTY(TRIM(CAST([qty] AS NVARCHAR) + '|' + ISNULL([liv], 'X')), 'BaseType')    AS Hv3, 
       SQL_VARIANT_PROPERTY('-1|X', 'BaseType')                                                      AS Hv4
FROM [dbo].test;

Comme vous le constatez, là encore, les types divergent :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Hv1       Hv2      Hv3       Hv4
--------- -------- --------- ---------
nvarchar  varchar  nvarchar  varchar

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT 
       SQL_VARIANT_PROPERTY(CONCAT([qty], '|', ISNULL([liv], 'X')), 'Collation')                     AS Hv1, 
       SQL_VARIANT_PROPERTY(CONCAT([qty], '|X'), 'Collation')                                         AS Hv2, 
       SQL_VARIANT_PROPERTY(TRIM(CAST([qty] AS NVARCHAR) + '|' + ISNULL([liv], 'X')), 'Collation')    AS Hv3, 
       SQL_VARIANT_PROPERTY('-1|X', 'Collation')                                                      AS Hv4
FROM [dbo].test;

Cette dernière requête devrait donner la même collation pour toutes les colonnes mais, pas forcément la même que la mienne, aussi vous laisse-je l'exécuter.

Ceci éclaircira le comportement du langage SQL au niveau du types de données, du transtypage automatique et des collations. SQL Server étant très normatif en général.

Malheureusement Oracle est totalement anormatif en ce domine (aucun respect des types du langage SQL, pas de collations…).

Le résultat du hachage avec MD5 est donc strictement conforme puisque seuls Hiv2 et Hiv4 sont strictement identiques du point de vue du type, de la longueur, de l'encodage et de la collation (la collation faisant partie intégrante du type de données…)

A +

[Liliyana]

Merci SQLpro, je vais faire mes tests et si je vous tiens au courant si je trouve la solution .
Merci pour votre aide.

[SQLpro]

J'en serais intéressé… Parce que vu le nombre de clients qui migrent d'Oracle à SQL Server, de telles demandes vont devenir légion !!!

A +

[Liliyana]

C'est bon j'ai trouvé la solution pour mes 2 champs de test.

Voici le bon script pour avoir le même résultat que oracle.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
SELECT [cmd_id]
     , [lig_id]
     , [qty]
     , [liv_code]
     , UPPER(SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5',concat(   [qty], '|', convert(varchar, trim(ISNULL([liv_code], 'X')))))), 3, 32))
FROM [dbo].[TB_COMMANDES]
WHERE cmd_id = 2313970
      AND lig_id = 857;

Mais je pense que je vais convertir tous mes champs en varchar et mettre des trim de partout pour etre tranquille .

Merci SQLpro, avec le datalength, ça m'a permis de trouver la dernière pièce du puzzle.

[SQLpro]

De rien…..

Il y a de grosses différences avec Oracle qui est très mal branlé sur le sujet et qui mélange joyeusement le concept de collation (inexistant dans Oracle) et le concept de jeu de caractères.
Les collations étant là pour assurer une abstraction sémantique des littéraux dans les bases de données, tandis que les jeux de caractères assurant le côté physique de la chose (la mécanique sous le capot).

A +