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 15:41:47 GMT
I dont know what happened but the SQLTemplate started to work. All I  
did is I converted all the table names and the attribute names to  
upper case, but I think that 'ant clean' on our project might have  
some impact as well.

Thanks
Marcin

On 14/05/2007, at 4:08 PM, Andrus Adamchik wrote:

> 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.prepareStat 
>>> ement(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.interceptPaginatedQ 
>>> uery(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
>>
>>
>>
>>
>

Marcin




Mime
View raw message