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.

Selecting databases to run scripts on in SQL Multi Select

When you add a server to a query group in SQL Multi Select, you can choose whether your scripts need to be ran on all the databases on that server, or just the database you’re connected to, or a specific set of databases.

The first option is an easy way to always run your queries on all your databases on a server.  Even when you add new databases to a server, your queries will still run on those databases.  When you need finer control over which databases to run your queries on, the third option comes in handy.

Let’s say you have a PostgreSQL server, and you set up the connection settings like this:

When you add this server to a group, you will need to specify on which databases your queries should run on.  If you want to run your queries on all databases on that server, just select the run scripts on all databases option.

Likewise, If you want to run your scripts only on the connected database, select the run scripts on connected database only option.

What if you need to specify a set of database names explicitly?  Just select the regular expressions filter option and list down the databases you want to include e.g. say you want to run your scripts only for the databases starting with the prefix database followed by the digits 0 to 6, and also databases starting with the prefix prod.

Or if you need to exclude a specific database, use the exclude filter e.g. say you want to exclude databases with the prefix dev.

This being a regular expression filter, you can easily include or exclude databases matching a string pattern e.g. say you want to include databases with the prefix prod, but not if the prefix is followed by the numbers 0 to 9.

 

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.

Compressing PDFs in your database

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

Recently, a user wanted to compress PDFs stored in his database, in order to reduce the overall size of the database.  He asked if we had any application that could do this.  Unfortunately, we don’t, but it got me to exploring the available options.

Turns out that PDF software development kits aren’t cheap at all.  Licensing can run into thousands of dollars, which isn’t feasible for us.  Open-source software is another option, which is what I finally went with.  In this case, I used Ghostscript, an all-purpose PDF toolkit, available at https://www.ghostscript.com/.

There are 3 steps to compressing PDFs in your database – extracting the PDFs, compressing or optimizing them, and finally uploading them back into the database.  We will use SQL Blob Viewer to first extract the PDFs, then Ghostscript to reduce the PDF size, and finally SQL File Import to upload the PDFs back into the database.  For reference purposes, these PDFs were created from document scans, so they have a 600 dpi resolution and are not optimized for PDF storage.  We’re running this example on Windows, but there is also a Linux version of Ghostscript, and both SQL Blob Viewer and SQL File Import will run on Linux using Wine.

 

Extracting the PDFs

Extracting PDFs from your database using SQL Blob Viewer is very simple – first write the SQL command to retrieve the PDFs.

We then export the PDF files to disk, using the primary key value in the ID field to name the exported files.  We do this so that when we upload the compressed files, we can use the ID value to update the correct rows.

If you have a lot of PDFs to export, you should choose to retrieve only the first few rows, to avoid loading the entire data set into memory.  After that, when you export the result set, the entire result set will be exported.  See this page for details on how to export large result sets with SQL Blob Viewer.

 

Compressing the PDFs

Now that we’ve exported the files, it’s time to use Ghostscript to compress the images found in those PDFs.

The easiest way to do this is to reduce the resolution of the images.  You can do this using the PDFSETTINGS option.  The possible values are:

  • /screen – converts to 72 dpi
  • /ebook – converts to 150 dpi
  • /printer – converts to 300 dpi
  • /prepress – converts to 300 dpi, color preserving

Depending on your requirements, you might want to test the various options to see which best suits your needs.  I took one of the exported PDFs, and converted them using each of the 4 options.  As you can see, the size of the PDF drops dramatically for all 4 options.

Here is the DOS batch script I used to convert the PDFs using the /prepress option (NOTE: Ghostscript options are case-sensitive, so you cannot for e.g write -PDFSETTINGS as -PDFSettings):

for %%x in (*.pdf)  do gswin64c.exe -sDEVICE=pdfwrite -dPDFSETTINGS=/prepress -dBATCH -dNOPAUSE -dQUIET -SOutputFile=”%%~nx_compressed.pdf” %%x

