SEVERE : Transactions not supported

gmartinezgil · **Joined:** Thu Aug 28, 2003 11:29 am **Posts:** 1

Hi

I'm using Hibernate 2.0.2 with the latest IBM Informix JDBC driver (2.21.JC5) and an IBM Informix Dynamic server (9.30.FC1) with a single table of employees for testing pourposes, the mapping of the Employee object (Employee.hbm.xml) and the hibernate.properties are correct because I can connect well to the database, even with a simple JDBC client, but I get the next message from the Log:

Code:

SEVERE: Transactions not supported

WARNING: Could not obtain connection metadata
java.sql.SQLException: Transactions not supported
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:355)
at com.informix.jdbc.IfxSqliConnect.setAutoCommit(IfxSqliConnect.java:1714)
at net.sf.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:95)
at net.sf.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:167)
at net.sf.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:627)
at Test.main(Test.java:34)

What could be, the driver that cannot obtain the metada information???, some misconfiguration with Hibernate or Informix...?

Thanks in advance.

richard · **Joined:** Tue Sep 23, 2003 5:19 pm **Posts:** 11

Hi

Did you find a solution to this problem?

I am getting the same message when I try and connection to Informix SE.

Richard

ezamudio · **Joined:** Fri Apr 02, 2004 9:20 pm **Posts:** 1

I'm getting the exact same exception with Hibernate 2.1 and Informix 9.3, using the JDBC driver supplied with the server (2.21 JC5).

If I connect the same app (a very simple helloworld-type app) to MySQL everything runs fine.

I already found some suggestions about turning on logging in the database, but I have done so and it didn't help.

Does anyone have a solution for this? We really want to use hibernate for a very big project but if I can't connect to Informix then we'll have to use something else... I'm willing to use a different driver but I can't find any on the net.

The exception I get is this:

Apr 2, 2004 7:11:53 PM net.sf.hibernate.cfg.SettingsFactory buildSettings
WARNING: Could not obtain connection metadata
java.sql.SQLException: Transactions not supported
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:355)
at com.informix.jdbc.IfxSqliConnect.setAutoCommit(IfxSqliConnect.java:1714)
at net.sf.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:97)
at net.sf.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java:72)
at net.sf.hibernate.cfg.Configuration.buildSettings(Configuration.java:1119)
at net.sf.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:748)
at Prueba.setupHibernate(Unknown Source)
at Prueba.main(Unknown Source)
Apr 2, 2004 7:11:53 PM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: Use scrollable result sets: false
Apr 2, 2004 7:11:53 PM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: Use JDBC3 getGeneratedKeys(): false
Apr 2, 2004 7:11:53 PM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: Optimize cache for minimal puts: false
Apr 2, 2004 7:11:53 PM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: Query language substitutions: {}
Apr 2, 2004 7:11:53 PM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: cache provider: net.sf.ehcache.hibernate.Provider
Apr 2, 2004 7:11:53 PM net.sf.hibernate.cfg.Configuration configureCaches
INFO: instantiating and configuring caches
Apr 2, 2004 7:11:53 PM net.sf.hibernate.impl.SessionFactoryImpl <init>
INFO: building session factory
Apr 2, 2004 7:11:55 PM net.sf.hibernate.impl.SessionFactoryObjectFactory addInstance
INFO: no JNDI name configured
***** Creando proyectos e insertando...
Apr 2, 2004 7:12:00 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: -79744, SQLState: IX000
Apr 2, 2004 7:12:00 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: Transactions not supported
Apr 2, 2004 7:12:00 PM net.sf.hibernate.JDBCException <init>
SEVERE: Cannot open connection
java.sql.SQLException: Transactions not supported
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:355)
at com.informix.jdbc.IfxSqliConnect.setAutoCommit(IfxSqliConnect.java:1714)
at net.sf.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:97)
at net.sf.hibernate.impl.BatcherImpl.openConnection(BatcherImpl.java:278)
at net.sf.hibernate.impl.SessionImpl.connect(SessionImpl.java:3264)
at net.sf.hibernate.impl.SessionImpl.connection(SessionImpl.java:3244)
at net.sf.hibernate.impl.BatcherImpl.prepareStatement(BatcherImpl.java:61)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:524)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:432)
at net.sf.hibernate.impl.ScheduledIdentityInsertion.execute(ScheduledIdentityInsertion.java:29)
at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:906)
at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:839)
at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:757)
at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:720)
at net.sf.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:1359)
at Prueba.createProjects(Unknown Source)
at Prueba.main(Unknown Source)
Cannot open connection
net.sf.hibernate.JDBCException: Cannot open connection
at net.sf.hibernate.impl.BatcherImpl.openConnection(BatcherImpl.java:281)
at net.sf.hibernate.impl.SessionImpl.connect(SessionImpl.java:3264)
at net.sf.hibernate.impl.SessionImpl.connection(SessionImpl.java:3244)
at net.sf.hibernate.impl.BatcherImpl.prepareStatement(BatcherImpl.java:61)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:524)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:432)
at net.sf.hibernate.impl.ScheduledIdentityInsertion.execute(ScheduledIdentityInsertion.java:29)
at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:906)
at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:839)
at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:757)
at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:720)
at net.sf.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:1359)
at Prueba.createProjects(Unknown Source)
at Prueba.main(Unknown Source)
Caused by: java.sql.SQLException: Transactions not supported
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:355)
at com.informix.jdbc.IfxSqliConnect.setAutoCommit(IfxSqliConnect.java:1714)
at net.sf.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:97)
at net.sf.hibernate.impl.BatcherImpl.openConnection(BatcherImpl.java:278)
... 13 more

