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

The following examples show how to use java.sql.CallableStatement#getResultSet() . 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 getMoreResults(Statement.KEEP_CURRENT_RESULT)  
 * 
 * @param cs
 * @param allRS
 * @throws SQLException
 */
private void keepCurrentGetMoreResults(CallableStatement cs, ResultSet[] allRS)
    throws SQLException {
  cs.execute();

  for (int i = 0; i < 5; i++) {
    allRS[i] = cs.getResultSet();
    assertSame(cs, allRS[i].getStatement());
    allRS[i].next();
    assertEquals(2 + i, allRS[i].getInt(1));

    if (i < 4)
      assertTrue(cs.getMoreResults(Statement.KEEP_CURRENT_RESULT));
    else
      assertFalse(cs.getMoreResults(Statement.KEEP_CURRENT_RESULT));
  }

  // resultSets should still be open
  for (int i = 0; i < 5; i++)
    JDBC.assertDrainResults(allRS[i]);
}
 
Example 2
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Check getMoreResults(Statement.KEEP_CURRENT_RESULT)
 *
 * @param cs
 * @param allRS
 * @throws SQLException
 */
private void keepCurrentGetMoreResults(CallableStatement cs, ResultSet[] allRS) throws SQLException {
    cs.execute();

    for (int i = 0; i < 5; i++)
    {
        allRS[i] = cs.getResultSet();
        assertSame(cs, allRS[i].getStatement());
        allRS[i].next();
        assertEquals(2+i, allRS[i].getInt(1));

        if (i < 4)
            assertTrue(cs.getMoreResults(Statement.KEEP_CURRENT_RESULT));
        else
            assertFalse(cs.getMoreResults(Statement.KEEP_CURRENT_RESULT));
    }

    // resultSets should still be open
    for (int i = 0; i < 5; i++)
        JDBC.assertDrainResults(allRS[i]);
}
 
Example 3
/**
 *  Implement ODBC equivalent for getColumns - SYSIBM.SQLCOLUMNS
 */
private ResultSet getColumnsODBC(
        String catalog, String schemaPattern, String tableNamePattern,
        String columnNamePattern)
    throws SQLException
{
    CallableStatement cs = prepareCall("CALL SYSIBM.SQLCOLUMNS(" +
            "?, ?, ?, ?, 'DATATYPE=''ODBC''')");

    cs.setString(1, catalog);
    cs.setString(2, schemaPattern);
    cs.setString(3, tableNamePattern);
    cs.setString(4, columnNamePattern);
    cs.execute();
    return cs.getResultSet();
}
 
Example 4
Source Project: evosql   File: TestStoredProcedure.java    License: Apache License 2.0 6 votes vote down vote up
public void testThree() throws SQLException {

        Connection conn = newConnection();
        Statement  st   = conn.createStatement();

        st.execute("declare varone int default 0;");
        st.execute(
            "create procedure proc_inout_result (inout intp int) "
            + " language java reads sql data external name 'CLASSPATH:org.hsqldb.test.TestStoredProcedure.procWithResultOne'");

        CallableStatement cs =
            conn.prepareCall("call proc_inout_result(varone)");
        boolean isResult = cs.execute();

        assertFalse(isResult);
        cs.getMoreResults();

        ResultSet rs = cs.getResultSet();

        rs.next();
        assertEquals(rs.getString(1), "SYSTEM_LOBS");
        assertEquals(rs.getString(2), "LOB_IDS");
        rs.close();
    }
 
Example 5
Source Project: gemfirexd-oss   File: Procedure2Test.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Check getMoreResults(Statement.KEEP_CURRENT_RESULT)  
 * 
 * @param cs
 * @param allRS
 * @throws SQLException
 */
private void keepCurrentGetMoreResults(CallableStatement cs, ResultSet[] allRS)
    throws SQLException {
  cs.execute();

  for (int i = 0; i < 5; i++) {
    allRS[i] = cs.getResultSet();
    assertSame(cs, allRS[i].getStatement());
    allRS[i].next();
    assertEquals(2 + i, allRS[i].getInt(1));

    if (i < 4)
      assertTrue(cs.getMoreResults(Statement.KEEP_CURRENT_RESULT));
    else
      assertFalse(cs.getMoreResults(Statement.KEEP_CURRENT_RESULT));
  }

  // resultSets should still be open
  for (int i = 0; i < 5; i++)
    JDBC.assertDrainResults(allRS[i]);
}
 
