I want to insert my product's image into a database and then want to get those image on request. please suggest.
A:
inserting is easily done into a field of BLOB type.
getting the image back will depend on what language you will be using. Do you use PHP?
Here Be Wolves
2009-06-03 12:49:07
can u tell me please what will be the query for BLOB.
Nipun
2009-06-03 12:59:39
+2
A:
Hi Nipun,
The best way to store your images in a SQL Server 2005 database is to store the path of where the image actually exists.
See this for more information.
http://stackoverflow.com/questions/805519/save-image-in-database
Aaron
2009-06-03 13:05:50
+3
A:
I disagree with Aaron. Storing images in the file system always leaves you with the potential problem of orphaned files and/or database entries. It is difficult to maintain "referential integrity". There may be certain scenarios where you don't want to go the blob route, because the images are particularly large, etc, but in most standard application scenarios I would store the image in the database.
cdonner
2009-06-03 13:14:30
SQL Server Filestream technology addresses your concerns but is only available in SQL 2008 onwards.
John Sansom
2009-06-03 13:24:25
Having images in the database adds another layer of complexity that is not needed. Agreed, some solutions might benefit from having the images directly in the DB, but with large databases, images will just reduce both space and performance. That said, it's always a plus to know and weigh your options before building your database :).
Aaron
2009-06-03 13:27:22
Storing files, in particular document managment e.g customer application forms/scanned documents etc. within the database tier seriously degrades performance (bloats tables and physical .mdf files) and hence why Microsoft reccomend storing file larger than 1GB on the files system.
John Sansom
2009-06-03 13:39:09
A:
SQL Server 2005 does have an Image datatype. Use it.
Regarding if it is good or bad, it depends on you requirements.
NinethSense
2009-06-03 13:26:30
Don't use IMAGE--it is a holdover from a bygone era (SQL 2000). Use VARBINARY(MAX).
Wyatt Barnett
2009-06-03 13:40:15
Yes, I do not use it. But still IMAGE datatype exists in SQL Server 2005 :)
NinethSense
2009-06-03 13:42:04
FYI: ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. http://msdn.microsoft.com/en-us/library/ms187993.aspx
KM
2009-06-03 19:43:48
+2
A:
This is a very common SQL Server discussion. The general rule of thumb is if the files to be stored are larger than say 1MB then store them in the file system.
For a complete discussion of the issues involved consult the following Microsoft Research document.
http://research.microsoft.com/apps/pubs/default.aspx?id=64525
On a side note SQL Server 2008 uses Filestream technology to cater for this scenario.
John Sansom
2009-06-03 13:28:46
A:
Best way is upgrade on SQL Server 2008 and use FileStream (content stored in the file system)
MicTech
2009-06-03 13:42:57
A:
Skipping the whole "are images in the db bad" debate, you need to solve a few problems here.
1) Data Storage: you need to have someplace to put the images. In SQL 2005, you would be best off using a VARBINARY(MAX) column.
2) Upload: you will need to upload the image into your database. Check out the FileUpload command. In terms of actually INSERTING stuff, the .NET data type would be byte[]. Your ORM should handle this for you in any case. Also remember you should store the file size, file name and the mimetype provided, you'll need those soon enough.
3) Display: here's the one that trips most folks up. You need to make a way to request the image file for display. The best way is to implement a custom IHttpHandler. Then SELECT the image data, file name, mimetype and file length. Push the mimetype and filename in the header, set the content length to the file length and stream the binary data to the output stream.
Wyatt Barnett
2009-06-03 13:47:00
A:
Another option that works very easy
Convert your image to Base64 string store it on a varchar field
To display it, just convert it from base64 to binary....
This works with any DB engine, in .net conversion from binary to base64 takes microseconds...
daniel
2009-06-03 15:00:40