{"id":601,"date":"2019-11-15T12:08:17","date_gmt":"2019-11-15T04:08:17","guid":{"rendered":"http:\/\/www.yohz.com\/blogs\/?p=601"},"modified":"2019-11-15T12:08:17","modified_gmt":"2019-11-15T04:08:17","slug":"sql-blob-to-file","status":"publish","type":"post","link":"https:\/\/www.yohz.com\/blogs\/2019\/11\/15\/sql-blob-to-file\/","title":{"rendered":"SQL blob to file"},"content":{"rendered":"

This post shows you how to export and extract your SQL blob to file using SQL Blob Export<\/a>.\u00a0 You can download a free 14-day trial here<\/a>.<\/p>\n

Start SQL Blob Export<\/a>, and click on the Export images and files<\/strong> item.<\/p>\n

\"\"<\/p>\n

Enter your project details if you plan to reuse the export settings.\u00a0 Otherwise, you can just accept the default values.<\/p>\n

\"\"<\/p>\n

Enter the connection details to connect to your database server.<\/p>\n

\"\"<\/p>\n

Select the table you want to export your blobs from.\u00a0 Tables containing blob columns are highlighted in green.<\/p>\n

\"\"<\/p>\n

You can also enter a SQL command to select the blobs you want to export.\u00a0 We will use the following SQL command to retrieve our blobs.<\/p>\n

\"\"<\/p>\n

Now you need to tell SQL Blob Export<\/a> how to name the exported files.<\/p>\n

\"\"<\/p>\n

Enter the folder to store the files in.<\/p>\n

\"\"<\/p>\n

You can ignore the OLE Object types<\/strong> section if your blobs are not stored in Microsoft Access OLE Object-type fields<\/a>.<\/p>\n

\"\"<\/p>\n

In the Other types<\/strong> section, enter the naming convention for your files.<\/p>\n

\"\"<\/p>\n

We use the value <ProductPhotoID:0000>_<%column%><\/strong> to name our files.\u00a0 <ProductPhotoID><\/strong> is one of the columns we retrieved using our SQL command, and is a number.\u00a0 The suffix :0000<\/strong> 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.<\/p>\n

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

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

Once we have set up our export options, SQL Blob Export will extract the SQL blob to file.<\/p>\n

\"\"<\/p>\n

One important thing you should note is that we did not tell SQL Blob Export the type of blob that’s stored in our table<\/strong>.\u00a0 SQL Blob Export<\/a> can identify the blob type and use the correct file extension accordingly.\u00a0 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.\u00a0 For a complete list of recognised file types, see here<\/a>.<\/p>\n

<\/fb:like><\/div>
<\/div><\/div>","protected":false},"excerpt":{"rendered":"

This post shows you how to export and extract your SQL blob to file using SQL Blob Export.\u00a0 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.\u00a0 Otherwise, you can just […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[44],"tags":[78,79,76,77,81,80,8,48,75,35],"_links":{"self":[{"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/posts\/601"}],"collection":[{"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/comments?post=601"}],"version-history":[{"count":1,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/posts\/601\/revisions"}],"predecessor-version":[{"id":613,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/posts\/601\/revisions\/613"}],"wp:attachment":[{"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/media?parent=601"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/categories?post=601"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/tags?post=601"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}