Converting a MSSQL varbinary to a base64 string

Question

The goal

Take the data in mssql, an image, convert to base64 and embed in an email.

Deets

I have an image, stored in a varbinary column in a mssql database.

0xFFD8FFE00....

On the other end, I'm querying it out into an ancient Jython environment because that's all I have access to.

When I query and print, I appear to get a a signed array of bytes or a char (maybe?).

>>> array('b', [-1, -40, -1, -32, 0, 16,...

Another thread had suggested dumping it into the b64 encoder

import base64
encoded = base64.b64encode(queryResult)

Which gave me an error TypeError: b2a_base64(): 1st arg can't be coerced to String

The thread also mentioned converting it to json, but since I'm in Python 2.4 land, I don't have access to import json or import simplejson. Using a json interpreter here seems like a major kludge to me.

I've also tried to convert it on the SQL end with decompress and casting to xml, neither of those work at all. The images work fine when passed as an email attachment, so they aren't corrupted as far as I can tell. To embed them in an html template, I need to get that Base64 string out.

I am missing something, I don't work with this stuff often enough to figure it out. I am aware of signed/unsigned, endian-ness at a high level but I can't quite crack this nut.

Answer 1

Converting Column values from VARBINARY to Base64

In most cases we will need to work on multiple rows in table, and we want to convert only the VARBINARY data into BASE64 String. The basic solution is the same as above, except for the solution using XML XQuery, which we will simply need to use different method.

Option 1: Convert binary to Base64 using JSON

    select Id,AvatarBinary
    from openjson(
        (
            select Id,AvatarBinary
            from AriTestTbl
            for json auto
        )
    ) with(Id int, AvatarBinary varchar(max))
    GO

Option 2: Convert binary to Base64 using XML XQuery

    select Id,
        cast('' as xml).value(
            'xs:base64Binary(sql:column("AriTestTbl.AvatarBinary"))', 'varchar(max)'
        )
    from AriTestTbl
    GO

Option 3: Convert binary to Base64 using XML and the hint "for xml path"

    select Id,AvatarBinary,s
    from AriTestTbl
    cross apply (select AvatarBinary as '*' for xml path('')) T (s)
    GO

Hope this helps...