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

The following are Jave code examples for showing how to use getResultSet() 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: NICON   File: Call.java   Source Code and License Vote up 6 votes
/**
 * Serve para executar um Stantment
 * @param quereSQL O sql de sera executado na base de dados
 * @param parans Os parametros que seram setados para as interogacoes
 * @return 
 */
                                            @SuppressWarnings("CallToPrintStackTrace")
public static ResultCall executeQuere (String quereSQL, Object ... parans)
{
    if(!EstadoConnexao.isValid) return null;
    if (quereSQL != null)
    {
        try 
        {
            Connection con = new  Conexao().getCon();
            CallableStatement call = mapParamsType(con, quereSQL, 1, parans);
            
            call.execute();
            return new ResultCall(call, call.getResultSet());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    return null;
}
 
Example 3
Project: NICON   File: Call.java   Source Code and License Vote up 6 votes
/**
 * Essa funcao serve para invocar a funcao da base de dados que retorna uma tabela
 * @param functionName O nome da funcao a ser invocado
 * @param campos Os campos que se quer obter da funcao {NULL ou String valida = *}
 * @param param Os parametros que serao usados para invocar a funcao {Nao é obrigatorio ter os parametros}
 * @return 
 */
@SuppressWarnings("CallToPrintStackTrace")
public static ResultSet callTableFunction  (String functionName, String campos, Object ... param)
{
    if (!EstadoConnexao.isValid) return null;
    try 
    {
        Connection con = new Conexao().getCon();
        
        campos = (campos == null||campos.length() == 0)? "*":campos;
        String interogations = (param != null && param.length>0)? createInterogation(param.length): "";
        String sql = "SELECT "+campos+" FROM TABLE("+functionName+interogations+")";
        
        CallableStatement call  = mapParamsType(con, sql, 1, param);
      
        if(call==null)
            return null;
        call.execute();
        return call.getResultSet();
    } catch (Exception e)
    {
        e.printStackTrace();
    }
    return null;
}
 
Example 4
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 5
Project: OpenDiabetes   File: TestStoredProcedure.java   Source Code and License Vote up 5 votes
public void testFive() throws SQLException {

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

        st.execute(
            "create function func_table (in namep varchar(128)) returns table(cola varchar(128), colb varchar(128)) "
            + "return table(select schema_name, schema_owner from information_schema.schemata where schema_owner=namep);");

        CallableStatement cs = conn.prepareCall("call func_table('_SYSTEM')");
        boolean           isResult = cs.execute();

        assertTrue(isResult);

        ResultSet rs = cs.getResultSet();

        rs.next();
        assertEquals(rs.getString(1), "INFORMATION_SCHEMA");
        assertEquals(rs.getString(2), "_SYSTEM");
        rs.close();

        //
        isResult = st.execute("call func_table('_SYSTEM')");

        assertTrue(isResult);

        rs = st.getResultSet();

        rs.next();
        assertEquals(rs.getString(1), "INFORMATION_SCHEMA");
        assertEquals(rs.getString(2), "_SYSTEM");
        rs.close();
    }
 
Example 6
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 7
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 8
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 9
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 10
Project: dev-courses   File: TestStoredProcedure.java   Source Code and License Vote up 5 votes
public void testFive() throws SQLException {

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

        st.execute(
            "create function func_table (in namep varchar(128)) returns table(cola varchar(128), colb varchar(128)) "
            + "return table(select schema_name, schema_owner from information_schema.schemata where schema_owner=namep);");

        CallableStatement cs = conn.prepareCall("call func_table('_SYSTEM')");
        boolean           isResult = cs.execute();

        assertTrue(isResult);

        ResultSet rs = cs.getResultSet();

        rs.next();
        assertEquals(rs.getString(1), "INFORMATION_SCHEMA");
        assertEquals(rs.getString(2), "_SYSTEM");
        rs.close();

        //
        isResult = st.execute("call func_table('_SYSTEM')");

        assertTrue(isResult);

        rs = st.getResultSet();

        rs.next();
        assertEquals(rs.getString(1), "INFORMATION_SCHEMA");
        assertEquals(rs.getString(2), "_SYSTEM");
        rs.close();
    }
 
Example 11
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 12
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 13
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 14
Project: the-vigilantes   File: ResultSetRegressionTest.java   Source Code and License Vote up 4 votes
/**
 * Tests fix for Bug#33678 - Multiple result sets not supported in
 * "streaming" mode. This fix covers both normal statements, and stored
 * procedures, with the exception of stored procedures with registered
 * OUTPUT parameters, which can't be used at all with "streaming" result
 * sets.
 * 
 * @throws Exception
 */
public void testBug33678() throws Exception {
    if (!versionMeetsMinimum(4, 1)) {
        return;
    }

    createTable("testBug33678", "(field1 INT)");

    Connection multiConn = getConnectionWithProps("allowMultiQueries=true");
    Statement multiStmt = multiConn.createStatement();

    try {
        multiStmt.setFetchSize(Integer.MIN_VALUE);

        multiStmt.execute("SELECT 1 UNION SELECT 2; INSERT INTO testBug33678 VALUES (1); UPDATE testBug33678 set field1=2; "
                + "INSERT INTO testBug33678 VALUES(3); UPDATE testBug33678 set field1=2 WHERE field1=3; UPDATE testBug33678 set field1=2; SELECT 1");
        this.rs = multiStmt.getResultSet();
        this.rs.next();
        assertEquals("1", this.rs.getString(1));

        assertFalse(multiStmt.getMoreResults());
        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(2, multiStmt.getUpdateCount());
        assertTrue(multiStmt.getMoreResults());
        this.rs = multiStmt.getResultSet();
        this.rs.next();
        assertEquals("1", this.rs.getString(1));

        this.rs.close();

        multiStmt.execute("INSERT INTO testBug33678 VALUES (1); INSERT INTO testBug33678 VALUES (1), (2); INSERT INTO testBug33678 VALUES (1), (2), (3)");

        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(2, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(3, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults() && multiStmt.getUpdateCount() == -1);

        this.rs.close();

        if (versionMeetsMinimum(5, 0)) {
            createProcedure("spBug33678", "() BEGIN SELECT 1; SELECT 2; SELECT 3; END");

            CallableStatement cStmt = multiConn.prepareCall("{CALL spBug33678()}");
            cStmt.setFetchSize(Integer.MIN_VALUE);
            cStmt.execute();

            for (int i = 0; i < 2; i++) {
                if (i != 0) {
                    assertTrue(cStmt.getMoreResults());
                }

                this.rs = cStmt.getResultSet();
                assertTrue(this.rs.next());
                assertEquals(i + 1, this.rs.getInt(1));
            }
        }
    } finally {
        multiStmt.close();
        multiConn.close();
    }
}
 
Example 15
Project: OpenVertretung   File: ResultSetRegressionTest.java   Source Code and License Vote up 4 votes
/**
 * Tests fix for Bug#33678 - Multiple result sets not supported in
 * "streaming" mode. This fix covers both normal statements, and stored
 * procedures, with the exception of stored procedures with registered
 * OUTPUT parameters, which can't be used at all with "streaming" result
 * sets.
 * 
 * @throws Exception
 */
public void testBug33678() throws Exception {
    if (!versionMeetsMinimum(4, 1)) {
        return;
    }

    createTable("testBug33678", "(field1 INT)");

    Connection multiConn = getConnectionWithProps("allowMultiQueries=true");
    Statement multiStmt = multiConn.createStatement();

    try {
        multiStmt.setFetchSize(Integer.MIN_VALUE);

        multiStmt.execute("SELECT 1 UNION SELECT 2; INSERT INTO testBug33678 VALUES (1); UPDATE testBug33678 set field1=2; "
                + "INSERT INTO testBug33678 VALUES(3); UPDATE testBug33678 set field1=2 WHERE field1=3; UPDATE testBug33678 set field1=2; SELECT 1");
        this.rs = multiStmt.getResultSet();
        this.rs.next();
        assertEquals("1", this.rs.getString(1));

        assertFalse(multiStmt.getMoreResults());
        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(2, multiStmt.getUpdateCount());
        assertTrue(multiStmt.getMoreResults());
        this.rs = multiStmt.getResultSet();
        this.rs.next();
        assertEquals("1", this.rs.getString(1));

        this.rs.close();

        multiStmt.execute("INSERT INTO testBug33678 VALUES (1); INSERT INTO testBug33678 VALUES (1), (2); INSERT INTO testBug33678 VALUES (1), (2), (3)");

        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(2, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(3, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults() && multiStmt.getUpdateCount() == -1);

        this.rs.close();

        if (versionMeetsMinimum(5, 0)) {
            createProcedure("spBug33678", "() BEGIN SELECT 1; SELECT 2; SELECT 3; END");

            CallableStatement cStmt = multiConn.prepareCall("{CALL spBug33678()}");
            cStmt.setFetchSize(Integer.MIN_VALUE);
            cStmt.execute();

            for (int i = 0; i < 2; i++) {
                if (i != 0) {
                    assertTrue(cStmt.getMoreResults());
                }

                this.rs = cStmt.getResultSet();
                assertTrue(this.rs.next());
                assertEquals(i + 1, this.rs.getInt(1));
            }
        }
    } finally {
        multiStmt.close();
        multiConn.close();
    }
}
 
Example 16
Project: ProyectoPacientes   File: ResultSetRegressionTest.java   Source Code and License Vote up 4 votes
/**
 * Tests fix for Bug#33678 - Multiple result sets not supported in
 * "streaming" mode. This fix covers both normal statements, and stored
 * procedures, with the exception of stored procedures with registered
 * OUTPUT parameters, which can't be used at all with "streaming" result
 * sets.
 * 
 * @throws Exception
 */
public void testBug33678() throws Exception {
    if (!versionMeetsMinimum(4, 1)) {
        return;
    }

    createTable("testBug33678", "(field1 INT)");

    Connection multiConn = getConnectionWithProps("allowMultiQueries=true");
    Statement multiStmt = multiConn.createStatement();

    try {
        multiStmt.setFetchSize(Integer.MIN_VALUE);

        multiStmt.execute("SELECT 1 UNION SELECT 2; INSERT INTO testBug33678 VALUES (1); UPDATE testBug33678 set field1=2; "
                + "INSERT INTO testBug33678 VALUES(3); UPDATE testBug33678 set field1=2 WHERE field1=3; UPDATE testBug33678 set field1=2; SELECT 1");
        this.rs = multiStmt.getResultSet();
        this.rs.next();
        assertEquals("1", this.rs.getString(1));

        assertFalse(multiStmt.getMoreResults());
        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(2, multiStmt.getUpdateCount());
        assertTrue(multiStmt.getMoreResults());
        this.rs = multiStmt.getResultSet();
        this.rs.next();
        assertEquals("1", this.rs.getString(1));

        this.rs.close();

        multiStmt.execute("INSERT INTO testBug33678 VALUES (1); INSERT INTO testBug33678 VALUES (1), (2); INSERT INTO testBug33678 VALUES (1), (2), (3)");

        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(2, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(3, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults() && multiStmt.getUpdateCount() == -1);

        this.rs.close();

        if (versionMeetsMinimum(5, 0)) {
            createProcedure("spBug33678", "() BEGIN SELECT 1; SELECT 2; SELECT 3; END");

            CallableStatement cStmt = multiConn.prepareCall("{CALL spBug33678()}");
            cStmt.setFetchSize(Integer.MIN_VALUE);
            cStmt.execute();

            for (int i = 0; i < 2; i++) {
                if (i != 0) {
                    assertTrue(cStmt.getMoreResults());
                }

                this.rs = cStmt.getResultSet();
                assertTrue(this.rs.next());
                assertEquals(i + 1, this.rs.getInt(1));
            }
        }
    } finally {
        multiStmt.close();
        multiConn.close();
    }
}
 
Example 17
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 18
Project: BibliotecaPS   File: ResultSetRegressionTest.java   Source Code and License Vote up 4 votes
/**
 * Tests fix for Bug#33678 - Multiple result sets not supported in
 * "streaming" mode. This fix covers both normal statements, and stored
 * procedures, with the exception of stored procedures with registered
 * OUTPUT parameters, which can't be used at all with "streaming" result
 * sets.
 * 
 * @throws Exception
 */
public void testBug33678() throws Exception {
    if (!versionMeetsMinimum(4, 1)) {
        return;
    }

    createTable("testBug33678", "(field1 INT)");

    Connection multiConn = getConnectionWithProps("allowMultiQueries=true");
    Statement multiStmt = multiConn.createStatement();

    try {
        multiStmt.setFetchSize(Integer.MIN_VALUE);

        multiStmt.execute("SELECT 1 UNION SELECT 2; INSERT INTO testBug33678 VALUES (1); UPDATE testBug33678 set field1=2; "
                + "INSERT INTO testBug33678 VALUES(3); UPDATE testBug33678 set field1=2 WHERE field1=3; UPDATE testBug33678 set field1=2; SELECT 1");
        this.rs = multiStmt.getResultSet();
        this.rs.next();
        assertEquals("1", this.rs.getString(1));

        assertFalse(multiStmt.getMoreResults());
        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(2, multiStmt.getUpdateCount());
        assertTrue(multiStmt.getMoreResults());
        this.rs = multiStmt.getResultSet();
        this.rs.next();
        assertEquals("1", this.rs.getString(1));

        this.rs.close();

        multiStmt.execute("INSERT INTO testBug33678 VALUES (1); INSERT INTO testBug33678 VALUES (1), (2); INSERT INTO testBug33678 VALUES (1), (2), (3)");

        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(2, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(3, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults() && multiStmt.getUpdateCount() == -1);

        this.rs.close();

        if (versionMeetsMinimum(5, 0)) {
            createProcedure("spBug33678", "() BEGIN SELECT 1; SELECT 2; SELECT 3; END");

            CallableStatement cStmt = multiConn.prepareCall("{CALL spBug33678()}");
            cStmt.setFetchSize(Integer.MIN_VALUE);
            cStmt.execute();

            for (int i = 0; i < 2; i++) {
                if (i != 0) {
                    assertTrue(cStmt.getMoreResults());
                }

                this.rs = cStmt.getResultSet();
                assertTrue(this.rs.next());
                assertEquals(i + 1, this.rs.getInt(1));
            }
        }
    } finally {
        multiStmt.close();
        multiConn.close();
    }
}
 
Example 19
Project: OpenVertretung   File: CallableStatementTest.java   Source Code and License Vote up 2 votes
/**
 * Tests functioning of output parameters.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testResultSet() throws Exception {
    if (versionMeetsMinimum(5, 0)) {
        CallableStatement storedProc = null;

        createTable("testSpResultTbl1", "(field1 INT)");
        this.stmt.executeUpdate("INSERT INTO testSpResultTbl1 VALUES (1), (2)");
        createTable("testSpResultTbl2", "(field2 varchar(255))");
        this.stmt.executeUpdate("INSERT INTO testSpResultTbl2 VALUES ('abc'), ('def')");

        createProcedure("testSpResult", "()\nBEGIN\nSELECT field2 FROM testSpResultTbl2 WHERE field2='abc';\n"
                + "UPDATE testSpResultTbl1 SET field1=2;\nSELECT field2 FROM testSpResultTbl2 WHERE field2='def';\nend\n");

        storedProc = this.conn.prepareCall("{call testSpResult()}");

        storedProc.execute();

        this.rs = storedProc.getResultSet();

        ResultSetMetaData rsmd = this.rs.getMetaData();

        assertTrue(rsmd.getColumnCount() == 1);
        assertTrue("field2".equals(rsmd.getColumnName(1)));
        assertTrue(rsmd.getColumnType(1) == Types.VARCHAR);

        assertTrue(this.rs.next());

        assertTrue("abc".equals(this.rs.getString(1)));

        // TODO: This does not yet work in MySQL 5.0
        // assertTrue(!storedProc.getMoreResults());
        // assertTrue(storedProc.getUpdateCount() == 2);
        assertTrue(storedProc.getMoreResults());

        ResultSet nextResultSet = storedProc.getResultSet();

        rsmd = nextResultSet.getMetaData();

        assertTrue(rsmd.getColumnCount() == 1);
        assertTrue("field2".equals(rsmd.getColumnName(1)));
        assertTrue(rsmd.getColumnType(1) == Types.VARCHAR);

        assertTrue(nextResultSet.next());

        assertTrue("def".equals(nextResultSet.getString(1)));

        nextResultSet.close();

        this.rs.close();

        storedProc.execute();
    }
}
 
Example 20
Project: ProyectoPacientes   File: CallableStatementTest.java   Source Code and License Vote up 2 votes
/**
 * Tests functioning of output parameters.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testResultSet() throws Exception {
    if (versionMeetsMinimum(5, 0)) {
        CallableStatement storedProc = null;

        createTable("testSpResultTbl1", "(field1 INT)");
        this.stmt.executeUpdate("INSERT INTO testSpResultTbl1 VALUES (1), (2)");
        createTable("testSpResultTbl2", "(field2 varchar(255))");
        this.stmt.executeUpdate("INSERT INTO testSpResultTbl2 VALUES ('abc'), ('def')");

        createProcedure("testSpResult", "()\nBEGIN\nSELECT field2 FROM testSpResultTbl2 WHERE field2='abc';\n"
                + "UPDATE testSpResultTbl1 SET field1=2;\nSELECT field2 FROM testSpResultTbl2 WHERE field2='def';\nend\n");

        storedProc = this.conn.prepareCall("{call testSpResult()}");

        storedProc.execute();

        this.rs = storedProc.getResultSet();

        ResultSetMetaData rsmd = this.rs.getMetaData();

        assertTrue(rsmd.getColumnCount() == 1);
        assertTrue("field2".equals(rsmd.getColumnName(1)));
        assertTrue(rsmd.getColumnType(1) == Types.VARCHAR);

        assertTrue(this.rs.next());

        assertTrue("abc".equals(this.rs.getString(1)));

        // TODO: This does not yet work in MySQL 5.0
        // assertTrue(!storedProc.getMoreResults());
        // assertTrue(storedProc.getUpdateCount() == 2);
        assertTrue(storedProc.getMoreResults());

        ResultSet nextResultSet = storedProc.getResultSet();

        rsmd = nextResultSet.getMetaData();

        assertTrue(rsmd.getColumnCount() == 1);
        assertTrue("field2".equals(rsmd.getColumnName(1)));
        assertTrue(rsmd.getColumnType(1) == Types.VARCHAR);

        assertTrue(nextResultSet.next());

        assertTrue("def".equals(nextResultSet.getString(1)));

        nextResultSet.close();

        this.rs.close();

        storedProc.execute();
    }
}