November 13, 2015 at 9:12 am
Hello,
I am trying to loop through an image folder in my machine to add images related to particular style. I have create a table:
create table TEST
(StyleID nvarchar(50),
StyleImage VARBINARY(MAX) NULL)
Then I created a cursor to loop through a folder and insert an image in StyleImage column with matching StyleID.
DECLARE @StyleID NVARCHAR(50)
DECLARE image_cursor CURSOR FOR
SELECT StyleID FROM NealProject WHERE StyleImage IS NULL
OPEN image_cursor;
FETCH NEXT FROM image_cursor
INTO @StyleID;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sql NVARCHAR(MAX)
DECLARE @imagePath NVARCHAR(255)
SET @imagePath = 'C:\Linesheet' + SUBSTRING(@StyleID,1,5) + '.jpg'
SET @sql = 'UPDATE TEST '
SET @sql = @sql + 'SET StyleImage = (SELECT BulkColumn FROM OPENROWSET( BULK ''' + @imagePath + ''', Single_Blob) AS StyleImage) '
SET @sql = @sql + 'WHERE StyleID = ''' + SUBSTRING(@StyleID,1,5) +''
BEGIN TRY
EXECUTE sp_executesql @sql
END TRY
BEGIN CATCH
END CATCH
FETCH NEXT FROM image_cursor
INTO @StyleID;
END
CLOSE image_cursor;
DEALLOCATE image_cursor;
I have tried so many times but it doesn't do anything. Cursor gives me message "Command(s) completed successfully." but cursor doesn't add images to a table.
Please help, as I am not able to understand what is that I'm doing wrong. No errors. I know it may not be a best approach but cursor is just for one time use only.
November 13, 2015 at 12:32 pm
You're executing your sql command in a TRY/CATCH block, but you're discarding any errors that you receive. How do you expect to troubleshoot your code if you're discarding all of the relevant errors?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 13, 2015 at 12:36 pm
drew.allen (11/13/2015)
You're executing your sql command in a TRY/CATCH block, but you're discarding any errors that you receive. How do you expect to troubleshoot your code if you're discarding all of the relevant errors?Drew
I have a name for that pattern. Try/Squelch 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 13, 2015 at 1:16 pm
Sean Lange (11/13/2015)
drew.allen (11/13/2015)
You're executing your sql command in a TRY/CATCH block, but you're discarding any errors that you receive. How do you expect to troubleshoot your code if you're discarding all of the relevant errors?Drew
I have a name for that pattern. Try/Squelch 😀
Uisng a TRY/CATCH is great for handling errors...as long as you handle them. You can put the TRY/CATCH blocks in place while developing, but I'd rem them out until you have the code working like you want it to. After it works, unrem your TRY/CATCH and decide how you want to handle the errors.
Sean, thanks for the new term. 😉
November 13, 2015 at 1:19 pm
Is Linesheet a directory or part of the image name that is the same for every image? If it is a directory then I think your issue is that you need to change this:
SET @imagePath = 'C:\Linesheet' + SUBSTRING(@StyleID,1,5) + '.jpg'
To this:
SET @imagePath = 'C:\Linesheet\' + SUBSTRING(@StyleID,1,5) + '.jpg'
And you aren't seeing the error because you are "Try/Squelch"ing the error.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 13, 2015 at 2:42 pm
I did remove try/squelch block, and I did catch "C:/Linesheet/" error to. But Now I'm getting conversion error. First StyleID is "01511 bubble dress.JPG".
Here's the error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'bubble'.
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '01511 bubble dress.JPG' to data type int.
Here's the revised code.
DECLARE @StyleID NVARCHAR(100)
DECLARE image_cursor CURSOR FOR
SELECT StyleID FROM TEST WHERE StyleImage IS NULL
OPEN image_cursor;
FETCH NEXT FROM image_cursor
INTO @StyleID;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sql NVARCHAR(MAX)
DECLARE @imagePath NVARCHAR(255)
SET @imagePath = 'C:\Linesheet\' + @StyleID
SET @sql = 'UPDATE TEST '
SET @sql = @sql + 'SET StyleImage = (SELECT BulkColumn FROM OPENROWSET( BULK ''' + @imagePath + ''', Single_Blob) AS StyleImage) '
SET @sql = @sql + 'WHERE StyleID = ' + @StyleID
EXECUTE sp_executesql @sql
FETCH NEXT FROM image_cursor
INTO @StyleID;
END
CLOSE image_cursor;
DEALLOCATE image_cursor;
November 13, 2015 at 2:52 pm
sqlizer (11/13/2015)
I did remove try/squelch block, and I did catch "C:/Linesheet/" error to. But Now I'm getting conversion error. First StyleID is "01511 bubble dress.JPG".Here's the error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'bubble'.
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '01511 bubble dress.JPG' to data type int.
Here's the revised code.
DECLARE @StyleID NVARCHAR(100)DECLARE image_cursor CURSOR FOR
SELECT StyleID FROM TEST WHERE StyleImage IS NULL
OPEN image_cursor;
FETCH NEXT FROM image_cursor
INTO @StyleID;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sql NVARCHAR(MAX)
DECLARE @imagePath NVARCHAR(255)
SET @imagePath = 'C:\Linesheet\' + @StyleID
SET @sql = 'UPDATE TEST '
SET @sql = @sql + 'SET StyleImage = (SELECT BulkColumn FROM OPENROWSET( BULK ''' + @imagePath + ''', Single_Blob) AS StyleImage) '
SET @sql = @sql + 'WHERE StyleID = ' + @StyleID
EXECUTE sp_executesql @sql
FETCH NEXT FROM image_cursor
INTO @StyleID;
END
CLOSE image_cursor;
DEALLOCATE image_cursor;
The best way to learn how to debug this is to comment out the EXECUTE sp_executesql line and instead just print or select @sql so you can see what the dynamic sql actually contains. Once you see that it should become pretty obvious what the problem is. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 16, 2015 at 7:40 am
Thank you Sean Lange. Your suggestion to comment out Execute statement and using select @SQL helped. I fixed my problem. Now table is loaded.
Thanks again.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply