Yes, but did you see my message at the bottom of the previous message?
Honestly I didn't.
Yeah, would be nice for EJBQL to handle IS NULL conversion
automatically.
(1) and (3) look like the same problem? Not quite sure about the cause
just yet.
(2) is caused by date passed as a literal. The only way to handle a
date is to pass it as a parameter, so we need to come up with some
tricks for toEJBQL to support parameters.
My suggestion would be to log a bug report with all these cases, and
use EJBQL directly without toEJBQL conversion. I don't fully
understand the IndirectQuery inheritance in your example. Such
superclass would've been needed if you'd run this query with Cayenne,
but instead you are using your own queryResult method that creates a
substitute query.
Andrus
On Aug 14, 2008, at 12:30 AM, Lachlan Deck wrote:
On 14/08/2008, at 7:02 AM, Andrus Adamchik wrote:
On Aug 13, 2008, at 11:42 PM, Lachlan Deck wrote:
(a.isDeleted = null)
A possible problem. Unlike Cayenne expressions, EJBQL does not
support "= null", it requires "IS NULL" instead. Pretty stupid :-/
Yes, but did you see my message at the bottom of the previous
message? i.e., when I regex-replace this with IS NULL ... I get
other problems. i.e., Boolean.FALSE has not been converted to
something usable and no matter what I do I keep getting parse errors.
i.e., doing the following is not enough:
String clause = this.expression.toEJBQL("a");
clause = clause.replaceAll("\\Q= null\\E", "IS NULL");
clause = clause.replaceAll("\\Q!= null\\E", "IS NOT NULL");
clause = clause.replaceAll("\\Q!=\\E", "<>");
query.append(clause);
Is there anything else that I can pass the Expression to (or result
of toEJBQL(...)) that will work?
The results after doing the above (three queries below) are that
null is still failing and other stuff doesn't work either. These are
naturally not exhaustive examples of queries that will fail... so
I'm wondering how do make this work or if it's currently possible?
(Note: I had other similar problems trying to use an SQLTemplate
with translating the expression).
1)
query --> SELECT count(a) FROM CourseClass a WHERE (a.isDeleted IS
NULL) or (a.isDeleted = false)
[java]
[java] WARNING: org.apache.cayenne.CayenneRuntimeException: [v.
3.0-SNAPSHOT Jul 09 2008 01:28:38] Query exception.
[java] org.apache.cayenne.CayenneRuntimeException: [v.3.0-
SNAPSHOT Jul 09 2008 01:28:38] Query exception.
[java] at
org
.apache
.cayenne
.access
.DataDomainQueryAction.nextQueryException(DataDomainQueryAction.java:
551)
[java] at
org.apache.cayenne.access.DataNode.performQueries(DataNode.java:237)
[java] at
org
.apache
.cayenne
.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:424)
[java] at org.apache.cayenne.access.DataDomainQueryAction.access
$0(DataDomainQueryAction.java:403)
[java] at org.apache.cayenne.access.DataDomainQueryAction
$2.transform(DataDomainQueryAction.java:397)
[java] at
org
.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:
847)
[java] at
org
.apache
.cayenne
.access
.DataDomainQueryAction
.runQueryInTransaction(DataDomainQueryAction.java:394)
[java] at
org
.apache
.cayenne
.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:120)
[java] at
org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:740)
[java] at
org
.apache
.cayenne
.util
.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:317)
[java] at
org
.apache
.cayenne
.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:
96)
[java] at
org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1336)
[java] at
org.apache.cayenne.access.DataContext.performQuery(DataContext.java:
1325)
[java] at
org
.apache.cayenne.DataObjectUtils.objectForQuery(DataObjectUtils.java:
274)
[java] at
ish.persistence.StatsQuery.queryResult(StatsQuery.java:167)
<...>
[java] Caused by: org.apache.cayenne.ejbql.EJBQLException: [v.
3.0-SNAPSHOT Jul 09 2008 01:28:38] Invalid identification variable:
null
[java] at
org
.apache
.cayenne
.access
.jdbc
.EJBQLConditionTranslator
.visitIdentificationVariable(EJBQLConditionTranslator.java:486)
[java] at
org
.apache
.cayenne
.ejbql
.parser
.EJBQLIdentificationVariable.visit(EJBQLIdentificationVariable.java:
35)
[java] at
org
.apache.cayenne.ejbql.parser.SimpleNode.visitChild(SimpleNode.java:84)
[java] at
org
.apache.cayenne.ejbql.parser.EJBQLEquals.visitChild(EJBQLEquals.java:
40)
[java] at
org.apache.cayenne.ejbql.parser.SimpleNode.visit(SimpleNode.java:64)
[java] at
org
.apache.cayenne.ejbql.parser.SimpleNode.visitChild(SimpleNode.java:84)
[java] at
org.apache.cayenne.ejbql.parser.EJBQLOr.visitChild(EJBQLOr.java:40)
[java] at
org.apache.cayenne.ejbql.parser.SimpleNode.visit(SimpleNode.java:64)
[java] at
org
.apache
.cayenne
.access.jdbc.EJBQLJoinAppender.appendTable(EJBQLJoinAppender.java:193)
[java] at
org
.apache
.cayenne
.access
.jdbc.EJBQLFromTranslator.visitFromItem(EJBQLFromTranslator.java:64)
[java] at
org
.apache
.cayenne.ejbql.parser.EJBQLFromItem.visitNode(EJBQLFromItem.java:89)
[java] at
org.apache.cayenne.ejbql.parser.SimpleNode.visit(SimpleNode.java:60)
[java] at
org
.apache.cayenne.ejbql.parser.SimpleNode.visitChild(SimpleNode.java:84)
[java] at
org.apache.cayenne.ejbql.parser.EJBQLFrom.visitChild(EJBQLFrom.java:
40)
[java] at
org.apache.cayenne.ejbql.parser.SimpleNode.visit(SimpleNode.java:64)
[java] at
org
.apache
.cayenne
.access
.jdbc.EJBQLSelectTranslator.visitFrom(EJBQLSelectTranslator.java:56)
[java] at
org.apache.cayenne.ejbql.parser.EJBQLFrom.visitNode(EJBQLFrom.java:35)
[java] at
org.apache.cayenne.ejbql.parser.SimpleNode.visit(SimpleNode.java:60)
[java] at
org
.apache.cayenne.ejbql.parser.SimpleNode.visitChild(SimpleNode.java:84)
[java] at
org.apache.cayenne.ejbql.parser.SimpleNode.visit(SimpleNode.java:64)
[java] at org.apache.cayenne.access.jdbc.EJBQLAction
$1.visitSelect(EJBQLAction.java:73)
[java] at
org
.apache.cayenne.ejbql.parser.EJBQLSelect.visitNode(EJBQLSelect.java:
35)
[java] at
org.apache.cayenne.ejbql.parser.SimpleNode.visit(SimpleNode.java:60)
[java] at
org
.apache
.cayenne.access.jdbc.EJBQLAction.performAction(EJBQLAction.java:67)
[java] at
org
.apache
.cayenne
.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:58)
[java] at
org.apache.cayenne.access.DataNode.performQueries(DataNode.java:230)
[java] ... 41 more
2)
[java] query --> SELECT count(a) FROM CourseClass a WHERE
((a.isDeleted IS NULL) or (a.isDeleted = false)) and ((a.endDateTime
>= Thu Aug 14 00:00:00 EST 2008) or (a.endDateTime IS NULL)) and
(a.isCancelled <> true)
[java]
[java] 14/08/2008 07:23:19
com.caucho.hessian.server.HessianSkeleton invoke
[java] WARNING: org.apache.cayenne.ejbql.EJBQLException: [v.3.0-
SNAPSHOT Jul 09 2008 01:28:38] Error parsing EJB QL statement
[java] org.apache.cayenne.ejbql.EJBQLException: [v.3.0-SNAPSHOT
Jul 09 2008 01:28:38] Error parsing EJB QL statement
[java] at org.apache.cayenne.ejbql.parser.EJBQL
$EJBQLDefaultParser.compile(EJBQL.java:31)
[java] at
org.apache.cayenne.query.EJBQLQuery.getExpression(EJBQLQuery.java:86)
[java] at
org
.apache
.cayenne.query.EJBQLQueryMetadata.resolve(EJBQLQueryMetadata.java:45)
[java] at
org.apache.cayenne.query.EJBQLQuery.getMetaData(EJBQLQuery.java:52)
[java] at
org
.apache
.cayenne
.util.ObjectContextQueryAction.<init>(ObjectContextQueryAction.java:
79)
[java] at
org
.apache
.cayenne
.access.DataContextQueryAction.<init>(DataContextQueryAction.java:49)
[java] at
org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1336)
[java] at
org.apache.cayenne.access.DataContext.performQuery(DataContext.java:
1325)
[java] at
org
.apache.cayenne.DataObjectUtils.objectForQuery(DataObjectUtils.java:
274)
[java] at
ish.persistence.StatsQuery.queryResult(StatsQuery.java:167)
<...>
[java] Caused by:
org.apache.cayenne.ejbql.parser.ParseException: Encountered "a .
endDateTime >= Thu Aug" at line 1, column 97.
[java] Was expecting one of:
[java] "(" ...
[java] "NOT" ...
[java] "+" ...
[java] "-" ...
[java] <DECIMAL_LITERAL> ...
[java] <INTEGER_LITERAL> ...
[java] ":" ...
[java] "?" ...
[java] "db:" ...
[java] <IDENTIFIER> "." "NEW" ...
[java] <IDENTIFIER> "." "ALL" ...
[java] <IDENTIFIER> "." "ANY" ...
[java] <IDENTIFIER> "." "EXISTS" ...
[java] <IDENTIFIER> "." "SOME" ...
[java] <IDENTIFIER> "." "EMPTY" ...
[java] <IDENTIFIER> "." "ASC" ...
[java] <IDENTIFIER> "." "DESC" ...
[java] <IDENTIFIER> "." "ORDER" ...
[java] <IDENTIFIER> "." "IS" ...
[java] <IDENTIFIER> "." "MEMBER" ...
[java] <IDENTIFIER> "." "OF" ...
[java] <IDENTIFIER> "." "LIKE" ...
[java] <IDENTIFIER> "." "ESCAPE" ...
[java] <IDENTIFIER> "." "BETWEEN" ...
[java] <IDENTIFIER> "." "NULL" ...
[java] <IDENTIFIER> "." "AVG" ...
[java] <IDENTIFIER> "." "MIN" ...
[java] <IDENTIFIER> "." "MAX" ...
[java] <IDENTIFIER> "." "SUM" ...
[java] <IDENTIFIER> "." "COUNT" ...
[java] <IDENTIFIER> "." "OR" ...
[java] <IDENTIFIER> "." "AND" ...
[java] <IDENTIFIER> "." "NOT" ...
[java] <IDENTIFIER> "." "CONCAT" ...
[java] <IDENTIFIER> "." "SUBSTRING" ...
[java] <IDENTIFIER> "." "TRIM" ...
[java] <IDENTIFIER> "." "LOWER" ...
[java] <IDENTIFIER> "." "UPPER" ...
[java] <IDENTIFIER> "." "LEADING" ...
[java] <IDENTIFIER> "." "TRAILING" ...
[java] <IDENTIFIER> "." "BOTH" ...
[java] <IDENTIFIER> "." "LENGTH" ...
[java] <IDENTIFIER> "." "LOCATE" ...
[java] <IDENTIFIER> "." "ABS" ...
[java] <IDENTIFIER> "." "SQRT" ...
[java] <IDENTIFIER> "." "MOD" ...
[java] <IDENTIFIER> "." "SIZE" ...
[java] <IDENTIFIER> "." "CURRENT_DATE" ...
[java] <IDENTIFIER> "." "CURRENT_TIME" ...
[java] <IDENTIFIER> "." "CURRENT_TIMESTAMP" ...
[java] <IDENTIFIER> "." "SELECT" ...
[java] <IDENTIFIER> "." "DISTINCT" ...
[java] <IDENTIFIER> "." "FROM" ...
[java] <IDENTIFIER> "." "UPDATE" ...
[java] <IDENTIFIER> "." "DELETE" ...
[java] <IDENTIFIER> "." "WHERE" ...
[java] <IDENTIFIER> "." "GROUP" ...
[java] <IDENTIFIER> "." "BY" ...
[java] <IDENTIFIER> "." "HAVING" ...
[java] <IDENTIFIER> "." "AS" ...
[java] <IDENTIFIER> "." "LEFT" ...
[java] <IDENTIFIER> "." "OUTER" ...
[java] <IDENTIFIER> "." "INNER" ...
[java] <IDENTIFIER> "." "JOIN" ...
[java] <IDENTIFIER> "." "FETCH" ...
[java] <IDENTIFIER> "." "IN" ...
[java] <IDENTIFIER> "." "SET" ...
[java] <IDENTIFIER> "." "OBJECT" ...
[java] <IDENTIFIER> "." <IDENTIFIER> "." ...
[java] <IDENTIFIER> "." <IDENTIFIER> "*" ...
[java] <IDENTIFIER> "." <IDENTIFIER> "/" ...
[java] <IDENTIFIER> "." <IDENTIFIER> "+" ...
[java] <IDENTIFIER> "." <IDENTIFIER> "-" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ")" ...
[java] <STRING_LITERAL> ...
[java] "SELECT" ...
[java] "EXISTS" ...
[java] <IDENTIFIER> "." <IDENTIFIER> "=" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "+" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "-" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">="
<DECIMAL_LITERAL> ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">="
<INTEGER_LITERAL> ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" ":" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "?" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "db:" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" <IDENTIFIER> "." ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "(" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "LENGTH" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "LOCATE" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "ABS" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "SQRT" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "MOD" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "SIZE" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "AVG" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "MAX" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "MIN" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "SUM" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "COUNT" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "SELECT" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "ANY" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "SOME" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "ALL" ...
[java] <IDENTIFIER> "." <IDENTIFIER> "<" ...
[java] <IDENTIFIER> "." <IDENTIFIER> "<=" ...
[java] <IDENTIFIER> "." <IDENTIFIER> "<>" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" <STRING_LITERAL> ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "CONCAT" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "SUBSTRING" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "TRIM" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "LOWER" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "UPPER" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "CURRENT_DATE" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">=" "CURRENT_TIME" ...
[java] <IDENTIFIER> "." <IDENTIFIER> ">="
"CURRENT_TIMESTAMP" ...
[java] <IDENTIFIER> "." <IDENTIFIER> "NOT" ...
[java] <IDENTIFIER> "." <IDENTIFIER> "BETWEEN" ...
[java] <IDENTIFIER> "." <IDENTIFIER> "LIKE" ...
[java] <IDENTIFIER> "." <IDENTIFIER> "IN" ...
[java] <IDENTIFIER> "." <IDENTIFIER> "IS" ...
[java] <IDENTIFIER> "." <IDENTIFIER> "MEMBER" ...
[java]
[java] at
org
.apache.cayenne.ejbql.parser.EJBQL.generateParseException(EJBQL.java:
9409)
[java] at
org.apache.cayenne.ejbql.parser.EJBQL.jj_consume_token(EJBQL.java:
9286)
[java] at
org.apache.cayenne.ejbql.parser.EJBQL.conditional_primary(EJBQL.java:
1931)
[java] at
org.apache.cayenne.ejbql.parser.EJBQL.conditional_factor(EJBQL.java:
1909)
[java] at
org.apache.cayenne.ejbql.parser.EJBQL.conditional_term(EJBQL.java:
1774)
[java] at
org
.apache.cayenne.ejbql.parser.EJBQL.conditional_expression(EJBQL.java:
1736)
[java] at
org.apache.cayenne.ejbql.parser.EJBQL.conditional_primary(EJBQL.java:
1926)
[java] at
org.apache.cayenne.ejbql.parser.EJBQL.conditional_factor(EJBQL.java:
1909)
[java] at
org.apache.cayenne.ejbql.parser.EJBQL.conditional_term(EJBQL.java:
1774)
[java] at
org
.apache.cayenne.ejbql.parser.EJBQL.conditional_expression(EJBQL.java:
1736)
[java] at
org.apache.cayenne.ejbql.parser.EJBQL.conditional_primary(EJBQL.java:
1926)
[java] at
org.apache.cayenne.ejbql.parser.EJBQL.conditional_factor(EJBQL.java:
1909)
[java] at
org.apache.cayenne.ejbql.parser.EJBQL.conditional_term(EJBQL.java:
1774)
[java] at
org.apache.cayenne.ejbql.parser.EJBQL.conditional_term(EJBQL.java:
1787)
[java] at
org
.apache.cayenne.ejbql.parser.EJBQL.conditional_expression(EJBQL.java:
1736)
[java] at
org.apache.cayenne.ejbql.parser.EJBQL.where_clause(EJBQL.java:1583)
[java] at
org.apache.cayenne.ejbql.parser.EJBQL.select_statement(EJBQL.java:67)
[java] at
org.apache.cayenne.ejbql.parser.EJBQL.parseQuery(EJBQL.java:39)
[java] at org.apache.cayenne.ejbql.parser.EJBQL
$EJBQLDefaultParser.compile(EJBQL.java:28)
[java] ... 37 more
3)
[java] query --> SELECT count(a) FROM CourseClass a WHERE
(a.isDeleted IS NULL) or (a.isDeleted = false)
[java]
[java] 14/08/2008 07:23:19
com.caucho.hessian.server.HessianSkeleton invoke
[java] WARNING: org.apache.cayenne.CayenneRuntimeException: [v.
3.0-SNAPSHOT Jul 09 2008 01:28:38] Query exception.
[java] org.apache.cayenne.CayenneRuntimeException: [v.3.0-
SNAPSHOT Jul 09 2008 01:28:38] Query exception.
[java] at
org
.apache
.cayenne
.access
.DataDomainQueryAction.nextQueryException(DataDomainQueryAction.java:
551)
[java] at
org.apache.cayenne.access.DataNode.performQueries(DataNode.java:237)
[java] at
org
.apache
.cayenne
.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:424)
[java] at org.apache.cayenne.access.DataDomainQueryAction.access
$0(DataDomainQueryAction.java:403)
[java] at org.apache.cayenne.access.DataDomainQueryAction
$2.transform(DataDomainQueryAction.java:397)
[java] at
org
.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:
847)
[java] at
org
.apache
.cayenne
.access
.DataDomainQueryAction
.runQueryInTransaction(DataDomainQueryAction.java:394)
[java] at
org
.apache
.cayenne
.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:120)
[java] at
org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:740)
[java] at
org
.apache
.cayenne
.util
.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:317)
[java] at
org
.apache
.cayenne
.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:
96)
[java] at
org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1336)
[java] at
org.apache.cayenne.access.DataContext.performQuery(DataContext.java:
1325)
[java] at
org
.apache.cayenne.DataObjectUtils.objectForQuery(DataObjectUtils.java:
274)
[java] at
ish.persistence.StatsQuery.queryResult(StatsQuery.java:167)
[java] Caused by: org.apache.cayenne.ejbql.EJBQLException: [v.
3.0-SNAPSHOT Jul 09 2008 01:28:38] Invalid identification variable:
null
with regards,
--
Lachlan Deck