The options used are:

  • -sDEVICE=pdfwrite – this tells Ghostscript that we want to create a PDF file
  • dPDFSETTINGS=/prepress – this tells Ghostscript to convert all images found in the source PDF to 300 dpi resolution
  • -dBATCH -dNOPAUSE -dQUIET – these options indicate that the process should run non-interactively
  • -SOutputFile=’%%~nx_compressed.pdf’ – this tells Ghostscript how to name the output file.  Since we want to add a _compressed suffix, we first use the ~nx option to extract just the source file name without the extension, add the _compressed suffix, followed by the .pdf extension.
  • %%x – this is the source file name that matches the search pattern in the for %%x in (*.pdf) loop

Basically, this loops through all the PDFs in the current folder, converts all images in each PDF to 300 dpi, and saves the PDF with the _compressed suffix.

As you can see, the new PDFs are significantly smaller than the original PDFs.

 

Updating the database

Now, we need to update the existing record with the optimized PDF file.  We can do this using SQL File Import.  First, we enter the search pattern for the files we want to use i.e. those with the _compressed suffix.

Next, we need to map the columns.  Using the file name as the input value for the ID column, we need to:

  • extract the ID value from the file name
  • indicate that this value is a key field
  • indicate that this is an update process

We do this via the following script:

For the attachment column, we simply indicate to SQL File Import that we want to use the file contents.

Internally, SQL File Import will form the following UPDATE statement based on our script as follows:

UPDATE attachments SET attachment = :attachment WHERE ID = :ID

The test script shows that we have extracted the ID value correctly, and that the attachment column will use the contents of the files.

Now, we just need to run the script in SQL File Import, and our records are updated with compressed versions of the PDF files.

 

That is basically all you need to do if you want to reduce the size of the PDFs in your database.  The steps are similar if you want to process any of your blob data and update them in your database e.g.

  • resize images
  • compress files into archive (zip) files
  • process images e.g. add watermarks, convert to grayscale etc

SQL Blob Viewer and SQL File Import will handle the extraction and update process respectively.  You are free to use any external tools to process your images/files.

Restoring a SQL Server bak file that’s password protected

Here at Yohz Software, we offer a service to remove passwords from a SQL Server backup files (or BAK files) that are password protected.  Please note that this is not the same as decrypting an encrypted backup.

Prior to SQL Server 2012, you could protect a backup with a password using the PASSWORD option e.g.

BACKUP DATABASE mydb TO DISK = ‘g:\backups\mydb.bak’ WITH PASSWORD = ‘mypassword’

You would then need to supply the password when you restored the backup.  An interesting point is that while the backup itself is password protected, the data in the backup file was still identical to that found in a backup file that was not password protected.  Thus, using a text editor, you could still inspect the contents of a password protected backup file.

The PASSWORD option was removed in SQL Server 2012 onwards, and in current versions of SQL Server, you can encrypt the backup file.  Now, the data in the backup file is truly protected, and you need the correct certificate to be able to restore the backup.  This is a much better and secure option compared to the old PASSWORD option.

Now, if you want to restore an old backup file that’s password protected, but you no longer have the password, we can help you with that.  Contact us at sales@yohz.com for a quote.

Exporting and extracting images and files from Microsoft Access databases

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

Using OLE Object column types, there are 3 ways you can store images and files in a Microsoft Access database, or in a database (e.g. SQL Server) used by a Microsoft Access front-end.

  • embedding

Embedding involves creating the image/file directly using the associated OLE Server application.  For example, if I chose to embed a bitmap image, Access will open Paint for me to create my bitmap image, and save that image into that field.

  • insert from a file

If you already have the image/file you want to store in your database, you can insert the file into your Access database directly using the ‘Create from File’ option.  Access then copies the file into the field.

  • link to a file

Similar to the above, but this time, the image/file is not stored in the database.  Access just creates a link to the external file, much like a hyperlink in your browser.

