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.

For T-SQL, see https://stackoverflow.com/questions/45664937/retrieve-varbinary-value-as-base64-in-mssql. — Dan Guzman, May 09 '19 at 14:36
@Dan Guzman I tried a number of methods in there, no luck yet. Still working through a few of the examples. The TechNet article mentioned is hot garbage for an easy to understand example. — Bmo, May 09 '19 at 16:47
This simple example works for me: `SELECT CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("YourBinaryColumn")))', 'VARCHAR(MAX)') Base64EncodingData FROM dbo.YourTable;` — Dan Guzman, May 09 '19 at 18:50

score 0 · Answer 1 · answered Aug 22 '19 at 19:40

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

Converting a MSSQL varbinary to a base64 string

The goal

Deets

1 Answers1