Perspective convert stored Varbinary(MAX) back into a downloadable file

Ignition
1

Hey, folks.

I have a situation where users are storing pdfs in a SQL table. I have a SQL query that is taking in the file name and bytes, converting the bytes to Varbinary(MAX).

Scripting on FileUpload action:

Name = event.file.name
data = event.file.getBytes()
	
		
system.db.runNamedQuery('FileUploadTest', {"FileData":data, 'FileName':Name})

Correlated Query:

image
image1391×418 20.8 KB

Next, I have a table + download button on a perspective view.

image
image1008×363 23.9 KB

Scripting on button action:

selectedRow = self.getSibling(Table).props.selection.selectedRow
FN = self.getSibling(Table).props.selection.data[0].FileName
filedata = system.db.runNamedQuery('FileDownloadTest2', {'FileName':FN})
FromTable = self.getSibling(Table).props.selection.data[0].File
system.perspective.download(FN, filedata)

Correlated Query:

image
image1387×436 20.3 KB

If you notice on the button scripting, I have been attempting two methods. The first pulls the data via the correlated query. This pulls up the file explorer and allows name input, but it does not save a file when save is pressed. The second is FromTable, in which the data is passed from the selected row on the table component. This pulls up the file explorer and downloads the file in the correct format. However, it is corrupted and does not show as intended.
Example photo:

image
image736×281 12.3 KB

I have seen extensive documentation on the forum, with many turning towards third-party modules or retrieving files from the gateway. Permissions and securities at my current place of employment prevent me from using either.

Is there some function or datatype that I can pull within the scripting that will pass this varbinary(max) back as the original bytearray? Or some other solution? My best guess is that it's not passing as the correct datatype. Please let me know if this is possible.

Thank you!

2

Do you want to just do a download of the file or do you want to display it in the PDF reader?

3

Download straight to the User's system. No viewing within Perspective.

4

Hmm... I'm doing something very similar with almost identical code and it is working on my end.

What database are you using?

5

Microsoft SQL Server Management Studio 17. I wonder, did you run yours as a named query or passed it within the script? That might be a difference maker.

6

All named query. Easier that way in perspective.

This is the message that calls the namedQuery

def onMessageReceived(self, payload):
	from com.inductiveautomation.ignition.common import Base64
	ticketNum = self.view.custom.ticket.ticketnum
	ticketPDF = system.db.runNamedQuery('Tickets/Ticket-PDF-Blob',parameters={'id':self.view.params.id})
	ticketPDF = Base64.decode(ticketPDF,0,len(ticketPDF),0)
	system.perspective.download('{}.pdf'.format(ticketNum),ticketPDF)

I store it as a base64 in the database due to other customer requirements. So to get the PDF I have to decode it.

The named query that gets the blob is simply:

SELECT ticketpdf
FROM tblticketpdf
WHERE ticketid = :id

Can you show your table configuration in SQL?
I'm using MySQL but I've done blob stuff in MSSQL as well.

7

This returns a dataset no?

1 Like
8

@victordcq is right.

Change your named query to a scalar type. That will just give you the single blob.

image

3 Likes
9

Thank you for the example.

The table has a very simple configuration, made of name and file.
image

I switched back over to Scalar query, but it runs into the same issue of not actually saving a file when save is pressed.

image
image1315×356 15.5 KB

I can play around with the base64 import, but I might run out of my hours today before I get to it, so I might have to update progress tomorrow. Thank you for the help!

10

You probably want a blob column instead of binary

11

Blob is not an option on this version. I was reading online that varbinary(MAX) is the version of blob for this software. I could have misread.

2 Likes
12

This is correct.

"BLOB" stands for the generic term "Binary Large Object". Different DB brands support these in a variety of column type names.

2 Likes
13

i think blob usually allows bigger files, but idk about the (MAX) bit it might be the same i guess

do you need to convert to varbinary then? ad why not convert it back to btyearray?

14

I'd add some logging into your function to make sure that you are actually getting something back from the query.

IE use a logger and log the len of the returned object and maybe the type. That will help determine if something else is going wrong.

3 Likes
15

Great suggestion! I will update you folks tomorrow or perhaps even late tonight. I am getting pushed out the door as we speak, hahah.

16

I couldn't find a simple way to do it in the scripting. While it is easy to convert within the query towards the DB, I could not find a way to convert it back to bytearray from the DB towards the script. byte(), bytearray(), bytes(), byte, array(), to no luck. There might be a standard way to do this that I just haven't found yet.

17

Might be interesting to push a test pdf (with no sensitive data like payrolls xP)
And give the resulting output as you saw in the table, the bytes. Then we could check if its a conversion/encoding error

18

I have definitely had a similar issue before where a byte array doesn't come into ignition as a python byte array.

I have solved this before with the following:

pythonByteArray = ''.join(map(lambda x: chr(x % 256), nonPythonByteArray))

For me, I was having the same issue with retrieving the image as a BLOB or varbinary from MSSQL so I ended up using the above to convert my array to a python byte array, and then I have been storing the data as a Base64 string in the database.
I do not suggest using base64 for your use, but the conversion may work for you coming out of the database query into the pdf download function.

19

A pyDataSet since 8.1.29, to be accurate.
Still probably not a proper argument to Base64.decode though.

21

WE HAVE FUNCTION!!! About to post an update. Uses a slight derivative of @bschroeder 's model

1 Like