Thread: JDBC and commit problems
I'm trying to roll a JDBC intensive application utilizing PostgreSQL and I came across a very difficult (and annoying) problem. The long and the short of it comes down to it appears that doing manual transactions do not appear to work presently under postgres. Switching the application to use autoCommit() cleared up the problem. I'm utilizing 7.2.2 for the database and grabbed devpgjdbc2.jar a couple days ago to test against, though I had similar issues with other versions of the jdbc driver. I've searched quite a bit and can't find any mention of others having this problem. Does everyone normally autocommit? Regardless, I have reasons where wanting full control over my transactions would be highly desirable and would like to either know what it is I'm doing wrong, or if this is indeed a flaw in the jdbc driver. Regards, Robert M. Zigweid
Can you post a small example of code showing what doesn't work for you and any errors? I use transactions all the time and turn autocommit on and off according to what I need as I'm sure do most others on the list. The only thing off the top of my head that I can think of is that if one of your statements in a transaction gives an error, postgresql requires you to rollback before it will accept other statements. Tom. On Fri, 2002-10-04 at 13:31, Robert M. Zigweid wrote: > I'm trying to roll a JDBC intensive application utilizing PostgreSQL and > I came across a very difficult (and annoying) problem. > > The long and the short of it comes down to it appears that doing manual > transactions do not appear to work presently under postgres. Switching > the application to use autoCommit() cleared up the problem. > > I'm utilizing 7.2.2 for the database and grabbed devpgjdbc2.jar a couple > days ago to test against, though I had similar issues with other > versions of the jdbc driver. > > I've searched quite a bit and can't find any mention of others having > this problem. Does everyone normally autocommit? Regardless, I have > reasons where wanting full control over my transactions would be highly > desirable and would like to either know what it is I'm doing wrong, or > if this is indeed a flaw in the jdbc driver. > > > Regards, > > > Robert M. Zigweid > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Thomas O'Dowd. - Nooping - http://nooper.com tom@nooper.com - Testing - http://nooper.co.jp/labs
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
>
Hmm, haven't used the executeBatch() functionality with PG. I'll let someone else on the list comment on this. Haven't the time to test it right now. Tom. On Sat, 2002-10-05 at 00:50, Robert M. Zigweid wrote: > 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. -- Thomas O'Dowd. - Nooping - http://nooper.com tom@nooper.com - Testing - http://nooper.co.jp/labs