Tag Archives: SQL Image Viewer

Incorrectly extracted files in Access OLE Export/SQL Blob Export

Access OLE Export / SQL Blob Export may sometimes extract items that appear to be wrong.  For e.g. it might extract an expected PDF file as an image file.  To troubleshoot such issues, we need your help in sending us the data exactly as stored in your database to us for further investigation.

One way to extract the data is to use SQL Image Viewer.  Once installed, connect to the same database you were trying to extract your items from, and run your query to select the wrongly extracted item(s).

Select the item you’re having problems with, right click to bring up the context menu, and select the View as hex option.

SQL Image Viewer will display the selected item in a hex viewer.  Click on the Save button to save the raw data to a file.

and send us that file at support@yohz.com.  Having the raw data to work with will help us immensely to determine why Access OLE Export / SQL Blob Export appears to be extracting your files wrongly.

 

Raw image file support in SQL Image Viewer

SQL Image Viewer 10 adds support to display RAW camera image files.  If SQL Image Viewer fails to display your RAW images, please send us a copy of the file for further analysis, to support@yohz.com.

You need to take into account the following when exporting RAW image files from your database using SQL Image Viewer.  Raw image files are exported using the .raw extension by default.  This is because SQL Image Viewer is unable to distinguish between the different raw formats (cr2, crw, nef, tec).

To export the raw image files using the correct extension, you need to have a column that contains the source file name. For e.g our result set contains the file name in the source column.

We cannot use the source column as is in our naming convention like this:

because it would include the path portion.  To use only the file name portion, we use the FILENAME operator e.g.

The FILENAME operator tells SQL Image Viewer to treat the value in the column as a fully qualified file name, and to use only the file name portion of the value.

What is we wanted to name the exported images using the ID column?  We will need to use the FILEEXT operator in this case.  Our file naming convention will be entered like lthis:

The FILEEXT operator tells SQL Image VIewer to treat the value in the column as a fully qualified file name, and to use only the file extension portion of the value (including the . separator).

By using the FILENAME and FILEEXT operators, you have more control over how the exported files are named, and how you can use elements from columns containing file names.

If you have any questions or requests, please do drop us a line at support@yohz.com.

SQL Image Viewer post processing option

In SQL Image Viewer 9.10, we added an option to allow you to run an application on each exported file.  So say your database contained zip archives, and you use SQL Image Viewer to export those zip archives to disk.  Using the post processing option, you can then use your favorite archiver to extract the files from the zip archives.

The post processing option is only available when you choose the Export images and files option.

In the export wizard, you will find the option on the Log, Email, and Post-processing Options page.

In this example, we will be using the 7zip command-line interface to extract the contents of our zip files.  The most important thing to note here is that whichever application you use, it needs to complete regardless of its execution status.

For example, in 7zip, if a file of the same name already exists, it will prompt you to overwrite or skip the file extraction.

You cannot allow this to happen when running the application from within SQL Image Viewer, because you cannot respond to the application from within SQL Image Viewer, and thus will block the export process.

To prevent this in our example, we use the -aoa flag to choose to always overwrite any existing files.

So to run your application, enter the fully qualified name to its executable file.  In our example, that’s E:\Program Files (x86)\7-zip\7z.exe.  Because the path contains spaces, we need to enclose them in double quotes.

After the executable path name,  enter the required options for your application.  There are 4 tags you can use to represent the exported file to process.  Given a file name of f:\temp\exports\0002_0003.zip:

  • <FILENAME> returns f:\temp\exports\0002_0003.zip
  • <FILENAME_PATH> returns f:\temp\exports\
  • <FILENAME_NOPATH> returns 0002_0003.zip
  • <FILENAME_NOPATH_NOEXT> returns 0002_0003

In our example, we want to extract the files from our zip archive, so we use the e option.  We then need to provide the archive file name, which we do so using the <FILENAME> tag.  Again, we enclose the <FILENAME> tag in double quotes in case it contains spaces.

We then want to specify the folder to extract the items into, using the -o option.  In this case, we want to extract the files into a subfolder using the zip file name.  So given a zip file name of f:\temp\exports\0002_0003.zip, the contents of that zip file will be extracted into the f:\temp\_dump\0002_0003 folder.

Now after every zip file has been extracted to disk, SQL Image Viewer will run 7-zip to extract the contents of the zip files.

One last option is the Delete file after successful processing item.  Selecting this will cause SQL Image Viewer to delete the exported files if the processing application returns an exit code value of 0.  Most command line applications do that.  A non-zero exit code usually signifies an error.

We hope you find this option useful.  If you want to use an application that requires some parameters using the input file that’s not provided by any of our tags, drop us a line at support@yohz.com, and we’ll try to help you out.

 

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.

 

Merge multiple blobs into a single file

Some applications break up a file into smaller chunks before storing them into a table.  So for e.g. a 200 Kb file might be stored in 4 rows, each storing a maximum of 60 Kb, in the following layout:

ID  fileID  sequence  blobdata
1     201       1              (60 Kb)
2     201       2              (60 Kb)
3     201       3              (60 Kb)
4     201       4              (20 Kb)

When you export the blobs using SQL Image Viewer/Access OLE Export/SQL Blob Export, 4 files will be created using the default settings.  This is because those products treat each row as containing a separate file.

To export the 4 rows as a single file, you need to first select the ‘append to file‘ option for existing files.

This will cause the blob data to be appended to any existing file of the same name.

Next, you need to ensure that the 4 rows will all export to the same file name.  In our example above, we might use the fileID value as the name of the file, so all 4 rows will export to the same file.

Then, your SQL to export the blobs need to sort the rows so that the blobs get appended to the file in the correct sequence.  In our example, this means we need to sort the rows by the sequence value e.g.

SELECT fileID, blobdata FROM mytable ORDER BY sequence

Lastly, we need to ensure that in our output folder, there are no existing files of the same name, otherwise our blobs will get appended to those files.

That’s basically all you need to do to ensure that the blobs are exported in the correct sequence to a valid file.  In summary:

  • select the append to file option for existing files
  • use a naming convention that ensures the related blobs use the same file name
  • sort the rows in the correct sequence so that the blobs are appended in the correct order
  • before the export, ensure that no files of the same name are already in the output folder

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.