Newbie question: Subselect with MySQL

[Ognjen Blagojevic <og...@etf.bg.ac.yu>]

Hello,

I am new to OpenJPA, and I have a question regarding subselects.

When I try to invoke an EJB QL query like this:

select p
   from Person p
  where p.id in (select st.idPerson.id from Student st)


I get the following exception:

Exception in thread "main" <0.9.7-incubating fatal general error> 
org.apache.openjpa.persistence.PersistenceException: The database 
dictionary in use ("class org.apache.openjpa.jdbc.sql.MySQLDictionary") 
reports that it does not have feature "SupportsSubselect".  This feature 
is needed to complete the current operation.  To force OpenJPA to try to 
use the feature anyway, set the following property:
openjpa.jdbc.DBDictionary: SupportsSubselect=<value>
	at ...


I'm using MySQL 4.1.16, which have subselect support, and writing this 
query in native SQL is easy. How can I use subselects with OpenJPA and 
EJB QL?


Thanks,
Ognjen

RE: Newbie question: Subselect with MySQL

[Pinaki Poddar <pp...@bea.com>]

Set the following property in META-INF/persistence.xml
<property name="openjpa.jdbc.DBDictionary"
value="SupoortsSubselect=true"/>
when you are using MySQL. This property is set to false by deafult in
openjpa.jdbc.MySQLDictionary to accomodate older MySQL versions. 

OpenJPA provides extensive options to configure a database declaratively

http://openjpa.apache.org/docs/latest/manual/manual.html#ref_guide_dbset
up_dbdictprops
Also you can programatically extend the DBDictionary (or its
derivations) for special purposes. 
  


Pinaki Poddar
BEA Systems
415.402.7317  


-----Original Message-----
From: Ognjen Blagojevic [mailto:ognjen@etf.bg.ac.yu] 
Sent: Monday, June 11, 2007 10:26 AM
To: dev@openjpa.apache.org
Subject: Newbie question: Subselect with MySQL

Hello,

I am new to OpenJPA, and I have a question regarding subselects.

When I try to invoke an EJB QL query like this:

select p
   from Person p
  where p.id in (select st.idPerson.id from Student st)


I get the following exception:

Exception in thread "main" <0.9.7-incubating fatal general error>
org.apache.openjpa.persistence.PersistenceException: The database
dictionary in use ("class org.apache.openjpa.jdbc.sql.MySQLDictionary")
reports that it does not have feature "SupportsSubselect".  This feature
is needed to complete the current operation.  To force OpenJPA to try to
use the feature anyway, set the following property:
openjpa.jdbc.DBDictionary: SupportsSubselect=<value>
	at ...


I'm using MySQL 4.1.16, which have subselect support, and writing this
query in native SQL is easy. How can I use subselects with OpenJPA and
EJB QL?


Thanks,
Ognjen

Notice:  This email message, together with any attachments, may contain information  of  BEA Systems,  Inc.,  its subsidiaries  and  affiliated entities,  that may be confidential,  proprietary,  copyrighted  and/or legally privileged, and is intended solely for the use of the individual or entity named in this message. If you are not the intended recipient, and have received this message in error, please immediately return this by email and then delete it.