Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Chad Miller

Chad Miller is a Senior Manager of Database Administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999 and has been automating administration tasks using Windows Powershell since 2007. Chad is the Project Coordinator/Developer of the Powershell-based Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at users groups, SQL Saturdays and Code Camps.

T-SQL Tuesday #006 Blobs, FileStream and PowerShell

In case you’re wondering about the title T-SQL Tuesday, it’s a monthly collection of SQL Server related content where a bunch of bloggers contribute posts related a specific topic. This month’s topic hosted by Michael Coles is Large Object data types or also know as Blobs. In this blog post I’ll demonstrate working with blob and filestream columns using PowerShell.

Blobs

Using the Knowledge Base article, How to read and write a file to or from a BLOB column by using ADO.NET and Visual C# .NET, as a guide I’ve loosely adapted the C# examples to PowerShell. I’m using the sample database AdventureWorks2008 which you can download from CodePlex. Note: traditional blob columns can be used in versions of SQL Server earlier than 2008 including SQL Server 2000. Because we’ll also be writing blobs we’ll need to create a SQL table in the AdventureWorks2008 database as follows:

1
2
3
4
CREATE TABLE [Production].[ProductPhoto2](
	[ProductPhotoID] [INT] IDENTITY(1,1) NOT NULL,
	[ThumbNailPhoto] [VARBINARY](MAX) NULL,
	[ThumbnailPhotoFileName] [NVARCHAR](50) NULL)

First let’s extract a bunch of images stored as blobs to the file system in a script called sqlblob2file.ps1. The script retrieves the first ten images and writes them to my C:\Users\u00 directory:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$server = "Z002\sql2k8"
$database = "AdventureWorks2008"
$query = "SELECT TOP 10 ThumbNailPhoto, ThumbnailPhotoFileName FROM Production.ProductPhoto"
$dirPath = "C:\Users\u00\"
 
$connection=new-object System.Data.SqlClient.SQLConnection
$connection.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $server,$database
$command=new-object system.Data.SqlClient.SqlCommand($query,$connection)
$command.CommandTimeout=120
$connection.Open()
$reader = $command.ExecuteReader()
while ($reader.Read())
{
    $sqlBytes = $reader.GetSqlBytes(0)
    $filepath = "$dirPath{0}" -f $reader.GetValue(1)
    $buffer = new-object byte[] -ArgumentList $reader.GetBytes(0,0,$null,0,$sqlBytes.Length)
    $reader.GetBytes(0,0,$buffer,0,$buffer.Length)
    $fs = new-object System.IO.FileStream($filePath,[System.IO.FileMode]'Create',[System.IO.FileAccess]'Write')
	$fs.Write($buffer, 0, $buffer.Length)
	$fs.Close()
}
$reader.Close()
$connection.Close()

Next we will write an image file on the file system to a SQL Server blob column in a script called file2SqlBlob.ps1. In this script we read the file into memory and write it to the blob column using an insert statement:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
$server = "Z002\sql2k8"
$database = "AdventureWorks2008"
$query = "INSERT Production.ProductPhoto2 VALUES (@ThumbNailPhoto, @ThumbnailPhotoFileName)"
$filepath = "C:\Users\u00\hotrodbike_black_small.gif"
$ThumbnailPhotoFileName = get-childitem $filepath | select -ExpandProperty Name
 
$connection=new-object System.Data.SqlClient.SQLConnection
$connection.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $server,$database
$command=new-object system.Data.SqlClient.SqlCommand($query,$connection)
$command.CommandTimeout=120
$connection.Open()
 
$fs = new-object System.IO.FileStream($filePath,[System.IO.FileMode]'Open',[System.IO.FileAccess]'Read')
$buffer = new-object byte[] -ArgumentList $fs.Length
$fs.Read($buffer, 0, $buffer.Length)
$fs.Close()
 
$command.Parameters.Add("@ThumbNailPhoto", [System.Data.SqlDbType]"VarBinary", $buffer.Length)
$command.Parameters["@ThumbNailPhoto"].Value = $buffer
$command.Parameters.Add("@ThumbnailPhotoFileName", [System.Data.SqlDbType]"NChar", 50)
$command.Parameters["@ThumbnailPhotoFileName"].Value = $ThumbnailPhotoFileName
$command.ExecuteNonQuery()
 
$connection.Close()

OpenRowset

While researching writing blob data I noticed T-SQL’s OpenRowset supports a very simple way to load image data. This is documented in the Books Online topic, OPENROWSET (Transact-SQL). Here’s an example that uses T-SQL to load a single image file into our ProductPhoto2 table:

1
2
3
4
5
USE AdventureWorks2008;
INSERT INTO Production.ProductPhoto2 (ThumbNailPhoto, ThumbnailPhotoFileName)
   SELECT *, 'hotrodbike_black_small.gif' AS ThumbnailPhotoFileName
    FROM OPENROWSET(BULK N'C:\Users\cmiller6\hotrodbike_black_small.gif', SINGLE_BLOB) AS ProductPhoto
GO

It should be noted the sqlblob2file.ps1, file2sqlblob.ps1, OpenRowSet scripts will also work against filestreams columns. Filestreams support both the old T-SQL blob read/write syntax as well as the Win32 syntax which we will look at next.

FileStreams

