Hi Marcin,
Initially I thought the query may be losing a parameter $joinClause,
but then the SQL at the DB level would look like
SELECT ... FROM ROOM join ORDER BY Room.name
The "join" word is not a parameter and is instead hardcoded in your
template, right? And still does not show up in the output... So
there's something else. Could you post the code you are using to
build and call the query on the client?
Andrus
On May 14, 2007, at 3:39 AM, Marcin Skladaniec wrote:
Hi
I did some more debugging and I found that SQLTemplate on client :
SELECT #result('Room.name' 'java.lang.String'), #result('site.name'
'java.lang.String'), #result('Room.seatedCapacity'
'java.lang.Integer') FROM ROOM INNER JOIN SITE on ROOM.siteid =
SITE.id ORDER BY Room.name
is loosing the join part when executed on server:
[java] 10:24:24,003 [SocketListener0-0] INFO
org.apache.cayenne.access.QueryLogger :276 - SELECT Room.name,
site.name, Room.seatedCapacity FROM ROOM ORDER BY Room.name
Why is that? Is there something I'm doing wrong ?
Marcin
On 13/05/2007, at 8:29 PM, Marcin Skladaniec wrote:
Hi
In our project we needed a generic way to relate many different
entities. Cayenne does not allow vertical inheritance, therefore I
had to make my way around, and create a fake relationship. I'm
using custom superclass for entities, in which accessing methods,
like the one in cayenne are implemented (getTags/addToTags/
removeFromTags). Those methods execute SelectQuery(). I had to
expose the pk's , but it works well.
The application is using derby and ROP.
the problem we have is with creating a query like:
select
$attributes
from $entity
join $joinClause
where ((id in
(
select
TAGRELATION.ENTITYRECORDID
from TAGRELATION join tag on TAGRELATION.tagid = tag.id
where ((TAGRELATION.ENTITYIDENTIFIER = $entityCode) AND (id =
$entityId))
)) AND ($qualifier))
order by $orderby
org.apache.cayenne.exp.Expression does not support that
complexity, therefore I started to use SQLTemplate, but when query
like this is executed:
SELECT #result('Room.name' 'java.lang.String'), #result
('site.name' 'java.lang.String'), #result('Room.seatedCapacity'
'java.lang.Integer') FROM ROOM JOIN SITE on ROOM.siteid = SITE.id
exception is thrown:
org.apache.cayenne.CayenneRuntimeException: [v.3.0-SNAPSHOT Mar 29
2007 11:34:53] Remote error. URL - http://localhost:8181/angel-
server-cayenne; CAUSE - [v.3.0-SNAPSHOT Mar 29 2007 11:34:53] [v.$
{project.version} ${project.build.date} ${project.build.time}]
Exception processing message
org.apache.cayenne.remote.QueryMessage. Root cause: [v.$
{project.version} ${project.build.date} ${project.build.time}]
Error getting ResultIterator: Query Exception:
java.sql.SQLException: Column 'SITE.NAME' is either not in any
table in the FROM list or appears within a join specification and
is outside the scope of the join specification or appears in a
HAVING clause and is not in the GROUP BY list. If this is a CREATE
or ALTER TABLE statement then 'SITE.NAME' is not a column in the
target table.
at org.apache.derby.client.am.SQLExceptionFactory.getSQLException
(Unknown Source)
at org.apache.derby.client.am.SqlException.getSQLException
(Unknown Source)
at org.apache.derby.client.am.Connection.prepareStatement(Unknown
Source)
at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement
(ConnectionWrapper.java:274)
at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement
(ConnectionWrapper.java:280)
at
org.apache.cayenne.access.TransactionConnectionDecorator.prepareState
ment(TransactionConnectionDecorator.java:179)
at org.apache.cayenne.access.jdbc.SQLTemplateAction.execute
(SQLTemplateAction.java:130)
at org.apache.cayenne.access.jdbc.SQLTemplateAction.performAction
(SQLTemplateAction.java:107)
at org.apache.cayenne.access.DataNodeQueryAction.runQuery
(DataNodeQueryAction.java:57)
at org.apache.cayenne.access.DataNode.performQueries
(DataNode.java:236)
at org.apache.cayenne.access.DataDomainLegacyQueryAction.execute
(DataDomainLegacyQueryAction.java:82)
at org.apache.cayenne.access.DataDomain$1.transform
(DataDomain.java:704)
at org.apache.cayenne.access.DataDomain.runInTransaction
(DataDomain.java:802)
at org.apache.cayenne.access.DataDomain.performQueries
(DataDomain.java:698)
at
org.apache.cayenne.access.DataContext.internalPerformIteratedQuery
(DataContext.java:1261)
at org.apache.cayenne.access.DataContext.performIteratedQuery
(DataContext.java:1227)
at org.apache.cayenne.access.IncrementalFaultList.fillIn
(IncrementalFaultList.java:190)
at org.apache.cayenne.access.IncrementalFaultList.<init>
(IncrementalFaultList.java:156)
at
org.apache.cayenne.access.DataContextQueryAction.interceptPaginatedQu
ery(DataContextQueryAction.java:109)
at org.apache.cayenne.access.DataContextQueryAction.execute
(DataContextQueryAction.java:54)
at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:
1321)
at
org.apache.cayenne.access.ClientServerChannelQueryAction.runQuery
(ClientServerChannelQueryAction.java:105)
at
org.apache.cayenne.access.ClientServerChannelQueryAction.execute
(ClientServerChannelQueryAction.java:66)
at org.apache.cayenne.access.ClientServerChannel.onQuery
(ClientServerChannel.java:79)
at org.apache.cayenne.intercept.DataChannelDecorator.onQuery
(DataChannelDecorator.java:57)
at org.apache.cayenne.remote.service.DispatchHelper.dispatch
(DispatchHelper.java:40)
at
org.apache.cayenne.remote.service.BaseRemoteService.processMessage
(BaseRemoteService.java:151)
When the same query is executed directly in derby there are no
problems, so either:
- cayenne does not handle joins in SQLTemplate correctly (is
http://issues.apache.org/cayenne/browse/CAY-552 related ?)
- I'm doing something wrong
Also, was my idea to use SQLTemplate right ? I have tried to use
Expression, but I could not find a way to encode the query I need.
Is there any better way to archive what I'm after ?
Cheers
Marcin
Marcin
