All of out JDBC is done in a try-catch-finally block where the prepared
statement and connection are closed in the finally block.
Transactions are managed using CMT, and CMP is not used as stateless
session EJBs make more sense in the design. Autocommit is off, allowing
the container to handle committing when needed. Our transactions have
been managed correctly up to now, so as far as I can see there is no
problem with this.
I have been using the classes12.jar and not classes12.zip, but there
shouldn't be any difference between them is there?
A typical database access scenario in our system is like this:
getJDBCConnection and returnJDBCConnection have been provided
previously.
try {
jdbcConn = Initializer.getJDBCConnection( providerId );
String sqlStr =
"SELECT STATE FROM CUSTOMERACCOUNTS WHERE MSISDN = ?" +
" AND ( STATE='P' OR STATE='N' )";
prepStmt = jdbcConn.prepareStatement( sqlStr );
prepStmt.setString( 1, msisdn );
ResultSet rs = prepStmt.executeQuery();
if ( rs.next() ) {
// for prepaid, subscriptionType is P, for postpaid
subscriptionType is N
subscriptionType = ( "P".equals( rs.getString( 1 ) ) ? "P" : "N"
); // current state for msisdn
validUser = true;
cat.debug("isValidUserForRegistration " + msisdn + " exists in
cust.accounts" );
}
else {
response.setInvalidUserCondition();
cat.debug("registrtaion for " + msisdn + " is not ok ");
}
return validUser;
}
catch ( InitializerException ie ) {
// Initializer failed somehow
response.setSystemExceptionCondition();
cat.fatal( "Initializer failed " + ie.getMessage(), ie );
return validUser;
}
catch ( SQLException se ) {
// select failed somehow
response.setSystemExceptionCondition();
se.printStackTrace();
cat.warn( "SQLException " + se.getMessage(), se );
return validUser;
}
catch (Exception e) {
e.printStackTrace();
cat.warn( "Exception " + e.getMessage(), e );
return validUser;
}
finally {
// Close PreparedStatement & Return JDBC connection
try {
if ( prepStmt != null ) prepStmt.close();
}
catch (SQLException sqle) {
// PreparedStatement closed already - do
nothing.
}
try {
if ( jdbcConn != null ) Initializer.returnJDBCConnection(
jdbcConn );
}
catch ( InitializerException ie ) {
// Unable to return connection. To bad, but at
least we tried.
}
}
Hope this clarifies some issues.
Ian
-----Oprindelig meddelelse-----
Fra: The elephantwalker [mailto:[EMAIL PROTECTED]]
Sendt: 11. september 2001 10:09
Til: Orion-Interest
Emne: RE: OC4J datasource / server crash problem
You need a finally at the end of the try block's to make sure your
connections close. What could be happening is your connections are not
being
closed from time to time. Most Oracle ora.ini files are set up for only
30
or so open connections (this is usually ok), but should never be more
than
300 or so for a typical installation. Talk to your dba about this.
Without the finally block, if anything goes south for the winter, the
connection is just left open. The problem could also be in your jdbc try
blocks. You will also need finally around these...for the same reason.
Also, your autocommit needs to be triggered in the actual jdbc try block
(you didn't show your dao code)...with autocommit off, transactions
won't
work at all unless you manage everything yourself.
Make sure you are using the latest 9i version of classes12.zip in
home/lib
directory.
Why on earth aren't you using cmp?
Regards,
the elephantwalker
.ps Your datasource is fine.
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Ian S. Hagen
Sent: Tuesday, September 11, 2001 12:21 AM
To: Orion-Interest
Subject: OC4J datasource / server crash problem
Hi,
I am currently part of a project developing a servlet/stateless EJB
solution to be run on the Orion platform. We are experiencing some
problems using our datasource connection to our Oracle 9i database. The
server will on occasion leak connections, which eventually results in
both the Orion server and the database crashing.
I include our data-sources.xml file to indicate how things are set up.
We currently only use the ejb-location for database access from our EJBs
and servlets.
<?xml version="1.0"?>
<!DOCTYPE data-sources PUBLIC "Orion data-sources"
"http://xmlns.oracle.com/ias/dtds/data-sources.dtd";>
<data-sources>
<!-- Setting up the data source for the zellsoft provider -->
<data-source
class="com.evermind.sql.DriverManagerDataSource"
name="zellsoft"
location="jdbc/zellsoft"
xa-location="jdbc/xa/zellsoft"
ejb-location="jdbc/ejb/zellsoft"
pooled-location="jdbc/pool/zellsoft"
connection-driver="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:@oracle.zellsoft.com:1521:UTF"
username="zs"
password="zs"
max-connections="20"
min-connections="5"
max-connect-attempts="10"
connection-retry-interval="1"
inactivity-timeout="30"
wait-timeout="30"
/>
</data-sources>
Our code for retrieval of the database connection:
private static Connection getConnection( String provider, String
prefix )
throws InitializerException {
try {
if (ic==null) ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup(prefix+provider);
Connection conn = ds.getConnection();
conn.setAutoCommit(false);
return conn;
} catch (NamingException cfe) {
throw new InitializerException("Unable to lookup Datasource" );
} catch (SQLException se) {
throw new InitializerException("Unable to get DB connection");
}
}
Returning the connection:
public static void returnJDBCConnection(Connection conn)
throws InitializerException {
try {
conn.close();
} catch (SQLException se) {
throw new InitializerException("Unable to return DB connection");
}
}
The exception we are getting is:
OrionCMTConnection not closed, check your code!
LogicalDriverManagerXAConnection not closed, check your code!
Created at:
java.lang.Throwable: OrionCMTConnection created
at
com.evermind.sql.OrionCMTConnection.<init>(OrionCMTConnection.java:39)
at
com.evermind.sql.OrionCMTDataSource.getConnection(OrionCMTDataSource.jav
a:56)
at
com.zellsoft.services.dbconnection.Initializer.getConnection(Initializer
.java:79)
at
com.zellsoft.services.dbconnection.Initializer.getJDBCConnection(Initial
izer.java:46)
at
com.zellsoft.session.SessionManagerEJBBean.logOnAdmUser(SessionManagerEJ
BBean.java:1276)
at
com.zellsoft.session.SessionManagerEJBBean.logOnUser(SessionManagerEJBBe
an.java:1250)
at
com.zellsoft.session.SessionManagerEJBBean.executeCmd(SessionManagerEJBB
ean.java:475)
at
SessionManagerEJB_StatelessSessionBeanWrapper0.executeCmd(SessionManager
EJB_StatelessSessionBeanWrapper0.java:71)
at com.zellsoft.media.web.adm.AmmWeb.doPost(AmmWeb.java:157)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:211)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:309)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:336)
at
com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestD
ispatcher.java:508)
at
com.evermind.server.http.ServletRequestDispatcher.forwardInternal(Servle
tRequestDispatcher.java:177)
at
com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHa
ndler.java:576)
at
com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:
189)
at
com.evermind.util.ThreadPoolThread.run(ThreadPoolThread.java:62)
I have experiemented using the
oracle.jdbc.pool.OracleConnectionPoolDataSource, but is appears to cause
connections to be leaked at an even higher rate.
Environment:
Java HotSpot(TM) Client VM (build 1.3.1-b24, mixed mode)
SuSE Linux
Can you say if we are doing anything wrong? I have inspected the code,
and cannot find anywhere where connections are never returned. All our
JDBC is within try-catch-finally blocks where returning the connection
is done in the finally block.
Any ideas?
Ian
