Thread: Cannot commit when autoCommit is enabled error

Gurkan Ozfidan wrote:
> We have been using postgresql-jdbc-8.3-604, and recently we started using postgresql-jdbc-9.2,
> everything seems working fine, except we are getting this error and could not find the solution yet.
> This related to our reporting and I could say that it is not happening every report we run. If you
> could help, I really appreciated.
> 
> Below is the part of the code and error that we are getting. Thanks.
> 
> 
> ERROR:
> ERROR in createReportTable(): Cannot commit when autoCommit is enabled.
> org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled.
>         at org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:703)
>         at sun.reflect.GeneratedMethodAccessor65.invoke(Unknown Source)
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>         at java.lang.reflect.Method.invoke(Method.java:597)
>         at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
>         at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
>         at
> org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:80)
>         at $Proxy0.commit(Unknown Source)
>         at sun.reflect.GeneratedMethodAccessor65.invoke(Unknown Source)
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>         at java.lang.reflect.Method.invoke(Method.java:597)
>         at org.hibernate.jdbc.BorrowedConnectionProxy.invoke(BorrowedConnectionProxy.java:40)
>         at $Proxy125.commit(Unknown Source)
>         at
> com.resolution.scheduler.dao.hibernate.SalesPAFNewDaoHibernate.runStorageSQLQuery(SalesPAFNewDaoHibern
> ate.java:219)
>         at
> com.resolution.scheduler.service.impl.SalesPAFNewManagerImpl.runStorageSQLQuery(SalesPAFNewManagerImpl
> .java:151)
> 
> 
> 
> PART OF THE CODE:
> 
> 
> public Integer runStorageSQLQuery(String sqlQuery) throws Exception {
>         int results=0;
>         try {
>             java.sql.Connection conn = getSession().connection();
>             boolean acs = conn.getAutoCommit();
>             //System.out.println("...................AutoCommit status: "+acs);
>             conn.setAutoCommit(false);
>             java.sql.PreparedStatement st = conn.prepareStatement(sqlQuery);
>             results = st.executeUpdate();
>             conn.commit();
>             conn.setAutoCommit(acs);
>             st.close();
>         } catch (Exception e) {
>             System.err.println("ERROR in runStorageSQLQuery(): " + e.getMessage() + " sqlQuery:
> "+sqlQuery);
>             e.printStackTrace();
>         }
>         return new Integer(results);
>     }

If that used to work with the old driver, it might be a bug in JDBC.

Try asking on the pgsql-jdbc mailing list.

Yours,
Laurenz Albe

Gurkan Ozfidan <gurkan@timeforgedev.com> wrote:

>ERROR in createReportTable(): Cannot commit when autoCommit is enabled.

>PART OF THE CODE:

>        } catch (Exception e) {
>            System.err.println("ERROR in runStorageSQLQuery(): " + e.getMessage() + " sqlQuery: "+sqlQuery);
>            e.printStackTrace();

It may be part of the code, but it's not the part that is throwing
the exception.

FWIW, the JDBC API requires that the driver throw an exception if
you try to invoke Connection.commit() on a connection which is in
autoCommit mode.  Older versions of the PostgreSQL JDBC driver
failed to do that; newer ones comply with that requirement.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company