Hex to Base64 to Images

App Building
1

I have a Microsoft sql database that contains staff pictures. They seem to be stored as hex code. When I remove the 0x from the start of the hex code and paste in the hex code online into some hex to base64 conversion tools it converts successfully. I can then view the image in a base64 image viewer online successfully.

Can something like this be achieved in Retool? Hex to Base64 to Image to make it viewable in an image component?

2

Hey @rcanpolat!

Based on this stackoverflow post it looks as though it's possible to convert Hex to Base64 using MSSQL syntax. Are your pictures stored in a varbinary column?

1 Like
3

They are stored in our microsoft sql database as 'image' not 'varbinary'

4

Hmm... can you try something like what's written in this post then?

select 
    staff_picture = CAST('' AS XML).value('xs:base64Binary(sql:column("img1"))','VARCHAR(MAX)')
from (
    select  
        img1 = cast(YOUR_IMAGE_COLUMN_NAME as varbinary(max))
    from YOUR_TABLE
    ) T

That seems to work with some testing on my end, curious to know what you find!

5

Yes that converts it to BASE64. I can fetch images using tha sql query and the image component with data:image/png;base64,{{query2.data.LNL_BLOB['2']}} in the image component URL where {{query2.data.LNL_BLOB['2']}} = a row in my table

Greatly appreciated @Kabirdas