Storing Photos in Sql Server 2005 Database

[DiverMarv]

All,

First, I know that it is best to store the photos in a folder, and just store the path to the image in the database. That is what I currently do for all my implementations of this sort of task. But. We have been tasked with creating a sql server report of a photo directory. I am getting hemmed in to do this. So what I will be doing is storing a thumbnail image, very small, in the database. But I cannot find any way to do this. Every example I have tried is failing.

Can someone please give advice on how to do this? For the application platform, I can use either .net or coldfusion (coldfusion preferable, but either will work)

Thanks
Marvin Hoffman
Medkinetics, LLC.

 

[onpnt]

Have you gotten the images into SQL Server yet? Which part are you asking about I guess.

store the images as varbinary and use OPENROWSET to insert them.

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[DiverMarv]

No, I do not have them in the database yet. But I do have a row that they will go into. So I wanted to do an update table instead of insert into table

 

[ESquared]

You probably need to give more examples of what you've tried and why it hasn't worked before anyone can help you.

 

[mrdenny]

First you will want to use an IMAGE datatype not varbinary (unless you are using SQL 2005 in which case you will want to use VARBINARY(MAX)).

When creating the table I would also recommend changing the file group that the blob data will be stored in. This will improve performance overall and give you the advantage of moving the images to cheeper storage and keeping them out of the main tables file group. If you are going to want to do this later you should do it now (even if you have to use the same drive letter for both data and binary data for now). Doing it later will be much more complex and could take hours.

To write the data you will need to use the WRITETEXT statement to load the data into the table. Something like this will do the trick.

create procedure usp_LoadImage
   @Pic image
as
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(pr_info) 
FROM pub_info pr INNER JOIN publishers p
   ON p.pub_id = pr.pub_id 
   AND p.pub_name = 'New Moon Books'
WRITETEXT pub_info.pr_info @ptrval @Pic
go

This sample is a hacked block of code from BOL you can find it in BOL under "image data type, modifying values".

If you are using SQL 2005 then OPENROWSET method is the recommend method to use.

USE AdventureWorks
GO
CREATE TABLE myTable(FileName nvarchar(60),
FileType nvarchar(60), Document varbinary(max))
GO

INSERT INTO myTable(FileName, FileType, Document)
SELECT 'Text1.txt' AS FileName,
'.txt' AS FileType,
* FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document
GO

To find this example browse to "varbinary columns" in the SQL 2005 BOL then click on the "OPENROWSET (Transact-SQL)" link in the document.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ESquared]

Excellent info, mrdenny.

DiverMarv, if you want to use ADO, there are some methods of ADO recordsets that manipulate BLOBs (Binary Large OBjects, what you're dealing with here). I think they are somethign like AppendChunk and WriteChunk, but that's off the top of my head.

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[bboffin]

As you've probably gathered from the above, your initial assumption that it's best to keep the images in file system folders and just store the path in the database is in fact incorrect.

The performance of SQL Server (2000 or 2005) when retrieving this type of data is MUCH better than that achievable via the file system and probably uses less disk space as the disk cluster size is no longer significant. I have an application that stores large numbers of documents in a variety of formats including PDF and JPEG and was surprised at how much faster it ran when I moved the documents into the database itself.

Since most of my documents will be referenced quite infrequently I also chose to put the table in which they are stored in a separate database on the same server and to put this database on a network storage device. This may not be appropriate for your application.

Bob Boffin

 

[DiverMarv]

Wow, I appreciate all the information supplied! And I also appreciate the comment about the feasibility of storing images in database!

Now, mrdenny, I have tried "OPENROWSET BULK" and I get an error:

Here is my particular sql statement:
INSERT INTO dbo.ProviderPhotoJoin
(photo)
SELECT * FROM OPENROWSET(BULK N'c:\Temp\photos\forest.jpg', SINGLE_BLOB) AS photo

And here is the error:
Incorrect syntax near the keyword 'BULK'.

I appreciate all of your assistance!

 

[Catadmin]

Try putting parens around the entire SELECT statement.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[DiverMarv]

Ok everyone. I'm very sorry to have wasted your time. But I have made yet another very stupid mistake in my life. The database I was using for testing is NOT a 2005 database, it's a 2000 database.

I appreciate everyones input, and I did learn about the improved ability of an actual 2005 database at storing binary objects.

Thanks again.