Writing FILESTREAM Objects to SQL Database

[maluigario]

TL; DR: I am trying to figure out how to push files as FILESTREAM BLOBs to a SQL Server database using the DB Connectivity Toolkit.  I am working with our IT department to ensure that the tables are set up properly, so I just need to know how to push these items from the LabVIEW side.

I am working on an update to our automated test setup.  The results of our tests are pushed to a database table currently so that, if we get an RMA back, we can go back and see if there was anything on the fringes when it was first tested and hopefully establish cause of failure.

At present, we have a number of human-readable files that are generated during the test, including several images and several CSV files containing things like the communication log from the device and DAQ measurements taken during critical sections of the test.  We have been storing these files as is in a separate folder in a separate area completely disconnected from the database results, meaning that a person has to go and hunt around the filesystem to find them.  Before, the database and the folder location for file results weren't even on the same server, making the problem even more convoluted.

I am no DB expert.  I'm barely more than a novice, mostly from things I have taught myself during the development of this project.  So I know only that FILESTREAM seems to be the way to go since these files are over 1MB in size and I can push the results to the table like any other object without having to worry about needing to store the files separately in the server's filesystem manually.  I assume I need to open the file and translate its contents to a variant in some way, or read its binary data from the file somehow, but how I do that is a complete mystery to me.  

NOTE: For a number of reasons, we DO NOT use Stored Procedures to push the results to the database.  I manually build the queryies and execute them LabVIEW.

[Kyle97330]

Are you using LabVIEW's database toolkit or another solution to do your uploading?

Anyway, if you look on this page about 1/3rd of the way down there's a "Inserting FILESTREAM data" section which shows some example SQL of how to upload a file as filestream data, can you try that as your query (after changing the values to mach your system and your table structure) and see if it works?

[maluigario]

---

@Kyle97330 wrote:

Are you using LabVIEW's database toolkit or another solution to do your uploading?

---

I am using the DB Connectivity Toolkit.  Putting that together with the link you provided, will I actually be able to upload the file through the SQL query from my side of the application?  Like, I can write exactly what is in their query into a string, provide the path to the file I want to upload on my local machine, and then reference the SQL variable later in the same query, and it will all properly push to the database?

[Kyle97330]

I actually don't know that it will work.  I suggest you try it and if it doesn't work, check the errors to see if the problem is easy to fix.  If not, come back and post your VI along with the database table structure you're trying to upload to and maybe we can be of more help?

[maluigario]

IT was supposed to have the database side sorted by now, but they do not.  I will respond again in the next few days when we have everything worked out.  They are loathe to allow FILESTREAM abilities because apparently it is a security risk.

[Mark_Yedinak]

What about simply storing the files in binary blob fields. This would be done through normal INSERT statements. If the files are very large yo may need to break them up into chunks. When we do this we use MD5 hashes for the ID for the file chunks. We have a separate link table that has the file name, file ID, record position and MD5 hash of the file chunk. Then no file streams are necessary. Though using stored procedures does make this easier both for the insert and the retrieval but it can be done in the application with basic SQL statements.

[maluigario]

---

@Mark_Yedinak wrote:

What about simply storing the files in binary blob fields.

---

My main reason for doing this is, according to what SQL knowledge I could find on the topic, normal BLOBs s should be avoided for files larger than 1MB due to issues both with fragmentation on the server's disk as well as speed in querying the table later.  The old secondary solution was to store the file on the server's normal filesystem somewhere and then push a path to the database as a field instead.  This comes with its own downsides, such as being confident that IT will maintain the filesystem when the move servers, which they are wont to do at my place of work far too frequently.  It looks like FILESTREAM objects are the best of both worlds, where the database basically manages a filesystem on its own and stores FILESTREAM BLOBs like files.  The contents will move when the database moves, and we still get the benefits of speed.  As far as I can tell, anyway. 

[Mark_Yedinak]

---

@maluigario wrote:

---

@Mark_Yedinak wrote:

What about simply storing the files in binary blob fields.

---

My main reason for doing this is, according to what SQL knowledge I could find on the topic, normal BLOBs s should be avoided for files larger than 1MB due to issues both with fragmentation on the server's disk as well as speed in querying the table later.  The old secondary solution was to store the file on the server's normal filesystem somewhere and then push a path to the database as a field instead.  This comes with its own downsides, such as being confident that IT will maintain the filesystem when the move servers, which they are wont to do at my place of work far too frequently.  It looks like FILESTREAM objects are the best of both worlds, where the database basically manages a filesystem on its own and stores FILESTREAM BLOBs like files.  The contents will move when the database moves, and we still get the benefits of speed.  As far as I can tell, anyway. 

---

If you read my complete response this is addressed. We store the files in 64K chunks using MD5 hashes to identify the chunks. A link table is used to define what chunks and their order that are used to make up a file. To retrieve a file you would need to read back all of the chunks and concatenate them. Depending on the data this can actually save some space if the data contains any 64K chunks that would be identical. You only need to store a chunk once and use the MD5 hash to specify that specific chunk. Your mileage may vary whether you achieve any space saving since it is highly dependent on the data.

[maluigario]

---

@Mark_Yedinak wrote:

---

---

If you read my complete response this is addressed. We store the files in 64K chunks using MD5 hashes to identify the chunks. A link table is used to define what chunks and their order that are used to make up a file. To retrieve a file you would need to read back all of the chunks and concatenate them. Depending on the data this can actually save some space if the data contains any 64K chunks that would be identical. You only need to store a chunk once and use the MD5 hash to specify that specific chunk. Your mileage may vary whether you achieve any space saving since it is highly dependent on the data.

---

I barely know SQL as it is, and am almost entirely self-taught with LabVIEW as well.  I write embedded C code and C#/VB.NET UI applications.  So basically, I haven't the slightest idea how I would do any of the things you've just said.  FILESTREAM seems like the easiest solution to do what I am trying to do and the one that would be easiest for my colleagues to understand and work with should one of them have to take over this program for me.

[Mark_Yedinak]

Just suggesting an alternative since you said IT was resistant to file streams. I know how IT departments can be and how sometimes they are an immovable obstacle.