Java CallableStatement.clearParameters方法代码示例

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包/类
/**
 * {@inheritDoc}
 */
@Override
public void freeConnection(Connection pooledConnection)  throws java.sql.SQLException {
    synchronized(clearCtxLock) {
        CallableStatement cs = null;
        try {
            cs = pooledConnection.prepareCall(CALL_JAFFA_SEC_CLEARCTX);
            if(log.isDebugEnabled()) log.debug("Clearing the Security Context");
            cs.execute();
        }
        catch (SQLException e) {
            log.error(CLEARING_THE_CONTEXT_ERROR_MESSAGE, e);
            throw new UOWSecurityException(CLEARING_THE_CONTEXT_ERROR_MESSAGE, e);
        }
        finally {
            if (cs != null) {
                cs.clearParameters();
                cs.close();
            }
        }
    }
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
protected void executeStoredProcedure(final Connection pooledConnection, final String userid) throws java.sql.SQLException {
    CallableStatement cs = null;

    final String localId = (String) ContextManagerFactory.instance().getProperty(LOCAL_ID);
    Object[] roles = getUserRoles(userid).toArray();
    ARRAY newArray = null;

    Connection connection = null;
    try {
        // Extract the actual oracle connection
        connection = pooledConnection.unwrap(oracle.jdbc.OracleConnection.class);
        if (!(connection instanceof oracle.jdbc.OracleConnection)) {
        	throw new Exception(String.format("Not an Oracle Connection : %s",connection.getClass().getName()));
        }

        cs = connection.prepareCall(CALL_JAFFA_SEC_SET_USERID);
        cs.setString(1,userid);

        newArray = ((oracle.jdbc.OracleConnection)connection).createARRAY(ROLE,roles);
        cs.setArray(2,newArray);
        cs.setString(3,localId);

        if (log.isDebugEnabled()) {
            // Log the call to the stored procedure
            debugStatement(userid, localId, roles, newArray);
        }

        if(log.isDebugEnabled()) log.debug("Calling the Stored Procedure to Set the Context");
        cs.execute();
        connection.commit();
    }
    catch (Exception exception) {
    	final String message = String.format(String.format(SET_THE_CONTEXT_ERROR_MESSAGE,exception.getMessage()));
        log.error(message, exception);
        if (connection != null)
        	connection.rollback();
        throw new UOWSecurityException(message, exception);
    }
    finally {
        roles = null;
        if (newArray != null)
            newArray.free();
        if (cs != null) {
            cs.clearParameters();
            cs.close();
            cs = null;
        }
    }
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/** Returns the CallableStatement object for the input sql.
 * @param sql the sql statement.
 * @throws SQLException if any database error occurs.
 * @return the CallableStatement object.
 */
public CallableStatement getCallableStatement(String sql) throws SQLException {
    final CallableStatement cstmt = m_connection.prepareCall(sql);
    cstmt.clearBatch();
    cstmt.clearParameters();
    cstmt.clearWarnings();
    registerStatement(cstmt, null);
    return cstmt;
}
 

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包/类
/**
 * 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;
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
public void testOutParamsNoBodies() throws Exception {
    if (versionMeetsMinimum(5, 0)) {
        CallableStatement storedProc = null;

        Properties props = new Properties();
        props.setProperty("noAccessToProcedureBodies", "true");

        Connection spConn = getConnectionWithProps(props);

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

        storedProc = spConn.prepareCall("{call testOutParam(?, ?)}");

        storedProc.setInt(1, 5);
        storedProc.registerOutParameter(2, Types.INTEGER);

        storedProc.execute();

        int indexedOutParamToTest = storedProc.getInt(2);

        assertTrue("Output value not returned correctly", indexedOutParamToTest == 6);

        storedProc.clearParameters();
        storedProc.setInt(1, 32);
        storedProc.registerOutParameter(2, Types.INTEGER);

        storedProc.execute();

        indexedOutParamToTest = storedProc.getInt(2);

        assertTrue("Output value not returned correctly", indexedOutParamToTest == 33);
    }
}