Sean Carpenter

I recently had the need to insert some binary data that was encoded as Base64 into a SQL Server image column.  Since I was using BizTalk, I was hoping it would be relatively straight forward - I was wrong.  There's a bit of information here on how I came to this problem and some BizTalk stuff, so if you just want the solution, jump to here.

The Background

I was using BizTalk to pull a record from one SQL Server, do some processing on the data, and then insert the processed record into another SQL Server.  The catch was that I needed to pull along some binary data and insert it into the second SQL Server.  The data was stored in an image column in each SQL Server DB.

The Problem

Things started out easily enough.  I was using a SQL Receive adapter to get the data from the source database.  I added the BINARY BASE64 option to the FOR XML clause I was using and got the binary data as Base64.  I then used the Add Generated Items wizard to generate the schema for the SQL insert (I was using a stored procedure that had a parameter typed as image).  I was happy to see that the generated schema listed the data type of the attribute corresponding to the stored procedure parameter as “xs:base64binary”.  This led me to believe that BizTalk (or the SQL Send Adapter, or SQLXML, or something) would convert the data back to binary as part of the insert.  Not so.  When the orchestration ran, I just got an error back from SQL Server:

The adapter "SQL" raised an error message.
Details "HRESULT="0x80040e07" Description="Operand type clash: ntext is incompatible with image"

So obviously nothing was converting the Base64 data back to binary.

The Solution

I posted a question to the newsgroups, but didn't really receive a good answer.  The solution I ended up with was to slightly modify a SQL Server UDF I found on the newsgroups.  The content of the modified UDF is below:

CREATE FUNCTION base64toBin (@bin64raw varchar(8000)) 
RETURNS varbinary(8000) 
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) 
 if right(@bin64raw, 1) <> '='
  set @length = @length + 1
 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

The one change I made was to add the section that begins “if right(@bin64raw...”.  I added this because my Base64 data was more than 8000 characters long and I needed to be able to call this UDF more than once with “chunks” of the Base64 data.  Without the change, the UDF will drop the last character of the Base64 data on “chunks” in the middle.  Below is a sample that calls this UDF with chunks of 2400 characters (because of the way Base64 encoding works, the “chunk” size has to be divisible by 4).

CREATE PROCEDURE testConvert
    @someParameter int,
    @attachmentData text
AS

/*** Table schema used for test
CREATE TABLE testData(someValue int, attachmentData image, CONSTRAINT PK_testData primary key nonclustered (someValue))
***/

-- insert NULL (0x0) into the image field so that the TEXTPTR function will work
insert testData(someValue, attachmentData)
values(@someParameter, 0x0)

declare @pointer varbinary(16)
select @pointer = TEXTPTR(attachmentData) from testData where someValue = @someParameter

declare @buff varchar(2400)
declare @offset int, @imgOffset int
set @offset = 1
set @imgOffset = 0
while @offset <= datalength(@attachmentData)
begin
 select @buff = substring(@attachmentData, @offset, 2400)
 declare @img varbinary(8000)
 select @img = dbo.base64toBin(@buff)
 UPDATETEXT testData.attachmentData @pointer @imgOffset NULL @img
 set @imgOffset = @imgOffset + datalength(@img)
 set @offset = @offset + 2400
end

So that's it.  This has been working in production now for over a month, so I'm pretty happy.  Hopefully this can help someone else.