Using the MSDN documentation, FILESTREAM Data in SQL Server 2008 (ADO.NET), as a guide I will loosely adapted the C# examples to PowerShell. We will again connect to the same AdventureWorks2008 database used in the blob demonstration. Note: Filestream columns are only supported in SQL Server 2008 or higher. There’s a bit of setup required to enable filestream on a SQL Server 2008 or higher instance. The SQL Server installation provides the option to configure filestream on install. If you didn’t turn on FileStream during setup, configuration is fairly simple and well documented in the Books Online topic, Getting Started with FileStream.

Because we will also be writing data to a filestream we’ll setup a separate database and table as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE DATABASE testFS
GO
ALTER DATABASE testFS
ADD FILEGROUP FileStreamGroup
CONTAINS FILESTREAM
GO
ALTER DATABASE testFS
ADD FILE(NAME = testFS_FileStream, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\testFS_FileStream.fs')
TO FILEGROUP FileStreamGroup
GO
CREATE TABLE [dbo].[Document2](
	[FileName] [NVARCHAR](50) NOT NULL,
	[Document] [VARBINARY](MAX) FILESTREAM  NULL,
	[rowguid] [UNIQUEIDENTIFIER] ROWGUIDCOL  NOT NULL UNIQUE DEFAULT NEWID()
)

The following, script called filestream2File.ps1 demonstrates how to read data from a filestream and write the bytes to a file on the file system:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
$server = "Z002\sql2k8"
$database = "AdventureWorks2008"
$query = "SELECT TOP(10) Document.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT(), Title + FileExtension AS FileName FROM Production.Document WHERE FileExtension = '.doc'"
$dirPath = "C:\Users\u00\"
 
$connection=new-object System.Data.SqlClient.SQLConnection
$connection.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $server,$database
$connection.Open()
$command=new-object system.Data.SqlClient.SqlCommand("",$connection)
$command.CommandTimeout=120
$tran = $connection.BeginTransaction([System.Data.IsolationLevel]'ReadCommitted')
$command.Transaction = $tran
$command.CommandText = $query
$reader = $command.ExecuteReader()
while ($reader.Read())
{
    $path = $reader.GetString(0)
    [byte[]]$transactionContext = $reader.GetSqlBytes(1).Buffer
    $filepath = "$dirPath{0}" -f $reader.GetValue(2)
 
    $fileStream = new-object System.Data.SqlTypes.SqlFileStream($path,[byte[]]$reader.GetValue(1), [System.IO.FileAccess]'Read', [System.IO.FileOptions]'SequentialScan', 0)
    $buffer = new-object byte[] $fileStream.Length
    $fileStream.Read($buffer,0,$fileStream.Length)
    $fileStream.Close()
    [System.IO.File]::WriteAllBytes($filepath,$buffer)
 
}
 
$reader.Close()
$tran.Commit()
$connection.Close()

And finally the script, file2Filestream.ps1 demonstrates how to write a file from the file system to a filestream.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
$server = "Z002\sql2k8"
$database = "testFS"
$filePath = "C:\Users\u00\racer02_green_small.gif"
$documentName =  get-childitem $filePath | select -ExpandProperty Name
$query = "INSERT dbo.[Document2] OUTPUT Inserted.Document.PathName() VALUES ('{0}',CAST('' as varbinary(max)),newid ())" -f $documentName
 
$fs = new-object System.IO.FileStream($filePath,[System.IO.FileMode]'Open',[System.IO.FileAccess]'Read')
$buffer = new-object byte[] -ArgumentList $fs.Length
$fs.Read($buffer, 0, $buffer.Length)
$fs.Close()
 
$connection=new-object System.Data.SqlClient.SQLConnection
$connection.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $server,$database
$connection.Open()
$command=new-object system.Data.SqlClient.SqlCommand("",$connection)
$command.CommandTimeout=120
$tran = $connection.BeginTransaction([System.Data.IsolationLevel]'ReadCommitted')
$command.Transaction = $tran
$command.CommandText = $query
 
$reader = $command.ExecuteReader([System.Data.CommandBehavior]'SingleRow')
$reader.Read()
$path = $reader.GetString(0)
$reader.Close()
 
$command2=new-object system.Data.SqlClient.SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()",$connection)
$command2.Transaction = $tran
$reader = $command2.ExecuteReader([System.Data.CommandBehavior]'SingleRow')
$reader.Read()
$fileStream = new-object System.Data.SqlTypes.SqlFileStream($path,[byte[]]$reader.GetSqlBytes(0).Buffer, [System.IO.FileAccess]'Write', [System.IO.FileOptions]'SequentialScan', 0)
$fileStream.Write($buffer, 0, $buffer.Length)
$fileStream.Close()
$reader.Close()
$tran.Commit()
$connection.Close()

Writing files to a filestream using Win 32 is somewhat odd compared to traditional blobs and requires some explanation. You first need to insert an empty record in order to get back a reference to the PathName which is an internal representation of the filestream path. Notice I’m inserting a record and returning the filepath using the Output option. Once you have a reference the path you also need perform any access of filestream via Win 32 in a transaction and so the second call to the database to get the GET_FILESTREAM_TRANSACTION_CONTEXT.

Comments

Posted by Anonymous on 11 May 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, T-SQL Tuesday #006 Blobs, FileStream and PowerShell - Chad Miller         [sqlservercentral.com]        on Topsy.com

Posted by Anonymous on 18 May 2010

Pingback from  Run a Database Dump and Keep Your Company Safe | Dump DB

Leave a Comment

Please register or log in to leave a comment.