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

# Note that this applies only to Navision 2013 and newer databases.

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.

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.

 

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.

 

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.

Exporting and extracting images and files from Microsoft Access databases

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 choose 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.  The exported images and files can then be opened ‘normally’, using the appropriate application.

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

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.

Download a FREE 14-day trial of Access OLE Export or SQL Image Viewer now to extract embedded objects from your Access databases the easy way.

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 4 – email notifications added

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

SQL Blob Viewer 4 was released last month, with a bunch of usability improvements.

One of it is the ability to send email notifications for the export process.  With the Professional Edition, you can schedule export jobs to run using the Windows Task Scheduler.  Previously, the job would run, and a log generated of the export process.  If there were any errors raised, you would only know about it if you inspected the log.

With email notifications, you can now receive a copy of the log via email.  You can also choose to have emails to be sent only when errors are raised.  The email options are configured in the Export Wizard.

logs_emails_01

Before email notifications can be sent, you would need to set up your SMTP mail settings first.

email_settings_01

 

Exporting images from OLE Object fields

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

If you use OLE Object fields in your Access or SQL Server databases to store images, you know it’s convenient to just be able to click on the item and have Windows open the image using the registered viewer on your computer.  However, it becomes difficult to extract the images because Access adds additional OLE data to the stored images, thus changing their original form.

Take a Windows bitmap image,

and store it in an OLE Object field in a SQL Server linked-table.

The original file was 2,002,182 bytes in size, but has increased to 2,002,298 bytes when stored in the OLE Object field.  Access has added 116 bytes to the image.

and that the data stored in the field is different from the original bitmap file.

How can we then export this image?  One way is to open the table using Access, then double click on the data, and Access should then open the stored image in the registered OLE server for that image type, in our example Microsoft Paint.

However, this is a tedious process if we need to export a lot of images.  SQL Blob Viewer and Access OLE Export are two applications that we developed to easily export images and other data stored in OLE Object fields.  Exporting the items using SQL Blob Viewer or Access OLE Export is just a matter of writing the appropriate SQL query or selecting the right table.  SQL Blob Viewer is for users who are comfortable writing queries and want more control over how images are exported, while Access OLE Export is for users who just want to be able to select a table or write a simple query and export the images as is.

SQL Blob Viewer also displays a preview of the image when we query the table:

So if you have a ton of images that you need to export from OLE Object fields, give SQL Blob Viewer or Access OLE Export a try.

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.

See also:

SQL Blob Viewer and SQL Image Viewer

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.