Java Code Examples for java.sql.Connection.prepareCall()

The following are Jave code examples for showing how to use prepareCall() of the java.sql.Connection class. You can vote up the examples you like. Your votes will be used in our system to get more good examples.
+ Save this method
Example 1
Project: the-vigilantes   File: CallableStatementRegressionTest.java   View Source Code Vote up 6 votes
/**
 * Tests fix for Bug#12417 - stored procedure catalog name is case-sensitive
 * on Windows (this is actually a server bug, but we have a workaround in
 * place for it now).
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug12417() throws Exception {
    if (serverSupportsStoredProcedures() && isServerRunningOnWindows()) {

        createProcedure("testBug12417", "()\nBEGIN\nSELECT 1;end\n");

        Connection ucCatalogConn = null;

        try {
            ucCatalogConn = getConnectionWithProps((Properties) null);
            ucCatalogConn.setCatalog(this.conn.getCatalog().toUpperCase());
            ucCatalogConn.prepareCall("{call testBug12417()}");
        } finally {
            if (ucCatalogConn != null) {
                ucCatalogConn.close();
            }
        }
    }
}
 
Example 2
Project: OpenVertretung   File: StatementRegressionTest.java   View Source Code Vote up 6 votes
/**
 * Tests fix for Bug#78961 - Can't call MySQL procedure with InOut parameters in Fabric environment.
 * 
 * Although this is a Fabric related bug we are able reproduce it using a couple of multi-host connections.
 */
public void testBug78961() throws Exception {
    createProcedure("testBug78961", "(IN c1 FLOAT, IN c2 FLOAT, OUT h FLOAT, INOUT t FLOAT) BEGIN SET h = SQRT(c1 * c1 + c2 * c2); SET t = t + h; END;");

    Connection highLevelConn = getLoadBalancedConnection(null);
    assertTrue(highLevelConn.getClass().getName().startsWith("com.sun.proxy") || highLevelConn.getClass().getName().startsWith("$Proxy"));

    Connection lowLevelConn = getMasterSlaveReplicationConnection(null);
    // This simulates the behavior from Fabric connections that are causing the problem.
    ((ReplicationConnection) lowLevelConn).setProxy((MySQLConnection) highLevelConn);

    CallableStatement cstmt = lowLevelConn.prepareCall("{CALL testBug78961 (?, ?, ?, ?)}");
    cstmt.setFloat(1, 3.0f);
    cstmt.setFloat(2, 4.0f);
    cstmt.setFloat(4, 5.0f);
    cstmt.registerOutParameter(3, Types.FLOAT);
    cstmt.registerOutParameter(4, Types.FLOAT);
    cstmt.execute();

    assertEquals(5.0f, cstmt.getFloat(3));
    assertEquals(10.0f, cstmt.getFloat(4));
}
 
Example 3
Project: ats-framework   File: SQLServerDbWriteAccess.java   View Source Code Vote up 6 votes
public void deleteTestcase(
                            List<Object> objectsToDelete ) throws DatabaseAccessException {

    StringBuilder testcaseIds = new StringBuilder();
    for (Object obj : objectsToDelete) {
        testcaseIds.append( ((Testcase) obj).testcaseId);
        testcaseIds.append(",");
    }
    testcaseIds.delete(testcaseIds.length() - 1, testcaseIds.length());

    final String errMsg = "Unable to delete testcase(s) with id " + testcaseIds;

    Connection connection = getConnection();
    CallableStatement callableStatement = null;
    try {
        callableStatement = connection.prepareCall("{ call sp_delete_testcase(?) }");
        callableStatement.setString(1, testcaseIds.toString());
        callableStatement.execute();
    } catch (SQLException e) {
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        DbUtils.close(connection, callableStatement);
    }
}
 
