3

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.

Improve this question
3
  • For T-SQL, see stackoverflow.com/questions/45664937/…. Commented May 9, 2019 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. Commented May 9, 2019 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; Commented May 9, 2019 at 18:50

1 Answer 1

Reset to default
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...

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Have used Option2 and it is suitable for my environment working fine.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.