Mengakses data FILESTREAM dengan Transact-SQL

Artikel
08/23/2023

Berlaku untuk:SQL Server

Artikel ini menjelaskan cara menggunakan pernyataan TRANSACT-SQL INSERT, UPDATE, dan DELETE untuk mengelola data FILESTREAM.

Catatan

Contoh dalam artikel ini memerlukan database dan tabel berkemampuan FILESTREAM yang dibuat di Membuat Database yang Diaktifkan FILESTREAM dan Membuat Tabel untuk Menyimpan Data FILESTREAM.

Sisipkan baris yang berisi data FILESTREAM

Untuk menambahkan baris ke tabel yang mendukung data FILESTREAM, gunakan pernyataan TRANSACT-SQL INSERT. Saat Anda menyisipkan data ke dalam kolom FILESTREAM, Anda bisa menyisipkan nilai NULL atau varbinary(maks).

Sisipkan NULL

Contoh berikut menunjukkan cara menyisipkan NULL. Ketika nilai FILESTREAM adalah NULL, Mesin Database tidak membuat file dalam sistem file.

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

Menyisipkan rekaman panjang nol

Contoh berikut menunjukkan cara menggunakan INSERT untuk membuat rekaman panjang nol. Ini berguna ketika Anda ingin mendapatkan handel file, tetapi akan memanipulasi file dengan menggunakan API Win32.

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

Membuat file data

Contoh berikut menunjukkan cara menggunakan INSERT untuk membuat file yang berisi data. Mesin Database mengonversi string Seismic Data menjadi varbinary(max) nilai. FILESTREAM membuat file Windows jika belum ada. Data kemudian ditambahkan ke file data.

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

Saat Anda memilih semua data dari Archive.dbo.Records tabel, hasilnya mirip dengan hasil yang diperlihatkan dalam tabel berikut. Namun, Id kolom akan berisi GUID yang berbeda.

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

Memperbarui data FILESTREAM

Anda dapat menggunakan Transact-SQL untuk memperbarui data dalam file sistem file, tetapi Anda mungkin tidak ingin saat melakukan streaming data dalam jumlah besar ke file.

Contoh berikut menggantikan teks apa pun dalam rekaman file dengan teks Xray 1.

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

Menghapus data FILESTREAM

Saat Anda menghapus baris yang berisi bidang FILESTREAM, Anda juga menghapus file sistem file yang mendasar. Satu-satunya cara untuk menghapus baris, dan oleh karena itu file, adalah dengan menggunakan pernyataan TRANSACT-SQL DELETE.

Contoh berikut menunjukkan cara menghapus baris dan file sistem file terkait.

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

Saat Anda memilih semua data dari Archive.dbo.Records tabel, baris hilang, dan Anda tidak dapat lagi menggunakan file terkait.

Catatan

File yang mendasar dihapus oleh pengumpul sampah FILESTREAM.

Memeriksa apakah tabel atau database berisi data FILESTREAM

Untuk mengetahui apakah database atau tabel berisi data FILESTREAM, Anda harus mengkueri tampilan sistem.

Contoh yang diperluas berikut ini memperlihatkan langkah-langkah untuk membuat database baru, membuat tabel yang memiliki data FILESTREAM, dan tampilan sistem kueri untuk melihat apakah tabel, dan database itu sendiri, berisi data 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';

Share via