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
>
>
>
>
This archive was generated by hypermail 2.0.0 : Mon May 14 2007 - 02:08:44 EDT