programming4us
programming4us
DATABASE

Microsoft SQL Server 2008 R2 : Using FILESTREAM Storage (part 2) - Using FILESTREAM Storage for Data Columns

7/24/2012 3:47:56 PM

Setting Up a Database for FILESTREAM Storage

After you enable FILESTREAM for the SQL Server instance, you can store FILESTREAM data in a database by creating a FILESTREAM filegroup. You can do this when creating the database or by adding a new filegroup to an existing database. The filegroup designated for FILESTREAM storage must include the CONTAINS FILESTREAM clause and be defined. The code in Listing 1 creates the Customer database and then adds a FILESTREAM filegroup.

Listing 1. Setting Up a Database for FILESTREAM Storage
CREATE DATABASE Customer
ON ( NAME='Customer_Data',
    FILENAME='C:\SQLData\Customer_Data1.mdf',
    SIZE=50,
    MAXSIZE=100,
    FILEGROWTH=10)
LOG ON ( NAME='Customer_Log',
    FILENAME='C:\SQLData\Customer_Log.ldf',
    SIZE=50,
    FILEGROWTH=20%)
GO

ALTER DATABASE Customer
 ADD FILEGROUP Cust_FSGroup CONTAINS FILESTREAM
GO

ALTER DATABASE Customer
 ADD FILE  ( NAME=custinfo_FS,
           FILENAME = 'G:\SQLData\custinfo_FS')
TO FILEGROUP Cust_FSGroup
GO

Notice in Listing 42.18 the FILESTREAM filegroup points to a file system folder rather than an actual file. This folder must not exist already (although the path up to the folder must exist); SQL Server creates the FILESTREAM folder (for example, in Listing 42.18, the custinfo_FS folder is created automatically by SQL Server in the G:\SQLData folder). The FILESTREAM files and file data actually end up being stored in the created folder. A FILESTREAM filegroup is restricted to referencing only a single file folder.

Using FILESTREAM Storage for Data Columns

Once FILESTREAM storage is enabled for a database, you can specify the FILESTREAM attribute on a varbinary(max) column to indicate that a column should store data in the FILESTREAM filegroup on the file system. When columns are defined with the FILESTREAM attribute, the Database Engine stores all data for that column on the file system instead of in the database file. In addition to a varbinary(max) column with the FILESTREAM attribute, tables used to store FILESTREAM data also require the existence of a UNIQUE ROWGUIDCOL, as shown in Listing 2, which creates a custinfo table on the FILESTREAM filegroup. CUSTDATA is defined as the FILESTREAM column, and ID is defined as the unique ROWGUID column.

Listing 2. Creating a FILESTREAM-Enabled Table
CREATE TABLE CUSTINFO
(ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
CUSTDATA VARBINARY (MAX) FILESTREAM NULL )
FILESTREAM_ON Cust_FSGroup

Each table created with a FILESTREAM column(s) creates a new subfolder in the FILESTREAM filegroup folder, and each FILESTREAM column in the table creates a separate subfolder under the table folder. These column folders are where the actual FILESTREAM files are stored. Initially, these folders are empty until you start adding rows into the table. A file is created in the column subfolder for each row inserted into the table with a non-NULL value for the FILESTREAM column.


To ensure that SQL Server creates a new, blank file within the FILESTREAM storage folder for each row inserted in the table, you can specify a default value of 0x for the FILESTREAM column:

alter table CUSTINFO add constraint custdata_def default 0x for CUSTDATA

Creating a default is not required if all access to the FILESTREAM data is going to be done through T-SQL. However, if you will be using Win32 streaming clients to upload file contents into the FILESTREAM column, the file needs to exist already. Without the default to ensure creation of a “blank” file for each row, new files would have to be created first by inserting contents directly through T-SQL before they could be accessed via Win32 client streaming applications.

To insert data into a FILESTREAM column, you use a normal INSERT statement and provide a varbinary(max) value to store into the FILESTREAM column:

INSERT CUSTINFO (ID, CUSTDATA)
VALUES (NEWID(),  CONVERT(VARBINARY(MAX), REPLICATE ('CUST DATA', 100000)))

To retrieve FILESTREAM data, you can use a simple T-SQL SELECT statement, although you may need to convert the varbinary(max) to varchar to be able to display text data:

select ID, CONVERT(varchar(40), CUSTDATA) as CUSTDATA
 from CUSTINFO
go

ID                                   CUSTDATA
------------------------------------ ----------------------------------------------
FA67BF05-51B5-4BA7-A383-7F88DAAE9C49 CUST DATACUST DATACUST DATACUST DATACUST


					  

The preceding examples work fine if the FILESTREAM data is essentially text data; however, neither SQL Server Management Studio nor SQL Server itself really has any user interface, or native way, to let you stream the contents of an actual file into a table that’s been marked with the FILESTREAM attribute on one of your varbinary(max) columns. In other words, if you have a .jpg or .mp3 file that you want to store within SQL Server, there’s no native functionality to convert that image’s byte stream into something that you could put, for example, into a simple INSERT statement. To read or store this type of data, you need to use Win32 to read and write data to a FILESTREAM BLOB. Following are the steps you need to perform in your client applications:

