本文介绍了如何在OPENROWSET(BULK ...)中动态指定文件的路径?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想将图像插入到图像字段中,最好使用存储过程来接受图像的路径。到处乱搞之后,我想到了这一点;

I want to insert images into an Image field, preferably using a stored procedure which will accept a path to an image. After hacking around I came up with this;

-- functional
DECLARE @parameters nvarchar(max) = '';
DECLARE @sql_string nvarchar(max) =
N'UPDATE MyTable
  SET MyImageField = (SELECT BulkColumn
                      FROM Openrowset(Bulk ''' + @PathToMyImage + ''', Single_Blob) ImageData)
  WHERE MyPrimaryKey = ' + CAST(@PrimaryKey AS NVARCHAR(max));

EXECUTE sp_executesql @sql_string,  @parameters

我这样做是因为尝试;

I did this because when I tried;

--Not functional
INSERT INTO MyTable (MyImageField)
VALUES ((SELECT BulkColumn
         FROM Openrowset(Bulk @PathToMyImage, Single_Blob) ImageData));

SQL Server抛出错误,抱怨说Bulk需要一个字符串。我希望不必为了维护性/可读性而诉诸于sp_executesql,是否有更好的方法来解决这个问题?

SQL Server throws an error, complaining that Bulk expects a string. I'd prefer to not have to resort to sp_executesql for maintainability / readability, is there a better way to go about this?

推荐答案

要做,必须使用动态SQL,但可以使用quotename()来避免嵌入引用梦night,并且应将主键作为实际参数传递。

You do have to use dynamic sql but you can use quotename() to avoid embedded quote nightmares and you should pass the primary key in as an actual parameter.

DECLARE @sql_string nvarchar(max) =
N'UPDATE MyTable
  SET MyImageField = (SELECT BulkColumn
                      FROM Openrowset(Bulk ' + quotename(@PathToMyImage,nchar(39)) + ', Single_Blob) ImageData)
  WHERE MyPrimaryKey = @PrimaryKey';

EXECUTE sp_executesql @sql_string, N'@PrimaryKey int',  @PrimaryKey

nchar(39)是单引号。路径名中的所有单引号都将被正确转义,并且路径将被单引号括起来以进行安全的串联。

nchar(39) is a single quote. All single quotes within the path name will be properly escaped and the path will be enclosed in single quotes for safe concatenation.

这篇关于如何在OPENROWSET(BULK ...)中动态指定文件的路径?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-30 05:51