CMIS query with a date

[agz]

Hi all,
I have my Alfresco Community 4.0.e with Solr and I try to get documents through CMIS.

I have my custom model defines a metadata (

ged:startDate‍

) with a d:date type (like cmis:creationDate).

When i try to execute a query through CMIS workbench (for testing query) I'm not able to compare date:

SELECT D.* FROM cmis:document as D JOIN ged:cotractModel AS C ON D.cmis:objectId = C.cmis:objectId  C.ged:startDate<TIMESTAMP '2013-05-22T13:59.54+02.00'‍‍‍‍‍‍

The workbench give me "Internal server error"

(The same query can be executed using

D.cmis:creationDate‍

instead

C.ged:startDate‍

)

In my Alfresco I have setted

d_dictionary.datatype.d_datetime.analyzer=org.alfresco.repo.search.impl.lucene.analysis.DateTimeAnalyser‍

but I have same errors.

Where is the problem?

I hope someone can helps me.
Thanks.

[agz]

Anyone can help me???

[jspuchau]

From what I see, I'm not sure if your datetime format is correct.

In similar queries I use a format like this one: cmis:lastModificationDate < TIMESTAMP '2014-05-22T00:00:00.000+00:00' that is different than yours.

Anyway, you should try to get debug log traces or a description on the Internal Server Error received.

Regards,

[agz]

I tried with your sintax

SELECT C.*FROM cmis:document as D  JOIN ged:contractModel AS C ON D.cmis:objectId = C.cmis:objectId  WHERE (IN_FOLDER(D, 'workspace://SpacesStore/f8e680b8-11b9-4efb-93fa-65a090173813') C.ged.startDate < TIMESTAMP '2014-05-22T00:00:00.000+00:00'‍‍‍‍‍‍‍‍‍

but I have this log:

27-mag-2013 11.41.36 org.apache.solr.common.SolrException logGRAVE: org.apache.chemistry.opencmis.commons.exceptions.CmisInvalidArgumentException: line 1:422 no viable alternative at input '.'    (decision=28 state 78) decision=<<569:1: predicate : ( comparisonPredicate | inPredicate | likePredicate | nullPredicate | quantifiedComparisonPredicate | quantifiedInPredicate | textSearchPredicate | folderPredicate );>> in where   [query, whereClause, searchOrCondition, searchAndCondition, searchNotCondition, searchTest, predicate]   at org.alfresco.repo.search.impl.parsers.CMISParser.predicate(CMISParser.java:3347)   at org.alfresco.repo.search.impl.parsers.CMISParser.searchTest(CMISParser.java:3099)   at org.alfresco.repo.search.impl.parsers.CMISParser.searchNotCondition(CMISParser.java:3015)   at org.alfresco.repo.search.impl.parsers.CMISParser.searchAndCondition(CMISParser.java:2870)   at org.alfresco.repo.search.impl.parsers.CMISParser.searchOrCondition(CMISParser.java:2724)   at org.alfresco.repo.search.impl.parsers.CMISParser.whereClause(CMISParser.java:2645)   at org.alfresco.repo.search.impl.parsers.CMISParser.query(CMISParser.java:343)   at org.alfresco.opencmis.search.CMISQueryParser.parse(CMISQueryParser.java:135)   at org.alfresco.solr.AlfrescoSolrDataModel.parseCMISQueryToAlfrescoAbstractQuery(AlfrescoSolrDataModel.java:1038)   at org.alfresco.solr.query.CmisQParserPlugin$CmisQParser.parse(CmisQParserPlugin.java:88)   at org.apache.solr.search.QParser.getQuery(QParser.java:131)   at org.apache.solr.handler.component.QueryComponent.prepare(QueryComponent.java:89)   at org.apache.solr.handler.component.SearchHandler.handleRequestBody(SearchHandler.java:174)   at org.apache.solr.handler.RequestHandlerBase.handleRequest(RequestHandlerBase.java:131)   at org.apache.solr.core.SolrCore.execute(SolrCore.java:1316)   at org.apache.solr.servlet.SolrDispatchFilter.execute(SolrDispatchFilter.java:338)   at org.apache.solr.servlet.SolrDispatchFilter.doFilter(SolrDispatchFilter.java:241)   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)   at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:563)   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)   at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)   at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)   at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)   at java.lang.Thread.run(Thread.java:662)Caused by: NoViableAltException(37@[569:1: predicate : ( comparisonPredicate | inPredicate | likePredicate | nullPredicate | quantifiedComparisonPredicate | quantifiedInPredicate | textSearchPredicate | folderPredicate );])   at org.antlr.runtime.DFA.noViableAlt(DFA.java:158)   at org.antlr.runtime.DFA.predict(DFA.java:144)   at org.alfresco.repo.search.impl.parsers.CMISParser.predicate(CMISParser.java:3220)   … 29 more‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[marcus_svensson]

Your query does not look correct, you are missing an AND and a paranthesis.

SELECT C.* FROM cmis:document as D  JOIN ged:contractModel AS C ON D.cmis:objectId = C.cmis:objectId   WHERE (IN_FOLDER(D, 'workspace://SpacesStore/f8e680b8-11b9-4efb-93fa-65a090173813') ANDC.ged.startDate < TIMESTAMP '2014-05-22T00:00:00.000+00:00')‍‍‍‍‍‍‍‍‍

[agz]

Now it works!
Thanks marcus.

Probably my problem was timestamp format and ( ) !

[raghav]

Does the above query work with equal comparison of time?