Unable to get results from query on dates

Support
#1

Hi,

I’m not sure why but it seems that I’m unable to get results from a jpql query on dates.

This is my query:


           LoadContext loadContext = LoadContext.create(Audit.class)
                .setQuery(LoadContext.createQuery("select a from base$Audit a where a.status = :complete and " +
                                                        "(a.end >= :fromDate and a.end < :untilDate)")
                        .setParameter("complete", PlanStatus.COMPLETED)
                        .setParameter("fromDate", mr.getFrom())
                        .setParameter("untilDate", mr.getUntil()))
                .setView("audit-view");
        List<Audit> periodAuditSet = dataManager.loadList(loadContext);

The mr.getFrom() and mr.getUntil() simply return Date types.

When running the query I get this error:


JpqlSyntaxException: Errors found for input jpql:[select a from base$Audit a where a.status = :complete and (a.end >= :fromDate and a.end < :untilDate)]
CommonErrorNode [<unexpected: [@24,60:60='.',<62>,1:60], resync=a.end >= :fromDate>]
CommonErrorNode [<unexpected: [@34,83:83='.',<62>,1:83], resync=a.end < :untilDate>]

I thought it would be not too difficult but it seems very hard to get this working.
Any thoughts?

#2

Any suggestions on this one?

I know it might not be a 100% cuba platform related question but the solutions that are found on the internet for JPQL simply don’t seem to work. So I am unsure if cuba expects parameters differently.

#3

Hi,

have you looked at the JPLQ macros: Macros in JPQL - CUBA Platform. Developer’s Manual?

This should probably work: select c from sales$Customer where @between(c.createTs, now, now+1, day) - although it not directly answers your question.

Bye
Mario

#4

Hi Mario. Thanks for your reply. And I did take a look at it but it seems that I cannot replace ‘now’ with some parameter (to get the field value). The dates are not related to “now” at all.

I’ve done something like this:


.setQuery(LoadContext.createQuery("select a from base$Audit a where a.status = :complete and " +
                                                        "@between(a.end, :fromDate, :untilDate, day)")
                        .setParameter("complete", PlanStatus.COMPLETED)
                        .setParameter("fromDate", mr.getFrom(), TemporalType.DATE)
                        .setParameter("untilDate", mr.getUntil(), TemporalType.DATE))

I still get the error:


JpqlSyntaxException: Errors found for input jpql:[select a from base$Audit a where a.status <> 'OPEN' and @between(a.end, com.haulmont.cuba.core.global.TemporalValue@2a747f04, com.haulmont.cuba.core.global.TemporalValue@78f7b14d, day)]
line 1:116 no viable alternative at character '2'

When I Leave out the ‘TemporalType.DATE’, the query is filled with a full Java date expression which the JPQL doesn’t like as well:


JpqlSyntaxException: Errors found for input jpql:[select a from base$Audit a where a.status <> 'OPEN' and @between(a.end, Sun Jan 01 00:00:00 CET 2017, Fri Mar 31 00:00:00 CEST 2017, day)]
line 1:86 mismatched character '0' expecting set null
#5

Even when I use ‘hard dates’, I get this error:


JpqlSyntaxException: Errors found for input jpql:[select a from base$Audit a where a.status <> 'OPEN' and @between(a.end, '2017-01-01', '2017-03-31', day)]
CommonErrorNode [<mismatched token: [@26,67:69='end',<95>,1:67], resync=@between(a.end, '2017-01-01', '2017-03-31', day)>]
#6

Hi,

I think the problem is in the attribute name: end. And I’m not sure we will be able to fix it, because it is used for example in case … when … end operator that we are going to support in the next version.

Can you rename the attribute, say to endDate?