SQL Serverストアド・プロシージャを使用して、BLOB/Imageなどの画像またはバイナリ・データをディスク・ファイルとして保存します.
詳細
-- , 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