Working with binary fields in SQL

Tuesday 11 November 2014 @ 4:43 pm

Jared uses a web-based forms application that collects and stores signature images as Base64-encoded PNG files. The Base64 data is stored in a nvarchar(MAX) column in a SQL Server database. He was looking for a way to render the data as images in his Crystal Report and couldn’t find a way to put all the pieces together and contacted me.

As it happens, I have a report that I created that reports on Email messages in my Goldmine database. The body of those messages is stored in my database using a binary field (data type ‘image’). To display the message body on a report I had to write a SQL Command to convert this binary into text.

CONVERT(VARCHAR(8000),CONVERT(VARBINARY(8000),RFC822)) AS message

This converts the first 8000 characters of the binary field called “RFC822” back into a readable text column called “message”. It sounded to me like Jared had the opposite problem with an image stored as a binary text object. I suggested that he create SQL to decode this field back into a Binary field (image). CR can read some binary image fields so it was worth a shot.

That message helped him find a resolution using a similar expression. After stripping off the beginning of the field (r.EncodedSig) which read: “data:image/png;base64” he was able to decode the remainder using XQuery to arrive at a binary column that Crystal would correctly interpret as an image column called DecodedSig:

CAST(N” AS XML).value(‘xs:base64Binary(sql:column(“r.EncodedSig”))’, ‘varbinary(max)’) AS DecodedSig

There was only one problem. The developer had encoded the images as transparent PNG’s. The PNG part is no problem in Crystal 2013 but transparent PNGs apparently don’t work well in any version of Crystal. If it weren’t for the transparency issue the expression above would have worked as planned.

Since Jared and I had both done research on this issue I decided that it was worth a post. You never know when someone might find this post helpful. I am also including a Microsoft link I found which explains a few more things about the VarBinary and the “max” data types.

And one more comment.  I find it remarkable that for my report I had to extract text from of field with an “image” data type, while Jared had to extract images from a field with a text data type. Go figure. And thanks to Jared Faulkner of JF Electric for letting me post his case.









Leave a Reply

Jeff-Net
Recrystallize Pro

Crystal Reports Server