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.prepareStatem
> ent(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.interceptPaginatedQue
> ry(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
This archive was generated by hypermail 2.0.0 : Sun May 13 2007 - 20:40:31 EDT