Example 4
Project: ats-framework   File: SQLServerDbReadAccess.java   View Source Code Vote up 6 votes
public int getSuiteMessagesCount( String whereClause ) throws DatabaseAccessException {

        String sqlLog = new SqlRequestFormatter().add("where", whereClause).format();
        Connection connection = getConnection();
        CallableStatement callableStatement = null;
        ResultSet rs = null;
        try {

            callableStatement = connection.prepareCall("{ call sp_get_suite_messages_count(?) }");
            callableStatement.setString(1, whereClause);

            rs = callableStatement.executeQuery();
            int messagesCount = 0;
            if (rs.next()) {
                messagesCount = rs.getInt("messagesCount");
            }
            logQuerySuccess(sqlLog, "suite messages count", messagesCount);

            return messagesCount;
        } catch (Exception e) {
            throw new DatabaseAccessException("Error when " + sqlLog, e);
        } finally {
            DbUtils.closeResultSet(rs);
            DbUtils.close(connection, callableStatement);
        }
    }
 
Example 5
Project: ProyectoPacientes   File: StatementRegressionTest.java   View Source Code Vote up 6 votes
/**
 * Tests fix for Bug#78961 - Can't call MySQL procedure with InOut parameters in Fabric environment.
 * 
 * Although this is a Fabric related bug we are able reproduce it using a couple of multi-host connections.
 */
public void testBug78961() throws Exception {
    createProcedure("testBug78961", "(IN c1 FLOAT, IN c2 FLOAT, OUT h FLOAT, INOUT t FLOAT) BEGIN SET h = SQRT(c1 * c1 + c2 * c2); SET t = t + h; END;");

    Connection highLevelConn = getLoadBalancedConnection(null);
    assertTrue(highLevelConn.getClass().getName().startsWith("com.sun.proxy") || highLevelConn.getClass().getName().startsWith("$Proxy"));

    Connection lowLevelConn = getMasterSlaveReplicationConnection(null);
    // This simulates the behavior from Fabric connections that are causing the problem.
    ((ReplicationConnection) lowLevelConn).setProxy((MySQLConnection) highLevelConn);

    CallableStatement cstmt = lowLevelConn.prepareCall("{CALL testBug78961 (?, ?, ?, ?)}");
    cstmt.setFloat(1, 3.0f);
    cstmt.setFloat(2, 4.0f);
    cstmt.setFloat(4, 5.0f);
    cstmt.registerOutParameter(3, Types.FLOAT);
    cstmt.registerOutParameter(4, Types.FLOAT);
    cstmt.execute();

    assertEquals(5.0f, cstmt.getFloat(3));
    assertEquals(10.0f, cstmt.getFloat(4));
}
 
Example 6
Project: unitimes   File: BlobRoomAvailabilityService.java   View Source Code Vote up 6 votes
protected void sendRequest(Document request) throws IOException {
    try {
        StringWriter writer = new StringWriter();
        (new XMLWriter(writer,OutputFormat.createPrettyPrint())).write(request);
        writer.flush(); writer.close();
        SessionImplementor session = (SessionImplementor)new _RootDAO().getSession();
        Connection connection = session.getJdbcConnectionAccess().obtainConnection();
        try {
            CallableStatement call = connection.prepareCall(iRequestSql);
            call.setString(1, writer.getBuffer().toString());
            call.execute();
            call.close();
        } finally {
        	session.getJdbcConnectionAccess().releaseConnection(connection);
        }
    } catch (Exception e) {
        sLog.error("Unable to send request: "+e.getMessage(),e);
    } finally {
        _RootDAO.closeCurrentThreadSessions();
    }
}
 
Example 7
Project: ats-framework   File: SQLServerDbReadAccess.java   View Source Code Vote up 5 votes
public Map<String, Integer>
        getNumberOfCheckpointsPerQueue( String testcaseIds ) throws DatabaseAccessException {

    Map<String, Integer> allStatistics = new HashMap<String, Integer>();

    String sqlLog = new SqlRequestFormatter().add("testcase ids", testcaseIds).format();

    Connection connection = getConnection();
    CallableStatement callableStatement = null;
    ResultSet rs = null;
    try {

        callableStatement = connection.prepareCall("{ call sp_get_number_of_checkpoints_per_queue(?) }");
        callableStatement.setString(1, testcaseIds);

        rs = callableStatement.executeQuery();
        int numberRecords = 0;
        while (rs.next()) {
            String name = rs.getString("name");
            int queueNumbers = rs.getInt("numberOfQueue");
            allStatistics.put(name, queueNumbers);
        }

        logQuerySuccess(sqlLog, "system statistics", numberRecords);
    } catch (Exception e) {
        throw new DatabaseAccessException("Error when " + sqlLog, e);
    } finally {
        DbUtils.closeResultSet(rs);
        DbUtils.close(connection, callableStatement);
    }

    return allStatistics;
}
 
