使用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