Solved

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

Posted on 2009-03-30
9
5,407 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 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

734 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