Insert/Update that doesn't

===========
The code:
==========

public boolean store( boolean addNew )
{
servlet.log("Category1(Store): save, addnew = " + (addNew
? "true" : "false") );
String sqlCommand = "";
if ( addNew ) {
sqlCommand = "INSERT INTO categories(ident, category,
description) VALUES(?,?,?)";
} else {
sqlCommand = "UPDATE categories SET " +
"category=?, " +
"description=? " +
" WHERE ident=?";
}
servlet.log("Category1(Store): Command=" + sqlCommand );
java.lang.Exception exception = null;
javax.sql.DataSource ds = servlet.findDataSource( null );
if ( ds != null ) {
servlet.log("Category1(Store): Connecting..." );

Connection conn = null;
PreparedStatement stmt = null;
try {
conn = ds.getConnection();
servlet.log("Category1(Store): Connected." );
stmt = conn.prepareStatement(sqlCommand);
if ( addNew ) {
stmt.setString(1,category.getId().trim());
stmt.setString(2,category.getName());
stmt.setString(3,category.getDescription());
} else {
stmt.setString(1,category.getName());
stmt.setString(2,category.getDescription());
stmt.setString(3,category.getId().trim());
}
servlet.log("Category1(Store): Store..." );
int upcount = stmt.executeUpdate();
servlet.log("Category1(Store): Done " + upcount );
SQLWarning w = stmt.getWarnings();
if ( w != null ) {
servlet.log("Category1(Store): warnings..." +
w.toString() );
}
} catch ( Exception ex ) {
exception = ex;
} finally {
if ( stmt != null ) {
try {
servlet.log("Category1(Store): Closing statement..." );
stmt.close();
servlet.log("Category1(Store): Closed" );
} catch ( Exception ex ) {
// Assume that the first exception caught is the
worst
if ( exception == null ) exception = ex;
}
}
if ( conn != null ) {
try {
servlet.log("Category1(Store): Closing connection..." );
conn.close();
servlet.log("Category1(Store): Closed..." );
} catch ( Exception ex ) {
// Assume that the first exception caught is the
worst
if ( exception == null ) exception = ex;
}
}
} // end finally
if ( exception != null ) {
servlet.log("CATEGORYACTION: Exception - " +
exception.getMessage() );
return false;
}
}
return true;
}

==========
The Trace:
==========

2001-12-06 20:12:15 action: SAVECATEGORY: entering perform()
2001-12-06 20:12:15 action: Save category - ID=00001
2001-12-06 20:12:15 action: Save category - Name=foo123
2001-12-06 20:12:15 action: Category1(Store): save, addnew = false
2001-12-06 20:12:15 action: Category1(Store): Command=UPDATE categories
SET category= ?, description= ? WHERE ident= ?2001-12-06 20:12:15
action: Category1(Store): Connecting...
2001-12-06 20:12:15 action: Category1(Store): Connected.
2001-12-06 20:12:15 action: Category1(Store): Store...
2001-12-06 20:12:15 action: Category1(Store): Done 1
2001-12-06 20:12:15 action: Category1(Store): Closing statement...
2001-12-06 20:12:15 action: Category1(Store): Closed
2001-12-06 20:12:15 action: Category1(Store): Closing connection...
2001-12-06 20:12:15 action: Category1(Store): Closed...

===============
The Server Log:
===============

011206.20:10:27.632 [2694] ProcessQuery
011206.20:10:27.637 [2694] CommitTransactionCommand
011206.20:12:15.390 [2458] StartTransactionCommand
011206.20:12:15.391 [2458] query: UPDATE categories SET category=
'foo123', description= 'Paper documents' WHERE ident= '00001'
011206.20:12:15.392 [2458] ProcessQuery
011206.20:12:15.400 [2458] CommitTransactionCommand
011206.20:12:15.405 [2458] StartTransactionCommand
011206.20:12:15.405 [2458] query: rollback
011206.20:12:15.405 [2458] ProcessUtility: rollback
011206.20:12:15.406 [2458] CommitTransactionCommand
011206.20:12:15.408 [2458] StartTransactionCommand
011206.20:12:15.408 [2458] query: begin
011206.20:12:15.408 [2458] ProcessUtility: begin
011206.20:12:15.408 [2458] CommitTransactionCommand
011206.20:12:15.410 [2458] StartTransactionCommand
011206.20:12:15.410 [2458] query: SET TRANSACTION ISOLATION LEVEL READ
COMMITTED
011206.20:12:15.410 [2458] ProcessUtility: SET TRANSACTION ISOLATION
LEVEL READ COMMITTED
011206.20:12:15.410 [2458] CommitTransactionCommand

HOWEVER:
--------

011206.20:13:23.535 [2694] StartTransactionCommand
011206.20:13:23.535 [2694] query: select * from categories;

011206.20:13:23.537 [2694] ProcessQuery
011206.20:13:23.538 [2694] CommitTransactionCommand
011206.20:13:54.033 [2694] StartTransactionCommand
011206.20:13:54.033 [2694] query: update categories set category='paper'
where ident='00001';
011206.20:13:54.037 [2694] ProcessQuery
011206.20:13:54.038 [2694] CommitTransactionCommand
011206.20:30:47.148 [2694] proc_exit(0)
011206.20:30:47.148 [2694] shmem_exit(0)
011206.20:30:47.148 [2694] exit(0)

==========
SYNOPSIS:
==========

First I ran an update against categories from a JDBC client in Tomcat. The
Tomcat trace indicates that the update succeeded. It lies. Nothing gets
changed.

Running an equivalent command from psql works fine.

One thing I notice is that the JDBC request is a lot more complex in terms
of what the backend does for it, and part of that complexity looks like a
transaction rollback undoing the update.

Can anyone explain what's going on here? It's really frustrating when you
do everything "right", get (apparently) no errors, and yet it doesn't
work.

The exact same thing happens with non-parameterized JDBC SQL, BTW.

Thanks,

Tim Holloway