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
This archive was generated by hypermail 2.0.0 : Mon May 14 2007 - 11:42:40 EDT