Re: Insert/Update that doesn't

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