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

A call to commit() did the trick OK, but I'm still puzzled. Per the JDBC
javadocs: " This method should be used only when auto-commit mode has been
disabled".

There's an implication there that the default is for auto-commit to have
been ENabled, and I didn't explicitly enable OR disable auto-commit. Does
the builtin Jakarta Struts datasource pooling facility turn off
auto-commit?

   Thanks!

     Tim Holloway

 On Mon, 10 Dec 2001, Barry Lind wrote:

> 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
> >
> >
>
>