Example 6
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 6 votes vote down vote up
private void closeCurrentGetMoreResults(CallableStatement cs,
    ResultSet[] allRS) throws SQLException {
  cs.execute();

  for (int i = 0; i < 5; i++) {
    allRS[i] = cs.getResultSet();
    assertSame(cs, allRS[i].getStatement());
    allRS[i].next();
    assertEquals(2 + i, allRS[i].getInt(1));

    if (i < 4)
      assertTrue(cs.getMoreResults(Statement.CLOSE_CURRENT_RESULT));
    else
      assertFalse(cs.getMoreResults(Statement.CLOSE_CURRENT_RESULT));
  }

  // verify resultSets are closed
  for (int i = 0; i < 5; i++)
    JDBC.assertClosed(allRS[i]);
}
 
Example 7
private void closeCurrentGetMoreResults(CallableStatement cs, ResultSet[] allRS) throws SQLException {
    cs.execute();
    
    for (int i = 0; i < 5; i++)
    {
        allRS[i] = cs.getResultSet();
        assertSame(cs, allRS[i].getStatement());
        allRS[i].next();
        assertEquals(2+i, allRS[i].getInt(1));
        
        if (i < 4)
            assertTrue(cs.getMoreResults(Statement.CLOSE_CURRENT_RESULT));
        else
            assertFalse(cs.getMoreResults(Statement.CLOSE_CURRENT_RESULT));
    }
    
    // verify resultSets are closed
    for (int i = 0; i < 5; i++)
        JDBC.assertClosed(allRS[i]);
}
 
Example 8
/**
 *  Implement ODBC equivalent for getColumns - SYSIBM.SQLCOLUMNS
 */
private ResultSet getColumnsODBC(
        String catalog, String schemaPattern, String tableNamePattern,
        String columnNamePattern)
    throws SQLException
{
    CallableStatement cs = prepareCall("CALL SYSIBM.SQLCOLUMNS(" +
            "?, ?, ?, ?, 'DATATYPE=''ODBC''')");

    cs.setString(1, catalog);
    cs.setString(2, schemaPattern);
    cs.setString(3, tableNamePattern);
    cs.setString(4, columnNamePattern);
    cs.execute();
    return cs.getResultSet();
}
 
Example 9
/**
 * Helper method for testing getProcedures - calls the ODBC procedure
 * @throws SQLException
 */
private ResultSet getProceduresODBC(
        String catalog, String schemaPattern, String procedureNamePattern)
    throws SQLException
{
    CallableStatement cs = prepareCall("CALL SYSIBM.SQLPROCEDURES(" +
            "?, ?, ?, 'DATATYPE=''ODBC''')");
        cs.setString(1, catalog);
        cs.setString(2, schemaPattern);
        cs.setString(3, procedureNamePattern);
        cs.execute();
        return cs.getResultSet();
}
 
Example 10
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 11
public void testJarInstallThroughVTI() throws Exception {
  Connection conn = TestUtil.getConnection();
  Statement stmt = conn.createStatement();
  String sql = "call SQLJ.INSTALL_JAR_BYTES(?, ?)";
  PreparedStatement ps = conn.prepareStatement(sql);
  ps.setBinaryStream(1, new FileInputStream(myjar));
  ps.setString(2, "app.sample1");
  ps.executeUpdate();

  String ddl = "create table EMP.PARTITIONTESTTABLE (ID int NOT NULL,"
      + " SECONDID int not null, THIRDID varchar(10) not null,"
      + " PRIMARY KEY (SECONDID, THIRDID)) PARTITION BY COLUMN (ID)";

  stmt.execute(ddl);

  stmt.execute("INSERT INTO EMP.PARTITIONTESTTABLE VALUES (2, 2, '3')");
  stmt.execute("INSERT INTO EMP.PARTITIONTESTTABLE VALUES (3, 3, '3')");

  stmt.execute("INSERT INTO EMP.PARTITIONTESTTABLE VALUES (2, 2, '4')");

  stmt.execute("CREATE PROCEDURE MergeSort () "
      + "LANGUAGE JAVA PARAMETER STYLE JAVA "
      + "READS SQL DATA DYNAMIC RESULT SETS 1 "
      + "EXTERNAL NAME 'myexamples.MergeSortProcedure.mergeSort' ");

  stmt.execute("CREATE ALIAS MergeSortProcessor FOR 'myexamples.MergeSortProcessor'");

  sql = "CALL MergeSort() " + "WITH RESULT PROCESSOR MergeSortProcessor "
      + "ON TABLE EMP.PARTITIONTESTTABLE WHERE 1=1";

  CallableStatement cs = conn.prepareCall(sql);
  cs.execute();

  ResultSet rs = cs.getResultSet();

  while (rs.next()) {
    System.out.println(rs.getObject(1));
  }
}
 
Example 12
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 13
/**
 * Test retrieving multiple resultsets, the return value and an additional
 * output parameter from a single procedure call.
 */
