ODBC Call | Client TDS Request | Server TDS Response |
SQLPrepare
SQLExecute |
sp_cursorprepexec | Returns cursor handle to client |
For Each ROW
SQLFetch |
sp_cursoroption
TXTPTR_ONLY (1)
sp_cursorfetch |
Returns row data for all columns and only TEXTPTR values for blob column |
For Each Column
SQLGetData |
sp_cursor
REFRESH (40)
or sp_cursoroption TEXT_DATA (3) sp_cursor REFRESH (40) |
Returns row data for all columns and only TEXTPTR values for blob column
If the column to be retrieved is a blob (TXTPTR ONLY) return TEXT_DATA and refresh row data for all columns |
select iID, strData, tData, dtEntry from tblTest
This results in the following behavior:
sqlrc = SQLBindCol(hstmt, 1, SQL_C_BINARY, bBlob,
1024
, &iBlobState);
// Initial 1K for sample
sqlrc = SQLBindCol(hstmt, 2, SQL_C_WCHAR, strData, 64, &istrDataState);
sqlrc = SQLBindCol(hstmt, 3, SQL_INTEGER, &iData, 0, NULL);
sqlrc = SQLBindCol(hstmt, 4, SQL_C_WCHAR, strData2, 64, NULL);
sqlrc = SQLFetch (hstmt);
// Retrieves data from SQL Server, streaming entire TEXT_DATA, you want this to work the majority of the time !!
if(true == truncated)
{
// Allocate new buffer as needed
sqlrc = SQLBindCol(hstmt, 1, SQL_C_BINARY, bBlob, iBlobState, &iBlobState);
sqlrc = SQLSetPos(hstmt, iRow, SQL_REFRESH, SQL_LOCK_NO_CHANGE);
// Round trip to SQL Server, streaming entire TEXT_DATA – Avoid this path as much as possible to optimize performance
}
// Final storage, local copy instead of across the wire retrieval for actual length
memcpy(bFinal, bBlob, iBlobState);
The optimization technique allows you to retrieve the majority of rows in a single network trip. Using SQLGetData for data retrieval encounters
the additional overhead.
Binding Array
ODBC allows binding of an array for a column and a multiple row fetch operation. This can also be helpful but requires a bit more work to retrieve the truncated blob data. In order to retrieve a specific row that encountered the short transfer, clear current bindings, set the rowset position for the cursor and use SQLGetData.
BYTE bBlob[2][64];
SQLINTEGER iBlobState[2];
WCHAR strData[2][64];
int iData;
WCHAR strData2[2][64];
sqlrc = SQLBindCol(*hstmt, 1, SQL_C_BINARY, bBlob, 64, iBlobState);
sqlrc = SQLBindCol(*hstmt, 2, SQL_C_WCHAR, strData, 128, NULL);
sqlrc = SQLBindCol(*hstmt, 3, SQL_INTEGER, &iData, 0, NULL);
sqlrc = SQLBindCol(*hstmt, 4, SQL_C_WCHAR, strData2, 128, NULL);
sqlrc = SQLFetch(*hstmt);
if (SQL_SUCCESS_WITH_INFO && iBlobLen is short)
{
//======================================================================a
// Clear all bindings so we don't have any references to mess up memory
//
// NOTE: This means the SQLGetData can be different from the original fetch data
//======================================================================
sqlrc = SQLBindCol(*hstmt, 1, SQL_C_BINARY, NULL, 0, NULL);
sqlrc = SQLBindCol(*hstmt, 2, SQL_C_WCHAR, NULL, 0, NULL);
sqlrc = SQLBindCol(*hstmt, 3, SQL_INTEGER, NULL, 0, NULL);
sqlrc = SQLBindCol(*hstmt, 4, SQL_C_WCHAR, NULL, 0, NULL);
// For just the blob row
sqlrc = SQLSetPos(*hstmt, 2, SQL_POSITION, SQL_LOCK_NO_CHANGE); // Update position to row with short transfer of blob
BYTE bBlob2[64] = {};
sqlrc = SQLGetData(*hstmt, 1, SQL_C_BINARY, bBlob2, 64, NULL); // Get blob that was short into new buffer not original array
sqlrc = SQLSetPos(*hstmt, rows_fetched, SQL_POSITION, SQL_LOCK_NO_CHANGE); // need to call this after to advance to next set of rows as on next SQLFetch call
}
// FIX BINDINGS AND LOOP ON NEXT FETCH GROUP
Make sure you have the correct driver version installed:
http://blogs.msdn.com/b/psssql/archive/2015/07/14/getting-the-latest-sql-server-native-client.a...
Bob Dorr - Principal SQL Server Escalation Engineer
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.