Java Code Examples for java.sql.CallableStatement.getMoreResults()

The following are Jave code examples for showing how to use getMoreResults() of the java.sql.CallableStatement class. You can vote up the examples you like. Your votes will be used in our system to get more good examples.
Example 1
Project: OpenDiabetes   File: TestStoredProcedure.java   Source Code and License Vote up 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 2
Project: dev-courses   File: TestStoredProcedure.java   Source Code and License Vote up 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 3
Project: OpenDiabetes   File: TestStoredProcedure.java   Source Code and License Vote up 5 votes
public void testFour() throws SQLException {

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

        st.execute("declare varone int default 0;");
        st.execute(
            "create procedure proc_inout_result_two (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(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();

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

            rs.next();
            assertEquals(rs.getString(1), "SYSTEM_LOBS");
            assertEquals(rs.getString(2), "LOBS");
            rs.close();
        }
    }
 
Example 4
Project: lams   File: MySQLDialect.java   Source Code and License Vote up 5 votes
@Override
public ResultSet getResultSet(CallableStatement ps) throws SQLException {
	boolean isResultSet = ps.execute();
	while ( !isResultSet && ps.getUpdateCount() != -1 ) {
		isResultSet = ps.getMoreResults();
	}
	return ps.getResultSet();
}
 
Example 5
Project: lams   File: DataDirectOracle9Dialect.java   Source Code and License Vote up 5 votes
@Override
public ResultSet getResultSet(CallableStatement ps) throws SQLException {
	boolean isResultSet = ps.execute();
	// This assumes you will want to ignore any update counts
	while (!isResultSet && ps.getUpdateCount() != -1) { 
		isResultSet = ps.getMoreResults();
	}

	return ps.getResultSet();
}
 
Example 6
Project: lams   File: AbstractTransactSQLDialect.java   Source Code and License Vote up 5 votes
@Override
public ResultSet getResultSet(CallableStatement ps) throws SQLException {
	boolean isResultSet = ps.execute();
	// This assumes you will want to ignore any update counts
	while ( !isResultSet && ps.getUpdateCount() != -1 ) {
		isResultSet = ps.getMoreResults();
	}

	// You may still have other ResultSets or update counts left to process here
	// but you can't do it now or the ResultSet you just got will be closed
	return ps.getResultSet();
}
 
Example 7
Project: lams   File: DB2Dialect.java   Source Code and License Vote up 5 votes
@Override
public ResultSet getResultSet(CallableStatement ps) throws SQLException {
	boolean isResultSet = ps.execute();
	// This assumes you will want to ignore any update counts 
	while ( !isResultSet && ps.getUpdateCount() != -1 ) {
		isResultSet = ps.getMoreResults();
	}

	return ps.getResultSet();
}
 
Example 8
Project: dev-courses   File: TestStoredProcedure.java   Source Code and License Vote up 5 votes
public void testFour() throws SQLException {

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

        st.execute("declare varone int default 0;");
        st.execute(
            "create procedure proc_inout_result_two (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(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();

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

            rs.next();
            assertEquals(rs.getString(1), "SYSTEM_LOBS");
            assertEquals(rs.getString(2), "LOBS");
            rs.close();
        }
    }
 
Example 9
Project: OpenDiabetes   File: TestStoredProcedure.java   Source Code and License Vote up 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 10
Project: OpenDiabetes   File: TestPreparedStatements.java   Source Code and License Vote up 4 votes
public void testB() throws SQLException, ClassNotFoundException {

        Statement statement = con.createStatement();

        statement.execute(
            "CREATE TABLE IF NOT EXISTS users (id INTEGER, name VARCHAR(25), PRIMARY KEY(id))");
        statement.executeUpdate("INSERT INTO users VALUES(1, 'Ramiro')");
        statement.executeUpdate("INSERT INTO users VALUES(2, 'Chanukya')");

        String storedProcedure1 =
            "CREATE PROCEDURE sp_say_hi(IN greeting_p VARCHAR(10)) "
            + "READS SQL DATA DYNAMIC RESULT SETS 2 " + "BEGIN ATOMIC "
            + "DECLARE result CURSOR WITH RETURN FOR SELECT COALESCE(greeting_p, 'Hi')+' '+name as greeting FROM users FOR READ ONLY; "
            + "DECLARE result1 CURSOR WITH RETURN FOR SELECT * FROM users FOR READ ONLY; "
            + "OPEN result; " + "OPEN result1; " + "END";

        statement.execute(storedProcedure1);

        String            sqlCall           = "CALL sp_say_hi(?)";
        CallableStatement callableStatement = con.prepareCall(sqlCall);

        callableStatement.setObject("GREETING_P", "Hola");

        boolean result = callableStatement.execute();

        if (!result) {
            int value = callableStatement.getUpdateCount();

            assertTrue(value == 0);
            result = callableStatement.getMoreResults();
            assertTrue(result);
            ResultSet result1 = callableStatement.getResultSet();
            result = callableStatement.getMoreResults();
            assertTrue(result);
            ResultSet result2 = callableStatement.getResultSet();
            result = callableStatement.getMoreResults();
            assertFalse(result);
            value = callableStatement.getUpdateCount();
            assertTrue(value == -1);

        }
    }
 
