You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Andrew Willerding <aw...@callistacti.com> on 2007/07/23 20:45:07 UTC
Expressions with date function
I'm trying to execute the following equivalent sql expression with
cayenne V2.0.3
select * from appointments where status < 2 and group_id = 2 and
date(dt_appointment) = '20070724'
where the intent is to select all appointments where the date matches
the provided date string. The underlying database is MySQL and the
expression above provides the correct results when directly executed.
The corresponding Appointments object contains columns named groupId
(integer), status (integer) and dtAppointment (datetime)
This is what I've coded...
Date item = Calendar.getTime();
String anExp = "groupId = " + identity +
" and status < 2" +
" and date(dtAppointment) = '" +
new
SimpleDateFormat("yyyyMMdd").format(item.getTime()) + "'";
Expression exp = Expression.fromString(anExp);
SelectQuery qAppointments = new SelectQuery(Appointments.class, exp);
List appointments = dc.performQuery(qAppointments, true);
However I am getting the following error...
Exception in thread "Thread-55"
org.apache.cayenne.exp.ExpressionException: [v.2.0.2 January 14 2007]
[v.2.0.2 January 14 2007] And: invalid child - ObjPath
at org.apache.cayenne.exp.Expression.fromString(Expression.java:203)
at
com.Callista.CallResponse.Reminder.FileServerThread.parseAndImportFileData(FileServerThread.java:261)
at
com.Callista.CallResponse.Reminder.FileServerThread.run(FileServerThread.java:128)
Caused by: org.apache.cayenne.exp.ExpressionException: [v.2.0.2 January
14 2007] And: invalid child - ObjPath
at
org.apache.cayenne.exp.parser.AggregateConditionNode.jjtAddChild(AggregateConditionNode.java:89)
at
org.apache.cayenne.exp.parser.JJTExpressionParserState.closeNodeScope(JJTExpressionParserState.java:113)
at
org.apache.cayenne.exp.parser.ExpressionParser.andCondition(ExpressionParser.java:129)
at
org.apache.cayenne.exp.parser.ExpressionParser.orCondition(ExpressionParser.java:56)
at
org.apache.cayenne.exp.parser.ExpressionParser.expression(ExpressionParser.java:49)
at org.apache.cayenne.exp.Expression.fromString(Expression.java:196)
... 2 more
What am I doing wrong in my expression builder?
Thanks,
Andrew
AW: AW: Expressions with date function
Posted by Peter Schröder <Pe...@freenet-ag.de>.
hi,
there is (i think the same) broken link on:
http://cayenne.apache.org/doc20/building-expressions.html
(formal grammar)
thx for the fast fix!
-----Ursprüngliche Nachricht-----
Von: Aristedes Maniatis [mailto:ari@maniatis.org]
Gesendet: Dienstag, 24. Juli 2007 09:17
An: user@cayenne.apache.org
Betreff: Re: AW: Expressions with date function
On 24/07/2007, at 4:45 PM, Peter Schröder wrote:
> @andrus: on http://cayenne.apache.org/doc20/expressions.html there
> is still a broken link to BNF expression language!
Just fixed. Should take about an hour to appear on the live site...
in the meantime look here: http://cayenne.apache.org/doc/bnf-for-
expressionparser.html
Ari Maniatis
-------------------------->
Aristedes Maniatis
phone +61 2 9660 9700
PGP fingerprint 08 57 20 4B 80 69 59 E2 A9 BF 2D 48 C2 20 0C C8
Re: AW: Expressions with date function
Posted by Aristedes Maniatis <ar...@maniatis.org>.
On 24/07/2007, at 4:45 PM, Peter Schröder wrote:
> @andrus: on http://cayenne.apache.org/doc20/expressions.html there
> is still a broken link to BNF expression language!
Just fixed. Should take about an hour to appear on the live site...
in the meantime look here: http://cayenne.apache.org/doc/bnf-for-
expressionparser.html
Ari Maniatis
-------------------------->
Aristedes Maniatis
phone +61 2 9660 9700
PGP fingerprint 08 57 20 4B 80 69 59 E2 A9 BF 2D 48 C2 20 0C C8
Re: AW: Expressions with date function
Posted by Andrew Willerding <aw...@callistacti.com>.
Hi Peter,
Thanks for your reply. I'm pretty sure I tried to do something like
that originally but still had errors like those I reported originally
where my expression was something like (I can't remember exactly what I had)
String anExp = "groupId = " + identity +
" and status < 2" +
" and date(dtAppointment) = '$date'" ;
Expression exp = Expression.fromString(anExp);
exp = exp.expWithParameters(Collections.singletonMap("date",
new SimpleDateFormat("yyyyMMdd").format(item.getTime())));
SelectQuery qAppointments = new
SelectQuery(Appointments.class, exp);
)
I have since worked around the problem by using the following statements
- which work but seems to me to defeat the purpose of using Cayenne.
I'm sure it's my lack of understanding on how to effectively use Cayenne
and not the capabilities of Cayenne!!
String anExp = "groupId = " + identity +
" and status < 2" +
" and dtAppointment >= '" +
new SimpleDateFormat("yyyyMMdd
00:00:00").format(item.getTime()) + "'" +
" and dtAppointment <= '" +
new SimpleDateFormat("yyyyMMdd
23:59:59").format(item.getTime()) + "'";
Expression exp = Expression.fromString(anExp);
SelectQuery qAppointments = new
SelectQuery(Appointments.class, exp);
Andrew
Peter Schröder wrote:
> hi andrew,
>
> i am not totally clear about that, but afaik you may not use that date() function in your expression. cayenne will put the right string of a date object to your query, if you pass a date as a parameter.
> take a look at named parameter expressions: http://cayenne.apache.org/doc20/building-expressions.html
>
>
> @andrus: on http://cayenne.apache.org/doc20/expressions.html there is still a broken link to BNF expression language!
>
>
AW: Expressions with date function
Posted by Peter Schröder <Pe...@freenet-ag.de>.
hi andrew,
i am not totally clear about that, but afaik you may not use that date() function in your expression. cayenne will put the right string of a date object to your query, if you pass a date as a parameter.
take a look at named parameter expressions: http://cayenne.apache.org/doc20/building-expressions.html
@andrus: on http://cayenne.apache.org/doc20/expressions.html there is still a broken link to BNF expression language!
-----Ursprüngliche Nachricht-----
Von: Andrew Willerding [mailto:awillerding@callistacti.com]
Gesendet: Montag, 23. Juli 2007 20:45
An: user@cayenne.apache.org
Betreff: Expressions with date function
I'm trying to execute the following equivalent sql expression with
cayenne V2.0.3
select * from appointments where status < 2 and group_id = 2 and
date(dt_appointment) = '20070724'
where the intent is to select all appointments where the date matches
the provided date string. The underlying database is MySQL and the
expression above provides the correct results when directly executed.
The corresponding Appointments object contains columns named groupId
(integer), status (integer) and dtAppointment (datetime)
This is what I've coded...
Date item = Calendar.getTime();
String anExp = "groupId = " + identity +
" and status < 2" +
" and date(dtAppointment) = '" +
new
SimpleDateFormat("yyyyMMdd").format(item.getTime()) + "'";
Expression exp = Expression.fromString(anExp);
SelectQuery qAppointments = new SelectQuery(Appointments.class, exp);
List appointments = dc.performQuery(qAppointments, true);
However I am getting the following error...
Exception in thread "Thread-55"
org.apache.cayenne.exp.ExpressionException: [v.2.0.2 January 14 2007]
[v.2.0.2 January 14 2007] And: invalid child - ObjPath
at org.apache.cayenne.exp.Expression.fromString(Expression.java:203)
at
com.Callista.CallResponse.Reminder.FileServerThread.parseAndImportFileData(FileServerThread.java:261)
at
com.Callista.CallResponse.Reminder.FileServerThread.run(FileServerThread.java:128)
Caused by: org.apache.cayenne.exp.ExpressionException: [v.2.0.2 January
14 2007] And: invalid child - ObjPath
at
org.apache.cayenne.exp.parser.AggregateConditionNode.jjtAddChild(AggregateConditionNode.java:89)
at
org.apache.cayenne.exp.parser.JJTExpressionParserState.closeNodeScope(JJTExpressionParserState.java:113)
at
org.apache.cayenne.exp.parser.ExpressionParser.andCondition(ExpressionParser.java:129)
at
org.apache.cayenne.exp.parser.ExpressionParser.orCondition(ExpressionParser.java:56)
at
org.apache.cayenne.exp.parser.ExpressionParser.expression(ExpressionParser.java:49)
at org.apache.cayenne.exp.Expression.fromString(Expression.java:196)
... 2 more
What am I doing wrong in my expression builder?
Thanks,
Andrew