Solved

Get file content without temporary saving file

Tejo van de Bor
Rank
Userlevel 2
Badge +2

Hi,

An 3rd party API gets us a JSON with a base64 encoded file which I can save in a varbinary field. Now this file contains specific text we need to extract, and this is possible by saving the file first to a temporary location, and then use process action Readfile to get the content in a process variable and then process it in a stored procedure.

Can this be done without the temp saving of the file? It feels like i’m using 90’s technique 😉 but I cannot find any suggestion in documentation or community for decoding the file on-the-fly. 

icon

Best answer by Mark Jongeling 20 June 2022, 14:55

View original
This topic has been closed for comments

4 replies

Mark Jongeling
Rank
Userlevel 7
Badge +21

Hi Tejo,

The returned Content of the API you can capture inside a variable which then can be converted to an usable format within a process procedure. This would eliminate the need of writing the data to a file and then reading it again. Would that suffice?

"Wake me up before you low-code"
Tejo van de Bor
Rank
Userlevel 2
Badge +2

hi Mark,

So there is no database equivalent of the ‘read-file’ action process action? I tried an approach with a process procedure, and some t-sql I found for encoding the base64 into a string:

 

SELECT 

@zpl_file_code = dbo.decode_utf8_string(result) 

FROM (

SELECT 

CONVERT

(

VARCHAR(MAX), CAST('' AS XML).value('xs:base64Binary(sql:column("BASE64_COLUMN"))', 'VARBINARY(MAX)')

) AS result

FROM

(

SELECT 

file_content AS BASE64_COLUMN

FROM 

shipment_document

WHERE 

document_id=@document_id

) a

) x

 

This works in order to get the zpl code from the file, but when executing in the process action, it takes almost a minute to process:


Any idea’s for this?

Mark Jongeling
Rank
Userlevel 7
Badge +21

Hi Tejo,

The Read file process action only reads the file data of the given File and places it in the file data output parameter. This usually is a variable with datatype varbinary(max). 

In case you have saved the Base64 data you have received from the 3rd party API, you can indeed decode the Base64 string as you explained. 

Example Base64 string conversion

This process shouldn't take too long to execute, although I haven't tried using large files. I suspect the called function dbo.decode_utf8_string may be the issue. Could you execute the query and show the Execution plan? (Ctrl+M to turn on before execution)

The Execution plan can be used to see where SQL Server uses the most resources to execute the query. This may show the spot that causes the performance issue.

For example, the Execution plans for the above queries are as follows:

Execution plans

Could you try this and share this plan? Thanks!

"Wake me up before you low-code"
Tejo van de Bor
Rank
Userlevel 2
Badge +2

hi Mark,

The function is indeed the issue, I got it from https://stackoverflow.com/questions/28168055/convert-text-value-in-sql-server-from-utf8-to-iso-8859-1 so I guess I have to get another decode function :-) 

But I get the idea of the process procedure, although it’s a shame it’s not showing in the process flow designer. Thanks for your help!

Terms & ConditionsCookie settings