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?
