craig a chapman

Do you have any more info. e.g a stack trace

I know that closing db connections returns them to the pool and anywhere in my code that I am finished with an open connection, I do so. I am aware of the consequences of not closing connections, result sets etc.

But, the authentication is performed by Tomcat. It uses the DataSourceRealm settings to obtain a connection to the database to authenticate users. I don't explicitly open a connection anywhere in my code to do this, so I cannot explicitly close the connection. Surely theres a parameter, such as the one Ulf mentioned that tells the pool to close any connections that have not been utilised in a specified time, thus returning them to the pool. If you can configure Tomcat to open a connection to the database to authenticate users, there must be a corresponding close option, I would have thought.

The "InactivityTimeout" parameter doesn't appear to work. I'm monitoring the connections through MySQL administrator. I would expect the number of connections to decrease with inactivity but they all remain open. I've only seen that parameter mentioned when setting up DataSource pools with Orion, with a quick search in google.

But I don't create the connections within my code. It's going on behind the scenes in Tomcat. It authenticates users based on the user roles etc within my database and obtains a connection to the database to do that, from the data source declaration.

Thanks Ulf, I wasn't aware of that parameter.

Eve, I have mine set up this way...

Within the Engine element in server.xml I have the realm configured like so: (These are the table/column info definitions in my database)


<Realm className="org.apache.catalina.realm.DataSourceRealm" debug="99"
dataSourceName="jdbc/tomcatusers"
userTable="users" userNameCol="user_name" userCredCol="user_pass"
userRoleTable="user_roles" roleNameCol="role_name"/>


In the GlobalNamingResources element I have my datasource properties like so: (Obviously your user name and password in the blank fields)

<Resource name="jdbc/tomcatusers"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFactory"
maxWait="5000"
maxActive="15"
maxIdle="2"
inactivityTimeout="300"
removeAbandoned="true"
removeAbandonedTimeout="60"
username=""
password=""
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/tomcatusers"/>

Then I have a resource link in my apps context.xml file in tomcat_home/catalina/localhost, like so:

<ResourceLink name="jdbc/tomcatusers"
global="jdbc/tomcatusers"
type="javax.sql.DataSource"/>


HTH

I never did solve this with JOTM. I ended up integrating Spring and made use of it's Hibernate and Transaction Management support. Very worthwhile.

I have DatasourceRealm configured in Tomcat 5.5.7. The user roles, passwords etc are in my database and I am using Basic authentication. So, a user tries to access a resource, prompted for credentials, log on etc. This works fine but eventually the number of maxActive connections are reached, 15 in my case when it shouldn't be. The problem seems to be that the connections are not being closed. How do the authentication connections get closed in tomcat? Is it related to the session expiry time, or is there a parameter that can be added to the datasource declaration to do this?

Has anyone successfully managed to get hibernate running with tomcat and using JOTM. I've followed a few examples and am using the HibernateUtil example from the CaveatEmptor example. In my application I get a begin a transaction by calling HibernateUtil.getSeesionFactory().getCurrentSession().beginTransactionI() but this always fails giving me a "org.hibernate.HibernateException: No TransactionManagerLookup specified" exception.

My hibernate.cfg.xml file is:

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

<session-factory>

<property name="connection.datasource">java:comp/env/jdbc/wikidsistas</property>
<property name="show_sql">true</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="hibernate.current_session_context_class">jta</property>
<property name="hibernate.transaction.factory_class">
org.hibernate.transaction.JTATransactionFactory
</property>
<property name="hibernate.transaction.manager_lookup_class">
org.hibernate.transaction.JOTMTransactionManagerLookup
</property>
<property name="jta.UserTransaction">java:comp/UserTransaction</property>

<!-- Mapping files -->
<mapping resource="Customer.hbm.xml"/>

</session-factory>

</hibernate-configuration>


and my tomcat context.xml file is:

<Context path="/wikidsistas" docBase="wikidsistas">
<Resource name="jdbc/wikidsistas" auth="Container"
type="javax.sql.DataSource"
username="user"
password="secret"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/wikidsistas"
maxActive="15" maxIdle="2"
maxWait="10000"/>

<ResourceLink name="jdbc/tomcatusers"
global="jdbc/tomcatusers"
type="javax.sql.DataSource"/>

<Resource name="UserTransaction" auth="Container"
type="javax.transaction.UserTransaction"
factory="org.objectweb.jotm.UserTransactionFactory"
jotm.timeout="60"/>
<Manager className="org.apache.catalina.session.PersistentManager" saveOnRestart="false"/>

</Context>

Strange thing is, calls to HibernateUtil.getConfiguration().getProperty(Environment.TRANSACTION_MANAGER_STRATEGY)) tells me that the strategy is "org.hibernate.transaction.JOTMTransactionManagerLookup"

I'm baffled.

Can you provide a bit more information on how your connecting to the database i.e a code sample. A stack trace of the exception too.

Ta

Scrap that last comment. I just remembered you have managed to connect with your bean.

Does the remote database have connections via TCP/IP enabled? You can change this setting through MySQL administrator -> Startup Variables -> General Parameters. I think it's disabled by default.

You haven't specified the port number in the second url.

Have a read at these.

http://forum.java.sun.com/thread.jspa?threadID=419214

http://support.microsoft.com/default.aspx?scid=kb;en-us;313178

Yes, I did think of that but I'm not sure its that straight-forward. I have a struts based web app and I am performing in-container unit testing. The particular struts action calls a delete method of my DAO, catches any exceptions, determines if it's a BatchUpdateException (BUE), and from the updateCounts I can determine which rows were not deleted, as the array of update counts is in the same order as the statements were executed, and therefore provide a useful error message.

To do as you suggest, I think I'd need to create a separate DAO that deliberately throws a BUE for test purposes only but that isn't testing my product code.

This test just involves setting some request parameters and invoking the actionPerform method of the struts action. I have control over the parameters for the sql statement, so it would be useful if I could do something with them to cause a BatchUpdateException.

Sorry fellas, I was getting my wires crossed there. I meant ResultSetMetaData not DatabaseMetaData. In any case I didn't answer the question as it appears I misunderstood it.