How to upload large binary data to dB so it can be read by any app?

[Ben Zimmer]

Hi everyone,

Short version: how do you upload binary data into a MySQL blob field in such a way that it can be read by any application?

Long version:

I've been struggling with the problem of putting files into database BLOB fields. (MySQL and Database Connectivity Toolkit).

I was initially building a query string and executing the query but was finding that certain binary characters were causing failures (end of string terminators, etc...) So, a working solution was to encode the binary string, and that worked fine, although bloated the dB a fair bit. I could decode in LV and then save the file as needed.

Now, the customer wants to be able to save the files using other apps, including the MySQL Query Browser, so an encoded file is no good.

I found using a parametrized query allows me to put the unencoded string into the dB, but it appends a 4-byte length at the front of the BLOB before it inserts it into the dB. Some apps ignore these 4-bytes (such as .pdf) but most do not.

A related thread on NI discussion forums: http://forums.ni.com/ni/board/message?boar...ssage.id=354361 has no solution, and my support ticket at NI has been ongoing without answer for a while.

Thanks,

Ben

[Phillip Brooks]

I had a similar problem to this several years ago.

My database grew so fast and then ran so slow that I ended up storing the data as a file in a shared location and stored a path to the file in the database.

If your database is providing some sort of security for the BLOB data, this might create an administrative issue (matching up OS and DB permissions) but I think you will find that the file method scales much better and will cost less hardware-wise in the long run.

[Ben Zimmer]

QUOTE (Phillip Brooks @ Jan 21 2009, 11:26 AM)

I had a similar problem to this several years ago.

My database grew so fast and then ran so slow that I ended up storing the data as a file in a shared location and stored a path to the file in the database.

If your database is providing some sort of security for the BLOB data, this might create an administrative issue (matching up OS and DB permissions) but I think you will find that the file method scales much better and will cost less hardware-wise in the long run.

Phillip,

Thanks for the comment, but unfortunately that isn't an option.

Luckily, not very much data is being uploaded, but I understand your point.

Also, in this case, access from anywhere is required, and the IT issues of opening up a file repository to multiple locations make it impossible.

Ben

[Michael Aivaliotis]

Are you using the NI database toolkit? I know that it allows parameterized queries because I've used that in the past for blob data but was not aware of the 4-byte issue.

[Ben Zimmer]

QUOTE (Michael_Aivaliotis @ Jan 21 2009, 03:24 PM)

Are you using the NI database toolkit? I know that it allows parameterized queries because I've used that in the past for blob data but was not aware of the 4-byte issue.

Yup.