Hi experts,

We have the following hibernate configuration using c3p0 connection, based on http://www.hibernate.org/214.html . However, we found the application is dead after the Oracle 9 got restarted or after a few days.

Does hibernate have the ability to reconnect to the database in such situation ? Do we have to get the better version of c3p0 ? Any help will be appreciated ?

---------------------------------------------------------------------------

Hibernate version: 2.01

Name and version of the database you are using:Oracle 9i

Server:Tomcat 4.1.29

hibernate.properties

hibernate.connection.username=user
hibernate.connection.password=password

hibernate.connection.url=jdbc:oracle:thin:@IMCOSL05.indygov.org:1521:test9

hibernate.connection.driver_class=oracle.jdbc.driver.OracleDriver
hibernate.dialect=net.sf.hibernate.dialect.Oracle9Dialect
hibernate.transaction.factory_class=net.sf.hibernate.transaction.JDBCTransactionFactory

hibernate.c3p0.max_size=30
hibernate.c3p0.min_size=3
hibernate.c3p0.idle_test_period=50
hibernate.c3p0.max_statements=0
hibernate.c3p0.acquire_increment=1
hibernate.c3p0.timeout=0

Full stack trace of any exception that occurs:
Stack track of the exception:

2004-09-15 23:30:46,812 DEBUG org.indygov.hr.city.par.util.PARScheduleThread - Background processing is running!
2004-09-15 23:30:47,000 WARN net.sf.hibernate.util.JDBCExceptionReporter - SQL Error: 17410, SQLState: null
2004-09-15 23:30:47,015 ERROR net.sf.hibernate.util.JDBCExceptionReporter - No more data to read from socket
2004-09-15 23:30:47,015 WARN net.sf.hibernate.util.JDBCExceptionReporter - SQL Error: 17410, SQLState: null
2004-09-15 23:30:47,015 ERROR net.sf.hibernate.util.JDBCExceptionReporter - No more data to read from socket
2004-09-15 23:30:47,031 ERROR net.sf.hibernate.util.JDBCExceptionReporter - Could not execute query
java.sql.SQLException: No more data to read from socket
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:210)
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:857)
at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:731)
at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:690)
at oracle.jdbc.ttc7.Oopen.receive(Oopen.java:103)
at oracle.jdbc.ttc7.TTC7Protocol.open(TTC7Protocol.java:466)
at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:413)
at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:432)
at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java:182)
at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java:165)
at oracle.jdbc.driver.OracleConnection.privatePrepareStatement(OracleConnection.java:604)
at oracle.jdbc.driver.OracleConnection.prepareStatement(OracleConnection.java:485)
at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:228)
at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(BatcherImpl.java:61)
at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:703)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:184)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:132)
at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
at net.sf.hibernate.loader.Loader.list(Loader.java:940)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:833)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1475)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1454)
at org.indygov.hr.city.par.dao.par.PARTrackDAO.findByPARStatus(PARTrackDAO.java:362)
at org.indygov.hr.city.par.service.par.PARTrackService.findByPARStatus(PARTrackService.java:108)
at org.indygov.hr.city.par.web.par.BackgroundProcess.handleIdle(BackgroundProcess.java:105)
at org.indygov.hr.city.par.web.par.BackgroundProcess.start(BackgroundProcess.java:64)
at org.indygov.hr.city.par.util.PARScheduleThread$PARBackGroundTask.run(PARScheduleThread.java:46)
at java.util.TimerThread.mainLoop(Timer.java:432)
at java.util.TimerThread.run(Timer.java:382)
2004-09-15 23:30:47,046 ERROR org.indygov.hr.city.par.dao.par.PARTrackDAO - Hibernate ExceptionCould not execute query
2004-09-15 23:30:47,046 ERROR org.indygov.hr.city.par.web.par.BackgroundProcess - net.sf.hibernate.JDBCException: Could not execute query


----------------------------------------------

actually, all is covered by hibernate.c3p0.idle_test_period=50 .... there must be something wrong.... elsewhere

ohhh what about
hibernate.connection.provider_class ? (C3P0ConnectionProvider )

it seems you're using the default pool --> ugly ;)

Thanks for the reply.

According to the hibernate manul chapter 3:
"C3P0 is an open source JDBC connection pool distributed along with Hibernate in the lib directory. Hibernate will use the built-in C3P0ConnectionProvider for connection pooling if you set the hibernate.c3p0.* properties."

Therefore, it does not seem to me that I need to define hibernate.connection.provider_class.

If so, is it
hibernate.connection.provider_class=C3P0ConnectionProvider

If you look at the props file you'll see


C3PO is commented out by default, in the quoted bit I've uncommented it, telling hibernate C3PO is the connection provider I want to use.

Perhaps the manual should have said "set the props AND select the provider"

friends,
let's continue on
http://forum.hibernate.org/viewtopic.php?t=934779

I establish a connection using standard JNDI and a connection provider class ...

My hibernate.cfg.xml file looks like :

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 2.0//EN"

"http://hibernate.sourceforge.net/hibernate-configuration-2.0.dtd">

<hibernate-configuration>

<session-factory>
<property name="DATASOURCE">java:comp/env/jdbc/RFDSRef</property>
<property name="connection.provider_class">somepath.JNDIConnection</property>
<property name="dialect">net.sf.hibernate.dialect.OracleDialect</property>
<property name="show_sql">false</property>
<property name="transaction.factory_class">net.sf.hibernate.transaction.JDBCTransactionFactory</property>
<property name="hibernate.cache.provider_class">net.sf.hibernate.cache.HashtableCacheProvider</property>
<mapping resource="someOtherPath/FormImpl.hbm.xml"/>
</session-factory>

</hibernate-configuration>

while JNDIConnection.java looks like :

package somePath;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.naming.Context;
import javax.sql.DataSource;

import net.sf.hibernate.HibernateException;
import net.sf.hibernate.connection.ConnectionProvider;

public class JNDIConnection implements ConnectionProvider
{
private String dsName;
private DataSource ds;

public void configure( Properties props ) throws HibernateException {
props.list(System.out);
dsName = props.getProperty("hibernate.DATASOURCE");
try {
Context ctx = new InitialContext();
ds = (DataSource)ctx.lookup( dsName );
} catch ( NamingException nx ) {
System.out.println(nx.getMessage());
throw new HibernateException(nx.getMessage());
}
if ( this.ds == null ) {
String msg = "invalid data source specified : " + this.dsName;
System.out.println( msg );
throw new HibernateException( msg );
}
}

public Connection getConnection() throws SQLException {
return this.ds.getConnection();
}

public void closeConnection( Connection con ) throws SQLException {
con.close();
}

public void close() throws HibernateException {
System.out.println( "shutting down data source : " + this.dsName );
}

}