Update a table with a column image

Question

Update a table with a column image

pacita_tibay

SSC Enthusiast

Points: 135
More actions
July 19, 2012 at 9:37 am

#259104

Hi,
I added a new column (image) to an existing table. I'd like to update this table populating the image column from the image files which were uploaded by the user via application to the file server. This same table contains the name of these image files.
I wrote a stored procedure (below) to populate the image column.
CREATE PROCEDURE [dbo].[_pInsertImages]
AS
BEGIN
DECLARE @imgString varchar(100), @docId INT, @docname VARCHAR(100)
DECLARE @numrows INT
SET @numrows = 0
DECLARE CursorId CURSOR
GLOBAL FOR SELECT DocumentId, DocumentName FROM dbo.Units_Documents WHERE DocumentTypeId= 81 and recordstatus=1
OPEN CursorId
FETCH NEXT FROM CursorId INTO @docId, @docname
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @imgString = 'C:\documents\Picture\' + @docname
PRINT @imgString
UPDATE Units_Documents
SET Picture = (SELECT myImage.* FROM OPENROWSET(BULK ''' + @imgString + ''', SINGLE_BLOB) AS MyImage) WHERE DocumentId=@docId
FETCH NEXT FROM CursorId INTO @docId, @docname
SELECT @numrows = @numrows + 1
END --While Begin
PRINT 'Updated records: ' + convert(varchar, @numrows)
CLOSE CursorId
DEALLOCATE CursorId
END
GO
When I run this sp, it gave an error:
C:\documents\Picture\F2103 Overall View1.jpg
Msg 4860, Level 16, State 1, Procedure _pInsertImages, Line 22
Cannot bulk load. The file "' + @imgString + '" does not exist.
I tried to put all files in my local c:\ and also in the SQL server C:\ but gave the same error.
When I run this statement:
UPDATE dbo.Units_Documents
SET Picture = (SELECT myImage.* FROM OPENROWSET(BULK 'c:\documents\picture\F2103 Overall View.jpg', SINGLE_BLOB) AS MyImage)
WHERE DocumentId=2
it works.
I wonder if BULK accepts only hardcoded filename and not a variable.
Any idea?
Thanks.

Viewing 4 posts - 1 through 4 (of 4 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Did you use the image data type or the varbinary(max) data type? I'm asking because text, ntext, and image data types have been depreciated since the release of SQL Server 2005 and shouldn't be used in new development projects as support for these data types could go away with a future release of SQL Server.

pacita_tibay SSC Enthusiast Points: 135 More actions · Answer 2

I've defined the column as Image and using SQL Server 2008 R2.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

If you haven't gotten any data loaded into this column yet, I'd consider dropping it and recreating it with the varbinary(max) data type. You may also want to consider looking at using filestream.