smccrory · **Posted:** Sat Apr 24, 2004 9:29 pm

I'm very, very interested in this as well since I'm looking to use Hibernate with Informix 9.x on a major application rewrite next month. If it's not answered in 30 days I'll probably hit upon the details myself - I'll be sure to post my results.
Scott

ChipOC · **Joined:** Tue Oct 19, 2004 6:33 pm **Posts:** 3

Has anyone found an answer to this?

From my research it appears that Hibernate automatically turns off auto commit mode when you ask for a connection. This causes a problem when the logging in informix is turned off it as the log is how transactions are implemented. So if you have logging turned off, informix HAS to use auto commit mode. Can anyone else with more experience confirm this?

As I am not allowed to turn on logging, is there a way to utilize Hibernate without transactions if the above is true?

smccrory · **Posted:** Mon Dec 27, 2004 10:34 pm

Sorry it took so long to respond (my project got delayed). I ran into the exact same problem today against a legacy Informix 9 database I can't turn logging on. I was thinking that maybe setting "hibernate.connection.isolation" to java.sql.Connection.TRANSACTION_NONE might work (I'll see in the morning) but I'm very interested in hearing if anyone else has solved this.

Thanks in advance,
Scott

emmanuel · **Posted:** Tue Dec 28, 2004 4:57 am

Huml, I did have this problem before.
You can either:
- pass a UserConnection to hibernate with autocommit to true
- implement your own connection provider letting autocommit to true.

smccrory · **Posted:** Tue Dec 28, 2004 9:52 am

Setting the transaction isolation level didn't help. Wish there was another way, but thanks - I'll look into writing a custom connection provider.
Scott

smccrory · **Posted:** Fri Dec 31, 2004 12:56 pm

Thanks a ton for the suggestions Emmanuel, it works!

Like you said, I extended C3P0ConnectionProvider and simply overrode getConnection():

Code:

   /**
    * @see net.sf.hibernate.connection.ConnectionProvider#getConnection()
    */
   public Connection getConnection() throws SQLException {

      final Connection c = ds.getConnection();

      if (isolation != null) {
         c.setTransactionIsolation(isolation.intValue());
      }

      // Here's the only code change we've made to
      // net.sf.hibernate.connection.C3P0ConnectionProvider
      c.setAutoCommit(true);
      //      if (c.getAutoCommit()) {
      //         c.setAutoCommit(false);
      //      }

      return c;

   }

Of course you have to point Hibernate at this new class as its connection provider. In my case it's a Spring bean property...

Code:

<prop key="hibernate.connection.provider_class">passport.dao.rdb.hibernate.AutoCommitC3P0ConnectionProvider</prop>

After the change, Hibernates successfully returns rows from my non-transactional Informix database without complaining.

Now I'm off to swing a rubber chicken at the DBAs who say they can't turn on that DB's transactions...

Scott

jiseller · **Joined:** Wed Nov 24, 2004 10:54 am **Posts:** 48

Sorry for the questions, I may just be overlooking something, but, did you change ds and isolation to be public?

When I tried your solution, ds and isolation came up as undefined becasue they are private in the super class.

I'm I missing something?

Thanks

