3.20 nested query warning about open connections

[aarti]

"aarti" wrote:
Using XA JDBC drivers from datadirect and SQLServer
In 3.04, I was getting a transaction exception with nested queries.

David Jencks I followed your suggestion and moved to 3.2 and also added the <track-connection-by-tx>true</track-connection-by-tx> to the config file.

In 3.2, the transaction works but I get messages about open connections.

The nested query code looks like this:

Method 1
Open connection
Execute query
loop through resultset
Call method 2
close resultset
close conn
end of method 1

Method 2
Open conn
execute query
close conn
End of method 2

[davidjencks]

"davidjencks" wrote:
Looks like it should work to me. Can you show the stack trace and more of your code? I assume the call to method2 is inside the loop? I also assume it is a direct method call rather than through the ejb interface?

[aarti]

"aarti" wrote:
This happens with any nested query:

This is the warning I am getting
18:14:55,080 WARN [jdbc.WrappedConnection] Closing a statement you left open, please do your own housekeeping

My source code looks like this:

public void updateCheckOrderStatus(ScheduledOrderVO scheduledOrderVO)
{
Connection con = null;

try
{
con = SQLDAOFactory.getConnection();

Statement stmt = con.createStatement();
MessageFormat mf = new MessageFormat(UPDATE_CHECK_ORDER);
Object[] arguments =
{
new String("'" + StringUtils.replace(scheduledOrderVO.checkedBy.userId.trim(), "'", "''") + "'"),
new String("'" + StringUtils.replace(scheduledOrderVO.patientKeyVO.patientId.trim(), "'", "''") + "'"),
new String("'" + StringUtils.replace(scheduledOrderVO.orderId.trim(), "'", "''") + "'"),
new Integer(scheduledOrderVO.orderAdminNum.trim())};
stmt.executeUpdate(mf.format(arguments));

insertVerifiedOrder(scheduledOrderVO);
stmt.close();
con.close();
}
catch (SQLException se)
{
throw new LoggableSystemException(se, log, Level.ERROR, null);
}
finally
{
try
{
if (con != null)
{
con.close();
}
}
catch (SQLException e)
{
}
}



private void insertVerifiedOrder(ScheduledOrderVO scheduledOrderVO)
{
Connection con = null;

try
{
con = SQLDAOFactory.getConnection();

Statement stmt = con.createStatement();
MessageFormat mf = null;
// Get the solution information for order
mf = new MessageFormat(SELECT_SOLUTION_BY_ORDER);
Object[] solutionArgs =
{ new String("'" + scheduledOrderVO.patientKeyVO.patientId + "'"),
new String("'" + scheduledOrderVO.orderId + "'")};
ResultSet rs = stmt.executeQuery(mf.format(solutionArgs));
if (rs != null && rs.next())
{
boolean isSolution = rs.getInt(1) == 1 ? true : false;
if (isSolution)
// For a solution order the dose_val_min and dose_units are copied to the volume fields leaving the dose min, max and units null.
mf = new MessageFormat(INSERT_VERIFIED_ORDER_WITH_VOLUME);
else
//For a non-solution order the dose_val_min, max and units are copied to the same fields and the volume fields are null.
mf = new MessageFormat(INSERT_VERIFIED_ORDER);

Object[] args =
{
new String("'" + scheduledOrderVO.patientKeyVO.patientId + "'"),
new String("'" + scheduledOrderVO.orderId + "'"),
new Integer(scheduledOrderVO.orderAdminNum)};
System.out.println("EXECUTING:" + mf.format(args));
int rows = stmt.executeUpdate(mf.format(args));

if (rows < 1)
throw new EJBException(
" Error inserting Secure Order for patient"
+ scheduledOrderVO.patientKeyVO.patientId
+ " and order "
+ scheduledOrderVO.orderId);
// For a solution order add the solution information in the secure order solution table
if (isSolution)
{
Statement stmt2 = con.createStatement();

MessageFormat mf2 = new MessageFormat(INSERT_VERIFIED_ORDER_SOLUTION);
System.out.println("EXECUTING:" + mf2.format(args));
rows = stmt.executeUpdate(mf2.format(args));
}
}
else
throw new EJBException(" InsertVerifiedOrder: No Order found ");

}
catch (Exception e)
{
throw new LoggableSystemException(e, log, Level.ERROR, null);
}
finally
{
try
{
if (con != null)
{
con.close();
}
}
catch (SQLException e)
{
}
}
}

[jonlee]

"jonlee" wrote:
Actually, the problem isn't about releasing connections. JBoss is complaining that you don't close your statements before you release the connection. In the first method, you have a stmt.close() - you should make sure that you try to do this even if things go awry when you execute statements.

In the second method, you create two statements but don't have any code to close either.