Share via


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