Monthly Archives: November 2019

SQL blob to file

This post shows you how to export and extract your SQL blob to file using SQL Blob Export.  You can download a free 14-day trial here.

Start SQL Blob Export, and click on the Export images and files item.

Enter your project details if you plan to reuse the export settings.  Otherwise, you can just accept the default values.

Enter the connection details to connect to your database server.

Select the table you want to export your blobs from.  Tables containing blob columns are highlighted in green.

You can also enter a SQL command to select the blobs you want to export.  We will use the following SQL command to retrieve our blobs.

Now you need to tell SQL Blob Export how to name the exported files.

Enter the folder to store the files in.

You can ignore the OLE Object types section if your blobs are not stored in Microsoft Access OLE Object-type fields.

In the Other types section, enter the naming convention for your files.

We use the value <ProductPhotoID:0000>_<%column%> to name our files.  <ProductPhotoID> is one of the columns we retrieved using our SQL command, and is a number.  The suffix :0000 means we want SQL Blob Export to format the number to 4 digits e.g. 14 is formatted to 0014, 234 is formatted to 0234 etc.

We can use any column names to name our exported file, as long as the column exists in the table we chose to export, or is a column retrieved by our SQL command.

The <%column%> value is a system value indicating the index of the column storing the blob.  Remember that our SQL command selects 2 blob columns (ThumbnailPhoto and LargePhoto), so our naming convention needs to be able to identify from which column the file was extracted from.

Once we have set up our export options, SQL Blob Export will extract the SQL blob to file.

One important thing you should note is that we did not tell SQL Blob Export the type of blob that’s stored in our tableSQL Blob Export can identify the blob type and use the correct file extension accordingly.  It can identify most image formats (jpg, png, tif, bmp, gif, tga, emf, wmf), Office files (doc, docs, xls, xlsx, ppt, pptx), Open Office files, PDFs, and many more.  For a complete list of recognised file types, see here.