EJBDeploy generates wrong SQL code for Enterprise JavaBeans Query Language (EJB QL) when Oracle is used as target database
 Technote (troubleshooting)
 
Problem(Abstract)
When Component Managed Persistence (CMP) uses EJB™ QL to access Oracle®, the SQL code generated by EJBDeploy tool has incorrect syntax
 
Cause
From the EJB deployment descriptor, the EJB QL is defined as
<query>
<description></description>
<query-method>
<method-name>findByName</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql>select object(o) from EventDefinitionStore o where o.name like ?1 order by o.name</ejb-ql>
</query>

The SQL code generated by EJBDeploy follows:
/* select object(o) from EventDefinitionStore o where o.name like ?1 */

pstmt = prepareStatement(connection,"select q1.\"NAME\", q1.\"PARENTNAME\", q1.\"PARENT_NAME\" from CEI.EVENTDEFINITIONSTORE q1 where ( q1.\"NAME\" LIKE CAST(? AS VARCHAR(32672)))");

This "CAST(? AS VARCHAR(32672))" portion of the generated SQL is not valid for Oracle database because the VARCHAR datatype in Oracle can only be 4000 bytes.
 
Resolving the problem
There was a problem if you had multiple mappings (for example, one mapping for DB2 and one mapping for Oracle), there was a bug in EJB Query Engine where all the mappings were appearing as DB2. The workaround is to have only one mapping.

  • The other instance of this was when mapping is to a vendor that we do not explicitly support. If the vendor type is not recognized, mapping defaults to DB2.

WebSphere Application Server V5.0.2 ships EJBDeploy
v5.0,20030608_1900-WB211-AD-V51D-GM (this version information is displayed to the console when EJBDeploy runs).

The V5.0.2 EJBDeploy cumulative fix dated 10-23-2003 (or later) removes the need to do the CASTing (except in very limited situations) which resolves the problems.
 
 
Cross Reference information
Segment Product Component Platform Version Edition
Application Servers Runtimes for Java Technology Java SDK
 
 


Document Information


Product categories: Software > Application Servers > Distributed Application & Web Servers > WebSphere Application Server > EJBDeploy (WSAD)
Operating system(s): Windows
Software version: 5.0.2.4
Software edition:
Reference #: 1165758
IBM Group: Software Group
Modified date: Jun 7, 2004