sql-server - 如何将存储在表中的路径与 OPENROWSET 一起使用？

Question

我最近才开始使用 OPENROWSET 将图像插入到表格中。以前，我会指定每个图像的路径(1 个图像 = 1 个 INSERT 语句)，并使用 PHP 生成图像的二进制字符串:

INSERT INTO nopCommerce..Picture (PictureBinary, MimeType, SeoFilename, AltAttribute, TitleAttribute, IsNew)
VALUES (
    (
        SELECT *
        FROM OPENROWSET(BULK '" . $this->image['path'] . "', SINGLE_BLOB) AS Binary
    ),
    '" . $this->image['mime_type'] . "',
    '" . $this->image['seo_filename'] . "',
    '" . $this->image['alt'] . "',
    '',
    0
)

但是，我试图通过单个查询插入所有图像。所以，我已经开始将每个图像的路径存储到一个表中，现在我需要像以前一样插入每个图像(只需使用表的路径字段而不是 PHP 字符串)。但是，当我尝试以下操作时:

INSERT INTO nopCommerce..Picture (PictureBinary, MimeType, SeoFilename, AltAttribute, TitleAttribute, IsNew)
SELECT
    (
        SELECT *
        FROM OPENROWSET(BULK ImagePath, SINGLE_BLOB) AS Binary
    ),
    MimeType,
    Name,
    Alt,
    '',
    0
FROM nopRMS..Product_Image_Mappings

我收到以下错误:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'ImagePath'.

因此，我尝试在列名称周围添加引号(无济于事):

INSERT INTO nopCommerce..Picture (PictureBinary, MimeType, SeoFilename, AltAttribute, TitleAttribute, IsNew)
SELECT
    (
        SELECT *
        FROM OPENROWSET(BULK 'ImagePath', SINGLE_BLOB) AS Binary
    ),
    MimeType,
    Name,
    Alt,
    '',
    0
FROM nopRMS..Product_Image_Mappings

Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "ImagePath" does not exist.

必须有一种方法来实现这一点，我只是无法在任何地方在线找到合适的语法。有谁知道如何告诉 SQL Server 从 dbo.Product_Image_Mappings.ImagePath 获取路径(字符串)？

更新
我忘了给你一个 dbo.Product_Image_Mappings.ImagePath 会返回的值的例子。它的路径类似于 \\DEREK\WebImages\1\ca-82300.jpg...

更新
Eirikur Eiriksson 似乎在 this thread 中提供了解决方案，但这看起来像是实现相同目的的过于复杂的方法...

更新(尝试使用 Eirikur Eiriksson 的方法)

DECLARE @SQL_STR NVARCHAR(MAX) = N'';

SELECT @SQL_STR = STUFF(
    (
        SELECT 
            N'
            UNION ALL
            SELECT '
            + N'(SELECT X.BulkColumn FROM OPENROWSET(BULK '
            + NCHAR(39) + im.ImagePath + NCHAR(39)
            + N', SINGLE_BLOB) AS X) AS PictureBinary,'
            + NCHAR(39) + im.MimeType + NCHAR(39)
            + N' AS MimeType,'
            + NCHAR(39) + im.Name + NCHAR(39)
            + N' AS SeoFilename,'
            + NCHAR(39) + REPLACE(im.Alt, '''', '''''') + NCHAR(39)
            + N' AS AltAttribute,'
            + N'NULL AS TitleAttribute,'
            + N'0 AS IsNew'
        FROM nopRMS..Product_Image_Mappings im
        FOR XML PATH(''), TYPE
    ).value('.[1]','NVARCHAR(MAX)'),1,12,N''
)

INSERT INTO nopCommerce..Picture (PictureBinary, MimeType, SeoFilename, AltAttribute, TitleAttribute, IsNew)
EXEC (@SQL_STR);

这有点管用，但它只插入了 42 行(超过 7200 行)...我需要它 100% 准确 :( 不过我承认，我可能需要更改此查询的某些内容，但我不对此一无所知(除了基本的 INSERT、SELECT 等)

Answer 1

也许不使用OPENROWSET？您可以使用 SQLCLR 以更简单、更清晰的方式处理您想要的内容。您可以创建标量 UDF 以仅读取文件的内容并将其作为 VARBINARY(MAX) 返回。然后它将很好地适合您现有的查询。例如:

INSERT INTO nopCommerce.dbo.Picture (PictureBinary, MimeType, SeoFilename, 
                      AltAttribute, TitleAttribute, IsNew)
SELECT
    dbo.GetBinaryFile([ImagePath]) AS [PictureBinary],
    MimeType,
    Name,
    Alt,
    '',
    0
FROM nopRMS.dbo.Product_Image_Mappings;

dbo.GetBinaryFile() 需要多少代码？在这里:

using System;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;

[return:SqlFacet(MaxSize = -1)]
[SqlFunction(IsDeterministic = false, IsPrecise = true)]
public static SqlBytes GetBinaryFile([SqlFacet(MaxSize = 1000)] SqlString FilePath)
{
    if (FilePath.Value.Trim().Equals(String.Empty))
    {
        return SqlBytes.Null;
    }

    return new SqlBytes(File.ReadAllBytes(FilePath.Value));
}

T-SQL 包装器对象如下(请注意 WITH RETURNS NULL ON NULL INPUT 行，因为如果 NULL 被传入，它会跳过执行，因此没有需要在 C# 代码中检查 FilePath.IsNull :-)

CREATE FUNCTION [dbo].[GetBinaryFile](@FilePath NVARCHAR(1000))
RETURNS VARBINARY(MAX)
WITH RETURNS NULL ON NULL INPUT
AS
 EXTERNAL NAME [CSVParser].[CSVUtils].[GetBinaryFile];

程序集需要标记为 WITH PERMISSION_SET = EXTERNAL_ACCESS。许多人通过将 TRUSTWORTHY 的数据库属性设置为 ON 来实现这一点，但这是一种安全风险，甚至没有必要。只需执行以下操作，您就可以将程序集设置为 EXTERNAL_ACCESS，同时将 TRUSTWORTHY 设置为 OFF:

1. 在大会上签字。
2. 从该 DLL 在 master 中创建一个非对称 key 。
3. 从该非对称 key 创建登录(也在 master 中)。
4. 授予新登录名 EXTERNAL ACCESS ASSEMBLY 权限。

您可以在我撰写的以下文章中找到有关如何在 Visual Studio/SSDT(SQL Server 数据工具)中执行此操作的详细说明:Stairway to SQLCLR Level 7: Development and Security (该站点确实需要免费注册才能查看内容)。

此外，对于不想为创建/部署程序集而烦恼的任何人，SQL# 的完整版中提供了类似的功能(尽管不是免费的)。库(我创建的，虽然许多功能是免费的，但 File_* 文件系统功能仅在完整版中提供)。