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

The following examples show how to use java.sql.CallableStatement#executeBatch() . 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
public void testChangeIsoLevelProcedureSqlCallableBatch() throws SQLException {
    CallableStatement cs = prepareCall("CALL SET_ISOLATION_SQL(?)");
    for (int i = 0; i < isoLevels.length; ++i) {
        cs.setString(1, isoLevels[i].getSqlName());
        cs.addBatch();
    }
    try {
        cs.executeBatch();
    } catch (SQLException e) {
        SQLException prev = e;
        while (e != null) {
            prev = e;
            e = e.getNextException();
        }
        throw prev;
    }
    verifyCachedIsolation(cs.getConnection());
    cs.close();
}
 
Example 2
public void testChangeIsoLevelProcedureJdbcCallableBatch() throws SQLException {
    CallableStatement cs = prepareCall("CALL SET_ISOLATION_JDBC(?)");
    for (int i = 0; i < isoLevels.length; ++i) {
        cs.setInt(1, isoLevels[i].getIsoLevel());
        cs.addBatch();
    }
    try {
        cs.executeBatch();
    } catch (SQLException e) {
        SQLException prev = e;
        while (e != null) {
            prev = e;
            e = e.getNextException();
        }
        throw prev;
    }
    verifyCachedIsolation(cs.getConnection());
    cs.close();
}
 
Example 3
public void testChangeIsoLevelProcedureSqlCallableBatch() throws SQLException {
    CallableStatement cs = prepareCall("CALL SET_ISOLATION_SQL(?)");
    for (int i = 0; i < isoLevels.length; ++i) {
        cs.setString(1, isoLevels[i].getSqlName());
        cs.addBatch();
    }
    try {
        cs.executeBatch();
    } catch (SQLException e) {
        SQLException prev = e;
        while (e != null) {
            prev = e;
            e = e.getNextException();
        }
        throw prev;
    }
    verifyCachedIsolation(cs.getConnection());
    cs.close();
}
 
Example 4
public void testChangeIsoLevelProcedureJdbcCallableBatch() throws SQLException {
    CallableStatement cs = prepareCall("CALL SET_ISOLATION_JDBC(?)");
    for (int i = 0; i < isoLevels.length; ++i) {
        cs.setInt(1, isoLevels[i].getIsoLevel());
        cs.addBatch();
    }
    try {
        cs.executeBatch();
    } catch (SQLException e) {
        SQLException prev = e;
        while (e != null) {
            prev = e;
            e = e.getNextException();
        }
        throw prev;
    }
    verifyCachedIsolation(cs.getConnection());
    cs.close();
}
 
Example 5
public void testChangeIsoLevelProcedureJdbcCallableBatch() throws SQLException {
    CallableStatement cs = prepareCall("CALL SET_ISOLATION_JDBC(?)");
    for (int i = 0; i < isoLevels.length; ++i) {
        cs.setInt(1, isoLevels[i].getIsoLevel());
        cs.addBatch();
    }
    try {
        cs.executeBatch();
    } catch (SQLException e) {
        SQLException prev = e;
        while (e != null) {
            prev = e;
            e = e.getNextException();
        }
        throw prev;
    }
    verifyCachedIsolation(cs.getConnection());
    cs.close();
}
 
Example 6
@Test
public void testBatch() throws SQLException, MalformedURLException {
    CallableStatement preparedStatement = multiHostConnection.prepareCall("UPDATE test SET a = ? WHERE b = ?");
    preparedStatement.setShort(1, (short) 12);
    preparedStatement.setTime(2, new Time(System.currentTimeMillis()));
    preparedStatement.addBatch();
    int[] resultSet = preparedStatement.executeBatch();
    preparedStatement.clearBatch();

    verify(mysqlCallableStatement).executeBatch();
    verify(mysqlCallableStatement).addBatch();
    verify(mysqlCallableStatement).clearBatch();

    assertThat(segmentStorage.getTraceSegments().size(), is(1));
    TraceSegment traceSegment = segmentStorage.getTraceSegments().get(0);
    List<AbstractTracingSpan> spans = SegmentHelper.getSpans(traceSegment);
    assertThat(spans.size(), is(1));
    assertDBSpan(spans.get(0), "Mysql/JDBI/CallableStatement/executeBatch", "");
}
 
