Solved

How can I export a SQL Server BLOB field to a standard Windows JPG file?

Posted on 2009-03-30
9
5,277 Views
Last Modified: 2013-11-25
Hello!

I'm trying to determine the feasiblitly of processing a SQL Server table through MS Access, and for each SQL Server record, converting a BLOB field in the record to an individual JPG file in Windows.  For example, if 10 SQL Server records were processed, then 10 JPG files would be produced in a Windows directory.

I'm assuming the use of MS Access, because Access can access SQL Server (thru ODBC) for input, and a Windows directory for output, simultaneously.

So, first of all, is this possible using MS Access?  If so, what Access facility do I need to use?

Thanks!

GLFord


0
Comment
Question by:GLFord
  • 4
  • 3
  • 2
9 Comments
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 500 total points
ID: 24020123
see these links:
     http://www.lebans.com/loadjpeggif.htm
     http://www.lebans.com/loadsavejpeg.htm

for general reference on image handling in ms access:
     http://www.jamiessoftware.tk/articles/handlingimages.html
               
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24020141
I would suggest you to do converting blob to JPG from SQL Server itself. you can use following code in SP or anywhere else to do it right from SQL Server



-- Using TextCopy in MSSQL$instancename\Binn for retrieving image.
 

--0. xp_cmdshell is turned on from sp_configure

sys.sp_configure xp_cmdshell, 1

RECONFIGURE;
 

GO

-- 1 Declaration

DECLARE @sql VARCHAR(500),

@server sysname,

@user sysname,

@pwd sysname,

@db sysname,

@table sysname,

@column sysname,

@whereclause VARCHAR(500),

@filename VARCHAR(100),

@filepath VARCHAR(500)
 

--2. Config

SET @server = @@SERVERNAME

SET @user =  input login here

SET @pwd =  and pwd

SET @db = DB_NAME()

SET @table = ImageTest

SET @column = Image

SET @whereclause = WHERE ID = 13

SET @filename = ImageFromSQL.jpg

SET @filepath = C:\tmp\
 

--3. Set textcopy path from path environment variable(manually)

--And set sql string

SET @sql= TextCopy  +

 /S  + @server +

 /U  + @user +

 /P  + @pwd +

 /D  + @db +

 /T  + @table +

 /C  + @column +

 /W  + @whereclause +

 /F  + @filepath + @filename +

 /O  +

 /Z   for debuging
 

EXEC master..xp_cmdshell @sql --,no_output if wanna see the debug

GO

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24020152
if you wish to do it from C# code, than do follow the link below

http://www.sqlhub.com/2009/03/image-store-in-sql-server-2005-database.html
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24020242
sorry, in my script in above post, I am seeing some junk character, here is the copy of the same.



-- Using TextCopy in MSSQL$instancename\Binn for retrieving image.
 

--0. xp_cmdshell is turned on from sp_configure

sys.sp_configure xp_cmdshell, 1

RECONFIGURE;
 

GO

-- 1 Declaration

DECLARE @sql VARCHAR(500),

@server sysname,

@user sysname,

@pwd sysname,

@db sysname,

@table sysname,

@column sysname,

@whereclause VARCHAR(500),

@filename VARCHAR(100),

@filepath VARCHAR(500)
 

--2. Config

SET @server = @@SERVERNAME

SET @user = -- input login here

SET @pwd = -- and pwd

SET @db = DB_NAME()

SET @table = 'ImageTest'

SET @column = 'Image'

SET @whereclause = 'WHERE ID = 13'

SET @filename = 'ImageFromSQL.jpg'

SET @filepath = 'C:\tmp\'
 

--3. Set textcopy path from path environment variable(manually)

--And set sql string

SET @sql= 'TextCopy ' +

' /S ' + @server +

' /U ' + @user +

' /P ' + @pwd +

' /D ' + @db +

' /T ' + @table +

' /C ' + @column +

' /W ' + @whereclause +

' /F ' + @filepath + @filename +

' /O ' +

' /Z ' -- for debuging
 

EXEC master..xp_cmdshell @sql --,no_output if wanna see the debug

GO

