Hello everybody:

I have developed a Java application using JDBC and MySQL and it works good. The problem is that i have to use it with DB2.

When I make a query, the returned resultset contains the data I need. The code is:

__________________________________________________ ________________
rs = pstmt.executeQuery();

}catch ( Throwable e){
System.out.println ("[ServBD]EjecutaInserccion" + e.getClass().getName() + ": " + e.getLocalizedMessage()+"\r\n\r\n"+ query);
if (parametros != null){for (int i=0; i<parametros.length; i++) System.out.println(parametros[i]);}
e.printStackTrace();
throw new BDException("Exceptión en ejecutaConsulta. Problema con la BD. "+e+" "+e.getClass().getName()+" "+e.getMessage()+"\r\n\r\n"+ query);
}
finally {
try {
disconnect();
}catch (Exception e) {e.printStackTrace();}
}
return rs;

__________________________________________________ ______________

So I close the connection and then return the resultset. If I do this using MySQL it wokrs good, but it I close the connection in DB2, the resultset is closed too, and i get this error:

COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC Driver] CLI0600E Descriptor de conexión no válido o la conexión está cerrada. SQLSTATE=S1000
at COM.ibm.db2.jdbc.net.SQLExceptionGenerator.throwCo nnectionClosedError(SQLExceptionGenerator.java:435 )
at COM.ibm.db2.jdbc.net.DB2Request.send(DB2Request.ja va:583)
at COM.ibm.db2.jdbc.net.DB2Request.sendAndRecv(DB2Req uest.java:562)
at COM.ibm.db2.jdbc.net.DB2RowObject.forwardOnlyNext( DB2RowObject.java:246)
at COM.ibm.db2.jdbc.net.DB2ResultSet.next(DB2ResultSe t.java:274)
at interDBAplic.Mediador.consultar(Mediador.java:142)
at Programa.doIt(Programa.java:97)
at Programa.main(Programa.java:42)

If I don't close the connection, the returned resultset contains the data and the exception is not throwed, so is there a way to close the connection and return the resultset without problems?

thanks a lot

I don't think so. JDBC specification doesn't say clearly that the ResultSet should stay open after its Connection is closed so I guess the behaviour is implementation-specific.

Absolutely, I'm surprised any vendor lets you read from a ResultSet when the Connection is closed! DB2 doesn't pull all the rows over to the client until they're requested (it does this in blocks) so without a connection it is quite impossible to continue... not to mention maintaining locks, etc...

MySQL basically stinks, though, in my experience... fine for quick-and-dirty I guess.

So the way I have to use is:

- execute the query.
- read the results in the resultSet.
- close the connection.

Isn't it?

Or maybe I have to copy the resultSet to other object not related to the connecition?

Thanks

Yes. A typical design pattern for Java is to create ValueObjects and use a DAO to load them.

ie. say you have a table that represents a Person, you would likely have a PersonVO, with getXXX and setXXX methods for things like Lastname, firstname, etc.

Then you would have a PersonDAO with a method like selectPerson(id), selectPersonByLastname(), updatePerson(PersonVO), etc, which would perform the SQL, build the ValueObject, and return it.... or shred it into an INSERT/UPDATE.

If you want to learn more detail, look at the Java blueprints:
http://java.sun.com/blueprints/patterns/index.html