Example 7
Source Project: r-course   File: CallableStatementTest.java    License: MIT License 5 votes vote down vote up
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 8
/**
 * 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 9
private void execProcBug49831(Connection c) throws Exception {
    CallableStatement cstmt = c.prepareCall("{call pTestBug49831(?)}");
    cstmt.setObject(1, "abc", Types.VARCHAR, 32);
    cstmt.addBatch();
    cstmt.setObject(1, "def", Types.VARCHAR, 32);
    cstmt.addBatch();
    cstmt.executeBatch();
    assertEquals(2, getRowCount("testBug49831"));
    this.rs = this.stmt.executeQuery("SELECT * from testBug49831 ORDER BY VAL ASC");
    this.rs.next();
    assertEquals("abc", this.rs.getString(1));
    this.rs.next();
    assertEquals("def", this.rs.getString(1));
}
 
Example 10
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 11
private void execProcBug49831(Connection c) throws Exception {
    CallableStatement cstmt = c.prepareCall("{call pTestBug49831(?)}");
    cstmt.setObject(1, "abc", Types.VARCHAR, 32);
    cstmt.addBatch();
    cstmt.setObject(1, "def", Types.VARCHAR, 32);
    cstmt.addBatch();
    cstmt.executeBatch();
    assertEquals(2, getRowCount("testBug49831"));
    this.rs = this.stmt.executeQuery("SELECT * from testBug49831 ORDER BY VAL ASC");
    this.rs.next();
    assertEquals("abc", this.rs.getString(1));
    this.rs.next();
    assertEquals("def", this.rs.getString(1));
}
 
Example 12
Source Project: gemfirexd-oss   File: BatchUpdateTest.java    License: Apache License 2.0 5 votes vote down vote up
private static void executeBatchCallableStatement(CallableStatement cs)
throws SQLException
{
    int updateCount[];

    updateCount = cs.executeBatch();
    assertEquals("there were 2 statements in the batch",
        2, updateCount.length);
    for (int i=0; i<updateCount.length; i++)
    {
        assertEquals("update count should be 1", 1, updateCount[i]);
    }
}
 
Example 13
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 14
/**
 * 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 {
    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 15
private void execProcBug49831(Connection c) throws Exception {
    CallableStatement cstmt = c.prepareCall("{call pTestBug49831(?)}");
    cstmt.setObject(1, "abc", Types.VARCHAR, 32);
    cstmt.addBatch();
    cstmt.setObject(1, "def", Types.VARCHAR, 32);
    cstmt.addBatch();
    cstmt.executeBatch();
    assertEquals(2, getRowCount("testBug49831"));
    this.rs = this.stmt.executeQuery("SELECT * from testBug49831 ORDER BY VAL ASC");
    this.rs.next();
    assertEquals("abc", this.rs.getString(1));
    this.rs.next();
    assertEquals("def", this.rs.getString(1));
}
 
Example 16
Source Project: gemfirexd-oss   File: BatchUpdateTest.java    License: Apache License 2.0 5 votes vote down vote up
private static void executeBatchCallableStatement(CallableStatement cs)
throws SQLException
{
    int updateCount[];

    updateCount = cs.executeBatch();
    assertEquals("there were 2 statements in the batch",
        2, updateCount.length);
    for (int i=0; i<updateCount.length; i++)
    {
        assertEquals("update count should be 1", 1, updateCount[i]);
    }
}
 
Example 17
private static void executeBatchCallableStatement(CallableStatement cs)
throws SQLException
{
    int updateCount[];

    updateCount = cs.executeBatch();
    assertEquals("there were 2 statements in the batch", 
        2, updateCount.length);
    for (int i=0; i<updateCount.length; i++) 
    {
        assertEquals("update count should be 1", 1, updateCount[i]);
    }
}
 
Example 18
Source Project: gemfirexd-oss   File: CallableTest.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * Batches up calls to a SQL procedure that updates a value in a table.
 * Uses DriverManager and Batch calls, so requires JDBC 2 support.
 * @throws SQLException 
 */
public void xtestBatchUpdate() throws SQLException
{
    // Setup table data
    Statement stmt = createStatement();
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(1, 'STRING_1',10)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(2, 'STRING_2',0)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(3, 'STRING_3',0)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(4, 'STRING_4a',0)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(4, 'STRING_4b',0)");

    // Setup batch to modify value to 10 * the id (makes verification easy).
    CallableStatement cstmt = prepareCall("CALL BATCH_UPDATE_PROC(?,?)");
    cstmt.setInt(1,2);  // Id 2's value will be updated to 20.
    cstmt.setInt(2,20);
    cstmt.addBatch();
    cstmt.setInt(1,3);  // Id 3's value will be updated to 30.
    cstmt.setInt(2,30);
    cstmt.addBatch();
    cstmt.setInt(1,4);  // Two rows will be updated to 40 for id 4.
    cstmt.setInt(2,40);
    cstmt.addBatch();
    cstmt.setInt(1,5);  // No rows updated (no id 5).
    cstmt.setInt(2,50);
    cstmt.addBatch();

    int[] updateCount=null;
    try {
        updateCount = cstmt.executeBatch();
        assertEquals("updateCount length", 4, updateCount.length);

        for(int i=0; i< updateCount.length; i++){
            if (usingEmbedded()) {
                assertEquals("Batch updateCount", 0, updateCount[0]);
            }
            else if (usingDerbyNetClient()) {
                assertEquals("Batch updateCount", -1, updateCount[0]);
            }
        }
    } catch (BatchUpdateException b) {
        assertSQLState("Unexpected SQL State", b.getSQLState(), b);
    }

    // Retrieve the updated values and verify they are correct.
    ResultSet rs = stmt.executeQuery(
        "SELECT id, tag, idval FROM BATCH_TABLE order by id, tag");
    assertNotNull("SELECT from BATCH_TABLE", rs);

    while (rs.next())
    {
        assertEquals(rs.getString(2), rs.getInt(1)*10, rs.getInt(3));
    }
}
 
