cached connection, statement and resultset
Jinsong Hu
Hi,
We have encountered a scenario in which the same jts connection, we did two query
on two different prepared statement, the first one is okay, while the second one
complaining about the following error: java.sql.SQLException: Io exception: Broken
pipe at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:114) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:156)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269) at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:1705)
at oracle.jdbc.driver.OracleStatement.doExecute(OracleStatement.java:1758) at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1805)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:322)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:280)
at weblogic.jdbc.pool.PreparedStatement.executeQuery(PreparedStatement.java:58)
It seems that Weblogic not only cache connection, but also cache statement even
result set.
If this is true, then I have the following questions:
(1) When did WL discarded cached statement and resultset? After the connection
returned to pool?
(2) If the connection pool is refreshed due to some db error, (Weblogic does provide
this), then it supposes that all new jts connection should be fine since it is
got from refreshed pool. But, we did find that after the refresh, we still keep
getting 'Io exception: Broken pipe' error, I think this is weblogic's bug if it
is true.
Any comments?
Sree Bodapati
What version of WebLogic are you using?
You can set the property testConnsOnReserve=true on the connection pool so
you are ensured to get a good connection as long as the db is up. If your
app holds on to the bad connection during the refresh process, there is no
way for WebLogic to refresh that connection so ensure that your app closes
all the connections its using in a finally block.
Another thing, you can try is to update the Oracle driver (get a new one
from oracle's website) and place it in the beginning of the CLASSPATH in the
WebLogic startup script, it should help.
hth
/
sree
"Jinsong Hu" <j...@glog.com> wrote in message
news:3c7455b9$1...@newsgroups.bea.com...
Jinsong Hu
Hi Sree,
Thanks for your reply.
We are using Weblogic6.0sp2. The connection we create is new connection and
also it is after the pool refreshing. Have you ever encountered this problem in
WL6.0SP2? It is not that we hold a bad connection, it is really we got a bad connection
from the pool. I am just wondering if we moved to WL6.1sp2 could help, or just
as you said, update Oracle JDBC thin driver.
This is very important for us, could you give me more information on this,
such as how does WL6.0 managed its connection pool and cached statement and result?
What's their lifecycle.
Thanks
Best Regards
Jinsong
Sree Bodapati
You should try this first, check with this property testConnsOnReserve=true.
an example config.xml tag would have,
<JDBCConnectionPool
.................
TestConnectionsOnReserve="true"
TestTableName="dual"
.................
/>
If you see no resource available after doing this let me know.
I have not seen this in WLS 6.0SP2, but I will take a look at it to see if
there is a bug.
as Joe points in his previous emails, heres a code section that has to be
the guide rule for closing the JDBC objects which will ensure the connection
pool works for you properly.
myJdbcMethods()
{
Connection c = null;
try {
c = getConnection ....
...do all JDBC...
stmt = ........
rs = stmt.executeQuery.........
while(rs.next())...
do something....
rs.close();
stmt.close();
}
catch (Exception e) {
// do whatever...
}
finally {
// VERY IMPORTANT TO DO THIS FIRST IN A FINALLY BLOCK SO IT ALWAYS
HAPPENS
if (c != null) try {c.close();} catch (Exception ignore){}
close rs and stmt here as well.
}
}
hth
/
sree
"Jinsong Hu" <j...@glog.com> wrote in message
news:3c750441$1...@newsgroups.bea.com...
Jinsong Hu
Hi Sree,
The connection pool is really refreshed (our settings for connection pool
is the same as you said), I got this message from weblogic.log, but it seems we
still ge PreparedStament with bad connection.
Sree Bodapati
Please post the JDBCConnection pool tag you are using. I will take a look at
it and see if I can find something.
To work around it for now you can set STATEMENT_CACHE_SIZE=0 in the
properties section of the connection pool definition to turn cache'ing off.
/
sree
"Jinsong Hu" <j...@glog.com> wrote in message
news:3c753824$1...@newsgroups.bea.com...
Jinsong Hu
Hi Sree,
Following is our setting for JDBC ConnectionPool:
<JDBCConnectionPool CapacityIncrement="2"
DriverName="oracle.jdbc.driver.OracleDriver"
InitialCapacity="50" MaxCapacity="100" Name="dbaPool"
Properties="user=glogdba;password=glogdba;dll=ocijdbc8;protocol=thin"
RefreshMinutes="5" Targets="gc3" TestConnectionsOnReserve="true"
TestTableName="dual" URL="jdbc:oracle:thin:@doyle.glogtech.com:1521:qgc35"/>
Joseph Weinstein
Jinsong Hu wrote:
> Hi Sree,
>
> Following is our setting for JDBC ConnectionPool:
>
> <JDBCConnectionPool CapacityIncrement="2"
> DriverName="oracle.jdbc.driver.OracleDriver"
> InitialCapacity="50" MaxCapacity="100" Name="dbaPool"
> Properties="user=glogdba;password=glogdba;dll=ocijdbc8;protocol=thin"
> RefreshMinutes="5" Targets="gc3" TestConnectionsOnReserve="true"
> TestTableName="dual" URL="jdbc:oracle:thin:@doyle.glogtech.com:1521:qgc35"/>
Hi. I suggest:
<JDBCConnectionPool CapacityIncrement="2"
DriverName="oracle.jdbc.driver.OracleDriver"
InitialCapacity="50" MaxCapacity="50" Name="dbaPool"
Properties="user=glogdba;password=glogdba;dll=ocijdbc8;protocol=thin"
RefreshMinutes="999999" Targets="gc3" TestConnectionsOnReserve="true"
TestTableName="dual" URL="jdbc:oracle:thin:@doyle.glogtech.com:1521:qgc35"/>
to make all the connections at startup and to effectively turn off refreshing, because it's
redundant if you have TestConnectionsOnReserve="true", unless you have a firewall
and need to keep connections busy.
We will make sure about your version of the server, but when the DBMS goes down,
the pool will discover it, and when it replaces a dead connection with a new one, it should
be clearing out the statement cache so any subsequent call for a prepared or callable
statement shoulc cause a new one to be made.
Joe