Monthly Archives: May 2019

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