Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Help required to extra image from varbinary column and create image on file system Expand / Collapse
Author
Message
Posted Wednesday, September 21, 2011 11:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 30, 2014 9:35 AM
Points: 219, Visits: 170
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



Post #1178898
Posted Wednesday, September 21, 2011 12:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,067, Visits: 11,903
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1178924
Posted Wednesday, September 21, 2011 1:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 12,877, Visits: 31,792
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1178946
Posted Wednesday, September 21, 2011 1:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 12,877, Visits: 31,792
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1178952
Posted Wednesday, September 21, 2011 2:00 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 30, 2014 9:35 AM
Points: 219, Visits: 170
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.


Post #1178984
Posted Wednesday, September 21, 2011 2:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,067, Visits: 11,903
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1178986
Posted Wednesday, September 21, 2011 2:03 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 30, 2014 9:35 AM
Points: 219, Visits: 170
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.


Post #1178987
Posted Wednesday, September 21, 2011 2:06 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 30, 2014 9:35 AM
Points: 219, Visits: 170
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.



Post #1178988
Posted Wednesday, September 21, 2011 2:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,067, Visits: 11,903
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1178991
Posted Wednesday, September 21, 2011 2:16 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 30, 2014 9:35 AM
Points: 219, Visits: 170
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.



Post #1178997
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse