How to store pictures in a Oracle database using labview

[ad_dekkers]

Hello,

I am using the Database connectivity toolset to store test- and quality data into a oracle database.

But i also want to store complete pictures in this database.

Can anyone tell me how to do this?! :worship:

[crelf]

I am using the Database connectivity toolset to store test- and quality data into a oracle database.

But i also want to store complete pictures in this database.

The datatype you want is called a "Long Raw" in Oracle (in most other SQL lands it's called a "Blob" or a "Binary Blob") and it's basically just a binary chunk. Try Googling it for more info... (note: my experience has shown large amounts of data in an array in Blob format can be slow and can cause the database to grow disproportionally to the size of the data you insert - you might want to consider keeping the images outside of the database and having a simple text field to point to its location)

[Mike Ashe]

... (note: my experience has shown large amounts of data in an array in Blob format can be slow and can cause the database to grow disproportionally to the size of the data you insert - you might want to consider keeping the images outside of the database and having a simple text field to point to its location)

I agree completely. If you are storing anything other than very small thumbnails, you should store imaging data or 2D arrays external to the database and give indexes or file names/paths.

The downside of that is that you need sometime of trigger/procedure to handle times when a file is not there, moved, etc, and obviously more files, etc.

[ad_dekkers]

The datatype you want is called a "Long Raw" in Oracle (in most other SQL lands it's called a "Blob" or a "Binary Blob") and it's basically just a binary chunk. Try Googling it for more info... (note: my experience has shown large amounts of data in an array in Blob format can be slow and can cause the database to grow disproportionally to the size of the data you insert - you might want to consider keeping the images outside of the database and having a simple text field to point to its location)

Sorry for my late response, but i was away to France for some days.

I know what kind of field to use and that the database will grow a lot, but what i don't know is how to store the picture (or other BLOB like data) into the database using labview tools.

I can't use a simple "insert into" statement i think, but i can't find a vi that does the job!

[xtal]

Sorry for my late response, but i was away to France for some days.

I know what kind of field to use and that the database will grow a lot, but what i don't know is how to store the picture (or other BLOB like data) into the database using labview tools.

I can't use a simple "insert into" statement i think, but i can't find a vi that does the job!

I can only answer your question with another question -- what format is this picture in LabVIEW? The way the Database Toolkit was written, any datatype that doesn't have a direct equivalent to a database datatype gets written as a BLOB (exactly what you want). So if you have the picture as a 2D array, a picture control, or similar, you can just use the Insert Data VI and wire that to the input. Or you can use a parameterized query and just wire in that 2D array or picture or whatever into the value input of the Set Parameter Value VI for a field defined as binary.

[Rammer]

QUOTE (xtaldaz @ May 3 2006, 01:47 PM)

I can only answer your question with another question -- what format is this picture in LabVIEW? The way the Database Toolkit was written, any datatype that doesn't have a direct equivalent to a database datatype gets written as a BLOB (exactly what you want). So if you have the picture as a 2D array, a picture control, or similar, you can just use the Insert Data VI and wire that to the input. Or you can use a parameterized query and just wire in that 2D array or picture or whatever into the value input of the Set Parameter Value VI for a field defined as binary.

I have been trying to put BLOB data into a SQL database with the parameterized query for a stored procedure. I choose the binary data type, however at some level NI must be converting to a string, because it limits me on how big this blob can be (to a very small value < 1000 data points). Has anyone overcome this?

[Omar Mussa]

QUOTE (Rammer @ Aug 28 2008, 06:26 AM)

I have been trying to put BLOB data into a SQL database with the parameterized query for a stored procedure. I choose the binary data type, however at some level NI must be converting to a string, because it limits me on how big this blob can be (to a very small value < 1000 data points). Has anyone overcome this?

I worked on a project in the past that used an Oracle database and we found that we couldn't write more than 1000 points either so we basically broke up our data into chunks of 999 points. I didn't like the solution because it made it hard to get the data back (had to join multiple rows from the same results table), but it worked.

On a side note, there's another issue that is sort of related that you can sometimes run into - I think there is a limit to how long of an SQL string you can execute using the ADO based toolkit VIs so you might want to be aware of that as well. I don't recall what the string length limit is.

[xtal]

At one point, there was a bug in the ODBC drivers for Oracle that limited the size string (or BLOB) you could write at one time. However, with the OLE DB Provider for Oracle, I was able to write 8 MB BLOBs of data (maybe more, I honestly can't remember anymore - it was in the CAR) with no problems. I kept writing bigger and bigger BLOBs to an Oracle table until I ran out of memory. So I recommend checking what driver you are using for your data communication through ADO.

[Pjotr]

That's right indeed: The DB Tools Insert Data.vi and DB Tools Select Data.vi (LabVIEW 8.5 + database toolkit) work absolutely fine if you use the 'Oracle Provider for OLE DB', but not with the 'Microsoft OLE DB Provider for ODBC Drivers', which not goes beyond a rediculous 1000 bytes or so. Oracle database entry of files and images from LabVIEW made very easy!