/**********************************************************************
* FILENAME :    CallSPWithParam.c
*
*
* DESCRIPTION :
*               Simple ODBC example to SELECT data from a table via a
*               stored procedure which takes one input parameter.
*
*               Illustrates the most basic call with a parameter,
*               in the form :
*
*               {CALL Stored_Procedure (?)}
*
* ODBC USAGE :
*               Drops and recreates a procedure 'Find_Record'
*               Confirms the procedure exists via SQLProcedures ()
*               While record id not zero
*                   Prompt user for record id
*                   Executes the procedure using SQLExecDirect()
*                   For each result set:
*                       calls SQLNumResultCols() to establish no of
*                       columns in result set
*                       Loops using SQLFetch() until SQL_NO_DATA returned
*                       to obtain result set rows
*                       Calls SQLMoreResults() for next result set, if exists
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>

#include "util.c"
#include "CallSPUtil.c"

int main () {

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

    SQLCHAR * strExecSP   = "{CALL Find_Record(?)}";
    char    * strProcName = "Find_Record";

    SQLSMALLINT columns;                 // number of columns in result-set
    int i, count;

    SQLHANDLE 	ipd;
    SQLINTEGER	pPersonID=-1;

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

    // Set ODBC Version
    retcode=SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
                          (void *) SQL_OV_ODBC3, 0);
    CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)",
                henv, SQL_HANDLE_ENV);

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

    // DSN
    retcode=SQLDriverConnect(hdbc, NULL, "DSN=DATASOURCE;", SQL_NTS, NULL, 0,
                             NULL, SQL_DRIVER_COMPLETE);
    CHECK_ERROR(retcode, "SQLDriverConnect(DATASOURCE)",
                hdbc, SQL_HANDLE_DBC);

    retcode = DropProcedure (hdbc, strProcName);
    retcode = CreateProcedure (hdbc, strProcName);
    retcode = ListProcedure (hdbc, strProcName);

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

    // Bind input Parameter
    retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG,
                               SQL_INTEGER, 0, 0, &pPersonID, 0, NULL);
    CHECK_ERROR(retcode, "SQLBindParameter(SQL_PARAM_INPUT)",
                hstmt, SQL_HANDLE_STMT);

    retcode = SQLPrepare (hstmt, strExecSP, SQL_NTS);
    CHECK_ERROR(retcode, "SQLPrepare(SQL_HANDLE_STMT)",
                hstmt, SQL_HANDLE_STMT);

    SQLLEN  indicator, RowCount;
    char *buf=malloc (255);

    while (pPersonID != 0) {
        // Get Person ID
        getInt ("\nPerson Id ", (int *) &pPersonID, 'N', 0);
        if (pPersonID==0) goto exit;

    	retcode = SQLExecute (hstmt);
        CHECK_ERROR(retcode, "SQLExecute(SQL_HANDLE_STMT)",
                    hstmt, SQL_HANDLE_STMT);

    	// For a more generic example, use SQLDescribeCol here to find column
        // names ...

    	printf ("\nPersonID   Firstname     Surname    Address       City");
    	printf ("\n--------   ---------     -------    -------       ----");

        do {
            // SQLNumResultCols() returns number of columns in result set.
            // If non zero use SQLFetch until SQL_NO_DATA returned
            retcode=SQLNumResultCols(hstmt, &columns);
            CHECK_ERROR(retcode, "SQLNumResultCols()",
                        hstmt, SQL_HANDLE_STMT);
            printf ("\nColumns : %i", columns);
            if (columns > 0) {
                printf ("\nStart Fetch ...");
                while (SQLFetch(hstmt) != SQL_NO_DATA) {
                    // Loop through the columns
                    memset (buf, ' ', 255);
                    printf ("\n");
                    for (i = 1; i <= columns; i++) {
                        // Retrieve column data as a string
                        retcode = SQLGetData(hstmt, i, SQL_C_CHAR,
                                             buf, 255, &indicator);
                        if (SQL_SUCCEEDED(retcode)) {
                            // Handle null columns
                            if (indicator == SQL_NULL_DATA)
                                strcpy (buf, "NULL");
                            buf=rtrim(buf, ' ');
                            if (i==1)
                                printf("%-8s ", buf);
                            else
                                printf("%-12s ", buf);
                	}
            	    }
                }
                printf ("\nEnd Fetch ...\n");
            } else {
                // SQLRowCount returns number of rows affected by INSERT,
                // UPDATE, DELETE or number of rows returned by a SELECT
                retcode=SQLRowCount(hstmt, &RowCount);
            	CHECK_ERROR(retcode, "SQLRowCount()", hstmt, SQL_HANDLE_STMT);
                printf ("\nRow count is : %i\n", (int) RowCount);
            }
        } while (SQLMoreResults(hstmt) == SQL_SUCCESS);
    }

exit:

    free (buf);

    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.