I have been researching for a simple way to submit a jpeg image file into an Oracle Blob, and have yet found a straight forward approach. I've searched the NI sight, forums and web. Does anyone have a code ready to go that will do this? Can I simply use the DB connectivity toolset? If so, exactly how would I code it (code screenshot please). Or do I have to do the oo4o route, in which I have to spend time trying to figure out how to use it with LabVIEW.
There has to be some very simple way to do this without loading oo4o, doesn't there? Keep in mind that my app will be distributed to many machines which might also require installing oo4o.
Using the Database Connectivity Toolkit, it's as simple as writing the JPG bytes to the blob field in your record, unless there is a need to escape certain characters. I had no such need in my tests.
I tried this out with a simple test database. My table structure consisted of (ID, name, description, image), where ID was an int, name and description were text fields, and image was a blob. I think I limited the blob field to 500,000 bytes when I defined the DB. To update a record, I just used "DB Tools Insert Data" and fed it a cluster of int, string, string, string; the image string was simply the output of "Read Characters from File" where I specified my image via its path on my hard drive. I happened to use PNG images instead of JPG, but it shouldn't make any difference.
I've attached my example (LV 7.0), and I can do a screenshot instead if you want it. One caveat: looking around the Web, most developers seem to discourage the use of blob fields to hold image data in relational databases, suggesting that you store the images on the filesystem instead and use the database for meta information about the images (filename, image type, description, etc.). I know absolutely nothing about the overhead associated with choosing particular field lengths for blob fields, but I'll bet it is typically more efficient to follow this advice and store the images on the filesystem instead--no wasted bytes that way, since that's what a filesystem is made for.
Thank you very much for your input. I will try submitting the BLOB as you suggested. Unfortunately, the DBA makes the calls on how items are stored but I will ask him about using a file system instead of the BLOB.
John, I tried this simple example with a test table with the same columns and received this error.
Error -2147217833 occurred at Cmd Execute.vi->DB Tools Insert Data.vi->Manage Image DB Records myVer.vi
Possible reason(s): Exception occured in Microsoft OLE DB Provider for Oracle, ORA-01461: can bind a LONG value only for insert into a LONG column . in Rec Create - Command.vi->Cmd Execute.vi->DB Tools Insert Data.vi->Manage Image DB Records myVer.vi
Not sure if you can help with this. Any ideas of what can be going on?
Are you 100% sure you've defined the database with field types that match the example? It sounds like a sort of a type mismatch is occurring.
I used MySQL as my database back-end instead of Oracle, so maybe that difference is playing a role. Here is what I would recommend trying: - Create the simplest possible database--a table with just one field--and see if you can create records in that situation - Add fields until you run into the problem
I really don't have much practical experience with the SQL toolkit--I'm more of a MySQL/php kind of guy. So, maybe others could chime in if this seems like a more general SQL toolkit problem. One thing that I wasn't sure about is whether or not you could use "DB Tools Insert Data" with a cluster of all the fields if one of those fields is actually set to auto-increment. I feared that you might have to leave out that field, but things worked without error in my example.