Insert/Update that doesn't
От | Tim Holloway |
---|---|
Тема | Insert/Update that doesn't |
Дата | |
Msg-id | Pine.LNX.4.21.0112062026150.15530-100000@sklave.mousetech.com обсуждение исходный текст |
Ответы |
Re: Insert/Update that doesn't
("Dave Cramer" <Dave@micro-automation.net>)
|
Список | pgsql-jdbc |
=========== 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
В списке pgsql-jdbc по дате отправления: