You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Tracey Annison <ta...@trisystems.co.uk> on 2007/09/13 15:58:01 UTC
Spring, Ibatis, and DBCP commons pooling
We are using Java with Spring to access AS/400 databases via Ibatis and
the SQLMapClient, and pooling connections via the Apache Commons DBCP.
We have a problem when the application remains running, but the AS/400
connection is lost, due to a closed job or an IPL or whatever. After
this happens, the first call on the database fails, though subsequent
calls succeed.
The odd thing is that it seems to be failing while trying to return the
bad connection, presumably as part of some cleanup operation, which
would hopefully be followed by an attempt to reconnect? It looks to me
as though the Spring DataSourceUtils finds that the connection is dead,
and tries to return it to the pool (presumably prior to obtaining a less
dead one?) and clashes with the Tomcat DBCP that has already done so.
But I cannot seem to find a way of seeing what is going on during this
process, as I can't find out how to log these actions in any more
detail.
So I don't know how to correct this issue... can anyone help me?
We are setting up an Ibatis DataSource in Spring like this :
[CODE]<bean id="iseriesJdbcDataSource"
class="org.springframework.jndi.JndiObjectFactoryBean">
<property name="jndiName">
<value>java:comp/env/jdbc/host</value>
</property>
</bean>
<bean id="sqlMapClient"
class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="configLocation">
<value>/WEB-INF/sql-map-config.xml</value>
</property>
<property name="useTransactionAwareDataSource">
<value>true</value>
</property>
<property name="dataSource">
<ref bean="iseriesJdbcDataSource"/>
</property>
</bean>
<bean id="sqlMapClientTemplate"
class="org.springframework.orm.ibatis.SqlMapClientTemplate">
<property name="sqlMapClient">
<ref bean="sqlMapClient"/>
</property>
</bean>
<bean id="ourDAO" class="uk.co.XXX.our.dao.OurIbatisDAO">
<property name="sqlMapClientTemplate">
<ref bean="sqlMapClientTemplate"/>
</property>
</bean>[/CODE]
Which uses the following settings in the Ibatis sql-map-client.xml :
[CODE] <transactionManager type="JDBC">
<dataSource type="JNDI">
<property name="DataSource" value="java:comp/env/jdbc/host" />
</dataSource>
</transactionManager>[/CODE]
And also these Apache DBCP settings in context.xml :
[CODE]<Resource name="jdbc/host"
auth="Container"
type="javax.sql.DataSource"
driverClassName="com.ibm.as400.access.AS400JDBCDriver"
url="jdbc:as400://999.999.999.999/HOSTLIB;prompt=false;naming=system;dat
eformat=iso"
username="HOSTUSER"
password="HOSTPWD" />
<ResourceParams name="jdbc/host">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>10</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>2</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>100</value>
</parameter>
<parameter>
<name>testOnBorrow</name>
<value>true</value>
</parameter>
<parameter>
<name>validationQuery</name>
<value>select 1</value>
</parameter>
</ResourceParams>[/CODE]
Using these log4J settings to get the debugs :
[CODE]log4j.category.com.ibatis = DEBUG, file
log4j.category.org.apache = DEBUG, file
log4j.category.org.springframework = DEBUG, file[/CODE]
We see logging like this for a successful result, AS/400 present, all is
well :
[CODE]11:43:45,271 uk.co.XXX.our.soap.OurService.getItem():359 - Using
sqlMapClient >com.ibatis.sqlmap.engine.impl.SqlMapClientImpl@1705cd8<
11:43:45,271 uk.co.XXX.our.soap.OurService.getItem():361 -
sqlMapClient.getCurrentConnection() is >null<
11:43:45,271 uk.co.XXX.our.soap.OurService.getItem():363 -
sqlMapClient.getDataSource() is
>org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy@1df
9efc<
11:43:45,287
org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection():11
1 - Fetching JDBC Connection from DataSource
11:43:52,803
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{conn-100000} Connection
11:43:53,365
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{pstm-100001} PreparedStatement: select count(*) from etc, etc, etc....
11:43:53,365
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{pstm-100001} Parameters: [etc, etc]
11:43:53,365
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{pstm-100001} Types: [etc, etc]
11:43:54,850
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{rset-100002} ResultSet
11:43:54,881
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{rset-100002} Header: [00001]
11:43:54,881
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{rset-100002} Result: [1]
11:43:54,975
org.springframework.jdbc.datasource.DataSourceUtils.doReleaseConnection(
):285 - Returning JDBC Connection to DataSource
[/CODE]
And logging like this for an unsuccessful result, when the AS/400 job
has been closed in the meantime :
[CODE]11:51:05,537 uk.co.XXX.our.soap.OurService.getItem():359 - Using
sqlMapClient >com.ibatis.sqlmap.engine.impl.SqlMapClientImpl@1705cd8<
11:51:05,537 uk.co.XXX.our.soap.OurService.getItem():361 -
sqlMapClient.getCurrentConnection() is >null<
11:51:05,537 uk.co.XXX.our.soap.OurService.getItem():363 -
sqlMapClient.getDataSource() is
>org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy@1df
9efc<
11:51:05,537
org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection():11
1 - Fetching JDBC Connection from DataSource
11:51:05,537
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{conn-100064} Connection
11:51:05,553
org.springframework.jdbc.datasource.DataSourceUtils.doReleaseConnection(
):285 - Returning JDBC Connection to DataSource
11:51:05,615 uk.co.XXX.our.dao.OurIbatisDAO.isItemExtant():538 - Ibatis
DAO Exception
java.sql.SQLException: Already closed.
at
org.apache.tomcat.dbcp.dbcp.PoolableConnection.close(PoolableConnection.
java:77)
at
org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper
.close(PoolingDataSource.java:180)
at
org.springframework.jdbc.datasource.DataSourceUtils.doReleaseConnection(
DataSourceUtils.java:286)
at
org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$Tran
sactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.jav
a:161)
at $Proxy52.close(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at
com.ibatis.common.jdbc.logging.ConnectionLogProxy.invoke(ConnectionLogPr
oxy.java:62)
at $Proxy50.close(Unknown Source)
at
com.ibatis.sqlmap.engine.transaction.external.ExternalTransaction.close(
ExternalTransaction.java:82)
at
com.ibatis.sqlmap.engine.transaction.TransactionManager.end(TransactionM
anager.java:110)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.endTransaction(SqlM
apExecutorDelegate.java:776)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.endTransaction(SqlMapSes
sionImpl.java:137)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.endTransaction(SqlMapClie
ntImpl.java:115)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.autoEndTransaction(
SqlMapExecutorDelegate.java:860)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlM
apExecutorDelegate.java:568)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlM
apExecutorDelegate.java:536)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSes
sionImpl.java:93)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClie
ntImpl.java:70)
at uk.co.XXX.our.dao.OurIbatisDAO.isItemExtant(ItemIbatisDAO.java:528)
at uk.co.XXX.our.soap.OurService.getItem(OurService.java:367)
at uk.co.XXX.our.soap.OurService.getItem(OurService.java:261)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at
org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java
:397)
at etc, etc, etc....
at java.lang.Thread.run(Unknown Source)[/CODE]
Cheers
Tracey Annison
----------------------------------------------------------------------
The information in this email is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this email by
anyone else is unauthorised. If you are not the intended recipient,
any disclosure, copying, distribution, or any action taken or omitted
to be taken in reliance on it, is prohibited and may be unlawful.
TriSystems Ltd. cannot accept liability for statements made which are clearly
the sender's own.
RE: Spring, Ibatis, and DBCP commons pooling
Posted by Tracey Annison <ta...@trisystems.co.uk>.
Hiya, Jeff
Yes, it is Tomcat we're using.
Ah... we thought that was handled by the context.xml setup that we'd
done, with the testOnBorrow property & query? In my ignorance, I figured
that if TestOnBorrow failed, it'd go and try again, and make another
connection that would work. Is there something else I should be looking
at?
Mind you, the logs (relevant bit below) kind of imply that this IS
what's happening, and that the failure is happening when the bad
connection is detected and "returned"... don't they?
11:51:05,537
org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection():11
1 - Fetching JDBC Connection from DataSource
11:51:05,537
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23
-{conn-100064} Connection
11:51:05,553
org.springframework.jdbc.datasource.DataSourceUtils.doReleaseConnection(
):285 - Returning JDBC Connection to DataSource
11:51:05,615 uk.co.XXX.our.dao.OurIbatisDAO.isItemExtant():538 - Ibatis
DAO Exception java.sql.SQLException: Already closed.
at
org.apache.tomcat.dbcp.dbcp.PoolableConnection.close(PoolableConnection.
java:77)
at
org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper
.close(PoolingDataSource.java:180)
at
org.springframework.jdbc.datasource.DataSourceUtils.doReleaseConnection(
DataSourceUtils.java:286)
at
org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$Tran
sactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.jav
a:161)
at $Proxy52.close(Unknown Source)
Cheers
Tracey Annison
_____
From: Jeff Butler [mailto:jeffgbutler@gmail.com]
Sent: 14 September 2007 12:07
To: user-java@ibatis.apache.org
Subject: Re: Spring, Ibatis, and DBCP commons pooling
If you're using JNDI datasources, then it's likely that you'll need to
configure ping queries in the container that's managing the connection
pool.
What's the container? I'm guessing you're using Tomcat, but it's not
clear.
Jeff Butler
On 9/14/07, Tracey Annison <ta...@trisystems.co.uk> wrote:
Heya
Hmmm, yes... But isn't that functionally the same as what we have here
in our context.xml :
<Resource name="jdbc/imacs"
auth="Container"
type="javax.sql.DataSource"
driverClassName="XXX"
url="XXX"
username="XXX"
password="XXX" />
<ResourceParams name="jdbc/imacs">
<parameter>
<name>factory</name>
<value>
org.apache.commons.dbcp.BasicDataSourceFactory
</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>10</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>2</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>100</value>
</parameter>
<parameter>
<name>testOnBorrow</name>
<value>true</value>
</parameter>
<parameter>
<name>validationQuery</name>
<value>select 1</value>
</parameter>
</ResourceParams>
Which is giving the bad results that we see...
Cheers
Tracey Annison
-----Original Message-----
From: charlie bird [mailto: zebthecat@yahoo.co.uk
<ma...@yahoo.co.uk> ]
Sent: 13 September 2007 16:57
To: user-java@ibatis.apache.org
Subject: RE: Spring, Ibatis, and DBCP commons pooling
Don't know if this'll help but we always put the dbcp config in the
Apache application context xml and NOT in the app's Resouce config.
I think that the properties you're looking for are:
testOnBorrow
testOnReturn
testWhileIdle
validationQuery
See here for more info:
http://commons.apache.org/dbcp/configuration.html
Something like this maybe:
<Resource name="jdbc/creditSubscriptions"
auth="Container" type=" javax.sql.DataSource"
maxActive="100" maxIdle="30"
maxWait="10000" username="sa" password="password"
driverClassName="class"
removeAbandoned="true" removeAbandonedTimeout="60"
logAbandoned="true"
url="jdbc:somurl"
validationQuery="select 'hello'"
testOnBorrow="true"
testOnReturn="true"
testWhileIdle="true"
/>
--- Tracey Annison <ta...@trisystems.co.uk> wrote:
> But this is a setup for a SimpleDataSource, with Ibatis pooling, and
> not using the apache commons DBCP pooling at all, isn't it?
>
> You see, we had it set up like this, with settings that we thought
> were a hangover from before the apace commons poling was enabled, and
> we saw exactly the same problems... it seemed to us that having pool
> settings here, and in the context.xml for apache, was a mistake... ?
>
> Cheers
> Tracey Annison
>
>
>
> _____
----------------------------------------------------------------------
The information in this email is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this email by
anyone else is unauthorised. If you are not the intended recipient,
any disclosure, copying, distribution, or any action taken or omitted
to be taken in reliance on it, is prohibited and may be unlawful.
TriSystems Ltd. cannot accept liability for statements made which are clearly
the sender's own.
Re: Spring, Ibatis, and DBCP commons pooling
Posted by Jeff Butler <je...@gmail.com>.
If you're using JNDI datasources, then it's likely that you'll need to
configure ping queries in the container that's managing the connection pool.
What's the container? I'm guessing you're using Tomcat, but it's not clear.
Jeff Butler
On 9/14/07, Tracey Annison <ta...@trisystems.co.uk> wrote:
>
> Heya
>
> Hmmm, yes... But isn't that functionally the same as what we have here
> in our context.xml :
>
> <Resource name="jdbc/imacs"
> auth="Container"
> type="javax.sql.DataSource"
> driverClassName="XXX"
> url="XXX"
> username="XXX"
> password="XXX" />
>
> <ResourceParams name="jdbc/imacs">
> <parameter>
> <name>factory</name>
> <value>
>
> org.apache.commons.dbcp.BasicDataSourceFactory
> </value>
> </parameter>
> <parameter>
> <name>maxActive</name>
> <value>10</value>
> </parameter>
> <parameter>
> <name>maxIdle</name>
> <value>2</value>
> </parameter>
> <parameter>
> <name>maxWait</name>
> <value>100</value>
> </parameter>
> <parameter>
> <name>testOnBorrow</name>
> <value>true</value>
> </parameter>
> <parameter>
> <name>validationQuery</name>
> <value>select 1</value>
> </parameter>
> </ResourceParams>
>
> Which is giving the bad results that we see...
>
> Cheers
> Tracey Annison
>
> -----Original Message-----
> From: charlie bird [mailto:zebthecat@yahoo.co.uk]
> Sent: 13 September 2007 16:57
> To: user-java@ibatis.apache.org
> Subject: RE: Spring, Ibatis, and DBCP commons pooling
>
> Don't know if this'll help but we always put the dbcp config in the
> Apache application context xml and NOT in the app's Resouce config.
>
> I think that the properties you're looking for are:
> testOnBorrow
> testOnReturn
> testWhileIdle
> validationQuery
>
> See here for more info:
> http://commons.apache.org/dbcp/configuration.html
>
> Something like this maybe:
>
> <Resource name="jdbc/creditSubscriptions"
> auth="Container" type="javax.sql.DataSource"
> maxActive="100" maxIdle="30"
> maxWait="10000" username="sa" password="password"
> driverClassName="class"
> removeAbandoned="true" removeAbandonedTimeout="60"
> logAbandoned="true"
> url="jdbc:somurl"
> validationQuery="select 'hello'"
> testOnBorrow="true"
> testOnReturn="true"
> testWhileIdle="true"
> />
>
>
>
>
> --- Tracey Annison <ta...@trisystems.co.uk> wrote:
>
> > But this is a setup for a SimpleDataSource, with Ibatis pooling, and
> > not using the apache commons DBCP pooling at all, isn't it?
> >
> > You see, we had it set up like this, with settings that we thought
> > were a hangover from before the apace commons poling was enabled, and
> > we saw exactly the same problems... it seemed to us that having pool
> > settings here, and in the context.xml for apache, was a mistake... ?
> >
> > Cheers
> > Tracey Annison
> >
> >
> >
> > _____
> >
> > From: Meindert [mailto:meindert@pastelebusiness.com]
> > Sent: 13 September 2007 15:06
> > To: user-java@ibatis.apache.org
> > Subject: RE: Spring, Ibatis, and DBCP commons pooling
> >
> >
> >
> > I would think you will need to ping the connection before you can use
> > it.
> >
> >
> >
> > <transactionManager type="JDBC">
> >
> > <dataSource type="SIMPLE">
> >
> > <property value="${driver}"
> > name="JDBC.Driver"/>
> >
> > <property value="${url}"
> > name="JDBC.ConnectionURL"/>
> >
> > <property value="${username}"
> > name="JDBC.Username"/>
> >
> > <property value="${password}"
> > name="JDBC.Password"/>
> >
> > <property value="15"
> > name="Pool.MaximumActiveConnections"/>
> >
> > <property value="15"
> > name="Pool.MaximumIdleConnections"/>
> >
> > <property value="1000"
> > name="Pool.MaximumWait"/>
> >
> > <property name="Pool.PingQuery" value="SELECT
> > 1 FROM Company"/>
> >
> > <property name="Pool.PingEnabled" value="true"
> > />
> >
> > <property name="Pool.PingConnectionsOlderThan"
> > value="1000000" />
> >
> > </dataSource>
> >
> > </transactionManager>
> >
> >
> >
> > _____
> >
> > From: Tracey Annison
> > [mailto:tannison@trisystems.co.uk]
> > Sent: Thursday, September 13, 2007 3:58 PM
> > To: user-java@ibatis.apache.org
> > Subject: Spring, Ibatis, and DBCP commons pooling
> >
> >
> >
> > We are using Java with Spring to access AS/400 databases via Ibatis
> > and the SQLMapClient, and pooling connections via the Apache Commons
> > DBCP.
> > We have a problem when the application remains running, but the AS/400
>
> > connection is lost, due to a closed job or an IPL or whatever. After
> > this happens, the first call on the database fails, though subsequent
> > calls succeed.
> >
> > The odd thing is that it seems to be failing while trying to return
> > the bad connection, presumably as part of some cleanup operation,
> > which would hopefully be followed by an attempt to reconnect? It looks
>
> > to me as though the Spring DataSourceUtils finds that the connection
> > is dead, and tries to return it to the pool (presumably prior to
> > obtaining a less dead one?) and clashes with the Tomcat DBCP that has
> > already done so.
> > But I cannot seem to find a way of seeing what is going on during this
>
> > process, as I can't find out how to log these actions in any more
> > detail.
> >
> > So I don't know how to correct this issue... can anyone help me?
> > We are setting up an Ibatis DataSource in Spring like this :
> > [CODE]<bean id="iseriesJdbcDataSource"
> >
> class="org.springframework.jndi.JndiObjectFactoryBean">
> > <property name="jndiName">
> > <value>java:comp/env/jdbc/host</value>
> > </property>
> > </bean>
> > <bean id="sqlMapClient"
> >
> class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
> > <property name="configLocation">
> > <value>/WEB-INF/sql-map-config.xml</value>
> > </property>
> > <property name="useTransactionAwareDataSource">
> > <value>true</value>
> > </property>
> > <property name="dataSource">
> > <ref bean="iseriesJdbcDataSource"/>
> > </property>
> > </bean>
> > <bean id="sqlMapClientTemplate"
> >
> class="org.springframework.orm.ibatis.SqlMapClientTemplate">
> > <property name="sqlMapClient">
> > <ref bean="sqlMapClient"/>
> > </property>
> > </bean>
> > <bean id="ourDAO"
> > class="uk.co.XXX.our.dao.OurIbatisDAO">
> > <property name="sqlMapClientTemplate"> <ref
> > bean="sqlMapClientTemplate"/> </property> </bean>[/CODE] Which uses
> > the following settings in the Ibatis sql-map-client.xml :
> > [CODE] <transactionManager type="JDBC"> <dataSource type="JNDI">
> > <property name="DataSource"
> > value="java:comp/env/jdbc/host" />
> > </dataSource>
> > </transactionManager>[/CODE]
>
> > And also these Apache DBCP settings in context.xml :
> > [CODE]<Resource name="jdbc/host"
> > auth="Container"
> > type="javax.sql.DataSource"
> >
> driverClassName="com.ibm.as400.access.AS400JDBCDriver"
> >
> url="jdbc:as400://999.999.999.999/HOSTLIB;prompt=false;naming=system;dat
> > eformat=iso"
> > username="HOSTUSER"
> > password="HOSTPWD" />
> > <ResourceParams name="jdbc/host">
> > <parameter>
> > <name>factory</name>
> >
> <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
> > </parameter>
> > <parameter>
> > <name>maxActive</name>
> > <value>10</value>
> > </parameter>
> > <parameter>
> > <name>maxIdle</name>
> > <value>2</value>
> > </parameter>
> > <parameter>
> > <name>maxWait</name>
> > <value>100</value>
> > </parameter>
> > <parameter>
> > <name>testOnBorrow</name>
> > <value>true</value>
> > </parameter>
> > <parameter>
> > <name>validationQuery</name>
> > <value>select 1</value>
> > </parameter>
> > </ResourceParams>[/CODE]
> > Using these log4J settings to get the debugs :
> > [CODE]log4j.category.com.ibatis = DEBUG, file
> > log4j.category.org.apache = DEBUG, file
> > log4j.category.org.springframework = DEBUG, file[/CODE] We see logging
>
> > like this for a successful result, AS/400 present, all is well :
> > [CODE]11:43:45,271
> > uk.co.XXX.our.soap.OurService.getItem():359
> === message truncated ===
>
>
>
> ___________________________________________________________
> Want ideas for reducing your carbon footprint? Visit Yahoo! For Good
> http://uk.promotions.yahoo.com/forgood/environment.html
>
>
>
> ----------------------------------------------------------------------
> The information in this email is confidential and may be legally
> privileged.
> It is intended solely for the addressee. Access to this email by
> anyone else is unauthorised. If you are not the intended recipient,
> any disclosure, copying, distribution, or any action taken or omitted
> to be taken in reliance on it, is prohibited and may be unlawful.
> TriSystems Ltd. cannot accept liability for statements made which are
> clearly
> the sender's own.
>
>
>
RE: Spring, Ibatis, and DBCP commons pooling
Posted by Tracey Annison <ta...@trisystems.co.uk>.
Well, I could use a real query, but all I need to know is whethere the
AS/400 is still there, and AFAIK "select 1" will do that. How would
doing an actual select help me?
Also, I can indeed get it to test on return and when idle, but I don't
quite see how that helps either... My problem seems to be that when I
try to use the database after the AS/400 job dies, the SQLMapClient is
still there, tries to use that connection, rejects it, and finds it
already closed and an error is thrown...
Cheers
Tracey Annison
-----Original Message-----
From: Meindert [mailto:meindert@pastelebusiness.com]
Sent: 14 September 2007 10:10
To: user-java@ibatis.apache.org
Subject: RE: Spring, Ibatis, and DBCP commons pooling
I don't use dbcp, so I won't actually answer your question but; What
about using a real (but fast) query for the valdationQuery (like select
x from table y where ID=1 And what about the other two parameters;
testOnReturn testWhileIdle
-----Original Message-----
From: Tracey Annison [mailto:tannison@trisystems.co.uk]
Sent: Friday, September 14, 2007 10:37 AM
To: user-java@ibatis.apache.org
Subject: RE: Spring, Ibatis, and DBCP commons pooling
Heya
Hmmm, yes... But isn't that functionally the same as what we have here
in our context.xml :
<Resource name="jdbc/imacs"
auth="Container"
type="javax.sql.DataSource"
driverClassName="XXX"
url="XXX"
username="XXX"
password="XXX" />
<ResourceParams name="jdbc/imacs">
<parameter>
<name>factory</name>
<value>
org.apache.commons.dbcp.BasicDataSourceFactory
</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>10</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>2</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>100</value>
</parameter>
<parameter>
<name>testOnBorrow</name>
<value>true</value>
</parameter>
<parameter>
<name>validationQuery</name>
<value>select 1</value>
</parameter>
</ResourceParams>
Which is giving the bad results that we see...
Cheers
Tracey Annison
-----Original Message-----
From: charlie bird [mailto:zebthecat@yahoo.co.uk]
Sent: 13 September 2007 16:57
To: user-java@ibatis.apache.org
Subject: RE: Spring, Ibatis, and DBCP commons pooling
Don't know if this'll help but we always put the dbcp config in the
Apache application context xml and NOT in the app's Resouce config.
I think that the properties you're looking for are:
testOnBorrow
testOnReturn
testWhileIdle
validationQuery
See here for more info:
http://commons.apache.org/dbcp/configuration.html
Something like this maybe:
<Resource name="jdbc/creditSubscriptions"
auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30"
maxWait="10000" username="sa" password="password"
driverClassName="class"
removeAbandoned="true" removeAbandonedTimeout="60"
logAbandoned="true"
url="jdbc:somurl"
validationQuery="select 'hello'"
testOnBorrow="true"
testOnReturn="true"
testWhileIdle="true"
/>
--- Tracey Annison <ta...@trisystems.co.uk> wrote:
> But this is a setup for a SimpleDataSource, with Ibatis pooling, and
> not using the apache commons DBCP pooling at all, isn't it?
>
> You see, we had it set up like this, with settings that we thought
> were a hangover from before the apace commons poling was enabled, and
> we saw exactly the same problems... it seemed to us that having pool
> settings here, and in the context.xml for apache, was a mistake... ?
>
> Cheers
> Tracey Annison
>
>
>
> _____
>
> From: Meindert [mailto:meindert@pastelebusiness.com]
> Sent: 13 September 2007 15:06
> To: user-java@ibatis.apache.org
> Subject: RE: Spring, Ibatis, and DBCP commons pooling
>
>
>
> I would think you will need to ping the connection before you can use
> it.
>
>
>
> <transactionManager type="JDBC">
>
> <dataSource type="SIMPLE">
>
> <property value="${driver}"
> name="JDBC.Driver"/>
>
> <property value="${url}"
> name="JDBC.ConnectionURL"/>
>
> <property value="${username}"
> name="JDBC.Username"/>
>
> <property value="${password}"
> name="JDBC.Password"/>
>
> <property value="15"
> name="Pool.MaximumActiveConnections"/>
>
> <property value="15"
> name="Pool.MaximumIdleConnections"/>
>
> <property value="1000"
> name="Pool.MaximumWait"/>
>
> <property name="Pool.PingQuery" value="SELECT
> 1 FROM Company"/>
>
> <property name="Pool.PingEnabled" value="true"
> />
>
> <property name="Pool.PingConnectionsOlderThan"
> value="1000000" />
>
> </dataSource>
>
> </transactionManager>
>
>
>
> _____
>
> From: Tracey Annison
> [mailto:tannison@trisystems.co.uk]
> Sent: Thursday, September 13, 2007 3:58 PM
> To: user-java@ibatis.apache.org
> Subject: Spring, Ibatis, and DBCP commons pooling
>
>
>
> We are using Java with Spring to access AS/400 databases via Ibatis
> and the SQLMapClient, and pooling connections via the Apache Commons
> DBCP.
> We have a problem when the application remains running, but the AS/400
> connection is lost, due to a closed job or an IPL or whatever. After
> this happens, the first call on the database fails, though subsequent
> calls succeed.
>
> The odd thing is that it seems to be failing while trying to return
> the bad connection, presumably as part of some cleanup operation,
> which would hopefully be followed by an attempt to reconnect? It looks
> to me as though the Spring DataSourceUtils finds that the connection
> is dead, and tries to return it to the pool (presumably prior to
> obtaining a less dead one?) and clashes with the Tomcat DBCP that has
> already done so.
> But I cannot seem to find a way of seeing what is going on during this
> process, as I can't find out how to log these actions in any more
> detail.
>
> So I don't know how to correct this issue... can anyone help me?
> We are setting up an Ibatis DataSource in Spring like this :
> [CODE]<bean id="iseriesJdbcDataSource"
>
class="org.springframework.jndi.JndiObjectFactoryBean">
> <property name="jndiName">
> <value>java:comp/env/jdbc/host</value>
> </property>
> </bean>
> <bean id="sqlMapClient"
>
class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
> <property name="configLocation">
> <value>/WEB-INF/sql-map-config.xml</value>
> </property>
> <property name="useTransactionAwareDataSource">
> <value>true</value>
> </property>
> <property name="dataSource">
> <ref bean="iseriesJdbcDataSource"/>
> </property>
> </bean>
> <bean id="sqlMapClientTemplate"
>
class="org.springframework.orm.ibatis.SqlMapClientTemplate">
> <property name="sqlMapClient">
> <ref bean="sqlMapClient"/>
> </property>
> </bean>
> <bean id="ourDAO"
> class="uk.co.XXX.our.dao.OurIbatisDAO">
> <property name="sqlMapClientTemplate"> <ref
> bean="sqlMapClientTemplate"/> </property> </bean>[/CODE] Which uses
> the following settings in the Ibatis sql-map-client.xml :
> [CODE] <transactionManager type="JDBC"> <dataSource type="JNDI">
> <property name="DataSource"
> value="java:comp/env/jdbc/host" />
> </dataSource>
> </transactionManager>[/CODE]
> And also these Apache DBCP settings in context.xml :
> [CODE]<Resource name="jdbc/host"
> auth="Container"
> type="javax.sql.DataSource"
>
driverClassName="com.ibm.as400.access.AS400JDBCDriver"
>
url="jdbc:as400://999.999.999.999/HOSTLIB;prompt=false;naming=system;dat
> eformat=iso"
> username="HOSTUSER"
> password="HOSTPWD" />
> <ResourceParams name="jdbc/host">
> <parameter>
> <name>factory</name>
>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
> </parameter>
> <parameter>
> <name>maxActive</name>
> <value>10</value>
> </parameter>
> <parameter>
> <name>maxIdle</name>
> <value>2</value>
> </parameter>
> <parameter>
> <name>maxWait</name>
> <value>100</value>
> </parameter>
> <parameter>
> <name>testOnBorrow</name>
> <value>true</value>
> </parameter>
> <parameter>
> <name>validationQuery</name>
> <value>select 1</value>
> </parameter>
> </ResourceParams>[/CODE]
> Using these log4J settings to get the debugs :
> [CODE]log4j.category.com.ibatis = DEBUG, file
> log4j.category.org.apache = DEBUG, file
> log4j.category.org.springframework = DEBUG, file[/CODE] We see logging
> like this for a successful result, AS/400 present, all is well :
> [CODE]11:43:45,271
> uk.co.XXX.our.soap.OurService.getItem():359
=== message truncated ===
___________________________________________________________
Want ideas for reducing your carbon footprint? Visit Yahoo! For Good
http://uk.promotions.yahoo.com/forgood/environment.html
----------------------------------------------------------------------
The information in this email is confidential and may be legally
privileged.
It is intended solely for the addressee. Access to this email by anyone
else is unauthorised. If you are not the intended recipient, any
disclosure, copying, distribution, or any action taken or omitted to be
taken in reliance on it, is prohibited and may be unlawful.
TriSystems Ltd. cannot accept liability for statements made which are
clearly the sender's own.
----------------------------------------------------------------------
The information in this email is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this email by
anyone else is unauthorised. If you are not the intended recipient,
any disclosure, copying, distribution, or any action taken or omitted
to be taken in reliance on it, is prohibited and may be unlawful.
TriSystems Ltd. cannot accept liability for statements made which are clearly
the sender's own.
RE: Spring, Ibatis, and DBCP commons pooling
Posted by Meindert <me...@pastelebusiness.com>.
I don't use dbcp, so I won't actually answer your question but;
What about using a real (but fast) query for the valdationQuery (like select
x from table y where ID=1
And what about the other two parameters;
testOnReturn
testWhileIdle
-----Original Message-----
From: Tracey Annison [mailto:tannison@trisystems.co.uk]
Sent: Friday, September 14, 2007 10:37 AM
To: user-java@ibatis.apache.org
Subject: RE: Spring, Ibatis, and DBCP commons pooling
Heya
Hmmm, yes... But isn't that functionally the same as what we have here
in our context.xml :
<Resource name="jdbc/imacs"
auth="Container"
type="javax.sql.DataSource"
driverClassName="XXX"
url="XXX"
username="XXX"
password="XXX" />
<ResourceParams name="jdbc/imacs">
<parameter>
<name>factory</name>
<value>
org.apache.commons.dbcp.BasicDataSourceFactory
</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>10</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>2</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>100</value>
</parameter>
<parameter>
<name>testOnBorrow</name>
<value>true</value>
</parameter>
<parameter>
<name>validationQuery</name>
<value>select 1</value>
</parameter>
</ResourceParams>
Which is giving the bad results that we see...
Cheers
Tracey Annison
-----Original Message-----
From: charlie bird [mailto:zebthecat@yahoo.co.uk]
Sent: 13 September 2007 16:57
To: user-java@ibatis.apache.org
Subject: RE: Spring, Ibatis, and DBCP commons pooling
Don't know if this'll help but we always put the dbcp config in the
Apache application context xml and NOT in the app's Resouce config.
I think that the properties you're looking for are:
testOnBorrow
testOnReturn
testWhileIdle
validationQuery
See here for more info:
http://commons.apache.org/dbcp/configuration.html
Something like this maybe:
<Resource name="jdbc/creditSubscriptions"
auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30"
maxWait="10000" username="sa" password="password"
driverClassName="class"
removeAbandoned="true" removeAbandonedTimeout="60"
logAbandoned="true"
url="jdbc:somurl"
validationQuery="select 'hello'"
testOnBorrow="true"
testOnReturn="true"
testWhileIdle="true"
/>
--- Tracey Annison <ta...@trisystems.co.uk> wrote:
> But this is a setup for a SimpleDataSource, with Ibatis pooling, and
> not using the apache commons DBCP pooling at all, isn't it?
>
> You see, we had it set up like this, with settings that we thought
> were a hangover from before the apace commons poling was enabled, and
> we saw exactly the same problems... it seemed to us that having pool
> settings here, and in the context.xml for apache, was a mistake... ?
>
> Cheers
> Tracey Annison
>
>
>
> _____
>
> From: Meindert [mailto:meindert@pastelebusiness.com]
> Sent: 13 September 2007 15:06
> To: user-java@ibatis.apache.org
> Subject: RE: Spring, Ibatis, and DBCP commons pooling
>
>
>
> I would think you will need to ping the connection before you can use
> it.
>
>
>
> <transactionManager type="JDBC">
>
> <dataSource type="SIMPLE">
>
> <property value="${driver}"
> name="JDBC.Driver"/>
>
> <property value="${url}"
> name="JDBC.ConnectionURL"/>
>
> <property value="${username}"
> name="JDBC.Username"/>
>
> <property value="${password}"
> name="JDBC.Password"/>
>
> <property value="15"
> name="Pool.MaximumActiveConnections"/>
>
> <property value="15"
> name="Pool.MaximumIdleConnections"/>
>
> <property value="1000"
> name="Pool.MaximumWait"/>
>
> <property name="Pool.PingQuery" value="SELECT
> 1 FROM Company"/>
>
> <property name="Pool.PingEnabled" value="true"
> />
>
> <property name="Pool.PingConnectionsOlderThan"
> value="1000000" />
>
> </dataSource>
>
> </transactionManager>
>
>
>
> _____
>
> From: Tracey Annison
> [mailto:tannison@trisystems.co.uk]
> Sent: Thursday, September 13, 2007 3:58 PM
> To: user-java@ibatis.apache.org
> Subject: Spring, Ibatis, and DBCP commons pooling
>
>
>
> We are using Java with Spring to access AS/400 databases via Ibatis
> and the SQLMapClient, and pooling connections via the Apache Commons
> DBCP.
> We have a problem when the application remains running, but the AS/400
> connection is lost, due to a closed job or an IPL or whatever. After
> this happens, the first call on the database fails, though subsequent
> calls succeed.
>
> The odd thing is that it seems to be failing while trying to return
> the bad connection, presumably as part of some cleanup operation,
> which would hopefully be followed by an attempt to reconnect? It looks
> to me as though the Spring DataSourceUtils finds that the connection
> is dead, and tries to return it to the pool (presumably prior to
> obtaining a less dead one?) and clashes with the Tomcat DBCP that has
> already done so.
> But I cannot seem to find a way of seeing what is going on during this
> process, as I can't find out how to log these actions in any more
> detail.
>
> So I don't know how to correct this issue... can anyone help me?
> We are setting up an Ibatis DataSource in Spring like this :
> [CODE]<bean id="iseriesJdbcDataSource"
>
class="org.springframework.jndi.JndiObjectFactoryBean">
> <property name="jndiName">
> <value>java:comp/env/jdbc/host</value>
> </property>
> </bean>
> <bean id="sqlMapClient"
>
class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
> <property name="configLocation">
> <value>/WEB-INF/sql-map-config.xml</value>
> </property>
> <property name="useTransactionAwareDataSource">
> <value>true</value>
> </property>
> <property name="dataSource">
> <ref bean="iseriesJdbcDataSource"/>
> </property>
> </bean>
> <bean id="sqlMapClientTemplate"
>
class="org.springframework.orm.ibatis.SqlMapClientTemplate">
> <property name="sqlMapClient">
> <ref bean="sqlMapClient"/>
> </property>
> </bean>
> <bean id="ourDAO"
> class="uk.co.XXX.our.dao.OurIbatisDAO">
> <property name="sqlMapClientTemplate"> <ref
> bean="sqlMapClientTemplate"/> </property> </bean>[/CODE] Which uses
> the following settings in the Ibatis sql-map-client.xml :
> [CODE] <transactionManager type="JDBC"> <dataSource type="JNDI">
> <property name="DataSource"
> value="java:comp/env/jdbc/host" />
> </dataSource>
> </transactionManager>[/CODE]
> And also these Apache DBCP settings in context.xml :
> [CODE]<Resource name="jdbc/host"
> auth="Container"
> type="javax.sql.DataSource"
>
driverClassName="com.ibm.as400.access.AS400JDBCDriver"
>
url="jdbc:as400://999.999.999.999/HOSTLIB;prompt=false;naming=system;dat
> eformat=iso"
> username="HOSTUSER"
> password="HOSTPWD" />
> <ResourceParams name="jdbc/host">
> <parameter>
> <name>factory</name>
>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
> </parameter>
> <parameter>
> <name>maxActive</name>
> <value>10</value>
> </parameter>
> <parameter>
> <name>maxIdle</name>
> <value>2</value>
> </parameter>
> <parameter>
> <name>maxWait</name>
> <value>100</value>
> </parameter>
> <parameter>
> <name>testOnBorrow</name>
> <value>true</value>
> </parameter>
> <parameter>
> <name>validationQuery</name>
> <value>select 1</value>
> </parameter>
> </ResourceParams>[/CODE]
> Using these log4J settings to get the debugs :
> [CODE]log4j.category.com.ibatis = DEBUG, file
> log4j.category.org.apache = DEBUG, file
> log4j.category.org.springframework = DEBUG, file[/CODE] We see logging
> like this for a successful result, AS/400 present, all is well :
> [CODE]11:43:45,271
> uk.co.XXX.our.soap.OurService.getItem():359
=== message truncated ===
___________________________________________________________
Want ideas for reducing your carbon footprint? Visit Yahoo! For Good
http://uk.promotions.yahoo.com/forgood/environment.html
----------------------------------------------------------------------
The information in this email is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this email by
anyone else is unauthorised. If you are not the intended recipient,
any disclosure, copying, distribution, or any action taken or omitted
to be taken in reliance on it, is prohibited and may be unlawful.
TriSystems Ltd. cannot accept liability for statements made which are
clearly
the sender's own.
RE: Spring, Ibatis, and DBCP commons pooling
Posted by Tracey Annison <ta...@trisystems.co.uk>.
Heya
Hmmm, yes... But isn't that functionally the same as what we have here
in our context.xml :
<Resource name="jdbc/imacs"
auth="Container"
type="javax.sql.DataSource"
driverClassName="XXX"
url="XXX"
username="XXX"
password="XXX" />
<ResourceParams name="jdbc/imacs">
<parameter>
<name>factory</name>
<value>
org.apache.commons.dbcp.BasicDataSourceFactory
</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>10</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>2</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>100</value>
</parameter>
<parameter>
<name>testOnBorrow</name>
<value>true</value>
</parameter>
<parameter>
<name>validationQuery</name>
<value>select 1</value>
</parameter>
</ResourceParams>
Which is giving the bad results that we see...
Cheers
Tracey Annison
-----Original Message-----
From: charlie bird [mailto:zebthecat@yahoo.co.uk]
Sent: 13 September 2007 16:57
To: user-java@ibatis.apache.org
Subject: RE: Spring, Ibatis, and DBCP commons pooling
Don't know if this'll help but we always put the dbcp config in the
Apache application context xml and NOT in the app's Resouce config.
I think that the properties you're looking for are:
testOnBorrow
testOnReturn
testWhileIdle
validationQuery
See here for more info:
http://commons.apache.org/dbcp/configuration.html
Something like this maybe:
<Resource name="jdbc/creditSubscriptions"
auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30"
maxWait="10000" username="sa" password="password"
driverClassName="class"
removeAbandoned="true" removeAbandonedTimeout="60"
logAbandoned="true"
url="jdbc:somurl"
validationQuery="select 'hello'"
testOnBorrow="true"
testOnReturn="true"
testWhileIdle="true"
/>
--- Tracey Annison <ta...@trisystems.co.uk> wrote:
> But this is a setup for a SimpleDataSource, with Ibatis pooling, and
> not using the apache commons DBCP pooling at all, isn't it?
>
> You see, we had it set up like this, with settings that we thought
> were a hangover from before the apace commons poling was enabled, and
> we saw exactly the same problems... it seemed to us that having pool
> settings here, and in the context.xml for apache, was a mistake... ?
>
> Cheers
> Tracey Annison
>
>
>
> _____
>
> From: Meindert [mailto:meindert@pastelebusiness.com]
> Sent: 13 September 2007 15:06
> To: user-java@ibatis.apache.org
> Subject: RE: Spring, Ibatis, and DBCP commons pooling
>
>
>
> I would think you will need to ping the connection before you can use
> it.
>
>
>
> <transactionManager type="JDBC">
>
> <dataSource type="SIMPLE">
>
> <property value="${driver}"
> name="JDBC.Driver"/>
>
> <property value="${url}"
> name="JDBC.ConnectionURL"/>
>
> <property value="${username}"
> name="JDBC.Username"/>
>
> <property value="${password}"
> name="JDBC.Password"/>
>
> <property value="15"
> name="Pool.MaximumActiveConnections"/>
>
> <property value="15"
> name="Pool.MaximumIdleConnections"/>
>
> <property value="1000"
> name="Pool.MaximumWait"/>
>
> <property name="Pool.PingQuery" value="SELECT
> 1 FROM Company"/>
>
> <property name="Pool.PingEnabled" value="true"
> />
>
> <property name="Pool.PingConnectionsOlderThan"
> value="1000000" />
>
> </dataSource>
>
> </transactionManager>
>
>
>
> _____
>
> From: Tracey Annison
> [mailto:tannison@trisystems.co.uk]
> Sent: Thursday, September 13, 2007 3:58 PM
> To: user-java@ibatis.apache.org
> Subject: Spring, Ibatis, and DBCP commons pooling
>
>
>
> We are using Java with Spring to access AS/400 databases via Ibatis
> and the SQLMapClient, and pooling connections via the Apache Commons
> DBCP.
> We have a problem when the application remains running, but the AS/400
> connection is lost, due to a closed job or an IPL or whatever. After
> this happens, the first call on the database fails, though subsequent
> calls succeed.
>
> The odd thing is that it seems to be failing while trying to return
> the bad connection, presumably as part of some cleanup operation,
> which would hopefully be followed by an attempt to reconnect? It looks
> to me as though the Spring DataSourceUtils finds that the connection
> is dead, and tries to return it to the pool (presumably prior to
> obtaining a less dead one?) and clashes with the Tomcat DBCP that has
> already done so.
> But I cannot seem to find a way of seeing what is going on during this
> process, as I can't find out how to log these actions in any more
> detail.
>
> So I don't know how to correct this issue... can anyone help me?
> We are setting up an Ibatis DataSource in Spring like this :
> [CODE]<bean id="iseriesJdbcDataSource"
>
class="org.springframework.jndi.JndiObjectFactoryBean">
> <property name="jndiName">
> <value>java:comp/env/jdbc/host</value>
> </property>
> </bean>
> <bean id="sqlMapClient"
>
class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
> <property name="configLocation">
> <value>/WEB-INF/sql-map-config.xml</value>
> </property>
> <property name="useTransactionAwareDataSource">
> <value>true</value>
> </property>
> <property name="dataSource">
> <ref bean="iseriesJdbcDataSource"/>
> </property>
> </bean>
> <bean id="sqlMapClientTemplate"
>
class="org.springframework.orm.ibatis.SqlMapClientTemplate">
> <property name="sqlMapClient">
> <ref bean="sqlMapClient"/>
> </property>
> </bean>
> <bean id="ourDAO"
> class="uk.co.XXX.our.dao.OurIbatisDAO">
> <property name="sqlMapClientTemplate"> <ref
> bean="sqlMapClientTemplate"/> </property> </bean>[/CODE] Which uses
> the following settings in the Ibatis sql-map-client.xml :
> [CODE] <transactionManager type="JDBC"> <dataSource type="JNDI">
> <property name="DataSource"
> value="java:comp/env/jdbc/host" />
> </dataSource>
> </transactionManager>[/CODE]
> And also these Apache DBCP settings in context.xml :
> [CODE]<Resource name="jdbc/host"
> auth="Container"
> type="javax.sql.DataSource"
>
driverClassName="com.ibm.as400.access.AS400JDBCDriver"
>
url="jdbc:as400://999.999.999.999/HOSTLIB;prompt=false;naming=system;dat
> eformat=iso"
> username="HOSTUSER"
> password="HOSTPWD" />
> <ResourceParams name="jdbc/host">
> <parameter>
> <name>factory</name>
>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
> </parameter>
> <parameter>
> <name>maxActive</name>
> <value>10</value>
> </parameter>
> <parameter>
> <name>maxIdle</name>
> <value>2</value>
> </parameter>
> <parameter>
> <name>maxWait</name>
> <value>100</value>
> </parameter>
> <parameter>
> <name>testOnBorrow</name>
> <value>true</value>
> </parameter>
> <parameter>
> <name>validationQuery</name>
> <value>select 1</value>
> </parameter>
> </ResourceParams>[/CODE]
> Using these log4J settings to get the debugs :
> [CODE]log4j.category.com.ibatis = DEBUG, file
> log4j.category.org.apache = DEBUG, file
> log4j.category.org.springframework = DEBUG, file[/CODE] We see logging
> like this for a successful result, AS/400 present, all is well :
> [CODE]11:43:45,271
> uk.co.XXX.our.soap.OurService.getItem():359
=== message truncated ===
___________________________________________________________
Want ideas for reducing your carbon footprint? Visit Yahoo! For Good
http://uk.promotions.yahoo.com/forgood/environment.html
----------------------------------------------------------------------
The information in this email is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this email by
anyone else is unauthorised. If you are not the intended recipient,
any disclosure, copying, distribution, or any action taken or omitted
to be taken in reliance on it, is prohibited and may be unlawful.
TriSystems Ltd. cannot accept liability for statements made which are clearly
the sender's own.
RE: Spring, Ibatis, and DBCP commons pooling
Posted by charlie bird <ze...@yahoo.co.uk>.
Don't know if this'll help but we always put the dbcp
config in the Apache application context xml and NOT
in the app's Resouce config.
I think that the properties you're looking for are:
testOnBorrow
testOnReturn
testWhileIdle
validationQuery
See here for more info:
http://commons.apache.org/dbcp/configuration.html
Something like this maybe:
<Resource name="jdbc/creditSubscriptions"
auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30"
maxWait="10000" username="sa" password="password"
driverClassName="class"
removeAbandoned="true" removeAbandonedTimeout="60"
logAbandoned="true"
url="jdbc:somurl"
validationQuery="select 'hello'"
testOnBorrow="true"
testOnReturn="true"
testWhileIdle="true"
/>
--- Tracey Annison <ta...@trisystems.co.uk> wrote:
> But this is a setup for a SimpleDataSource, with
> Ibatis pooling, and not
> using the apache commons DBCP pooling at all, isn't
> it?
>
> You see, we had it set up like this, with settings
> that we thought were
> a hangover from before the apace commons poling was
> enabled, and we saw
> exactly the same problems... it seemed to us that
> having pool settings
> here, and in the context.xml for apache, was a
> mistake... ?
>
> Cheers
> Tracey Annison
>
>
>
> _____
>
> From: Meindert [mailto:meindert@pastelebusiness.com]
> Sent: 13 September 2007 15:06
> To: user-java@ibatis.apache.org
> Subject: RE: Spring, Ibatis, and DBCP commons
> pooling
>
>
>
> I would think you will need to ping the connection
> before you can use
> it.
>
>
>
> <transactionManager type="JDBC">
>
> <dataSource type="SIMPLE">
>
> <property value="${driver}"
> name="JDBC.Driver"/>
>
> <property value="${url}"
> name="JDBC.ConnectionURL"/>
>
> <property value="${username}"
> name="JDBC.Username"/>
>
> <property value="${password}"
> name="JDBC.Password"/>
>
> <property value="15"
> name="Pool.MaximumActiveConnections"/>
>
> <property value="15"
> name="Pool.MaximumIdleConnections"/>
>
> <property value="1000"
> name="Pool.MaximumWait"/>
>
> <property name="Pool.PingQuery" value="SELECT
> 1 FROM Company"/>
>
> <property name="Pool.PingEnabled" value="true"
> />
>
> <property name="Pool.PingConnectionsOlderThan"
> value="1000000" />
>
> </dataSource>
>
> </transactionManager>
>
>
>
> _____
>
> From: Tracey Annison
> [mailto:tannison@trisystems.co.uk]
> Sent: Thursday, September 13, 2007 3:58 PM
> To: user-java@ibatis.apache.org
> Subject: Spring, Ibatis, and DBCP commons pooling
>
>
>
> We are using Java with Spring to access AS/400
> databases via Ibatis and
> the SQLMapClient, and pooling connections via the
> Apache Commons DBCP.
> We have a problem when the application remains
> running, but the AS/400
> connection is lost, due to a closed job or an IPL or
> whatever. After
> this happens, the first call on the database fails,
> though subsequent
> calls succeed.
>
> The odd thing is that it seems to be failing while
> trying to return the
> bad connection, presumably as part of some cleanup
> operation, which
> would hopefully be followed by an attempt to
> reconnect? It looks to me
> as though the Spring DataSourceUtils finds that the
> connection is dead,
> and tries to return it to the pool (presumably prior
> to obtaining a less
> dead one?) and clashes with the Tomcat DBCP that has
> already done so.
> But I cannot seem to find a way of seeing what is
> going on during this
> process, as I can't find out how to log these
> actions in any more
> detail.
>
> So I don't know how to correct this issue... can
> anyone help me?
> We are setting up an Ibatis DataSource in Spring
> like this :
> [CODE]<bean id="iseriesJdbcDataSource"
>
class="org.springframework.jndi.JndiObjectFactoryBean">
> <property name="jndiName">
> <value>java:comp/env/jdbc/host</value>
> </property>
> </bean>
> <bean id="sqlMapClient"
>
class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
> <property name="configLocation">
> <value>/WEB-INF/sql-map-config.xml</value>
> </property>
> <property name="useTransactionAwareDataSource">
> <value>true</value>
> </property>
> <property name="dataSource">
> <ref bean="iseriesJdbcDataSource"/>
> </property>
> </bean>
> <bean id="sqlMapClientTemplate"
>
class="org.springframework.orm.ibatis.SqlMapClientTemplate">
> <property name="sqlMapClient">
> <ref bean="sqlMapClient"/>
> </property>
> </bean>
> <bean id="ourDAO"
> class="uk.co.XXX.our.dao.OurIbatisDAO">
> <property name="sqlMapClientTemplate">
> <ref bean="sqlMapClientTemplate"/>
> </property>
> </bean>[/CODE]
> Which uses the following settings in the Ibatis
> sql-map-client.xml :
> [CODE] <transactionManager type="JDBC">
> <dataSource type="JNDI">
> <property name="DataSource"
> value="java:comp/env/jdbc/host" />
> </dataSource>
> </transactionManager>[/CODE]
> And also these Apache DBCP settings in context.xml :
> [CODE]<Resource name="jdbc/host"
> auth="Container"
> type="javax.sql.DataSource"
>
driverClassName="com.ibm.as400.access.AS400JDBCDriver"
>
url="jdbc:as400://999.999.999.999/HOSTLIB;prompt=false;naming=system;dat
> eformat=iso"
> username="HOSTUSER"
> password="HOSTPWD" />
> <ResourceParams name="jdbc/host">
> <parameter>
> <name>factory</name>
>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
> </parameter>
> <parameter>
> <name>maxActive</name>
> <value>10</value>
> </parameter>
> <parameter>
> <name>maxIdle</name>
> <value>2</value>
> </parameter>
> <parameter>
> <name>maxWait</name>
> <value>100</value>
> </parameter>
> <parameter>
> <name>testOnBorrow</name>
> <value>true</value>
> </parameter>
> <parameter>
> <name>validationQuery</name>
> <value>select 1</value>
> </parameter>
> </ResourceParams>[/CODE]
> Using these log4J settings to get the debugs :
> [CODE]log4j.category.com.ibatis = DEBUG, file
> log4j.category.org.apache = DEBUG, file
> log4j.category.org.springframework = DEBUG,
> file[/CODE]
> We see logging like this for a successful result,
> AS/400 present, all is
> well :
> [CODE]11:43:45,271
> uk.co.XXX.our.soap.OurService.getItem():359
=== message truncated ===
___________________________________________________________
Want ideas for reducing your carbon footprint? Visit Yahoo! For Good http://uk.promotions.yahoo.com/forgood/environment.html
RE: Spring, Ibatis, and DBCP commons pooling
Posted by Tracey Annison <ta...@trisystems.co.uk>.
But this is a setup for a SimpleDataSource, with Ibatis pooling, and not
using the apache commons DBCP pooling at all, isn't it?
You see, we had it set up like this, with settings that we thought were
a hangover from before the apace commons poling was enabled, and we saw
exactly the same problems... it seemed to us that having pool settings
here, and in the context.xml for apache, was a mistake... ?
Cheers
Tracey Annison
_____
From: Meindert [mailto:meindert@pastelebusiness.com]
Sent: 13 September 2007 15:06
To: user-java@ibatis.apache.org
Subject: RE: Spring, Ibatis, and DBCP commons pooling
I would think you will need to ping the connection before you can use
it.
<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property value="${driver}" name="JDBC.Driver"/>
<property value="${url}" name="JDBC.ConnectionURL"/>
<property value="${username}" name="JDBC.Username"/>
<property value="${password}" name="JDBC.Password"/>
<property value="15" name="Pool.MaximumActiveConnections"/>
<property value="15" name="Pool.MaximumIdleConnections"/>
<property value="1000" name="Pool.MaximumWait"/>
<property name="Pool.PingQuery" value="SELECT 1 FROM Company"/>
<property name="Pool.PingEnabled" value="true" />
<property name="Pool.PingConnectionsOlderThan" value="1000000" />
</dataSource>
</transactionManager>
_____
From: Tracey Annison [mailto:tannison@trisystems.co.uk]
Sent: Thursday, September 13, 2007 3:58 PM
To: user-java@ibatis.apache.org
Subject: Spring, Ibatis, and DBCP commons pooling
We are using Java with Spring to access AS/400 databases via Ibatis and
the SQLMapClient, and pooling connections via the Apache Commons DBCP.
We have a problem when the application remains running, but the AS/400
connection is lost, due to a closed job or an IPL or whatever. After
this happens, the first call on the database fails, though subsequent
calls succeed.
The odd thing is that it seems to be failing while trying to return the
bad connection, presumably as part of some cleanup operation, which
would hopefully be followed by an attempt to reconnect? It looks to me
as though the Spring DataSourceUtils finds that the connection is dead,
and tries to return it to the pool (presumably prior to obtaining a less
dead one?) and clashes with the Tomcat DBCP that has already done so.
But I cannot seem to find a way of seeing what is going on during this
process, as I can't find out how to log these actions in any more
detail.
So I don't know how to correct this issue... can anyone help me?
We are setting up an Ibatis DataSource in Spring like this :
[CODE]<bean id="iseriesJdbcDataSource"
class="org.springframework.jndi.JndiObjectFactoryBean">
<property name="jndiName">
<value>java:comp/env/jdbc/host</value>
</property>
</bean>
<bean id="sqlMapClient"
class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="configLocation">
<value>/WEB-INF/sql-map-config.xml</value>
</property>
<property name="useTransactionAwareDataSource">
<value>true</value>
</property>
<property name="dataSource">
<ref bean="iseriesJdbcDataSource"/>
</property>
</bean>
<bean id="sqlMapClientTemplate"
class="org.springframework.orm.ibatis.SqlMapClientTemplate">
<property name="sqlMapClient">
<ref bean="sqlMapClient"/>
</property>
</bean>
<bean id="ourDAO" class="uk.co.XXX.our.dao.OurIbatisDAO">
<property name="sqlMapClientTemplate">
<ref bean="sqlMapClientTemplate"/>
</property>
</bean>[/CODE]
Which uses the following settings in the Ibatis sql-map-client.xml :
[CODE] <transactionManager type="JDBC">
<dataSource type="JNDI">
<property name="DataSource" value="java:comp/env/jdbc/host" />
</dataSource>
</transactionManager>[/CODE]
And also these Apache DBCP settings in context.xml :
[CODE]<Resource name="jdbc/host"
auth="Container"
type="javax.sql.DataSource"
driverClassName="com.ibm.as400.access.AS400JDBCDriver"
url="jdbc:as400://999.999.999.999/HOSTLIB;prompt=false;naming=system;dat
eformat=iso"
username="HOSTUSER"
password="HOSTPWD" />
<ResourceParams name="jdbc/host">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>10</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>2</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>100</value>
</parameter>
<parameter>
<name>testOnBorrow</name>
<value>true</value>
</parameter>
<parameter>
<name>validationQuery</name>
<value>select 1</value>
</parameter>
</ResourceParams>[/CODE]
Using these log4J settings to get the debugs :
[CODE]log4j.category.com.ibatis = DEBUG, file
log4j.category.org.apache = DEBUG, file
log4j.category.org.springframework = DEBUG, file[/CODE]
We see logging like this for a successful result, AS/400 present, all is
well :
[CODE]11:43:45,271 uk.co.XXX.our.soap.OurService.getItem():359 - Using
sqlMapClient >com.ibatis.sqlmap.engine.impl.SqlMapClientImpl@1705cd8<
11:43:45,271 uk.co.XXX.our.soap.OurService.getItem():361 -
sqlMapClient.getCurrentConnection() is >null<
11:43:45,271 uk.co.XXX.our.soap.OurService.getItem():363 -
sqlMapClient.getDataSource() is
>org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy@1df
9efc<
11:43:45,287
org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection():11
1 - Fetching JDBC Connection from DataSource
11:43:52,803
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{conn-100000} Connection
11:43:53,365
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{pstm-100001} PreparedStatement: select count(*) from etc, etc, etc....
11:43:53,365
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{pstm-100001} Parameters: [etc, etc]
11:43:53,365
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{pstm-100001} Types: [etc, etc]
11:43:54,850
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{rset-100002} ResultSet
11:43:54,881
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{rset-100002} Header: [00001]
11:43:54,881
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{rset-100002} Result: [1]
11:43:54,975
org.springframework.jdbc.datasource.DataSourceUtils.doReleaseConnection(
):285 - Returning JDBC Connection to DataSource
[/CODE]
And logging like this for an unsuccessful result, when the AS/400 job
has been closed in the meantime :
[CODE]11:51:05,537 uk.co.XXX.our.soap.OurService.getItem():359 - Using
sqlMapClient >com.ibatis.sqlmap.engine.impl.SqlMapClientImpl@1705cd8<
11:51:05,537 uk.co.XXX.our.soap.OurService.getItem():361 -
sqlMapClient.getCurrentConnection() is >null<
11:51:05,537 uk.co.XXX.our.soap.OurService.getItem():363 -
sqlMapClient.getDataSource() is
>org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy@1df
9efc<
11:51:05,537
org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection():11
1 - Fetching JDBC Connection from DataSource
11:51:05,537
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{conn-100064} Connection
11:51:05,553
org.springframework.jdbc.datasource.DataSourceUtils.doReleaseConnection(
):285 - Returning JDBC Connection to DataSource
11:51:05,615 uk.co.XXX.our.dao.OurIbatisDAO.isItemExtant():538 - Ibatis
DAO Exception
java.sql.SQLException: Already closed.
at
org.apache.tomcat.dbcp.dbcp.PoolableConnection.close(PoolableConnection.
java:77)
at
org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper
.close(PoolingDataSource.java:180)
at
org.springframework.jdbc.datasource.DataSourceUtils.doReleaseConnection(
DataSourceUtils.java:286)
at
org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$Tran
sactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.jav
a:161)
at $Proxy52.close(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at
com.ibatis.common.jdbc.logging.ConnectionLogProxy.invoke(ConnectionLogPr
oxy.java:62)
at $Proxy50.close(Unknown Source)
at
com.ibatis.sqlmap.engine.transaction.external.ExternalTransaction.close(
ExternalTransaction.java:82)
at
com.ibatis.sqlmap.engine.transaction.TransactionManager.end(TransactionM
anager.java:110)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.endTransaction(SqlM
apExecutorDelegate.java:776)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.endTransaction(SqlMapSes
sionImpl.java:137)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.endTransaction(SqlMapClie
ntImpl.java:115)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.autoEndTransaction(
SqlMapExecutorDelegate.java:860)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlM
apExecutorDelegate.java:568)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlM
apExecutorDelegate.java:536)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSes
sionImpl.java:93)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClie
ntImpl.java:70)
at uk.co.XXX.our.dao.OurIbatisDAO.isItemExtant(ItemIbatisDAO.java:528)
at uk.co.XXX.our.soap.OurService.getItem(OurService.java:367)
at uk.co.XXX.our.soap.OurService.getItem(OurService.java:261)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at
org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java
:397)
at etc, etc, etc....
at java.lang.Thread.run(Unknown Source)[/CODE]
Cheers
Tracey Annison
----------------------------------------------------------------------
The information in this email is confidential and may be legally
privileged.
It is intended solely for the addressee. Access to this email by
anyone else is unauthorised. If you are not the intended recipient,
any disclosure, copying, distribution, or any action taken or omitted
to be taken in reliance on it, is prohibited and may be unlawful.
TriSystems Ltd. cannot accept liability for statements made which are
clearly
the sender's own.
----------------------------------------------------------------------
The information in this email is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this email by
anyone else is unauthorised. If you are not the intended recipient,
any disclosure, copying, distribution, or any action taken or omitted
to be taken in reliance on it, is prohibited and may be unlawful.
TriSystems Ltd. cannot accept liability for statements made which are clearly
the sender's own.
RE: Spring, Ibatis, and DBCP commons pooling
Posted by Meindert <me...@pastelebusiness.com>.
I would think you will need to ping the connection before you can use it.
<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property value="${driver}" name="JDBC.Driver"/>
<property value="${url}" name="JDBC.ConnectionURL"/>
<property value="${username}" name="JDBC.Username"/>
<property value="${password}" name="JDBC.Password"/>
<property value="15" name="Pool.MaximumActiveConnections"/>
<property value="15" name="Pool.MaximumIdleConnections"/>
<property value="1000" name="Pool.MaximumWait"/>
<property name="Pool.PingQuery" value="SELECT 1 FROM Company"/>
<property name="Pool.PingEnabled" value="true" />
<property name="Pool.PingConnectionsOlderThan" value="1000000" />
</dataSource>
</transactionManager>
_____
From: Tracey Annison [mailto:tannison@trisystems.co.uk]
Sent: Thursday, September 13, 2007 3:58 PM
To: user-java@ibatis.apache.org
Subject: Spring, Ibatis, and DBCP commons pooling
We are using Java with Spring to access AS/400 databases via Ibatis and the
SQLMapClient, and pooling connections via the Apache Commons DBCP. We have a
problem when the application remains running, but the AS/400 connection is
lost, due to a closed job or an IPL or whatever. After this happens, the
first call on the database fails, though subsequent calls succeed.
The odd thing is that it seems to be failing while trying to return the bad
connection, presumably as part of some cleanup operation, which would
hopefully be followed by an attempt to reconnect? It looks to me as though
the Spring DataSourceUtils finds that the connection is dead, and tries to
return it to the pool (presumably prior to obtaining a less dead one?) and
clashes with the Tomcat DBCP that has already done so. But I cannot seem to
find a way of seeing what is going on during this process, as I can't find
out how to log these actions in any more detail.
So I don't know how to correct this issue... can anyone help me?
We are setting up an Ibatis DataSource in Spring like this :
[CODE]<bean id="iseriesJdbcDataSource"
class="org.springframework.jndi.JndiObjectFactoryBean">
<property name="jndiName">
<value>java:comp/env/jdbc/host</value>
</property>
</bean>
<bean id="sqlMapClient"
class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="configLocation">
<value>/WEB-INF/sql-map-config.xml</value>
</property>
<property name="useTransactionAwareDataSource">
<value>true</value>
</property>
<property name="dataSource">
<ref bean="iseriesJdbcDataSource"/>
</property>
</bean>
<bean id="sqlMapClientTemplate"
class="org.springframework.orm.ibatis.SqlMapClientTemplate">
<property name="sqlMapClient">
<ref bean="sqlMapClient"/>
</property>
</bean>
<bean id="ourDAO" class="uk.co.XXX.our.dao.OurIbatisDAO">
<property name="sqlMapClientTemplate">
<ref bean="sqlMapClientTemplate"/>
</property>
</bean>[/CODE]
Which uses the following settings in the Ibatis sql-map-client.xml :
[CODE] <transactionManager type="JDBC">
<dataSource type="JNDI">
<property name="DataSource" value="java:comp/env/jdbc/host" />
</dataSource>
</transactionManager>[/CODE]
And also these Apache DBCP settings in context.xml :
[CODE]<Resource name="jdbc/host"
auth="Container"
type="javax.sql.DataSource"
driverClassName="com.ibm.as400.access.AS400JDBCDriver"
url="jdbc:as400://999.999.999.999/HOSTLIB;prompt=false;naming=system;datefor
mat=iso"
username="HOSTUSER"
password="HOSTPWD" />
<ResourceParams name="jdbc/host">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>10</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>2</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>100</value>
</parameter>
<parameter>
<name>testOnBorrow</name>
<value>true</value>
</parameter>
<parameter>
<name>validationQuery</name>
<value>select 1</value>
</parameter>
</ResourceParams>[/CODE]
Using these log4J settings to get the debugs :
[CODE]log4j.category.com.ibatis = DEBUG, file
log4j.category.org.apache = DEBUG, file
log4j.category.org.springframework = DEBUG, file[/CODE]
We see logging like this for a successful result, AS/400 present, all is
well :
[CODE]11:43:45,271 uk.co.XXX.our.soap.OurService.getItem():359 - Using
sqlMapClient >com.ibatis.sqlmap.engine.impl.SqlMapClientImpl@1705cd8<
11:43:45,271 uk.co.XXX.our.soap.OurService.getItem():361 -
sqlMapClient.getCurrentConnection() is >null<
11:43:45,271 uk.co.XXX.our.soap.OurService.getItem():363 -
sqlMapClient.getDataSource() is
>org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy@1df9efc
<
11:43:45,287
org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection():111 -
Fetching JDBC Connection from DataSource
11:43:52,803
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{conn-100000} Connection
11:43:53,365
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{pstm-100001} PreparedStatement: select count(*) from etc, etc, etc....
11:43:53,365
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{pstm-100001} Parameters: [etc, etc]
11:43:53,365
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{pstm-100001} Types: [etc, etc]
11:43:54,850
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{rset-100002} ResultSet
11:43:54,881
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{rset-100002} Header: [00001]
11:43:54,881
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{rset-100002} Result: [1]
11:43:54,975
org.springframework.jdbc.datasource.DataSourceUtils.doReleaseConnection():28
5 - Returning JDBC Connection to DataSource
[/CODE]
And logging like this for an unsuccessful result, when the AS/400 job has
been closed in the meantime :
[CODE]11:51:05,537 uk.co.XXX.our.soap.OurService.getItem():359 - Using
sqlMapClient >com.ibatis.sqlmap.engine.impl.SqlMapClientImpl@1705cd8<
11:51:05,537 uk.co.XXX.our.soap.OurService.getItem():361 -
sqlMapClient.getCurrentConnection() is >null<
11:51:05,537 uk.co.XXX.our.soap.OurService.getItem():363 -
sqlMapClient.getDataSource() is
>org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy@1df9efc
<
11:51:05,537
org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection():111 -
Fetching JDBC Connection from DataSource
11:51:05,537
com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl.debug():23 -
{conn-100064} Connection
11:51:05,553
org.springframework.jdbc.datasource.DataSourceUtils.doReleaseConnection():28
5 - Returning JDBC Connection to DataSource
11:51:05,615 uk.co.XXX.our.dao.OurIbatisDAO.isItemExtant():538 - Ibatis DAO
Exception
java.sql.SQLException: Already closed.
at
org.apache.tomcat.dbcp.dbcp.PoolableConnection.close(PoolableConnection.java
:77)
at
org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.clo
se(PoolingDataSource.java:180)
at
org.springframework.jdbc.datasource.DataSourceUtils.doReleaseConnection(Data
SourceUtils.java:286)
at
org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$Transact
ionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:161)
at $Proxy52.close(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at
com.ibatis.common.jdbc.logging.ConnectionLogProxy.invoke(ConnectionLogProxy.
java:62)
at $Proxy50.close(Unknown Source)
at
com.ibatis.sqlmap.engine.transaction.external.ExternalTransaction.close(Exte
rnalTransaction.java:82)
at
com.ibatis.sqlmap.engine.transaction.TransactionManager.end(TransactionManag
er.java:110)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.endTransaction(SqlMapEx
ecutorDelegate.java:776)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.endTransaction(SqlMapSession
Impl.java:137)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.endTransaction(SqlMapClientIm
pl.java:115)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.autoEndTransaction(SqlM
apExecutorDelegate.java:860)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapEx
ecutorDelegate.java:568)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapEx
ecutorDelegate.java:536)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSession
Impl.java:93)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientIm
pl.java:70)
at uk.co.XXX.our.dao.OurIbatisDAO.isItemExtant(ItemIbatisDAO.java:528)
at uk.co.XXX.our.soap.OurService.getItem(OurService.java:367)
at uk.co.XXX.our.soap.OurService.getItem(OurService.java:261)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at
org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397
)
at etc, etc, etc....
at java.lang.Thread.run(Unknown Source)[/CODE]
Cheers
Tracey Annison
----------------------------------------------------------------------
The information in this email is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this email by
anyone else is unauthorised. If you are not the intended recipient,
any disclosure, copying, distribution, or any action taken or omitted
to be taken in reliance on it, is prohibited and may be unlawful.
TriSystems Ltd. cannot accept liability for statements made which are
clearly
the sender's own.