Example 11
Project: lams   File: JdbcTemplate.java   Source Code and License Vote up 4 votes
/**
 * Extract returned ResultSets from the completed stored procedure.
 * @param cs JDBC wrapper for the stored procedure
 * @param updateCountParameters Parameter list of declared update count parameters for the stored procedure
 * @param resultSetParameters Parameter list of declared resultSet parameters for the stored procedure
 * @return Map that contains returned results
 */
protected Map<String, Object> extractReturnedResults(CallableStatement cs,
		List<SqlParameter> updateCountParameters, List<SqlParameter> resultSetParameters, int updateCount)
		throws SQLException {

	Map<String, Object> returnedResults = new HashMap<String, Object>();
	int rsIndex = 0;
	int updateIndex = 0;
	boolean moreResults;
	if (!this.skipResultsProcessing) {
		do {
			if (updateCount == -1) {
				if (resultSetParameters != null && resultSetParameters.size() > rsIndex) {
					SqlReturnResultSet declaredRsParam = (SqlReturnResultSet) resultSetParameters.get(rsIndex);
					returnedResults.putAll(processResultSet(cs.getResultSet(), declaredRsParam));
					rsIndex++;
				}
				else {
					if (!this.skipUndeclaredResults) {
						String rsName = RETURN_RESULT_SET_PREFIX + (rsIndex + 1);
						SqlReturnResultSet undeclaredRsParam = new SqlReturnResultSet(rsName, new ColumnMapRowMapper());
						if (logger.isDebugEnabled()) {
							logger.debug("Added default SqlReturnResultSet parameter named '" + rsName + "'");
						}
						returnedResults.putAll(processResultSet(cs.getResultSet(), undeclaredRsParam));
						rsIndex++;
					}
				}
			}
			else {
				if (updateCountParameters != null && updateCountParameters.size() > updateIndex) {
					SqlReturnUpdateCount ucParam = (SqlReturnUpdateCount) updateCountParameters.get(updateIndex);
					String declaredUcName = ucParam.getName();
					returnedResults.put(declaredUcName, updateCount);
					updateIndex++;
				}
				else {
					if (!this.skipUndeclaredResults) {
						String undeclaredName = RETURN_UPDATE_COUNT_PREFIX + (updateIndex + 1);
						if (logger.isDebugEnabled()) {
							logger.debug("Added default SqlReturnUpdateCount parameter named '" + undeclaredName + "'");
						}
						returnedResults.put(undeclaredName, updateCount);
						updateIndex++;
					}
				}
			}
			moreResults = cs.getMoreResults();
			updateCount = cs.getUpdateCount();
			if (logger.isDebugEnabled()) {
				logger.debug("CallableStatement.getUpdateCount() returned " + updateCount);
			}
		}
		while (moreResults || updateCount != -1);
	}
	return returnedResults;
}
 
Example 12
Project: dev-courses   File: TestStoredProcedure.java   Source Code and License Vote up 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 13
Project: elastic-db-tools-for-java   File: SqlResults.java   Source Code and License Vote up 4 votes
/**
 * Populates instance of SqlResults using rows from ResultSet.
 *
 * @param cstmt
 *            Statement whose rows are to be read.
 */
public static StoreResults newInstance(CallableStatement cstmt) {
    StoreResults storeResults = new StoreResults();
    try {
        do {
            ResultSet rs = cstmt.getResultSet();
            if (rs == null) {
                return storeResults;
            }
            if (!rs.next()) { // move to first row.
                continue;
            }
            SqlResultType resultType = resultFromColumnName.get(rs.getMetaData().getColumnLabel(2));
            switch (resultType) {
                case ShardMap:
                    do {
                        storeResults.getStoreShardMaps().add(readShardMap(rs, 2));
                    }
                    while (rs.next());
                    break;
                case Shard:
                    do {
                        storeResults.getStoreShards().add(readShard(rs, 2));
                    }
                    while (rs.next());
                    break;
                case ShardMapping:
                    do {
                        storeResults.getStoreMappings().add(readMapping(rs, 2));
                    }
                    while (rs.next());
                    break;
                case ShardLocation:
                    do {
                        storeResults.getStoreLocations().add(readLocation(rs, 2));
                    }
                    while (rs.next());
                    break;
                case SchemaInfo:
                    do {
                        storeResults.getStoreSchemaInfoCollection().add(readSchemaInfo(rs, 2));
                    }
                    while (rs.next());
                    break;
                case StoreVersion:
                    do {
                        storeResults.setStoreVersion(readVersion(rs, 2));
                    }
                    while (rs.next());
                    break;
                case Operation:
                    do {
                        storeResults.getLogEntries().add(readLogEntry(rs, 2));
                    }
                    while (rs.next());
                    break;
                default:
                    break;
            }
        }
        while (cstmt.getMoreResults());

    }
    catch (SQLException e) {
        e.printStackTrace();
    }
    return storeResults;
}
 
Example 14
Project: spwrap   File: HSQL.java   Source Code and License Vote up 4 votes
@Override
public boolean executeCall(CallableStatement call) throws SQLException {
    super.executeCall(call);
    return call.getMoreResults();
}