Output parameter streaming feature in SQL Native client 11
Recently we had a case where customer wanted to stream output parameter using SNAC 11. Rather than let the good research go to waste, we're posting the steps here.
It’s not optimum or sometimes not even possible to define large buffer when the parameter that is being fetched from the SQL server is very large, because earlier versions ODBC Driver Manager version 3.8 does not support retrieving the large output parameter in small chunks multiple times.
Beginning from ODBC Driver Manager 3.8 & SQL Native Client Version 11.0 supports a new feature called Output Parameter Streaming. Applications memory footprint size can be reduced using this feature, by invoking SQLGetData using small size buffer multiple times to retrieve large output parameter value. This feature is supported by ODBC Driver manager version 3.8 and SQL Native client version 11.0 or higher only.
Lets follow these steps to implement this feature using sample application:
1. Download and install the latest version of Windows platform SDK https://msdn.microsoft.com/en-us/windows/bb980924
2. Install SQL Native Client 11.0 on the application machine or workstation - https://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9
3. Create a sample table and import some binary data in SQL Server ( For example, SQL 2008, SQL 2008 R2)
CREATE TABLE TableImage(Document varbinary(max)) INSERT INTO TableImage(Document) SELECT * FROM OPENROWSET(BULK N'SomeImage.bmp', SINGLE_BLOB) AS I
4. Create stored procedure that returns image of given ID:
CREATE PROCEDURE [dbo].[SP_TestOutputPara] @Param1 integer,
@Param2 VARBINARY(max) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT @Param2 = [Document] FROM [pubs].[dbo].[TableImage] where [TableImage].[id] = 1
END
GO
5. Define DSN using SQL Native Client Version 11.0 to point to the database that hosts the table and SP created at the above steps 3 & 4
6. The sample code that shows how to bind the streamed output parameter and retrieve the large output parameter multiple times using SQLGetData()
BOOL GetBinaryDataInChunks(SQLUINTEGER idOfPicture, SQLHSTMT hstmt)
{
SQLINTEGER lengthOfPicture=SQL_DATA_AT_EXEC;
SQLPOINTER ParamValuePtr = (SQLPOINTER) 2;
BYTE smallBuffer[2048]; // A very small buffer.
SQLRETURN retcode, retcode2;
// Bind the first parameter (input parameter)
retcode = SQLBindParameter(hstmt,
1, // The first parameter.
SQL_PARAM_INPUT, // Input parameter: The ID_of_picture.
SQL_C_ULONG, // The C Data Type of the ID.
SQL_INTEGER, // The Param-Type of the ID.
0, // ColumnSize is ignored for integer.
0, // DecimalDigits is ignored for integer.
&idOfPicture, // The Address of the buffer for the input parameter.
0, // Buffer length is ignored for integer.
NULL); // This is ignored for integer.
if (retcode != SQL_SUCCESS)
Return FALSE;
// Bind the streamed output parameter.
retcode = SQLBindParameter(hstmt,
2, // The second parameter.
SQL_PARAM_OUTPUT_STREAM, // A streamed output parameter.
SQL_C_BINARY, // The C Data Type of the picture.
SQL_VARBINARY, // The Param-Type of the picture.
0, // ColumnSize: The maximum size of varbinary(max).
0, // DecimalDigits is ignored for binary type.
(SQLPOINTER)2, // ParameterValuePtr: An application-defined. token (this will be returned from SQLParamData).
// In this example, we used the ordinal of the parameter.
0, // This is ignored for streamed output parameters.
&lengthOfPicture); // StrLen_or_IndPtr: The status variable returned.
if (retcode != SQL_SUCCESS)
Return FALSE;
retcode = SQLPrepare(hstmt,(SQLCHAR*) "{call SP_TestOututPara(?, ?)}", SQL_NTS);
if ( retcode == SQL_ERROR )
return FALSE;
//Execute the stored procedure.
retcode = SQLExecute(hstmt);
if ( retcode == SQL_ERROR )
return FALSE;
// Assume that the retrieved picture exists. Use SQLBindCol or SQLGetData to retrieve the result-set.
// Process the result set and move to the streamed output parameters.
retcode = SQLMoreResults( hstmt );
// SQLGetData retrieves and displays the picture in parts. The streamed output parameter is available.
while (retcode == SQL_PARAM_DATA_AVAILABLE)
{
SQLPOINTER token; // Output by SQLParamData.
SQLINTEGER cbLeft; // #bytes remained
retcode = SQLParamData(hstmt, &token);
if ( retcode == SQL_PARAM_DATA_AVAILABLE )
{
do
{
retcode2 = SQLGetData(hstmt,
(UWORD) token, // the value of the token is the ordinal.
SQL_C_BINARY, // The C-type of the picture.
smallBuffer, // A small buffer.
sizeof(smallBuffer), // The size of the buffer.
&cbLeft); // How much data we can get.
//Print the buffer.
cout << smallBuffer << "n";
if (retcode2 == SQL_ERROR)
Return FALSE;
}
while ( retcode2 == SQL_SUCCESS_WITH_INFO );
}
}
return TRUE;
}
Further references:
What are the new features in SQL Native Client Version 11.0 https://msdn.microsoft.com/en-us/library/cc280510(SQL.110).aspx
Retrieving Output parameters - https://msdn.microsoft.com/en-us/library/ms712625(VS.85).aspx
The ODBC 3.8 features supported in code name "Denali" or SNAC 11 - https://blogs.msdn.com/b/sqlnativeclient/archive/2010/11/16/sql-server-code-named-quot-denali-quot-native-client-supporting-odbc-3-8.aspx
Author : Srini(MSFT), SQL Developer Engineer
Reviewed by : Enamul(MSFT), SQL Developer Technical Lead