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

The following examples show how to use java.sql.CallableStatement#executeQuery() . 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
/**
 * Test for Bug#73070 - prepareCall() throws NPE
 * 
 * To test this, we create a basic stored procedure with a
 * parameter, call it and check the result.
 */
public void testBug73070() throws Exception {
    if (!this.isSetForFabricTest) {
        return;
    }
    this.conn = (FabricMySQLConnection) getNewDefaultDataSource().getConnection(this.username, this.password);
    this.conn.setServerGroupName("fabric_test1_global");

    this.conn.createStatement().executeUpdate("drop procedure if exists bug73070");
    this.conn.createStatement().executeUpdate("create procedure bug73070(in x integer) select x");
    CallableStatement stmt = this.conn.prepareCall("{call bug73070(?)}");
    stmt.setInt(1, 42);
    ResultSet rs = stmt.executeQuery();
    rs.next();
    assertEquals(42, rs.getInt(1));
    rs.close();
    stmt.close();
    this.conn.createStatement().executeUpdate("drop procedure bug73070");

    this.conn.close();
}
 
Example 2
public void testCallableStatementExec8()
   throws Exception
{
   dropProcedure( "test" );

   Statement stmt;

   stmt = con.createStatement();
   stmt.execute( "create procedure test as SELECT COUNT(*) FROM sysobjects" );
   stmt.close();

   CallableStatement cstmt = con.prepareCall( "execute test" );

   ResultSet rs = cstmt.executeQuery();
   dump( rs, SILENT );

   rs.close();
   cstmt.close();
}
 
Example 3
/**
 * Test for bug [974801] stored procedure error in Northwind
 */
public void testCallableStatementCall4() throws Exception
{
   dropProcedure( "test space" );

   Statement stmt;

   stmt = con.createStatement();
   stmt.execute( "create procedure \"test space\" as SELECT COUNT(*) FROM sysobjects" );
   stmt.close();

   CallableStatement cstmt = con.prepareCall( "{call \"test space\"}" );

   ResultSet rs = cstmt.executeQuery();
   dump( rs, SILENT );

   rs.close();
   cstmt.close();
}
 
Example 4
@Test
public void testGetSchemaInfo() throws Exception {
    String TABLE_NAME = "ZONING2";
    SpliceUnitTest.MyWatcher tableWatcher =
            new SpliceUnitTest.MyWatcher(TABLE_NAME,CLASS_NAME,
                    "(PARCELID INTEGER UNIQUE NOT NULL, ADDRESS VARCHAR(15), BOARDDEC VARCHAR(11), EXSZONE VARCHAR(8), PRPZONE VARCHAR(8), HEARDATE DATE)");
    tableDAO.drop(CLASS_NAME, TABLE_NAME);
    tableWatcher.create(Description.createSuiteDescription(CLASS_NAME, "testGetSchemaInfo"));

    CallableStatement cs = methodWatcher.prepareCall("call SYSCS_UTIL.SYSCS_GET_SCHEMA_INFO()");
    ResultSet rs = cs.executeQuery();
    TestUtils.FormattedResult fr = TestUtils.FormattedResult.ResultFactory.convert("call SYSCS_UTIL.SYSCS_GET_SCHEMA_INFO()", rs);
    System.out.println(fr.toString());
    DbUtils.closeQuietly(rs);
}
 
Example 5
public void testCallableStatementExec1() throws Exception {
    CallableStatement cstmt = con.prepareCall("exec sp_who");

    ResultSet rs = cstmt.executeQuery();
    dump( rs,SILENT );

    rs.close();
    cstmt.close();
}
 
Example 6
@Test
public void testCallSysSchemas() throws Exception {
   		methodWatcher.getOrCreateConnection().setAutoCommit(true);
		CallableStatement cs = methodWatcher.prepareCall("CALL SYSIBM.METADATA()");			
		ResultSet rs = cs.executeQuery();			
		int count = 0;
		while (rs.next()) {
			Assert.assertTrue(rs.getBoolean(1));
			count++;
		}
		Assert.assertEquals(1, count);
           DbUtils.closeQuietly(rs);
}
 
Example 7
public void testCallableStatementExec5() throws Exception {
    CallableStatement cstmt = con.prepareCall("eXeC sp_who");

    ResultSet rs = cstmt.executeQuery();
    dump( rs,SILENT );

    rs.close();
    cstmt.close();
}
 
Example 8
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Tests that <code>CallableStatement.executeQuery()</code>
 * succeeds when one result set is returned from a stored
 * procedure.
 * @exception SQLException if a database error occurs
 */
