Tag Archives: SQL Image Viewer

SQL Image Viewer post processing option

Leave a reply

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

2 Replies

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 (see here). 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

Leave a reply

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

Leave a reply

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

Leave a reply

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

Leave a reply

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

Leave a reply

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

Leave a reply

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.

Extracting files and data from OLE Object fields

1 Reply

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 Image 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.

If instead you need to insert or update OLE-Object data in your databases, have a look at Access OLE Import.  Using Excel spreadsheets as the input data, you can easily add and update your embedded or linked OLE-Object data.

You can purchase our products on this page.

See also:

SQL Blob Viewer and SQL Image Viewer

Leave a reply

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.