/**********************************************************************
* FILENAME :    CallSPOutputParameterStreaming
*
* DESCRIPTION :
*               ODBC 3.8 introduces a new way to retrieve procedure
*               output parameters in parts. An application can now call
*               SQLGetData with a small buffer multiple times to retrieve
*               a large parameter value. This approach reduces the
*               application's memory footprint.
*
*               This is is a SQL Server specific example. To run it, you need:
*
*               - Version 1.7.12+ of the SQL Server ODBC driver
*
*               - Create a SQL Server ODBC driver data source that connects to a database in
*                 which you have created the following procedure and table:
*
*                 use mydb
*
*                 CREATE PROCEDURE [dbo].[SP_OutputParameterStreaming] @Param1 VARBINARY(max) OUTPUT
*
*                 AS
*
*                 BEGIN
*
*                 -- SET NOCOUNT ON added to prevent extra result sets from
*
*                 -- interfering with SELECT statements.
*
*                 SET NOCOUNT ON;
*
*                 SELECT @Param1 = [Document]  FROM [mydb].[dbo].[TableImage] where [TableImage].[id]  = 1
*
*                 END
*
*                 CREATE TABLE TableImage(id integer identity, Document varbinary(max))
*                 INSERT INTO TableImage(Document)  SELECT * FROM OPENROWSET(BULK N'photo.jpg', SINGLE_BLOB) AS I
*
*               - Compile the example against the unixODBC Driver Manager included with the
*                 SQL Server ODBC driver rather than one included with your system:
*
*                 $ LANG=C cc -I/usr/local/easysoft/unixODBC/include -L/usr/local/easysoft/unixODBC/lib \
*                     -lodbc CallSPOutputParameterStreaming.c -o CallSPOutputParameterStreaming
*
*/


#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>

#include "util.c"

int main () {
    SQLHENV   henv  = SQL_NULL_HENV;
    SQLHDBC   hdbc  = SQL_NULL_HDBC;
    SQLHSTMT  hstmt = SQL_NULL_HSTMT;
    SQLRETURN retcode, retcode2;
    SQLLEN    lengthofpicture;          // The actual length of the picture
    BYTE      smallbuffer[100];         // A very small buffer
    CHAR      filename[14] = "photo.jpg";
    FILE      *pfile;

    // Allocate an environment handle
    retcode=SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
                henv, SQL_HANDLE_ENV);

    // Notify ODBC that this is an ODBC 3.8 app
    retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
                                    (SQLPOINTER) SQL_OV_ODBC3_80, 0);
    CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_HANDLE_ENV)",
                henv, SQL_HANDLE_ENV);

    // Allocate ODBC connection handle and connect
    retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
                hdbc, SQL_HANDLE_DBC);

    // Connect to data source
    retcode = SQLDriverConnect(hdbc, NULL, "DSN=DATASOURCE;", SQL_NTS,
                             NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
    CHECK_ERROR(retcode, "SQLDriverConnect(DATASOURCE)",
                hdbc, SQL_HANDLE_DBC);

    // Allocate a statement handle
    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
                hstmt, SQL_HANDLE_STMT);

   // Bind the streamed output parameter
   retcode = SQLBindParameter(
         hstmt,
         1,
         SQL_PARAM_OUTPUT_STREAM,   // A streamed output parameter
         SQL_C_BINARY,
         SQL_VARBINARY,
         0,                         // ColumnSize: The maximum size of varbinary(max)
         0,                         // DecimalDigits is ignored for binary type
         (SQLPOINTER)1,             // ParameterValuePtr: An application-defined
                                    // token (this will be returned from SQLParamData)
                                    // In this example, we used the ordinal
                                    // of the parameter
         0,                         // BufferLength is ignored for streamed output parameters
         &lengthofpicture);         // StrLen_or_IndPtr: The status variable returned

   CHECK_ERROR(retcode, "SQLBindParameter()",
                hstmt, SQL_HANDLE_STMT);

   retcode = SQLPrepare(hstmt, "{call SP_OutputParameterStreaming(?)}", SQL_NTS);

   retcode = SQLExecute(hstmt);

   pfile = fopen(filename, "w+b");
   if ( !pfile ) {
    printf( "failed to open '%s' for w+b\n", filename );
    exit( -1 );
   }

   // 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
   if ( retcode != SQL_PARAM_DATA_AVAILABLE )
      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
      SQLLEN cbleft;      // #bytes remained
      retcode = SQLParamData(hstmt, &token);   // Returned token is 2 (according to the binding)
      if ( retcode == SQL_PARAM_DATA_AVAILABLE ) {
         // A do-while loop retrieves the picture in parts
         do {
            retcode2 = SQLGetData(
               hstmt,
               1,          // The value of the token is the ordinal
               SQL_C_BINARY,
               smallbuffer,            // A small buffer
               sizeof(smallbuffer),
	       &cbleft);               // How much data we can get

	    if (cbleft > sizeof(smallbuffer)) // Full buffer
             {
               fwrite(smallbuffer, sizeof(char), sizeof(smallbuffer), pfile);
             }
	     else // Partial buffer on last SQLGetData
             {
               fwrite(smallbuffer, sizeof(char), cbleft, pfile);
             }
         }
         while ( retcode2 == SQL_SUCCESS_WITH_INFO );
      }
   }

   // Close the file
   fflush(pfile);
   fclose(pfile);

exit:

    printf ("\nComplete.\n");

    // Statement
    if (hstmt != SQL_NULL_HSTMT)
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);

    // Connection
    if (hdbc != SQL_NULL_HDBC) {
        SQLDisconnect(hdbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    }

    // Environment
    if (henv != SQL_NULL_HENV)
        SQLFreeHandle(SQL_HANDLE_ENV, henv);

    return 0;
}

See Also


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.