Article
12/05/2014

FILESTREAM Support (ODBC)

ODBC in SQL Server Native Client supports the enhanced FILESTREAM feature. For more information about this feature, see FILESTREAM Support.

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 Transact-SQL 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;
}

FILESTREAM Support (ODBC)

Example

Read

Insert

Helper Routines

See Also

Other Resources

Additional resources