2 Replies Latest reply on Jun 3, 2003 4:44 PM by mverbist

    Problems with parameterized LIKE emulation

    mverbist Newbie

      Hi,

      I wanted a parameterized LIKE in EJBQL.
      Seems this isn't possible until EJB 2.1
      I found a way around for the start of a word.
      It goes like this.
      SELECT OBJECT(l) FROM Label l WHERE SUBSTRING(l.name, 1, ?2) = ?1
      Where the second parameter is the length of the like-string.
      This worked fine on MySQL.
      But now I have done a migration to Oracle, it results in an error.
      The JBoss console gives me this:
      15:43:47,984 DEBUG [findLike] Executing SQL: SELECT t0_l.ID FROM IRIS_LABELS t0_
      l WHERE substring(t0_l.NAME FROM 1 FOR ?) = ?
      15:43:47,984 DEBUG [findLike] Find failed
      java.sql.SQLException: ORA-00907: missing right parenthesis

      Anybody an idea why this would fail on Oracle and not on MySQL?

      Thx.

        • 1. Re: Problems with parameterized LIKE emulation
          Frank Langelage Master

          You may find it easier to use the jboss-ql, a superset of ejb-ql.
          One example from me (XDoclet):
          * @ejb.finder signature="java.util.Collection findByMatchcode( java.lang.String matchcode )"
          * view-type="local"
          * query="SELECT OBJECT(o) FROM Address AS o WHERE o.matchcode LIKE '%SIEMENS%'"
          * result-type-mapping="Local"
          * @jboss.query signature="java.util.Collection findByMatchcode( java.lang.String matchcode )"
          * query="SELECT OBJECT(o) FROM Address AS o WHERE o.matchcode LIKE ?1 ORDER BY o.addressId"

          • 2. Re: Problems with parameterized LIKE emulation
            mverbist Newbie

            Easyer yes.
            But less portable also.
            And since we are using JBoss for development environment and Websphere for production, I won't be able to do it this way.
            Thanks for the reply though.

            The only option I see left is to go full BMP in stead of CMP.
            Or does anyone see an other solution?