Easier way of displaying images from SQL databases

Queries and Resources
1

I would like to request an easier way of displaying image fields from a MS-SQL database. There seems to be a work around for this but I honestly can't figure it out after spending a lot of time on it.

2

Hi, I’m not too familiar with MS SQL — how are the images stored? What kind of file format is it in? Thanks!

3

This was very helpful and I did get it to work. MSSQL’s image field is actually just a varbinary field. Going back and forth between Base64 and varbinary was, I admit, very confusing for me. In case someone is trying to figure this out later on, here is a stored procedure I ended up writing that stores the image in the database in Base64 and in Varbinary(MAX).

CREATE PROCEDURE [dbo].[spImageData_Put]
(
–
@Base64String as varchar(MAX),
@TableID as integer
)
AS
BEGIN
SET NOCOUNT ON
update [dbo].[testtable] set
base64imagestring = @Base64String,
imagefield = CAST(N’’ AS xml).value(‘xs:base64Binary(sql:variable("@Base64String"))’, ‘varbinary(max)’)
where TestTableID = @TableID
END
GO

4

Just to add on to this, I used this sql function to convert from sql Image data types or varbinary datatypes to Base64 so the retool image handler could deal with it. Here is the code for the function:

CREATE function [dbo].[fnConvertBinaryToBase64] (@BinaryImage varbinary(max))
RETURNS varchar(MAX)
AS
BEGIN
DECLARE @result varchar(MAX)

set @result = cast('' as xml).value('xs:base64Binary(sql:variable("@BinaryImage"))', 'varchar(max)')
RETURN @result;

END
GO

Then to feed data to the image field in retool I have the following query:

select *, [dbo].fnConvertBinaryToBase64 (ImageList.Image1000x500) as Image1000x500Base64
, [dbo].fnConvertBinaryToBase64 (ImageList.Image400x200) as Image400x200Base64
, [dbo].fnConvertBinaryToBase64 (ImageList.Image200x200) as Image200x200Base64
from dbo.ImageList where ImageID = {{select1.value}}

Hope this helps other folks working through this.

5

This is awesome @pdamato! Cool example of adjusting your APIs (although in this case it’s a database directly) to work better with Retool. Are you cool if I add this to our docs for MSSQL?

6

Of course. Happy to help.

7