CODEWITHSUNDEEP
sqlsql-serverimagesql-server-2008
Neville Nazerane
Neville Nazerane

Reputation: 7019

inserting and get image from sql server database

I am trying to insert an image into a table which has a column with type (image). Here is what I tried:

INSERT INTO tblDDPhotos(ID, img)
VALUES (1, SELECT * FROM
OPENROWSET(BULK 'blob:http%3A//localhost%3A64527/81655427-3653-427d-92bf-254cd2a2e647', SINGLE_BLOB) as imagefile)`

I wanted to know,

  1. How do we insert the image from blob.
  2. How can we access and retrieve the image.
  3. What are the disadvantage of storing an image in the DB as over to saving the image in a folder in the server.

Upvotes: 0

Views: 4464

Answers (1)

marc_s
marc_s

Reputation: 754258

You cannot mix the VALUES and the SELECT approaches in your INSERT statement - use one or the other, but you cannot mix them.

So one way to do this would be to use:

DECLARE @FileContents VARBINARY(MAX)

SELECT @FileContents = BulkColumn
FROM OPENROWSET(BULK 'blob:http%3A//localhost%3A64527/81655427-3653-427d-92bf-254cd2a2e647', SINGLE_BLOB) as imagefile)

INSERT INTO tblDDPhotos(ID, img)
VALUES (1, @FileContents)

or the alternative approach would be to use:

INSERT INTO tblDDPhotos(ID, img)
   SELECT 1, BulkColumn
   FROM OPENROWSET(BULK 'blob:http%3A//localhost%3A64527/81655427-3653-427d-92bf-254cd2a2e647', SINGLE_BLOB)

Pick whichever approach suits you better - both work equally well.

Side note: ntext, text, and image data types will be removed in a future version of 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.

See details about this here

Upvotes: 1

Related Questions