How to 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.

Convert PDF to JPG fast

Easy PDF Explorer is a WIndows application that can help you convert your PDF  to JPG images.   You can also export your PDF pages to PNG or bitmap files.  It works with batches of files, and can easily convert hundreds or thousands of PDF files.

We start by first selecting the PDF files we want to convert.  In the example below, we have selected 6 PDF files to convert.

Next, click on the Export pages as images button:

to bring up the export options page.

We enter the folder we want to store the exported images in, and also the naming convention for the image files.  In this example, we will use the PDF file name without the extension, and append the page number.  In this way, for a PDF file named contract.pdf, the exported pages will be named

  • contract_0001.jpg
  • contract_0002.jpg
  • contract_0003.jpg

You can easily modify the naming convention to suit your needs.  In addition to the JPEG format, you can also export your pages as PNG or BMP files.

 

You can also choose the size of the image relative to the on-screen size.  Finally, you can choose which pages to convert to images.

Here, we are choosing to export pages 1 to 5.  In addition to explicitly listing the page numbers, we can use the FIRST and LAST tags.  For e.g. to export the last 3 pages, use the <LAST:3> tag.

You can provide multiple values for the page numbers.  For e.g. to export the first 3 and last 3 pages, we will enter the following:

Download a 14-day trial now, and see how Easy PDF Explorer can help you convert your PDF pages to JPEG images fast and easily.

How to merge PDF files

Easy PDF Explorer is a WIndows application that can help you merge hundreds or thousands of PDF files fast and easily.  It works with batches of files, and can easily merge one or more PDF files into your existing file, and at specific pages.

Let’s start off by adding a PDF file containing a copyright page to 2 PDF files.  We first select the 2 files so that they appear in the file details list.

select the files to merge

Next, click on the Add pages button:

the button to open the merge options

to bring up the merge options.

the merge options page

We want to merge a PDF file containing copyright messages to the beginning and end of each of the  2 files.  To do this, we select the PDF file we want to insert, and enter the insertion points as ‘beginning, end‘ e.g.

indicate which file to combine with, and at which pages

Click on the Start button, and Easy PDF Explorer will add the pages from copyright.pdf file to our files.  That’s all there is to it.

Easy PDF Explorer doesn’t limit you to a single PDF file to merge.  For e.g. say we want to combine another PDF file containing our company information.  We simply add the file to the list of files to add e.g.

specify more than 1 file to combine with

You can also provide the exact pages at which to merge the PDF files.  For e.g. if we wanted to insert the copyright.pdf file at pages 2 and 6, we would do this:

indicate which pages to combine the files into

Download a 14-day trial now, and see how Easy PDF Explorer can help you save time merging  PDF files.

DB Doc 6 released

New in DB Doc 6 is improved support for PostgreSQL 11.x databases and XML output.

  • PostgreSQL 11.x databases

    There were some changes to the system tables in PostgreSQL 11, which meant DB Doc had to retrieve some object properties  from a different source.

  • XML output

    You can now generate your PostgreSQL documentation as XML files, in addition to HTML, PDF, CHM, and Word documents.
    The XML files are generated using a fully-customizable script, so you can modify the XML file to fit your requirements.  A customizable XSL stylesheet is also provided to render the XML files in your browser.

More details about DB Doc can be found here, and you can download a free 14-day trial using this link.

DB Doc 6 and XML files

New in DB Doc 6 is the option to export your PostgreSQL database schema in XML format.

We provide a script to generate the XML file, and also a sample XSL stylesheet to display the XML files in your browser.  The provided XML structure is very basic, and if you need to modify the XML structure, it’s very simple to do.

For example, let’s take a look at the XML file that’s generated for a view.

This is the script that generates the XML file for the properties section of the view.

Now let’s say you have an application that will process the XML file, and needs the ‘OID’ and ‘owner’ properties to be easily accessed.  The provided script generates these nodes as part of the ‘properties’ collection, which is great for a stylesheet, but not so easily accessed by an application that needs to read those values.

So we need to be able to generate 2 ‘types’ of XML files – one to be easily displayed, and one to be easily read.  We also don’t want to have to maintain 2 different scripts.

So first, let’s add the code to generate the ‘OID’ and ‘owner’ values in their own nodes.

At this point, our XML file will contain 2 nodes with the ‘OID’ and ‘owner’ values, like this.

We could leave it as is – our application will read the values it requires from the top-level node, while our stylesheet will read the values from the ‘properties’ collection and ignore the top-level node.

Or we could make the script leave out the redundant nodes depending on which version of the XML file we need.  We can do this using the DB Doc script user-defined values.

Let’s say the default output is to have the ‘OID’ and ‘owner’ values as part of the ‘properties’ collection.  When we want the other output, we will define a value named ‘PARSER’ as the first user-defined value.

In our script, we simply look for this user-defined value to generate the correct output.

Now every time we want to generate the XML file for our application to use, just enter the value PARSER as the first user-defined value, and DB Doc will generate the XML file accordingly.  Thus, we only need to maintain a single script file.

More details about DB Doc can be found here, and you can download a free 14-day trial using this link.

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.

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.