Saving and Loading files to SQL table

Our goal is to load specific files into an Ignition accessible SQL database for archiving/future use. I’ve followed the tutorials showing how to put files into SQL and then extract them back onto the client computer. This seems to work fine for text, csv, and ino files.

However, when I do the same thing for a .pdf or .jpg file, in every instance the file fails to download/open properly. In both cases here the resulting downloaded file is noticeably smaller than the original.

I should mention that I set up the data column in our database as varchar(MAX) as we use MS SQL. I’m wondering if there is any other setup that I am missing here? I would have thought that given we’re uploading/downloading as a binary it wouldn’t matter what the file format is.

Below are my bare-bones scripts for uploading and downloading files to my table “filetable”. Any advise you can provide would be appreciated!

Load file from computer to SQL table:
path = system.file.openFile()
if path != None:
data = system.file.readFileAsBytes(path)
name = system.gui.inputBox(“Enter a name for the file”, path.split(’\’)[-1])
if name != None:
system.db.runPrepUpdate(“INSERT INTO filetable (FileName, fileBytes) VALUES (?,?)”, [name,data])

Save file on computer from SQL table:
nameindb = system.gui.inputBox(“Enter file to load:”, event.source.text)
querystring = “SELECT fileBytes FROM filetable WHERE FileName = '” + nameindb+"’"
resultSet = system.db.runQuery(querystring)
data = resultSet[0][0]
filename = system.file.saveFile(nameindb)
system.file.writeFile(filename, data)

Try using varbinary(MAX)

1 Like

I remember reading that same post. On the apps I did, I put a constraint on the files to be less than 1MB. It has worked quite well in my opinion.

Changing the data column to varbinary(MAX) did the trick! Thanks!!!

1 Like