Save Signature (Image) to MS SQL Blob

Hi,

I have a solution whereby I'd like to save the image generated by a Signature panel component into MS SQL, specifically a Varbinary BLOB column. I'm not wedded to BLOB, but given the data type we're working with it seems the most appropriate.

The first block I encountered was when trying to run an single row update query - the pick list doesn't show me the BLOB column so I can't select it. Nevermind, I thought, I'll use a bulk-update query and input an array instead. Sadly, it doesn't like this either and I get the following error:

insert into [dbo].[Signatures] ([Sig1Signature], [id]) values (@p0, @p1) - Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

Array looks like this:

Any help appreciated!

Thanks,
Dave

Hey @dcsearle!

Retool stores images as base64 strings, so in order to store it a varbinary data you'll need to explicitly convert it in your query which takes using SQL mode instead of GUI mode and some tricky code :sweat_smile: There's a good StackExchange post on how to do the conversion here. Essentially, you'll want something like:

declare @str varchar(max) = {{fileInput1.value[0]}};
INSERT INTO your_table (id, signature)
VALUES(1, cast(N'' as xml).value('xs:base64Binary(sql:variable("@str"))', 'varbinary(max)'));

If you'd like to insert multiple rows you might consider triggering that query for each signature in an array of items as outlined in these docs. Or, you can try disabling prepared statements so that you can dynamically construct a more complex SQL statement like:

{{listView1.data.map((row, i) => `declare @str${i} varchar(max) = '${row.signature1}';`).join("")}}
INSERT INTO your_table (id, signature)
VALUES {{listView1.data.map((row, i) => `('${row.id}', cast(N'' as xml).value('xs:base64Binary(sql:variable("@str${i}"))', 'varbinary(max)'))`).join(",")}};

Reading that data back into Retool would then require the inverse as well (see this thread). There are more docs on how to do so here, which translate to something like:

select id, signature
from openjson(
    (
        select id, signature
        from your_table
        for json auto
    )
) with(id int, signature varchar(max))
GO

Let me know if that helps!

@Kabirdas ,

Really helpful, thank you :slight_smile:

Based on your advice, I went for this:

Put:

UPDATE dbo.Table
SET Sig1Signature = (cast(N'' as xml).value('xs:base64Binary(sql:variable("{{signature1.value}}"))', 'varbinary(max)'))
WHERE id = {{table1.selectedRow.data.id}}

Get (query):

SELECT   --the varbinary value we want converted to base64
   CAST('' AS XML).value('xs:base64Binary(sql:column("Sig1Signature"))', 'varchar(max)') AS AttachmentBase64
FROM dbo.Table

Image URL:

data:image/png;base64,{{query.data.AttachmentBase64}}

It would be great if Retool could support SQL datatypes just a little bit better for tasks like this as this conversion process adds some overhead, especially for the more novice user. If you could abstract the process of saving down an image to a BLOB at least, that would be a helpful step. I'll raise this seperately as an enhancement idea.