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
