8 Replies Latest reply on Nov 22, 2002 12:58 PM by james

    EJB QL LIKE QUERY

    Chris Newbie

      Hi,

      In EJB2.0 there is no way to use LIKE with a variable.
      e.g.
      where o.param LIKE '%?1%'

      Reading about 2.1 on the Server Side I could not see any mention of it being a planned/new feature either.

      I did find a reference to something in O'Reilly saying that LOCATE could be used instead, as follows:
      where LOCATE(o.param, ?1) > -1
      but this failed the ejb-ql parser in JBoss3.0.3 as well.

      Does anyone have a workaround to get this working in EJB-QL?

      I know I could perhaps do it in JBoss-QL but I need my application to also work in WebLogic.

      TIA

      CNS.
      ----

        • 1. Re: EJB QL LIKE QUERY
          Chris Newbie

          do I take that to mean it can't be done?

          Can't believe I'm the only one wanting to do such a fundamental thing

          Thanks

          C.
          --

          • 2. Re: EJB QL LIKE QUERY
            Schneider Newbie

            the ejb-ql uses a PreparedStatment for executing the sql-query,
            and as far as I know the PreparedStatement do not support parameters in the LIKE operator.

            greetings,
            rudi

            • 3. Re: EJB QL LIKE QUERY
              Noel Rocher Apprentice

              Hi CNS (what a strange name!),

              The EJB2.0 spec is :

              ===========================================
              11.2.7.9 Like expressions
              The syntax for the use of the comparison operator [NOT] LIKE in a conditional expression is as follows:
              single_valued_path_expression [NOT] LIKE pattern-value [ESCAPE escape-character]
              The single_valued_path_expression must have a String value. The pattern-value is a string literal in
              which an underscore (_) stands for any single character, a percent (%) character stands for any sequence
              of characters (including the empty sequence), and all other characters stand for themselves. The
              optional escape-characteris a single character string literal and is used to escape the special meaning of
              the underscore and percent characters in pattern-value.
              ================================

              Note the pattern-value definition


              In EJB 2.1 it changes to :

              =================================
              11.2.6.9 Like expressions
              The syntax for the use of the comparison operator [NOT] LIKE in a conditional expression is as follows:

              cmp_path_expression [NOT] LIKE pattern_value [ESCAPE escape_character]

              The cmp_path_expressionmust have a string value. The pattern_value is a string literal or a string-valued
              input parameter
              in which an underscore (_) stands for any single character, a percent (%) character
              stands for any sequence of characters (including the empty sequence), and all other characters stand for
              themselves. The optional escape_character is a single-character string literal or a character-valued
              input parameter (i.e., char or Character) and is used to escape the special meaning of the underscore
              and percent characters in pattern_value.[23]

              ==========================

              So calm down, it comes !!! ;o)

              Noel

              • 4. Re: EJB QL LIKE QUERY
                Noel Rocher Apprentice

                Hi CNS (what a strange name!),

                The EJB2.0 spec is :

                ===========================================
                11.2.7.9 Like expressions
                The syntax for the use of the comparison operator [NOT] LIKE in a conditional expression is as follows:
                single_valued_path_expression [NOT] LIKE pattern-value [ESCAPE escape-character]
                The single_valued_path_expression must have a String value. The pattern-value is a string literal in
                which an underscore (_) stands for any single character, a percent (%) character stands for any sequence
                of characters (including the empty sequence), and all other characters stand for themselves. The
                optional escape-characteris a single character string literal and is used to escape the special meaning of
                the underscore and percent characters in pattern-value.
                ================================

                Note the pattern-value definition


                In EJB 2.1 it changes to :

                =================================
                11.2.6.9 Like expressions
                The syntax for the use of the comparison operator [NOT] LIKE in a conditional expression is as follows:

                cmp_path_expression [NOT] LIKE pattern_value [ESCAPE escape_character]

                The cmp_path_expressionmust have a string value. The pattern_value is a string literal or a string-valued
                input parameter
                in which an underscore (_) stands for any single character, a percent (%) character
                stands for any sequence of characters (including the empty sequence), and all other characters stand for
                themselves. The optional escape_character is a single-character string literal or a character-valued
                input parameter (i.e., char or Character) and is used to escape the special meaning of the underscore
                and percent characters in pattern_value.[23]

                ==========================

                So calm down, it comes !!!
                Maybe in JBoss 4.

                ;o)

                Noel

                • 5. Re: EJB QL LIKE QUERY
                  Noel Rocher Apprentice

                  Hi CNS (what a strange name!),

                  The EJB2.0 spec is :

                  ===========================================
                  11.2.7.9 Like expressions
                  The syntax for the use of the comparison operator [NOT] LIKE in a conditional expression is as follows:
                  single_valued_path_expression [NOT] LIKE pattern-value [ESCAPE escape-character]
                  The single_valued_path_expression must have a String value. The pattern-value is a string literal in
                  which an underscore (_) stands for any single character, a percent (%) character stands for any sequence
                  of characters (including the empty sequence), and all other characters stand for themselves. The
                  optional escape-characteris a single character string literal and is used to escape the special meaning of
                  the underscore and percent characters in pattern-value.
                  ================================

                  Note the pattern-value definition


                  In EJB 2.1 it changes to :

                  =================================
                  11.2.6.9 Like expressions
                  The syntax for the use of the comparison operator [NOT] LIKE in a conditional expression is as follows:

                  cmp_path_expression [NOT] LIKE pattern_value [ESCAPE escape_character]

                  The cmp_path_expressionmust have a string value. The pattern_value is a string literal or a string-valued
                  input parameter
                  in which an underscore (_) stands for any single character, a percent (%) character
                  stands for any sequence of characters (including the empty sequence), and all other characters stand for
                  themselves. The optional escape_character is a single-character string literal or a character-valued
                  input parameter (i.e., char or Character) and is used to escape the special meaning of the underscore
                  and percent characters in pattern_value.[23]

                  ==========================

                  So calm down, it comes !!!
                  Maybe in JBoss 4.

                  ;o)

                  Noel

                  • 6. Re: EJB QL LIKE QUERY
                    Brad Leupen Newbie

                    you are correct that you cannot do this trivial thing in standard ejb-ql (check the sun forums).

                    i would suggest using jboss-cmp to get the job done but factor your code in such a way that you can substitute a BMP-type finder if you need to support a different application server.

                    i've written "query" home methods that do this. e.g. declare the method "queryByXXXByYYY()" on your Home interface but don't implement it on your standard bean class. instead, subclass your bean class with a JBossBean.java class that implements the home method (i.e. ejbHomeQueryByXXXByYYY()) using dynamic jboss-ejbql.

                    b

                    • 7. Re: EJB QL LIKE QUERY
                      Chris Newbie

                      Hi,

                      Thanks for the all replies!

                      I cannot wait for EJB-QL2.1 (literally!) - according to WebLogic, EJB-QL2.1 isn't planned to be supported until Spring 2004 (which probably means December 2004!), and I need to make sure that it runs on JBoss *and* WebLogic.

                      WebLogic QL probably already supports the LIKE query (I need to investigate) - is it possible to already have a finder which uses JBoss-QL if deployed on JBoss and WebLogic-QL if deployed on WebLogic, and (possibly) defaults to standard EJB-QL if deployed on anything else?

                      That's plan B anyway

                      Cheers

                      C.
                      --

                      • 8. Re: EJB QL LIKE QUERY
                        james Newbie

                        as far as I know,
                        LOCATE works in JBoss
                        You should tell the db server you plan to use
                        The postgresql server "LOCATE" function is not working
                        I have post one fix on forum(but no one notice)
                        Anyway,"Locate" workaround can work for me with postgres

                        James