?
Solved

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

Posted on 2009-03-30
9
Medium Priority
?
5,502 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 2000 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
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
 

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

718 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