欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

使用SQL Server存储过程将BLOB/Image等图片或二进制数据另存为磁盘文件

程序员文章站 2022-04-23 08:35:57
...

--要想下面的存储过程正常执行,必须先开启OLE对象的安全权限
sp_configure 'show advanced options',1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures',1;
GO
RECONFIGURE;
GO

--存储过程:将二进制数据保存到指定的目录
--需要先将@FilePathAndName所在目录的安全权限,添加Windows账户“Network Service”的写入权限,才会成功
CREATE PROCEDURE [dbo].[up_SaveBinaryToFile](@Binary VARBINARY(MAX), @FilePathAndName VARCHAR(MAX))
AS 
BEGIN
DECLARE @ObjectToken INT
DECLARE @ErrorSource VARCHAR(255)
DECLARE @ErrorDesc VARCHAR(255)
DECLARE @ObjectReturn INT
	
	EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
	EXEC sp_OASetProperty @ObjectToken, 'Type', 1
	EXEC sp_OAMethod @ObjectToken, 'Open'
	EXEC @ObjectReturn = sp_OAMethod @ObjectToken, 'Write', NULL, @Binary
	IF (@ObjectReturn <> 0)
    BEGIN
        EXEC sp_OAGetErrorInfo @ObjectToken, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
        RAISERROR('Write Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
    END
    
	EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FilePathAndName, 2
	EXEC sp_OAMethod @ObjectToken, 'Close'
	EXEC sp_OADestroy @ObjectToken
END
GO

--创建表[Temp_File]:如果在此表中插入新行时,isSave为1,则将文件保存到服务器指定的路径
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Temp_File](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[uuid] [uniqueidentifier] NOT NULL,
	[title] [nvarchar](250) NULL,
	[name] [nvarchar](50) NULL,
	[ext] [nvarchar](50) NULL,
	[path] [nvarchar](max) NULL,
	[size] [int] NULL,
	[binData] [image] NULL,
	[comment] [nvarchar](max) NULL,
	[ownedBy] [nvarchar](50) NULL,
	[isSave] bit NULL	--如果为1,则将数据作为物理文件存储至[path]
 CONSTRAINT [PK_Temp_File] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Temp_File] ADD  CONSTRAINT [DF_Temp_File_uuid]  DEFAULT (newid()) FOR [uuid]
GO

--以下是通过触发器来调用:当在此表中新增一行时,则将文件保存到服务器指定的路径
CREATE TRIGGER tgr_exportTempFileTosvrDisk
ON Temp_File 
	INSTEAD OF INSERT
AS
	DECLARE @uuid uniqueidentifier
	DECLARE @title NVARCHAR(250)
	DECLARE @name NVARCHAR(50)
	DECLARE @ext NVARCHAR(50)
	DECLARE @isSave BIT	
	DECLARE @size INT
	DECLARE @fileData VARBINARY(MAX)
	DECLARE @comment NVARCHAR(MAX)
	DECLARE @ownedBy NVARCHAR(50)
		
	DECLARE @TempFolder NVARCHAR(256)
	DECLARE @SavedFile NVARCHAR(MAX)
	DECLARE @ObjectReturn INT
	DECLARE @Fso INT
	DECLARE @File INT	
	
	select @uuid = [uuid]
	  ,@title = [title]
      ,@name = [name]
      ,@ext = [ext]
      ,@isSave = [isSave]
      ,@size = [size]
      ,@fileData = [binData]
      ,@comment = [comment]
      ,@ownedBy = [ownedBy]
	  from inserted;	
	
	--文件写入 WINXP : C:\Documents and Settings\NetworkService\Local Settings\Temp
	--		   WIN7/8/10/2008/2012:   C:\Windows\ServiceProfile\NetworkService\AppData\Local\Temp\
	IF @isSave = 1
	BEGIN
		EXEC @ObjectReturn = sp_OACreate 'Scripting.FileSystemObject', @Fso OUTPUT;
		EXEC @ObjectReturn = sp_OAMethod @Fso, 'GetSpecialFolder', @File OUTPUT, 2; --系统临时文件夹
		EXEC @ObjectReturn = sp_OAGetProperty @File, 'Path', @TempFolder OUTPUT;
		SET @SavedFile = @TempFolder + N'\' + CONVERT(NVARCHAR(256), @uuid) + '.' + @ext;
		
		EXEC up_SaveBinaryToFile  @fileData , @SavedFile;
	END
	
	insert into Temp_File ([title],[name],[ext],[isSave],[size],[binData],[comment],[ownedBy]) values (@title,@name,@ext,@isSave,@size,@fileData,@comment,@ownedBy);
	update Temp_File set [path]=@SavedFile where [uuid]=@uuid
GO

------------------------------------------------------------
--以下是直接使用SQL语句,手动调用上述存储过程保存文件
DECLARE @fileData VARBINARY(MAX)
DECLARE @fileName NVARCHAR(255)
DECLARE @extName NVARCHAR(255)
DECLARE @filePath NVARCHAR(MAX)
select top 1 @fileData = binData, @fileName = [name], @extName= [type] from dbo.system_picsLib
SET @filePath = 'C:\temp\'+@fileName+'.'+@extName
exec up_SaveBinaryToFile  @fileData, @filePath