using mysql functions in queries

View: New views

2 Messages — Rating Filter: Alert me

using mysql functions in queries

by Derek Knapp-3 :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

is it possible to do something like

select t from Tasklist t where t.userid = :user and t.taskstatus = 'open' and cast(t.duetime as date) = :today order by t.duetime

this is the exception I get when I try. I assume cast is mysql specific?

Exception Description: Syntax error parsing the query [select t from Tasklist t where t.userid = :user and t.taskstatus = 'open' and cast(t.duetime as date) = :today order by t.duetime], line 1, column 78: unexpected token [cast].
Internal Exception: NoViableAltException(66!=[792:1: arithmeticPrimary returns [Object node] : ({...}?n= aggregateExpression | n= variableAccess | n= stateFieldPathExpression | n= functionsReturningNumerics | n= functionsReturningDatetime | n= functionsReturningStrings | n= inputParameter | n= literalNumeric | n= literalString | n= literalBoolean | LEFT_ROUND_BRACKET n= simpleArithmeticExpression RIGHT_ROUND_BRACKET );])
        at org.eclipse.persistence.exceptions.JPQLException.unexpectedToken(JPQLException.java:365)
        at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.handleRecognitionException(JPQLParser.java:319)
        at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.addError(JPQLParser.java:245)
        at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.reportError(JPQLParser.java:362)
        at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.arithmeticPrimary(JPQLParser.java:8904)
        at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.arithmeticFactor(JPQLParser.java:8620)
        at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.arithmeticTerm(JPQLParser.java:8444)
        at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.simpleArithmeticExpression(JPQLParser.java:8351)
        at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.arithmeticExpression(JPQLParser.java:8292)
        at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.simpleConditionalExpression(JPQLParser.java:7039)
        at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.conditionalPrimary(JPQLParser.java:6997)
        at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.conditionalFactor(JPQLParser.java:2989)
        at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.conditionalTerm(JPQLParser.java:2903)
        at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.conditionalExpression(JPQLParser.java:2805)
        at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.whereClause(JPQLParser.java:2762)
        at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.selectStatement(JPQLParser.java:356)
        at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.document(JPQLParser.java:261)
        at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.parse(JPQLParser.java:133)
        at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.buildParseTree(JPQLParser.java:94)
        at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:203)
        at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:170)
        at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:134)
        at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:95)
        at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:80)
        at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:740)
        ... 96 more
Caused by: NoViableAltException(66!=[792:1: arithmeticPrimary returns [Object node] : ({...}?n= aggregateExpression | n= variableAccess | n= stateFieldPathExpression | n= functionsReturningNumerics | n= functionsReturningDatetime | n= functionsReturningStrings | n= inputParameter | n= literalNumeric | n= literalString | n= literalBoolean | LEFT_ROUND_BRACKET n= simpleArithmeticExpression RIGHT_ROUND_BRACKET );])
        at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.arithmeticPrimary(JPQLParser.java:8678)
        ... 116 more

_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

Re: using mysql functions in queries

by tch :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

I believe you'd need to do a Native Query for this, not JPQL.

./tch

On Fri, Oct 23, 2009 at 2:09 PM, Derek Knapp <derek@...> wrote:

> is it possible to do something like
>
> select t from Tasklist t where t.userid = :user and t.taskstatus = 'open'
> and cast(t.duetime as date) = :today order by t.duetime
>
> this is the exception I get when I try. I assume cast is mysql specific?
>
>
>
>
> Exception Description: Syntax error parsing the query [select t from
> Tasklist t where t.userid = :user and t.taskstatus = 'open' and
> cast(t.duetime as date) = :today order by t.duetime], line 1, column 78:
> unexpected token [cast].
> Internal Exception: NoViableAltException(66!=[792:1: arithmeticPrimary
> returns [Object node] : ({...}?n= aggregateExpression | n= variableAccess |
> n= stateFieldPathExpression | n= functionsReturningNumerics | n=
> functionsReturningDatetime | n= functionsReturningStrings | n=
> inputParameter | n= literalNumeric | n= literalString | n= literalBoolean |
> LEFT_ROUND_BRACKET n= simpleArithmeticExpression RIGHT_ROUND_BRACKET );])
>         at
> org.eclipse.persistence.exceptions.JPQLException.unexpectedToken(JPQLException.java:365)
>         at
> org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.handleRecognitionException(JPQLParser.java:319)
>         at
> org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.addError(JPQLParser.java:245)
>         at
> org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.reportError(JPQLParser.java:362)
>         at
> org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.arithmeticPrimary(JPQLParser.java:8904)
>         at
> org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.arithmeticFactor(JPQLParser.java:8620)
>         at
> org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.arithmeticTerm(JPQLParser.java:8444)
>         at
> org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.simpleArithmeticExpression(JPQLParser.java:8351)
>         at
> org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.arithmeticExpression(JPQLParser.java:8292)
>         at
> org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.simpleConditionalExpression(JPQLParser.java:7039)
>         at
> org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.conditionalPrimary(JPQLParser.java:6997)
>         at
> org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.conditionalFactor(JPQLParser.java:2989)
>         at
> org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.conditionalTerm(JPQLParser.java:2903)
>         at
> org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.conditionalExpression(JPQLParser.java:2805)
>         at
> org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.whereClause(JPQLParser.java:2762)
>         at
> org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.selectStatement(JPQLParser.java:356)
>         at
> org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.document(JPQLParser.java:261)
>         at
> org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.parse(JPQLParser.java:133)
>         at
> org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.buildParseTree(JPQLParser.java:94)
>         at
> org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:203)
>         at
> org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:170)
>         at
> org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:134)
>         at
> org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:95)
>         at
> org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:80)
>         at
> org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:740)
>         ... 96 more
> Caused by: NoViableAltException(66!=[792:1: arithmeticPrimary returns
> [Object node] : ({...}?n= aggregateExpression | n= variableAccess | n=
> stateFieldPathExpression | n= functionsReturningNumerics | n=
> functionsReturningDatetime | n= functionsReturningStrings | n=
> inputParameter | n= literalNumeric | n= literalString | n= literalBoolean |
> LEFT_ROUND_BRACKET n= simpleArithmeticExpression RIGHT_ROUND_BRACKET );])
>         at
> org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.arithmeticPrimary(JPQLParser.java:8678)
>         ... 116 more
>
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@...
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>
>

_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users