Example 1: Calling a stored procedure that returns a single result set

This example shows the API calls that an Open Database Connectivity (ODBC) application can use to call a stored procedure to return a result set.

Note that in this example the DECLARE CURSOR statement does not have an explicit returnability specified. When there is only a single stored procedure on the call stack, the returnability attribute of RETURN TO CALLER as well as that of RETURN TO CLIENT will make the result set available to the caller of the application. Also note that the stored procedure is defined with a DYNAMIC RESULT SETS clause. For SQL procedures, this clause is required if the stored procedure will be returning result sets.

Defining the stored procedure:

PROCEDURE prod.resset
 
CREATE PROCEDURE prod.resset () LANGUAGE SQL 
 DYNAMIC RESULT SETS 1 
 BEGIN 
 DECLARE C1 CURSOR FOR SELECT * FROM QIWS.QCUSTCDT;
 OPEN C1;
 RETURN; 
 END

ODBC application

Note: Some of the logic has been removed.

Note: By using the code examples, you agree to the terms of the Code license and disclaimer information.

 :
strcpy(stmt,"call prod.resset()");
rc = SQLExecDirect(hstmt,stmt,SQL_NTS);
if (rc == SQL_SUCCESS) 
 {
   // CALL statement has executed successfully. Process the result set. 
   // Get number of result columns for the result set. 
   rc = SQLNumResultCols(hstmt, &wNum);
   if (rc == SQL_SUCCESS) 
       // Get description of result columns in result set
      { rc = SQLDescribeCol(hstmt,à); 
        if (rc == SQL_SUCCESS) 
             : 
        
       {
       // Bind result columns based on attributes returned 
       //  
         rc = SQLBindCol(hstmt,à);
       :
      // FETCH records until EOF is returned   
      
        rc = SQLFetch(hstmt);
        while (rc == SQL_SUCCESS) 
          { // process result returned on the SQLFetch 
                 : 
            rc = SQLFetch(hstmt); 
          } 
         : 
          } 
         // Close the result set cursor when done with it.
         rc = SQLFreeStmt(hstmt,SQL_CLOSE); 
        :