Java Code Examples for java.sql.CallableStatement#getMoreResults()

/**
 * Check that CallableStatement.close() closes results
 * @param cs
 * @param allRS
 * @throws SQLException
 */
private void checkCSCloseClosesResults(CallableStatement cs, ResultSet[] allRS) throws SQLException {
    cs.execute();
    int pass = 0;
    do {

            allRS[pass++] = cs.getResultSet();
            assertSame(cs, allRS[pass-1].getStatement());
            // expect everything to stay open.

    } while (cs.getMoreResults(Statement.KEEP_CURRENT_RESULT));
    //fetched all results
    // All should still be open.
    for (int i = 0; i < 5; i++)
        JDBC.assertDrainResults(allRS[i]);

    cs.close();
    // all should be closed.
    for (int i = 0; i < 5; i++)
        JDBC.assertClosed(allRS[i]);
}
 

/**
 * Check that CallableStatement.execute() closes results
 * @param cs
 * @param allRS
 * @throws SQLException
 */
private void checkExecuteClosesResults(CallableStatement cs, ResultSet[] allRS)
    throws SQLException {
  //Fetching result sets with 
  // getMoreResults(Statement.KEEP_CURRENT_RESULT) and checking that cs.execute() closes them");          
  cs.execute();
  int pass = 0;
  do {

    allRS[pass++] = cs.getResultSet();
    assertSame(cs, allRS[pass - 1].getStatement());
    // expect everything to stay open.                        

  } while (cs.getMoreResults(Statement.KEEP_CURRENT_RESULT));
  //fetched all results
  // All should still be open.
  for (int i = 0; i < 5; i++)
    JDBC.assertDrainResults(allRS[i]);

  cs.execute();
  // all should be closed.
  for (int i = 0; i < 5; i++)
    JDBC.assertClosed(allRS[i]);
}
 

/**
 * Check that CallableStatement.execute() closes results
 * @param cs
 * @param allRS
 * @throws SQLException
 */
private void checkExecuteClosesResults(CallableStatement cs, ResultSet[] allRS) throws SQLException {
    //Fetching result sets with getMoreResults(Statement.KEEP_CURRENT_RESULT) and checking that cs.execute() closes them");
    cs.execute();
    int pass = 0;
    do {

            allRS[pass++] = cs.getResultSet();
            assertSame(cs, allRS[pass-1].getStatement());
            // expect everything to stay open.

    } while (cs.getMoreResults(Statement.KEEP_CURRENT_RESULT));
    //fetched all results
    // All should still be open.
    for (int i = 0; i < 5; i++)
        JDBC.assertDrainResults(allRS[i]);

    cs.execute();
    // all should be closed.
    for (int i = 0; i < 5; i++)
        JDBC.assertClosed(allRS[i]);
}
 

public void _testExecuteQueryWithMissingResultProcessorClass()
throws SQLException {
  
  setup();
  CallableStatement cs = prepareCall(
    "CALL RETRIEVE_DYNAMIC_RESULTS(?) WITH RESULT PROCESSOR Fubar ON TABLE EMP.PARTITIONTESTTABLE WHERE SECONDID in (?,?,?) AND THIRDID='3'");
  cs.setInt(1, 2);
  cs.setInt(2, 3);
  cs.setInt(3, 4);
  cs.setInt(4, 5);
  cs.execute();
  
  String[][] results=new String[2][1];
  results[0][0]="1";
  results[1][0]="1";
  
  int rsIndex=-1;
  do {
    ++rsIndex;
    int rowIndex=0;
    ResultSet rs = cs.getResultSet();
    ResultSetMetaData metaData = rs.getMetaData();
    int rowCount = metaData.getColumnCount();
    while (rs.next()) {
      String row="";
      for (int i = 1; i <=rowCount; ++i) {
        Object value = rs.getObject(i);
        row+=value.toString();          
      }
      if(rsIndex>1 || rowIndex>1) {
        fail("the result is not correct!");
      }
      if(!row.equals(results[rsIndex][rowIndex])) {
        fail("the result is not correct!");
      }
      ++rowIndex;
    }
  } while (cs.getMoreResults());    
  
}
 