Open in new window

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:GLFord
ID: 24032689
Hi all,

Thanks so much for these ideas.  It'll be a week or so before I am able to work further on this issue.  I'll send an update at that time.

Thanks!
0
 

Author Comment

by:GLFord
ID: 24061869
puppydogbuddy:

Thanks for those links!  I found Leban's A2KLoadSaveJpeg Access application very interesting.  The main difference between this application and my need, is that I would like to process a table that has a BLOB field in each row, and automatically (in batch) have the program read each row, convert the BLOB field to a JPG and write out the JPG.  (The name for each JPG is contained as a field in the same row as the BLOB).  So, in other words, I need the process to run "lights out", once an operator clicks a button on an Access form to kick it off.  Is it easy to convert a BLOB to JPG directly, without going through an image control in Access?

Thanks!
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24062233
GLFord
AFAIK, with BLOB you can only input/output binary "chunks" (see link below).  Other than Access 2007 (stores jpg directly) or Leban's method, compessed >>>storage<<< for OLE not available in Access.  

see :  http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q103257&
Excerpt from prior posted link to Jamie's software:
   On a final note, of the three techniques discussed, I prefer to use the last technique as the images are neatly stored away inside the database file and we do not have to suffer the bloat associated with the first. For a working example of storing images as binary large object bitmaps (BLOB) in an OLE field, please feel free to download and evaluate my Image Albums application. A free example of storing files in this way using DAO with viewable source code can be found by downloading the getBinary zip file, an Access 97 file that can also be converted to Access 2000 or 2002 depending on which version you are running.

The final technique can be tricky to set up but has the advantage of storing images inside the database, meaning that password protection is possible and also avoids the bloat associated with the first technique, as the images are stored reflecting their true size. For this technique I would again recommend using the "file open" dialog, allowing the user to browse to an image. We can use either DAO or ADO (in Access 2000 or 2002) to grab the image file and read it into the OLE field in chunks. To display the image we have to extract it from the database to a temporary file and like the second technique, use an image control to display it by setting the control's "picture" property to the path of the temporary file. Once we are finished with the temporary file we can use the VBA "kill" command to delete it. Unfortunately, as with the second technique, the same problem with Access continuous forms exists. If displaying the images using single forms is not adequate you could take the time to create a customized form to display more than one individual image, although unfortunately this task is made awkward by the fact that Access does not support control arrays. In addition, this technique also incurs a marginal overhead when compared to the second, as the image files first have to be extracted from the database before we can view them.
0
 

Author Comment

by:GLFord
ID: 24119577
I'm making some progress... will update this question next week with the results.

Thanks to all!

0
 

Author Comment

by:GLFord
ID: 24141738
I used the last link in puppydogbuddy's first posting.  Therefore I have awarded the points to puppydogbuddy.  The link itself did not provide the answer, but I was able to follow links from this page to find the solution here:

http://www.ammara.com/access_image_faq/read_write_blob.html

Using MS Access, I used the first Access function on this page successfully, to be able to convert SQL-Server BLOBs to JPGs, as per the code snippet below.  You pass the Windows name to be given to the JPG as the first argument, and then the BLOB field as the second argument.  I converted a SQL Server table with 400 rows (400 BLOBS), into 400 individual JPGs, each with an individual name based on the key field of each row.  Perfect!  Thanks to all who participated in this question!

GLFord


Public Function BlobToFile(strFile As String, ByRef Field As Object) As Long

    On Error GoTo BlobToFileError
 

    Dim nFileNum As Integer

    Dim abytData() As Byte

    BlobToFile = 0

    nFileNum = FreeFile

    Open strFile For Binary Access Write As nFileNum

    abytData = Field

    Put #nFileNum, , abytData

    BlobToFile = LOF(nFileNum)
 

BlobToFileExit:

    If nFileNum > 0 Then Close nFileNum

    Exit Function
 

BlobToFileError:

    MsgBox "Error " & err.Number & ": " & err.Description, vbCritical, _

           "Error writing file in BlobToFile"

    BlobToFile = 0

    Resume BlobToFileExit
 

End Function

Open in new window

0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now