OpenJPA
  1. OpenJPA
  2. OPENJPA-511

Unable to execute native queries which start with the WITH keyword.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.0.1
    • Fix Version/s: 1.0.2, 1.1.0
    • Component/s: jdbc
    • Labels:
      None
    • Environment:
      DB2

      Description

      Some databases support using WITH $

      {var} AS (SELECT . . .) SELECT ${var}

      , . . . The variable may be used in a future select statement.

      Currently OpenJPA checks whether a native query begins with "select" (case insensitive) and throws an exception if an application calls getSingleResult() or getResultList() if select isn't found. As I understand it the WITH statement may only be used in conjunction with a SELECT statement, so it should be safe to treat the WITH statement as a select.

      I'm not sure how many databases support this syntax so it might be best to limit the support to specific databases.

      The only database to support the WITH statement that I know of is DB2, but this may apply to other databases too. Additional documentation can be found in the DB2 infocenter : http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.datatools.sqlbuilder.doc/topics/ruisqlbldrwth.html.

        Activity

        Hide
        Patrick Linskey added a comment -

        I would suggest putting a List<String> into DBDictionary that contains native SQL start words, and then putting WITH into the list in the DB2Dictionary constructor, and then change the logic to delegate to a new DBDictionary method to determine if a string is a SELECT-style statement or a callable statement.

        Show
        Patrick Linskey added a comment - I would suggest putting a List<String> into DBDictionary that contains native SQL start words, and then putting WITH into the list in the DB2Dictionary constructor, and then change the logic to delegate to a new DBDictionary method to determine if a string is a SELECT-style statement or a callable statement.
        Hide
        Michael Dick added a comment -

        That's pretty much what the patch does, I used a Set instead of a List, and I didn't mess with the callable statement checking though. I'm running the tests on 1.0.x now and I'll commit when they're done.

        Show
        Michael Dick added a comment - That's pretty much what the patch does, I used a Set instead of a List, and I didn't mess with the callable statement checking though. I'm running the tests on 1.0.x now and I'll commit when they're done.

          People

          • Assignee:
            Michael Dick
            Reporter:
            Michael Dick
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development