Category Archives: SQL Blob Viewer

High DPI support

We recently added high DPI support to some of our applications so that they render better when user displays are scaled to 125% or more.  We may have missed 1 or 2 items, so if you encounter any GUI elements that are oversized or undersized, we would appreciate it very much if you could let us know at support@yohz.com.

The applications we’ve added high DPI support for are:

Extracting and viewing PDF files in a SimpleIndex database

SimpleIndex is an application that stores PDF files in SQL Server databases.  The PDFs are stored in the General table, in the Image column.  That column is of the SQL Server image type, generally used to store binary data (or blobs).

We recently had a user that could not export the PDF files from his SimpleIndex database.    The user was very helpful to send us the original PDF file, and the PDF file content as stored in his database for comparison.  Turns out that SimpleIndex first converts the binary data in your PDF files to a unicode text string, then stores this unicode text.

This is certainly a strange way to go about storing a file in a column that has a data type that’s perfectly suited to storing binary files.  In addition to making it difficult to extract the PDF file, it doubles the storage requirements.  Whatever the reason, SQL Image Viewer could not identify and display the PDF files.

So the first thing we did was to convert the exported ‘mangled’ PDF files back to their original state.  We added this feature to our UTF8Tool application.

Now the user could export the content from his database, then use this tool to convert those files into proper PDF files.

However, the user wanted to be able to query and view the PDF files from within SQL Image Viewer.  We ended up with the easiest option – we created a second column to store the proper PDF content, then create an application to convert the mangled PDF files from the first column and store them in this new column.

It’s a very basic unpolished conversion application, and you can download and use this application freely.

IMPORTANT NOTE:  This application updates a column in your database, so please make a backup of your database first, in case things don’t turn out exactly the way you want it.

Currently, this application supports only SQL Server databases.  If you need a version that supports another database engine, or if you require some modifications, drop us a line at support@yohz.com.

Now that the user could display the PDF directly in SQL Image Viewer, we had another issue.  By default, SQL Image Viewer only displays the first page of each PDF file that it detects e.g.

This made it difficult to review each PDF file in details.  Fortunately, SQL Image Viewer (professional edition) supports custom layouts.  With custom layouts, you can choose to display a specific number of pages from your PDF files, and also the size of the pages e.g.

Now the user could easily preview more of his PDF files prior to exporting them.

In summary:

  • if you need to convert binary data from unicode files to ansi files, give our free UTF8Tool a try
  • if you want to convert a column containing binary data in unicode text format to ansi text format and store the converted data in a separate column, give this free application a try (but please back up your database first)
  • if you want to preview multiple pages of a PDF file, use the custom layouts feature in SQL Image Viewer (professional edition)
  • if you have data that SQL Image Viewer/Access OLE Export/SQL Blob Export does not recognize, send us an email at support@yohz.com

 

Extracting files from a ProgressCRM database

We recently had a user who needed to extract files from a SQL Server database created by ProgressCRM and needed help.  He was helpful enough to send us a couple of the sample files that were not extracted correctly.

It turned out that each file that was being uploaded was stored in an OLE container.  Normally, our products (SQL Image Viewer, Access OLE Export, and SQL Blob Export) can extract the embedded files, but in this case, the entire OLE container itself had a 12-byte prefix, most likely added by ProgressCRM for their internal use.

So the task was to first extract the content without the 12-byte prefix, and then let SQL Blob Export handle the extraction of the files from the OLE container.

Luckily, this was easy enough to do in a SQLServer database.  We could use the SUBSTRING command to retrieve only the content from the 13-th byte onwards e.g.

SELECT SUBSTRING(CAST(<the column containing the binary data> AS VARBINARY(MAX)), 13, DATALENGTH(content))  FROM <the table name>

So if you find that your application is uploading your files with their own prefix data, you can use the SUBSTRING function (in SQL Server) to remove the prefix so that our products can recognize the actual file content and export them for you correctly.

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

 

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.