Geeks With Blogs
Sean Carpenter Thoughts on Development

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) 
 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 
  SELECT @bin64char = substring(@bin64raw,@i,1) 
   IF ASCII(@bin64char) BETWEEN 65 AND 90 
    SELECT @bin64rawval = ASCII(@bin64char)-65 
    IF @bin64char LIKE '[a-z]' 
     SELECT @bin64rawval = ASCII(@bin64char)-71 
     IF @bin64char LIKE '[0-9]' 
      SELECT @bin64rawval = ASCII(@bin64char)+4 
      IF @bin64char = '+' 
       SELECT @bin64rawval = ASCII(@bin64char)+19 
       IF @bin64char = '/' 
        SELECT @bin64rawval = ASCII(@bin64char)+16 
        SELECT @bin64rawval = 0 
        SELECT @i = @length-1 
  IF @bin64phase = 0 
   SELECT @bin64nibble1 = (@bin64rawval - @bin64rawval%4)/4 
   SELECT @bin64nibble2 = @bin64rawval%4 
   SELECT @bin64nibble3 = 0 
   IF @bin64phase =1   
    SELECT @bin64nibble2 = (@bin64nibble2*4) + (@bin64rawval - @bin64rawval%16)/16 
    SELECT @bin64nibble3 = @bin64rawval%16 
    IF @i<5 
     SELECT @out= convert (binary(1),((16*@bin64nibble1) + @bin64nibble2)) 
     SELECT @out= @out + convert (binary(1),((16*@bin64nibble1) + @bin64nibble2)) 
    IF @bin64phase =2   
     SELECT @bin64nibble1 = @bin64nibble3 
     SELECT @bin64nibble2 = (@bin64rawval - @bin64rawval%4)/4 
     SELECT @bin64nibble3 = @bin64rawval%4 
     SELECT @out=@out+ convert (binary(1),((16*@bin64nibble1) + @bin64nibble2)) 
     IF @bin64phase =3 
      SELECT @bin64nibble1 = (@bin64nibble3*4) + (@bin64rawval - @bin64rawval%16)/16 
      SELECT @bin64nibble2 = @bin64rawval%16 
      SELECT @out=@out+ convert (binary(1),((16*@bin64nibble1) + @bin64nibble2)) 
  SELECT @bin64phase = (@bin64phase + 1)%4 
  SELECT @i = @i + 1 

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).

    @someParameter int,
    @attachmentData text

/*** 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)
 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

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.

Posted on Wednesday, October 19, 2005 1:09 PM SQL Server | Back to top

Comments on this post: Inserting Base64 Data into an image Column

Comments are closed.
Comments have been closed on this topic.
Copyright © Sean Carpenter | Powered by: