Java CallableStatement.execute方法代码示例

import java.sql.CallableStatement; //导入方法依赖的package包/类
private void lookupDefaultSchema(DatabaseMetaData databaseMetaData) {
	try {
		CallableStatement cstmt = null;
		try {
			cstmt = databaseMetaData.getConnection().prepareCall("{? = call sys_context('USERENV', 'CURRENT_SCHEMA')}");
			cstmt.registerOutParameter(1, Types.VARCHAR);
			cstmt.execute();
			this.defaultSchema = cstmt.getString(1);
		}
		finally {
			if (cstmt != null) {
				cstmt.close();
			}
		}
	}
	catch (Exception ignore) {
	}
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#17898 - registerOutParameter not working when some
 * parameters pre-populated. Still waiting for feedback from JDBC experts
 * group to determine what correct parameter count from getMetaData() should
 * be, however.
 * 
 * @throws Exception
 *             if the test fails
 */
public void testBug17898() throws Exception {
    if (!serverSupportsStoredProcedures()) {
        return;
    }

    createProcedure("testBug17898", "(param1 VARCHAR(50), OUT param2 INT)\nBEGIN\nDECLARE rtn INT;\n" + "SELECT 1 INTO rtn;\nSET param2=rtn;\nEND");

    CallableStatement cstmt = this.conn.prepareCall("{CALL testBug17898('foo', ?)}");
    cstmt.registerOutParameter(1, Types.INTEGER);
    cstmt.execute();
    assertEquals(1, cstmt.getInt(1));

    cstmt.clearParameters();
    cstmt.registerOutParameter("param2", Types.INTEGER);
    cstmt.execute();
    assertEquals(1, cstmt.getInt(1));
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#9682 - Stored procedures with DECIMAL parameters with
 * storage specifications that contained "," in them would fail.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug9682() throws Exception {
    if (!serverSupportsStoredProcedures()) {
        return;
    }

    createProcedure("testBug9682", "(decimalParam DECIMAL(18,0))\nBEGIN\n   SELECT 1;\nEND");

    CallableStatement cStmt = null;

    try {
        cStmt = this.conn.prepareCall("Call testBug9682(?)");
        cStmt.setDouble(1, 18.0);
        cStmt.execute();
    } finally {
        if (cStmt != null) {
            cStmt.close();
        }
    }
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
public int populateCheckpointSummary( int loadQueueId, String name, String transferRateUnit,
                                      boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to populate checkpoint summary '" + name + "' in load queue " + loadQueueId;

    final int indexCheckpointSummaryId = 4;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_populate_checkpoint_summary(?, ?, ?, ?) }");
        callableStatement.setInt(1, loadQueueId);
        callableStatement.setString(2, name);
        callableStatement.setString(3, transferRateUnit);
        callableStatement.registerOutParameter(indexCheckpointSummaryId, Types.INTEGER);

        callableStatement.execute();

        if (callableStatement.getInt(indexCheckpointSummaryId) == 0) {
            throw new DatabaseAccessException(errMsg + " - checkpoint summary ID returned was 0");
        }

        return callableStatement.getInt(indexCheckpointSummaryId);

    } catch (Exception e) {
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
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();
        }
    }
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
@Override
public ResultSet getResultSet(CallableStatement ps) throws SQLException {
	ps.execute();
	return (ResultSet) ps.getObject( 1 );
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
public void testReadOnlyWithProcBodyAccess() throws Exception {
    if (versionMeetsMinimum(5, 0)) {
        Connection replConn = null;
        Properties props = getHostFreePropertiesFromTestsuiteUrl();
        props.setProperty("autoReconnect", "true");

        try {
            createProcedure("testProc1", "()\nREADS SQL DATA\nbegin\nSELECT NOW();\nend\n");

            createProcedure("`testProc.1`", "()\nREADS SQL DATA\nbegin\nSELECT NOW();\nend\n");

            replConn = getMasterSlaveReplicationConnection();
            replConn.setReadOnly(true);

            CallableStatement cstmt = replConn.prepareCall("CALL testProc1()");
            cstmt.execute();
            cstmt.execute();

            cstmt = replConn.prepareCall("CALL `" + replConn.getCatalog() + "`.testProc1()");
            cstmt.execute();

            cstmt = replConn.prepareCall("CALL `" + replConn.getCatalog() + "`.`testProc.1`()");
            cstmt.execute();

        } finally {

            if (replConn != null) {
                replConn.close();
            }
        }
    }
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#60816 - Cannot pass NULL to an INOUT procedure parameter
 * 
 * @throws Exception
 */
public void testBug60816() throws Exception {

    createProcedure("test60816_1", "(INOUT x INTEGER)\nBEGIN\nSET x = x + 1;\nEND");
    createProcedure("test60816_2", "(x INTEGER, OUT y INTEGER)\nBEGIN\nSET y = x + 1;\nEND");
    createProcedure("test60816_3", "(INOUT x INTEGER)\nBEGIN\nSET x = 10;\nEND");

    CallableStatement call = this.conn.prepareCall("{ call test60816_1(?) }");
    call.setInt(1, 1);
    call.registerOutParameter(1, Types.INTEGER);
    call.execute();
    assertEquals(2, call.getInt(1));

    call = this.conn.prepareCall("{ call test60816_2(?, ?) }");
    call.setInt(1, 1);
    call.registerOutParameter(2, Types.INTEGER);
    call.execute();
    assertEquals(2, call.getInt(2));

    call = this.conn.prepareCall("{ call test60816_2(?, ?) }");
    call.setNull(1, Types.INTEGER);
    call.registerOutParameter(2, Types.INTEGER);
    call.execute();
    assertEquals(0, call.getInt(2));
    assertTrue(call.wasNull());

    call = this.conn.prepareCall("{ call test60816_1(?) }");
    call.setNull(1, Types.INTEGER);
    call.registerOutParameter(1, Types.INTEGER);
    call.execute();
    assertEquals(0, call.getInt(1));
    assertTrue(call.wasNull());

    call = this.conn.prepareCall("{ call test60816_3(?) }");
    call.setNull(1, Types.INTEGER);
    call.registerOutParameter(1, Types.INTEGER);
    call.execute();
    assertEquals(10, call.getInt(1));
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
@Override
public void insertUserActivityStatistics( int testCaseId, String machine, String statisticIds,
                                          String statisticValues, long timestamp,
                                          boolean closeConnection ) throws DatabaseAccessException {

    timestamp = inUTC(timestamp);

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_insert_user_activity_statistic_by_ids(?, ?, ?, ?, ?) }");
        callableStatement.setInt(1, testCaseId);
        callableStatement.setString(2, machine);
        callableStatement.setString(3, statisticIds);
        callableStatement.setString(4, statisticValues);
        callableStatement.setTimestamp(5, new Timestamp(timestamp));

        callableStatement.execute();

    } catch (Exception e) {
        String errMsg = "Unable to insert user activity statistics, statistic IDs '" + statisticIds
                        + "', statistic values '" + statisticValues + "', timestamp " + timestamp;
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Tests fix for Bug#57022 - cannot execute a store procedure with output
 * parameters Problem was in CallableStatement.java, private void
 * determineParameterTypes() throws SQLException if (procName.indexOf(".")
 * == -1) { useCatalog = true; } The fix will be to "sanitize" db.sp call
 * just like in noAccessToProcedureBodies.
 * 
 * @throws Exception
 *             if the test fails
 */

public void testBug57022() throws Exception {
    if (!serverSupportsStoredProcedures()) {
        return;
    }

    String originalCatalog = this.conn.getCatalog();

    createDatabase("bug57022");

    createProcedure("bug57022.procbug57022", "(x int, out y int)\nbegin\ndeclare z int;\nset z = x+1, y = z;\nend\n");

    CallableStatement cStmt = null;
    try {
        cStmt = this.conn.prepareCall("{call `bug57022`.`procbug57022`(?, ?)}");
        cStmt.setInt(1, 5);
        cStmt.registerOutParameter(2, Types.INTEGER);

        cStmt.execute();
        assertEquals(6, cStmt.getInt(2));
        cStmt.clearParameters();
        cStmt.close();

        this.conn.setCatalog("bug57022");
        cStmt = this.conn.prepareCall("{call bug57022.procbug57022(?, ?)}");
        cStmt.setInt(1, 5);
        cStmt.registerOutParameter(2, Types.INTEGER);

        cStmt.execute();
        assertEquals(6, cStmt.getInt(2));
        cStmt.clearParameters();
        cStmt.close();

        this.conn.setCatalog("mysql");
        cStmt = this.conn.prepareCall("{call `bug57022`.`procbug57022`(?, ?)}");
        cStmt.setInt(1, 5);
        cStmt.registerOutParameter(2, Types.INTEGER);

        cStmt.execute();
        assertEquals(6, cStmt.getInt(2));
    } finally {
        if (cStmt != null) {
            cStmt.clearParameters();
            cStmt.close();
        }
        this.conn.setCatalog(originalCatalog);
    }

}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
public void endCheckpoint(
                           CheckpointInfo runningCheckpointInfo,
                           long endTimestamp,
                           long transferSize,
                           int result,
                           boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to end checkpoint with name '" + runningCheckpointInfo.getName()
                          + "', checkpoint summary id " + runningCheckpointInfo.getCheckpointSummaryId()
                          + ", id " + runningCheckpointInfo.getCheckpointId();

    endTimestamp = inUTC(endTimestamp);

    final int indexRowsInserted = 8;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_end_checkpoint(?, ?, ?, ?, ?, ?, ?, ?) }");
        callableStatement.setInt(1, runningCheckpointInfo.getCheckpointSummaryId());
        callableStatement.setInt(2, runningCheckpointInfo.getCheckpointId());
        callableStatement.setInt(3,
                                 (int) (endTimestamp - runningCheckpointInfo.getStartTimestamp()));
        callableStatement.setLong(4, transferSize);
        callableStatement.setInt(5, result);
        callableStatement.setInt(6, checkpointLogLevel.toInt());
        callableStatement.setTimestamp(7, new Timestamp(endTimestamp));
        callableStatement.registerOutParameter(indexRowsInserted, Types.INTEGER);

        callableStatement.execute();
        if (callableStatement.getInt(indexRowsInserted) != 1) {
            throw new DatabaseAccessException(errMsg);
        }
    } catch (Exception e) {
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
public int populateSystemStatisticDefinition(
                                              String name,
                                              String parentName,
                                              String internalName,
                                              String unit,
                                              String params ) throws DatabaseAccessException {

    if (parentName == null) {
        parentName = "";
    }
    if (internalName == null) {
        internalName = "";
    }

    CallableStatement callableStatement = null;
    Connection con = null;
    boolean useLocalConnection = false;
    try {
        if (connection == null || connection.isClosed()) {
            // connection not set externally so use new connection only for
            // this method invocation
            useLocalConnection = true;
            con = getConnection();
        } else {
            useLocalConnection = false;
            con = connection;
        }
        final int statisticId = 6;
        callableStatement = con.prepareCall("{ call sp_populate_system_statistic_definition(?, ?, ?, ?, ?, ?) }");
        callableStatement.setString(1, parentName);
        callableStatement.setString(2, internalName);
        callableStatement.setString(3, name);
        callableStatement.setString(4, unit);
        callableStatement.setString(5, params);
        callableStatement.registerOutParameter(statisticId, Types.INTEGER);

        callableStatement.execute();

        return callableStatement.getInt(statisticId);

    } catch (Exception e) {
        String errMsg = "Unable to populate statistic '" + name + "' with unit '" + unit
                        + "' and params '" + params + "'";
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        DbUtils.closeStatement(callableStatement);
        if (useLocalConnection) {
            DbUtils.closeConnection(con);
        }
    }
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
@Override
public boolean insertMessage( String message, int level, boolean escapeHtml, String machineName,
                              String threadName, long timestamp, int testCaseId,
                              boolean closeConnection ) throws DatabaseAccessException {

    timestamp = inUTC(timestamp);

    Connection currentConnection;
    if (!isBatchMode) {
        currentConnection = refreshInternalConnection();
    } else {
        currentConnection = dbEventsCache.connection;
    }

    CallableStatement insertMessageStatement = insertFactory.getInsertTestcaseMessageStatement(currentConnection,
                                                                                               message,
                                                                                               level,
                                                                                               escapeHtml,
                                                                                               machineName,
                                                                                               threadName,
                                                                                               timestamp,
                                                                                               testCaseId);

    if (isBatchMode) {
        // schedule this event for batch execution
        return dbEventsCache.addInsertTestcaseMessageEventToBatch(insertMessageStatement);
    } else {
        // execute this event now
        final String errMsg = "Unable to insert testcase message '" + message + "'";

        try {
            insertMessageStatement.execute();
        } catch (SQLException e) {
            throw new DatabaseAccessException(errMsg, e);
        } finally {
            if (closeConnection) {
                DbUtils.close(connection, insertMessageStatement);
            } else {
                DbUtils.closeStatement(insertMessageStatement);
            }
        }

        return false;
    }
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Test for CallableStatement.registerOutParameter().
 */
public void testCallStmtRegisterOutParameter() throws Exception {
    createProcedure("testRegisterOutParameterProc", "(OUT b BIT, OUT i INT, OUT c CHAR(10)) BEGIN SELECT 1, 1234, 'MySQL' INTO b, i, c; END");
    final CallableStatement testCstmt = this.conn.prepareCall("{CALL testRegisterOutParameterProc(?, ?, ?)}");

    // registerOutParameter by parameter index
    testCstmt.registerOutParameter(1, JDBCType.BOOLEAN);
    testCstmt.registerOutParameter(2, JDBCType.INTEGER);
    testCstmt.registerOutParameter(3, JDBCType.CHAR);
    testCstmt.execute();

    assertEquals(Boolean.TRUE, testCstmt.getObject(1));
    assertEquals(Integer.valueOf(1234), testCstmt.getObject(2));
    assertEquals("MySQL", testCstmt.getObject(3));

    testCstmt.registerOutParameter(1, JDBCType.BOOLEAN, 1);
    testCstmt.registerOutParameter(2, JDBCType.INTEGER, 1);
    testCstmt.registerOutParameter(3, JDBCType.CHAR, 1);
    testCstmt.execute();

    assertEquals(Boolean.TRUE, testCstmt.getObject(1));
    assertEquals(Integer.valueOf(1234), testCstmt.getObject(2));
    assertEquals("MySQL", testCstmt.getObject(3));

    testCstmt.registerOutParameter(1, JDBCType.BOOLEAN, "dummy");
    testCstmt.registerOutParameter(2, JDBCType.INTEGER, "dummy");
    testCstmt.registerOutParameter(3, JDBCType.CHAR, "dummy");
    testCstmt.execute();

    assertEquals(Boolean.TRUE, testCstmt.getObject(1));
    assertEquals(Integer.valueOf(1234), testCstmt.getObject(2));
    assertEquals("MySQL", testCstmt.getObject(3));

    // registerOutParameter by parameter name
    testCstmt.registerOutParameter("b", JDBCType.BOOLEAN);
    testCstmt.registerOutParameter("i", JDBCType.INTEGER);
    testCstmt.registerOutParameter("c", JDBCType.CHAR);
    testCstmt.execute();

    assertEquals(Boolean.TRUE, testCstmt.getObject(1));
    assertEquals(Integer.valueOf(1234), testCstmt.getObject(2));
    assertEquals("MySQL", testCstmt.getObject(3));

    testCstmt.registerOutParameter("b", JDBCType.BOOLEAN, 1);
    testCstmt.registerOutParameter("i", JDBCType.INTEGER, 1);
    testCstmt.registerOutParameter("c", JDBCType.CHAR, 1);
    testCstmt.execute();

    assertEquals(Boolean.TRUE, testCstmt.getObject(1));
    assertEquals(Integer.valueOf(1234), testCstmt.getObject(2));
    assertEquals("MySQL", testCstmt.getObject(3));

    testCstmt.registerOutParameter("b", JDBCType.BOOLEAN, "dummy");
    testCstmt.registerOutParameter("i", JDBCType.INTEGER, "dummy");
    testCstmt.registerOutParameter("c", JDBCType.CHAR, "dummy");
    testCstmt.execute();

    assertEquals(Boolean.TRUE, testCstmt.getObject(1));
    assertEquals(Integer.valueOf(1234), testCstmt.getObject(2));
    assertEquals("MySQL", testCstmt.getObject(3));
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#61150 - First call to SP
 * fails with "No Database Selected"
 * The workaround introduced in DatabaseMetaData.getCallStmtParameterTypes
 * to fix the bug in server where SHOW CREATE PROCEDURE was not respecting
 * lower-case table names is misbehaving when connection is not attached to
 * database and on non-casesensitive OS.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug61150() throws Exception {
    NonRegisteringDriver driver = new NonRegisteringDriver();
    Properties oldProps = driver.parseURL(BaseTestCase.dbUrl, null);

    String host = driver.host(oldProps);
    int port = driver.port(oldProps);
    StringBuilder newUrlToTestNoDB = new StringBuilder("jdbc:mysql://");
    if (host != null) {
        newUrlToTestNoDB.append(host);
    }
    newUrlToTestNoDB.append(":").append(port).append("/");

    Statement savedSt = this.stmt;

    Properties props = getHostFreePropertiesFromTestsuiteUrl();
    props.remove(NonRegisteringDriver.DBNAME_PROPERTY_KEY);
    Connection conn1 = DriverManager.getConnection(newUrlToTestNoDB.toString(), props);

    this.stmt = conn1.createStatement();
    createDatabase("TST1");
    createProcedure("TST1.PROC", "(x int, out y int)\nbegin\ndeclare z int;\nset z = x+1, y = z;\nend\n");

    CallableStatement cStmt = null;
    cStmt = conn1.prepareCall("{call `TST1`.`PROC`(?, ?)}");
    cStmt.setInt(1, 5);
    cStmt.registerOutParameter(2, Types.INTEGER);

    cStmt.execute();
    assertEquals(6, cStmt.getInt(2));
    cStmt.clearParameters();
    cStmt.close();

    conn1.setCatalog("TST1");
    cStmt = null;
    cStmt = conn1.prepareCall("{call TST1.PROC(?, ?)}");
    cStmt.setInt(1, 5);
    cStmt.registerOutParameter(2, Types.INTEGER);

    cStmt.execute();
    assertEquals(6, cStmt.getInt(2));
    cStmt.clearParameters();
    cStmt.close();

    conn1.setCatalog("mysql");
    cStmt = null;
    cStmt = conn1.prepareCall("{call `TST1`.`PROC`(?, ?)}");
    cStmt.setInt(1, 5);
    cStmt.registerOutParameter(2, Types.INTEGER);

    cStmt.execute();
    assertEquals(6, cStmt.getInt(2));
    cStmt.clearParameters();
    cStmt.close();

    this.stmt = savedSt;
}