Example 8
Project: BibliotecaPS   File: ConnectionTest.java   View Source Code Vote up 5 votes
private void testInterfaceImplementation(Connection connToCheck) throws Exception {
    Method[] dbmdMethods = java.sql.DatabaseMetaData.class.getMethods();

    // can't do this statically, as we return different
    // implementations depending on JDBC version
    DatabaseMetaData dbmd = connToCheck.getMetaData();

    checkInterfaceImplemented(dbmdMethods, dbmd.getClass(), dbmd);

    Statement stmtToCheck = connToCheck.createStatement();

    checkInterfaceImplemented(java.sql.Statement.class.getMethods(), stmtToCheck.getClass(), stmtToCheck);

    PreparedStatement pStmtToCheck = connToCheck.prepareStatement("SELECT 1");
    ParameterMetaData paramMd = pStmtToCheck.getParameterMetaData();

    checkInterfaceImplemented(java.sql.PreparedStatement.class.getMethods(), pStmtToCheck.getClass(), pStmtToCheck);
    checkInterfaceImplemented(java.sql.ParameterMetaData.class.getMethods(), paramMd.getClass(), paramMd);

    pStmtToCheck = ((com.mysql.jdbc.Connection) connToCheck).serverPrepareStatement("SELECT 1");

    checkInterfaceImplemented(java.sql.PreparedStatement.class.getMethods(), pStmtToCheck.getClass(), pStmtToCheck);
    ResultSet toCheckRs = connToCheck.createStatement().executeQuery("SELECT 1");
    checkInterfaceImplemented(java.sql.ResultSet.class.getMethods(), toCheckRs.getClass(), toCheckRs);
    toCheckRs = connToCheck.createStatement().executeQuery("SELECT 1");
    checkInterfaceImplemented(java.sql.ResultSetMetaData.class.getMethods(), toCheckRs.getMetaData().getClass(), toCheckRs.getMetaData());

    if (versionMeetsMinimum(5, 0, 0)) {
        createProcedure("interfaceImpl", "(IN p1 INT)\nBEGIN\nSELECT 1;\nEND");

        CallableStatement cstmt = connToCheck.prepareCall("{CALL interfaceImpl(?)}");

        checkInterfaceImplemented(java.sql.CallableStatement.class.getMethods(), cstmt.getClass(), cstmt);
    }
    checkInterfaceImplemented(java.sql.Connection.class.getMethods(), connToCheck.getClass(), connToCheck);
}
 
