Obtener acceso a datos FILESTREAM con Transact-SQL

Artículo
07/08/2024

Se aplica a: SQL Server

En este artículo se describe cómo utilizar las instrucciones INSERT, DELETE y UPDATE de Transact-SQL para administrar los datos de FILESTREAM.

Nota:

Los ejemplos de este artículorequieren la tabla y la base de datos habilitada para FILESTREAM que se crean en Crear una base de datos habilitada para FILESTREAM y Crear una tabla para almacenar datos FILESTREAM.

Insertar una fila que contiene datos FILESTREAM

Para añadir una fila a una tabla que admite datos FILESTREAM, use la instrucción INSERT de Transact-SQL. Al insertar datos en una columna FILESTREAM, se puede insertar NULL o un valor varbinary(max) .

Insertar NULL

El ejemplo siguiente muestra la forma de utilizar el valor NULL. Cuando el valor FILESTREAM es NULL, el motor de base de datos no crea un archivo en el sistema de archivos.

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

Insertar un registro de longitud cero

En el siguiente ejemplo se muestra cómo utilizar INSERT para crear un registro de longitud cero. Esto es útil cuando se desea obtener un identificador de archivo, pero el archivo se va a tratar con las API de Win32.

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

Crear un archivo de datos

En el siguiente ejemplo se muestra cómo utilizar INSERT para crear un archivo que contiene datos. El motor de base de datos convierte la cadena Seismic Data en un valor varbinary(max). FILESTREAM crea el archivo de Windows si aún no existe. A continuación, se añaden los datos al archivo de datos.

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

Al seleccionar todos los datos de la tabla Archive.dbo.Records, los resultados son similares a los que se muestran en la tabla siguiente. Sin embargo, la columna Id contendrá GUID diferentes.

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`

Actualizar datos FILESTREAM

Puede usar Transact-SQL para actualizar los datos del archivo del sistema de archivos; pero posiblemente no quiera si hay que transmitir grandes cantidades de datos a un archivo.

En el ejemplo siguiente se reemplaza cualquier texto del registro del archivo por el texto Xray 1.

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

Eliminar datos FILESTREAM

Al eliminar una fila que contiene un campo FILESTREAM, también elimina sus archivos de sistema de archivos subyacentes. La única manera de eliminar una fila y, por consiguiente, el archivo, es utilizar la instrucción DELETE de Transact-SQL.

El ejemplo siguiente muestra cómo eliminar una fila y sus archivos de sistema de archivo asociados.

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

Al seleccionar todos los datos de la tabla Archive.dbo.Records, la fila desaparece y no se puede usar el archivo asociado.

Nota:

El recolector de elementos no utilizados de FILESTREAM quita los archivos subyacentes.

Comprobar si una tabla o base de datos contiene datos FILESTREAM

Para averiguar si una base de datos o una tabla contiene datos FILESTREAM, debe consultar las vistas del sistema.

En el ejemplo siguiente ampliado se muestran los pasos para crear una nueva base de datos, crear tablas que tengan datos FILESTREAM y consultar vistas del sistema para ver si las tablas y la propia base de datos contienen datos 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';

Compartir vía