public void testDataAwareProcedureWithOutgoingResultSetsOnAll()
throws SQLException {

  setup();
  CallableStatement cs = prepareCall("CALL RETRIEVE_OUTGOING_RESULTS(?) ON ALL");
  int number=2;
  cs.setInt(1, number);   
  cs.execute();
  
  String[][] results=new String[2][10];
  results[0][0]="1";
  for(int i=0; i<10; i++) {
    results[1][i]=i+"String"+i;
  }  
  
  int[] numRows={0,9};
  
  int rsIndex=-1;
  do {
    ++rsIndex;
    int rowIndex=0;
    ResultSet rs = cs.getResultSet();
    ResultSetMetaData metaData = rs.getMetaData();
    int rowCount = metaData.getColumnCount();
    while (rs.next()) {
      String row="";
      for (int i = 1; i <=rowCount; ++i) {
        Object value = rs.getObject(i);
        row+=value.toString();          
      }
      if(rsIndex>1 || rowIndex>numRows[rsIndex]) {
        fail("the result is not correct!");
      }
      if(!row.equals(results[rsIndex][rowIndex])) {
        fail("the result is not correct!");
      }
      ++rowIndex;
    }
  } while (cs.getMoreResults());           
}
 

/**
 * Method for a Java procedure that calls another procedure
 * and just passes on the dynamic results from that call.
 */
public static void nestedDynamicResultSets(String procedureText,
        ResultSet[] rs1, ResultSet[] rs2, ResultSet[] rs3, ResultSet[] rs4,
        ResultSet[] rs5, ResultSet[] rs6)
throws SQLException
{
    Connection c = DriverManager.getConnection("jdbc:default:connection");

    CallableStatement cs = c.prepareCall("CALL " + procedureText);

    cs.execute();

    // Mix up the order of the result sets in the returned
    // parameters, ensures order is defined by creation
    // and not parameter order.
    rs6[0] = cs.getResultSet();
    if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
        return;
    rs3[0] = cs.getResultSet();
    if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
        return;
    rs4[0] = cs.getResultSet();
    if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
        return;
    rs2[0] = cs.getResultSet();
    if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
        return;
    rs1[0] = cs.getResultSet();
    if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
        return;
    rs5[0] = cs.getResultSet();

}
 

protected static ResultSet[] callProcedureByCidRangePortfolio(Connection conn, String sql, 
    int cid1, int cid2, int sid, int tid, int[] data) throws SQLException { 
  ResultSet[] rs = new ResultSet[4];
  CallableStatement cs = null;
  cs = conn.prepareCall(sql);
  Log.getLogWriter().info(sql + " with cid1: " + cid1 + " and with cid2: " + cid2  +
      " with sid: " + sid + " and with tid: " + tid );
  cs.setInt(1, cid1);
  cs.setInt(2, cid2);
  cs.setInt(3, sid);
  cs.setInt(4, tid);
  cs.registerOutParameter(5, Types.INTEGER);
  cs.execute();
  data[0] = new Integer(cs.getInt(5));

  rs[0] = cs.getResultSet();
  int i=1;
  while (cs.getMoreResults(Statement.KEEP_CURRENT_RESULT)) {
    Log.getLogWriter().info("has more results");
    rs[i] = cs.getResultSet();
    i++;
  }
  if (rs == null) Log.getLogWriter().info("could not get result sets in callProcedureByCidRangePortfolio");
  
  SQLWarning warning = cs.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  return rs;
}
 

public void testDataAwareProcedureWithOutgoingResultSets()
throws SQLException {
  
  setup();
  CallableStatement cs = prepareCall("CALL RETRIEVE_OUTGOING_RESULTS(?) ON TABLE EMP.PARTITIONTESTTABLE WHERE SECONDID=4 AND THIRDID='3'");
  int number=2;
  cs.setInt(1, number);   
  cs.execute();
  
  String[][] results=new String[2][10];
  results[0][0]="1";
  for(int i=0; i<10; i++) {
    results[1][i]=i+"String"+i;
  }  
  
  int[] numRows={0,9};
  
  int rsIndex=-1;
  do {
    ++rsIndex;
    int rowIndex=0;
    ResultSet rs = cs.getResultSet();
    ResultSetMetaData metaData = rs.getMetaData();
    int rowCount = metaData.getColumnCount();
    while (rs.next()) {
      String row="";
      for (int i = 1; i <=rowCount; ++i) {
        Object value = rs.getObject(i);
        row+=value.toString();          
      }
      if(rsIndex>1 || rowIndex>numRows[rsIndex]) {
        fail("the result is not correct!");
      }
      if(!row.equals(results[rsIndex][rowIndex])) {
        fail("the result is not correct!");
      }
      ++rowIndex;
    }
  } while (cs.getMoreResults());           
}
 