public void testCallWithResultSet() throws Exception {
    dropProcedure( "testCallWithResultSet" );
    Statement st = con.createStatement();
    st.execute("create proc testCallWithResultSet @in varchar(16), @out varchar(32) output as" +
               " begin" +
               "  select 'result set' as ret" +
               "  set @out = 'Test ' + @in " +
               "  select 'result set 2' as ret2" +
               "  return 1" +
               " end");
    st.close();

    CallableStatement cstmt = con.prepareCall("{?=call testCallWithResultSet(?,?)}");
    cstmt.registerOutParameter(1, Types.INTEGER);
    cstmt.setString(2, "data");
    cstmt.registerOutParameter(3, Types.VARCHAR);
    cstmt.execute();

    // resultset 1
    ResultSet rs = cstmt.getResultSet();
    assertNotNull(rs);
    assertTrue(rs.next());
    assertEquals("result set", rs.getString(1));
    assertFalse(rs.next());
    rs.close();

    // resultset 2
    assertTrue(cstmt.getMoreResults());
    rs = cstmt.getResultSet();
    assertTrue(rs.next());
    assertEquals("result set 2", rs.getString(1));
    assertFalse(rs.next());
    rs.close();

    // return value and output parameter
    assertEquals(1, cstmt.getInt(1));
    assertEquals("Test data", cstmt.getString(3));
    cstmt.close();
}
 
Example 14
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 15
Source Project: gemfirexd-oss   File: UseCase1Client.java    License: Apache License 2.0 4 votes vote down vote up
private void doSingleMatchWithOnePrimaryKey(CallableStatement callableStmt,
                                            String clientAccount)
throws SQLException {
  long start = this.useCase1stats.startMatch();

  // set up inBackOfficeMsg with one client account

  Map<String, String> tableMap = new HashMap<String, String>();
  tableMap.put(CLIENT_ACCOUNT, clientAccount);

  Map<String,Map<String,String>> inBackOfficeMsg = new HashMap<String,Map<String,String>>();
  inBackOfficeMsg.put(SECL_BO_DATA_STATUS_HIST, tableMap);

  // set up singlePrimaryMatchingKeySet with one MatchingInfo

  Set<MatchingInfo> singlePrimaryMatchingKeySet = new HashSet<MatchingInfo>();
  MatchingInfo mi = new MatchingInfo();
  mi.setBackOfficeCode(IPAY);
  mi.setKeyName(CLIENT_ACCOUNT);
  mi.setMatchingPriority(1);
  mi.setChnDataTable(SECT_CHANNEL_DATA);
  mi.setBoDataTable(SECL_BO_DATA_STATUS_HIST);
  mi.setBoOnBoardTimestamp(new Timestamp(new Date().getTime()));
  mi.setKeyOnBoardTimestamp(new Timestamp(new Date().getTime()));
  singlePrimaryMatchingKeySet.add(mi);

  SortedMap<Integer,Set<MatchingInfo>> inMatchingKeyMap = new TreeMap<Integer,Set<MatchingInfo>>();
  inMatchingKeyMap.put(Integer.valueOf(1), singlePrimaryMatchingKeySet);

  if (Log.getLogWriter().fineEnabled()) {
    Log.getLogWriter().fine("Match Task matching CLIENT_ACCOUNT=" + clientAccount);
  }

  callableStmt.setObject(1, inBackOfficeMsg);
  callableStmt.setObject(2, inMatchingKeyMap);
  callableStmt.execute();

  int errorStateValue = callableStmt.getInt(3);
  ResultSet rs = callableStmt.getResultSet();
  int countOfRows = 0;
  while (rs.next()) {
    countOfRows++;
    if (Log.getLogWriter().fineEnabled()) {
      Log.getLogWriter().fine("Match Task got" +
                              " rs.getString(1) " + rs.getString(1));
    }
  }
  rs.close();
  rs = null;
  if (Log.getLogWriter().fineEnabled()) {
    Log.getLogWriter().fine("Match Task got" +
                            " countOfRows=" + countOfRows +
                            " errorStateValue=" + errorStateValue);
  }
  this.useCase1stats.endMatch(start, countOfRows, errorStateValue);
}
 
