Category Archives: SQL Blob Viewer

Redirecting OLE-Object linked files during export

In SQL Image Viewer 10.3, and Access OLE Export/SQL Blob Export 3.18 we added the option to redirect where to look for OLE-Object linked files during the export process.

A common issue when using OLE-Object linked files is that the location of the linked files have changed.  For e.g. you may have previously linked files from the g:\customer_invoices\ folder, but say those files have now been moved to g:\archive\customer_invoices\2018\.

Previous versions of SQL Image Viewer will fail to locate these items during the export process.  In version 10.3, you will now find a redirect button in the Image and File Options screen.

In Access OLE Export/SQL Blob Export, you will find the Redirect button in the Options screen.

Click on that button to bring up the Redirect window.  Here, you can define how the old folders map to the new folders.  For our previous example, we would enter the following:

Now during the export process, when SQL Image Viewer fails to find the files in the original folder, it will look to the redirection rules defined here and look in the new folder for the files.  It will then export the files that it finds.  You can enter multiple redirection entries, and each entry must be on a new line.

Note that the redirection entries are essentially a search/replace process.  Anything entered prior to the redirection symbol (>) is replaced with the value entered after the symbol.  Thus, the entries are not just limited to redirecting entire paths.

Exporting content from Navision Incoming Document Attachment table

In SQL Image Viewer 10.3, we added support for Navision compressed files stored in the Incoming Document Attachment table.

When you run a query on the Incoming Document Attachment table, the Content field will display the following if it contains compressed data.

To extract the contents, click on the Export images and files button.

This opens the Export images and files dialog.

Walk through the steps, and SQL Image Viewer will extract and export the files stored in the Content field for you.

Learn more about SQL Image Viewer here, and download a free 14-day trial now and see how SQL Image Viewer can export your Navision compressed content in minutes.

How to view image data type in SQL Server

The image data type in SQL Server is frequently used to store variable length binary data.  It does not have to store only ‘images’, though most people associate that data type with images.

You may occasionally need to view the type of data that’s stored, but SQL Server Management Studio can only display the binary representation of the data (we’re using the AdventureWorks sample database here).

SQL Image Viewer is one tool that can display the image directly in the result set.

Interesting points to note:

  • the image format was never provided.  SQL Image Viewer can read the binary data and determine the image format itself.  It recognizes png, gif, jpg, tiff, bmp, wmf, emf, dicom, and raw camera formats.
  • details of the image like size and resolution are provided automatically

Though its name might suggest otherwise, SQL Image Viewer doesn’t just support ‘images’.  It recognizes a wide variety of file formats, including Microsoft Office documents, OpenOffice documents, PDF, epub, multimedia files (avi, wav, mp3, mp4, svg, swf, flv, mkv, webm, m4v, mov, 3gp), and compressed archives (zip, 7zip, rar, bzip2, gzip).

For PDFs, SQL Image Viewer is able to display a thumbnail of the first page, and also the number of pages in the file.

Once retrieved, you can export the binary data.  You can export them to a spreadsheet:

or simply export them as individual files:

You can name the files using values from other columns.  You can also convert the exported images to other formats:

For images that contain EXIF values, you can view these values using the internal viewer:

SQL Image Viewer supports Oracle, PostgreSQL, MySQL, MariaDB, Microsoft Access, SQLite and ODBC supported databases.

Thus, you can easily view the data that’s stored in

  • binary and varbinary columns in Oracle databases
  • bytea columns in PostgreSQL databases
  • binary and varbinary columns in MySQL and MariaDB databases
  • binary and varbinary columns in Microsoft Access databases
  • OLE-Object columns in Microsoft Access databases

Download a free 14-day trial now and see how SQL Image Viewer can make working with image data types in SQL Server so much easier.

Using the newer Excel format (xlsx) in your exports

When SQL Image Viewer first supported exporting your result sets to Excel spreadsheets, it only supported the older .xls format.

Since version 9.7 (released in December 2019), the newer Excel format (.xlsx) is now supported.  However, because SQL Image Viewer uses your last export settings when you choose the Excel file name, you may still be using the older .xls format.

To export your data and images to the new Excel format, simply change the file extension from xls to xlsx.

For new users, and users who have never exported to Excel previously, the default extension used is xlsx,  so no action is required on your part to use the new Excel format.

The main advantage in using the xlsx format is that the maximum number of rows in your worksheet increases to 1,048,576 from 65,536 rows.

 

Connecting to a Microsoft Access (.accdb) database

If you have problems connecting to a Microsoft Access database with the .accdb extension (using SQL Image Viewer, SQL Blob Export or Access OLE Export) and see this error:

there are a few possibilities this error is being raised.

You can connect to that database using Access on the same machine

If you have Access installed on the machine and can connect to the .accdb file without problems, then it’s possible that you are using Microsoft Office 365.

Microsoft Office 365 runs in a self-contained virtual environment, and 3rd party products do not have access to the necessary library files to connect to the .accdb file.  In this situation, you will need to install the Microsoft Access runtime files.  You have the following options:

There is, however, one caveat. You cannot install Office 365 and the above Redistributable/Runtime files of the same major version together. You will have to install the Redistributable/Runtime files of a lower version than your current Office 365 version. For example, if you have Office 365 in version 2016 installed, you will have to install the ‘Microsoft Access 2013 Runtime’ or ‘Microsoft Access Database Engine 2010 Redistributable’ and not ‘Microsoft Access 2016 Runtime’.