Example 9
Project: ProyectoPacientes   File: CallableStatementRegressionTest.java   View Source Code Vote up 5 votes
/**
 * 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();
        }
    }
}
 
Example 10
Project: ProyectoPacientes   File: MetaDataRegressionTest.java   View Source Code Vote up 5 votes
private void callFunction(CallableStatement cStmt, Connection c) throws SQLException {
    cStmt = c.prepareCall("{? = CALL testbug61203fn(?)}");
    cStmt.registerOutParameter(1, Types.INTEGER);
    cStmt.setFloat(2, 2);
    cStmt.execute();
    assertEquals(2f, cStmt.getInt(1), .001);
}
 
Example 11
Project: OpenVertretung   File: CallableStatementTest.java   View Source Code Vote up 5 votes
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();
        }
    }
}
 
Example 12
Project: the-vigilantes   File: CallableStatementTest.java   View Source Code Vote up 5 votes
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();
        }
    }
}
 
Example 13
Project: BibliotecaPS   File: MetaDataRegressionTest.java   View Source Code Vote up 5 votes
private void callFunction(CallableStatement cStmt, Connection c) throws SQLException {
    cStmt = c.prepareCall("{? = CALL testbug61203fn(?)}");
    cStmt.registerOutParameter(1, Types.INTEGER);
    cStmt.setFloat(2, 2);
    cStmt.execute();
    assertEquals(2f, cStmt.getInt(1), .001);
}
 
Example 14
Project: ats-framework   File: SQLServerDbReadAccess.java   View Source Code Vote up 5 votes
public int getTestcasesCount( String whereClause ) throws DatabaseAccessException {

        String sqlLog = new SqlRequestFormatter().add("where", whereClause).format();
        Connection connection = getConnection();
        CallableStatement callableStatement = null;
        ResultSet rs = null;
        try {

            callableStatement = connection.prepareCall("{ call sp_get_testcases_count(?) }");
            callableStatement.setString(1, whereClause);

            rs = callableStatement.executeQuery();
            int testcasesCount = 0;
            while (rs.next()) {
                testcasesCount = rs.getInt("testcasesCount");
                logQuerySuccess(sqlLog, "test cases", testcasesCount);
                break;
            }

            return testcasesCount;
        } catch (Exception e) {
            throw new DatabaseAccessException("Error when " + sqlLog, e);
        } finally {
            DbUtils.closeResultSet(rs);
            DbUtils.close(connection, callableStatement);
        }
    }
 
Example 15
Project: ProyectoPacientes   File: MetaDataRegressionTest.java   View Source Code 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: ats-framework   File: SQLServerDbWriteAccess.java   View Source Code 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 17
Project: dremio-oss   File: Drill2489CallsAfterCloseThrowExceptionsTest.java   View Source Code Vote up 4 votes
@BeforeClass
public static void setUpClosedObjects() throws Exception {
  // (Note: Can't use JdbcTest's connect(...) for this test class.)

  final Connection connToClose =
      new Driver().connect("jdbc:dremio:zk=local",
                           JdbcAssert.getDefaultProperties());
  final Connection connToKeep =
      new Driver().connect("jdbc:dremio:zk=local",
                           JdbcAssert.getDefaultProperties());

  final Statement plainStmtToClose = connToKeep.createStatement();
  final Statement plainStmtToKeep = connToKeep.createStatement();
  final PreparedStatement preparedStmtToClose =
      connToKeep.prepareStatement("VALUES 'PreparedStatement query'");
  try {
    connToKeep.prepareCall("VALUES 'CallableStatement query'");
    fail("Test seems to be out of date.  Was prepareCall(...) implemented?");
  }
  catch (SQLException | UnsupportedOperationException e) {
    // Expected.
  }

  final ResultSet resultSetToCloseOnStmtToClose =
      plainStmtToClose.executeQuery("VALUES 'plain Statement query'");
  resultSetToCloseOnStmtToClose.next();
  final ResultSet resultSetToCloseOnStmtToKeep =
      plainStmtToKeep.executeQuery("VALUES 'plain Statement query'");
  resultSetToCloseOnStmtToKeep.next();

  final ResultSetMetaData rsmdForClosedStmt =
      resultSetToCloseOnStmtToKeep.getMetaData();
  final ResultSetMetaData rsmdForOpenStmt =
      resultSetToCloseOnStmtToClose.getMetaData();

  final DatabaseMetaData dbmd = connToClose.getMetaData();

  connToClose.close();
  plainStmtToClose.close();
  preparedStmtToClose.close();
  resultSetToCloseOnStmtToClose.close();
  resultSetToCloseOnStmtToKeep.close();

  closedConn = connToClose;
  openConn = connToKeep;
  closedPlainStmtOfOpenConn = plainStmtToClose;
  closedPreparedStmtOfOpenConn = preparedStmtToClose;
  closedResultSetOfClosedStmt = resultSetToCloseOnStmtToClose;
  closedResultSetOfOpenStmt = resultSetToCloseOnStmtToKeep;
  resultSetMetaDataOfClosedResultSet = rsmdForOpenStmt;
  resultSetMetaDataOfClosedStmt = rsmdForClosedStmt;
  databaseMetaDataOfClosedConn = dbmd;

  // Self-check that member variables are set (and objects are in right open
  // or closed state):
  assertTrue("Test setup error", closedConn.isClosed());
  assertFalse("Test setup error", openConn.isClosed());
  assertTrue("Test setup error", closedPlainStmtOfOpenConn.isClosed());
  assertTrue("Test setup error", closedPreparedStmtOfOpenConn.isClosed());
  assertTrue("Test setup error", closedResultSetOfClosedStmt.isClosed());
  assertTrue("Test setup error", closedResultSetOfOpenStmt.isClosed());
  // (No ResultSetMetaData.isClosed() or DatabaseMetaData.isClosed():)
  assertNotNull("Test setup error", resultSetMetaDataOfClosedResultSet);
  assertNotNull("Test setup error", resultSetMetaDataOfClosedStmt);
  assertNotNull("Test setup error", databaseMetaDataOfClosedConn);
}
 
Example 18
Project: lams   File: JdbcTemplate.java   View Source Code Vote up 4 votes
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
	return con.prepareCall(this.callString);
}
 
Example 19
Project: the-vigilantes   File: CallableStatementTest.java   View Source Code Vote up 2 votes
/**
 * Tests parsing of stored procedures
 * 
 * @throws Exception
 *             if an error occurs.
 */
