/**********************************************************************
* FILENAME :    UsingMultipleParamArrays.c
*
* DESCRIPTION :
*       	Example to illustrate using a SELECT with
*               two parameter markers and parameter arrays
*
* ODBC USAGE :
* 		SQLSetStmtAttr () 	- SQL_ATTR_PARAMSET_SIZE
* 					  SQL_ATTR_PARAM_STATUS_PTR
* 					  SQL_ATTR_PARAMS_PROCESSED_PTR
* 		SQLBindParameter () - To bind 2 input parameters
* 		SQLBindCol ()		- To bind 5 output paramaters
* 		SQLExecDirect ()	- to execute a SELECT statement
* 		For each parameter in the param arrays:
*	 		SQLFetch ()		- to retrieve the results sets
*	 		SQLMoreResults ()	- to initiate the next parameter
*/

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

#include "util.c"

/*
 * Uses TestTBL1,
 *
 *      PersonID int NOT NULL IDENTITY(1,1),
 *      FirstName varchar(255) NOT NULL,
 *      LastName varchar(255),
 *      Address varchar(255),
 *      City varchar(255)
*/

#define PARAM_ARRAY_SIZE 5

#define FIRSTNAME_LEN 256
#define LASTNAME_LEN 256
#define ADDRESS_LEN 256
#define CITY_LEN 256

int main () {

    SQLHENV   henv  = SQL_NULL_HENV;   // Environment
    SQLHDBC   hdbc  = SQL_NULL_HDBC;   // Connection handle
    SQLHSTMT  hstmt = SQL_NULL_HSTMT;  // Statement handle
    SQLRETURN retcode;

    // Select statements to return person details from TestTBL1 -
    // one with param, one without
    SQLCHAR     *stmt = "SELECT PersonID, FirstName, LastName, Address, City"
                                    "FROM TestTBL1"
                                    "WHERE PersonID=? AND FirstName=?";

    // Array of person IDs to get records for
    SQLINTEGER  PersonIDs[PARAM_ARRAY_SIZE] = {1,2,3,4,5};
    SQLCHAR     Names[PARAM_ARRAY_SIZE][2]  = {"a","b","c","d","e"};

    // Column binding variables
    SQLLEN      PersonID;
    SQLLEN      PersonID_l;
    SQLCHAR     FirstName [FIRSTNAME_LEN];
    SQLLEN      FirstName_l;
    SQLCHAR     LastName [LASTNAME_LEN];
    SQLLEN      LastName_l;
    SQLCHAR     Address [ADDRESS_LEN];
    SQLLEN      Address_l;
    SQLCHAR     City [CITY_LEN];
    SQLLEN      City_l;

    int i;
    char *     tmp;
    char       strPersonID[11];

    SQLUSMALLINT ParamStatusArray[PARAM_ARRAY_SIZE];
    SQLLEN       ParamsProcessed=0;

    // Allocate the ODBC environment and save handle.
    retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
                henv, SQL_HANDLE_ENV);

    // Notify ODBC that this is an ODBC 3.0 app.
    retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
                                                (SQLPOINTER) SQL_OV_ODBC3, 0);
    CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)",
                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 DATASOURCE
	retcode=SQLDriverConnect(hdbc, NULL, "DSN=DATASOURCE;",
                             SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
    CHECK_ERROR(retcode, "SQLDriverConnect(DATASOURCE)",
                hdbc, SQL_HANDLE_DBC);

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

    // Set parameter set size, status array and params processed pointers
    retcode = SQLSetStmtAttr (hstmt, SQL_ATTR_PARAMSET_SIZE,
                                     (SQLPOINTER) PARAM_ARRAY_SIZE, 0);
    retcode = SQLSetStmtAttr (hstmt, SQL_ATTR_PARAM_STATUS_PTR,
                                     ParamStatusArray, PARAM_ARRAY_SIZE);
    retcode = SQLSetStmtAttr (hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR,
                                     &ParamsProcessed, 0);

    // Bind array values of parameter 1
    retcode = SQLBindParameter(hstmt, 1,
                               SQL_PARAM_INPUT, SQL_C_LONG,
                               SQL_INTEGER, 0, 0, PersonIDs,
                               0, NULL);
    retcode = SQLBindParameter(hstmt, 2,
                               SQL_PARAM_INPUT, SQL_C_CHAR,
                               SQL_VARCHAR, 2, 0, Names,
                               2, NULL);

    // Bind columns to the Record_num Field of the first row in the array
    retcode = SQLBindCol(hstmt, 1,
                         SQL_C_LONG,   &PersonID, 0,             &PersonID_l);
    retcode = SQLBindCol(hstmt, 2,
                         SQL_C_CHAR,   FirstName, FIRSTNAME_LEN, &FirstName_l);
    retcode = SQLBindCol(hstmt, 3,
                         SQL_C_CHAR,   LastName,  LASTNAME_LEN,  &LastName_l);
    retcode = SQLBindCol(hstmt, 4,
                         SQL_C_CHAR,   Address,   ADDRESS_LEN,   &Address_l);
    retcode = SQLBindCol(hstmt, 5,
                         SQL_C_CHAR,   City,      CITY_LEN,      &City_l);

    // execute stmt
    retcode = SQLExecDirect (hstmt, stmt, SQL_NTS);
    CHECK_ERROR(retcode, "SQLExecDirect()", hstmt, SQL_HANDLE_STMT);

    // dumpDescriptors ("HSTMT", hstmt, 'Y', 'Y', 1);

    // Note: The PersonID field is an identifier and therefore at most
    // we have one record per record set per parameter. Were multiple
    // records per parameter possible, the SQLSelect() should have its own
    // loop endong with status SQL_NO_DATA, to ensure all matching records
    // are retrieved.
    do {
        retcode = SQLFetch (hstmt);
        if (retcode != SQL_NO_DATA) {
            tmp=itoa((int) PersonID);
            memset (strPersonID, ' ', sizeof(strPersonID));
            strcpy(strPersonID, tmp);
            strPersonID[strlen(strPersonID)]=' ';
            strPersonID[3]='\0';
            printf ("\n  - %.3s %.10s %.10s %.10s %.10s",
                            strPersonID, FirstName, LastName, Address, City);
        }
        // MoreResults decides whether there are any more array params to process
    } while (SQLMoreResults(hstmt) == SQL_SUCCESS);

exit:

    printf ("\nComplete.\n");

    // Free handles
    // 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.