Call stored procedures

Use stored procedures to improve the performance and function of an ODBC application.

Any iSeries™ program can act as a stored procedure. iSeries stored procedures support input, input/output and output parameters. They also support returning result sets, both single and multiple. The stored procedure program can return a result set by specifying a cursor to return (from an embedded SQL statement) or by specifying an array of values. See Stored procedures for more information.

To call a stored procedure, complete the following steps:

Verify that the stored procedure has been declared by using the SQL statement CREATE PROCEDURE.
Detail: CREATE PROCEDURE should be executed only once for the life of the stored procedure. DROP PROCEDURE can be used to delete the procedure without deleting the procedure's program. DECLARE PROCEDURE also can be used, but this method has several disadvantages. The Database Programming book contains additional information about DECLARE PROCEDURE. View an HTML online version of the book, or print a PDF version, from the DB2 Universal Database™ for iSeries SQL Reference topic in the iSeries Information Center.
Prepare the call of the stored procedure by using SQL Prepare.
Bind the parameters for input and output parameters.
Execute the call to the stored procedure.
Retrieve the result set (if one is returned)

In this C example, a COBOL program named NEWORD which resided in the default iSeries library, is called. A value in a field named szCustId is passed, and it returns a value to a field named szName.

SQLRETURN rc;
HSTMT hstmt;
SQLCHAR Query[320];
SQLCHAR szCustId[10];
SQLCHAR szName[30];
SQLINTEGER strlen_or_indPtr = SQL_NTS, strlen_or_indPtr2 = SQL_NTS;
  
rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
  
// Create the stored procedure definition.
// The create procedure could be moved to the application's
// install program so that it is only executed once.
strcpy(Query,"CREATE PROCEDURE NEWORD (:CID IN CHAR(10), :NAME OUT CHAR(30) )");
strcat(Query," (EXTERNAL NAME NEWORD LANGUAGE COBOL GENERAL WITH NULLS)");
  
// Create the stored procedure
rc = SQLExecDirect(hstmt, (unsigned char *)Query, SQL_NTS);
  
strcpy(Query, "CALL NEWORD(?,?)");
  
// Prepare the stored procedure call
rc = SQLPrepare(hstmt, (unsigned char *)Query, SQL_NTS);
  
// Bind the parameters
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 
                                      10, 0, szCustId, 11, &strlen_or_intPtr);
  
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 
                                      30, 0, szName, 31, &strlen_or_indPtr2);
strcpy (szCustId,"0000012345");
// Execute the stored procedure
rc = SQLExecute(hstmt);