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

The following examples show how to use java.sql.CallableStatement#getMoreResults() . These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * 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]);
}
 
Example 2
Source Project: gemfirexd-oss   File: Procedure2Test.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * 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]);
}
 
Example 3
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * 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]);
}
 
Example 4
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 5 votes vote down vote up
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());    
  
}
 
Example 5
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 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();

}
 
Example 6
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();
}
 
Example 7
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 5 votes vote down vote up
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));
    
  

}
 
Example 8
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));
    
  

}
 
Example 9
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 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());           

}
 
Example 10
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 5 votes vote down vote up
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());           
}
 
Example 11
Source Project: gemfirexd-oss   File: DAProcedures.java    License: Apache License 2.0 5 votes vote down vote up
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;
}
 
Example 12
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 5 votes vote down vote up
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());           
}
 
Example 13
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());
}
 
Example 14
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 4 votes vote down vote up
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());           
}
 
Example 15
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));
  }
}
 
Example 16
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 4 votes vote down vote up
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();
}
 
Example 17
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 4 votes vote down vote up
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();
}
 
Example 18
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));
  }
  
}
 
Example 19
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());
}
 
Example 20
Source Project: gemfirexd-oss   File: TestListAgg.java    License: Apache License 2.0 4 votes vote down vote up
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();
		}

	}