public void testNCExecuteOnServerGroupProcedureCall() throws Exception {
  startServerVMs(3, 0, "sg1");
  startServerVMs(1, 0, "SG2");
  int netPort = startNetworkServer(2, null, null);
  final Connection netConn = TestUtil.getNetConnection(netPort, null, null);
  Statement stmt = netConn.createStatement();
  stmt.execute("CREATE PROCEDURE SERVER_GROUP_PROC(number INT) "
      + "LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME '"
      + ProcedureTestDUnit.class.getName() + ".serverGroupProc' "
      + "DYNAMIC RESULT SETS 4");
  CallableStatement cs = netConn.prepareCall(
      "CALL SERVER_GROUP_PROC(4)");
  cs.execute();

  int rsNumber = 0;
  do {
    ++rsNumber;
    int rowIndex = 0;
    ResultSet rs = cs.getResultSet();
    ResultSetMetaData metaData = rs.getMetaData();
    int rowCount = metaData.getColumnCount();
    while (rs.next()) {
      String row = "";
      for (int i = 1; i <= rowCount; ++i) {
        Object value = rs.getObject(i);
        row += value.toString();
        assertTrue(row.equalsIgnoreCase("sg1") || row.equalsIgnoreCase("sg2"));
      }
      getLogWriter().info(
          "testNCExecuteQueryWithDataAwareProcedureCall row=" + row
              + " resultset index=" + rsNumber + " rowIndex=" + rowIndex);
    }
  } while (cs.getMoreResults());
  assertEquals(4, rsNumber);
  cs.close();
}
 

public void testDataAwareProcedureWithoutResultSets()
throws SQLException {
  
  setup();
  CallableStatement cs = prepareCall("CALL PROCEDURE_WITHOUT_RESULTSET(?, ?) ON TABLE EMP.PARTITIONTESTTABLE WHERE SECONDID=4 AND THIRDID='3'");
  cs.registerOutParameter(2, java.sql.Types.VARCHAR, 20);
  int number=2;
  String name="INOUTPARAMETER";
  cs.setInt(1, number);
  cs.setString(2, name);
 
  
  cs.execute();
  
  ResultSet rs=cs.getResultSet();
  if(rs!=null || cs.getMoreResults()) {
    fail("no dynamic result set for the procedure!");
  }
  
  ParameterMetaData pmd=cs.getParameterMetaData();  
  int numParameters=pmd.getParameterCount();
  assertTrue(" the number of parameter is 2", numParameters==2);
  try {
    cs.getInt(1);
    fail("the in parameteter cannot be read!");
  } catch (Exception e) {
    
  }
  Object parameter2=cs.getObject(2);    
  assertTrue("the second inout parameter is "+name+number, parameter2.equals(name+number));
    
  

}
 

public void testDataAwareProcedureWithoutResultSets()
throws Exception {
  
  setup();
  CallableStatement cs = prepareCall("CALL PROCEDURE_WITHOUT_RESULTSET(?, ?) ON TABLE EMP.PARTITIONTESTTABLE WHERE SECONDID=4 AND THIRDID='3'");
  cs.registerOutParameter(2, java.sql.Types.VARCHAR, 20);
  int number=2;
  String name="INOUTPARAMETER";
  cs.setInt(1, number);
  cs.setString(2, name);
 
  
  cs.execute();
  
  ResultSet rs=cs.getResultSet();
  if(rs!=null || cs.getMoreResults()) {
    fail("no dynamic result set for the procedure!");
  }
  
  ParameterMetaData pmd=cs.getParameterMetaData();  
  int numParameters=pmd.getParameterCount();
  assertTrue(" the number of parameter is 2", numParameters==2);
  try {
    cs.getInt(1);
    fail("the in parameteter cannot be read!");
  } catch (Exception e) {
    
  }
  Object parameter2=cs.getObject(2);    
  assertTrue("the second inout parameter is "+name+number, parameter2.equals(name+number));
    
  

}
 

/**
 * Tests that <code>CallableStatement.executeQuery()</code> fails
 * when multiple result sets are returned.
 * @exception SQLException if a database error occurs
 */
