Java Code Examples for java.sql.CallableStatement#getResultSet()
The following examples show how to use
java.sql.CallableStatement#getResultSet() .
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 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 File: ProcedureTest.java From gemfirexd-oss with Apache License 2.0 | 6 votes |
|
/**
* 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
| Source File: DatabaseMetaDataTest.java From gemfirexd-oss with Apache License 2.0 | 6 votes |
|
/**
* 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 File: ProcedureTest.java From spliceengine with GNU Affero General Public License v3.0 | 6 votes |
|
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 5
| Source File: DatabaseMetaDataTest.java From gemfirexd-oss with Apache License 2.0 | 6 votes |
|
/**
* 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 6
| Source File: ProcedureTest.java From gemfirexd-oss with Apache License 2.0 | 6 votes |
|
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
| Source File: Procedure2Test.java From gemfirexd-oss with Apache License 2.0 | 6 votes |
|
/**
* 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 8
| Source File: TestStoredProcedure.java From evosql with Apache License 2.0 | 6 votes |
|
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 9
| Source File: CallableStatementTest.java From jTDS with GNU Lesser General Public License v2.1 | 5 votes |
|
/**
* 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 10
| 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 11
| Source File: GfxdJarInstallationTest.java From gemfirexd-oss with Apache License 2.0 | 5 votes |
|
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 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 13
| Source File: DatabaseMetaDataTest.java From gemfirexd-oss with Apache License 2.0 | 5 votes |
|
/**
* 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 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: TestStoredProcedure.java From evosql with Apache License 2.0 | 4 votes |
|
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 16
| 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 17
| Source File: UseCase1Client.java From gemfirexd-oss with Apache License 2.0 | 4 votes |
|
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 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: testListAgg.java From gemfirexd-oss with Apache License 2.0 | 4 votes |
|
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 File: TestStoredProcedure.java From evosql with Apache License 2.0 | 2 votes |
|
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));
}
}