Example 16
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 17
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 18
Source Project: evosql   File: TestStoredProcedure.java    License: Apache License 2.0 4 votes vote down vote up
public void testFourParams() throws SQLException {

        Connection conn = newConnection();
        Statement  st   = conn.createStatement();

        st.execute(
            "create procedure proc_inout_result_two_params (inout intp int) "
            + " language java reads sql data dynamic result sets 2 external name 'CLASSPATH:org.hsqldb.test.TestStoredProcedure.procWithResultTwo'");

        CallableStatement cs =
            conn.prepareCall("{call proc_inout_result_two_params(?)}");

        cs.setInt(1, 0);

        boolean isResult = cs.execute();

        assertFalse(isResult);
        cs.getMoreResults();

        ResultSet rs = cs.getResultSet();

        rs.next();
        assertEquals(rs.getString(1), "SYSTEM_LOBS");
        assertEquals(rs.getString(2), "LOB_IDS");
        rs.close();

        if (cs.getMoreResults()) {
            rs = cs.getResultSet();

            rs.next();
            assertEquals(rs.getString(1), "SYSTEM_LOBS");
            assertEquals(rs.getString(2), "LOBS");
            rs.close();
        }

        rs = cs.executeQuery();

        rs.next();
        assertEquals(rs.getString(1), "SYSTEM_LOBS");
        assertEquals(rs.getString(2), "LOB_IDS");
        rs.close();
    }
 
Example 19
Source Project: gemfirexd-oss   File: testListAgg.java    License: Apache License 2.0 4 votes vote down vote up
public testListAgg() {

		try {
			Connection cxn = DriverManager.getConnection("jdbc:gemfirexd:");
			Statement stmt = cxn.createStatement();

			stmt.execute("DROP TABLE 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 APP.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 APP.XML_DOC_1  VALUES (2, 1, '3'); ");
			 stmt.execute("INSERT INTO APP.XML_DOC_1  VALUES (3, 3, '3'); " );
			 stmt.execute("INSERT INTO APP.XML_DOC_1  VALUES (4, 4, '3'); ");
			 stmt.execute("INSERT INTO APP.XML_DOC_1  VALUES (5, 5, '3'); ");
			 stmt.execute("INSERT INTO APP.XML_DOC_1  VALUES (2, 1, '9'); ");
			 stmt.execute("INSERT INTO APP.XML_DOC_1  VALUES (2, 3, '4'); ");
			 stmt.execute("INSERT INTO APP.XML_DOC_1  VALUES (3, 3, '4'); "); 
			 stmt.execute("INSERT INTO APP.XML_DOC_1  VALUES (5, 3, '4'); "); 
			 stmt.execute("select count(*) from APP.XML_DOC_1 ");
			 

			String queryString = "{CALL APP.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();
		}

	}
 
Example 20
Source Project: evosql   File: TestStoredProcedure.java    License: Apache License 2.0 2 votes vote down vote up
public void testSix() throws SQLException {

        Connection conn = newConnection();
        Statement  st   = conn.createStatement();

        st.execute(testSixProcedure);

        CallableStatement cs = conn.prepareCall(
            "call get_columns_and_table('TABLES', 'INFORMATION_SCHEMA')");
        boolean isResult = cs.execute();

        assertFalse(isResult);

        isResult = cs.getMoreResults();

        ResultSet rs = cs.getResultSet();

        rs.next();
        assertEquals("INFORMATION_SCHEMA", rs.getString(2));
        rs.close();

        boolean more = cs.getMoreResults();

        if (more) {
            rs = cs.getResultSet();

            rs.next();
            assertEquals("INFORMATION_SCHEMA", rs.getString(2));
        }

        cs = conn.prepareCall("call get_columns_and_table(?, ?)");

        cs.setString(1, "TABLES");
        cs.setString(2, "INFORMATION_SCHEMA");

        isResult = cs.execute();

        assertFalse(isResult);
        cs.getMoreResults();

        rs = cs.getResultSet();

        rs.next();
        assertEquals("INFORMATION_SCHEMA", rs.getString(2));
        rs.close();

        more = cs.getMoreResults();

        if (more) {
            rs = cs.getResultSet();

            rs.next();
            assertEquals("INFORMATION_SCHEMA", rs.getString(2));
        }

        st = conn.createStatement();
        isResult = st.execute(
            "call get_columns_and_table('TABLES', 'INFORMATION_SCHEMA')");

        assertFalse(isResult);
        st.getMoreResults();

        rs = st.getResultSet();

        rs.next();
        assertEquals("INFORMATION_SCHEMA", rs.getString(2));
        rs.close();

        more = st.getMoreResults();

        if (more) {
            rs = st.getResultSet();

            rs.next();
            assertEquals("INFORMATION_SCHEMA", rs.getString(2));
        }

        PreparedStatement ps = conn.prepareStatement(
            "call get_columns_and_table('TABLES', 'INFORMATION_SCHEMA')");

        isResult = ps.execute();

        assertFalse(isResult);
        ps.getMoreResults();

        rs = ps.getResultSet();

        rs.next();
        assertEquals("INFORMATION_SCHEMA", rs.getString(2));
        rs.close();

        more = ps.getMoreResults();

        if (more) {
            rs = ps.getResultSet();

            rs.next();
            assertEquals("INFORMATION_SCHEMA", rs.getString(2));
        }

    }