1.
Read the FILESTREAM file path.

2.
Read the current transaction context.

3.
Obtain a Win32 handle and use the handle to read and write data to the FILESTREAM BLOB.

Each cell in a FILESTREAM table has a file path associated with it. You can use the PATHNAME property to retrieve the file path of a varbinary(max) column in a T-SQL statement:

DECLARE @filePath varchar(max)

SELECT @filePath = CUSTDATA.PathName()
FROM CUSTINFO
WHERE ID = 'FA67BF05-51B5-4BA7-A383-7F88DAAE9C49'


PRINT @filepath
go

\\LATITUDED830-W7\FILESTREAM\v1\Customer\dbo\CUSTINFO\CUSTDATA
\FA67BF05-51B5-4BA7-A383-7F88DAAE9C49

Next, to obtain the current transaction context and return it to the client application, use the GET_FILESTREAM_TRANSACTION_CONTEXT() T-SQL function:

BEGIN TRAN
SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()

After you obtain the transaction context, the next step in your application code is to obtain a Win32 file handle to read or write the data to the FILESTREAM column. To obtain a Win32 file handle, you call the OpenSqlFilestream API. The returned handle can then be passed to any of the following Win32 APIs to read and write data to a FILESTREAM BLOB:

  • ReadFile

  • WriteFile

  • TransmitFile

  • SetFilePointer

  • SetEndOfFile

  • FlushFileBuffers

To summarize, the steps you perform to upload a file to a FILESTREAM column are as follows:

1.
Start a new transaction and obtain the transaction context ID that can be used to initiate the Win32 file-streaming process.

2.
Execute a SqlDataReader connection to pull back the full path (in SQL Server) of the FILESTREAM file to which you will be uploading data.

3.
Initiate a straight file-streaming operation using the System.Data.SqlTypes.SqlFileStream class.

4.
Create a new System.IO.FileStream object to read the file locally and buffer bytes along to the SqlFileStream object until there are no more bytes to transfer.

5.
Close the transaction.

Note

Because you’re streaming file contents via a Win32 process, you need to use integrated security to connect to SQL Server because native SQL logins can’t generate the needed security tokens to access the underlying file system where the FILESTREAM data is stored.


To retrieve data from a FILESTREAM column to a file on the client, you primarily follow the same steps as you do for inserting data; however, instead you pull data from a SqlFileStream object into a buffer and push it into a local FILESTREAM object until there are no more bytes left to retrieve.

Other  
  •  SQL Server 2005 : Using Excel (part 2) - Using PivotTables and Charts in Applications and Web Pages
  •  SQL Server 2005 : Using Excel (part 1) - Working Within Excel
  •  Microsoft SQL Server 2008 R2 : Hierarchyid Data Type (part 2) - Modifying the Hierarchy
  •  Microsoft SQL Server 2008 R2 : Hierarchyid Data Type (part 1) - Creating a Hierarchy, Populating the Hierarchy, Querying the Hierarchy
  •  Using SQL Server 2005 Integration Services : Extensibility (part 4) - Custom Connection Managers
  •  Using SQL Server 2005 Integration Services : Extensibility (part 3) - Script Components
  •  Using SQL Server 2005 Integration Services : Extensibility (part 2) - Custom Components
  •  Using SQL Server 2005 Integration Services : Extensibility (part 1) - Script Tasks
  •  .NET Compact Framework 3.5 : Working with Data Sets (part 3) - Reading and Writing a Data Set as XML
  •  .NET Compact Framework 3.5 : Working with Data Sets (part 2) - Data Binding
  •  
    Top 10 Video Game
    -   Minecraft Mods - MAD PACK #10 'NETHER DOOM!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Minecraft Mods - MAD PACK #9 'KING SLIME!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Minecraft Mods - MAD PACK #2 'LAVA LOBBERS!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Minecraft Mods - MAD PACK #3 'OBSIDIAN LONGSWORD!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Total War: Warhammer [PC] Demigryph Trailer
    -   Minecraft | MINIONS MOVIE MOD! (Despicable Me, Minions Movie)
    -   Minecraft | Crazy Craft 3.0 - Ep 3! "TITANS ATTACK"
    -   Minecraft | Crazy Craft 3.0 - Ep 2! "THIEVING FROM THE CRAZIES"
    -   Minecraft | MORPH HIDE AND SEEK - Minions Despicable Me Mod
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 92 "IS JOE DEAD?!"
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 93 "JEDI STRIKE BACK"
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 94 "TATOOINE PLANET DESTRUCTION"
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 95 "TATOOINE CAPTIVES"
    -   Hitman [PS4/XOne/PC] Alpha Gameplay Trailer
    -   Satellite Reign [PC] Release Date Trailer
    Video
    programming4us
     
     
    programming4us