DB2 Version 9.7 for Linux, UNIX, and Windows

Simultaneously accessing the result sets returned by CURSOR type output parameters

When using the IBM® Data Server Provider for .NET, the DB2Type.Cursor is specified to simultaneously access all the cursors in output parameters.

About this task

For Stored procedure that has multiple CURSOR type output parameters, binding DB2TYPE.Cursor to the output parameter object allows simultaneous access to all the cursors in output parameters.

For example, OrderDetails stored procedure declares three cursors, each giving relevant information about the product and its sales.
CREATE OR REPLACE TYPE cur AS CURSOR 
CREATE PROCEDURE OrderDetails (p_startDate TIMESTAMP, p_endDate TIMESTAMP, 
  OUT prodDetails cur, OUT prodOrderDetails cur, OUT custOrderDetails cur) 
LANGUAGE SQL 
BEGIN 
  SET prodDetails = CURSOR WITH HOLD FOR 
    SELECT p.pid, price, quantity FROM products p, inventory i 
      WHERE p.pid = i.pid AND p.pid IN (SELECT DISTINCT pid FROM orders) ORDER BY pid; 
  SET prodOrderDetails = CURSOR WITH HOLD FOR 
    SELECT pid, COUNT(*), SUM (quantity) FROM orders  
      WHERE date >= p_startDate AND date <= p_endDate GROUP BY pid ORDER BY pid; 
  SET custOrderDetails = CURSOR WITH HOLD FOR 
    SELECT pid, custID, COUNT(*), SUM(quantity) FROM orders 
      WHERE date >= p_startDate AND date <= p_endDate 
      GROUP BY pid, custID ORDER by pid, custID; 
  OPEN prodDetails; 
  OPEN prodOrderDetails; 
  OPEN custOrderDetails; 
END; 
The caller needs to access the cursors simultaneously so that it can gather the relevant information for a particular product from each of the cursors and calculate the discount. To provide simultaneous access to the cursors, the stored procedure returns the cursors as output parameters. The application must set the DB2Type to DB2Type.Cursor when binding the CURSOR type output parameters for simultaneous access to occur.
//C# Code sample
cmd.CommandText = "CALL OrderDetails( 
      @p_startDate, @p_endDate, @prodDetails, @prodOrderDetails, @custOrderDetails)"; 
cmd.Parameters.Add("@p_startDate", DateTime.Parse("1/1/2010")); 
cmd.Parameters.Add("@p_endDate", DateTime.Parse("12/31/2010")); 
cmd.Parameters.Add("@prodDetails", DB2Type.Cursor); 
cmd.Parameters["@prodDetails"].Direction = ParameterDirection.Output; 
cmd.Parameters.Add("@prodOrderDetails", DB2Type.Cursor); 
cmd.Parameters["@prodOrderDetails"].Direction = ParameterDirection.Output; 
cmd.Parameters.Add("@custOrderDetails", DB2Type.Cursor); 
cmd.Parameters["@custOrderDetails"].Direction = ParameterDirection.Output; 
cmd.ExecuteNonQuery(); 
DB2DataReader prodDetailsDR = 
    (DB2DataReader)cmd.Parameters["@prodDetails"].Value; 
DB2DataReader prodOrderDetailsDR = 
    (DB2DataReader)cmd.Parameters["@prodOrderDetails"].Value; 
DB2DataReader custOrderDetailsDR = 
    (DB2DataReader)cmd.Parameters["@custOrderDetails"].Value; 

while (prodOrderDetailsDR.Read()) 
{ 
    pid = prodOrderDetailsDR.GetInt32(0); 
    numOrders = prodOrderDetailsDR.GetInt32(1); 
    totalOrderQuantity = prodOrderDetailsDR.GetInt32(2); 
    prodDetailsDR.Read(); 
    price = prodDetailsDR.GetDecimal(1); 
    currentInventory = prodDetailsDR.GetInt32(2); 
    int totalCustOrders = 0; 
    while (custOrderDetailsDR.Read()) 
    { 
        custID = custOrderDetailsDR.GetInt32(1); 
        numOrdersByCust = custOrderDetailsDR.GetInt32(2); 
        totalCustOrders += numOrdersByCust; 
        totalOrderQuantityByCust = custOrderDetailsDR.GetInt32(3); 
        //Calculate discount based on numOrders, numOrdersByCust, 
        //  totalOrderQuantity, totalOrderQuantityByCust, price and currentInventory 
        if (totalCustOrders == numOrders) //done with this pid 
            break; 
    } 
} 
prodDetailsDR.Close(); 
prodOrderDetailsDR .Close(); 
custOrderDetailsDR .Close(); 

The data reader from a cursor type output parameter can be accessed from the Value property only after invoking the ExecuteNonQuery method.

If the command is executed using either the ExecuteReader or ExecuteResultSet methods, the result sets are returned in the DB2DataReader or DB2ResultSet object. The subsequent result sets must be accessed sequentially by calling the NextResult method. Although the output parameters have been bound, accessing the output parameter Value property will result in an InvalidOperation exception because the query was not executed with the ExecuteNonQuery method.

When working with cursors simultaneously, the application might want to commit the work done before continuing with reading the cursor. For application to issue commit without destroying the open cursor, the cursor must be declared as holdable within the stored procedure.