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.
