Hi!

Question one:

I have bunches of the following code (there is Oracle 9i behind it):

public static Vector getSomeVector(Connection conn) throws
SQLException {
 java.util.Vector result = new Vector();
 java.sql.PreparedStatement pstmt = conn.prepareStatement(SOME_SQL);
 try {

   //Should I put this out of the try finally block. Start of block
   pstmt.setInt(1, 123456);
   pstmt.setString(2, "dummy");
   java.sql.ResultSet rset = pstmt.executeQuery();
   //End of block

   while (rset.next()) {
     result.add(rset.getString("SOME_FIELD"));
   }
 } finally {
   pstmt.close();
 }
}

I don't want to catch any exception, just want to close the
PreparedStatement the right way. The question is: Do I have to close
the _prepared_ statement or whether I have to close the _executed_
statement? I know it would probably work the both way, I just want to
clearify my idea of PreparedStatements.

Question Two:

I wonder if it is a good idea not to close after every execution, but
just once at the end of the loop? I have heard of caching at the
server side, and that the code below should have better performance if
the PreparedStatement is not closed after every execution. Is this
true?

public static Vector getSomeVector(Connection conn) throws
SQLException {
 java.util.Vector result = new Vector();
 java.sql.PreparedStatement pstmt = conn.prepareStatement(SOME_SQL);
 try {

   //Is it a good idea not to close after every execution, but just
once?
   for(int i = 0; i < 10; i++) {
     pstmt.setInt(1, 123456);
     pstmt.setString(2, "dummy");
     java.sql.ResultSet rset = pstmt.executeQuery();
     while (rset.next()) {
       result.add(rset.getString("SOME_FIELD"));
     }
   }

 } finally {
   pstmt.close();
 }
}

Thanks in advance,

jeges