PQ57305: NULL VALUES ARE BEING SET INTO THE 'WHERE CLAUSE' FINDERS WHEN USING THE EJB DEPLOY TOOL CAUSING A DB2 EXCEPTION

APAR status
Closed as program error.

Error description
We have run into a pervasive problem in all 'where clause'
finders in cases where null values are to be set into the
fields.  The following is a sample of the code generated
from the deploy tool: (modified to fit RETAIN)
.
 public EJSFinder
 findByFulfillmentCenterShipModeAndJurisdiction
  (java.lang.Integer arg0, java.lang.Integer arg1,
  java.lang.Integer arg2) throws
 javax.ejb.FinderException, java.rmi.RemoteException {
  ResultSet resultSet = null;
  PreparedStatement pstmt = null;
  try {
 preFind();
 pstmt = getPreparedStatement("SELECT
 T1.JURSTGROUP_ID, T1.SHIPMODE_ID,
 T1.FFMCENTER_ID, T1.CALRULE_ID, T1.PRECEDENCE,
 T1.SHPJCRULE_ID FROM SHPJCRULE  T1 WHERE
 (T1.FFMCENTER_ID = ? or T1.FFMCENTER_ID is null)
 and (T1.SHIPMODE_ID = ? or T1.SHIPMODE_ID is
 null) and (T1.JURSTGROUP_ID is null or
 T1.JURSTGROUP_ID in (select
 JURSTGPREL.JURSTGROUP_ID from JURSTGPREL where
 JURSTGPREL.JURST_ID = ?)) order by T1.PRECEDENCE
 desc");
 pstmt.setObject(1, arg0);
 pstmt.setObject(2, arg1);
 pstmt.setObject(3, arg2);
 resultSet = pstmt.executeQuery();
 return new EJSJDBCFinder(resultSet, this,
 pstmt);
  }
  catch (Exception ex) {
  throw new EJSPersistenceException("find
   failed:", ex);
  }
 }
When a null is passed as any of the parameters, the following
exception occurs for DB2 (we have not tried Oracle yet):
COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC Driver] CLI0613E
Program type out of range. SQLSTATE=S1003
.
We have found that the cause appears to be the use of the
setObject(int, Object) call in the persister code. Using the
method with the signature setObject(int, Object, int
targetSQLType) where the sqlType can be determined based on
the input object seems to handle nulls correctly .
Local fix Problem summary
****************************************************************
* USERS AFFECTED: WebSphere Application Server users of the    *
*                 EJB deploy tool                              *
****************************************************************
* PROBLEM DESCRIPTION: A problem exists in all WHERE clause    *
*                      custom finders in cases where null      *
*                      values can be set into the fields.      *
****************************************************************
* RECOMMENDATION:                                              *
****************************************************************
A sample of the generated code can be found in the submittor
text.  There are two problems with the code being generated
with the EJB deploy tool.
1) The generated code should use setInteger() rather than
   setObject.
2) Since the arg values can be null, the generated code
   needs to explicitly check for null and to call setNull() in
   that case.
Problem conclusion
Code has been changed to handle null values in where clauses.
This APAR was opened to handle java.lang.Integer, it also
applies to java.lang.Float, java.lang.Double, and
java.lang.Short data types.
Temporary fix Comments
APAR information
APAR number PQ57305
Reported component name WEBSPHERE AE NT
Reported component ID 5630A2201
Reported release 400
Status CLOSED PER
PE NoPE
HIPER NoHIPER
Submitted date 2002-01-30
Closed date 2002-03-05
Last modified date 2002-03-05

APAR is sysrouted FROM one or more of the following:

APAR is sysrouted TO one or more of the following:

Modules/Macros
DEPLOY          

Fix information
Fixed component name WEBSPHERE AE NT
Fixed component ID 5630A2201

Applicable component levels
R400 PSY    UP


Document Information


Product categories: Software > Application Servers > Distributed Application & Web Servers > WebSphere Application Server > General
Operating system(s):
Software version: 400
Software edition:
Reference #: PQ57305
IBM Group: Software Group
Modified date: Mar 5, 2002