sql - 如何使用正确的文件扩展名将(所有)SQL Server 图像数据类型保存到文件系统

Question

我的表中有不同类型的文件(txt、doc、jpg 等)，我想将它们全部保存到我的文件系统中。我知道如何逐个文件执行此操作，但这没有用，我必须执行一个循环以一次保存所有具有正确扩展名的文件。有什么建议吗？

我现在这样做:

DECLARE @ObjectToken INT
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, <this is where binary data goes>
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, 'd:/file.txt', 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken

所以，我的问题是有没有一种方法可以同时对数据库表中的所有记录(保存所有文件)执行此操作，并为每个保存的文件设置正确的文件扩展名？

Answer 1

基于code I mentioned in the comments , 并且假设您使用 SQL Server 2005 或更高版本，此解决方案应该适合您

DECLARE 
    @FileContent VARBINARY(MAX),
    @FileName VARCHAR(MAX),
    @ObjectToken INT,
    @FileID BIGINT

DECLARE cFiles CURSOR FAST_FORWARD FOR
SELECT Id, FileName, Image_Data from MyFileTable
OPEN cFiles

FETCH NEXT FROM cFiles INTO @FileID, @FileName, @FileContent

WHILE @@FETCH_STATUS = 0 BEGIN

    -- CHOOSE 1 of these SET statements:

    -- if FileName is unique, then just remove special characters
    SET @FileName = REPLACE(REPLACE(@FileName, '\', ' '), ':', ' ')
    -- if FileName without path information is unique
    SET @FileName = SUBSTRING(@FileName,
        len(@FileName)+2-CHARINDEX('\',REVERSE(@FileName)),len(@FileName))
    -- if you just take ID + extensions
    SET @FileName = 'c:\path\to\' + CAST(@FileID AS varchar) + 
        SUBSTRING(@ap_description,len(@ap_description)+1
            -CHARINDEX('.',REVERSE(@ap_description)),len(@ap_description))

    EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
    EXEC sp_OASetProperty @ObjectToken, 'Type', 1
    EXEC sp_OAMethod @ObjectToken, 'Open'
    EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @FileContent
    EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FileName, 2
    EXEC sp_OAMethod @ObjectToken, 'Close'
    EXEC sp_OADestroy @ObjectToken

    FETCH NEXT FROM cFiles INTO @FileID, @FileName, @FileContent
END

CLOSE cFiles
DEALLOCATE cFiles

image 应该自动转换为 varbinary(max)。

('\' 中断 SO 语法高亮。)