Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Easy SQL Script for handling SQL Image Data

DZone's Guide to

Easy SQL Script for handling SQL Image Data

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

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.

1 sp_configure 'show advanced options', 1;
2 GO
3 RECONFIGURE;
4 GO
5 sp_configure 'Ole Automation Procedures', 1;
6 GO
7 RECONFIGURE;
8 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. ^^

01 DECLARE @objStream INT
02 DECLARE @imageBinary VARBINARY(MAX)
03 SET @imageBinary = (SELECT imagedata FROM dbo.tblDefaultImages WHERE imageid = 121)
04 DECLARE @filePath VARCHAR(8000)
05 SET @filePath = 'C:\\MS\\r.jpg'
06 EXEC sp_OACreate 'ADODB.Stream', @objStream OUTPUT
07 EXEC sp_OASetProperty @objStream, 'Type', 1
08 EXEC sp_OAMethod @objStream, 'Open'
09 EXEC sp_OAMethod @objStream, 'Write', NULL, @imageBinary
10 EXEC sp_OAMethod @objStream, 'SaveToFile', NULL,@filePath, 2
11 EXEC sp_OAMethod @objStream, 'Close'
12 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..

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

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

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:

Published at DZone with permission of Michael Sync. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}