Managing Filestream Data Win32

Managing FILESTREAM Data by Using Win32

Page 1 of 9

©2009 Microsoft Corporation. All rights reserved.

SQL Server 2008 Books Online (September 2009)

Managing FILESTREAM Data by Using Win32 You can use Win32 to read and write data to a FILESTREAM BLOB. The following steps are required: 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. Note:

The examples in this topic require the FILESTREAM-enabled database and table that are created in How To: Create a FILESTREAM-Enabled Database [ http://msdn.microsoft.com/enus/library/cc645585.aspx ] and How to: Create a Table for Storing FILESTREAM Data [ http://msdn.microsoft.com/en-us/library/cc645583.aspx ] .

Reading the FILESTREAM File Path Each cell in a FILESTREAM table has a file path that is associated with it. To read the path, use the PathName property of a varbinary(max) column in a Transact-SQL statement. The following example shows how to read the file path of a varbinary(max) column. Copy Code DECLARE @filePath varchar(max) SELECT @filePath = Chart.PathName() FROM Archive.dbo.Records WHERE SerialNumber = 3 PRINT @filepath

Reading the Transaction Context To obtain the current transaction context, use the Transact-SQL GET_FILESTREAM_TRANSACTION_CONTEXT() [ http://msdn.microsoft.com/enus/library/bb934014.aspx ] function. The following example shows how to begin a transaction and read the current transaction context. Copy Code DECLARE @txContext varbinary(max) BEGIN TRANSACTION SELECT @txContext = GET_FILESTREAM_TRANSACTION_CONTEXT() PRINT @txContext COMMIT

Obtaining a Win32 File Handle To obtain a Win32 file handle, call the OpenSqlFilestream API. This API is exported from the sqlncli.dll file. The returned handle can be passed to any of the following Win32 APIs: ReadFile [ http://go.microsoft.com/fwlink/?LinkId=86422 ] , WriteFile [ http://go.microsoft.com/fwlink/? LinkId=86423 ] , TransmitFile [ http://go.microsoft.com/fwlink/?LinkId=86424 ] , SetFilePointer [ http://go.microsoft.com/fwlink/?LinkId=86425 ] , SetEndOfFile [ http://go.microsoft.com/fwlink/? LinkId=86426 ] , or FlushFileBuffers [ http://go.microsoft.com/fwlink/?LinkId=86427 ] . The following

http://msdn.microsoft.com/en-us/library/cc645940(printer).aspx

10/22/2009

Managing FILESTREAM Data by Using Win32

Page 2 of 9

examples show you how to obtain a Win32 File handle and use it to read and write data to a FILESTREAM BLOB. CSharp using using using using using using using

Copy Code System.IO; System; System.Collections.Generic; System.Text; System.Data; System.Data.SqlClient; System.Data.SqlTypes;

namespace FILESTREAM { class Program { static void Main(string[] args) { SqlConnection sqlConnection = new SqlConnection( "Integrated Security=true;server=(local)"); SqlCommand sqlCommand = new SqlCommand(); sqlCommand.Connection = sqlConnection; try { sqlConnection.Open(); //The first task is to retrieve the file path //of the SQL FILESTREAM BLOB that we want to //access in the application. sqlCommand.CommandText = "SELECT Chart.PathName()" + " FROM Archive.dbo.Records" + " WHERE SerialNumber = 3"; String filePath = null; Object pathObj = sqlCommand.ExecuteScalar(); if (DBNull.Value != pathObj) filePath = (string)pathObj; else { throw new System.Exception( "Chart.PathName() failed" + " to read the path name " + " for the Chart column."); } //The next task is to obtain a transaction //context. All FILESTREAM BLOB operations //occur within a transaction context to //maintain data consistency. //All SQL FILESTREAM BLOB access must occur in //a transaction. MARS-enabled connections //have specific rules for batch scoped transactions, //which the Transact-SQL BEGIN TRANSACTION statement //violates. To avoid this issue, client applications //should use appropriate API facilities for transaction management, //management, such as the SqlTransaction class. SqlTransaction transaction = sqlConnection.BeginTransaction("mainTranac tion"); sqlCommand.Transaction = transaction; sqlCommand.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";

http://msdn.microsoft.com/en-us/library/cc645940(printer).aspx

10/22/2009

Managing FILESTREAM Data by Using Win32

Page 3 of 9

Object obj = sqlCommand.ExecuteScalar(); byte[] txContext = (byte[])obj; //The next step is to obtain a handle that //can be passed to the Win32 FILE APIs. SqlFileStream sqlFileStream = new SqlFileStream(filePath, txContext, Fi leAccess.ReadWrite); byte[] buffer = new byte[512]; int numBytes = 0; //Write the string, "EKG data." to the FILESTREAM BLOB. //In your application this string would be replaced with //the binary data that you want to write. string someData = "EKG data."; Encoding unicode = Encoding.GetEncoding(0); sqlFileStream.Write(unicode.GetBytes(someData.ToCharArray()), 0, someData.Length); //Read the data from the FILESTREAM //BLOB. sqlFileStream.Seek(0L, SeekOrigin.Begin); numBytes = sqlFileStream.Read(buffer, 0, buffer.Length); string readData = unicode.GetString(buffer); if (numBytes != 0) Console.WriteLine(readData); //Because reading and writing are finished, FILESTREAM //must be closed. This closes the c# FileStream class, //but does not necessarily close the the underlying //FILESTREAM handle. sqlFileStream.Close(); //The final step is to commit or roll back the read and write //operations that were performed on the FILESTREAM BLOB. sqlCommand.Transaction.Commit(); } catch (System.Exception ex) { Console.WriteLine(ex.ToString()); } finally { sqlConnection.Close(); } return; } } }

VisualBasic Imports Imports Imports Imports Imports Imports

Copy Code

System.IO System System.Collections.Generic System.Text System.Data System.Data.SqlClient

http://msdn.microsoft.com/en-us/library/cc645940(printer).aspx

10/22/2009

Managing FILESTREAM Data by Using Win32

Page 4 of 9

Imports System.Data.SqlTypes Module Module1 Public Sub Main(ByVal args As String()) ' Dim sqlConnection As New SqlConnection("Integrated Security=true;serve r=(local)") Dim sqlConnection As New SqlConnection("Integrated Security=true;server=kellyre yue\MSSQL1") Dim sqlCommand As New SqlCommand() sqlCommand.Connection = sqlConnection Try sqlConnection.Open() 'The first task is to retrieve the file path 'of the SQL FILESTREAM BLOB that we want to 'access in the application. sqlCommand.CommandText = "SELECT Chart.PathName()" + " FROM Archive.dbo.Rec ords" + " WHERE SerialNumber = 3" Dim filePath As String = Nothing Dim pathObj As Object = sqlCommand.ExecuteScalar() If Not pathObj.Equals(DBNull.Value) Then filePath = DirectCast(pathObj, String) Else Throw New System.Exception("Chart.PathName() failed" + " to read the pa th name " + " for the Chart column.") End If 'The next task is to obtain a transaction 'context. All FILESTREAM BLOB operations 'occur within a transaction context to 'maintain data consistency. 'All SQL FILESTREAM BLOB access must occur in 'a transaction. MARS-enabled connections 'have specific rules for batch scoped transactions, 'which the Transact-SQL BEGIN TRANSACTION statement 'violates. To avoid this issue, client applications 'should use appropriate API facilities for transaction management, 'management, such as the SqlTransaction class. Dim transaction As SqlTransaction = sqlConnection.BeginTransaction("mainTra naction") sqlCommand.Transaction = transaction sqlCommand.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()" Dim obj As Object = sqlCommand.ExecuteScalar() Dim txContext As Byte() = Nothing Dim contextLength As UInteger If Not obj.Equals(DBNull.Value) Then txContext = DirectCast(obj, Byte()) contextLength = txContext.Length() Else Dim message As String = "GET_FILESTREAM_TRANSACTION_CONTEXT() failed" Throw New System.Exception(message) End If 'The next step is to obtain a handle that 'can be passed to the Win32 FILE APIs. Dim sqlFileStream As New SqlFileStream(filePath, txContext, FileAccess.Read Write) Dim buffer As Byte() = New Byte(511) {}

http://msdn.microsoft.com/en-us/library/cc645940(printer).aspx

10/22/2009

Managing FILESTREAM Data by Using Win32

Page 5 of 9

Dim numBytes As Integer = 0 'Write the string, "EKG data." to the FILESTREAM BLOB. 'In your application this string would be replaced with 'the binary data that you want to write. Dim someData As String = "EKG data." Dim unicode As Encoding = Encoding.GetEncoding(0) sqlFileStream.Write(unicode.GetBytes(someData.ToCharArray()), 0, someData.L ength) 'Read the data from the FILESTREAM 'BLOB. sqlFileStream.Seek(0, SeekOrigin.Begin) numBytes = sqlFileStream.Read(buffer, 0, buffer.Length) Dim readData As String = unicode.GetString(buffer) If numBytes <> 0 Then Console.WriteLine(readData) End If 'Because reading and writing are finished, FILESTREAM 'must be closed. This closes the c# FileStream class, 'but does not necessarily close the the underlying 'FILESTREAM handle. sqlFileStream.Close() 'The final step is to commit or roll back the read and write 'operations that were performed on the FILESTREAM BLOB. sqlCommand.Transaction.Commit() Catch ex As System.Exception Console.WriteLine(ex.ToString()) Finally sqlConnection.Close() End Try Return End Sub End Module

ManagedCPlusPlus

Copy Code

#include <windows.h> #include <sql.h> #include<sqltypes.h> #include<sqlext.h> #include <stdio.h> #include <sqlncli.h> #define COPYBUFFERSIZE 4096 /// <summary> ///This class iterates though the ODBC error queue and prints all of the ///accumulated error messages to the console. /// class ODBCErrors { private: int m_iLine; SQLSMALLINT m_type; SQLHANDLE m_handle;

//Source code line on which the error occurred //Type of handle on which the error occurred //ODBC handle on which the error occurred

public:

http://msdn.microsoft.com/en-us/library/cc645940(printer).aspx

10/22/2009

Managing FILESTREAM Data by Using Win32

Page 6 of 9

/// <summary> ///Default constructor for the ODBCErrors class /// ODBCErrors() { m_iLine = -1; m_type = 0; m_handle = SQL_NULL_HANDLE; } /// <summary> ///Constructor for the ODBCErrors class /// /// <param name="iLine"> /// This parameter is the source code line /// at which the error occurred. /// /// <param name="type"> /// This parameter is the type of ODBC handle passed in /// the next parameter. /// /// <param name="handle"> /// This parameter is the handle on which the error occurred. /// ODBCErrors(int { m_iLine = m_type = m_handle = }

iLine, SQLSMALLINT type, SQLHANDLE handle) iLine; type; handle;

///<summary> /// This method iterates though the error stack for the handle passed /// into the constructor and displays those errors on the console. /// void Print() { SQLSMALLINT SQLINTEGER SQLTCHAR SQLRETURN

i = 0, len = 0; native; state[9], text[256]; sqlReturn = SQL_SUCCESS;

if ( m_handle == SQL_NULL_HANDLE ) { wprintf_s(TEXT("The error handle is not a valid handle.\n"), m_iLine); return; } wprintf_s(TEXT("Error Line(%d)\n"), m_iLine); while( sqlReturn == SQL_SUCCESS ) { len = 0; sqlReturn = SQLGetDiagRec( m_type, m_handle, ++i, state, &native, text, sizeof(text)/sizeof(SQLTCHAR), &len); if ( SQL_SUCCEEDED(sqlReturn) ) wprintf_s(TEXT("Error(%d, %ld, %s) : %s\n"), i, native, state, text); } }

http://msdn.microsoft.com/en-us/library/cc645940(printer).aspx

10/22/2009

Managing FILESTREAM Data by Using Win32

Page 7 of 9

};

BOOL CopyFileToSQL(LPTSTR srcFilePath, LPTSTR dstFilePath, LPBYTE transactionToken, SQL INTEGER cbTransactionToken) { BOOL bRetCode = FALSE; HANDLE srcHandle = INVALID_HANDLE_VALUE; HANDLE dstHandle = INVALID_HANDLE_VALUE; BYTE buffer[COPYBUFFERSIZE] = { 0 }; TCHAR TCHAR TCHAR TCHAR

*szErrMsgSrc *szErrMsgDst *szErrMsgRead *szErrMsgWrite

= = = =

TEXT("Error TEXT("Error TEXT("Error TEXT("Error

opening opening reading writing

source file."); destFile file."); source file."); SQL file.");

try { if ( (srcHandle = CreateFile( srcFilePath, GENERIC_READ, FILE_SHARE_READ, NULL, OPEN_EXISTING, FILE_FLAG_SEQUENTIAL_SCAN, NULL)) == INVALID_HANDLE_VALUE ) throw szErrMsgSrc; if ( (dstHandle = OpenSqlFilestream( dstFilePath, Write, 0, transactionToken, cbTransactionToken, 0)) == INVALID_HANDLE_VALUE) throw szErrMsgDst; DWORD bytesRead = 0; DWORD bytesWritten = 0; do { if ( ReadFile(srcHandle, buffer, COPYBUFFERSIZE, &bytesRead, NULL) == 0 ) throw szErrMsgRead; if (bytesRead > 0) { if ( WriteFile(dstHandle, buffer, bytesRead, &bytesWritten, NULL) == 0 ) throw szErrMsgWrite; } } while (bytesRead > 0); bRetCode = TRUE; } catch( TCHAR *szErrMsg ) { wprintf_s(szErrMsg); bRetCode = FALSE; } if ( srcHandle != INVALID_HANDLE_VALUE ) CloseHandle(srcHandle); if ( dstHandle != INVALID_HANDLE_VALUE ) CloseHandle(dstHandle); return bRetCode; }

http://msdn.microsoft.com/en-us/library/cc645940(printer).aspx

10/22/2009

Managing FILESTREAM Data by Using Win32

Page 8 of 9

void main() { TCHAR *sqlDBQuery = TEXT("INSERT INTO Archive.dbo.Records(Id, SerialNumber, Chart)") TEXT(" OUTPUT GET_FILESTREAM_TRANSACTION_CONTEXT(), inserted.Chart.PathName()") TEXT("VALUES (newid (), 5, CONVERT(VARBINARY, '**Temp**'))"); SQLCHAR transactionToken[32]; SQLHANDLE henv = SQL_NULL_HANDLE; SQLHANDLE hdbc = SQL_NULL_HANDLE; SQLHANDLE hstmt = SQL_NULL_HANDLE; try { //These statements Initialize ODBC for the client application and //connect to the database. if ( SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv) != SQL_SUCCESS ) throw new ODBCErrors(__LINE__, SQL_HANDLE_ENV, henv); if ( SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3, NULL) != SQ L_SUCCESS ) throw new ODBCErrors(__LINE__, SQL_HANDLE_ENV, henv); if ( SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc) != SQL_SUCCESS ) throw new ODBCErrors(__LINE__, SQL_HANDLE_ENV, henv); //This code assumes that the dataset name "Sql Server FILESTREAM" //has been previously created on the client computer system. An //ODBC DSN is created with the ODBC Data Source item in //the Windows Control Panel. if ( SQLConnect(hdbc, TEXT("Sql Server FILESTREAM"), SQL_NTS, NULL, 0, NULL, 0) <= 0 ) throw new ODBCErrors(__LINE__, SQL_HANDLE_DBC, hdbc); //FILESTREAM requires that all read and write operations occur //within a transaction. if ( SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_IS_UINTEGER) != SQL_SUCCESS ) throw new ODBCErrors(__LINE__, SQL_HANDLE_DBC, hdbc); if ( SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt) != SQL_SUCCESS ) throw new ODBCErrors(__LINE__, SQL_HANDLE_DBC, hdbc); if ( SQLExecDirect(hstmt, sqlDBQuery, SQL_NTS) != SQL_SUCCESS ) throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt); //Retrieve the transaction token. if ( SQLFetch(hstmt) != SQL_SUCCESS ) throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt); SQLINTEGER cbTransactionToken = sizeof(transactionToken); if ( SQLGetData(hstmt, 1, SQL_C_BINARY, transactionToken, sizeof(transactionToken), &cbTransactionToken) != SQL_SUCCESS ) throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt); //Retrieve the file path for the inserted record. TCHAR dstFilePath[1024]; SQLINTEGER cbDstFilePath; if ( SQLGetData(hstmt, 2, SQL_C_TCHAR, dstFilePath, sizeof(dstFilePath), &cbDst FilePath) != SQL_SUCCESS )

http://msdn.microsoft.com/en-us/library/cc645940(printer).aspx

10/22/2009

Managing FILESTREAM Data by Using Win32

Page 9 of 9

throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt); if ( SQLCloseCursor(hstmt) != SQL_SUCCESS ) throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt); SQLUSMALLINT mode = SQL_ROLLBACK; if ( CopyFileToSQL( TEXT("C:\\Users\\Data\\chart1.jpg"), dstFilePath, transactionToken, cbTransactionToken) == TRUE ) mode = SQL_COMMIT; SQLTransact(henv, hdbc, mode); } catch(ODBCErrors *pErrors) { pErrors->Print(); delete pErrors; } if ( hstmt != SQL_NULL_HANDLE ) SQLFreeHandle(SQL_HANDLE_STMT, hstmt); if ( hdbc != SQL_NULL_HANDLE ) SQLDisconnect(hdbc); if ( hdbc != SQL_NULL_HANDLE ) SQLFreeHandle(SQL_HANDLE_DBC, hdbc); if ( henv != SQL_NULL_HANDLE ) SQLFreeHandle(SQL_HANDLE_ENV, henv); }

See Also Reference OpenSqlFilestream API [ http://msdn.microsoft.com/en-us/library/bb933972.aspx ] Concepts Win32 and Transact-SQL Conflicts [ http://msdn.microsoft.com/en-us/library/cc645941.aspx ] Using FILESTREAM Storage in Client Applications [ http://msdn.microsoft.com/enus/library/bb933877.aspx ] FSCTL_SQL_FILESTREAM_FETCH_OLD_CONTENT [ http://msdn.microsoft.com/enus/library/cc627407.aspx ] Other Resources Designing and Implementing FILESTREAM Storage [ http://msdn.microsoft.com/enus/library/bb895234.aspx ] Help and Information Getting SQL Server 2008 Assistance [ http://msdn.microsoft.com/en-us/library/ms166016.aspx ]

Tags:

Community Content

http://msdn.microsoft.com/en-us/library/cc645940(printer).aspx

10/22/2009