Example 19
Source Project: gemfirexd-oss   File: CallableTest.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * Batches up calls to a SQL procedure that updates a value in a table.
 * Uses DriverManager and Batch calls, so requires JDBC 2 support.
 * @throws SQLException 
 */
public void xtestBatchUpdate() throws SQLException
{
    // Setup table data
    Statement stmt = createStatement();
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(1, 'STRING_1',10)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(2, 'STRING_2',0)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(3, 'STRING_3',0)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(4, 'STRING_4a',0)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(4, 'STRING_4b',0)");

    // Setup batch to modify value to 10 * the id (makes verification easy).
    CallableStatement cstmt = prepareCall("CALL BATCH_UPDATE_PROC(?,?)");
    cstmt.setInt(1,2);  // Id 2's value will be updated to 20.
    cstmt.setInt(2,20);
    cstmt.addBatch();
    cstmt.setInt(1,3);  // Id 3's value will be updated to 30.
    cstmt.setInt(2,30);
    cstmt.addBatch();
    cstmt.setInt(1,4);  // Two rows will be updated to 40 for id 4.
    cstmt.setInt(2,40);
    cstmt.addBatch();
    cstmt.setInt(1,5);  // No rows updated (no id 5).
    cstmt.setInt(2,50);
    cstmt.addBatch();

    int[] updateCount=null;
    try {
        updateCount = cstmt.executeBatch();
        assertEquals("updateCount length", 4, updateCount.length);

        for(int i=0; i< updateCount.length; i++){
            if (usingEmbedded()) {
                assertEquals("Batch updateCount", 0, updateCount[0]);
            }
            else if (usingDerbyNetClient()) {
                assertEquals("Batch updateCount", -1, updateCount[0]);
            }
        }
    } catch (BatchUpdateException b) {
        assertSQLState("Unexpected SQL State", b.getSQLState(), b);
    }

    // Retrieve the updated values and verify they are correct.
    ResultSet rs = stmt.executeQuery(
        "SELECT id, tag, idval FROM BATCH_TABLE order by id, tag");
    assertNotNull("SELECT from BATCH_TABLE", rs);

    while (rs.next())
    {
        assertEquals(rs.getString(2), rs.getInt(1)*10, rs.getInt(3));
    }
}
 
Example 20
/**
 * Batches up calls to a SQL procedure that updates a value in a table.
 * Uses DriverManager and Batch calls, so requires JDBC 2 support.
 * @throws SQLException 
 */
public void xtestBatchUpdate() throws SQLException
{
    // Setup table data
    Statement stmt = createStatement();
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(1, 'STRING_1',10)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(2, 'STRING_2',0)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(3, 'STRING_3',0)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(4, 'STRING_4a',0)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(4, 'STRING_4b',0)");

    // Setup batch to modify value to 10 * the id (makes verification easy).
    CallableStatement cstmt = prepareCall("CALL BATCH_UPDATE_PROC(?,?)");
    cstmt.setInt(1,2);  // Id 2's value will be updated to 20.
    cstmt.setInt(2,20);
    cstmt.addBatch();
    cstmt.setInt(1,3);  // Id 3's value will be updated to 30.
    cstmt.setInt(2,30);
    cstmt.addBatch();
    cstmt.setInt(1,4);  // Two rows will be updated to 40 for id 4.
    cstmt.setInt(2,40);
    cstmt.addBatch();
    cstmt.setInt(1,5);  // No rows updated (no id 5).
    cstmt.setInt(2,50);
    cstmt.addBatch();

    int[] updateCount=null;
    try {
        updateCount = cstmt.executeBatch();
        assertEquals("updateCount length", 4, updateCount.length);

        for(int i=0; i< updateCount.length; i++){
            if (usingEmbedded()) {
                assertEquals("Batch updateCount", 0, updateCount[0]);
            }
            else if (usingDerbyNetClient()) {
                assertEquals("Batch updateCount", -1, updateCount[0]);
            }
        }
    } catch (BatchUpdateException b) {
        assertSQLState("Unexpected SQL State", b.getSQLState(), b);
    }

    // Retrieve the updated values and verify they are correct.
    ResultSet rs = stmt.executeQuery(
        "SELECT id, tag, idval FROM BATCH_TABLE order by id, tag");
    assertNotNull("SELECT from BATCH_TABLE", rs);

    while (rs.next())
    {
        assertEquals(rs.getString(2), rs.getInt(1)*10, rs.getInt(3));
    }
}