smccrory · **Posted:** Fri Dec 31, 2004 3:05 pm

Ah, I did misrepresent it didn't I. I ran into the same problem that you did, so I copied C3P0ConnectionProvider's source code and modified getConnection() as shown below. Sorry for not being correct - I guess I forgot why I replaced the class instead of extending it and figured that others would prefer to subclass. Here's the complete file - note that I also deleted the deprecated validateConnection and setTestConnectionOnCheckout lines in configure() to prevent my team from using them:

Code:

package passport.dao.rdb.hibernate;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import net.sf.hibernate.HibernateException;
import net.sf.hibernate.cfg.Environment;
import net.sf.hibernate.connection.C3P0ConnectionProvider;
import net.sf.hibernate.connection.ConnectionProvider;
import net.sf.hibernate.connection.ConnectionProviderFactory;
import net.sf.hibernate.util.PropertiesHelper;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.mchange.v2.c3p0.DataSources;
import com.mchange.v2.c3p0.PoolConfig;

/**
 * Sets the connection auto-commit mode to true for non-logging databases.
 */
public class AutoCommitC3P0ConnectionProvider implements ConnectionProvider {

   /**
   * The datasource.
   */
   private DataSource ds;

   /**
   * The isolation level.
   */
   private Integer isolation;

   /**
   * The logging object for recording application activity.
   */
   private static final Log log = LogFactory.getLog(C3P0ConnectionProvider.class);

   /**
    * @see net.sf.hibernate.connection.ConnectionProvider#getConnection()
    */
   public Connection getConnection() throws SQLException {

      final Connection c = ds.getConnection();

      if (isolation != null) {
         c.setTransactionIsolation(isolation.intValue());
      }

      // Here's the only code change we've made to
      // net.sf.hibernate.connection.C3P0ConnectionProvider
      c.setAutoCommit(true);
      //      if (c.getAutoCommit()) {
      //         c.setAutoCommit(false);
      //      }

      return c;

   }

   /**
    * @see net.sf.hibernate.connection.ConnectionProvider#closeConnection(java.sql.Connection)
    */
   public void closeConnection(Connection conn) throws SQLException {
      conn.close();
   }

   /**
    * @see net.sf.hibernate.connection.ConnectionProvider#configure(java.util.Properties)
    */
   public void configure(Properties props) throws HibernateException {

      String jdbcDriverClass = props.getProperty(Environment.DRIVER);
      String jdbcUrl = props.getProperty(Environment.URL);
      Properties connectionProps = ConnectionProviderFactory.getConnectionProperties(props);

      log.info("C3P0 using driver: " + jdbcDriverClass + " at URL: " + jdbcUrl);
      log.info("Connection properties: " + connectionProps);

      if (jdbcDriverClass == null) {
         log.warn("No JDBC Driver class was specified by property " + Environment.DRIVER);
      } else {
         try {
            Class.forName(jdbcDriverClass);
         } catch (ClassNotFoundException cnfe) {
            String msg = "JDBC Driver class not found: " + jdbcDriverClass;
            log.fatal(msg);
            throw new HibernateException(msg);
         }
      }

      try {

         int minPoolSize = PropertiesHelper.getInt(Environment.C3P0_MIN_SIZE, props, 1);
         int maxPoolSize = PropertiesHelper.getInt(Environment.C3P0_MAX_SIZE, props, 100);
         int maxIdleTime = PropertiesHelper.getInt(Environment.C3P0_TIMEOUT, props, 0);
         int maxStatements = PropertiesHelper.getInt(Environment.C3P0_MAX_STATEMENTS, props, 0);
         int acquireIncrement = PropertiesHelper.getInt(Environment.C3P0_ACQUIRE_INCREMENT, props, 1);
         int idleTestPeriod = PropertiesHelper.getInt(Environment.C3P0_IDLE_TEST_PERIOD, props, 0);

         // C3P0_VALIDATE_CONNECTION is expensive and deprecated - use idleTestPeriod instead
         //boolean validateConnection = PropertiesHelper.getBoolean(Environment.C3P0_VALIDATE_CONNECTION, props);

         PoolConfig pcfg = new PoolConfig();
         pcfg.setInitialPoolSize(minPoolSize);
         pcfg.setMinPoolSize(minPoolSize);
         pcfg.setMaxPoolSize(maxPoolSize);
         pcfg.setAcquireIncrement(acquireIncrement);
         pcfg.setMaxIdleTime(maxIdleTime);
         pcfg.setMaxStatements(maxStatements);

         // C3P0_VALIDATE_CONNECTION is expensive and deprecated - use idleTestPeriod instead
         //pcfg.setTestConnectionOnCheckout(validateConnection);

         pcfg.setIdleConnectionTestPeriod(idleTestPeriod);

         /*DataSource unpooled = DataSources.unpooledDataSource(
            jdbcUrl, props.getProperty(Environment.USER), props.getProperty(Environment.PASS)
         );*/
         DataSource unpooled = DataSources.unpooledDataSource(jdbcUrl, connectionProps);
         ds = DataSources.pooledDataSource(unpooled, pcfg);

      } catch (Exception e) {
         log.fatal("could not instantiate C3P0 connection pool", e);
         throw new HibernateException("Could not instantiate C3P0 connection pool", e);
      }

      String i = props.getProperty(Environment.ISOLATION);
      if (i == null) {
         isolation = null;
      } else {
         isolation = new Integer(i);
         log.info("JDBC isolation level: " + Environment.isolationLevelToString(isolation.intValue()));
      }

   }

