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

The following are Jave code examples for showing how to use registerOutParameter() 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: ProyectoPacientes   File: CallableStatementRegressionTest.java   Source Code and License Vote up 6 votes
/**
 * 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
Project: BibliotecaPS   File: CallableStatementRegressionTest.java   Source Code and License Vote up 6 votes
/**
 * 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
Project: lams   File: OracleTableMetaDataProvider.java   Source Code and License Vote up 6 votes
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) {
	}
}
 
Example 4
Project: ProyectoPacientes   File: CallableStatementRegressionTest.java   Source Code and License Vote up 6 votes
/**
 * 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));
}
 
Example 5
Project: ats-framework   File: SQLServerDbWriteAccess.java   Source Code and License Vote up 6 votes
public void updateMachineInfo(
                               String machineName,
                               String machineInfo,
                               boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to update the info about machine with name " + machineName;

    // then start the run
    final int indexRowsInserted = 3;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_update_machine_info(?, ?, ?) }");
        callableStatement.setString(1, machineName);
        callableStatement.setString(2, machineInfo);
        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);
        }
    }
}
 
Example 6
Project: ProyectoPacientes   File: MetaDataRegressionTest.java   Source Code and License Vote up 5 votes
private void callProcedure(CallableStatement cStmt, Connection c) throws SQLException {
    cStmt = c.prepareCall("{CALL testbug61203pr(?,?,?)}");
    cStmt.setFloat(1, 2);
    cStmt.setInt(2, 1);
    cStmt.setInt(3, 1);
    cStmt.registerOutParameter(1, Types.INTEGER);
    cStmt.execute();
    assertEquals(2f, cStmt.getInt(1), .001);
}
 
Example 7
Project: OpenVertretung   File: CallableStatementRegressionTest.java   Source Code and License Vote up 5 votes
public void testHugeNumberOfParameters() throws Exception {
    if (!serverSupportsStoredProcedures()) {
        return;
    }

    StringBuilder procDef = new StringBuilder("(OUT param_0 VARCHAR(32)");
    StringBuilder placeholders = new StringBuilder("?");

    for (int i = 1; i < 274; i++) {
        procDef.append(", OUT param_" + i + " VARCHAR(32)");
        placeholders.append(",?");
    }
    procDef.append(")\nBEGIN\nSELECT 1;\nEND");

    createProcedure("testHugeNumberOfParameters", procDef.toString());

    CallableStatement cStmt = null;

    try {
        cStmt = this.conn.prepareCall("{call testHugeNumberOfParameters(" + placeholders.toString() + ")}");
        cStmt.registerOutParameter(274, Types.VARCHAR);

        cStmt.execute();
    } finally {
        if (cStmt != null) {
            cStmt.close();
        }
    }
}
 
Example 8
Project: BibliotecaPS   File: MetaDataRegressionTest.java   Source Code and License Vote up 5 votes
private void callProcedure(CallableStatement cStmt, Connection c) throws SQLException {
    cStmt = c.prepareCall("{CALL testbug61203pr(?,?,?)}");
    cStmt.setFloat(1, 2);
    cStmt.setInt(2, 1);
    cStmt.setInt(3, 1);
    cStmt.registerOutParameter(1, Types.INTEGER);
    cStmt.execute();
    assertEquals(2f, cStmt.getInt(1), .001);
}
 
Example 9
Project: BibliotecaPS   File: CallableStatementRegressionTest.java   Source Code and License Vote up 4 votes
/**
 * 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 10
Project: ProyectoPacientes   File: StatementsTest.java   Source Code and License Vote up 4 votes
/**
 * 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));
}
 
Example 11
Project: ats-framework   File: SQLServerDbWriteAccess.java   Source Code and License Vote up 4 votes
public int startSuite(
                       String packageName,
                       String suiteName,
                       long timestamp,
                       int runId,
                       boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to start suite with name " + suiteName;
    // create a new suite

    timestamp = inUTC(timestamp);

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        // TODO : remove me after 3.6.0
        String dbVersionString = getDatabaseVersion();
        int dbVersion = Integer.parseInt(dbVersionString.replace(".", ""));

        if (dbVersion >= 350) {
            callableStatement = connection.prepareCall("{ call sp_start_suite(?, ?, ?, ?, ?, ?) }");

            if (packageName == null) {
                packageName = "";
            }
            callableStatement.setString("@package", packageName);
        } else {
            callableStatement = connection.prepareCall("{ call sp_start_suite(?, ?, ?, ?, ?) }");
        }
        callableStatement.setString("@suiteName", suiteName);
        callableStatement.setInt("@runId", runId);
        callableStatement.setTimestamp("@dateStart", new Timestamp(timestamp));
        callableStatement.registerOutParameter("@RowsInserted", Types.INTEGER);
        callableStatement.registerOutParameter("@suiteId", Types.INTEGER);

        callableStatement.execute();

        if (callableStatement.getInt("@RowsInserted") != 1) {
            throw new DatabaseAccessException(errMsg);
        } else {
            if (callableStatement.getInt("@suiteId") == 0) {
                throw new DatabaseAccessException(errMsg + " - suite ID returned was 0");
            }
        }
        // get the result
        return callableStatement.getInt("@suiteId");

    } catch (Exception e) {
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }
}
 
Example 12
Project: BibliotecaPS   File: CallableStatementRegressionTest.java   Source Code and License Vote up 4 votes
/**
 * 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();
        }
    }
}
 
Example 13
Project: OpenVertretung   File: CallableStatementRegressionTest.java   Source Code and License Vote up 4 votes
/**
 * 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 14
Project: ats-framework   File: SQLServerDbWriteAccess.java   Source Code and License Vote up 4 votes
public void updateTestcase(
                            String suiteFullName,
                            String scenarioName,
                            String scenarioDescription,
                            String testcaseName,
                            String userNote,
                            int testcaseResult,
                            int testcaseId,
                            long timestamp,
                            boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to update testcase with name '" + testcaseName + "' and id " + testcaseId;

    timestamp = inUTC(timestamp);

    final int indexRowsUpdate = 9;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_update_testcase(?, ?, ?, ?, ?, ?, ?, ?, ?) }");
        callableStatement.setInt(1, testcaseId);
        callableStatement.setString(2, suiteFullName);
        callableStatement.setString(3, scenarioName);
        callableStatement.setString(4, scenarioDescription);
        callableStatement.setString(5, testcaseName);
        callableStatement.setString(6, userNote);
        callableStatement.setInt(7, testcaseResult);
        callableStatement.setTimestamp(8, new Timestamp(timestamp));
        callableStatement.registerOutParameter(indexRowsUpdate, Types.INTEGER);

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

}
 
Example 15
Project: OpenVertretung   File: MetaDataRegressionTest.java   Source Code and License Vote up 4 votes
/**
 * 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 16
Project: the-vigilantes   File: CallableStatementTest.java   Source Code and License Vote up 4 votes
/**
 * Tests functioning of inout parameters
 * 
 * @throws Exception
 *             if the test fails
 */

