Communauté Française des utilisateurs de BizTalk - Insérer des données Base64 dans une colonne SQL Server varbinary(max)

Contexte

J’ai eu besoin de récupérer des données dans une base de données SQL Server, d’enrichir ces données et de les insérer dans une autre base de données SQL Server. Les données sont stockées dans une colonne de type « varbinary(max) ». Je me suis dit « finger in the nose » avec BizTalk. Et bien, je me suis trompé !

La démarche

Pour la récupération des données: ajout de l’option « BINARY BASE64 » dans la clause « FOR XML » et utilisation d’un « SQL Receive Adapter ».

Utilisation de l’assistant « Add Generated Items » pour générer le schéma pour l’appel à la procédure stockée d’insertion. Le « Data Type » de l’attribut correspondant au paramètre de type varbinary(max) est « xs:base64Binary ».

Insertion des données à partir d’une orchestration et un « SQL Send Adapter ».

Le problème

A l’insertion, le « SQL Send Adapter » renvoi l’erreur suivante :

HRESULT="0x80040e07" Description="Operand type clash: ntext is incompatible with varbinary(max)"

Les données « xs:base64Binary » est vu comme du texte et ne peut être converti en « varbinary(max) ».

La solution

La solution adoptée est l’utilisation d’une fonction SQL pour effectuer la conversion

CREATE FUNCTION base64tobin (@bin64raw varchar(max))
RETURNS varbinary(max)
AS
BEGIN
declare @out varbinary(6000)
declare @i int
declare @length int
declare @bin64char char(1)
declare @bin64rawval tinyint
declare @bin64phase tinyint
declare @bin64nibble1 tinyint
declare @bin64nibble2 tinyint
declare @bin64nibble3 tinyint
SELECT @bin64phase = 0
SELECT @i = 1
SELECT @length = len(@bin64raw)
WHILE @i < @length
BEGIN
SELECT @bin64char = substring(@bin64raw,@i,1)
BEGIN
IF ASCII(@bin64char) BETWEEN 65 AND 90
SELECT @bin64rawval = ASCII(@bin64char)-65
ELSE
IF @bin64char LIKE '[a-z]'
SELECT @bin64rawval = ASCII(@bin64char)-71
ELSE
IF @bin64char LIKE '[0-9]'
SELECT @bin64rawval = ASCII(@bin64char)+4
ELSE
IF @bin64char = '+'
SELECT @bin64rawval = ASCII(@bin64char)+19
ELSE
IF @bin64char = '/'
SELECT @bin64rawval = ASCII(@bin64char)+16
ELSE
BEGIN
SELECT @bin64rawval = 0
SELECT @i = @length-1
END

END
IF @bin64phase = 0
BEGIN
SELECT @bin64nibble1 = (@bin64rawval - @bin64rawval%4)/4
SELECT @bin64nibble2 = @bin64rawval%4
SELECT @bin64nibble3 = 0
END
ELSE
IF @bin64phase =1
BEGIN
SELECT @bin64nibble2 = (@bin64nibble2*4) + (@bin64rawval -
@bin64rawval%16)/16
SELECT @bin64nibble3 = @bin64rawval%16
IF @i<5

SELECT @out= convert (binary(1),((16*@bin64nibble1) + @bin64nibble2))
ELSE
SELECT @out= @out + convert (binary(1),((16*@bin64nibble1) +
@bin64nibble2))
END
ELSE
IF @bin64phase =2
BEGIN
SELECT @bin64nibble1 = @bin64nibble3
SELECT @bin64nibble2 = (@bin64rawval - @bin64rawval%4)/4
SELECT @bin64nibble3 = @bin64rawval%4
SELECT @out=@out+ convert (binary(1),((16*@bin64nibble1) +
@bin64nibble2))
END
ELSE
IF @bin64phase =3
BEGIN
SELECT @bin64nibble1 = (@bin64nibble3*4) + (@bin64rawval -
@bin64rawval%16)/16
SELECT @bin64nibble2 = @bin64rawval%16
SELECT @out=@out+ convert (binary(1),((16*@bin64nibble1) +
@bin64nibble2))
END
SELECT @bin64phase = (@bin64phase + 1)%4
SELECT @i = @i + 1
END
RETURN(@out)
END

Cette fonction doit être appelée dans la procédure stockée d’insertion :

CREATE PROCEDURE [dbo].[Insert_data]

@binaryMessageString varchar(max)

BEGIN

DECLARE @buffer varbinary(max)

SELECT @buffer = dbo.base64tobin(binaryMessageString)

INSERT My_Table(My_Data) VALUES (@buffer)

END