RE: Relationship between JDBC Connections, Statements, ResultSets and open data paths

["Patrick L Archibald" <Patrick.Archibald@xxxxxxxxxxxxx>]

It is always best practice to:

resultSet.close();
preparedStatement.close();
connection.close();

I had a problem with file locks and it was because I
was just closing the connection and not the
result set and prepared statement.

Don't count on the system closing your ResultSet and
PreparedStatement.

Richard Dettinger [cujo@us.ibm.com] set me straight on
that one (see his comments below).

Thanx, PLA


The CLI and the QSQSRVR jobs are probably working as designed.  My guess is
that the SQL cursors are being left open.

The system is able to release locks and process the CL command if the
QSQSRVR only has pseudo-open cursors.  By pseudo-open, I mean that you have
closed the result set, but the system has kept it open for performance
reasons.  I'm thinking that my code is not attempting to close ResultSets
to clean up anything when objects are returned to the pool.  Although in a
quick look through the code I didn't see conclusively what happens (I'm in
class the next several days and very overwhelmed right now).

If the result set is truly left open, then the locks on that object can't
be removed by the system until they are explicitely released.

Richard D. Dettinger
AS/400 Java Data Access Team

Democracy's enemies have always underestimated the courage of the American
people.
It was true at Concord Bridge.  It was true at Pearl Harbor.  And it was
true today.

         Rochester Post-Bulletin
         Tuesday September 11, 2001


"Patrick L Archibald" <Patrick.Archibald@HOMETELCO.COM> on 01/14/2002
09:43:47 AM

To:    Richard Dettinger/Rochester/IBM@IBMUS
cc:
Subject:    File locks



Hi Richard

When using statement and connection pooling,
shouldn't the AS/400 release locks on files when
the CLRPFM or DLTF commands are issued on the
locked files?  I have found that the QSQSRVR job
will not release the file for clearing.

If this is a bug I'll report it to software
support.  I just thought I'd ask you first.

Thanx, PLA
/
/ Patrick L Archibald
/ http://HomeTelco.com/pla/
/





-----Original Message-----
From: java400-l-admin@midrange.com
[mailto:java400-l-admin@midrange.com]On Behalf Of Fred Kulack
Sent: Tuesday, October 08, 2002 10:28 AM
To: java400-l@midrange.com
Subject: RE: Relationship between JDBC Connections, Statements,
ResultSets and open data paths



On 10/08/2002 at 02:45:46 AM, java400-l-admin@midrange.com wrote:
> If you close the ResultSet, the Statement remains open (as does the
> Connection).  If you close the Statement (without
> closing the ResultSet) The Statement is closed but the Connection is
still
open
> and the ResultSet object still has scope.

I'm just curious - how does it correspond to the statement from JDBC
documentation (interface java.sql.ResultSet, method close()):

"A ResultSet object is automatically closed by the Statement object that
generated it when that Statement object is closed, re-executed, or is used
to retrieve the next result from a sequence of multiple results. A
ResultSet
object is also automatically closed when it is garbage collected."

Does it mean IBM goes its own way here?  :)
--- end of excerpt ---

If you close the statement, its ResultSet(s) is/are closed.


"The stuff we call "software" is not like anything that human society
  is used to thinking about. Software is something like a machine, and
  something like mathematics, and something like language, and
  something like thought, and art, and information...
  but software is not in fact any of those other things."
Bruce Sterling - The Hacker Crackdown

Fred A. Kulack - IBM eServer iSeries - Enterprise Application Solutions
ERP, Java DB2 access, Jdbc, JTA, etc...
IBM in Rochester, MN  (Phone: 507.253.5982   T/L 553-5982)
mailto:kulack@us.ibm.com   Personal: mailto:kulack@magnaspeed.net
AIM Home:FKulack  AIM Work:FKulackWrk
MSN Work: fakulack@hotmail.com


_______________________________________________
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L) 
mailing list
To post a message email: JAVA400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l
or email: JAVA400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.