Save Signature (Image) to MS SQL Blob

💬 App Building
,
2

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!