How can I extract Blobs back to .jpg files

Question

How can I extract Blobs back to .jpg files

Stephen Rickett

SSC Veteran

Points: 201
More actions
November 23, 2006 at 5:20 pm

#97901

Hi guys,
Right I need a clever way of extracting Blobs or Binary date out of SQL and re-create the .jpg file?
A. Can this even be done (i've been told it can)
B. How?
I have a 250GB DB with over a million images in that I need to extract.......
Cheers
Steve
November 27, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

typically, you use SQL Server to store the blob, and something like ADO to do the actual stream-to-file operations.

as far as i know, if you were to try and do everything in TSQL, you'd end up creating an ADODB.Stream object anyway; there is no native image-to-file or file-to-image operations.

here's a link to a good example;

http://www.devx.com/tips/Tip/14246

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

john-132685 SSCertifiable Points: 6134 More actions · Answer 2

major kluge:

You can always script a series of BCPs (1 for each row/output .jpg), but you'd probably have to script a separate fmt file for each with 0 length prefix and the datalength.

Still, pretty easy to script and export a batch file to do each record independently (or loop and use xp_cmdshell for each row).

Lowell SSC Guru Points: 323518 More actions · Answer 3

here's a function that you can use in VB6, with minor adaptions.

with 250 GIG database, you'll need to make sure your savepath has enough space.

I'm assuming you have the filename stored along with the image data itself. change the SQL statement and field names to whatever is appropriate for you;

I used this to export a bunch of executables to disk as an example, and it works without error.

Private Function ExportBlobs()

Dim ConnObj As Object

Dim rsObj As Object

Dim binObj As Object

Dim SavePath As String

Dim sql As String

On Error GoTo ExportBlobs_Error

Set ConnObj = CreateObject("ADODB.Connection")

Set rsObj = CreateObject("ADODB.Recordset")

Set binObj = CreateObject("ADODB.Stream")

ConnObj.ConnectionString = "Provider=SQLOLEDB;Persist Security Info=True;User ID=yourusername;Password=yourpassword;Initial Catalog=yourdatabase;Network Library=dbmssocn; Data Source=yourserver;"

ConnObj.CursorLocation = adUseClient

ConnObj.Open

sql = "SELECT MODULENAME,MODULEDATA FROM BINARYMODULES"

SavePath = "C:\images"

Set rsObj = glbcn.Execute(sql)

Do While Not rsObj.eof

Set binObj = CreateObject("ADODB.Stream")

binObj.Type = adTypeBinary

binObj.Open

binObj.Write rsObj!MODULEDATA

binObj.SaveToFile SavePath & "\" & rsObj!MODULENAME, adSaveCreateOverWrite

binObj.Close

Set binObj = Nothing

rsObj.MoveNext

Loop

On Error GoTo 0

Exit Function

ExportBlobs_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExportBlobs of Form frmActDet"

End Function

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!