   /**
    * @see net.sf.hibernate.connection.ConnectionProvider#close()
    */
   public void close() {
      try {
         DataSources.destroy(ds);
      } catch (SQLException sqle) {
         log.warn("could not destroy C3P0 connection pool", sqle);
      }
   }

}

Scott

jhoeller · **Posted:** Mon Jan 03, 2005 12:14 pm

Scott, everybody,

FYI, instead of writing your own Hibernate ConnectionProvider, you could use a Spring-configured DataSource instance, passed to LocalSessionFactoryBean's "dataSource" property. In the case of C3P0, use C3P0's ComboPooledDataSource:

Code:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
  <property name="driverClass">
    <value>org.hsqldb.jdbcDriver</value></property>
  <property name="jdbcUrl">
    <value>jdbc:hsqldb:hsql://localhost:9001</value>
  </property>
  <property name="user">
    <value>sa</value>
  </property>
  <property name="password">
    <value></value>
  </property>
</bean>

<bean id="sessionFactory" class="org.springframework.orm.hibernate.LocalSessionFactoryBean">
  <property name="dataSource">
    <ref local="dataSource"/>
  </property>
  <property name="mappingResources">
    <value>petclinic.hbm.xml</value>
  </property>
  <property name="hibernateProperties">
    <props>
      <prop key="hibernate.dialect">...</prop>
      <prop key="hibernate.show_sql">true</prop>
    </props>
  </property>
</bean>

This is actually the typical way of setting up a connection pool reference in a Spring environment: either with a local DataSource instance (like a C3P0 ComboPooledDataSource or a Commons DBCP BasicDataSource) or with a JNDI reference (via Spring's JndiObjectFactoryBean). Of course, a Hibernate-specified ConnectionProvider will work in a Spring environment too.

In the case of a DataSource passed to LocalSessionFactoryBean, Hibernate will automatically fetch Connections from the given DataSource. Note that LocalSessionFactoryBean does not change any settings on that Connection; so it doesn't try to modify the auto-commit flag in the first place. For custom Connection settings, I usually recommend to configure your DataSource accordingly: All connection pools out there support default auto-commit flags, default isolation levels, etc.

I don't understand why Hibernate's default ConnectionProvider implementations modify the auto-commit flag outside of transactions. Couldn't the auto-commit flag just be left as-is, to be switched off once a transaction starts and reset to the original value after transaction completion? If someone wants auto-commit off by default, why not configure the connection pool accordingly? This is really not the job of the ConnectionProvider...

Juergen

smccrory · **Posted:** Mon Jan 03, 2005 2:21 pm

Works perfectly - thanks Juergen! This is a better solution for Spring-enabled Hibernate configs because it 1) eliminates the need to customize one's own connection provider, 2) makes the definition of a datasource more atomic and 3) makes it reusable in low-level JDBC DAOs (as is often prefered for batch updates). Let me know if anyone wants a clip of my working Spring/Hibernate config files.

Scott

levw · **Joined:** Mon Mar 28, 2005 7:51 am **Posts:** 1

But if you don't use Spring... How can we fix this problem in stand-alone application?

smccrory · **Posted:** Mon Mar 28, 2005 11:37 am

levw wrote:

But if you don't use Spring... How can we fix this problem in stand-alone application?

My non-Spring answer is above Juergen's Spring optimization.