PK77658: JPA ISSUES WITH NAMED QUERY ( @NAMEDQUERY ) AFTER PK69000

Fixes are available

APAR status

Closed as program error.

Error description

JPA issues with named query ( @NamedQuery )

Local fix

Problem summary

****************************************************************
* USERS AFFECTED:  All users of the Java Persistence API       *
*                  (JPA) with Oracle databases on WebSphere    *
*                  Application Server                          *
****************************************************************
* PROBLEM DESCRIPTION: Incorrect SQL is generated for Java     *
*                      Persistence Query Language              *
*                      (JPQL) queries which contain a sub      *
*                      query.                                  *
****************************************************************
* RECOMMENDATION:                                              *
****************************************************************
When a JPQL query is executed on multiple entities and
contains a sub query the generated SQL might not include the
correct table aliases.

For example consider this JPQL query :
SELECT e,d from Employee e, Dependent d
     WHERE e.empId = :empid
     AND d.id.empid = (SELECT MAX (e2.empId) FROM Employee e2)
     AND d.id.effDate > :minDate
     AND d.id.effDate < :maxDate

The generated SQL might look like this :
ELECT t0.empId, t3.effDate, t3.empid, t3.name
    FROM SUBQ_EMPLOYEE t0, SUBQ_DEPENDENT t3
    WHERE (t0.empId = ?
    AND t1.empid = (SELECT MAX(t2.empId) FROM SUBQ_DEPENDENT
            t1, SUBQ_EMPLOYEE t2)
    AND t3.effDate > ?
    AND t3.effDate < ?

The SQL is missing the definition for table alias t1 and t2.
When this occurs a SQLException is thrown by the JDBC
provider. For example :

openjpa-1.0.4-SNAPSHOT-r420667:740105M nonfatal general error>
org.apache.openjpa.persistence.PersistenceException:
ORA-00904: "T1"."EMPID": invalid identifier
 {prepstmnt 23206283 SELECT t0.empId, t3.effDate, t3.empid,
t3.name FROM SUBQ_EMPLOYEE t0, SUBQ_DEPENDENT t3 WHERE
(t0.empId = ? AND t1.empid = (SELECT MAX(t2.empId) FROM
SUBQ_DEPENDENT t1, SUBQ_EMPLOYEE t2) AND t3.effDate > ? AND
t3.effDate < ?) [params=(long) 101, (Timestamp) 1969-12-31
18:00:00.1, (Timestamp) 1969-12-31 18:01:40.0]} [code=904,
state=42000]
 at
org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDic
tionary.java:3951)
 at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions
.java:97

<snip>

estedThrowables:
java.sql.SQLException: ORA-00904: "T1"."EMPID": invalid
identifier
 at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError
.java:112)

Problem conclusion

The JPA Provider product code was updated to include the
correct table alias definitions.

The fix for this APAR is currently targeted for inclusion in
fixpack 6.1.0.25.  Please refer to the Recommended Updates
page for delivery information:
http://www.ibm.com/support/docview.wss?rs=180&uid=swg27004980

Temporary fix

Comments

APAR Information

APAR number
PK77658
Reported component name
WEBSPHERE APP S
Reported component ID
5724J0800
Reported release
61W
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2008-12-17
Closed date
2009-02-11
Last modified date
2009-02-24

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
WEBSPHERE APP S
Fixed component ID
5724J0800

Applicable component levels

R61A PSY
UP
R61H PSY
UP
R61I PSY
UP
R61P PSY
UP
R61S PSY
UP
R61W PSY
UP
R61Z PSY
UP

Rate this page:

(0 users)Average rating

Copyright and trademark information

IBM, the IBM logo and ibm.com are trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at "Copyright and trademark information" at www.ibm.com/legal/copytrade.shtml.

Document information

WebSphere Application Server

General

Software version:
6.1

Reference #:
PK77658

Modified date:
2009-02-24

PK77658: JPA ISSUES WITH NAMED QUERY ( @NAMEDQUERY ) AFTER PK69000

Fixes are available

Subscribe

APAR status

Closed as program error.

Error description

Local fix

Problem summary

Problem conclusion

Temporary fix

Comments

APAR Information

APAR number

Reported component name

Reported component ID

Reported release

Status

PE

HIPER

Special Attention

Submitted date

Closed date

Last modified date

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

Fixed component ID

Applicable component levels

R61A PSY

R61H PSY

R61I PSY

R61P PSY

R61S PSY

R61W PSY

R61Z PSY

Rate this page:

Copyright and trademark information

Rate this page:

Add comments

Document information

Translate my page

Content navigation

Footer links