/**********************************************************************
* 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
