9 Replies Latest reply on Oct 9, 2002 1:39 PM by aloubyansky

    JBoss 3.0.3/EJBQL - comparing dates

    noel.rocher

      Hi all,


      here is my EJBQL query :

      <ejb-ql>
      <![CDATA[select object(a) FROM Country AS c
        WHERE
          c.row_valid_to_date IS NOT NULL AND
      (c.row_valid_to_date < ?1 OR
      c.row_valid_to_date = ?1) ]]>
      </ejb-ql>



      Here is the message I have :

      org.jboss.deployment.DeploymentException: Error compiling ejbql; - nested throwable: (org.jboss.ejb.plugins.cmp.ejbql.ParseException: Encountered "1" at line 2, column 112.
      Was expecting:
      <STRING_LITERAL> ...
      )


      What is wrong ?
      Thanks

        • 1. Re: JBoss 3.0.3/EJBQL - comparing dates
          noel.rocher

          Sorry I forgot this :


          the parameter is declared as :
          <method-param>java.sql.Timestamp</method-param>

          the database is MSSQL2000 and the column type is datetime

          • 2. Re: JBoss 3.0.3/EJBQL - comparing dates
            scoy

            Date comparison is explicitly not supported by the current EJB spec.

            However, all is not lost, because jboss-ql supports it, as well as other nifty things like ORDER BY.

            See the JBoss CMP doco for details.

            • 3. Re: JBoss 3.0.3/EJBQL - comparing dates
              noel.rocher

              Hi SCOY,

              Here is what you can find in the page #240 of the EJB2.0
              spec :

              comparison_expression ::=
              string_value { =|<>} string_expression |
              boolean_value { =|<>} boolean_expression} |
              datetime_value { = | <> | > | < } datetime_expression |
              entity_bean_value { = | <> } entity_bean_expression |
              arithmetic_value comparison_operator single_value_designator

              As you can see, datetime comparison is described.

              • 4. Re: JBoss 3.0.3/EJBQL - comparing dates
                noel.rocher

                The spec says :

                11.2.11 Restrictions
                Date and time values should use the standard Java long millisecond value. The standard way to produce
                millisecond values is to use java.util.Calendar.

                Is this means that if declare my parameter as to be a java.lang.Long and if I translate my parameter with java.util.Calendar, this will work ?

                • 5. Re: JBoss 3.0.3/EJBQL - comparing dates
                  aloubyansky

                  The following works for me:

                  <query-method>
                  <method-name>findByDatePeriod</method-name>
                  <method-params>
                  <method-param>java.sql.Timestamp</method-param>
                  </method-params>
                  </query-method>
                  <ejb-ql><![CDATA[SELECT OBJECT(cmp) FROM CmpBean AS cmp WHERE cmp.dateField IS NOT NULL AND (cmp.dateField < ?1 OR cmp.dateField = ?1)]]></ejb-ql>

                  in JBoss-3.0.4RC1.

                  • 6. Re: JBoss 3.0.3/EJBQL - comparing dates
                    noel.rocher

                    Hi,

                    I found that there was several problems in my application. I'm migrating one that works fine on weblogic 6.1.
                    The key is to understand the spec. With JBoss it's strictly and well defined where the spec stops and where JBoss goes ahead.
                    With Weblogic it's not so clear. Some things work (as a "like ?1" in EJB-QL) that are extensions and you never know it.
                    Thanks to JBoss for its jboss-ql that work around some undefined functionalities.
                    Welcome to the 2.1 spec.

                    By the way : thanks for all your work and giving it freely.

                    • 7. Re: JBoss 3.0.3/EJBQL - comparing dates
                      noel.rocher

                      Sorry, the most important :

                      Dates in an ejb-ql "where" clause works fine.

                      ( Scoy, take care when you reply ;o)

                      • 8. Re: JBoss 3.0.3/EJBQL - comparing dates
                        scoy

                        Hmm,

                        I think I've misunderstood what Monson-Haefel has said in his book "Enterprise JavaBeans". On page 252, he says "EJB QL doesn't provide native support for the java.util.Date class" and I've somehow extended that to the java.sql.Date class family as well, probably because of what 11.2.11 says: "Date and time values should use the standard Java long millisecond value".

                        I took that to mean that dates needed to be stored as a numeric type, rather than a DATE column, and that we could subsequently only compare milliseconds with milliseconds.

                        I've spent the last nine months with this particular misunderstanding, so I was pretty sure I knew what I was talking about. Oh well.

                        I'm glad that you sorted it (and me) out.

                        Steve Coy

                        • 9. Re: JBoss 3.0.3/EJBQL - comparing dates
                          aloubyansky

                          As I understand it, the spec means that dates are compared by its millisecond values but not by year, month, day and so.