Thread: Statement as a parameter of a method

Statement as a parameter of a method

From
Jean-Michel Zigna
Date:
Hello,

We have got a problem in our application when updating our operational
server with redhat 7.3, postgresql 7.4.2, JDL 1.4.2 which does not occur in
our development and validation platform with the same softwares.

See the source below to get the context of the problem.

Main :

     ResultSet res = stmt.executeQuery(query.toString());
          while (res.next()) {

             if (!res.wasNull()) actu.setTheme(getThemeNom(idTheme, stmt));
          }
         res.close();
         stmt.close();

Actu.setTheme :

      private String getThemeNom(int id, Statement stmt) throws SQLException {
         String nom = null;
         ResultSet rs = stmt.executeQuery("select nom from themes where
id_theme = " + id);
         if (rs.next()) nom = rs.getString("nom");
         rs.close();
         return nom;
}

It looks like if the statement parameter declared and used in Main and send
to the actu.setTheme method to execute the new query is closed when closing
the resultset in Actu.setTheme. We solved the problem by declaring a new
statement in Actu.setTheme and changing the signature a this method as below.

Main :
         ResultSet res = null;
         try {
             res = stmt.executeQuery(query.toString());
             while (res.next()) {

                  actu.setTheme(getThemeNom(idTheme));

             }
         } catch(SQLException se) {
             throw se;
         } finally {
             if (res != null) res.close();
             stmt.close();
        }

     private String getThemeNom(int id) throws SQLException {
         Statement stmt = getConnexion().createStatement();
        String nom = null;
         ResultSet rs = stmt.executeQuery("select nom from themes where
id_theme = " + id);
         if (rs.next()) nom = rs.getString("nom");
         rs.close();
         stmt.close();
         return nom;
}

Please, can you tell me if JDBC specifications are not compatible with the
old version of the source ? Can we declare a statement as a parameter of a
method, and if, what are the limits when using it in the method ? Do we
need to change a postgres parameter or setting ?

Thanks for your explanations.

Jean-Michel Zigna.


Re: Statement as a parameter of a method

From
Kris Jurka
Date:

On Fri, 16 Jul 2004, Jean-Michel Zigna wrote:

> It looks like if the statement parameter declared and used in Main and send
> to the actu.setTheme method to execute the new query is closed when closing
> the resultset in Actu.setTheme. We solved the problem by declaring a new
> statement in Actu.setTheme and changing the signature a this method as below.
>
> Please, can you tell me if JDBC specifications are not compatible with the
> old version of the source ? Can we declare a statement as a parameter of a
> method, and if, what are the limits when using it in the method ? Do we
> need to change a postgres parameter or setting ?
>

From the ResultSet javadoc:

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

This means you can only have one open ResultSet on a given Statement at a
time which your original coding violates.

Kris Jurka