Easy SQL Script for handling SQL Image Data

Obviously, I’m not a SQL expert and I was focusing on WPF, Silverlight and patterns during last two years. But recently, I got an opportunity to wear multiple hats in my new job and SQL is the one of the hats that I’m wearing now. :)

Okay. Let’s talk about this post.. This post is about how to read the SQL image data, save it as an image file and how to update the existing image data with new image file from SQL Query Panel.

This is the screenshot of one of our games. As you can see in this screenshot, there are some images that we are using in our Silverlight game. Those image comes from database.

I thought that SQL 2008 R2 Management Studio would have something that allows us to view or change the existing images (binary) but Unfortunately, it has nothing that you can view the images. (Please take a look at the screenshot below. This is how you will see the image data in SQL 2008 R2 Management Studio. very powerful tool, isn’t it? :) )

I looked for open source or free tool that I can view the image from SQL database and save it as a file. I found a few trail tools but none of them are good enough. I decided to do some¬†experiments on SQL script and here is something that I come up with.. I’m not sure whether this is a good solution for you guys or not but it’s good solution that I can live with for now. If you have any good solution that you are using in your work then please feel free to let me know..

Let’s take a look at the script.

Enabling Ole Automation Procedures in SQL

Before running the script, you need to enable “Ole Automation Procedures” by using the script below. Note that you need to have an administrative right to run the script.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Once you enable the option then you should be able to read the image by using Ole and save it as a file.

Reading Image Data and Save it as a image file

You need to set the path where you want the generated image to be saved. Did you notice that I mentioned the file extension in @filePath? It’s because I know that all images that we are using are .jpg but it would be great if we can write something that can detect the image format from the binary data. If you have the script please share with me. ^^

DECLARE @objStream INT
DECLARE @imageBinary VARBINARY(MAX)
SET @imageBinary = (SELECT imagedata FROM dbo.tblDefaultImages WHERE imageid = 121)
DECLARE @filePath VARCHAR(8000)
SET @filePath = 'C:\\MS\\r.jpg'
EXEC sp_OACreate 'ADODB.Stream', @objStream OUTPUT
EXEC sp_OASetProperty @objStream, 'Type', 1
EXEC sp_OAMethod @objStream, 'Open'
EXEC sp_OAMethod @objStream, 'Write', NULL, @imageBinary
EXEC sp_OAMethod @objStream, 'SaveToFile', NULL,@filePath, 2
EXEC sp_OAMethod @objStream, 'Close'
EXEC sp_OADestroy @objStream

Updating the image

Here is the script for updating the existing binary data with new image file. I didn’t use Ole here because OpenRowSet is very easy to use and much shorter..

UPDATE [YourDatabase].[dbo].[tblDefaultImages]
SET imagedata =  (SELECT * FROM
OPENROWSET(BULK N'E:\\FTPRoot\\MS\\rhyme\\rhymeObjects\\wine.png', SINGLE_BLOB)
AS Document)
WHERE imageid = 127

Okay. That’s.. What do you think?

6 thoughts on “Easy SQL Script for handling SQL Image Data

  1. Hey Mike,

    Thx for sharing your knowledge. I’m actually looking for sample to save/retireve image with silverlight. In your example, it seems your’re front end is a silverlight app but I don’t see any mentioning about xaml or code-behind. Is your way a new approach whcih can accomplish save/retieve operation to/from sql?

  2. Michael,
    Excellent post.

    About 2 years ago, I created an ASP.NET http handler that dinamicaly resized images, for saving the client and server bandwitdh. Later, on another project, I had to work with images stored at the database (just like you’re showing here) and I updated my handler to become extensible and enable me to serve images from SQL Server by simply inheriting one class and implementing 5 methods. The cool thing about this is that I didn’t have to keep files in sync with the database and all my images now had their own url.

    The control deals with cache, resize logic and image substitution when there is an error or the original is not found (good for a products page, for example). The source code is available at http://cnzklibrary.codeplex.com, along with some other helper libraries I’m developing for my daily use. I need to update the release (since it is from a version older than the one in the source code, but the source is updated) and I also intend to publish a sample extension that works with images stored in a database, but you can take a look now and create your own extension, if you want.

    Best regards,
    Kelps

  3. hi Michael ,
    I perform your sql script and run ok. so i try the following code like your style write myself sql.

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go

    ALTER proc [dbo].[Sp_ExportDB]
    @path nvarchar(1000)
    as

    declare @fname nvarchar(250)
    declare @password varchar(20)
    declare @s_user sysname
    declare @s_password varchar(50)
    set @fname = replace(replace(replace(CONVERT(varchar, getdate(), 120 ),’-‘,”),’ ‘,”),’:’,”)
    SET @fname=’tnf’+@fname+’.mdb’
    –set @password= ‘ICCARD’
    set @password= ”
    set @S_User= ‘SA’
    set @s_Password= ‘123456’

    set nocount on
    declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
    declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)
    declare @tb_exists bit
    declare @temp nvarchar(100)
    if right(@path,1) ‘\’ set @path=@path+ ‘\’
    set @sql=@path+@fname
    create table #tb(a bit,b bit,c bit)
    insert into #tb exec master..xp_fileexist @sql

    –create db
    select @sql=@path+@fname,@constr= ‘Provider=Microsoft.Jet.OLEDB.4.0;Data Source=’+@sql+case isnull(@password, ”) when ” then ” else ‘;Jet OLEDB:Database Password=’+@password end

    set @tb_exists=0
    if exists(select 1 from #tb where a=1)
    BEGIN
    set @temp= ‘del ‘+@sql
    exec master..xp_cmdshell @temp
    end

    exec @err=sp_oacreate ‘ADOX.Catalog’,@obj out
    if @err 0 goto lberr

    exec @err=sp_oamethod @obj, ‘Create’,@out out,@constr
    if @err 0 goto lberr

    EXEC sp_OAMethod @obj, ‘Close’
    EXEC sp_OADestroy @obj
    –connection db
    exec @err=sp_oacreate ‘adodb.connection’,@obj out
    if @err 0 goto lberr

    exec @err=sp_oamethod @obj, ‘open’,null,@constr
    if @err 0 goto lberr

    set @tb_exists=0
    –Export AR_Head Table
    set @sql=case
    when @tb_exists=0
    then ‘SELECT * into [AR_Head] ‘
    else ‘insert into [AR_Head] select * ‘
    end+ ‘ FROM [ODBC;Driver=SQL Server;Server=’+@@servername
    + ‘;Uid=’+@s_user+ ‘;Pwd=’+isnull(@s_password, ”)
    + ‘;database=’+db_name()+ ‘].[AR_Head]’

    exec @err=sp_oamethod @obj,’execute’,@out out,@sql
    if @err 0 goto lberr

    EXEC sp_OAMethod @obj, ‘Close’

    exec @err=sp_oadestroy @obj
    if @err 0 goto lberr
    return 0

    lberr:
    exec sp_oageterrorinfo 0,@src out,@desc out
    if @obj 0 exec @err=sp_oadestroy @obj

    lbexit:
    select cast(@err as varbinary(4)) as errno,@src as errsource,@desc as errdesc

    select @sql,@constr,@fdlist
    drop table #tb
    return 1

    –exec Sp_ExportDB ‘e:\mdbfile’

    This code be ok.but the generated the mdb file be locked . and generated the other ldb file.
    that’s why?Thank you

  4. I’ve been using these codes for extracting files. XLS but when I go to the folder you indicated that you save the file does not appear.

    That’s what happens friends?

  5. Thanks for this – very new at SQL and surprised when not able to enter image data directly into a cell. Your last sample give me a method to prepare my database. Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *