{"id":442,"date":"2019-05-18T00:01:54","date_gmt":"2019-05-17T16:01:54","guid":{"rendered":"http:\/\/www.yohz.com\/blogs\/?p=442"},"modified":"2019-09-05T10:24:18","modified_gmt":"2019-09-05T02:24:18","slug":"how-to-merge-multiple-blobs-into-a-single-file","status":"publish","type":"post","link":"https:\/\/www.yohz.com\/blogs\/2019\/05\/18\/how-to-merge-multiple-blobs-into-a-single-file\/","title":{"rendered":"Merge multiple blobs into a single file"},"content":{"rendered":"

Some applications break up a file into smaller chunks before storing them into a table.\u00a0 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:<\/p>\n

ID\u00a0 fileID\u00a0 sequence\u00a0 blobdata
\n1\u00a0 \u00a0 \u00a0201\u00a0 \u00a0 \u00a0 \u00a01\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 (60 Kb)
\n2\u00a0 \u00a0 \u00a0201\u00a0 \u00a0 \u00a0 \u00a02\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 (60 Kb)
\n3\u00a0 \u00a0 \u00a0201\u00a0 \u00a0 \u00a0 \u00a03\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 (60 Kb)
\n4\u00a0 \u00a0 \u00a0201\u00a0 \u00a0 \u00a0 \u00a04\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 (20 Kb)<\/p>\n

When you export the blobs using SQL Image Viewer<\/a>\/Access OLE Export<\/a>\/SQL Blob Export<\/a>, 4 files will be created using the default settings.\u00a0 This is because those products treat each row as containing a separate file.<\/p>\n

To export the 4 rows as a single file, you need to first select the ‘append to file<\/strong>‘ option for existing files.<\/p>\n

\"\"<\/p>\n

This will cause the blob data to be appended to any existing file of the same name.<\/p>\n

Next, you need to ensure that the 4 rows will all export to the same file name.\u00a0 In our example above, we might use the fileID<\/strong> value as the name of the file, so all 4 rows will export to the same file.<\/p>\n

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.\u00a0 In our example, this means we need to sort the rows by the sequence<\/strong> value e.g.<\/p>\n

SELECT fileID, blobdata FROM mytable ORDER BY sequence<\/p>\n

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.<\/p>\n

That’s basically all you need to do to ensure that the blobs are exported in the correct sequence to a valid file.\u00a0 In summary:<\/p>\n