SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help required to extra image from varbinary column and create image on file system


Help required to extra image from varbinary column and create image on file system

Author
Message
Charlottecb
Charlottecb
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 226
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



Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26273 Visits: 17553
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.

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)
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28399 Visits: 39963
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28399 Visits: 39963
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Charlottecb
Charlottecb
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 226
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.



Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26273 Visits: 17553
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.

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)
Charlottecb
Charlottecb
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 226
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.



Charlottecb
Charlottecb
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 226
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



Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26273 Visits: 17553
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.

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)
Charlottecb
Charlottecb
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 226
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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search