Java CallableStatement.close方法代码示例

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包/类
/**
 * Test for Bug#73070 - prepareCall() throws NPE
 * 
 * To test this, we create a basic stored procedure with a
 * parameter, call it and check the result.
 */
public void testBug73070() throws Exception {
    if (!this.isSetForFabricTest) {
        return;
    }
    this.conn = (FabricMySQLConnection) getNewDefaultDataSource().getConnection(this.username, this.password);
    this.conn.setServerGroupName("fabric_test1_global");

    this.conn.createStatement().executeUpdate("drop procedure if exists bug73070");
    this.conn.createStatement().executeUpdate("create procedure bug73070(in x integer) select x");
    CallableStatement stmt = this.conn.prepareCall("{call bug73070(?)}");
    stmt.setInt(1, 42);
    ResultSet rs = stmt.executeQuery();
    rs.next();
    assertEquals(42, rs.getInt(1));
    rs.close();
    stmt.close();
    this.conn.createStatement().executeUpdate("drop procedure bug73070");

    this.conn.close();
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#21462 - JDBC (and ODBC) specifications allow
 * no-parenthesis CALL statements for procedures with no arguments, MySQL
 * server does not.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug21462() throws Exception {
    if (!serverSupportsStoredProcedures()) {
        return;
    }

    createProcedure("testBug21462", "() BEGIN SELECT 1; END");

    CallableStatement cstmt = null;

    try {
        cstmt = this.conn.prepareCall("{CALL testBug21462}");
        cstmt.execute();
    } finally {
        if (cstmt != null) {
            cstmt.close();
        }
    }

}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
public void testNotReallyCallableStatement() throws Exception {
    if (!versionMeetsMinimum(5, 0)) {
        return;
    }

    CallableStatement cstmt = null;

    try {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testNotReallyCallableStatement");
        cstmt = this.conn.prepareCall("CREATE TABLE testNotReallyCallableStatement(field1 INT)");

    } finally {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testNotReallyCallableStatement");

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

import java.sql.CallableStatement; //导入方法依赖的package包/类
public void testBug35199() throws Exception {
    if (!versionMeetsMinimum(5, 0)) {
        return;
    }

    createFunction("test_function", "(a varchar(40), b bigint(20), c varchar(80)) RETURNS bigint(20) LANGUAGE SQL DETERMINISTIC "
            + "MODIFIES SQL DATA COMMENT 'bbb' BEGIN RETURN 1; END; ");

    CallableStatement callable = null;
    try {
        callable = this.conn.prepareCall("{? = call test_function(?,101,?)}");
        callable.registerOutParameter(1, Types.BIGINT);

        callable.setString(2, "FOO");
        callable.setString(3, "BAR");
        callable.executeUpdate();
    } finally {
        if (callable != null) {
            callable.close();
        }
    }
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#22024 - Newlines causing whitespace to span confuse
 * procedure parser when getting parameter metadata for stored procedures.
 * 
 * @throws Exception
 *             if the test fails
 */
public void testBug22024() throws Exception {
    if (!serverSupportsStoredProcedures()) {
        return;
    }

    createProcedure("testBug22024_1", "(\r\n)\r\n BEGIN SELECT 1; END");
    createProcedure("testBug22024_2", "(\r\na INT)\r\n BEGIN SELECT 1; END");

    CallableStatement cstmt = null;

    try {
        cstmt = this.conn.prepareCall("{CALL testBug22024_1()}");
        cstmt.execute();

        cstmt = this.conn.prepareCall("{CALL testBug22024_2(?)}");
        cstmt.setInt(1, 1);
        cstmt.execute();
    } finally {
        if (cstmt != null) {
            cstmt.close();
        }
    }

}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
public static void importXml(String baseFileName){
    	Debug.info("filename = " + baseFileName);
   	try {
   		String fileReceiveSql =
   	        ApplicationProperties.getProperty("tmtbl.data.exchange.receive.file","{?= call timetable.receive_xml_file.receive_file(?, ?)}");
   		String exchangeDir = 
   	    	ApplicationProperties.getProperty("tmtbl.data.exchange.directory", "LOAD_SMASDEV");
           SessionImplementor session = (SessionImplementor)new _RootDAO().getSession();
           Connection connection = session.getJdbcConnectionAccess().obtainConnection();
           CallableStatement call = connection.prepareCall(fileReceiveSql);
           call.registerOutParameter(1, java.sql.Types.CLOB);
	    call.setString(2, exchangeDir);
           call.setString(3, baseFileName);
           call.execute();
           String response = call.getString(1);
           call.close();
           session.getJdbcConnectionAccess().releaseConnection(connection);
           if (response==null || response.length()==0) return;
           StringReader reader = new StringReader(response);
           Document document = (new SAXReader()).read(reader);
           reader.close();
           DataExchangeHelper.importDocument(document, null, null);           
	} catch (Exception e) {
		e.printStackTrace();
	} 
	
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
private void executeBatchedStoredProc(Connection c) throws Exception {
    this.stmt.executeUpdate("TRUNCATE TABLE testBatchTable");

    CallableStatement storedProc = c.prepareCall("{call testBatch(?)}");

    try {
        int numBatches = 300;

        for (int i = 0; i < numBatches; i++) {
            storedProc.setInt(1, i + 1);
            storedProc.addBatch();
        }

        int[] counts = storedProc.executeBatch();

        assertEquals(numBatches, counts.length);

        for (int i = 0; i < numBatches; i++) {
            assertEquals(1, counts[i]);
        }

        this.rs = this.stmt.executeQuery("SELECT field1 FROM testBatchTable ORDER BY field1 ASC");

        for (int i = 0; i < numBatches; i++) {
            assertTrue(this.rs.next());
            assertEquals(i + 1, this.rs.getInt(1));
        }
    } finally {

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

import java.sql.CallableStatement; //导入方法依赖的package包/类
public void CloseConn(CallableStatement cStmt) {
	try{
		if(cStmt != null) {
			cStmt.close();
		}
		if (conn != null && IsTransaction == false) {
			conn.commit();
			conn.close();
			conn = null;
		}	
	}catch(Exception e){
		LogUtils.error(CommonUtil.getStackTrace(e),DataAdapter.class);
	}
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#28689 - CallableStatement.executeBatch() doesn't work
 * when connection property "noAccessToProcedureBodies" has been set to
 * "true".
 * 
 * The fix involves changing the behavior of "noAccessToProcedureBodies", in
 * that the driver will now report all paramters as "IN" paramters but allow
 * callers to call registerOutParameter() on them.
 * 
 * @throws Exception
 */
public void testBug28689() throws Exception {
    if (!versionMeetsMinimum(5, 0)) {
        return; // no stored procedures
    }

    createTable("testBug28689", "(" +

            "`id` int(11) NOT NULL auto_increment,`usuario` varchar(255) default NULL,PRIMARY KEY  (`id`))");

    this.stmt.executeUpdate("INSERT INTO testBug28689 (usuario) VALUES ('AAAAAA')");

    createProcedure("sp_testBug28689", "(tid INT)\nBEGIN\nUPDATE testBug28689 SET usuario = 'BBBBBB' WHERE id = tid;\nEND");

    Connection noProcedureBodiesConn = getConnectionWithProps("noAccessToProcedureBodies=true");
    CallableStatement cStmt = null;

    try {
        cStmt = noProcedureBodiesConn.prepareCall("{CALL sp_testBug28689(?)}");
        cStmt.setInt(1, 1);
        cStmt.addBatch();
        cStmt.executeBatch();

        assertEquals("BBBBBB", getSingleIndexedValueWithQuery(noProcedureBodiesConn, 1, "SELECT `usuario` FROM testBug28689 WHERE id=1"));
    } finally {
        if (cStmt != null) {
            cStmt.close();
        }

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

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Tests fix for Bug#84324 - CallableStatement.extractProcedureName() not work when catalog name with dash.
 */
public void testBug84324() throws Exception {
    createDatabase("`testBug84324-db`");

    /*
     * Test procedure.
     */
    createProcedure("`testBug84324-db`.`testBug84324-proc`", "(IN a INT, INOUT b VARCHAR(100)) BEGIN SELECT a, b; END");

    final CallableStatement cstmtP = this.conn.prepareCall("CALL testBug84324-db.testBug84324-proc(?, ?)");
    ParameterMetaData pmd = cstmtP.getParameterMetaData();

    assertEquals(2, pmd.getParameterCount());
    // 1st parameter
    assertEquals("INT", pmd.getParameterTypeName(1));
    assertEquals(Types.INTEGER, pmd.getParameterType(1));
    assertEquals(Integer.class.getName(), pmd.getParameterClassName(1));
    assertEquals(ParameterMetaData.parameterModeIn, pmd.getParameterMode(1));
    // 2nd parameter
    assertEquals("VARCHAR", pmd.getParameterTypeName(2));
    assertEquals(Types.VARCHAR, pmd.getParameterType(2));
    assertEquals(String.class.getName(), pmd.getParameterClassName(2));
    assertEquals(ParameterMetaData.parameterModeInOut, pmd.getParameterMode(2));

    cstmtP.setInt(1, 1);
    cstmtP.setString(2, "foo");
    assertThrows(SQLException.class, new Callable<Void>() {
        public Void call() throws Exception {
            cstmtP.execute();
            return null;
        }
    }); // Although the procedure metadata could be obtained, the end query actually fails due to syntax errors.
    cstmtP.close();

    /*
     * Test function.
     */
    createFunction("`testBug84324-db`.`testBug84324-func`", "(a INT, b VARCHAR(123)) RETURNS INT BEGIN RETURN a + LENGTH(b); END");

    final CallableStatement cstmtF = this.conn.prepareCall("{? = CALL testBug84324-db.testBug84324-func(?, ?)}");
    pmd = cstmtF.getParameterMetaData();

    assertEquals(3, pmd.getParameterCount());
    // 1st parameter
    assertEquals("INT", pmd.getParameterTypeName(1));
    assertEquals(Types.INTEGER, pmd.getParameterType(1));
    assertEquals(Integer.class.getName(), pmd.getParameterClassName(1));
    assertEquals(ParameterMetaData.parameterModeOut, pmd.getParameterMode(1));
    // 2nd parameter
    assertEquals("INT", pmd.getParameterTypeName(2));
    assertEquals(Types.INTEGER, pmd.getParameterType(2));
    assertEquals(Integer.class.getName(), pmd.getParameterClassName(2));
    assertEquals(ParameterMetaData.parameterModeIn, pmd.getParameterMode(2));
    // 3rd parameter
    assertEquals("VARCHAR", pmd.getParameterTypeName(3));
    assertEquals(Types.VARCHAR, pmd.getParameterType(3));
    assertEquals(String.class.getName(), pmd.getParameterClassName(3));
    assertEquals(ParameterMetaData.parameterModeIn, pmd.getParameterMode(3));

    cstmtF.registerOutParameter(1, Types.INTEGER);
    cstmtF.setInt(2, 1);
    cstmtF.setString(3, "foo");
    assertThrows(SQLException.class, new Callable<Void>() {
        public Void call() throws Exception {
            cstmtF.execute();
            return null;
        }
    }); // Although the function metadata could be obtained, the end query actually fails due to syntax errors.
    cstmtP.close();
    cstmtF.close();
}
 

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包/类
/**
 * Tests the new parameter parser that doesn't require "BEGIN" or "\n" at
 * end of parameter declaration
 * 
 * @throws Exception
 */
public void testParameterParser() throws Exception {

    if (!versionMeetsMinimum(5, 0)) {
        return;
    }

    CallableStatement cstmt = null;

    try {

        createTable("t1", "(id   char(16) not null default '', data int not null)");
        createTable("t2", "(s   char(16),  i   int,  d   double)");

        createProcedure("foo42", "() insert into test.t1 values ('foo', 42);");
        this.conn.prepareCall("{CALL foo42()}");
        this.conn.prepareCall("{CALL foo42}");

        createProcedure("bar", "(x char(16), y int, z DECIMAL(10)) insert into test.t1 values (x, y);");
        cstmt = this.conn.prepareCall("{CALL bar(?, ?, ?)}");

        ParameterMetaData md = cstmt.getParameterMetaData();
        assertEquals(3, md.getParameterCount());
        assertEquals(Types.CHAR, md.getParameterType(1));
        assertEquals(Types.INTEGER, md.getParameterType(2));
        assertEquals(Types.DECIMAL, md.getParameterType(3));

        createProcedure("p", "() label1: WHILE @a=0 DO SET @a=1; END WHILE");
        this.conn.prepareCall("{CALL p()}");

        createFunction("f", "() RETURNS INT NO SQL return 1; ");
        cstmt = this.conn.prepareCall("{? = CALL f()}");

        md = cstmt.getParameterMetaData();
        assertEquals(Types.INTEGER, md.getParameterType(1));
    } finally {
        if (cstmt != null) {
            cstmt.close();
        }
    }
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#87704 (26771560) - THE STREAM GETS THE RESULT SET ?THE DRIVER SIDE GET WRONG ABOUT GETLONG().
 * 
 * @throws Exception
 *             if an error occurs.
 */
public void testBug87704() throws Exception {
    if (!serverSupportsStoredProcedures()) {
        return;
    }

    createProcedure("testBug87704",
            "(IN PARAMIN BIGINT, OUT PARAM_OUT_LONG BIGINT, OUT PARAM_OUT_STR VARCHAR(100))\nBEGIN\nSET PARAM_OUT_LONG = PARAMIN + 100000;\nSET PARAM_OUT_STR = concat('STR' ,PARAM_OUT_LONG);end\n");

    final Properties props = new Properties();
    props.setProperty("useSSL", "false");
    props.setProperty("useServerPrepStmts", "true");
    props.setProperty("cachePrepStmts", "true");
    props.setProperty("prepStmtCacheSize", "500");
    props.setProperty("prepStmtCacheSqlLimit", "2048");
    props.setProperty("useOldAliasMetadataBehavior", "true");
    props.setProperty("rewriteBatchedStatements", "true");
    props.setProperty("useCursorFetch", "true");
    props.setProperty("defaultFetchSize", "100");

    Connection con = getConnectionWithProps(props);

    CallableStatement callableStatement = null;
    try {
        callableStatement = con.prepareCall("call testBug87704(?,?,?)");
        callableStatement.setLong(1, 30214567L);
        callableStatement.registerOutParameter(2, Types.BIGINT);
        callableStatement.registerOutParameter(3, Types.VARCHAR);
        callableStatement.execute();
        System.out.println(callableStatement.getLong(2));
        System.out.println(callableStatement.getString(3));

        assertEquals(30314567L, callableStatement.getLong(2));
        assertEquals("STR30314567", callableStatement.getString(3));

    } finally {
        if (callableStatement != null) {
            callableStatement.close();
        }
        if (con != null) {
            con.close();
        }
    }
}
 

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);
    }

}