Another possibility is that you already have the Redistributable/Runtime files installed, but you are currently using the wrong version of SQL Image Viewer/SQL Blob Export/Access OLE Export.  All 3 products come in 32-bit and 64-bit versions, and are installed automatically on your computer.  If you only have the 32-bit Redistributable/Runtime files installed on your machine, you will need to use the 32-bit versions of our products; likewise if you have the 64-bit Redistributable/Runtime files installed.

You have never connected to the Access database on the same machine

To connect to the .accdb file, you will need to install the Microsoft Access Runtime/Redistributable files first.  You have the following options:

Usually, you can just download and install the latest version (Microsoft Access 2016 Runtime) to connect to your database successfully.

 

Running the SQL Image Viewer command line interface in batch files

The SQL Image Viewer Professional Edition includes a command-line interface that you can use to run your export jobs unattended.  If you use batch files to run your export jobs, you would need to ensure that the working directory is folder where the SQL Image Viewer command-line executable (SQLImageViewerCmd.exe) is located.

So say the executable files are located in this folder:

Our batch file (which is in another folder) calls the executable, and provides the export definitions as the first parameter.

This is the error that will be raised when we run the batch file.

This is because the working directory is the folder where the batch file is located.  To fix this issue, use the CD command with the /N flag in the batch file to set the working directory to the SQL Image Viewer folder i.e.

In this way, when SQL Image Viewer runs, it can find all the files that it requires to run successfully.

SQL Image Viewer 9.1 and PDF files

SQL Image Viewer 9.1 can now display thumbnails of PDF files in your result sets.  You do not need to change or add your configuration – simply run your query and if any PDF files are detected, a thumbnail of the first page is displayed in the results area.

Using custom layouts (available only in the Professional Edition), you can display additional pages from your PDF files.

As of now, SQL Image Viewer can only display thumbnails of your PDF files if they are stored as-is in your database.  Thumbnails cannot be displayed for PDFs that are stored in OLE-Object containers, zip archives, or any other format that embeds the PDF file in it.

 

Why is SQL Image Viewer not displaying details of my blobs

If you are using a third-party application to upload images and/or files into your database, and SQL Image Viewer cannot identify the image or file type, then there’s a high probability that your application has modified the data.

Examples of such applications include the MAZE School Information System and the Financial Edge system by Blackbaud.  We had a user who had the following data stored in their Financial Edge database.  In SQL Image Viewer, the following is displayed:

SQL Image Viewer is unable to identify the data that’s stored in the fields (they’re actually PDF files).  If we look at the data using the SQL Image Viewer hex viewer:

we can see that the OLE wrapper (or the original source data) begins at offset 32.  This means that Financial Edge has added 32 bytes of data to the beginning of the original file, which is why SQL Image Viewer does not recognize the file format.

To identify and extract the data correctly, we need to skip the first 32 bytes, so that we only retrieve the original source file.  In SQL Server, we can use the following syntax:

Now, SQL Image Viewer is able to identify the file type correctly.

Ok, admittedly not everybody knows what an OLE wrapper looks like, or any of the other file headers, which is why if SQL Image Viewer cannot identify your blobs, please send us a couple of samples to analyze.  We need the data exactly as stored in your database, so to extract the data, please perform the following steps and send us the resulting files.

Select the column containing the unidentified blob data.

Right click the mouse button to bring up the context menu, and select the ‘Save item’ option.

Enter a file name, save the blob data, and send the file to us at support@yohz.com.

The same issue applies to Access OLE Export and SQL Blob Export too.  If these products cannot identify the file type because the original files have an additional header, they will be exported with a .bin extension.  Please send us a couple of those .bin files to analyze, or you can also use SQL Image Viewer to retrieve the data and follow the steps above to send us the samples.

SQL Image Viewer 9 and custom layouts

We just released SQL Image Viewer 9 with support for custom layouts.  Custom layouts allow you to customize how your data is displayed.  Take for example this default layout:

You see some textual values, and thumbnails of images from the result set.  Using custom layouts, the result could be displayed this way for production staff:

or in this layout for sales staff:

You can pretty much lay out every piece of data in any manner you want, depending on your needs.

Custom layouts can also display multiple frames from a single DICOM, TIFF, or GIF image.  Here is a custom layout showing frames from DICOM images:

Here is another image showing how custom layouts can be used for verification purposes.  In this example, the user wants to verify the scanned values against the actual values on the cheques, so a custom layout is used to place the scanned values right next to the cheque values.

Take a look at the videos describing custom layouts in detail:

  • how to create custom layouts
  • how to display multi-frame images
  • how to easily verify data from scanned images using custom layouts

Let us know how you’re using custom layouts, and how we can improve this further.  Do drop us a line at support@yohz.com with any suggestions or comments.  Thank you.

# Note that custom layouts is only available in the Professional Edition of SQL Image Viewer.

Exporting base64 encoded values in your databases

We recently had a prospect that had issues exporting her data using SQL Image Viewer.  She kindly sent a sample of the data, and it turned out that it was a base64 encoded string stored in a memo field.

So we got to work and now, SQL Image Viewer can decode base64 encoded data stored in memo fields.  It will recognize most image and binary file types.  For example, this is the result returned by Management Studio for a query that retrieves a base64 encoded PDF file:

 

This is the result returned by the same query in SQL Image Viewer:

 

You can immediately identify the type of data that’s stored in your database.  When you want to export the file, ensure that you select the memo field:

 

and also the Extract base64 encoded values in memo fields option:

 

For now, SQL Image Viewer will not inspect binary blob fields for base64 encoded data, as it wouldn’t make sense to store text values in a binary fields.  However, if you do find yourself in such a situation, do drop us a line at support@yohz.com.

Download a trial of SQL Image Viewer now, or buy a license from only USD 45.