PK41663: EJBQL FINDER METHOD

6.1.1.3: WebSphere Application Server Toolkit Version 6.1.1 Fix Pack 3
Rational Application Developer, Version 7.0.0.3

APAR status

• Closed as program error.

Error description

• Customer has problem with Version 6 migrated EJB's.
  
  In the 5.6.0.4 env they have had a EJBQL finder method to search
  for a
  row in a
  table - select object(o) from SchoolOfTheMonthBean o where ?1
  BETWEEN o.
  dateFrom and o.dateTo.
  
  This generated a finder method in a certain way.The exact same
  finder
  descriptor
  in version 6 has been generated in a different way and as a
  result does
  not
  work anymore.
  
  In Version 5.x(WASD), the genarated code looks like below,
  
  ===================
  public EJSFinder findXByDate(Date dateNow) throws
  FinderException,
  RemoteException {
  
     ResultSet resultSet = null;
     PreparedStatement pstmt = null;
  
     try {
  
   preFind();
   pstmt = getPreparedStatement(" select q1.\"USERID\",
  q1.\"DATEFROM\",
  q1.\"DATETO\", q1.\"EDITEDBY\", q1.\"DATEMODIFIED\",
  q1.\"SPARESTRING\",
  q1.\"SPARELONG\", q1.\"ID\" from SCHOOLOFTHEMONTH q1 where (? >=
  q1.\"DATEFROM\") and (? <= q1.\"DATETO\") "); Object convObj =
  null;
  convObj =
  VapTimestampToUtilDateConverter.singleton().dataFrom(dateNow);
   if(convObj == null)
     pstmt.setNull(1, 93);
        else
         pstmt.setTimestamp(1, (Timestamp)convObj); convObj =
  VapTimestampToUtilDateConverter.singleton().dataFrom(dateNow);
  if(convObj == null) pstmt.setNull(2, 93); else
  pstmt.setTimestamp(2,
  (Timestamp)convObj);
   resultSet = pstmt.executeQuery();
   return new EJSJDBCFinder(resultSet, this, pstmt);
  
     }
    catch(Exception ex) {
    throw new EJSPersistenceException("find failed:", ex);
    }
  }
  ===================
  
  however, in version 6 this is generated as
  
  ===================
  public EJSFinder findXByDate(java.util.Date dateNow) throws
  javax.ejb.FinderException, java.rmi.RemoteException {
  
     ResultSet resultSet = null;
     PreparedStatement pstmt = null;
     try {
  
      preFind();
      pstmt = getPreparedStatement(" select q1.\"USERID\",
  q1.\"DATEFROM\", q1.\"DATETO\", q1.\"EDITEDBY\",
  q1.\"DATEMODIFIED\",
  q1.\"SPARESTRING\", q1.\"SPARELONG\", q1.\"ID\",
  q1.\"OPTCOUNTER\" from
  SCHOOLOFTHEMONTH q1 where (? >= q1.\"DATEFROM\") and (? <=
  q1.\"DATETO\") ");
      Object convObj = null;
      convObj =
  com.ibm.vap.converters.VapTimestampToUtilDateConverter.singleton
  ().dataF
  rom(dateNow);
      if (convObj == null)
          pstmt.setNull(1, java.sql.Types.TIMESTAMP);
      else
           pstmt.setTimestamp(1, ((java.sql.Timestamp) convObj));
    resultSet = pstmt.executeQuery();
    return new EJSJDBCFinder(resultSet, this, pstmt);
     }
     catch (Exception ex) {
    throw new EJSPersistenceException("find failed:", ex);
    }
  }
  ====================
  
  Resulting error is [09/01/07 11:19:17:514 GMT] 0000004c
  ExceptionUtil E
  CNTR0019E: EJB threw an unexpected (non-declared) exception
  during
  invocation of
  method "findXByDate". Exception data:
  com.ibm.ejs.persistence.EJSPersistenceException: find failed:;
  nested
  exception
  is: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0100E
  Wrong
  number of
  parameters. SQLSTATE=07001 at
  
  COMPLETE STACK TRACE IS attached. <<40606_999_866-info.txt>>
  
  3)Things that you have tried to resolve.
  I had worked with test case to see the generated code, although
  customer
  runs the client application using
  Websphere commerce server that starts the WAS Server.
  
  I also tried to create a project in RAD  6, no migration
  involved, with
  same table and as same finder method and as same EJB QL.
  the generated finder method code looks different from above
  codes.
  
  Steps to recreate the problem:
  
  please import project with PI
  <<WebSphereCommerceServerExtensionsData.zip>>
  please extract the zip
  files(PMR_40606_999_866_part_1.zip/PMR_40606_999_866_part_2.zip)
  please configure the jars from extracted zip in RAD -->
  properties -->
  Java Build Path --> Libraries tab.
  build the ejb project. You will see the generated findXByDate()
  in
  uk.co.tbp.commerce.extension.objects\ (package)
  EJSJDBCPersisterCMPSchoolOfTheMonthBean_60f81dbe.java\
  public EJSFinder findXByDate(java.util.Date dateNow)
  

Local fix

• yes, they can change the code in findXByDate(java.util.Date
  dateNow)
  with code generated in 5.x.
  uk.co.tbp.commerce.extension.objects\ (package)
  EJSJDBCPersisterCMPSchoolOfTheMonthBean_60f81dbe.java\
  public EJSFinder findXByDate(java.util.Date dateNow)
  
  but the problem with this workaround is, when they try to deploy
  the
  project,
  the new code generated will overwrite on old code and error
  occurs.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All users of the EJB deploy code             *
  *                 generation facility in Rational              *
  *                 Application Developer v6.0 and v7.0.         *
  ****************************************************************
  * PROBLEM DESCRIPTION: An EJB-QL statement defined to accept   *
  *                      two input parameters causes a runtime   *
  *                      failure as only one parameter in the    *
  *                      generated deployment code is set.       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  If an EJB-QL statement is defined as the following:
  
  select object(o) from Month o where ?1 between o.dateFrom and
  o.dateTo
  
  Then the generated SQL looks similar to:
  
  select q1.id, q1.dateFrom, q1.dateTo from Month q1 where(? >=
  q1.dateFrom) and (? <= q1.dateTo)
  
  However in the generated EJB deployment code only one
  parameter in the WHERE clause is being set, therefore
  resulting in a JDBC error similar to:
  
  COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0100E
  Wrong number of parameters.  SQLSTATE=09001
  

Problem conclusion

• The code generator for finder methods has been corrected to
  properly account for the number of parameters in this
  situation.  The fix for this APAR is currently targeted for
  inclusion in Rational Application Developer v6.0.1.2 and
  v7.0.0.3.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  WEBAPP SERVER A

• Fixed component ID

  5724J0820

Applicable component levels

• R600 PSY

     UP

• R610 PSY

     UP