Torque
  1. Torque
  2. TORQUE-96

Can not use * or ? in LIKE clauses

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 3.3-RC3, 3.3
    • Fix Version/s: 4.0-beta1
    • Component/s: Runtime
    • Labels:
      None

      Description

      If you have a ? or * in a like criteria, they get replaced with "_" and "%" in the SqlExpression class. However, this causes problems if you want to search for these characters.

      If you try to escape them with a \, the SQL generated is invalid for some DB's (Like MS SQL). This causes an SQLException to be thrown with text like:

      Invalid escape sequence. Valid characters after ESC are: an ESC pair, underscore, percent sign

      My memory is that % and _ are the SQL standard wild card/match characters. So, is the bug here that since ? and * are non-standard
      they should not be processed?

      If for some reason they need to be processed, do we go down the road of having a Torque escape character so that these characters can be searched?

      But this leads to possibly requiring stuff like "\\\\" in criterias to get an escaped
      to the SQL parser so that single \ can be passed. This can be very confusing and would need to be documented correctly somewhere.

        Activity

        Hide
        Thomas Fox added a comment -
        • and ? are treaded as additional wildcards since I can remember.
          You can (now?) escape * and ? with backslashes. See org.apache.torque.DataTest.testLikeClauseEscaping()
        Show
        Thomas Fox added a comment - and ? are treaded as additional wildcards since I can remember. You can (now?) escape * and ? with backslashes. See org.apache.torque.DataTest.testLikeClauseEscaping()

          People

          • Assignee:
            Unassigned
            Reporter:
            CG Monroe
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development