DB2Exception on rs.next()

[Ted J Schrader]

Hello,
I have stumbled on a very frustrating error that may be of interest to others. Note that this error only occurs every once in a while.
First, the error:
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0501N The cursor specified in a FETCH or CLOSE statement is not open. SQLSTATE=24501
Now, the old code:

 
public static NotificationBean getNotification(String saCode, Timestamp notificationDate, Connection conn) throws java.sql.SQLException {
System.out.println("NotificationBean.getNotification: starting. conn is: "+ conn);
// guard clause
if(saCode == null) throw new IllegalArgumentException("NotificationBean.getNotification: SA Code cannot be null.");
if(notificationDate == null) throw new IllegalArgumentException("NotificationBean.getNotification: Notification Date cannot be null.");

final String GET_NOTIFICATION =
"SELECT SA_CD, TRN_CD, NTFTN_DT, NTFTN_TXT, LST_UPDT_ID, LST_UPDT_TS " +
"FROM NTFTN_TBL " +
"WHERE SA_CD = ? AND NTFTN_DT = ?";
NotificationBean notice = null;
ResultSet rs = null;

PreparedStatement stmt = null;
try
{
//set up the statement
stmt = conn.prepareStatement(GET_NOTIFICATION);
stmt.setString(1, saCode);
stmt.setTimestamp(2, notificationDate);

//execute the statement
rs = stmt.executeQuery();
// navigate the result set
// rs.next() makes it crash
if(rs.next())
{
// create and set the notification bean information
notice = new NotificationBean();
notice.setSaCode(rs.getString("sa_cd"));
notice.setTranCode(rs.getInt("trn_cd"));
notice.setNotificationDate(rs.getTimestamp("ntftn_dt"));
notice.setNotificationText(rs.getString("ntftn_txt"));
notice.setLastUpdateId(rs.getString("lst_updt_id"));
notice.lastUpdateTimestamp = rs.getTimestamp("lst_updt_ts");
}// end if
}
finally
{
try
{
stmt.close();
}
catch(Exception e)
{
System.out.println("Exception closing statement in finally: " + e);
}// end catch
}// end try-finally
System.out.println("NotificationBean.getNotification: ending.");
return notice;
}

As noted in the code, it bombs at rs.next(). On the database, the NTFTN_DT column is defined on DB2 as DATE.
Now, if I change

stmt.setTimestamp(2, notificationDate);
 

to

stmt.setDate(2, new java.sql.Date(notificationDate.getTime()));
 

it will start to work.
I've been told that even this isn't the best way to do this; just hard-code CURRENT DATE in the SQL to ensure that we are getting database date instead of server date.
Hope this helps.
Ted Schrader, SCJ2P