Hell everyone. I wanted to try out C3P0 for pooling vs using tomcats JNDI named pool and a simple test app that creates 2 objects in the db is failing with a null exception on the second object. This works fine using just a connection.datasource= in my hibernate config but switch it to c3po (config below) and it fails every time. I have no idea why at this point and I'm hoping someone can help.

Thanks
Sean

Hibernate version:
3.0rc1
Mapping documents:
hibernate.cfg.xml
<hibernate-configuration>
<session-factory>

<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<!-- settings for C3PO -->
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost/testdb</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password">password</property>
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">20</property>
<property name="hibernate.c3p0.timeout">1800</property>
<property name="hibernate.c3p0.max_statements">50</property>


<property name="show_sql">true</property>
<property name="use_sql_comments">true</property>

<!-- mapping files. -->
<mapping resource="com/cascada/dataaccess/Host.hbm.xml"/>
<mapping resource="com/cascada/dataaccess/Contact.hbm.xml"/>
<mapping resource="com/cascada/dataaccess/ContactList.hbm.xml"/>
<mapping resource="com/cascada/dataaccess/User.hbm.xml"/>
<mapping resource="com/cascada/dataaccess/Device.hbm.xml"/>
<mapping resource="com/cascada/dataaccess/Application.hbm.xml"/>
<mapping resource="com/cascada/dataaccess/ApplicationInstance.hbm.xml"/>
<mapping resource="com/cascada/dataaccess/ApplicationLocation.hbm.xml"/>
<mapping resource="com/cascada/dataaccess/Recommendation.hbm.xml"/>
<mapping resource="com/cascada/dataaccess/RecommendationStatus.hbm.xml"/>

</session-factory>

</hibernate-configuration>
host.hbm.xml - the object in question
<hibernate-mapping package="com.cascada.dataaccess">

<class name="Host">
<id name="id">
<generator class="identity"/>
</id>
<version name="version" type="long"/>

<property name="name"/>
<property name="WAPPlugin"/>
<property name="SMSPlugin"/>
<property name="locatorPlugin"/>
<property name="rules"/>

<!-- host has a set of other hosts that can be searched for content -->
<set name="searchableHosts" table="host_host_link">
<key column="hostID" not-null="true"/>
<many-to-many column="searchableID" class="Host"/>
</set>
<!-- a set of application locations stored on that host -->
<set name="applicationLocations" inverse="true" cascade="all-delete-orphan" batch-size="10">
<key column="hostID"/>
<one-to-many class="ApplicationLocation"/>
</set>
</class>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
Long lastID = null;
for (int i = 0; i < 5; i++)
{
Host newhost = new Host();
newhost.setLocatorPlugin("locator");
newhost.setName("host #" + i);
newhost.setRules("rules");
newhost.setSMSPlugin("sms");
newhost.setWAPPlugin("wap");
session.save(newhost);
out.println("added host '" + newhost.getName() + "' id = " + newhost.getId() + "<br>");
lastID = newhost.getId();
}

Full stack trace of any exception that occurs:
java.lang.NullPointerException
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2315)
at com.mysql.jdbc.PreparedStatement.setLong(PreparedStatement.java:697)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setLong(NewProxyPreparedStatement.java:650)
at org.hibernate.type.LongType.set(LongType.java:40)
at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:62)
at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:39)
at org.hibernate.persister.entity.BasicEntityPersister.dehydrate(BasicEntityPersister.java:1592)
at org.hibernate.persister.entity.BasicEntityPersister.dehydrate(BasicEntityPersister.java:1569)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1714)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:2149)
at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:34)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:238)
at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:158)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:104)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:184)
at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:173)
at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:69)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:429)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:424)
at com.cascada.demo.standalone.testHost(standalone.java:101)
at com.cascada.demo.standalone.doit(standalone.java:29)
at com.cascada.demo.standalone.main(standalone.java:518)
Name and version of the database you are using:

The generated SQL (show_sql=true):
MySQL4.1.1 win32
Mysqlconnector 3.1.7

Little fiddling showed that disabling statement caching (removing max_statements line) fixed it. Should that lead me to assume caching isn't support for mysql or am I still missing some options?

I am having equal problem with c3p0 0.8.5.1, jTDS 1.0.2, Hibernate 2.1.8 and SQL Server. Apparently the new c3p0 not only returns but also closes cached PreparedStatemens together with the underlying one - here the jTDS PrepareStatement. When th same query is executed again, the closed statement is taken from the cache, which issues an exception:

java.sql.SQLException: Invalid state, the PreparedStatement object is closed.
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.checkOpen(JtdsPreparedStatement.java:185)
at net.sourceforge.jtds.jdbc.JtdsStatement.setFetchSize(JtdsStatement.java:773)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setFetchSize(NewProxyPreparedStatement.java:1448)
at net.sf.hibernate.impl.BatcherImpl.setStatementFetchSize(BatcherImpl.java:287)
....

Please note, with the previous Version c3p0 0.8.4.5 this does not happen.
With best Regards
Peter

PS: Here is my configuration "c0p3.properties" (the same is set to hibernate.properties too):

# Sample ResourceBundle properties file
c3p0.acquireRetryAttempts=30
c3p0.acquireRetryDelay=1000
#c3p0.autoCommitOnClose=false

c3p0.automaticTestTable=c3p0TestTable
c3p0.breakAfterAcquireFailure=false
c3p0.idleConnectionTestPeriod=120
c3p0.maxStatements=0
c3p0.maxStatementsPerConnection=5000
c3p0.numHelperThreads=15

c3p0.testConnectionOnCheckin=false
c3p0.testConnectionOnCheckout=false

Very embarrassing.

Please upgrade to c3p0-0.8.5.2 or c3p0-0.9.0-pre3. As fassev figured out, c3p0 was erroneously calling close() on Statements on check-in to the cache.

(This should have been a very obvious error, but some databases don't actually invalidate Statements on close(), so my Statement caching tests appeared to work beautifully. Sigh.)

Apologies for the rather serious issue. All users of c3p0-0.8.5.x or c3p0-0.9.0-preX who use Statement caching are strongly encouraged to upgrade. As fassev noted, users of c3p0-0.8.4.x are not affected by this issue.

Many thanks to tbayboy and fassev for finding and figuring out the problem.

smiles,
Steve (c3p0 guy)