MySQL Bugs: #6823: continueBatchOnError

Bug #6823	continueBatchOnError
Submitted:	25 Nov 2004 8:14	Modified:	30 Nov 2004 20:54
Reporter:	Rohithaksha Kalluraya	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / J	Severity:	S4 (Feature request)
Version:	Any	OS:	Any (All)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Can "continueBatchOnError" feature be made available for Statement.executeBatch() ? Currently it is available for PreparedStatement.

How to repeat:
Use Statement.addBatch(String sql) to add requests into the batch. Make one of them somewhere in the middle of the batch to fail (Ex: "Duplicate entry '1' for key 1"). This throws SQLException and rejects all the remaining requests.
If we use PreparedStatement it continues with the next request if any request fails.

I have put together a test which seems to demonstrate that it is working for both statements and prepared statements. I'll paste in what I've done so far.

// -------------------------
    public void testContinueBatchOnError() throws SQLException {

        Properties continueBatchOnErrorProps = new Properties();
        continueBatchOnErrorProps.put("continueBatchOnError", "true");

        this.conn = DriverManager.getConnection(dbUrl,
                continueBatchOnErrorProps);

        this.stmt = this.conn.createStatement();

        this.stmt.executeUpdate("DROP TABLE IF EXISTS statement_batch_test");

        this.stmt.executeUpdate("CREATE TABLE statement_batch_test "
                + "(id int not null primary key auto_increment, "
                + "strdata1 varchar(255) not null, strdata2 varchar(255), "
                + "UNIQUE INDEX (strdata1))");

        this.pstmt = this.conn.prepareStatement("INSERT INTO "
                + "statement_batch_test (strdata1, strdata2) VALUES (?,?)");

        int c = 0;
        addBatchItems(++c);
        addBatchItems(++c);
        addBatchItems(++c);
        addBatchItems(c); // duplicate entry
        addBatchItems(++c);
        addBatchItems(++c);

        SQLException e1 = null;
        SQLException e2 = null;

        try {
            this.pstmt.executeBatch();
        } catch (SQLException e) {
            e1 = e;
        }
        assertNotNull(e1);
        try {
            this.stmt.executeBatch();
        } catch (SQLException e) {
            e2 = e;
        }
        assertNotNull(e2);

        int psRows = 0;
        this.rs = stmt.executeQuery("SELECT * from statement_batch_test"
                + " WHERE strdata1 like \"ps_%\"");
        while (rs.next()) {
            psRows++;
        }
        assertTrue(psRows > 0);

        int sRows = 0;
        this.rs = stmt.executeQuery("SELECT * from statement_batch_test"
                + " WHERE strdata1 like \"s_%\"");
        while (rs.next()) {
            sRows++;
        }
        assertTrue(sRows > 0);

        assertEquals(psRows + "!=" + sRows, psRows, sRows);
    }

    private void addBatchItems(int i) throws SQLException {
        this.pstmt.setString(1, "ps_batch_" + i);
        this.pstmt.setString(2, "ps_batch_" + i);
        this.pstmt.addBatch();

        this.stmt.addBatch("INSERT INTO "
                + "statement_batch_test (strdata1, strdata2) VALUES "
                + "(\"s_batch_" + i + "\",\"s_batch_" + i + "\")");
    }

// -------------------------

I'd like you to try to modify this test so it will run in your environment and report what you find. Also, it would be useful to know what version of Connector/J you are using and what version of MySQL you are connecting to.

Cheers,
-- Eric Herman

Actually I tried something and it was not working, so I felt this feature is not available. Please check the Bug #6822.

Let me know if you need any more information.

Thanks a lot,
Rohit

A junit testcase comparing executeBatch() for Statements and PreparedStatements

Attachment: TestBug6823.java (text/x-java), 7.13 KiB.

I have tested the behavior of executeBatch for both Statement and PreparedStatement. Their behavior is consistant in the cases of continueBatchOnError of "true" and "false".
My testing included both 3.0 and 3.1 versions of Connector/J connecting to MySQL version 4.1.7.

I notice that bug #6822 is closed as "Not a bug" .... perhaps you could be more specific.

Please look in the files section, download and run the provided test, and post the results.
You will need to have JUnit and Connector/J in your classpath.
Be sure to include the versions of Connector/J and MySQL.

Cheers,
 -- Eric

This is obviously a duplicate of BUG#6822, so I'm marking it as such. Please don't open new issues for previous bugs.

I have the same problem, please help