public void testExecuteQueryWithOutgoingResultSetInDerbyCall()
    throws SQLException {
  setup();
  CallableStatement cs = prepareCall("CALL RETRIEVE_OUTGOING_RESULTS(?)");
  cs.setInt(1, 2);
  cs.execute();
  String[][] results=new String[2][10];
  results[0][0]="1";
  for(int i=0; i<10; i++) {
    results[1][i]=i+"String"+i;
  }  
  
  int[] numRows={0,9};
  
  int rsIndex=-1;
  do {
    ++rsIndex;
    int rowIndex=0;
    ResultSet rs = cs.getResultSet();
    ResultSetMetaData metaData = rs.getMetaData();
    int rowCount = metaData.getColumnCount();
    while (rs.next()) {
      String row="";
      for (int i = 1; i <=rowCount; ++i) {
        Object value = rs.getObject(i);
        row+=value.toString();          
      }
      if(rsIndex>1 || rowIndex>numRows[rsIndex]) {
        fail("the result is not correct!");
      }
      assertEquals(results[rsIndex][rowIndex], row);
      ++rowIndex;
    }
  } while (cs.getMoreResults());           

}
 

public void testListAgg(Connection cxn) {

		try {
			Statement stmt = cxn.createStatement();
			/*			stmt.execute("DROP TABLE cdsdba.XML_DOC_1");
			stmt.execute("DROP PROCEDURE ListAgg");
			stmt.execute("DROP ALIAS  ListAggProcessor");
			
			stmt.execute("CREATE PROCEDURE ListAgg(IN groupBy VARCHAR(256), "
					+"IN ListAggCols VARCHAR(256), IN tableName VARCHAR(128), "
					+"IN whereClause VARCHAR(256), IN delimiter VARCHAR(10)) "
					+"LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA "
					+"DYNAMIC RESULT SETS 1 EXTERNAL NAME 'ListAggProcedure.ListAgg';");
			String aliasString = "CREATE ALIAS ListAggProcessor FOR '"
					+ LISTAGGPROCESSOR.class.getName() + "'"; */
/*			System.out.println(aliasString);
			stmt.execute(aliasString);*/
			
	/*		String tableDDL = "create table cdsdba.XML_DOC_1 (ID int NOT NULL,"
					+ " SECONDID int not null, THIRDID varchar(10) not null) PARTITION BY COLUMN (ID)";

			stmt.execute(tableDDL);*/
			DatabaseMetaData dbmd = cxn.getMetaData();
			ResultSet resultSet = dbmd.getColumns(null, "CDSDBA", "XML_DOC_1", null);
			

			while (resultSet.next()) {
			    String strTableName = resultSet.getString("COLUMN_NAME");
			    System.out.println("TABLE_NAME is " + strTableName);
			}
	/*		stmt.execute("INSERT INTO cdsdba.XML_DOC_1  VALUES (2, 1, '3'); ");
			stmt.execute("INSERT INTO cdsdba.XML_DOC_1  VALUES (3, 3, '3'); ");
			stmt.execute("INSERT INTO cdsdba.XML_DOC_1  VALUES (4, 4, '3'); ");
			stmt.execute("INSERT INTO cdsdba.XML_DOC_1  VALUES (5, 5, '3'); ");
			stmt.execute("INSERT INTO cdsdba.XML_DOC_1  VALUES (2, 1, '9'); ");
			stmt.execute("INSERT INTO cdsdba.XML_DOC_1  VALUES (2, 3, '4'); ");
			stmt.execute("INSERT INTO cdsdba.XML_DOC_1  VALUES (3, 3, '4'); ");
			stmt.execute("select count(*) from cdsdba.XML_DOC_1 ");*/
			
			String queryString = "{CALL CDSDBA.ListAgg(?,?,?,?,?) WITH RESULT PROCESSOR ListAggProcessor }";
			queryString = "{CALL CDSDBA.ListAgg('structure_id_nbr DESC','create_mint_cd','CDSDBA.XML_DOC_1','MSG_PAYLOAD_QTY=5',',') WITH RESULT PROCESSOR ListAggProcessor ;}";
			CallableStatement cs = cxn.prepareCall(queryString);

			String groupBy = "ID";
			String listAgg = "THIRDID";
			String table = "XML_DOC_1";
			String whereClause = "";
			String delimiter = ",";




			cs = cxn.prepareCall(queryString);
/*			cs.setString(1, groupBy);
			cs.setString(2, listAgg);
			cs.setString(3, table);
			cs.setString(4, whereClause);
			cs.setString(5, delimiter);*/


			long startTime = new Date().getTime();
			cs.execute();

			long endTime = new Date().getTime();
			System.out.println("Duration = "+(endTime-startTime));
			ResultSet thisResultSet;
			boolean moreResults = true;
			int cnt = 0;

			do {
				thisResultSet = cs.getResultSet();
				ResultSetMetaData rMeta = thisResultSet.getMetaData();
				int colCnt = rMeta.getColumnCount();
				for (int i = 1; i < colCnt+1; i++) {
					System.out.print(rMeta.getColumnName(i));
					System.out.print("\t");
				}
				System.out.println("");
				
				if (cnt == 0) {
					while (thisResultSet.next()) {
						for (int i = 1; i < colCnt + 1; i++) {
							System.out.print(thisResultSet.getObject(i));
							System.out.print("\t");
						}
						System.out.println("");
					}
					System.out.println("ResultSet 1 ends\n");
					cnt++;
				} 
				moreResults = cs.getMoreResults();
			} while (moreResults);

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
 

public void testExecuteOnServerGroupsAndLocalAndGlobalEscape() throws Exception {
  startServerVMs(2, 0, "SG2");
  startServerVMs(1, 0, null);
  startClientVMs(1, 0, null);

  clientSQLExecute(1, "create schema EMP default server groups (SG2)"); 
  clientSQLExecute(1,
      "create table EMP.PARTITIONTESTTABLE (ID int not null, "
          + " DESCRIPTION varchar(1024) not null, primary key (ID))"
          + "PARTITION BY RANGE ( ID )"
          + " ( VALUES BETWEEN 20 and 40, VALUES BETWEEN 40 and 59, "
          + "VALUES BETWEEN 60 and 80 ) redundancy 2");

  clientSQLExecute(1, 
      "insert into emp.partitiontesttable values (20, 'r1'), (50, 'r2'), (30, 'r1'), (70, 'r3')");
  
  clientSQLExecute(1, "CREATE PROCEDURE MY_ESCAPE_SELECT(number INT) "
      + "LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME '"
      + ProcedureTest2DUnit.class.getName() + ".select_proc' "
      + "DYNAMIC RESULT SETS 4");

  Connection conn = TestUtil.getConnection();
  CallableStatement cs = conn.prepareCall(
      "CALL MY_ESCAPE_SELECT(?) ON server groups (sg2)");
  cs.setInt(1, onServerGroups);
  cs.execute();
  
  ResultSet rs;
  int escapeSeq = 0;
  do {
    rs = cs.getResultSet();
    getLogWriter().info("result set no: "+escapeSeq);
    getLogWriter().info("*********************");
    ResultSetMetaData rsmd = rs.getMetaData();
    getLogWriter().info("metadata: " + rsmd.getColumnCount());
    for (int col = 1; col <= rsmd.getColumnCount(); col++) {
      getLogWriter().info(
          "metadata: " + rsmd.getColumnName(col) + '['
              + rsmd.getColumnType(col) + ']');
    }
    int totalRows = 0;
    Set<String> rows = new HashSet<String>();
    while(rs.next()) {
      String row = rs.getInt(1)+", "+rs.getString(2);
      getLogWriter().info(row);
      totalRows++;
      rows.add(row);
    }
    getLogWriter().info("*********************");

    switch(escapeSeq) {
      case 0:
        assertEquals(8, totalRows);
        assertEquals(4, rows.size());
        break;
        
      case 1:
        assertEquals(4, totalRows);
        assertEquals(4, rows.size());
        break;
        
      case 2:
        assertEquals(8, totalRows);
        assertEquals(4, rows.size());
        break;
        
        default:
          break;
    }

    escapeSeq++;
  } while(cs.getMoreResults());
}
 

public void testExecuteQueryWithDataAwareProcedureCall()
throws Exception {
  
  setup();
  CallableStatement cs = prepareCall("CALL RETRIEVE_DYNAMIC_RESULTS(?) ON TABLE EMP.PARTITIONTESTTABLE WHERE SECONDID in (?,?,?) AND THIRDID='3'");
  cs.setInt(1, 2);
  cs.setInt(2, 3);
  cs.setInt(3, 4);
  cs.setInt(4, 5);
  cs.execute();
  
  String[][] results=new String[2][2];
  results[0][0]="1";
  results[0][1]="1";
  
  results[1][0]="1";
  results[1][1]="1";
           
  int rsIndex=-1;
  do {
    ++rsIndex;
    int rowIndex=0;
    ResultSet rs = cs.getResultSet();
    ResultSetMetaData metaData = rs.getMetaData();
    int rowCount = metaData.getColumnCount();
    while (rs.next()) {
      String row="";
      for (int i = 1; i <=rowCount; ++i) {
        Object value = rs.getObject(i);
        row+=value.toString();          
        
      }
      System.out.println("XXX testExecuteQueryWithDataAwareProcedureCall the row="+row+ "resultset index="+rsIndex+ " rowIndex="+rowIndex);
      if(rsIndex>1 || rowIndex>1) {
        fail("the result is not correct!");
      }
      if(!row.equals(results[rsIndex][rowIndex])) {
        fail("the result is not correct!");
      }
      ++rowIndex;
    }
    if(rsIndex<=1 && rowIndex!=2) {      
       fail("the number of row to be excpected is "+2+ " not "+rowIndex);
    }      
  } while (cs.getMoreResults());
  
  if(rsIndex!=3) {
    fail("the number of result sets to be excpected is 4 not" + (rsIndex+1));
  }
  
}
 

private void mixedGetMoreResults(CallableStatement cs, ResultSet[] allRS)
    throws SQLException {
  //Fetching result sets with getMoreResults(<mixture>)"
  cs.execute();

  //first two with KEEP_CURRENT_RESULT"
  allRS[0] = cs.getResultSet();
  assertSame(cs, allRS[0].getStatement());
  boolean moreRS = cs.getMoreResults(Statement.KEEP_CURRENT_RESULT);
  if (!moreRS)
    fail("FAIL - no second result set");
  allRS[1] = cs.getResultSet();
  assertSame(cs, allRS[1].getStatement());
  // two open
  allRS[0].next();
  assertEquals(2, allRS[0].getInt(1));
  allRS[1].next();
  assertEquals(3, allRS[1].getInt(1));

  //third with CLOSE_CURRENT_RESULT"
  moreRS = cs.getMoreResults(Statement.CLOSE_CURRENT_RESULT);
  if (!moreRS)
    fail("FAIL - no third result set");
  // first and third open
  allRS[2] = cs.getResultSet();
  assertSame(cs, allRS[2].getStatement());
  assertEquals(2, allRS[0].getInt(1));
  JDBC.assertClosed(allRS[1]);
  allRS[2].next();
  assertEquals(4, allRS[2].getInt(1));

  //fourth with KEEP_CURRENT_RESULT"
  moreRS = cs.getMoreResults(Statement.KEEP_CURRENT_RESULT);
  if (!moreRS)
    fail("FAIL - no fourth result set");
  allRS[3] = cs.getResultSet();
  assertSame(cs, allRS[3].getStatement());
  allRS[3].next();
  // first, third and fourth open, second closed
  assertEquals(2, allRS[0].getInt(1));
  JDBC.assertClosed(allRS[1]);
  assertEquals(4, allRS[2].getInt(1));
  assertEquals(5, allRS[3].getInt(1));

  //fifth with CLOSE_ALL_RESULTS"
  moreRS = cs.getMoreResults(Statement.CLOSE_ALL_RESULTS);
  if (!moreRS)
    fail("FAIL - no fifth result set");
  allRS[4] = cs.getResultSet();
  assertSame(cs, allRS[4].getStatement());
  allRS[4].next();
  // only fifth open
  JDBC.assertClosed(allRS[0]);
  JDBC.assertClosed(allRS[1]);
  JDBC.assertClosed(allRS[2]);
  JDBC.assertClosed(allRS[3]);
  assertEquals(6, allRS[4].getInt(1));

  //no more results with with KEEP_CURRENT_RESULT"
  moreRS = cs.getMoreResults(Statement.KEEP_CURRENT_RESULT);
  if (moreRS)
    fail("FAIL - too many result sets");
  // only fifth open
  JDBC.assertClosed(allRS[0]);
  JDBC.assertClosed(allRS[1]);
  JDBC.assertClosed(allRS[2]);
  JDBC.assertClosed(allRS[3]);
  assertEquals(6, allRS[4].getInt(1));

  allRS[4].close();
}
 

private void mixedGetMoreResults(CallableStatement cs, ResultSet[] allRS) throws SQLException {
    //Fetching result sets with getMoreResults(<mixture>)"
    cs.execute();

    //first two with KEEP_CURRENT_RESULT"
    allRS[0] = cs.getResultSet();
    assertSame(cs, allRS[0].getStatement());
    boolean moreRS = cs.getMoreResults(Statement.KEEP_CURRENT_RESULT);
    if (!moreRS)
            fail("FAIL - no second result set");
    allRS[1] = cs.getResultSet();
    assertSame(cs, allRS[1].getStatement());
    // two open
    allRS[0].next();
    assertEquals(2,allRS[0].getInt(1));
    allRS[1].next();
    assertEquals(3,allRS[1].getInt(1));

    //third with CLOSE_CURRENT_RESULT"
    moreRS = cs.getMoreResults(Statement.CLOSE_CURRENT_RESULT);
    if (!moreRS)
            fail("FAIL - no third result set");
    // first and third open
    allRS[2] = cs.getResultSet();
    assertSame(cs, allRS[2].getStatement());
    assertEquals(2,allRS[0].getInt(1));
    JDBC.assertClosed(allRS[1]);
    allRS[2].next();
    assertEquals(4,allRS[2].getInt(1));


    //fourth with KEEP_CURRENT_RESULT"
    moreRS = cs.getMoreResults(Statement.KEEP_CURRENT_RESULT);
    if (!moreRS)
            fail("FAIL - no fourth result set");
    allRS[3] = cs.getResultSet();
    assertSame(cs, allRS[3].getStatement());
    allRS[3].next();
    // first, third and fourth open, second closed
    assertEquals(2,allRS[0].getInt(1));
    JDBC.assertClosed(allRS[1]);
    assertEquals(4,allRS[2].getInt(1));
    assertEquals(5,allRS[3].getInt(1));

    //fifth with CLOSE_ALL_RESULTS"
    moreRS = cs.getMoreResults(Statement.CLOSE_ALL_RESULTS);
    if (!moreRS)
           fail("FAIL - no fifth result set");
    allRS[4] = cs.getResultSet();
    assertSame(cs, allRS[4].getStatement());
    allRS[4].next();
    // only fifth open
    JDBC.assertClosed(allRS[0]);
    JDBC.assertClosed(allRS[1]);
    JDBC.assertClosed(allRS[2]);
    JDBC.assertClosed(allRS[3]);
    assertEquals(6,allRS[4].getInt(1));

    //no more results with with KEEP_CURRENT_RESULT"
    moreRS = cs.getMoreResults(Statement.KEEP_CURRENT_RESULT);
    if (moreRS)
            fail("FAIL - too many result sets");
    // only fifth open
    JDBC.assertClosed(allRS[0]);
    JDBC.assertClosed(allRS[1]);
    JDBC.assertClosed(allRS[2]);
    JDBC.assertClosed(allRS[3]);
    assertEquals(6,allRS[4].getInt(1));

    allRS[4].close();
}
 

public void testDataAwareProcedureWithOutgoingResultSetsAndNoRoutingObjects()
throws Exception {
  
  setup();
  CallableStatement cs = prepareCall("CALL RETRIEVE_OUTGOING_RESULTS(?) ON TABLE EMP.PARTITIONRANGETESTTABLE WHERE SECONDID in (?, ?, ?)");
  int number=2;
  cs.setInt(1, number);   
  
  cs.setInt(2, 2);
  cs.setInt(3, 3);
  cs.setInt(4, 4);
  
  cs.execute();
  
  String[][] results=new String[2][20];
  results[0][0]="1";
  results[0][1]="1";
  for(int i=0; i<10; i++) {
    results[1][i]=i+"String"+i;
    results[1][i+10]=i+"String"+i;
  }  
  
  int[] numRows={2,20,0,0};
  
  int rsIndex=-1;
  do {
    ++rsIndex;
    int rowIndex=0;
    ResultSet rs = cs.getResultSet();
    ResultSetMetaData metaData = rs.getMetaData();
    int rowCount = metaData.getColumnCount();
    while (rs.next()) {
      String row="";
      for (int i = 1; i <=rowCount; ++i) {
        Object value = rs.getObject(i);
        row+=value.toString(); 
        
      }
      if(rsIndex>1 || rowIndex>=numRows[rsIndex]) {
        fail("the result is not correct! "+" ResultSet Index="+rsIndex+" row count="+rowIndex+" expected Row count="+numRows[rsIndex] );
      }
      if(!row.equals(results[rsIndex][rowIndex])) {
        fail("the result is not correct!");
      }
      ++rowIndex;
    }
    if(rowIndex!=numRows[rsIndex]){
       fail("The "+rsIndex+" result set has "+numRows[rsIndex]+" instead of "+rowIndex);
    }
    
  } while (cs.getMoreResults());
  if(rsIndex!=3) {
     fail("The number of result sets is 4! instead of "+(rsIndex+1));
  }
}
 

public void testDataAwareProcedureWithOutgoingResultSetsOnListPartitionTable()
throws SQLException {

  setup();
  CallableStatement cs = prepareCall("CALL RETRIEVE_OUTGOING_RESULTS(?) ON TABLE EMP.PARTITIONLISTTESTTABLE WHERE ID in (?, ?, ?)");
  int number=2;
  cs.setInt(1, number);   
  cs.setInt(2, 10);
  cs.setInt(3, 15);
  cs.setInt(4, 31);
  cs.execute();
  
  String[][] results=new String[2][10];
  results[0][0]="1";
  for(int i=0; i<10; i++) {
    results[1][i]=i+"String"+i;
  }  
  
  int[] numRows={0,9};
  
  int rsIndex=-1;
  do {
    ++rsIndex;
    int rowIndex=0;
    ResultSet rs = cs.getResultSet();
    ResultSetMetaData metaData = rs.getMetaData();
    int rowCount = metaData.getColumnCount();
    while (rs.next()) {
      String row="";
      for (int i = 1; i <=rowCount; ++i) {
        Object value = rs.getObject(i);
        row+=value.toString();          
      }
      if(rsIndex>1 || rowIndex>numRows[rsIndex]) {
        fail("the result is not correct!");
      }
      if(!row.equals(results[rsIndex][rowIndex])) {
        fail("the result is not correct!");
      }
      ++rowIndex;
    }
  } while (cs.getMoreResults());           
}
 

public void testExecuteOnTableWithNoWhereClauseAndLocalAndGlobalEscape() throws Exception {
  startVMs(1, 3);
  clientSQLExecute(1,
      "create table EMP.PARTITIONTESTTABLE (ID int not null, "
          + " DESCRIPTION varchar(1024) not null, primary key (ID))"
          + "PARTITION BY RANGE ( ID )"
          + " ( VALUES BETWEEN 20 and 40, VALUES BETWEEN 40 and 59, "
          + "VALUES BETWEEN 60 and 80 ) redundancy 2");

  clientSQLExecute(1, 
      "insert into emp.partitiontesttable values (20, 'r1'), (50, 'r2'), (30, 'r1'), (70, 'r3')");
  
  clientSQLExecute(1, "CREATE PROCEDURE MY_ESCAPE_SELECT(number INT) "
      + "LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME '"
      + ProcedureTest2DUnit.class.getName() + ".select_proc' "
      + "DYNAMIC RESULT SETS 4");

  Connection conn = TestUtil.getConnection();
  CallableStatement cs = conn.prepareCall(
      "CALL MY_ESCAPE_SELECT(?) ON TABLE EMP.PARTITIONTESTTABLE");
  cs.setInt(1, onTableNoWhereClause);
  cs.execute();
  
  ResultSet rs;
  int escapeSeq = 0;
  do {
    rs = cs.getResultSet();
    getLogWriter().info("result set no: "+escapeSeq);
    getLogWriter().info("*********************");
    int totalRows = 0;
    Set<String> rows = new HashSet<String>();
    while(rs.next()) {
      String row = rs.getInt(1)+", "+rs.getString(2);
      getLogWriter().info(row);
      totalRows++;
      rows.add(row);
    }
    getLogWriter().info("*********************");

    switch(escapeSeq) {
      case 0:
        assertEquals(12, totalRows);
        assertEquals(4, rows.size());
        break;
        
      case 1:
        assertEquals(4, totalRows);
        assertEquals(4, rows.size());
        break;
        
      case 2:
        assertEquals(12, totalRows);
        assertEquals(4, rows.size());
        break;
        
        default:
          break;
    }

    escapeSeq++;
  } while(cs.getMoreResults());
}