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

Hi Alexander, E-Mail could to delivered…

It seems, that all created records have the same header (column DocData)…

I am evaluating serveral low code CRUD solutions for my company. I think the Async code is a bit out of sync :wink:

IMHO {{ ui.UploadFile.value[0] }} returns too much data. I tried it with other low code solutions and the code is working there as expected.
The download of the files via frontend works there too, with similar code.

The MSSQL-Table for storing the binary data is quite simple:

/****** Object: Table [dbo].[__Org_Documents] Script Date: 16.09.2021 20:10:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[__Org_Documents](
[DocOid] [UNIQUEIDENTIFIER] NOT NULL, --Unique identifier for each document
[DocData] VARBINARY NULL, --binary data
[DocLink] NVARCHAR NULL, --file name and file path (if supplied)
[DocSessionOid] [UNIQUEIDENTIFIER] NULL,
[DocCreationTime] [DATETIME] NULL,
[DocModificationTime] [DATETIME] NULL,
[DocUserClsID] [UNIQUEIDENTIFIER] NULL,
[DocType] [TINYINT] NULL,
[DocOwner] [UNIQUEIDENTIFIER] NULL,
[DocReadPermission] [UNIQUEIDENTIFIER] NULL,
[DocWritePermission] [UNIQUEIDENTIFIER] NULL,
CONSTRAINT [PK____Org_Do__420639F07A41E5B7] PRIMARY KEY CLUSTERED
(
[DocOid] ASC
)WITH (STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[__Org_Documents] ADD CONSTRAINT [DF____Org_Doc__DocOi__267ABA7A] DEFAULT (NEWSEQUENTIALID()) FOR [DocOid]
GO

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 tried to create the app with similar functionality:

  1. My async step produce base64 string with image content

  2. Then my second step with update statement encode it it. My query looks like I wrote above:

  3. When I retrieving my images back my first step of download action got base64 string from db

  4. The second step unwraps base64 so I can see the image base64 format that could be displayed in table or form

What step does behave differently in your app?
You can write me an email alex@uibakery.io to share credentials or we can arrange a call where I will try to help you

Hi Alexander,

I think the last step where the result is converted back via “<>Code” is different. It’s still the same result as from my SQL query:

The SQL-Column, where the data is stored, is varbinary(MAX).
Can You access my test App? or shall I E-Mail You the SQL-Queries and SQL-Connection details?

Hi Falk, on your last screenshot I can only see baze64 filed, (your code step maps result in newColumn field)

Send me please an email with SQL connection details

Hi Falk,
I guess my suggested code for the second step of download action was written for displaying in table.
If you would like to show only one document, then the async code step should contain the following code:

return atob({{ data }}[0].baze64);

Hi Alexander,

still no progress:

Async code is only

return atob({{ data }}[0].baze64);

I tried

return {{ data }}.map(item => {
item.newColumn = atob(item.baze64);
//return item;
return atob({{ data }}[0].baze64);
});

too :frowning:

Hi Falk,

I see the correct representation of file in the result.

If you want to create a download link, you have to do the following steps:

  1. Add a link component
  2. Set link to URL setting to {{ actions.DownloadFile.data }}
  3. Add the download attribute to link

Unfortunately, there is no way to do the third step out of the box,
The workaround is: add an action on the link on init and programmatically add download attribute.

// Find a link elemnt by text content
const link = Array.from(document.querySelectorAll('a'))
  .find(el => {
    return /\s*download\s*/.test(el.textContent);
  });
link.setAttribute('download', 'My File')

Thank You very much for Your help.
Still this file handling seems not very low code/ easy to obtain knowledge.

Is there any documentation/guide for uibakery, how to obtain such a knowledge like You do? I did not find very detailed information about this specific topic.
I compare different low code solutions, but to handle/create files is always a delicat topic.
Is there a way to export/import a solution? Could You publish/export a sample application?

Kind regards,

Falk

Hey @Falk_Wegener
Let me jump in for Alex here, while he’s away
We will make sure to add some documentation on the topic, as it’s rather techy indeed :nerd_face:
Meanwhile, did you manage to solve the initial problem or any further assistance is required?