Images; inserting and retrieving

Question

Images; inserting and retrieving

Xavier Castillo-325754

SSC Enthusiast

Points: 105
More actions
May 8, 2006 at 9:49 am

#68797

I'm trying to insert and then retrieve a 3K jpeg image in a SQL Server 2000 database. This is the first time I'm doing this. I created a table with a varbinary field for this purpose (is this the right type of field?). I'm using PHP 5.0 to do this. I already inserted the row, and the varbinary field seems to be right, but I can not see the image with Enterprise manager. So I'm also using PHP to retrieve the image and show it in the Internet Explorer browser, but I only get and empty square as a placeholder for the image. I don't need an explanation about how to this with PHP, I already have that information. I need the explanation of how the SQL database handles this. I've read the SQL Server documentation, but I didn't find examples on this subject, or at least clear examples.
Thanks in advance,
Xavier.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Mike C SSC-Insane Points: 23224 More actions · Answer 1

Personally I prefer to store images in the file system and store a reference to the file in the database.

If that's not an option, here's a really good article on the subject with links to a ton of MS KB articles and code samples (I think most of the code samples are ASP though, but should give you a general idea): http://www.aspfaq.com/show.asp?id=2149

Xavier Castillo-325754 SSC Enthusiast Points: 105 More actions · Answer 2

I think your advice is correct, so I'm going to store images in the file system.

Thanks a lot,

Xavier.

Mike C SSC-Insane Points: 23224 More actions · Answer 3

No problem I hate saying "you shouldn't do that..." to people because it always turns out they don't have a choice in the matter I'm just glad you have a choice!

AnilT SSC Enthusiast Points: 160 More actions · Answer 4

I agree with storing the paths and not the files within Access or SQL Server.

Problems such as images being lost if database is corrupted or massive db file growth can occur especially in MS Access.

d viz Ten Centuries Points: 1092 More actions · Answer 5

I have an application that has been running for over two years with MS Access. Its mdb file is about 70 MB (compacted) and the images that are associated with the data are over 8 GB !!!

To begin with, MS access files cannot grow that large, but the SQL server version also keeps the images on the file system.

Less space used by the db files, easier backups and the list goes on.

I believe that it's not really a choice to keep images in the database (any database). Even SAP works this way....

Mike C SSC-Insane Points: 23224 More actions · Answer 6

It can be done, but just because you can do something doesn't mean you should

Ian Yates SSCoach Points: 19738 More actions · Answer 7

The BLOBs in SQL Server work very well - provides some benefits that the file system doesn't offer - but that's another story and not the point of the question

For starters, Enterprise Manager will see the varbinary column as exactly that - a variable length bunch of binary data... It will not interpret it as an image, zip file or anything else.. MS Access (haven't used it in years) I think lets you use OLE controls and such to display the data - again not relevant to you as you are in PHP land but I think that's why many ppl think Enterprise Manager will also show images, etc.

Anyhow, you will need to be sure that, from the PHP side of things, you are indeed sending binary data to SQL Server and not some strange text equivalent. You'll find using a stored procedure with a varbinary parameter easier to use than hand-crafting a SQL insert/update string that contains the binary data as an encoded string. I imagine the PHP DB access routines have the ability to set parameters of stored procs and probably provide some way of setting the binary data - use that if at all possible....

But, if the shoe fits, use it - the file system is certainly easier if you are running into such issues and it suites your purpose.

Cheers

Xavier Castillo-325754 SSC Enthusiast Points: 105 More actions · Answer 8

Thanks to everyone for your comments. I appreciate them so much. I'm going to store images in the filesystem. It seems to be the easiest way to handle them.

Xavier Castillo.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 9

Xavier,

There are 3 things that you have to have in mind when storing images in the file system:

1. Backups. Your backup plan should include database backups and attachments directory backup. Also it should be documented well so other people who are moving your application to another machine would know that

2. NTFS Permissions to users. If users will be putting files to the directory through the application you need to create a share for the files and give users appropriate permissions.

3. When making a copy of the environment for the development or test purposes do not forget to change the folder /share name in the application so your test environment would not point to the same folder as production.

Regards,Yelena Varsha