0

I have a table at the moment with columns ( id#(int), picture(varbinary(MAX) ), and I have a folder on my HDD with a ton of pictures. The pictures in the folder are named by the 'id' I want to match them with in my table. How can I do this?

Example:

Table Row: id=25166, picture=NULL
25166.jpg
fwaokda
  • 15
  • 2
  • 5
  • Maybe you could tell us a little more about what technologies you're trying to use...obviously, you're going to loop through the files in the specified directory, and update the content in the db with the image content, but what tools do you want to do that with? – Chris B. Behrens Aug 22 '11 at 22:28
  • What language are you using: SQL-side and to interface between the SQL database and the server, I'm guessing SQL-server and C#, am I right? – Johan Aug 22 '11 at 22:30
  • @Chris oh sorry, I'm using SQL Server 2008. I was thinking I should be able to do it in just that or am I going to need another language to help loop through everything? This is just supposed to be a one time thing not something I'm needed to build a front end for to do more than just this once... – fwaokda Aug 22 '11 at 22:31
  • Well, it would be pretty easy with C#. If nothing else, you could download Visual Studio Express and use some pasted in code from here. Would that help? – Chris B. Behrens Aug 22 '11 at 22:32

1 Answers1

2

If you need an example which uses just SQL checkout the following:

It uses a cursor to loop through each of the rows which do not contain image data and for each row found uses the OPENROWSET BULK provider to load the file.

CREATE TABLE ImageStore
( 
id INT, 
picture VARBINARY(MAX) NULL
)
GO 

INSERT INTO ImageStore (id) VALUES(25166)
INSERT INTO ImageStore (id) VALUES(25167)

DECLARE @id INT
DECLARE image_cursor CURSOR FOR 
SELECT id FROM ImageStore WHERE picture IS NULL

OPEN image_cursor;

FETCH NEXT FROM image_cursor 
INTO @id;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sql NVARCHAR(MAX)

    DECLARE @imagePath NVARCHAR(255)
    SET @imagePath = 'C:\' + RTRIM(LTRIM(STR(@id))) + '.gif'

    SET @sql = 'UPDATE ImageStore '
    SET @sql = @sql + 'SET picture = (SELECT BulkColumn FROM OPENROWSET( BULK ''' + @imagePath + ''', Single_Blob) AS picture) '
    SET @sql = @sql + 'WHERE id = ' + STR(@id)

    BEGIN TRY
        EXECUTE sp_executesql @sql 
    END TRY
    BEGIN CATCH

    END CATCH   

    FETCH NEXT FROM image_cursor 
    INTO @id;
END
CLOSE image_cursor;
DEALLOCATE image_cursor;

SELECT * FROM ImageStore

DROP TABLE ImageStore
jdavies
  • 12,784
  • 3
  • 33
  • 33