Re: JDBC and commit problems

Ok, I set up a test function, very similar to what you suggested, and I
stand corrected on the issue of whether or not transactions are the
issue.  There are, however some issues that either I'm misinformed
about, or that need to be cleared up, because I'm still having a problem
and it frankly, doesn't make any sense to me.  SOMEWHERE there should be
an error reported that is not.

Please allow me to expound:

Code Sample 1: The test() method.

    try {
        Connection con=getConnection();
        System.out.println("BEGIN TEST");

        con.setAutoCommit(false);
        con.createStatement().execute("INSERT INTO test (col2) VALUES
('First Test')");
        con.createStatement().execute("INSERT INTO test (col2) VALUES
('Second Test')");
        con.commit();
        con.createStatement().execute("INSERT INTO test (col2) VALUES
('Third Test')");
        con.createStatement().execute("INSERT INTO test (col2) VALUES
('Fourth Test')");
        con.commit();
        con.setAutoCommit(true);
        con.createStatement().execute("INSERT INTO test (col2) VALUES
('Fifth Test')");
        con.createStatement().execute("INSERT INTO test (col2) VALUES
('Sixth Test')");

        PreparedStatement foo=con.prepareStatement("INSERT INTO TEST (col2)
VALUES (?)");
        con.setAutoCommit(true);
        foo.setObject(1,"PrepareTest 1");
        foo.addBatch();
        foo.setObject(1, "PrepareTest 2");
        foo.addBatch();
        foo.executeBatch();
         foo.clearBatch();
        foo=con.prepareStatement("INSERT INTO TEST (col2) VALUES (?)");
        con.setAutoCommit(false);
        foo.setObject(1,"PrepareTest 3");
        foo.addBatch();
        foo.setObject(1, "PrepareTest 4");
        foo.addBatch();
        foo.executeBatch();
        con.commit();
        foo.clearBatch();

        System.out.println("END TEST");
    }
        catch(SQLException e) {
        e.printStackTrace();
    }
    }

This code, as written works fine, but subtle changes make it break in
the PreparedStatement Section. Most notable, is if the second
PreparedStatement is commented out the test method fails by throwing an
SQLException error for Parameter out of range on the "PrepareTest 3"
line.  This occurrs regardless of the clearBatch() line's presence, so
I'm assuming that executeBatch is removing the core statement which
there is nothing in the JDK docs indicating that this is what should be
happening (I haven't looked at the source yet).  This would appear to
effectively make reusing a PreparedStatement, impossible.



Second Issue:

Code Sample 2:
    try {
        test();
        System.out.println("Test 1 complete ");
        results=statement.executeBatch();
        System.out.println("executeBatch() complete");
        test();
        System.out.println("Test 2 complete");
    }

The test() method is the one supplied in Code Sample 1.
The SQL for the PreparedStatement 'statement' is:
    INSERT INTO projects ( clientid, questionaireresults, summary,
        lead_auditorid, name) VALUES ( ?, ?, ?, ?, ?);

One statement has been added to the batch filling in three of the
values, and utilizing PreparedStatements setNull() for the remaining
columns.

Based upon the output, all statements are successfully completed,
however, no entries are inserted into the projects table.  Also, no
error or exception is thrown.  This is a problem.  Even if my statement
is incorrect, an exception should be thrown reflecting the error to
notify me of this.

Thanks for the help so far, and I hope that we can continue to resolve
this issue.

Regards,

Robert
On Fri, 2002-10-04 at 01:50, Thomas O'Dowd wrote:
> Have you tried...
>
[snip]
> > > --
> > > Thomas O'Dowd. - Nooping - http://nooper.com
> > > tom@nooper.com - Testing - http://nooper.co.jp/labs
> > >
> >
> >
> --
> Thomas O'Dowd. - Nooping - http://nooper.com
> tom@nooper.com - Testing - http://nooper.co.jp/labs
>