September 21, 2011 at 11:41 am
SQLServer 2008.
Problem: I have a table populated by an external system which stores image data in a varbinary column. I need to create a trigger on the table so that on an insert to the table, the trigger fires and picks up the newly inserted record and extracts the varbinary data and outputs it to a physical image file with a name composed of the recordID + '.JPG' (or whatever graphic format is used) on the hard disk then I can write a row to another table with the filename. I don't mind if the generated file is a JPG or GIF or PNG although I would prefer it not to be a BMP due to the size of these.
Creating the trigger is no problem - the issue I am having is extracting the data from varbinary column and creating the graphic file.
I've tried googling for this and I can't find a complete solution anywhere. Ideally this needs to run on the SQLServer within a trigger so that it is fully automated.
Can anyone help me with this?
Many thanks
Charlotte CB
September 21, 2011 at 12:33 pm
Charlottecb (9/21/2011)
SQLServer 2008.Problem: I have a table populated by an external system which stores image data in a varbinary column. I need to create a trigger on the table so that on an insert to the table, the trigger fires and picks up the newly inserted record and extracts the varbinary data and outputs it to a physical image file with a name composed of the recordID + '.JPG' (or whatever graphic format is used) on the hard disk then I can write a row to another table with the filename. I don't mind if the generated file is a JPG or GIF or PNG although I would prefer it not to be a BMP due to the size of these.
Creating the trigger is no problem - the issue I am having is extracting the data from varbinary column and creating the graphic file.
I've tried googling for this and I can't find a complete solution anywhere. Ideally this needs to run on the SQLServer within a trigger so that it is fully automated.
Can anyone help me with this?
Many thanks
Charlotte CB
Do you know which format it is being sent in as? Regardless, SSIS is the tool to use. You should be able to create an SSIS package to pull your data and create you image file. Then your trigger just needs to call the package. This seems like an extremely strange requirement. You put an image in the database and then immediately write it disc. Can you just skip over putting the image in the database in the first place?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 21, 2011 at 1:11 pm
is the original file name saved in the row of data with the ID and the varbinary data? if you don't have the filename ,(which implies the image type),or at least the original extension, it's going to be a lot harder, and not something that can be done in SQL; to determine a graphics type , you have to do some fancy stuff in aprogramming language.,
Lowell
September 21, 2011 at 1:22 pm
here's some .NET code i posted from a different post on the same issue;
you can use it as a model if you have the file name and binary data together, ie Whatever.pdf, or myprogram.exe, or myimage.jpg...
Private Sub btnBlobsToDisk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBlobsToDisk.Click
'--Imports System.IO
'--Imports System.Data.SqlClient
'--this works whether the datatype for is IMAGE or VARBINARY(max)
Dim sqlcmd As String = "SELECT TOP 3 FileName,ImageData From BatchOfBlobs;"
Dim mySqlConnectionFormat As String = "data source={0};initial catalog={1};user id={2};password={3};Trusted_Connection=False;Connect Timeout=600;Workstation ID=GhostInTheMachine;Application Name=HaxxorPadPlusPlus;"
Dim MyConn As New SqlConnection
MyConn.ConnectionString = String.Format(mySqlConnectionFormat, "DEV223", "SandBox", "Noobie", "NotARealPassword")
MyConn.Open()
'now lets get a commadn object
Dim mySqlCommand As New SqlCommand
mySqlCommand.Connection = MyConn
mySqlCommand.CommandTimeout = 600
mySqlCommand.CommandType = CommandType.Text
mySqlCommand.CommandText = sqlcmd
Dim myDataReader As SqlDataReader
myDataReader = mySqlCommand.ExecuteReader
While myDataReader.Read
Try
Dim path As String = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)
Dim imageInBytes As Byte() = myDataReader("ImageData")
Dim memoryStream As System.IO.Stream = New System.IO.MemoryStream(imageInBytes, True)
Dim image As New System.IO.BinaryWriter(File.Open(path & "\" & myDataReader("FileName"), FileMode.Create))
'%APPDATA% variable like "C:\Users\Lowell\AppData\Roaming"
image.Write(imageInBytes)
image.Flush()
image.Close()
Catch ex As Exception
Console.WriteLine(ex.StackTrace)
End Try
End While
End Sub
Lowell
September 21, 2011 at 2:00 pm
Hi, thanks for the response. I have no control over the original dumping of the varbinary data - it' done by an external system which I have no control over.
September 21, 2011 at 2:03 pm
Charlottecb (9/21/2011)
Hi, thanks for the response. I have no control over the original dumping of the varbinary data - it' done by an external system which I have no control over.
But as Lowell asked, do you have the filename (or the file extension)? That will make a TON of difference. without it you are going to have to do a ton of work to determine what type of file it is.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 21, 2011 at 2:03 pm
I don't have the filename with it, I will generate the filename. The varbinary data will always be in the same format. I am trying to acertain what image format that is, but if I can't get that info then I will just use a process of trial and error with different filename extensions until I figure it out.
September 21, 2011 at 2:06 pm
Lowell (9/21/2011)
here's some .NET code i posted from a different post on the same issue;you can use it as a model if you have the file name and binary data together, ie Whatever.pdf, or myprogram.exe, or myimage.jpg...
Private Sub btnBlobsToDisk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBlobsToDisk.Click
'--Imports System.IO
'--Imports System.Data.SqlClient
'--this works whether the datatype for is IMAGE or VARBINARY(max)
Dim sqlcmd As String = "SELECT TOP 3 FileName,ImageData From BatchOfBlobs;"
Dim mySqlConnectionFormat As String = "data source={0};initial catalog={1};user id={2};password={3};Trusted_Connection=False;Connect Timeout=600;Workstation ID=GhostInTheMachine;Application Name=HaxxorPadPlusPlus;"
Dim MyConn As New SqlConnection
MyConn.ConnectionString = String.Format(mySqlConnectionFormat, "DEV223", "SandBox", "Noobie", "NotARealPassword")
MyConn.Open()
'now lets get a commadn object
Dim mySqlCommand As New SqlCommand
mySqlCommand.Connection = MyConn
mySqlCommand.CommandTimeout = 600
mySqlCommand.CommandType = CommandType.Text
mySqlCommand.CommandText = sqlcmd
Dim myDataReader As SqlDataReader
myDataReader = mySqlCommand.ExecuteReader
While myDataReader.Read
Try
Dim path As String = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)
Dim imageInBytes As Byte() = myDataReader("ImageData")
Dim memoryStream As System.IO.Stream = New System.IO.MemoryStream(imageInBytes, True)
Dim image As New System.IO.BinaryWriter(File.Open(path & "\" & myDataReader("FileName"), FileMode.Create))
'%APPDATA% variable like "C:\Users\Lowell\AppData\Roaming"
image.Write(imageInBytes)
image.Flush()
image.Close()
Catch ex As Exception
Console.WriteLine(ex.StackTrace)
End Try
End While
End Sub
Many thanks for posting your sample code - it's very kind of you. Ideally I would like to do this via SQL Server only without involving dot net so I can fire it via a table trigger, but if it's not possible then I'll implement a dot net solution using your code.:-D
September 21, 2011 at 2:08 pm
Well then as Lowell said you can't do this with sql alone. This is more Lowell's expertise than mine but that is going to be pretty painful. You are going to have to try to determine what filetype it is by the contents. YUCK!!! What are you going to do when they ask for a new filetype? Seems that without the full original filename or at least the filetype you are in for a long and possibly impossible battle. This could prove to be a very interesting thread to follow to see what others have to say.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 21, 2011 at 2:16 pm
Sean Lange (9/21/2011)
Well then as Lowell said you can't do this with sql alone. This is more Lowell's expertise than mine but that is going to be pretty painful. You are going to have to try to determine what filetype it is by the contents. YUCK!!! What are you going to do when they ask for a new filetype? Seems that without the full original filename or at least the filetype you are in for a long and possibly impossible battle. This could prove to be a very interesting thread to follow to see what others have to say.
I'm probably being very simplistic \ ignorant in my outlook here, so please bare with me as I've never had to do anything like this before... Why can I just write the output to a file with a JPG format then try manually opening the file using a graphics package. If the file opens and renders correctly then it was a JPG file so I can proceed on that basis. If not then I adjust my code to create a file with a GIF extension then repeat the process until I get the correct format.
Once I get it to open I'm good to go as I know the format gerenated by the external system won't change.
Thanks.
September 21, 2011 at 2:21 pm
Charlottecb (9/21/2011)
I'm probably being very simplistic \ ignorant in my outlook here, so please bare with me as I've never had to do anything like this before... Why can I just write the output to a file with a JPG format then try manually opening the file using a graphics package. If the file opens and renders correctly then it was a JPG file so I can proceed on that basis. If not then I adjust my code to create a file with a GIF extension then repeat the process until I get the correct format.Once I get it to open I'm good to go as I know the format gerenated by the external system won't change.
Thanks.
how many images are we talking about? if it's more than say, a couple hundred, that's a lot of manual work to review, fiddle with extensions, and fiddle again.
after you export them out, woudl you save the extention type back in the data to resolve the issue in the future?
also, it might depend on your viewer...some "smart" viewers like Paint Shop pro, PhotoShop, ACDSee will still render an image as visible, even if it's the wrong extension, by using the same Graphic logic we were infering...looking at the binary data for headers and showing it anyway.
in that case, it renders perfectly for one person, but not the other.
Lowell
September 21, 2011 at 2:31 pm
LOL well I guess that would work too assuming they only send one type of file. I guess I am more used to users who send attachments of who knows what file type. 😛 Why keep it simple when you can over complicate it?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 21, 2011 at 2:37 pm
untested, but syntax wise in vb.NET it looks like i can take any binary data, try to stick it in a "System.Drawing.Image", and if there's no error, do a Save As...jpg or whatever type i want.
so it might be just as easy to convert all images (assuming the are images as in pictures, and not images as in raw binary data) to one file type (jpg or png for example)
Lowell
September 21, 2011 at 2:42 pm
Lowell (9/21/2011)
Charlottecb (9/21/2011)
I'm probably being very simplistic \ ignorant in my outlook here, so please bare with me as I've never had to do anything like this before... Why can I just write the output to a file with a JPG format then try manually opening the file using a graphics package. If the file opens and renders correctly then it was a JPG file so I can proceed on that basis. If not then I adjust my code to create a file with a GIF extension then repeat the process until I get the correct format.Once I get it to open I'm good to go as I know the format gerenated by the external system won't change.
Thanks.
how many images are we talking about? if it's more than say, a couple hundred, that's a lot of manual work to review, fiddle with extensions, and fiddle again.
after you export them out, woudl you save the extention type back in the data to resolve the issue in the future?
also, it might depend on your viewer...some "smart" viewers like Paint Shop pro, PhotoShop, ACDSee will still render an image as visible, even if it's the wrong extension, by using the same Graphic logic we were infering...looking at the binary data for headers and showing it anyway.
in that case, it renders perfectly for one person, but not the other.
Don't balk at this number - somewhere between 75 and 150 thousand images. Howerver... These will take place over a 5 month period, probably around a couple of hundred images a day, some days there may be none [weekends] other days there could be up to 500 or so. These are images of work taken by the operative.
I'll give a bit of further info of what needs to happen...
The job is done and the operative records in a PDA the details then takes one or more pictures as well as the client's signature as an image. These are then sent back and the external system dumps them as varbinary in their table and replicates them to our table. I need to pick these up and convert them to actual files then delete them from the original table as I will never want to view them as varbinary or do anything with them in this format (I also don't want my DB growing to an unmanagable size). My own dot net app which does various things will then make the images available for viewing via a hyperlink in a grid column. The user will click the hyperlink to load the physical file for viewing.
The reason I wanted it done with a SQL trigger is that as soon as the images was inserted into the db I could convert it and have it immediatly available for viewing via the front end. As a worst case scenario, I could write a Windows Service in dot net to poll the table for any new rows, convert them via dot net then clear the rows form the original table. The problem with this is that there is a delay potentially as I need to wait for the service to poll and it means the server is doing a load of unneccesary work polling when nothing may have been added. Any suggestions on this - am I completely mad::-)
September 21, 2011 at 2:52 pm
I went down the rabbit hole pretty deep investgating this; kind of interesting.
this code project example compares teh first 10 bytes to determine the image file type.
if you can add a column to your table to hold the File Extension, you might be able to do it all in SQL , fixing the extension problem, saving blobs to dis(requires xp_cmdshell)
and finally setting up a process that fixes new rows where the extensionType is NULL.
http://www.codeproject.com/KB/files/ImageClassifier.aspx
you could update the new column based on a CASE statement comparing the first ten bytes of the varbinary to the known values.
Lowell
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply