将多个图像插入数据库的问题。 [英] problem in inserting multiple images to the database.

问题描述

我有一个数据库，其中包含产品详细信息产品代码，名称等是列，我在系统中的产品图像在一个文件夹中我需要根据关于的正确行将数据直接添加到数据库产品代码，产品代码和图像是sam，产品代码是110-1,110-2等，图像名称是110-1jpg，110-2jpg等我有一个程序，但它没有更新所有行请帮助

I have a database in which contain product details The product code, name etc are the columns, I have the product images in the system in a folder I need to add the images directly to the database according to the proper rows regarding the product code, The product code and images are sam, the product code is like 110-1,110-2 etc, and image names are 110-1jpg,110-2jpg etc I have a program but its not updating all the rows pls help

DECLARE @CODE varchar

DECLARE image_cursor CURSOR FOR

SELECT CODE FROM MyMast WHERE img IS NULL

OPEN image_cursor;

FETCH NEXT FROM image_cursor

INTO @CODE;

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @sql VARCHAR(MAX)

DECLARE @imagePath VARCHAR(255)

SET @imagePath = 'D:\images\' + RTRIM(LTRIM(@CODE)) + '.jpg'

SET @sql = 'UPDATE Mymast'

SET @sql = @sql + 'SET img = (SELECT BulkColumn FROM OPENROWSET( BULK ''' + @imagePath + ''', Single_Blob) AS Picture), SET PictureFileName = ' + @imagepath

SET @sql = @sql + 'WHERE CODE = ''' + @CODE + ''';'

BEGIN TRY

EXECUTE sp_executesql @sql

END TRY

BEGIN CATCH

END CATCH

FETCH NEXT FROM image_cursor

INTO @CODE;

END

CLOSE image_cursor;

DEALLOCATE image_cursor;

SELECT CODE, img FROM MyMast WHERE img IS NOT NULL

推荐答案

需要解决一些小错误。

A few minor errors need to be addressed.

--length of variable required
DECLARE @CODE varchar(20);

--string concatenation issues
--also an addition SET to be removed from SET PictureFileName
--the initial @imagePath has single quotes surrounding it on the generated sql but the second one does not
SET @sql = 'UPDATE Mymast'
SET @sql = @sql + 'SET img = (SELECT BulkColumn FROM OPENROWSET( BULK ''' + @imagePath + ''', Single_Blob) AS Picture), SET PictureFileName = ' + @imagepath
SET @sql = @sql + 'WHERE CODE = ''' + @CODE + ''';'
--on the concatenation side the above will produce UPDATE MymastSET and the WHERE will be appended to generated @imagepath

--the @sql needs to be of nvarchar for the statement to be executed using sp_executesql
EXECUTE sp_executesql @sql
--or use
EXEC (@sql);

sp_executesql（Transact-SQL）： http://msdn.microsoft.com/en-us/library/ms188001.aspx [ ^ ]



我相信所有问题都已经指出。

考虑使用替代方案到光标，因为它们被认为是不好的做法：

http://stackoverflow.com/questions/58141/why-is-it-considered-bad-practice-to-use-cursors-in-sql-server [ ^ ]



也可能养成使用分号语句终止符的习惯：

未来版本的SQL Server不支持的功能 [ ^ ]

sp_executesql (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms188001.aspx[^]

I believe all the issues have been pointed out.
Consider using an alternative to a cursor as they are considered bad practice:
http://stackoverflow.com/questions/58141/why-is-it-considered-bad-practice-to-use-cursors-in-sql-server[^]

Also possibly get in the habit of using the semicolon statement terminator:
Features Not Supported in a Future Version of SQL Server[^]

--here are the changes as specified above
--only slight modification of your original code
DECLARE @CODE varchar(50);
DECLARE image_cursor CURSOR FOR
 	SELECT CODE FROM MyMast WHERE img IS NULL
 	OPEN image_cursor;
 		FETCH NEXT FROM image_cursor
 			INTO @CODE;
 			
		WHILE @@FETCH_STATUS = 0 BEGIN
			DECLARE @sql VARCHAR(MAX);
			DECLARE @imagePath VARCHAR(255);
			SET @imagePath = 'D:\images\' + RTRIM(LTRIM(@CODE)) + '.jpg';
			SET @sql = 'UPDATE Mymast ';
			SET @sql = @sql + 'SET img = (SELECT BulkColumn FROM OPENROWSET( BULK ''' + @imagePath + ''', Single_Blob) AS Picture),  PictureFileName = ''' + @imagepath + ''' ';
			SET @sql = @sql + 'WHERE CODE = ''' + @CODE + ''';';
			BEGIN TRY
--				EXECUTE sp_executesql @sql
				EXEC (@sql);
			END TRY
			BEGIN CATCH
			END CATCH

			FETCH NEXT FROM image_cursor
				INTO @CODE;
		END;
	CLOSE image_cursor;
DEALLOCATE image_cursor;
SELECT CODE, img FROM MyMast WHERE img IS NOT NULL;
--if it does not work, ensure all images in CODE column are present in D:\images\ folder.

这篇关于将多个图像插入数据库的问题。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！