public void testSPCache() throws Exception {
    if (versionMeetsMinimum(5, 0)) {

        CallableStatement storedProc = null;

        createProcedure("testSpParse", "(IN FOO VARCHAR(15))\nBEGIN\nSELECT 1;\nend\n");

        int numIterations = 10;

        long startTime = System.currentTimeMillis();

        for (int i = 0; i < numIterations; i++) {
            storedProc = this.conn.prepareCall("{call testSpParse(?)}");
            storedProc.close();
        }

        long elapsedTime = System.currentTimeMillis() - startTime;

        System.out.println("Standard parsing/execution: " + elapsedTime + " ms");

        storedProc = this.conn.prepareCall("{call testSpParse(?)}");
        storedProc.setString(1, "abc");
        this.rs = storedProc.executeQuery();

        assertTrue(this.rs.next());
        assertTrue(this.rs.getInt(1) == 1);

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

        Connection cachedSpConn = getConnectionWithProps(props);

        startTime = System.currentTimeMillis();

        for (int i = 0; i < numIterations; i++) {
            storedProc = cachedSpConn.prepareCall("{call testSpParse(?)}");
            storedProc.close();
        }

        elapsedTime = System.currentTimeMillis() - startTime;

        System.out.println("Cached parse stage: " + elapsedTime + " ms");

        storedProc = cachedSpConn.prepareCall("{call testSpParse(?)}");
        storedProc.setString(1, "abc");
        this.rs = storedProc.executeQuery();

        assertTrue(this.rs.next());
        assertTrue(this.rs.getInt(1) == 1);

    }
}
 
Example 20
Project: OpenVertretung   File: CallableStatementTest.java   View Source Code Vote up 2 votes
/**
 * Tests parsing of stored procedures
 * 
 * @throws Exception
 *             if an error occurs.
 */
public void testSPCache() throws Exception {
    if (versionMeetsMinimum(5, 0)) {

        CallableStatement storedProc = null;

        createProcedure("testSpParse", "(IN FOO VARCHAR(15))\nBEGIN\nSELECT 1;\nend\n");

        int numIterations = 10;

        long startTime = System.currentTimeMillis();

        for (int i = 0; i < numIterations; i++) {
            storedProc = this.conn.prepareCall("{call testSpParse(?)}");
            storedProc.close();
        }

        long elapsedTime = System.currentTimeMillis() - startTime;

        System.out.println("Standard parsing/execution: " + elapsedTime + " ms");

        storedProc = this.conn.prepareCall("{call testSpParse(?)}");
        storedProc.setString(1, "abc");
        this.rs = storedProc.executeQuery();

        assertTrue(this.rs.next());
        assertTrue(this.rs.getInt(1) == 1);

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

        Connection cachedSpConn = getConnectionWithProps(props);

        startTime = System.currentTimeMillis();

        for (int i = 0; i < numIterations; i++) {
            storedProc = cachedSpConn.prepareCall("{call testSpParse(?)}");
            storedProc.close();
        }

        elapsedTime = System.currentTimeMillis() - startTime;

        System.out.println("Cached parse stage: " + elapsedTime + " ms");

        storedProc = cachedSpConn.prepareCall("{call testSpParse(?)}");
        storedProc.setString(1, "abc");
        this.rs = storedProc.executeQuery();

        assertTrue(this.rs.next());
        assertTrue(this.rs.getInt(1) == 1);

    }
}