Upload/Donwload binary files from PC/Smartphone to MSSQL-Server

We have an existing CRM-Database for multiple customers with a MSSQL-Server and we are evaluating Low Code CRUD systems. Within the Database we have internal binary files, which are stored as varbinary(max). To include this column in a SQL-Statement is no problem. But now it’s getting tricky.
Please, could You provide us with an example,

how I can show such a column (jpg/pdf-files) in the UI bakery and
how I can download such a binary file to the client and
how can I upload a file with the input control (What is the format of the value, base64/binary?)

Thank You very much for your assistance.

Hi @Falk_Wegener

To upload a file with input control you can use variables {{ui.input.value}}, {{ui.form.value.file}} or {{ ui.table.editedRow.newData.file }}} that contain file.

So, upload action may look something like this:

  1. Async code step:
    Read image/document from input into string:
const file = {{ ui.input.value[0] }};
return new Promise((resolve) => {  
  const reader = new FileReader();
  reader.addEventListener('load', (event) => {
    resolve(reader.result);
  });
  reader.readAsDataURL(file);
});
  1. Next step is SQL query
    It’s a little bit tricky. Before insert, I convert string into base64 and then to VARBINARY.
declare @str varchar(max) = '{{ btoa(data) }}';
INSERT INTO UserTable (ID, FILE_COLUMN) VALUES (
10,
cast(N'' as xml).value('xs:base64Binary(sql:variable("@str"))', 'varbinary(max)')
);

To load and show data we need another action with two steps:

  1. SQL Query - load table and convert VARBINARY to base64
    SELECT * from UserTable cross apply (select filecolumn as '*' for xml path('')) T (baze64);
  2. Code step - unwrap baze64 column from base64 one more time, because we had to wrap it before insert:
return {{ data }}.map(item => {
item.newColumn = atob(item.baze64);
return item;
});

The result of the second action could be used as a source for a table, images could be displayed with an image view type, PDF files could be displayed as a download link via file view type.

Hi @Alexander ,

thanks for Your help. I am new to the Javascript world only worked with SQL, VB.net & VBA.

I get the file uploaded to my SQL-Database, but downloading is doing something, but is not showing the file in an image control or the download link is not downloading the file.
I want to add the filename from the input control, too. How can I access the filename shown in the input control?

My Code is downloading the binary from SQL

SELECT T.* --d.DocData
FROM
dbo.__Org_Documents d
CROSS APPLY
(SELECT DocData AS ‘*’ FOR XML PATH (’’)) T(baze64)
WHERE d.DocOid = ‘8CAD9AD1-3A5D-4F10-8BD8-D02814AE9B9F’; – static guid for testing

and the next step is a <>Code with:

return {{ data }}.map(item => {
item.newColumn = atob(item.baze64);
return item;
});

I tried to set the content for an image control to my DownloadFile-action data:

{{ actions.DownloadFile.data }}

but without luck and I get no errors :frowning:

Thank You very much for Your reply.

Hmm…

I assume that data that is stored in your DB may be in format, other than I described in my comment.
How does look the beginning of string that is returned from the first step?

It seems, that all have the same header…

If it helps, I can give You access to my azure test SQL-Server. The Azure SQL-DB is just for evaluation and contains only demo databases with demo records.

Thanks for Your help

I have written an email to you, looking forward to hearing from you