<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<body link="#355491" alink="#4262a1" vlink="#355491" style="background: #e2e2e2; margin: 0; padding: 20px;">
<div>
<table cellpadding="0" bgcolor="#FFFFFF" border="0" cellspacing="0" style="border: 1px solid #dadada; margin-bottom: 30px; width: 100%; -moz-border-radius: 6px; -webkit-border-radius: 6px;">
<tbody>
<tr>
<td>
<table border="0" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF" style="border: solid 2px #ccc; background: #dadada; width: 100%; -moz-border-radius: 6px; -webkit-border-radius: 6px;">
<tbody>
<tr>
<td bgcolor="#000000" valign="middle" height="58px" style="border-bottom: 1px solid #ccc; padding: 20px; -moz-border-radius-topleft: 3px; -moz-border-radius-topright: 3px; -webkit-border-top-right-radius: 5px; -webkit-border-top-left-radius: 5px;">
<h1 style="color: #333333; font: bold 22px Arial, Helvetica, sans-serif; margin: 0; display: block !important;">
<!-- To have a header image/logo replace the name below with your img tag -->
<!-- Email clients will render the images when the message is read so any image -->
<!-- must be made available on a public server, so that all recipients can load the image. -->
<a href="http://community.jboss.org/index.jspa" style="text-decoration: none; color: #E1E1E1">JBoss Community</a></h1>
</td>
</tr>
<tr>
<td bgcolor="#FFFFFF" style="font: normal 12px Arial, Helvetica, sans-serif; color:#333333; padding: 20px; -moz-border-radius-bottomleft: 4px; -moz-border-radius-bottomright: 4px; -webkit-border-bottom-right-radius: 5px; -webkit-border-bottom-left-radius: 5px;"><h3 style="margin: 10px 0 5px; font-size: 17px; font-weight: normal;">
Configure check-valid-connection-sql or valid-connection-checker-class-name
</h3>
<span style="margin-bottom: 10px;">
created by <a href="http://community.jboss.org/people/jmoratillap">J M</a> in <i>Datasource Configuration</i> - <a href="http://community.jboss.org/message/605627#605627">View the full discussion</a>
</span>
<hr style="margin: 20px 0; border: none; background-color: #dadada; height: 1px;">
<div class="jive-rendered-content"><p><span style="font-family: arial,helvetica,sans-serif; font-size: 12pt;">Hi all,</span></p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p><span style="font-family: arial,helvetica,sans-serif; font-size: 12pt;">We are having problems in production enviroments with datasource connections. We have a JBOSS 4.2.2.GA with a datasource configured to create connections against an Oracle Database 10g Enterprise Edition Release 10.2.0.1.0. It seems that some connections returned by the pool are closed because we get the following exception when some thread try to use that connections:</span></p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p><span style="font-family: courier new,courier; font-size: 12pt;">2011-05-15 01:32:01,455 ERROR [STDERR] java.sql.SQLException: Conexi?n cerrada</span></p><p><span style="font-family: courier new,courier; font-size: 12pt;">2011-05-15 01:32:01,455 ERROR [STDERR]     at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)</span></p><p><span style="font-family: courier new,courier; font-size: 12pt;">2011-05-15 01:32:01,455 ERROR [STDERR]     at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)</span></p><p><span style="font-family: courier new,courier; font-size: 12pt;">2011-05-15 01:32:01,455 ERROR [STDERR]     at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)</span></p><p><span style="font-family: courier new,courier; font-size: 12pt;">2011-05-15 01:32:01,455 ERROR [STDERR]     at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:868)</span></p><p><span style="font-family: courier new,courier; font-size: 12pt;">2011-05-15 01:32:01,455 ERROR [STDERR]     at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:349)</span></p><p><span style="font-family: courier new,courier; font-size: 12pt;">2011-05-15 01:32:01,456 ERROR [STDERR]     at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:344)</span></p><p><span style="font-family: courier new,courier; font-size: 12pt;">2011-05-15 01:32:01,456 ERROR [STDERR]     at org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:201)</span></p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p><span style="font-family: arial,helvetica,sans-serif; font-size: 12pt;">Other connections returned by de DB connection pool more ore less at the same time seems to work properly. For some reason (firewalls, proxyx may be) this connections are not in use.</span></p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p><span style="font-family: arial,helvetica,sans-serif; font-size: 12pt;">We think that including a configuration in the datasource which forces the pool to test the connections before being borrowed would solve this problem, because the pool would returned util connections prepared to be used. We have found two <span class="short_text" id="result_box" lang="en"><span class="hps" title="Haz clic para obtener otras posibles traducciones">possible solutions after reading 5.3 Configuring JDBC DataSources</span></span><a class="jive-link-anchor-small"> </a><span class="short_text" id="result_box" lang="en"><span class="hps" title="Haz clic para obtener otras posibles traducciones"><a class="jive-link-external-small" href="http://docs.jboss.org/jbossas/docs/Server_Configuration_Guide/4/html/Connectors_on_JBoss-Configuring_JDBC_DataSources.html">http://docs.jboss.org/jbossas/docs/Server_Configuration_Guide/4/html/Connectors_on_JBoss-Configuring_JDBC_DataSources.html :</a></span></span></span></p><p><br/><span style="font-family: arial,helvetica,sans-serif; font-size: 12pt;">We have some questions about how these configurations works:</span></p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><ul><li><span style="font-family: arial,helvetica,sans-serif; font-size: 12pt;"><strong>check-valid-connection-sql</strong></span></li></ul><p><span style="font-family: arial,helvetica,sans-serif; font-size: 12pt;">          Before the pool  returned one connection, it is checked, executing the sql sentence specified, such as <span style="font-family: courier new,courier;">select * from dual</span></span></p><p><span style="font-family: arial,helvetica,sans-serif; font-size: 12pt;">        If the SQL sentence is executed correctly the connection is returned because the test was OK. Am I right?<br/></span></p><p><span style="font-family: arial,helvetica,sans-serif; font-size: 12pt;">        If it was not posible to executed the statement, what is the <span class="short_text" id="result_box" lang="en"><span class="hps" title="Haz clic para obtener otras posibles traducciones">behavior? Would the datasource try with another connection an consider that the previous connection is not in use and would be removed from pool? Does exists in JBOSS datasource configuration any tag which specifies a timeout to decide that a connection is closed in case that there are any response to the SQL test statement?</span></span></span></p><p><span style="font-family: arial,helvetica,sans-serif; font-size: 12pt;">     Including a check-valid-connections, does ensure that every connection returned by de pool will be in used in terms of connectivity?</span></p><p><span style="font-family: arial,helvetica,sans-serif; font-size: 12pt;">     If all the connections of the pool were invalid, Will the JBOSS datasource test all the connections and when find that all are invalid would create a new pool with new connections?</span></p><p><span style="font-family: arial,helvetica,sans-serif; font-size: 12pt;">     As I understand in this kind of solution a "user" of pool connections would get an exception after pool has test all available connections and all were invalid, Am I right?</span></p><p><span style="font-family: arial,helvetica,sans-serif; font-size: 12pt;">     What would be the behaviour of pool in terms of throwing exceptions when request a DB connection?<br/></span></p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><ul><li><span style="font-family: arial,helvetica,sans-serif; font-size: 12pt;"><strong>valid-connection-checker-class-name</strong></span></li></ul><p><span style="font-size: 12pt;">         What are the differences between this solution and the one above beyond the procedure to decide if a connection is valid?   I mean, if I implement the org.jboss.resource.adapter.jdbc.ValidConnectionChecker interface and the method <code>SQLException isValidConnection(Connection e) d</code></span><span style="font-size: 12pt; font-family: arial,helvetica,sans-serif;"><code>oesn't do anything more than executed "select * from dual" statement we would be talking about the same solution?</code></span></p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p><span style="font-size: 12pt; font-family: arial,helvetica,sans-serif;">     <br/></span></p><p><span style="font-size: 12pt;">valid-connection-checker-class-name provides more possibilities to test a connection, right? but the rest of behaviour of datasource is equal in the two solutions, the unique difference is the method to test if a connection is OK, Am right?</span></p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p><span style="font-size: 12pt;">Many thanks, best regards</span></p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p><span style="font-size: 12pt;">Juan Moratilla Peón</span></p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p><span style="font-size: 12pt;">HP</span></p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p><span style="font-family: arial,helvetica,sans-serif; font-size: 12pt;"><br/></span></p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"><span style="font-family: arial,helvetica,sans-serif;"> </span> </p></div>
<div style="background-color: #f4f4f4; padding: 10px; margin-top: 20px;">
<p style="margin: 0;">Reply to this message by <a href="http://community.jboss.org/message/605627#605627">going to Community</a></p>
<p style="margin: 0;">Start a new discussion in Datasource Configuration at <a href="http://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2077">Community</a></p>
</div></td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>