question

dbk avatar image
dbk asked

IMAGE IN SQL

How to insert a image/picture jpg/gif file into database and also how to display the image/picture from database
images
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
You should insert the image into a varbinary(max) filed in a table. To store an image into a table using a query, take a look on the OPENROWSET examples on MSDN and look on the SINGLE_BLOB option and examples: [OPENROWSET (Transact-SQL)][1]. Normally again a user application takes care about storing images into binary fields. Related to displaying images - SQL Servers takes care only about string data. The user front end application should take care about displaying the data stored in database. SQL Server doesn't have some standard way to display images as it stores the images as raw binary data and doesn't take care about the content. You need some interface, which will query the image data and then display the image inappropriately. [1]: http://msdn.microsoft.com/en-us/library/ms190312.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
Another option is take advantage of the [FILESTREAM][1] data type. This will allow you to store files on the file system (where they belong in my opinion) but maintain a referential constraint against the file. [1]: http://msdn.microsoft.com/en-us/library/bb933993.aspx
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Gant, in reality FILESTREAM is not a data type. It's an option for varbinary field which defines how the field is persisted into a storage. (in that case it is not stored in the database file but on file system as separate files). Anyway on the T-SQL side the FILESTREAM fields are handled in the same way as they were normal fiels.
0 Likes 0 ·
Absolutely correct. It's not a data type. Except, it sort of is in the way MS has us deal with it. However, I'll totally defer on this one. I find file management within SQL Server (filestream or no) far more difficult than it ought to be.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.