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

Question

Post reply

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

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Charlottecb Hall of Fame Points: 3932 More actions · Answer 1

Lowell, Thanks for your continued responses.

After doing a bit of googling, this is what I've put into an insert trigger on the table...

DECLARE @folderPath VARCHAR(MAX)

SET @folderPath = 'C:\' -- Need to get full path from System Settings table [For Live System] ... --

DECLARE @objStream INT

DECLARE @imageBinary VARBINARY(MAX)

DECLARE @filePath VARCHAR(8000)

DECLARE @ID INT

SELECT @imageBinary = A.[capturedImage],

@filePath = @folderPath + CAST(A.[id] AS VARCHAR(MAX)) + '_' + replace(replace(replace(replace(convert(varchar,getdate(),121),'-',''),':',''),'.',''),' ','') + '.JPG',

@ID = A.[id]

FROM [Inserted] A

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

-- Clear row from DB now it's been extracted from DB --

DELETE

FROM[tblImages]

WHERE id = @ID

Not sure if this will work inside a trigger as I've not finished it yet. I'm hoping if it generates a file and it doesn't open then I can adjust the filename with a different extension then try again - I could be barking up the wrong tree though.

If I don't get anywhere then I'll investigate further the link to the Code Project sample you sent me.

Thanks.

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

That looks like a good start but you will have to add some of looping to handle multiple line inserts. If Lowell doesn't have time I can give you a hand tomorrow.

_______________________________________________________________

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/

Charlottecb Hall of Fame Points: 3932 More actions · Answer 3

Sean Lange (9/21/2011)
That looks like a good start but you will have to add some of looping to handle multiple line inserts. If Lowell doesn't have time I can give you a hand tomorrow.

Thanks Sean. I would asume I will need to put them into a dreaded cursor and loop round them...

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Charlottecb (9/22/2011)
Sean Lange (9/21/2011)
That looks like a good start but you will have to add some of looping to handle multiple line inserts. If Lowell doesn't have time I can give you a hand tomorrow.
Thanks Sean. I would asume I will need to put them into a dreaded cursor and loop round them...

Most likely. And a cursor in a trigger is probably the worst place to have to put one for performance. 🙁 Maybe it would make sense to create a CLR sproc to handle the RBAR stuff? Then you could just call that sproc from your trigger. Not certain that would help but it might be worth looking into.

_______________________________________________________________

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/

Lowell SSC Guru Points: 323518 More actions · Answer 5

ok check this out please.

this has worked correctly on all 5 of my tables which happen to have picutres/images in either image datatypes or varbinary(max) datatypes.

i could correctly guess the datatype based on my limited samples..i only have maybe 100 images in all those tables, and they were pretty much 95% jpgs, two bmps,a few gifs, and one tif.

i also found one bmp file that ahs a gif extension.

please test drive this and see if this allows you to guess the file extension based ont eh image data:

with MYSampleData

AS

(

--change this to sample one of your tables with image data!

SELECT TOP 500

PrimaryKeyID,

OtherColumns,

convert(varbinary(10),RawImageorVarBinaryColumn) As TheBits

from YourTable

)

SELECT

MYSampleData.*,

CASE

WHEN convert(varbinary(2),TheBits) = 0x424D --example .bmp||42-4D-66-03-00-00-00-00-00-00

THEN '.bmp'

WHEN convert(varbinary(3),TheBits) = 0x474946 --example .gif||47-49-46-38-39-61-0D-00-14-00

THEN '.gif'

WHEN convert(varbinary(4),TheBits) = 0x00000100 --example .ico||00-00-01-00-09-00-10-10-00-00

THEN '.ico'

WHEN convert(varbinary(3),TheBits) = 0xFFD8FF --example .jpeg||FF-D8-FF-E0-00-10-4A-46-49-46

THEN '.jpg'

WHEN convert(varbinary(2),TheBits) = 0x4949 --example .tif||49-49-2A-00-08-00-00-00-13-00 --only 1 image to test agaisnt!

THEN '.tif'

ELSE '.???'

END

FROM MYSampleData

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 Hall of Fame Points: 3932 More actions · Answer 6

Lowell (9/22/2011)
ok check this out please.
this has worked correctly on all 5 of my tables which happen to have picutres/images in either image datatypes or varbinary(max) datatypes.
i could correctly guess the datatype based on my limited samples..i only have maybe 100 images in all those tables, and they were pretty much 95% jpgs, two bmps,a few gifs, and one tif.
i also found one bmp file that ahs a gif extension.
please test drive this and see if this allows you to guess the file extension based ont eh image data:
with MYSampleData
AS
(
--change this to sample one of your tables with image data!
SELECT TOP 500
PrimaryKeyID,
OtherColumns,
convert(varbinary(10),RawImageorVarBinaryColumn) As TheBits
from YourTable
)
SELECT
MYSampleData.*,
CASE
WHEN convert(varbinary(2),TheBits) = 0x424D --example .bmp||42-4D-66-03-00-00-00-00-00-00
THEN '.bmp'
WHEN convert(varbinary(3),TheBits) = 0x474946 --example .gif||47-49-46-38-39-61-0D-00-14-00
THEN '.gif'
WHEN convert(varbinary(4),TheBits) = 0x00000100 --example .ico||00-00-01-00-09-00-10-10-00-00
THEN '.ico'
WHEN convert(varbinary(3),TheBits) = 0xFFD8FF --example .jpeg||FF-D8-FF-E0-00-10-4A-46-49-46
THEN '.jpg'
WHEN convert(varbinary(2),TheBits) = 0x4949 --example .tif||49-49-2A-00-08-00-00-00-13-00 --only 1 image to test agaisnt!
THEN '.bmp'
ELSE '.???'
END
FROM MYSampleData

