cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Marcin Skladaniec <mar...@ish.com.au>
Subject Re: complex query and SQLtemplate
Date Mon, 14 May 2007 00:39:39 GMT
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




Mime
View raw message