I am getting an SQLException from the SQL generated by a DynamicQL statement. I
did not got this error until I added left-joins to my jbosscmp-jdbc.xml. I will
try to explain as best I can the circumstances which have led to this error.
I am using Jboss 4.0.3SP1 connecting to a PostgreSQL 8.0.4 server.
I have a DynamicQL query, which can look something like this:
DYNAMIC-QL: SELECT DISTINCT Object (r)
| FROM Recording r
| WHERE 1=1
| AND r.phoneCall.dateOfCall >= ?1
| AND r.phoneCall.dateOfCall <= ?2
| AND r.phoneCall.startTime >= ?3
| AND r.phoneCall.callCentre.key = ?4
| ORDER BY r.phoneCall.key, r.timestampSequence
It can take over 5 minutes to get the results and populate classes with the
values from the recording table and all of its CMR fields, so I'm trying to use
read-ahead on-find and left-joins to see if that speeds it up. The read ahead
is:
<read-ahead>
| <strategy>on-find</strategy>
| <page-size>4</page-size>
| <eager-load-group>*</eager-load-group>
| <left-join cmr-field="question" eager-load-group="*"/>
| <left-join cmr-field="phoneCall" eager-load-group="*">
| <left-join cmr-field="interviewerEmp"
eager-load-group="*">
| <left-join cmr-field="dept"
eager-load-group="*">
| <left-join cmr-field="parentDept"
eager-load-group="*"/>
| </left-join>
| <left-join cmr-field="title"
eager-load-group="*"/>
| </left-join>
| <left-join cmr-field="versionStatus"
eager-load-group="*"/>
| <left-join cmr-field="version"
eager-load-group="tss">
| <left-join cmr-field="study"
eager-load-group="*"/>
| </left-join>
| <left-join cmr-field="dashCase"
eager-load-group="*">
| <left-join cmr-field="form"
eager-load-group="*">
| <left-join cmr-field="studyDetail"
eager-load-group="*">
| <left-join cmr-field="study"
eager-load-group="*">
| </left-join>
| </left-join>
| </left-join>
| </left-join>
| <left-join cmr-field="callStatus"
eager-load-group="*"/>
| </left-join>
| <left-join cmr-field="monitoringEmp"
eager-load-group="*">
| <left-join cmr-field="dept" eager-load-group="*">
| <left-join cmr-field="parentDept"
eager-load-group="*"/>
| </left-join>
| <left-join cmr-field="title" eager-load-group="*"/>
| </left-join>
| </read-ahead>
|
The SQL generated by this query fails, appearantly because of the strange way
it combines left outer joins and WHERE clauses to join the classes together.
In the FROM clause:
FROM dash.recording t0_r,
| dash.phone_call t2_r_phoneCall,
| call_centre_view t19_r_phoneCall_callCentre LEFT OUTER JOIN dash.question
t1_r_question ON t0_r.question_id=t1_r_question.id
| LEFT OUTER JOIN...
In there WHERE clause:
...AND t0_r.phone_call_id=t2_r_phoneCall.id
| AND t2_r_phoneCall.call_centre_id=t19_r_phoneCall_callCentre.id))
| AND t0_r.phone_call_id=t2_r_phoneCall.id
The resulting error is:
anonymous wrote : javax.ejb.FinderException: Find failed:
java.sql.SQLException: ERROR: relation "t0_r" does not exist
Is there something wrong with the way I'm setting up the left-joins, or might
this be a bug in the sql generation code?
Thanks in advance for any help,
Chris Niermeier
View the original post :
http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3964660#3964660
Reply to the post :
http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=3964660
_______________________________________________
jboss-user mailing list
jboss-user@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/jboss-user
