Programming Interfaces Guide
   CHAPTER 4. ODBC Programming     

Calling stored procedures in ODBC

This section describes how to call stored procedures and process the results from an ODBC application.

     For a full description of stored procedures and triggers, see Using Procedures, Triggers, and Batches.

Procedures and result sets 

There are two types of procedures: those that return result sets and those that do not. You can use SQLNumResultCols to tell the difference: the number of result columns is zero if the procedure does not return a result set. If there is a result set, you can fetch the values using SQLFetch or SQLExtendedFetch just like any other cursor.

Parameters to procedures should be passed using parameter markers (question marks). Use SQLSetParam to assign a storage area for each parameter marker, whether it is an INPUT, OUTPUT or INOUT parameter.

In order to handle multiple result sets, ODBC must describe the currently executing cursor, not the procedure defined result set. Therefore, ODBC does not always describe column names as defined in the RESULT clause of the stored procedure definition. To avoid this problem, you can use column aliases in your procedure result set cursor.

An example with no result set 

The following example creates and calls a procedure. The procedure takes one INOUT parameter, and increments its value. In the example, the variable num_col will have the value zero, since the procedure does not return a result set. Error checking has been omitted, to make the example easier to read.

HDBC dbc;
HSTMT stmt;
long i;
SWORD num_col;

/* Create a procedure */
SQLAllocStmt( dbc, &stmt );
SQLExecDirect( stmt,
      "CREATE PROCEDURE Increment( INOUT a INT )" \
      " BEGIN" \
         " SET a = a + 1" \
      " END", SQL_NTS );

/* Call the procedure to increment 'i' */
i = 1;
SQLSetParam( stmt, 1, SQL_C_LONG, SQL_INTEGER, 0, 
            0, &i, NULL );
SQLExecDirect( stmt, "CALL Increment( ? )", 
            SQL_NTS );
SQLNumResultCols( stmt, &num_col );
do_something( i );

An example with a result set 

The following example calls a procedure that returns a result set. In the example, the variable num_col will have the value 2, since the procedure returns a result set with two columns. Again, error checking has been omitted, to make the example easier to read.

HDBC dbc;
HSTMT stmt;
SWORD num_col;
RETCODE retcode;
char emp_id[ 10 ];
char emp_lame[ 20 ];

/* Create the procedure */
SQLExecDirect( stmt,
      "CREATE PROCEDURE employees()" \
      " RESULT( emp_id CHAR(10), emp_lname CHAR(20))"\
      " BEGIN" \
      " SELECT emp_id, emp_lame FROM employee" \
      " END", SQL_NTS );

/* Call the procedure - print the results */
SQLExecDirect( stmt, "CALL employees()", SQL_NTS );
SQLNumResultCols( stmt, &num_col );
SQLBindCol( stmt, 1, SQL_C_CHAR, &emp_id,
            sizeof(emp_id), NULL );
SQLBindCol( stmt, 2, SQL_C_CHAR, &emp_lname,
            sizeof(emp_lname), NULL );

for( ;; ) {
   retcode = SQLFetch( stmt );
   if( retcode == SQL_NO_DATA_FOUND ) {
   retcode = SQLMoreResults( stmt );
   if( retcode == SQL_NO_DATA_FOUND ) break;
} else {
   do_something( emp_id, emp_lname );
}
}