Re: Insert/Update that doesn't - Mailing list pgsql-jdbc

Tim,

I don't see any commit happening.  If you want the changes to be final,
you need to commit them.  The difference between psql and jdbc can be
explained by the fact that by default psql runs in autocommit mode (each
statement is automatically commited when executed), and it appears that
your jdbc is running in non-autocommit mode (see setAutoCommit()).

thanks,
--Barry


Tim Holloway wrote:

> ===========
> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>