public void testInOutParams() throws Exception {
    if (versionMeetsMinimum(5, 0)) {
        CallableStatement storedProc = null;

        createProcedure("testInOutParam", "(IN p1 VARCHAR(255), INOUT p2 INT)\nbegin\n DECLARE z INT;\nSET z = p2 + 1;\nSET p2 = z;\n"
                + "SELECT p1;\nSELECT CONCAT('zyxw', p1);\nend\n");

        storedProc = this.conn.prepareCall("{call testInOutParam(?, ?)}");

        storedProc.setString(1, "abcd");
        storedProc.setInt(2, 4);
        storedProc.registerOutParameter(2, Types.INTEGER);

        storedProc.execute();

        assertEquals(5, storedProc.getInt(2));

    }
}
 
Example 17
Project: OpenVertretung   File: CallableStatementRegressionTest.java   Source Code and License Vote up 4 votes
public void testBitSp() throws Exception {
    if (!versionMeetsMinimum(5, 0)) {
        return;
    }

    createTable("`Bit_Tab`", "( `MAX_VAL` tinyint(1) default NULL, `MIN_VAL` tinyint(1) default NULL, `NULL_VAL` tinyint(1) default NULL)");

    createProcedure("Bit_Proc", "(out MAX_PARAM TINYINT, out MIN_PARAM TINYINT, out NULL_PARAM TINYINT)"
            + "begin select MAX_VAL, MIN_VAL, NULL_VAL  into MAX_PARAM, MIN_PARAM, NULL_PARAM from Bit_Tab; end");

    Boolean minBooleanVal;
    Boolean oRetVal;

    String Min_Val_Query = "SELECT MIN_VAL from Bit_Tab";
    //String sMaxBooleanVal = "1";
    // sMaxBooleanVal = "true";
    //Boolean bool = Boolean.valueOf("true");
    String Min_Insert = "insert into Bit_Tab values(1,0,null)";
    // System.out.println("Value to insert=" + extractVal(Min_Insert,1));
    CallableStatement cstmt;

    this.stmt.executeUpdate("delete from Bit_Tab");
    this.stmt.executeUpdate(Min_Insert);
    cstmt = this.conn.prepareCall("{call Bit_Proc(?,?,?)}");

    System.out.println("register the output parameters");
    cstmt.registerOutParameter(1, java.sql.Types.BIT);
    cstmt.registerOutParameter(2, java.sql.Types.BIT);
    cstmt.registerOutParameter(3, java.sql.Types.BIT);

    System.out.println("execute the procedure");
    cstmt.executeUpdate();

    System.out.println("invoke getBoolean method");
    boolean bRetVal = cstmt.getBoolean(2);
    oRetVal = new Boolean(bRetVal);
    minBooleanVal = new Boolean("false");
    this.rs = this.stmt.executeQuery(Min_Val_Query);
    if (oRetVal.equals(minBooleanVal)) {
        System.out.println("getBoolean returns the Minimum value ");
    } else {
        System.out.println("getBoolean() did not return the Minimum value, getBoolean Failed!");

    }
}
 
Example 18
Project: ats-framework   File: SQLServerDbWriteAccess.java   Source Code and License Vote up 4 votes
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);
        }
    }
}
 
Example 19
Project: OpenVertretung   File: CallableStatementTest.java   Source Code and License Vote up 3 votes
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 20
Project: uroborosql   File: OutParameter.java   Source Code and License Vote up 3 votes
/**
 * ステートメントに出力パラメータを登録。
 * @param callableStatement コーラブルステートメント
 * @param index パラメータインデックス
 * @return 次のパラメータインデックス
 * @throws SQLException SQL例外
 */
protected int setOutParameter(final CallableStatement callableStatement, int index) throws SQLException {
	callableStatement.registerOutParameter(index, sqlType);
	parameterLog(index);
	index++;
	return index;
}