SQL Server FileStream - 如何获取文件路径

Question

我正在开发一个应用程序，需要将大型数据文件上传到我的SQL Server数据库中，我一直在使用FileStream来提高效率。

我知道这些文件直接存储在我的系统文件夹（C：\ CryptoDB）中。

问题是，我需要操作这些文件（解密它们），但我无法恢复它们的文件路径。 这样做，我就可以直接操作它们，而不必通过SQL重新下载它们，这是真正的浪费。

到目前为止，我所能做的：

我的表格：

CREATE TABLE [arquivo] (
[idUsuario]   INT NOT NULL,
[fileState]   INT NOT NULL,
[fileContent] varbinary(max) FILESTREAM,
[fileName]    VARCHAR (150)   NULL,
[fileSize]    VARCHAR (50)    NULL,

id UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,

CONSTRAINT [FK_arquivo_usuario] FOREIGN KEY ([idUsuario]) REFERENCES usuario(id)
);

插入：

Insert into arquivo(id, idUsuario, fileState, fileContent, fileName, fileSize) Values(
newId(),
1,
5,
(SELECT * FROM OPENROWSET(BULK 'c:\medio.jpeg', SINGLE_BLOB) AS varbinary(max)) ,
'medio.jpeg',
'123'
)

当我尝试恢复文件路径时：

DECLARE @filePath varchar(max)

SELECT @filePath = fileContent.PathName()
FROM arquivo

PRINT @filepath

The result I get:

\\TEHORT-PC\MSSQLSERVER\v02-A60EC2F8-2B24-11DF-9CC3-AF2E56D89593\CryptoDB\dbo\arquivo\fileContent\31E3697E-0576-4B0F-B0AA-6E046F4116A1\VolumeHint-HarddiskVolume2

文件实际路径：

C:\CryptoDB\DATA\902a7d8d-c8c1-43b0-8c94-b12319293f42\7febdbd1-02c6-4b00-aa3c-a72bee80ef9c\

- Tehort

Answer 1

1. 获取所有FILESTREAM数据的物理位置的SQL查询（source）

   SELECT t.name AS 'table', c.name AS 'column', fg.name AS 'filegroup_name', dbf.type_desc AS 'type_description', dbf.physical_name AS 'physical_location' FROM sys.filegroups fg INNER JOIN sys.database_files dbf ON fg.data_space_id = dbf.data_space_id INNER JOIN sys.tables t ON fg.data_space_id = t.filestream_data_space_id INNER JOIN sys.columns c ON t.object_id = c.object_id AND c.is_filestream = 1

所有FILESTREAM BLOB字段查询结果示例

2. 获取服务器上FILESTREAM数据的子文件夹的SQL查询： （这些表只在专用管理员连接（DAC）内使用）。

   SELECT o.name AS [Table], cp.name AS [Column], r.rsguid AS [Rowset GUID], rs.colguid AS [Column GUID] FROM SYS.SYSROWSETS r CROSS APPLY sys.sysrscols rs JOIN sys.partitions p ON rs.rsid = p.partition_id JOIN sys.objects o ON o.object_id = p.object_id JOIN sys.syscolpars cp ON cp.colid = rs.rscolid WHERE rs.colguid IS NOT NULL AND o.object_id = cp.id AND r.rsguid IS NOT NULL AND r.rowsetid = rs.rsid AND o.name = 'DOCUMENT' and cp.name = 'DIGITAL_FILE';

2.1. 查询结果：

表：DOCUMENT
列：DIGITAL_FILE
行集GUID：0x6AA5E6045794D34D8B1FAC0F49A49B0A
列GUID：0xD756E638FB2CC843AE98F489B57F6D7D

从这些GUID计算子路径:

0x6AA5E6045794D34D8B1FAC0F49A49B0A 等于此路径： 04e6a56a-9457-4dd3-8b1f-ac0f49a49b0a

[反转6AA5E604]-[反转5794]-[反转D34D]-[反转8B1F]-[原始AC0F49A49B0A]

0xD756E638FB2CC843AE98F489B57F6D7D 等于此路径： 38e656d7-2cfb-43c8-ae98-f489b57f6d7d (在先前GUID解析中的规则)

2.2 计算出用于FILESTREAM存储的完整路径：

i:\SQL Base posc_astrachan FileStreams\GTMK\GTM_FILE_STREAM\04e6a56a-9457-4dd3-8b1f-ac0f49a49b0a\38e656d7-2cfb-43c8-ae98-f489b57f6d7d

获取NTFS文件夹中BLOB值的原始文件名。

3.1. 查询高级SQL Server页面信息的存储过程。

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[procDBCC_PAGE]
    @db_name varchar (500),
    @filenum INT,
    @pagenum INT
AS
BEGIN
SET NOCOUNT ON 
DBCC TRACEON (3604);
DBCC PAGE (@db_name, @filenum, @pagenum, 3) WITH TABLERESULTS;
SET NOCOUNT OFF     
END

3.2. 查询表中 FILESTREAM BLOB 字段的原始文件名的存储过程

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE  PROCEDURE [dbo].[procFindLogSequenceNumber] 
-- @TableName varchar (500),
 @instanceS varchar (19), -- key value for filed INSTANCE_S
 @tableName varchar(500), -- DOCUMENT
 @keyFieldName varchar(500), -- INSTANCE_S
 @LogSequenceNumber varchar (500) OUTPUT
