Java Code Examples for java.sql.CallableStatement#executeQuery()
The following examples show how to use
java.sql.CallableStatement#executeQuery() .
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 File: CallableStatementTest.java From jTDS with GNU Lesser General Public License v2.1 | 6 votes |
|
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 2
| Source File: TestRegressions.java From Komondor with GNU General Public License v3.0 | 6 votes |
|
/**
* 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 3
| Source File: CallableStatementTest.java From jTDS with GNU Lesser General Public License v2.1 | 6 votes |
|
/**
* 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
| Source File: SQLServerDbReadAccess.java From ats-framework with Apache License 2.0 | 5 votes |
|
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 5
| Source File: TimestampAdminIT.java From spliceengine with GNU Affero General Public License v3.0 | 5 votes |
|
/**
* 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 6
| Source File: ProcedureTest.java From gemfirexd-oss with Apache License 2.0 | 5 votes |
|
/**
* 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 7
| Source File: SWCallableStatementTest.java From skywalking with Apache License 2.0 | 5 votes |
|
@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 8
| Source File: GemFireXDPlatform.java From gemfirexd-oss with Apache License 2.0 | 5 votes |
|
@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 9
| Source File: ProcedureTest.java From spliceengine with GNU Affero General Public License v3.0 | 5 votes |
|
/**
* 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 10
| Source File: TimestampAdminIT.java From spliceengine with GNU Affero General Public License v3.0 | 5 votes |
|
/**
* 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 File: SqlCommandExtend.java From fixflow with Apache License 2.0 | 5 votes |
|
/**
* 执行存储过程(查询数据 无参数)
*
* @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 12
| Source File: ProcedureTest.java From gemfirexd-oss with Apache License 2.0 | 5 votes |
|
/**
* 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 13
| Source File: CallableStatementTest.java From jTDS with GNU Lesser General Public License v2.1 | 5 votes |
|
public void testCallableStatementExec5() throws Exception {
CallableStatement cstmt = con.prepareCall("eXeC sp_who");
ResultSet rs = cstmt.executeQuery();
dump( rs,SILENT );
rs.close();
cstmt.close();
}
Example 14
| Source File: CallStatementOperationIT.java From spliceengine with GNU Affero General Public License v3.0 | 5 votes |
|
@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 15
| Source File: CallableStatementTest.java From jTDS with GNU Lesser General Public License v2.1 | 5 votes |
|
public void testCallableStatementExec1() throws Exception {
CallableStatement cstmt = con.prepareCall("exec sp_who");
ResultSet rs = cstmt.executeQuery();
dump( rs,SILENT );
rs.close();
cstmt.close();
}
Example 16
| Source File: SpliceAdminIT.java From spliceengine with GNU Affero General Public License v3.0 | 5 votes |
|
@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 17
| Source File: SQLServerDbReadAccess.java From ats-framework with Apache License 2.0 | 4 votes |
|
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;
}
Example 18
| Source File: SQLServerDbReadAccess.java From ats-framework with Apache License 2.0 | 4 votes |
|
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 19
| Source File: SQLServerDbReadAccess.java From ats-framework with Apache License 2.0 | 4 votes |
|
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 20
| Source File: SQLServerDbReadAccess.java From ats-framework with Apache License 2.0 | 4 votes |
|
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;
}
