Storing and Retrieving JPEG pictures in an MS Access database using LabWindows/CVI

[ryk]

Hello,

I could use some helpful tips from any database wizards out there...

I am using LabWindows/CVI 2010, MS Access 2010, and using a database that contains image data (jpg, tiff, cad) along with other data types that I can read using the binding function within CVI (eg, DBBindColInt, DBBindColChar etc).  

Viewing the image data as it is stored in this database, the .jpg files are shown as long binary data while in normal view and as OLE object in design view (see attached screen shots for illustration). 

I am not sure how to read image data into files of their native type, i.e. how is jpg data, stored in the database as binary data extracted into a .jpg file? 

Thanks in advance for your help.

Ryk

[JD_war_eagle]

Hi ryk,

Referencing this post: http://www.access-programmers.co.uk/forums/showthread.php?t=164766.  It looks like it will depend on how the data was initially inserted into the DB.  If it was inserted as an OLE Object then things might get a little tricky.  It it was saved to the DB as a standard BLOB then that means the image's exact binary data was stored in the DB.  In this case you should be able to use the DBBindColBinary function available from the CVI SQL Toolkit in order to simply ready the binary data out as it is.  There is an example of how to use this function in the SQL Toolkit Reference Manual available here: http://www.ni.com/pdf/manuals/370502a.pdf and also in the CVI Help documentation.  With the binary data extracted from the DB you will be able to just write the binary data directly to a file on the disk.  There is a useful example showing how to do finally file I/O operations here: http://courses.cs.vt.edu/~cs2604/fall02/binio.html.  Depending on the format of the image file, you can change the extension of the file that you save to disk to match the format of the image.  So basically if you write raw binary data of a JPEG to the disk and then give it the correct JPEG extension, the data will be correctly interpretted by programs that are using it.

[ryk]

Hello JD_War_Eagle,

It is interesting that you should point out a dependancy on how the data is entered into the database, i.e. either binary or OLE object.  depending on how the data is viewed it looks as if either answer would be correct.  (see attachments)  In normal view (using Access 2003 instead of 2010) the data for the illustration column shows long binary data which may be synonomous with BLOB?  While viewing the data in design view, the data type indicates OLE Object.  I am not sure how to interpret how the data was entered.  Nonetheless, I will do some experimenting, and with the information I can gleen from your links, hopefully make some progress.

Thanks,

Ryk

[JD_war_eagle]

Hi ryk,

I was able to look into this situation some more, and it appears that the OLE Object type is somewhat confusing as it just indicates that the field is stored as binary data (a BLOB) in the DB and does not guarantee an OLE Object is stored there.  So it looks like it does go back to knowing exactly how the images were inserted into the DB.  If they are in fact OLE Objects then it is likely they were inserted as Windows BMPs, so they must be extracted as such.  Otherwise, you should be able to simply extract the raw binary data.  Hope this information helps.

[ryk]

Hello JD_war_eagle,

This is good information.  I am pretty certain that the image data is stored as one of three:  .cad, .jpg or .tiff.  I am confident of this because  in columns of the same table, there is numeric information indicating what file type the data is.  The columns are titled CAD, JPG,TIFF or Unknown.  Only one of the columns will have a non-zero value, indicating the stored data is that type of file.  So it looks as if your links may lead to some success once I am finally able to get back to that project.

Thanks again,

Ryk