Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

image insertion into database Expand / Collapse
Author
Message
Posted Friday, June 14, 2013 1:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 12:04 AM
Points: 83, Visits: 500
how to add and retrieve a image to and from databse table using open rowset(exaplain)?
is there any other ways of doing image insertion and retrieval?
Thanks and Regards
Post #1463411
Posted Friday, June 14, 2013 5:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 14,541, Visits: 38,381
gurjer48 (6/14/2013)
how to add and retrieve a image to and from databse table using open rowset(exaplain)?
is there any other ways of doing image insertion and retrieval?
Thanks and Regards


this post has an example of how to Insert, from disk, a image , both via openrowset and also a vb.net example:
http://www.sqlservercentral.com/Forums/FindPost1463248.aspx

If you are going to do something like that regularly, and you feel you have to do it via TSQL and not a programming language/applciaiton, i'd suggest adding some CLR functions to make it much easier; Elliot Witlow has a very nice implementation, with source code, here:

--Elliot Whitlow's project! awesome
http://nclsqlclrfile.codeplex.com/

and using his project, here 's code examples of reading and writing:
--MFGetFileImage
-- Parameters: @FilePath, @FileName
-- purpose: given a path and filename, return the varbinary of the file to store in the database.
-- usage:
CREATE TABLE myImages(id int,filename varchar(200),rawimage varbinary(max) )
INSERT INTO myImages(id,filename,rawimage)
SELECT 1,'fedora_spinner.gif',dbo.MFGetFileImage('C:\Data\','fedora_spinner.gif' )


--MSPSaveFileImage
-- Parameters: @FilePath,@FileName,@FileBytes
-- purpose: given an varbinary image column in a table, write that image to disk
-- usage:
--assumes table and the file from the example above for dbo.MFGetFileImage exists already.
declare @myfile varbinary(max)
SELECT @myfile = rawimage FROM myImages WHERE id = 1
EXEC dbo.MSPSaveFileImage 'C:\Data','spinning.gif',@myfile




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!
Post #1463494
Posted Friday, June 14, 2013 6:29 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 9,934, Visits: 9,323
I've always done this using a .NET application and found it far easier than trying to force it with T-SQL. The first big hurdle in doing it with T-SQL is that the image has to be accessible in the file system on the SQL Server. This makes is generally not very useful for user applications.

That being said, Lowell, thank you for the link. It's definitely something I'm interested in and will check out.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1463523
Posted Friday, June 14, 2013 6:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 14,541, Visits: 38,381
Ed Wagner (6/14/2013)
I've always done this using a .NET application and found it far easier than trying to force it with T-SQL. The first big hurdle in doing it with T-SQL is that the image has to be accessible in the file system on the SQL Server. This makes is generally not very useful for user applications.

That being said, Lowell, thank you for the link. It's definitely something I'm interested in and will check out.


I've done it the same way Ed; typically doing it in /adding that kind of functionality to an application or website.

I've had a few one-off issues, like "export all these documents stored in the database to disk" for various biz reasons, and a simple loop and doing it in TSQL makes sense in those cases, but the main reason you identified: not useful to end users is a rock solid reason to not do it in TSQL. the above is nice to have in your toolbox, but not required.


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!
Post #1463533
Posted Friday, June 14, 2013 6:57 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 9,934, Visits: 9,323
Lowell (6/14/2013)
Ed Wagner (6/14/2013)
I've always done this using a .NET application and found it far easier than trying to force it with T-SQL. The first big hurdle in doing it with T-SQL is that the image has to be accessible in the file system on the SQL Server. This makes is generally not very useful for user applications.

That being said, Lowell, thank you for the link. It's definitely something I'm interested in and will check out.


I've done it the same way Ed; typically doing it in /adding that kind of functionality to an application or website.

I've had a few one-off issues, like "export all these documents stored in the database to disk" for various biz reasons, and a simple loop and doing it in TSQL makes sense in those cases, but the main reason you identified: not useful to end users is a rock solid reason to not do it in TSQL. the above is nice to have in your toolbox, but not required.


Definitely - the one-off requests is exactly what I was thinking of when I read your post on the CLR library. Thank you for the link.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1463538
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse