Retrieving multiple result sets from a stored procedure in Python

When a single call to a stored procedure returns more than one result set, you can use the ibm_db.next_result function of the ibm_db API to retrieve the result sets.

To retrieve multiple result sets:

1. Fetch rows from the first result set returned from the procedure by calling one of the listed ibm_db fetch functions, passing the statement resource as an argument. (The first result set that is returned from the procedure is associated with the statement resource.)

   Table 1. ibm_db fetch functions

   Function	Description
   ibm_db.fetch_tuple	Returns a tuple, which is indexed by column position, representing a row in a result set. The columns are 0-indexed.
   ibm_db.fetch_assoc	Returns a dictionary, which is indexed by column name, representing a row in a result set.
   ibm_db.fetch_both	Returns a dictionary, which is indexed by both column name and position, representing a row in a result set.
   ibm_db.fetch_row	Sets the result set pointer to the next row or requested row. Use this function to iterate through a result set.

2. Retrieve the subsequent result sets by passing the original statement resource as the first argument to the ibm_db.next_result function. You can fetch rows from the statement resource until no more rows are available in the result set.

   The ibm_db.next_result function returns False when no more result sets are available or if the procedure did not return a result set.

   For more information about the ibm_db API, see https://github.com/ibmdb/python-ibmdb/wiki/APIs.

import ibm_db
conn = ibm_db.connect( "sample", "user", "password" )
if conn:
    sql = 'CALL sp_multi()'
    stmt = ibm_db.exec_immediate(conn, sql)
    row = ibm_db.fetch_assoc(stmt)
    while row != False :
        print "The value returned : ", row
        row = ibm_db.fetch_assoc(stmt)
    
    stmt1 = ibm_db.next_result(stmt)
    while stmt1 != False:
        row = ibm_db.fetch_assoc(stmt1)
        while row != False :
            print "The value returned : ", row
            row = ibm_db.fetch_assoc(stmt1)
        stmt1 = ibm_db.next_result(stmt)