Java Code Examples for java.sql.CallableStatement#clearParameters()

The following examples show how to use java.sql.CallableStatement#clearParameters() . These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
/**
 * 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));
}
 
Example 2
/**
 * 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));
}
 
Example 3
/**
 * 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 {
    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));

}
 
Example 4
/**
 * 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);
    }

}
 
Example 5
Source Project: r-course   File: MetaDataRegressionTest.java    License: MIT License 4 votes vote down vote up
/**
 * 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;
}
 
Example 6
/**
 * 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);
    }

}
 
Example 7
/**
 * 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;
}
 
Example 8
/**
 * 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 {
    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);
    }

}
 
Example 9
Source Project: r-course   File: CallableStatementTest.java    License: MIT License 3 votes vote down vote up
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);
    }
}
 
Example 10
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);
    }
}
 
Example 11
public void testOutParamsNoBodies() throws Exception {
    CallableStatement storedProc = null;

    Properties props = new Properties();
    props.setProperty(PropertyKey.noAccessToProcedureBodies.getKeyName(), "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);
}