# Saving and Loading files to SQL table

**URL:** <https://forum.inductiveautomation.com/t/saving-and-loading-files-to-sql-table/42545>\
**Category:** Ignition\
**Tags:** ignition80\
**Created:** [January 23, 2021, 1:21am UTC](https://forum.inductiveautomation.com/t/saving-and-loading-files-to-sql-table/42545 "2021-01-23T01:21:25Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![richard.d.white](https://avatars.discourse-cdn.com/v4/letter/r/b2d939/32.png) [@richard.d.white](https://forum.inductiveautomation.com/u/richard.d.white)\
**Post date:** [January 23, 2021, 1:21am UTC](https://forum.inductiveautomation.com/t/saving-and-loading-files-to-sql-table/42545/1 "2021-01-23T01:21:26Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\
**Post date:** [January 23, 2021, 2:07am UTC](https://forum.inductiveautomation.com/t/saving-and-loading-files-to-sql-table/42545/2 "2021-01-23T02:07:11Z")

</div>

Try using varbinary(MAX)

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [January 23, 2021, 3:39am UTC](https://forum.inductiveautomation.com/t/saving-and-loading-files-to-sql-table/42545/3 "2021-01-23T03:39:05Z")

</div>

> <https://stackoverflow.com/questions/2347842/storing-pdf-files-as-binary-objects-in-sql-server-yes-or-no>

---

<div class="post-metadata">

**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\
**Post date:** [January 23, 2021, 8:11pm UTC](https://forum.inductiveautomation.com/t/saving-and-loading-files-to-sql-table/42545/4 "2021-01-23T20:11:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![richard.d.white](https://avatars.discourse-cdn.com/v4/letter/r/b2d939/32.png) [@richard.d.white](https://forum.inductiveautomation.com/u/richard.d.white)\
**Post date:** [January 25, 2021, 6:43pm UTC](https://forum.inductiveautomation.com/t/saving-and-loading-files-to-sql-table/42545/5 "2021-01-25T18:43:32Z")

</div>

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