If you store your images/files using the first 2 methods, then Microsoft Access adds additional data to your image/file, so that it knows which application to use to open that particular image/file.  This means that if you extracted the data from your database, it will appear different from your original file.

For example, let’s insert an image into an OLE Object field.  The image file is 18.7 Kb in size.

Once inserted into the Access database, the size has increased to 19.192 Kb.

The increase is due to the additional data added by Access.  If you now extract the data as is from the database, it cannot be opened by Paint because of the additional data.  The PNG data is preceded by OLE headers.

This is a common issue faced by Microsoft Access users – when they want to extract images and files from OLE Object fields to their original format, there isn’t a way they can do this easily.  Every image/file has been modified by Access.  You have to open each image/file in Access, then save the item to disk manually.

This is the reason we created Access OLE Export.  In just 4 simple steps, Access OLE Export will extract and export your embedded images and files to disk, stripping out the additional data added by Access.  This means that the exported images and files can be opened ‘normally’, using the appropriate application.

If you require more control over what is exported, SQL Blob Viewer is another option.  SQL Blob Viewer allows you to inspect your data before exporting them, but requires that you be able to write SQL commands.

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.

Using Easy Explorer

Do you keep a bunch of Windows Explorer instances open all the time, each accessing a different folder, simply because you need to access them frequently?  Well, Easy Explorer may be a better alternative for you.  Easy Explorer is our product that allows you to work with multiple Explorer-like windows within a single application.

Easy Explorer starts off simple enough – with a single window.

Say you create another window to access a different folder.  You now have easy access to both folders within Easy Explorer.

You can rearrange the windows in various layouts to suit your needs

save the different layouts, and restore the layouts by simply selecting the desired layout.

Create as many windows as you require, in whatever layout suits you.  You’re only limited by the size of your monitor.

In addition to customizing layouts, Easy Explorer also allows you to:

  • use filters so that Easy Explorer only displays the files you want to see as you open your folders
  • use regular expressions as filter values
  • search for files using regular expressions
  • create bookmarks to frequently used folders

Learn more about Easy Explorer here.

The Explorer bits in SQL BAK Explorer

The left panel in SQL BAK Explorer is essentially a Windows Explorer clone, and shares the same features as found in Easy Explorer.  In this blog, I’ll describe some of the cooler things you can do in the Explorer panel.

Bookmarks!  If you access a folder frequently, simply drag the folder from the Explorer window to the ‘Bookmarks bar’ area.  A bookmark is created for you.  Simply click on that bookmark to open the folder.

 

Filters!  If you want the Explorer window to display only files with the bak extension, simply enter bak in the filter window and click Apply.  Now, only bak files will be displayed in the Explorer window in every folder you browse to.

 

Default layouts!  Say you want the explorer window to be of a certain size, the default folder to be a particular folder, the backup summary panel to be this size, the SQL Window editor area to be that size etc.  First, set up the panels exactly as how you want them to appear every time when SQL BAK Explorer starts.  Then click on the Set as default button.

 

More bookmarks!  In addition to the bookmarks bar mentioned above, you can also maintain bookmarks in a menu.  You can group bookmarks in categories, to make them easier to organize.

SQL BAK Explorer 2.0 released

We just released SQL BAK Explorer 2.0 today, and the significant addition is that of a Query Window.

As some of you may know, SQL BAK Explorer stores backup details of the files it reads in a SQL Server Compact database.  There are 5 tables used to store the details i.e.

  • backupset
  • backupmediaset
  • backupmediafamily
  • backupfile
  • backupfilegroup

The structure of these tables are similar to the same tables found in the msdb database, so if you’ve ever queried for backup details in msdb, you’ll feel right at home here.

So in the Query Window, you simply enter a query to retrieve the backup details you want e.g.

If you want to return multiple result sets, end each query with a semi-colon, and start each query on a new line e.g.

The SQL syntax for SQL Server Compact is fairly similar to the SQL Server syntax, so if you are already familiar with SQL Server query syntax, writing for SQL Server Compact should be easy.