Accéder aux données FILESTREAM avec Transact-SQL

Article
07/08/2024

S’applique à : SQL Server

Cet article explique comment utiliser les instructions Transact-SQL INSERT, UPDATE et DELETE Transact-SQL pour gérer des données FILESTREAM.

Remarque

Les exemples de cet article nécessitent la base de données compatible FILESTREAM et la table qui sont créées dans Créer une base de données compatible FILESTREAM et Créer une table pour le stockage de données FILESTREAM.

Insérer une ligne qui contient des données FILESTREAM

Pour ajouter une ligne à une table prenant en charge les données FILESTREAM, utilisez l’instruction Transact-SQL INSERT. Quand vous insérez des données dans une colonne FILESTREAM, vous pouvez insérer une valeur NULL ou varbinary(max) .

Insérer une valeur NULL

L'exemple suivant montre comment insérer NULL. Lorsque la valeur FILESTREAM est NULL, le moteur de base de données ne crée pas de fichier dans le système de fichiers.

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

Insérer un enregistrement de longueur nulle

L'exemple suivant illustre l'utilisation de INSERT pour créer un enregistrement de longueur nulle. C'est utile lorsque vous souhaitez obtenir un descripteur de fichier, mais que vous manipulerez le fichier en utilisant des API Win32.

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

Créer un fichier de données

L'exemple suivant illustre l'utilisation de INSERT pour créer un fichier contenant des données. Le moteur de base de données convertit la chaîne Seismic Data en valeur varbinary(max). FILESTREAM crée le fichier Windows s’il n’existe pas déjà. Les données sont ensuite ajoutées au fichier de données.

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

Quand vous sélectionnez toutes les données de la table Archive.dbo.Records, les résultats sont semblables à ceux affichés dans le tableau suivant. Toutefois, la colonne Id contiendra des GUID différents.

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

Mettre à jour les données FILESTREAM

Vous pouvez utiliser Transact-SQL pour mettre à jour les données dans le fichier de système de fichiers, bien que cela soit plutôt déconseillé quand vous devez transmettre en continu de gros volumes de données à un fichier.

L'exemple suivant remplace tout texte dans l'enregistrement de fichier par le texte Xray 1.

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

Supprimer les données FILESTREAM

Lorsque vous supprimez une ligne qui contient un champ FILESTREAM, vous supprimez également ses fichiers de système de fichiers sous-jacents. La seule façon de supprimer une ligne, et par conséquent le fichier, consiste à utiliser l’instruction Transact-SQL DELETE.

L'exemple suivant montre comment supprimer une ligne et les fichiers de système de fichiers qui lui sont associés.

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

Quand vous sélectionnez toutes les données de la table Archive.dbo.Records, la ligne disparaît et vous ne pouvez plus utiliser le fichier associé.

Remarque

Les fichiers sous-jacents sont supprimés par le garbage collector FILESTREAM.

Vérifier si une table ou une base de données contient des données FILESTREAM

Pour savoir si une base de données ou une table contient des données FILESTREAM, vous devez interroger les vues système.

L’exemple étendu suivant montre les étapes à suivre pour créer une base de données, créer des tables qui ont des données FILESTREAM et interroger des vues système pour voir si les tables et la base de données elle-même contiennent des données 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';

Partage via