Category Archives: SQL Image Viewer

Exporting images from OLE Object fields

→ This article refers to SQL Blob Viewer, which has now been renamed to SQL Image Viewer.  The techniques described in this blog is still applicable, as the functionality of the product remains the same.  Only the name has changed.

If you use OLE Object fields in your Access or SQL Server databases to store images, you know it’s convenient to just be able to click on the item and have Windows open the image using the registered viewer on your computer.  However, it becomes difficult to extract the images because Access adds additional OLE data to the stored images, thus changing their original form.

Take a Windows bitmap image,

and store it in an OLE Object field in a SQL Server linked-table.

The original file was 2,002,182 bytes in size, but has increased to 2,002,298 bytes when stored in the OLE Object field.  Access has added 116 bytes to the image.

and that the data stored in the field is different from the original bitmap file.

How can we then export this image?  One way is to open the table using Access, then double click on the data, and Access should then open the stored image in the registered OLE server for that image type, in our example Microsoft Paint.

However, this is a tedious process if we need to export a lot of images.  SQL Blob Viewer and Access OLE Export are two applications that we developed to easily export images and other data stored in OLE Object fields.  Exporting the items using SQL Blob Viewer or Access OLE Export is just a matter of writing the appropriate SQL query or selecting the right table.  SQL Blob Viewer is for users who are comfortable writing queries and want more control over how images are exported, while Access OLE Export is for users who just want to be able to select a table or write a simple query and export the images as is.

SQL Blob Viewer also displays a preview of the image when we query the table:

So if you have a ton of images that you need to export from OLE Object fields, give SQL Blob Viewer or Access OLE Export a try.

There may be situations where both products are unable to identify embedded images correctly.  This may be because the registered OLE Server for that content type is not yet supported.  In these cases, send us (at support@yohz.com) a sample of the embedded data, and we will add support for that OLE server type.

Extracting files and data from OLE Object fields

OLE Object fields are commonly used by applications using Access as the back-end database to store files and common document formats.  It is convenient because Windows handles how the files or documents are opened, modified, and saved via OLE servers.  However, it is difficult to extract the data from those fields because of the additional OLE information embedded together with your data.

For example, let’s create a table in Access, and store a simple Excel workbook, first as an embedded object, and second as an embedded file.  Our table structure is as follows:

We create the first record by directly embedding an Excel workbook.

For the second record, we simply attach an existing Excel workbook.

In Access, we can open both workbooks easily simply by double-clicking on them.  This is the OLE servers at work.

Now if we take a look at the size of the 2 records using SQL Blob Viewer, we can see that the size of the embedded workbook is larger than the attached file.  The size of the attached file record is also larger than the original file size, because Access needs to add additional data to the file.

If you try to export the data as is, you will not be able to open the exported files, because the format itself is not Excel-compliant.  Both records in the OLE Object fields have had additional OLE wrappers added to them.

The usual way to extract the content is to open each item individually in Excel, and save them to files.  This is a tedious process if you have a lot of records you need to extract.

We have 2 products, SQL Image Viewer and Access OLE Export, that can remove the OLE wrappers for data stored in OLE Object fields, and export them to disk.

SQL Image Viewer is for technical users who are comfortable writing SQL queries to retrieve the required data.

Access OLE Export is for less technically inclined users who just want to be able to select a table and export their data quickly.

Both products can identify embedded Office document content, images, PDF content, Open Document content, and other common binary types.  Both products can also extract data from OLE Object fields used in other database engines like SQL Server, MySQL, Oracle, PostgreSQL, Firebird, SQLite, and ODBC data sources.

There may be situations where both products are unable to identify embedded content correctly.  This may be because the registered OLE Server for that content type is not yet supported.  For e.g. PDF files can have different OLE servers like Adobe Acrobat, Foxit PDF Reader. Nitro, etc.  In these cases, send us (at support@yohz.com) a sample of the embedded data, and we will add support for that OLE server type.

Download a FREE 14-day trial of SQL Image Viewer or Access OLE Export now, and see how easy it is to export your ‘trapped’ OLE Object data.

SQL Blob Viewer and SQL Image Viewer

So we have 2 very similar products, SQL Blob Viewer and SQL Image Viewer, and here’s why.

SQL Image Viewer was released 9 years ago, and over the years, it has accumulated a lot of code that is user-specific.  When we made the decision to create a 64-bit version of SQL Image Viewer, we discovered that it would be pointless to port those user-specific functions over too.  We did not want to leave our existing customers with a new version that did not have those functions, so we renamed the new product SQL Blob Viewer.

SQL Blob Viewer has almost the same feature set as SQL Image Viewer.  The most obvious difference is how it displays images – it does this by displaying the images together with the textual data –

while SQL Image Viewer displays images separate from the textual data (we’re still gathering feedback on whether users prefer the SQL Image Viewer way of displaying images).

SQL Blob Viewer can also embed images in exported Excel spreadsheets, has a user-friendlier interface to configure incremental exports, supports 64-bit versions of Access, and supports larger data sets with the 64-bit versions.  The plan is to improve on SQL Blob Viewer, while SQL Image Viewer will be updated only with bug fixes.  As both products were developed using different tools, it would be too much work to maintain 2 code bases.

In the near future, probably when we’ve decided if we should support the same views as SQL Image Viewer, we will release SQL Blob Viewer as SQL Image Viewer (new), as the name SQL Image Viewer seems to attract more traffic (I suppose SQL Blob Viewer is just a tad too technical).  When that happens, you will still be able to install SQL Image Viewer (new) alongside SQL Image Viewer (old), so you can still use both versions concurrently.  Existing users with valid licenses can request for a SQL Image Viewer (new) license.

I would encourage you to give SQL Blob Viewer a try if you have not already done so, and let us know what you think.  If you’re a SQL Image Viewer user with a valid maintenance license, contact us at sales@yohz.com for a SQL Blob Viewer license.