File Upload Save To Local Disk

Ignition
, ,
1

I’ve got the following code in my onFileReceived for a File Upload component within my Perspective view. Everything with this code works exactly as expected and uploads to my SQL Server table with the file data in the varbinary format:

	# Grab the file name and data 
	TagNumber = self.view.params.TagNumber
	FileName = event.file.name
	FileData = event.file.getBytes()
	timestamp = getCurrentTime.getCurrentTime()
	     
	# Use a query to insert the file
	query = "INSERT INTO [Database].[dbo].[ProjectFiles] (TagNumber, FileName, FileData, FileTimestamp) VALUES(?, ?, CONVERT(varbinary(MAX), ?), ?)"
	args = [TagNumber, FileName, FileData, timestamp]
	system.db.runPrepUpdate(query, args)

What I’d like to do now is have some Python code to download the file from the SQL Server to my local machine. It seems that the file data is saved as byte data in the table but I’m thrown off by the varbinary as the datatype.

I know there are system.file.saveFile and system.file.writeFile functions available, but it seems the saveFile doesn’t work on a perspective, and the writeFile doesn’t seem to work without the saveFile beforehand (throws an IO Error file does not exist).

Has anyone developed a script either within Ignition or outside as a python job to save the filedata from the table as a local file on the Ignition Gateway server disk?

Thanks!

2

By local machine, you mean the one running a browser viewing your Perspective application? If so, your only option is system.perspective.download(). It will initiate a download of the given bytes through the browser. Note that you do not get any opportunity to suggest a target folder or enforce a file name. Browsers don't allow that.

It seems odd that you'd want to download what you just uploaded....

saveFile doesn't exist because the gateway can't open a Java dialog box--it has no UI. WriteFile should work given a complete target path and filename. The gateway service will need permissions in the target folder.

3

Okay that makes sense for the saveFile function with opening the file browse box. For writeFile, I ran some tests with just giving it the data from the SQL table and a file name, and I either got the IO Error File doesn’t exist or another error that I can’t remember off the top of my head. I’ve tried just giving it a filename and letting it attempt to write to wherever it wants to with no luck. I also tried doing our factory’s shared drive location which is already mapped on the server with both the actual path and the mapped path. I remoted in to the machine as well to confirm that the server has access to save files there, and it did.

The FileData in the SQL table is in the format of “0x89504E470D0A1A0A0000…”. I think this is byte data correct? Would that have anything to do with the writeFile or do I need to manipulate that data before writing?

4

I would not expect that to work. Especially if you have unescaped backslashes.

That won't work either unless you've taken special steps. Services don't have access to shares mapped on the desktop, and by default cannot access network paths, either.

Provide a complete path to writeFile, where the user the gateway service is running as (LOCAL_SYSTEM, normally) has write access.

Maybe you could show your actual code, Hmm?

5

Thanks for the help on this :slight_smile:

Here is the code that I have tried working with:

	# get file data
	query2 = "SELECT TOP 1 [FileName], [FileData] FROM [Database].[dbo].[ProjectFiles] WHERE TagNumber = ?"
	args2 = [TagNumber]
	result2 = system.db.runPrepQuery(query2, args2)
	
	# (ATTEMPT #1) Tried full path to shared drive like this:
	FileName = "\\\\fshare01\\ProcessAutomation\\NCM\\TagFiles\\" + str(TagNumber) + "_" + str(result2[0][0])
	#  OR (ATTEMPT #2) Tried just the file name to save locally
	FileName = str(result2[0][0])

	FileData = result2[0][1]
	
	system.file.writeFile(FileName, FileData)

So I’ve tried ATTEMPT #1 above where I did the path to the shared drive directly. I did not realize about the mapping of the network drive, so I will look into getting that configured on the server.

I also did ATTEMPT #2 where it was just the filename in hopes it would save just somewhere on the machine.

Let’s say I wanted to save to C:\temp<FILENAME> like that. Would that be possible without mapping?

Thanks!

6

You need to look at the folder permissions that apply to the gateway's service user. There's no one answer, though Ignition itself will choke if it can't write into its own install directory. That may be the only place you can be sure will work.

7

So I’ve come up with a new approach for this since I can’t seem to get it to work on the ignition side of things. I’ve already got a Python script running in the background to catch things in a database when their status changes. I’m planning to add something in there for converting this file data back into a file, but I’m unsure of how to do that. In the Ignition documentation, it says the file data comes back as a ByteArray and in the database it is formatted as 0xASDF1234..... type data.

Does anyone know of a way to get that converted in Python back to a file? I’ve researched bytearrays or binary file data, but I’m not coming up with anything.

Thanks!

Tagging @pturmel since you’ve had some good insight on this topic so far :slight_smile:

8

It is perfectly normal for DBs to display blob data in a hexadecimal format. When delivered in a query, it should actually be bytes.

You never did say why you need these as files on the gateway's disk. If you are going to be displaying these in Perspective, you'll need something like my Blob Server Module anyways, and it needs to pull from a DB.

9

Good question! We have the file upload component in our Ignition app and want to get the user to upload a picture for certain submissions of the form that we have for them. Once that picture is submitted, it goes into the SQL table and we would like it to get stored there but ALSO uploaded into SAP. Our plan was to use a Python script and SAP GUI scripting to click through the SAP screens which eventually gets to a file browser to select the file. So we need a way to get that file from the file upload onto the machine where the Python script is running. All the Python scripting is pretty straightforward for getting the file into SAP, but we are missing the part of getting the file from the SQL table (or straight from the file upload?) onto either the local machine or our shared drive.

Hopefully that makes sense. I looked at your Blob Server Module, but I’m not sure that would be what we need in this instance.

Thanks!

10

Huh. Sounds fragile. Doesn’t SAP have an API you could invoke with httpClient ?

11

We actually just this week got an API working for SAP to create Qnotes which is what we would be attaching the pictures to. I know there is some functionality built in for attaching pictures via the API, but as far as I know, it requires a file location from wherever the API is being called. Our interim solution was going to be the Python script until we could test out the API more extensively, but that would still require the byte data to be converted back into a file.

12

Well…as expected, this ended up being way easier than I thought it would be.

FileName = result[0][0]
FileData = result[0][1]
FileID = str(result[0][2])
		
def safe_open_w(path):
	''' Open "path" for writing, creating any parent directories as needed.
	'''
	if not os.path.exists('C:\\TempFiles'):
		os.makedirs(os.path.dirname(path))
			
	return open(path, 'wb')
		
with safe_open_w('C:\\TempFiles\\' + FileName) as f:
	f.write(FileData)

The open(path, 'wb') is the part of this that made it work since it’s byte data. I couldn’t get it to work when I was troubleshooting originally because I was just taking the data copied out of the SQL table, but it is written as binary data in the SQL table, so nothing was working. This safe_open function is used in case the directory doesn’t exist, it will create one first.