--- title: "FILESTREAM Support (ODBC) | Microsoft Docs" ms.custom: "" ms.date: "03/17/2017" ms.prod: sql ms.prod_service: "database-engine" ms.reviewer: "" ms.technology: native-client ms.topic: "reference" helpviewer_keywords: - "FILESTREAM [SQL Server], ODBC" - "ODBC, FILESTREAM support" ms.assetid: 87982955-1542-4551-9c06-447ffe8193b9 author: markingmyname ms.author: maghan --- # FILESTREAM Support (ODBC) [!INCLUDE[appliesto-ss-xxxx-xxxx-xxx-md](../../../includes/appliesto-ss-xxxx-xxxx-xxx-md.md)] ODBC in [!INCLUDE[ssNoVersion](../../../includes/ssnoversion-md.md)] Native Client supports the enhanced FILESTREAM feature. For more information about this feature, see [FILESTREAM Support](../../../relational-databases/native-client/features/filestream-support.md). For a sample demonstrating ODB support for FILESTREAM, see [Send and Receive Data Incrementally with FILESTREAM (ODBC)](../../../relational-databases/native-client-odbc-how-to/send-and-receive-data-incrementally-with-filestream-odbc.md). To send and receive **varbinary(max)** values greater than 2 GB, an application must bind parameters by using SQLBindParameter with *ColumnSize* set to **SQL_SS_LENGTH_UNLIMITED**, and set the contents of *StrLen_or_IndPtr* to **SQL_DATA_AT_EXEC** before SQLExecDirect or SQLExecute. As with any data-at-execution parameter, the data will be supplied with SQLParamData and SQLPutData. You can call SQLGetData to fetch data in chunks for a FILESTREAM column if the column is not bound with SQLBindCol. You can update FILESTREAM data if it is bound with SQLBindCol. If you call SQLFetch on a bound column, you will receive a "data truncated" warning if the buffer is not large enough to hold the entire value. Ignore this warning and update the data in this bound column with SQLParamData and SQLPutData calls. You can update FILESTREAM data by using SQLSetPos if it is bound with SQLBindCol. ## Example FILESTREAM columns behave exactly like **varbinary(max)** columns, but without a size limit. They are bound as SQL_VARBINARY. (SQL_LONGVARBINARY is used with image columns, and there are restrictions on this type. For example, SQL_LONGVARBINARY connot be used as an output parameter.) The following examples show direct NTFS access for FILESTREAM columns. These examples assume that the following [!INCLUDE[tsql](../../../includes/tsql-md.md)] code has been executed in the database: ``` CREATE TABLE fileStreamDocs( id uniqueidentifier ROWGUIDCOL NOT NULL UNIQUE, author varchar(64), document VARBINARY(MAX) FILESTREAM NULL) ``` ### Read ``` void selectFilestream (LPCWSTR dstFilePath) { SQLRETURN r; SQLCHAR transactionToken[1024]; SQLWCHAR srcFilePath[1024]; SQLINTEGER cbTransactionToken, cbsrcFilePath; // The GUID columns must be visible to the query, // even if it is not used r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("select GET_FILESTREAM_TRANSACTION_CONTEXT(); \ select TOP(1) id, document.PathName() \ from fileStreamDocs WHERE author = 'Chris Lee'"), SQL_NTS); if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) { ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1); } r = SQLFetch(hstmt); if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) { ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1); } r = SQLGetData(hstmt, 1, SQL_C_BINARY, transactionToken, sizeof(transactionToken), &cbTransactionToken); if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) { ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1); } r = SQLMoreResults(hstmt); if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) { ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1); } if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) { ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1); } r = SQLFetch(hstmt); r = SQLGetData(hstmt, 2, SQL_C_TCHAR, srcFilePath, sizeof(srcFilePath), &cbsrcFilePath); if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) { ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1); } if (!copyFileFromSql(srcFilePath, dstFilePath, transactionToken, cbTransactionToken)) { DeleteFile(dstFilePath); } r = SQLTransact(henv, hdbc, SQL_ROLLBACK); if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) { ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1); } } ``` ### Insert ``` void insertFilestream(LPCWSTR srcFilePath) { SQLRETURN r; SQLCHAR transactionToken[64]; SQLWCHAR dstFilePath[1024]; SQLINTEGER cbTransactionToken, cbDstFilePath; SQLUSMALLINT mode; r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("insert into fileStreamDocs (id, author, document)\ output Get_Filestream_Transaction_Context(), inserted.document.PathName() \ values (newid(), 'Chris Lee', convert(varbinary, '**Temp**')) "), SQL_NTS); if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) { ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1); } r = SQLFetch(hstmt); if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) { ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1); } r = SQLGetData(hstmt, 1, SQL_C_BINARY, transactionToken, sizeof(transactionToken), &cbTransactionToken); if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) { ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1); } r = SQLGetData(hstmt, 2, SQL_C_TCHAR, dstFilePath, sizeof(dstFilePath), &cbDstFilePath); if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) { ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1); } r = SQLCloseCursor(hstmt); if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) { ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1); } if (copyFileToSql( srcFilePath, dstFilePath, transactionToken, cbTransactionToken)) { mode = SQL_COMMIT; } else { mode = SQL_ROLLBACK; } r = SQLTransact(henv, hdbc, mode); if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) { ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1); } } ``` ### Helper Routines ``` #define COPYBUFFERSIZE 4096 BOOL copyFileContents (HANDLE srcHandle, HANDLE dstHandle) { BYTE buffer[COPYBUFFERSIZE]; DWORD bytesRead, bytesWritten; BOOL r; do { r = ReadFile(srcHandle, buffer, COPYBUFFERSIZE, &bytesRead,NULL); if (bytesRead == 0) { return r; } r = WriteFile(dstHandle, buffer, bytesRead, &bytesWritten, NULL); if (bytesWritten == 0) { return r; } } while (TRUE); } BOOL copyFileToSql(LPCWSTR srcFilePath, LPCWSTR dstFilePath, LPBYTE transactionToken, SQLINTEGER cbTransactionToken) { BOOL r; HANDLE srcHandle, dstHandle; unsigned int NtStatus; srcHandle = CreateFile( srcFilePath, GENERIC_READ, FILE_SHARE_READ, NULL, OPEN_EXISTING, FILE_FLAG_SEQUENTIAL_SCAN, NULL); if (srcHandle == INVALID_HANDLE_VALUE) { return FALSE; } dstHandle = OpenSqlFilestream( dstFilePath, Write, 0, transactionToken, cbTransactionToken, 0); if (dstHandle == INVALID_HANDLE_VALUE) { NtStatus = GetLastError(); r = CloseHandle(srcHandle); return FALSE; } //copy file r = copyFileContents(srcHandle, dstHandle); CloseHandle(srcHandle); CloseHandle(dstHandle); return r; } BOOL copyFileFromSql(LPCWSTR srcFilePath, LPCWSTR dstFilePath, LPBYTE transactionToken, SQLINTEGER cbTransactionToken) { BOOL r; HANDLE srcHandle, dstHandle; unsigned int NtStatus; srcHandle = OpenSqlFilestream( srcFilePath, Read, 0, transactionToken, cbTransactionToken, 0); if (srcHandle == INVALID_HANDLE_VALUE) { return FALSE; } dstHandle = CreateFile( dstFilePath, GENERIC_WRITE, 0, NULL, OPEN_ALWAYS, FILE_ATTRIBUTE_NORMAL, NULL); if (dstHandle == INVALID_HANDLE_VALUE) { CloseHandle(srcHandle); return FALSE; } r = copyFileContents(srcHandle, dstHandle); CloseHandle(srcHandle); CloseHandle(dstHandle); return r; } ``` ## See Also [SQL Server Native Client Programming](../../../relational-databases/native-client/sql-server-native-client-programming.md)