Java Code Examples for java.sql.CallableStatement#getMoreResults()
The following examples show how to use
java.sql.CallableStatement#getMoreResults() .
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: ProcedureTest.java From gemfirexd-oss with Apache License 2.0 | 6 votes |
|
/**
* 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 File: Procedure2Test.java From gemfirexd-oss with Apache License 2.0 | 6 votes |
|
/**
* 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 File: ProcedureTest.java From gemfirexd-oss with Apache License 2.0 | 6 votes |
|
/**
* 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 File: ProcedureTest.java From gemfirexd-oss with Apache License 2.0 | 5 votes |
|
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 File: ProcedureTest.java From gemfirexd-oss with Apache License 2.0 | 5 votes |
|
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 6
| Source File: ProcedureTest.java From gemfirexd-oss with Apache License 2.0 | 5 votes |
|
/**
* 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 7
| Source File: DAProcedures.java From gemfirexd-oss with Apache License 2.0 | 5 votes |
|
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 8
| Source File: ProcedureTest.java From gemfirexd-oss with Apache License 2.0 | 5 votes |
|
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 9
| Source File: ProcedureTestDUnit.java From gemfirexd-oss with Apache License 2.0 | 5 votes |
|
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 10
| Source File: ProcedureTest.java From gemfirexd-oss with Apache License 2.0 | 5 votes |
|
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 11
| Source File: ProcedureTestDUnit.java From gemfirexd-oss with Apache License 2.0 | 5 votes |
|
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 12
| Source File: ProcedureTest.java From gemfirexd-oss with Apache License 2.0 | 5 votes |
|
/**
* 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 13
| Source File: TestListAgg.java From gemfirexd-oss with Apache License 2.0 | 4 votes |
|
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();
}
}
Example 14
| Source File: ProcedureTest2DUnit.java From gemfirexd-oss with Apache License 2.0 | 4 votes |
|
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 15
| Source File: ProcedureTestDUnit.java From gemfirexd-oss with Apache License 2.0 | 4 votes |
|
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 16
| Source File: ProcedureTest.java From gemfirexd-oss with Apache License 2.0 | 4 votes |
|
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 File: ProcedureTest.java From gemfirexd-oss with Apache License 2.0 | 4 votes |
|
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
| Source File: ProcedureTestDUnit.java From gemfirexd-oss with Apache License 2.0 | 4 votes |
|
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 19
| Source File: ProcedureTest.java From gemfirexd-oss with Apache License 2.0 | 4 votes |
|
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 20
| Source File: ProcedureTest2DUnit.java From gemfirexd-oss with Apache License 2.0 | 4 votes |
|
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());
}
