Acessar dados FILESTREAM com Transact-SQL

Artigo
07/08/2024

Aplica-se a: SQL Server

Este artigo descreve como usar as instruções INSERT, UPDATE e DELETE do Transact-SQL para gerenciar dados FILESTREAM.

Observação

Os exemplos citados neste artigo exigem o banco de dados e a tabela habilitados para FILESTREAM criados em Criar um banco de dados habilitado para FILESTREAM e Criar uma tabela para armazenar dados de FILESTREAM.

Inserir uma linha contendo dados de FILESTREAM

Para adicionar uma linha a uma tabela que suporte dados de FILESTREAM, use a instrução Transact-SQL INSERT. Ao inserir dados em uma coluna FILESTREAM, é possível inserir NULL ou um valor varbinary(max) .

Inserir NULL

O exemplo a seguir mostra como inserir NULL. Quando o valor de FILESTREAM for NULL, o Mecanismo de Banco de Dados não criará um arquivo no sistema de arquivos.

INSERT INTO Archive.dbo.Records
    VALUES (NEWID(), 1, NULL);
GO

Inserir um registro de comprimento zero

O exemplo a seguir mostra como usar INSERT para criar um registro com comprimento zero, o que é útil quando você quer obter um identificador de arquivo, mas manipulará o arquivo usando APIs de Win32.

INSERT INTO Archive.dbo.Records
    VALUES (NEWID(), 2, 
      CAST ('' AS VARBINARY(MAX)));
GO

Crie um arquivo de dados

O exemplo a seguir mostra como usar o objeto INSERT para criar um arquivo que contenha dados. O Mecanismo de Banco de Dados converte a cadeia de caracteres Seismic Data em um valor varbinary(max). FILESTREAM criará o arquivo do Windows se ele ainda não existir. Os dados são então adicionados ao arquivo de dados.

INSERT INTO Archive.dbo.Records
    VALUES (NEWID(), 3, 
      CAST ('Seismic Data' AS VARBINARY(MAX)));
GO

Quando você selecionar todos os dados da tabela Archive.dbo.Records, os resultados serão similares aos mostrados na tabela a seguir. Porém, a coluna Id conterá GUIDs diferente.

ID	SerialNumber	Gráfico
`C871B90F-D25E-47B3-A560-7CC0CA405DAC`	`1`	`NULL`
`F8F5C314-0559-4927-8FA9-1535EE0BDF50`	`2`	`0x`
`7F680840-B7A4-45D4-8CD5-527C44D35B3F`	`3`	`0x536569736D69632044617461`

Atualizar dados FILESTREAM

Você pode usar o Transact-SQL para atualizar os dados no arquivo do sistema de arquivos; não é recomendável fazer isso quando houver grandes quantidades de fluxo de dados em um arquivo.

O exemplo a seguir substitui qualquer texto no registro do arquivo pelo texto Xray 1.

UPDATE Archive.dbo.Records
SET [Chart] = CAST('Xray 1' AS VARBINARY(MAX))
WHERE [SerialNumber] = 2;

Excluir dados FILESTREAM

Ao excluir uma linha que contém um campo de FILESTREAM, você também exclui seus arquivos subjacentes do sistema de arquivos. O único modo de excluir uma linha, e portanto o arquivo, é usar a instrução Transact-SQL DELETE.

O exemplo a seguir mostra como excluir uma linha e seus arquivos associados do sistema de arquivos.

DELETE Archive.dbo.Records
WHERE SerialNumber = 1;
GO

Quando você seleciona todos os dados da tabela Archive.dbo.Records, a linha é excluída e o arquivo associado não pode mais ser usado.

Observação

Os arquivos subjacentes são removidos pelo coletor de lixo do FILESTREAM.

Verificar se uma tabela ou banco de dados contém dados FILESTREAM

Para descobrir se um banco de dados ou tabela contém dados FILESTREAM, você deve consultar as exibições do sistema.

O exemplo estendido a seguir mostra as etapas para criar um novo banco de dados, criar tabelas que tenham dados FILESTREAM e exibições do sistema de consulta para ver se as tabelas e o próprio banco de dados contêm dados FILESTREAM.

USE [master];
GO

-- Create database with FILESTREAM
CREATE DATABASE [FileStreamTest] CONTAINMENT = NONE ON PRIMARY (
    NAME = N'FileStreamTest'
    , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\FileStreamTest.mdf'
    , SIZE = 204800 KB
    , MAXSIZE = UNLIMITED
    , FILEGROWTH = 65536 KB
    )
    , FILEGROUP [FileStreamFG] CONTAINS FILESTREAM DEFAULT(NAME = N'FileStreamTestFStream', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\FileStreamTestFStream', MAXSIZE = UNLIMITED) LOG ON (
    NAME = N'FileStreamTest_log'
    , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\FileStreamTest_log.ldf'
    , SIZE = 270336 KB
    , MAXSIZE = 2048 GB
    , FILEGROWTH = 65536 KB
    )
    WITH CATALOG_COLLATION = DATABASE_DEFAULT;
GO

USE [FileStreamTest];
GO

CREATE TABLE FSTiffs (
    Guid UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID()
    , DocumentID INT NOT NULL
    , DocumentType VARCHAR(10) NOT NULL
    , FileContent VARBINARY(MAX) FILESTREAM NOT NULL
    , DateInserted DATETIME
);

-- Which database and files use FILESTREAM 
SELECT db_name(database_id) dbname
    , name AS file_name
    , physical_name
    , type_desc
    , *
FROM sys.master_files
WHERE type_desc = 'FILESTREAM';

-- Which tables in the database have FILESTREAM enabled
USE [FileStreamTest]
GO

SELECT *
FROM sys.tables
WHERE filestream_data_space_id IS NOT NULL;

--insert a TIFF file
INSERT INTO FSTiffs (
    DocumentID
    , DocumentType
    , FileContent
    , DateInserted
    )
SELECT 101
    , '.tiff'
    , *
    , GETDATE()
FROM OPENROWSET(BULK N'C:\Temp\Sample1.tiff', SINGLE_BLOB) rs;

-- Select data from FILESTREAM table
SELECT *
FROM FSTiffs;

-- Update a document
UPDATE FSTiffs
SET FileContent = (
        SELECT *
        FROM OPENROWSET(BULK N'C:\Temp\Sample2.tiff', SINGLE_BLOB) AS rs
        )
WHERE DocumentID = 101;

-- Delete a document
DELETE FSTiffs
WHERE DocumentID = 101;

--clean up any delete files
EXEC sp_filestream_force_garbage_collection @dbname = N'FileStreamTest'
    , @filename = N'FileStreamTestFStream';

Partilhar via