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 is sysrouted FROM one or more of the following: APAR is sysrouted TO one or more of the following: Modules/Macros
|
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
(C) Copyright IBM Corporation 2000, 2006. All Rights Reserved.