public void testExecuteQueryWithOneDynamicResultSet_callable()
    throws SQLException
{
    CallableStatement cs =
        prepareCall("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
    cs.setInt(1, 1);
    ResultSet rs = cs.executeQuery();
    assertNotNull("executeQuery() returned null.", rs);
    assertSame(cs, rs.getStatement());
    JDBC.assertDrainResultsHasData(rs);
}
 
Example 9
Source Project: fixflow   File: SqlCommandExtend.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 执行存储过程(查询数据 无参数)
 * 
 * @param procName
 *            存储过程名称
 * @return 数组列表对象
 * @throws Exception
 */
public ArrayList<HashMap<Object, Object>> executeProcedureQuery(
		String procName) throws Exception {
	//getConnection();// 获取连接
	String callStr = "{call " + procName + "}";// 构造执行存储过程的sql指令
	CallableStatement cs = con.prepareCall(callStr);
	logger.info("###############::执行存储过程(查询数据):" + procName);
	rs = cs.executeQuery();
	//con.commit();
	cs.close();
	//close_DB_Object();
	return convertResultSetToArrayList(rs);
}
 
Example 10
/**
 * Tests SYSCS_GET_TIMESTAMP_REQUEST_INFO system procedure.
 */
@Test
public void testGetTimestampRequestInfo() throws Exception {
	String template = "call SYSCS_UTIL.SYSCS_GET_TIMESTAMP_REQUEST_INFO()";
    CallableStatement cs = methodWatcher.prepareCall(template);
    ResultSet rs = cs.executeQuery();
    int rowCount = 0;
    while (rs.next()) {
    	rowCount++;
    	long num = rs.getLong(2);
        Assert.assertTrue("Unexpected number of requests", num > 0);
    }
    Assert.assertTrue(rowCount > 0);
    DbUtils.closeQuietly(rs);
}
 
Example 11
Source Project: gemfirexd-oss   File: GemFireXDPlatform.java    License: Apache License 2.0 5 votes vote down vote up
@Override
protected void writeAllDDLs(Connection conn, Writer writer, boolean exportAll)
    throws SQLException, IOException {
  CallableStatement cstmt = conn.prepareCall("call SYS.EXPORT_DDLS(?)");
  cstmt.setBoolean(1, exportAll);
  ResultSet rs = cstmt.executeQuery();
  while (rs.next()) {
    writer.write(rs.getString(3));
    writer.write(';');
    writer.write(SqlBuilder.LINE_SEPARATOR);
    writer.write(SqlBuilder.LINE_SEPARATOR);
  }
  rs.close();
  writer.flush();
}
 
Example 12
@Test(expected = SQLException.class)
public void testMultiHostWithException() throws SQLException {
    when(mysqlCallableStatement.executeQuery()).thenThrow(new SQLException());
    try {
        CallableStatement preparedStatement = multiHostConnection.prepareCall("SELECT * FROM test WHERE a = ? OR b = ? OR c=? OR d = ? OR e=?");
        preparedStatement.setBigDecimal(1, new BigDecimal(10000));
        preparedStatement.setBlob(2, inputStream);
        preparedStatement.setBlob(3, inputStream, 1000000L);
        preparedStatement.setByte(3, (byte) 1);
        preparedStatement.setBytes(4, bytesParam);
        preparedStatement.setLong(5, 100L);

        ResultSet resultSet = preparedStatement.executeQuery();

        preparedStatement.close();
    } finally {
        verify(mysqlCallableStatement).executeQuery();
        verify(mysqlCallableStatement, times(0)).close();
        verify(mysqlCallableStatement).setBigDecimal(anyInt(), any(BigDecimal.class));
        verify(mysqlCallableStatement).setBlob(anyInt(), any(InputStream.class));
        verify(mysqlCallableStatement).setBlob(anyInt(), any(InputStream.class), anyLong());
        verify(mysqlCallableStatement).setByte(anyInt(), anyByte());
        assertThat(segmentStorage.getTraceSegments().size(), is(1));
        TraceSegment traceSegment = segmentStorage.getTraceSegments().get(0);
        List<AbstractTracingSpan> spans = SegmentHelper.getSpans(traceSegment);
        assertThat(spans.size(), is(1));
        assertDBSpan(spans.get(0), "Mysql/JDBI/CallableStatement/executeQuery", "SELECT * FROM test WHERE a = ? OR b = ? OR c=? OR d = ? OR e=?");
        List<LogDataEntity> logs = SpanHelper.getLogs(spans.get(0));
        Assert.assertThat(logs.size(), is(1));
        assertDBSpanLog(logs.get(0));
    }
}
 
Example 13
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 no result sets are returned.
 * @exception SQLException if a database error occurs
 */
public void testExecuteQueryWithNoDynamicResultSets_callable()
    throws SQLException
{
    CallableStatement cs =
        prepareCall("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
    cs.setInt(1, 0);
    try {
        cs.executeQuery();
        fail("executeQuery() didn't fail.");
    } catch (SQLException sqle) {
        assertNoResultSetFromExecuteQuery(sqle);
    }
}
 
Example 14
/**
 * Tests SYSCS_GET_TIMESTAMP_GENERATOR_INFO system procedure.
 */
@Test
public void testGetTimestampGeneratorInfo() throws Exception {
	String template = "call SYSCS_UTIL.SYSCS_GET_TIMESTAMP_GENERATOR_INFO()";
    CallableStatement cs = methodWatcher.prepareCall(template);
    ResultSet rs = cs.executeQuery();
    int rowCount = 0;
    while (rs.next()) {
    	rowCount++;
    	long num = rs.getLong(1);
        Assert.assertTrue("Unexpected number of timestamps", num > 0);
    }
    Assert.assertTrue(rowCount == 1);
    DbUtils.closeQuietly(rs);
}
 
Example 15
public Map<String, Integer>
        getNumberOfCheckpointsPerQueue( String testcaseIds ) throws DatabaseAccessException {

    Map<String, Integer> allStatistics = new HashMap<String, Integer>();

    String sqlLog = new SqlRequestFormatter().add("testcase ids", testcaseIds).format();

    Connection connection = getConnection();
    CallableStatement callableStatement = null;
    ResultSet rs = null;
    try {

        callableStatement = connection.prepareCall("{ call sp_get_number_of_checkpoints_per_queue(?) }");
        callableStatement.setString(1, testcaseIds);

        rs = callableStatement.executeQuery();
        int numberRecords = 0;
        while (rs.next()) {
            String name = rs.getString("name");
            int queueNumbers = rs.getInt("numberOfQueue");
            allStatistics.put(name, queueNumbers);
        }

        logQuerySuccess(sqlLog, "system statistics", numberRecords);
    } catch (Exception e) {
        throw new DatabaseAccessException("Error when " + sqlLog, e);
    } finally {
        DbUtils.closeResultSet(rs);
        DbUtils.close(connection, callableStatement);
    }

    return allStatistics;
}
 
Example 16
/**
 * Tests that <code>CallableStatement.executeQuery()</code> fails
 * when no result sets are returned.
 * @exception SQLException if a database error occurs
 */
public void testExecuteQueryWithNoDynamicResultSets_callable()
    throws SQLException
{
    CallableStatement cs =
        prepareCall("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
    cs.setInt(1, 0);
    try {
        cs.executeQuery();
        fail("executeQuery() didn't fail.");
    } catch (SQLException sqle) {
        assertNoResultSetFromExecuteQuery(sqle);
    }
}
 
Example 17
public List<Scenario> getScenarios( int startRecord, int recordsCount, String whereClause,
                                    String sortColumn, boolean ascending,
                                    int utcTimeOffset ) throws DatabaseAccessException {

    List<Scenario> scenarios = new ArrayList<Scenario>();

    String sqlLog = new SqlRequestFormatter().add("start record", startRecord)
                                             .add("records", recordsCount)
                                             .add("where", whereClause)
                                             .add("sort by", sortColumn)
                                             .add("asc", ascending)
                                             .format();
    Connection connection = getConnection();
    CallableStatement callableStatement = null;
    ResultSet rs = null;
    try {

        callableStatement = connection.prepareCall("{ call sp_get_scenarios(?, ?, ?, ?, ?) }");
        callableStatement.setString(1, String.valueOf(startRecord));
        callableStatement.setString(2, String.valueOf(recordsCount));
        callableStatement.setString(3, whereClause);
        callableStatement.setString(4, sortColumn);
        callableStatement.setString(5, (ascending
                                                  ? "ASC"
                                                  : "DESC"));

        int numberRecords = 0;
        rs = callableStatement.executeQuery();
        while (rs.next()) {
            Scenario scenario = new Scenario();
            scenario.scenarioId = rs.getString("scenarioId");
            scenario.suiteId = rs.getString("suiteId");
            scenario.name = rs.getString("name");
            scenario.description = rs.getString("description");

            scenario.testcasesTotal = rs.getInt("testcasesTotal");
            scenario.testcasesFailed = rs.getInt("testcasesFailed");
            scenario.testcasesPassedPercent = String.valueOf(rs.getInt("testcasesPassedPercent"))
                                              + "%";
            scenario.testcaseIsRunning = rs.getBoolean("testcaseIsRunning");

            if (rs.getTimestamp("dateStart") != null) {
                scenario.setStartTimestamp(rs.getTimestamp("dateStart").getTime());
            }
            if (rs.getTimestamp("dateEnd") != null) {
                scenario.setEndTimestamp(rs.getTimestamp("dateEnd").getTime());
            }

            scenario.setTimeOffset(utcTimeOffset);

            scenario.result = rs.getInt("result");
            /*
             *   -- 0 FAILED
             *   -- 1 PASSED
             *   -- 2 SKIPPED
             *   -- 4 RUNNING
             */
            switch (scenario.result) {
                case 0:
                    scenario.state = "FAILED";
                    break;
                case 1:
                    scenario.state = "PASSED";
                    break;
                case 2:
                    scenario.state = "SKIPPED";
                    break;
                case 4:
                    scenario.state = "RUNNING";
                    break;
                default:
                    //TODO: add warning
                    scenario.state = "unknown";
            }

            scenario.userNote = rs.getString("userNote");
            scenarios.add(scenario);

            numberRecords++;
        }

        logQuerySuccess(sqlLog, "scenarios", numberRecords);
    } catch (Exception e) {
        throw new DatabaseAccessException("Error when " + sqlLog, e);
    } finally {
        DbUtils.closeResultSet(rs);
        DbUtils.close(connection, callableStatement);
    }

    return scenarios;
}
 
Example 18
public List<Message> getMessages( int startRecord, int recordsCount, String whereClause,
                                  String sortColumn, boolean ascending,
                                  int utcTimeOffset ) throws DatabaseAccessException {

    List<Message> messages = new ArrayList<Message>();

    String sqlLog = new SqlRequestFormatter().add("start record", startRecord)
                                             .add("records", recordsCount)
                                             .add("where", whereClause)
                                             .add("sort by", sortColumn)
                                             .add("asc", ascending)
                                             .format();
    Connection connection = getConnection();
    CallableStatement callableStatement = null;
    ResultSet rs = null;
    try {

        callableStatement = connection.prepareCall("{ call sp_get_messages(?, ?, ?, ?, ?) }");
        callableStatement.setString(1, String.valueOf(startRecord));
        callableStatement.setString(2, String.valueOf(recordsCount));
        callableStatement.setString(3, whereClause);
        callableStatement.setString(4, sortColumn);
        callableStatement.setString(5, (ascending
                                                  ? "ASC"
                                                  : "DESC"));

        int numberRecords = 0;
        rs = callableStatement.executeQuery();
        Map<Integer, Message> splitMessages = new HashMap<Integer, Message>(); // <parentMessageId, Message>
        while (rs.next()) {
            Message message = new Message();
            message.messageId = rs.getInt("messageId");
            message.messageContent = rs.getString("message");
            message.messageType = rs.getString("typeName");

            if (rs.getTimestamp("timestamp") != null) {
                message.setStartTimestamp(rs.getTimestamp("timestamp").getTime());
            }

            message.setTimeOffset(utcTimeOffset);

            message.machineName = rs.getString("machineName");
            message.threadName = rs.getString("threadName");
            message.parentMessageId = rs.getInt("parentMessageId");

            if (message.parentMessageId != 0) {
                // split message
                if (splitMessages.containsKey(message.parentMessageId)) {
                    // append to the message - result set is ordered by message ID
                    Message splitMessage = splitMessages.get(message.parentMessageId);
                    if (splitMessage.messageId < message.messageId) {
                        // append at the end
                        splitMessage.messageContent = splitMessage.messageContent
                                                      + message.messageContent;
                    } else {
                        // append at the beginning
                        splitMessage.messageContent = message.messageContent
                                                      + splitMessage.messageContent;
                    }
                } else {
                    // first part of the split message
                    splitMessages.put(message.parentMessageId, message);
                    messages.add(message);
                }
            } else {
                // single message
                messages.add(message);
            }
            numberRecords++;
        }

        logQuerySuccess(sqlLog, "messages", numberRecords);
    } catch (Exception e) {
        throw new DatabaseAccessException("Error when " + sqlLog, e);
    } finally {
        DbUtils.closeResultSet(rs);
        DbUtils.close(connection, callableStatement);
    }
    return messages;
}
 
Example 19
public List<Message> getSuiteMessages( int startRecord, int recordsCount, String whereClause,
                                       String sortColumn,
                                       boolean ascending,
                                       int utcTimeOffset ) throws DatabaseAccessException {

    List<Message> suiteMessages = new ArrayList<Message>();

    String sqlLog = new SqlRequestFormatter().add("start record", startRecord)
                                             .add("records", recordsCount)
                                             .add("where", whereClause)
                                             .add("sort by", sortColumn)
                                             .add("asc", ascending)
                                             .format();
    Connection connection = getConnection();
    CallableStatement callableStatement = null;
    ResultSet rs = null;
    try {

        callableStatement = connection.prepareCall("{ call sp_get_suite_messages(?, ?, ?, ?, ?) }");
        callableStatement.setString(1, String.valueOf(startRecord));
        callableStatement.setString(2, String.valueOf(recordsCount));
        callableStatement.setString(3, whereClause);
        callableStatement.setString(4, sortColumn);
        callableStatement.setString(5, (ascending
                                                  ? "ASC"
                                                  : "DESC"));

        int numberRecords = 0;
        rs = callableStatement.executeQuery();
        while (rs.next()) {
            Message suiteMessage = new Message();
            suiteMessage.messageId = rs.getInt("suiteMessageId");
            suiteMessage.messageContent = rs.getString("message");
            suiteMessage.messageType = rs.getString("typeName");

            if (rs.getTimestamp("timestamp") != null) {
                suiteMessage.setStartTimestamp(rs.getTimestamp("timestamp").getTime());
            }

            suiteMessage.setTimeOffset(utcTimeOffset);

            suiteMessage.machineName = rs.getString("machineName");
            suiteMessage.threadName = rs.getString("threadName");
            suiteMessages.add(suiteMessage);

            numberRecords++;
        }

        logQuerySuccess(sqlLog, "suite messages", numberRecords);
    } catch (Exception e) {
        throw new DatabaseAccessException("Error when " + sqlLog, e);
    } finally {
        DbUtils.closeResultSet(rs);
        DbUtils.close(connection, callableStatement);
    }

    return suiteMessages;
}
 
Example 20
public List<CheckpointSummary> getCheckpointsSummary( String whereClause, String sortColumn,
                                                      boolean ascending ) throws DatabaseAccessException {

    List<CheckpointSummary> checkpoints = new ArrayList<CheckpointSummary>();

    String sqlLog = new SqlRequestFormatter().add("where", whereClause)
                                             .add("sort by", sortColumn)
                                             .add("asc", ascending)
                                             .format();
    Connection connection = getConnection();
    CallableStatement callableStatement = null;
    ResultSet rs = null;
    try {

        callableStatement = connection.prepareCall("{ call sp_get_checkpoints_summary(?, ?, ?) }");
        callableStatement.setString(1, "where " + whereClause);
        callableStatement.setString(2, sortColumn);
        callableStatement.setString(3, (ascending
                                                  ? "ASC"
                                                  : "DESC"));

        rs = callableStatement.executeQuery();
        int numberRecords = 0;
        while (rs.next()) {
            CheckpointSummary checkpointSummary = new CheckpointSummary();
            checkpointSummary.checkpointSummaryId = rs.getInt("checkpointSummaryId");
            checkpointSummary.name = rs.getString("name");

            checkpointSummary.numRunning = rs.getInt("numRunning");
            checkpointSummary.numPassed = rs.getInt("numPassed");
            checkpointSummary.numFailed = rs.getInt("numFailed");
            checkpointSummary.numTotal = checkpointSummary.numRunning + checkpointSummary.numPassed
                                         + checkpointSummary.numFailed;

            checkpointSummary.minResponseTime = rs.getInt("minResponseTime");
            if (checkpointSummary.minResponseTime == Integer.MAX_VALUE) {
                checkpointSummary.minResponseTime = 0;
            }
            checkpointSummary.avgResponseTime = rs.getDouble("avgResponseTime");
            checkpointSummary.maxResponseTime = rs.getInt("maxResponseTime");

            checkpointSummary.minTransferRate = rs.getDouble("minTransferRate");
            if (checkpointSummary.minTransferRate == Integer.MAX_VALUE) {
                checkpointSummary.minTransferRate = 0.0F;
            }
            checkpointSummary.avgTransferRate = rs.getDouble("avgTransferRate");
            checkpointSummary.maxTransferRate = rs.getDouble("maxTransferRate");
            checkpointSummary.transferRateUnit = rs.getString("transferRateUnit");

            checkpoints.add(checkpointSummary);
            numberRecords++;
        }

        logQuerySuccess(sqlLog, "checkpoints summary", numberRecords);
    } catch (Exception e) {
        throw new DatabaseAccessException("Error when " + sqlLog, e);
    } finally {
        DbUtils.closeResultSet(rs);
        DbUtils.close(connection, callableStatement);
    }

    return checkpoints;
}