AS
SET NOCOUNT ON

DECLARE @db_name varchar (500) 
DECLARE @filenum INT
DECLARE @pagenum INT
DECLARE @slotnum INT
DECLARE @rid varchar (100) 
DECLARE @ridDotted varchar (100) 

DECLARE @parent_object varchar (500)

DECLARE @sql nvarchar(2000)
DECLARE @sqlTable Table(physloc varchar(100))

DECLARE @DBCC_PAGE_Output Table ([ParentObject] varchar (MAX), [Object] varchar (MAX), [Field] varchar (MAX), [VALUE] varchar (MAX))

SET @db_name = db_name()

SET @sql = 'SELECT top 1 sys.fn_PhysLocFormatter (%%physloc%%) AS [PhysicalRID] FROM '+@tableName+' WHERE '
             +@keyFieldName+' = '''+@instanceS+''''

INSERT @sqlTable (physloc)
EXECUTE sp_executesql @sql

SET @rid = (select top 1 physloc from @sqlTable)
if @rid is NULL
BEGIN
  RETURN -1;
END

-- parse (@rid): (1:1172779:6)  1-@filenum, 2- @pagenum, 3- @slotnum 
SET @ridDotted = Replace(@rid, ':', '.');
SET @ridDotted = Replace(@ridDotted, '(', '');
SET @ridDotted = Replace(@ridDotted, ')', '');

SET @filenum = (SELECT Parsename(@ridDotted, 3))
SET @pagenum = (SELECT Parsename(@ridDotted, 2))
SET @slotnum = (SELECT Parsename(@ridDotted, 1))

INSERT @DBCC_PAGE_Output ([ParentObject], [Object], [Field], [VALUE]) 
EXECUTE procDBCC_PAGE @db_name, @filenum , @pagenum

SET @parent_object = (SELECT TOP 1 [ParentObject] FROM @DBCC_PAGE_Output WHERE [Field] = 'INSTANCE_S'
 AND [VALUE] = @instanceS) 

--CreateLSN field Only
SET @LogSequenceNumber = (SELECT [VALUE] FROM  @DBCC_PAGE_Output WHERE 
 [ParentObject] = @parent_object AND
 [Field] = 'CreateLSN'
)

if @LogSequenceNumber is NULL
BEGIN
  RETURN -1;
END

-- result 0006c050:00000120:0090 (442448:288:144)
-- clear (...)
SET @LogSequenceNumber = Replace(@LogSequenceNumber, ' ', '.');
SET @LogSequenceNumber = (SELECT Parsename(@LogSequenceNumber, 2))

--replace ":" to "-"
SET @LogSequenceNumber = Replace(@LogSequenceNumber, ':', '-');

SET NOCOUNT OFF

3.3. 获取NTFS文件夹中BLOB文件名的存储过程示例查询：

declare @filestreamFileName varchar(500);
exec procFindLogSequenceNumber 'ZW_NU9hGZ0CKoSXYAoc', 'DOCUMENT', 'INSTANCE_S', @filestreamFileName OUTPUT
select @filestreamFileName

3.4. 结果（在NTFS文件夹中的原始文件名）：

0003137a-00001244-00d0

3.5. 结果完整路径：

i:\SQL Base posc_astrachan FileStreams\GTMK\GTM_FILE_STREAM\04e6a56a-9457-4dd3-8b1f-ac0f49a49b0a\38e656d7-2cfb-43c8-ae98-f489b57f6d7d\0003137a-00001244-00d0

- Alexander Semenyak

Answer 2

你获取的路径是正确的，应该获取网络共享路径而不是本地路径，并使用SqlFileStream打开流。

https://msdn.microsoft.com/en-us/library/system.data.sqltypes.sqlfilestream%28v=vs.110%29.aspx

您还可以使用OpenSqlFilestream方法获取文件句柄，即可在Windows API中使用。

https://msdn.microsoft.com/en-us/library/bb933972.aspx

- user3285954

Answer 3

Alexander的回答很好，帮我省了很多麻烦，因为我不用再将页面/插槽号与实际的LSN相关联。在我的情况下，我使用SQL Server 2008 R2，我必须对他的SP进行一些调整才能使其正常工作，这些是：

SP的第一个参数：

@instanceS varchar (100)，--字段INSTANCE_S的键值

在我的情况下，这是一个唯一标识符，所以我需要更大的varchar，原始值为19。

当查询DBCC PAGE输出时：

SET @parent_object = (SELECT TOP 1 [ParentObject] FROM @DBCC_PAGE_Output WHERE [Field] = @keyFieldName AND [VALUE] = @instanceS)

它最初声明了"[Field] = 'INSTANCE_S'"，显然硬编码了一个适用于OP但不适用于我的值。它需要匹配FILESTREAM表的键字段的名称。

此外，为了澄清SP的输入参数：

@instanceS = 实际列值，用于标识行。这个值是否总是与作为表“RowGuid”的列匹配？
@tableName = 很清楚。FILESTREAM表的名称。
@keyFieldName = 表主键列的名称。应该是从@instanceS获取的源列的名称。

- Baelphes