Many thanks for the code sample, it's great - I've tested it against my very limited test data I have and I am getting Jpg files. I should have a bigger sample data set to test aginst within the next few days.:-D

Charlottecb Hall of Fame Points: 3932 More actions · Answer 7

Sean Lange (9/22/2011)
Charlottecb (9/22/2011)
Sean Lange (9/21/2011)
That looks like a good start but you will have to add some of looping to handle multiple line inserts. If Lowell doesn't have time I can give you a hand tomorrow.
Thanks Sean. I would asume I will need to put them into a dreaded cursor and loop round them...
Most likely. And a cursor in a trigger is probably the worst place to have to put one for performance. 🙁 Maybe it would make sense to create a CLR sproc to handle the RBAR stuff? Then you could just call that sproc from your trigger. Not certain that would help but it might be worth looking into.

Thanks Sean, I'll take a look into that also.:-D

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 8

Can I offer something else..

A trigger to do the work could easily cause unnecessary waits in the application. I would probably use the trigger to write an entry into a service broker queue. This would allow the trigger to return very quickly and processing to continue.

The queue would then be used to trigger a sproc that is then used to write the data to the file system. I noticed the code to determine a file type further up the thread. I like that idea. I can also point you to a SQLCLR object that would let you interact and write the image to the file system without any trouble.

It is at: http://nclsqlclrfile.codeplex.com/[/url]

CEWII

Charlottecb Hall of Fame Points: 3932 More actions · Answer 9

Elliott Whitlow (9/22/2011)
Can I offer something else..
A trigger to do the work could easily cause unnecessary waits in the application. I would probably use the trigger to write an entry into a service broker queue. This would allow the trigger to return very quickly and processing to continue.
The queue would then be used to trigger a sproc that is then used to write the data to the file system. I noticed the code to determine a file type further up the thread. I like that idea. I can also point you to a SQLCLR object that would let you interact and write the image to the file system without any trouble.
It is at: http://nclsqlclrfile.codeplex.com/[/url]
CEWII

Hi, Thanks for the response. I'm not sure speed will be an issue here - the table the trigger is on is not actually used anywhere else in my system. I extract the file to the file system then insert a record with the path to the file into another table which is used by my system. I only need the table with the trigger on it so it will work with the external system. I'll check out the link you provided though.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 10

If you chose to use the code from codeplex and have any trouble let me know, I am the author.

CEWII

Lowell SSC Guru Points: 323518 More actions · Answer 11

Elliott Whitlow (9/22/2011)
If you chose to use the code from codeplex and have any trouble let me know, I am the author.
CEWII

Elliot that is a really nice project, and was very easy to install; i'm playing with it now, although all i did so far was use the fileExists function;

Thank you very much for your efforts on that;

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 Hall of Fame Points: 3932 More actions · Answer 12

Elliott Whitlow (9/22/2011)
If you chose to use the code from codeplex and have any trouble let me know, I am the author.
CEWII

Thanks Elliot:-D

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 13

Lowell (9/22/2011)
Elliott Whitlow (9/22/2011)
If you chose to use the code from codeplex and have any trouble let me know, I am the author.
CEWII
Elliot that is a really nice project, and was very easy to install; i'm playing with it now, although all i did so far was use the fileExists function;
Thank you very much for your efforts on that;

Thanks, some of my favorites are the ones used to read/write BLOBs to the file system and get directory lists. Another favorite is the one that breaks a filename with path down to a filename and a path.

Although I try to avoid using SQL to manage files I have hit many cases where it made more sense than other alternatives..

Enjoy, if you see any issues or suggestions I'd love to hear them.

CEWII

donnaklinton Valued Member Points: 56 More actions · Answer 14

Hello

Here is my code:

using RasterEdge.Imaging.Basic.Core;

using RasterEdge.Imaging.Basic.Codec;

using RasterEdge.Imaging.TIFF;

using RasterEdge.Imaging.Basic;

namespace RE__Test

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void button1_Click(object sender, EventArgs e)

{

int width = 100;

int height = 60;

REImage reImage = new REImage(width, height, ImageMode.RGB888);// Create a image[/url] with specified width, height and color mode; all pixels are set to white

REFile.SaveImageFile(reImage, "c:/reimage.bmp", new BMPEncoder());

REFile.SaveImageFile(reImage, "c:/reimage.png", new PNGEncoder());

REFile.SaveImageFile(reImage, "c:/reimage.jpg", new JPGEncoder());

REFile.SaveImageFile(reImage, "c:/reimage.tif", new TIFEncoder());

}

Sean Lange SSC Guru Points: 286590 More actions · Answer 15

donnaklinton (9/25/2013)
Hello
Here is my code:

This thread is 2 years old and your post looks a LOT like spam to me. Nothing but references to your SDK and a link to the same. 😉

_______________________________________________________________

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/