/**********************************************************************
* FILENAME : CallSPWithInOutParam.c
*
*
* DESCRIPTION :
* Simple ODBC example to SELECT data from a table via a
* stored procedure which shows how to use return values,
* an input paramater and an output parameter.
*
* Illustrates the most basic call with the 2 types of
* parameter and a return value, in the form :
*
* {? = CALL Stored_Procedure (?, ?)}
*
* ODBC USAGE :
* Drops and recreates a procedure 'InOutRet_Params'
* 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 exist
* prints return value and output param value returned by
* the procedure.
*
*/
#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 * strCallSP = "{? = CALL InOutRet_Params (?, ?)}";
char * strProcName = "InOutRet_Params";
SQLSMALLINT columns; // Number of columns in result-set
int i, count;
SQLINTEGER pPersonID=-1;
// SQLBindParameter variables.
SWORD RetParam = 1, OutParam = 1;
SQLLEN cbRetParam = SQL_NTS, cbOutParam = SQL_NTS;
// 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 the output parameter to variable RetParam.
retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_SSHORT,
SQL_INTEGER, 0, 0, &RetParam, 0, &cbRetParam);
CHECK_ERROR(retcode, "SQLBindParameter(SQL_PARAM_OUTPUT)",
hstmt, SQL_HANDLE_STMT);
// Bind input parameter
retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG,
SQL_INTEGER, 0, 0, &pPersonID, 0, NULL);
CHECK_ERROR(retcode, "SQLBindParameter(SQL_PARAM_INPUT)",
hstmt, SQL_HANDLE_STMT);
// Bind the output parameter to variable OutParam.
retcode = SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT, SQL_C_SSHORT,
SQL_INTEGER, 0, 0, &OutParam, 0, &cbOutParam);
CHECK_ERROR(retcode, "SQLBindParameter(SQL_PARAM_INPUT)",
hstmt, SQL_HANDLE_STMT);
retcode = SQLPrepare (hstmt, strCallSP, 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);
printf("Return Parameter : %d\n", RetParam);
printf("Number of Records : %d\n", OutParam);
}
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;
}
Further information
