[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Microsoft SQL Server 2005 - Procdure to import images as binary data to columns in a table

Posted on 2009-02-13
6
Medium Priority
?
382 Views
Last Modified: 2012-05-06
I'm attempting to write a procedure that will allow me to import images as binary data to columns in a table.

Currently the table contains several columns relating to information about each product.  Additionally, the table has a columns called:

FileName (nvarchar(255))
WebImageFull (varbinary(max))
WebImageThumb (varbinary(max))

I need a procedure that will spin thorugh the table, look up the filename from the 'FileName' column and then import the images into the 'WebImageFull' and 'WebImageThumb' columns respectivley.

The images are stored in the file system at the following locations:

Full size - 'C:\images\fabrics\full_180_180\'
Thumb size - 'C:\images\fabrics\thumb_50_50\'

I began writing the query, but got hung up and decided to post it here.  Any help would be appreciated.  Here is my code so far:



DECLARE @FileName varchar(80)
DECLARE	@imgStringFull varchar(80)
DECLARE	@imgStringThumb varchar(80)
DECLARE @insertString varchar(3000)
 
 
SET @FileName = filename 
 
WHILE @FileName <> ''
 
BEGIN
 
SET @imgStringFull = 'C:\images\fabrics\full_180_180\' + @FileName
SET @imgStringThumb = 'C:\images\fabrics\thumb_50_50\' + @FileName
 
SET @insertString = 'UPDATE tmpImageTable SET WebImageFull = (SELECT BulkColumn FROM OPENROWSET(BULK ' + @imgStringFull + ', SINGLE_BLOB) AS x), WebImageThumb = (SELECT BulkColumn FROM OPENROWSET(BULK ' + @imgStringThumb + ', SINGLE_BLOB) AS x_1)'
 
EXEC(@insertString)
 
END
 
GO

Open in new window

0
Comment
Question by:mkarnofel
  • 3
  • 3
6 Comments
 
LVL 7

Expert Comment

by:maradam
ID: 23637133
You need to quote your path. This should work:

SET @insertString = 'UPDATE tmpImageTable SET WebImageFull = (SELECT BulkColumn FROM OPENROWSET(BULK ' + QUOTENAME(@imgStringFull,'''') + ', SINGLE_BLOB) AS x), WebImageThumb = (SELECT BulkColumn FROM OPENROWSET(BULK ' + QUOTENAME(@imgStringThumb,'''') + ', SINGLE_BLOB) AS x_1)'
0
 

Author Comment

by:mkarnofel
ID: 23663072
OK.  I updated the code as you instructed.  Now I get the following msg:

Msg 207, Level 16, State 1, Line 7
Invalid column name 'filename'.

Sorry for being dense here.  While I've done quite a bit of work with SQL, this looping concept is eluding me.

I pasted the new code below:
DECLARE @FileName varchar(80)
DECLARE	@imgStringFull varchar(80)
DECLARE	@imgStringThumb varchar(80)
DECLARE @insertString varchar(3000)
 
 
SET @FileName = filename 
 
WHILE @FileName <> ''
 
BEGIN
 
SET @imgStringFull = 'C:\images\fabrics\full_180_180\' + @FileName
SET @imgStringThumb = 'C:\images\fabrics\thumb_50_50\' + @FileName
 
SET @insertString = 'UPDATE tmpImageTable SET WebImageFull = (SELECT BulkColumn FROM OPENROWSET(BULK ' + QUOTENAME(@imgStringFull,'''') + ', SINGLE_BLOB) AS x), WebImageThumb = (SELECT BulkColumn FROM OPENROWSET(BULK ' + QUOTENAME(@imgStringThumb,'''') + ', SINGLE_BLOB) AS x_1)' 
EXEC(@insertString)
 
END
 
GO

Open in new window

0
 
LVL 7

Expert Comment

by:maradam
ID: 23663593
This is because of this statement

SET @FileName = filename
You need to assign a string literal to @FileName variable. For example
 
SET @FileName = 'myCoolOne.bmp'
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:mkarnofel
ID: 23663795
My problem is that I do not know how to assign the value.  The filename for each image is stored in a column of a row in the table.  I'm trying to update two additional columns of the row that contain the filename.  For example:

Filename                    WebImageFull                    WebImageThumb
adobe_100.jpg          insert binary img                 insert binary img

So in short, I'm trying to construct a procedure that will spin through the table, look up the filename, then bulk copy the binary image into the same row as the filename.  This would be done twice, once for the full size image, and once for the thumb size image.

I hope this helps explain.
0
 
LVL 7

Accepted Solution

by:
maradam earned 2000 total points
ID: 23664476
OK. Cursor is the thing you need. Look at the code in the snippet. This should work.
DECLARE @FileName varchar(80)
DECLARE	@imgStringFull varchar(80)
DECLARE	@imgStringThumb varchar(80)
DECLARE @insertString varchar(3000)
 
 
SET @FileName = filename 
 
declare c cursor local static read_only forward_only 
for 
select FileName from tmpImageTable where WebImageFull is null or WebImageThumb is null
open c
fetch next from c into @FIleName
WHILE @@FETCH_STATUS = 0
BEGIN
 
	SET @imgStringFull = 'C:\images\fabrics\full_180_180\' + @FileName
	SET @imgStringThumb = 'C:\images\fabrics\thumb_50_50\' + @FileName
	 
	SET @insertString = 'UPDATE tmpImageTable SET WebImageFull = (SELECT BulkColumn FROM OPENROWSET(BULK ' + QUOTENAME(@imgStringFull,'''') + ', SINGLE_BLOB) AS x), WebImageThumb = (SELECT BulkColumn FROM OPENROWSET(BULK ' + QUOTENAME(@imgStringThumb,'''') + ', SINGLE_BLOB) AS x_1)' 
	EXEC(@insertString)
        fetch next from c into @FIleName
 
END
close c
deallocate c
GO

Open in new window

0
 

Author Comment

by:mkarnofel
ID: 23673691
Thanks so much for your help.  I removed the "SET @FileName = filename" and the code worked like a charm.  I have pasted the final working code below.
DECLARE @FileName varchar(80)
DECLARE	@imgStringFull varchar(80)
DECLARE	@imgStringThumb varchar(80)
DECLARE @insertString varchar(3000)
 
DECLARE C CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY 
FOR
SELECT FileName FROM tmpImageTable WHERE WebImageFull IS NULL OR WebImageThumb IS NULL
OPEN C
FETCH NEXT FROM C INTO @FileName
WHILE @@FETCH_STATUS = 0
 
BEGIN
 
	SET @imgStringFull = 'C:\Inetpub\kaslentextiles.com\images\fabrics\full_180_180\' + @FileName
	SET @imgStringThumb = 'C:\Inetpub\kaslentextiles.com\images\fabrics\thumb_50_50\' + @FileName
	 
	SET @insertString = 'UPDATE tmpImageTable SET WebImageFull = (SELECT BulkColumn FROM OPENROWSET(BULK ' + QUOTENAME(@imgStringFull,'''') + ', SINGLE_BLOB) AS x), WebImageThumb = (SELECT BulkColumn FROM OPENROWSET(BULK ' + QUOTENAME(@imgStringThumb,'''') + ', SINGLE_BLOB) AS x_1)' 
	EXEC(@insertString)
        fetch next from c into @FileName
 
END
CLOSE C
DEALLOCATE C
GO

Open in new window

0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question