OpenJPA
  1. OpenJPA
  2. OPENJPA-84

Escape sql reserved words in column names

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: sql
    • Labels:
      None

      Description

      OpenJPA does not escape reserved words in SQL queries when they are used as column names. To escape a reserved name you just put it in quotes:

      INSERT INTO FB_PRIVILEGES (comment, inheritable, level, role, type, username, item_uuid) ...

      on oracle, becomes

      INSERT INTO FB_PRIVILEGES ("comment", inheritable, "level", role, "type", username, item_uuid) ...

      Looking at the code in DBDictionary, it appears that the escaping is done for table and sequence names by appending a numeral to the name. There isn't really any good reason to do this instead of quoting the identifier.

      table.name identifiers would have to be escaped as "table"."name" if both table and name were reserved words.

        Issue Links

          Activity

          Hide
          Knut-Håvard Aksnes added a comment -

          This is also a problem for table names. I am at the time being trying to interface a legacy database where I have no control of the database schema at all. The database contains a table with the name USER giving me all kinds of problems.
          I am using geronimo 2.2.1 (OpenJPA 1.2.2 AFAIK)

          Show
          Knut-Håvard Aksnes added a comment - This is also a problem for table names. I am at the time being trying to interface a legacy database where I have no control of the database schema at all. The database contains a table with the name USER giving me all kinds of problems. I am using geronimo 2.2.1 (OpenJPA 1.2.2 AFAIK)
          Hide
          Simone Gianni added a comment -

          This issue is big because it also limits portability to different databases. For example, having an entity named "User" is not a problem in MySql or Hsql, but is an error in Derby.

          While quoted names makes SQL less readable, having to name your entities depending on the kind of database or adding table annotations for each entity just to make sure they don't cause errors makes it far more ugly.

          Show
          Simone Gianni added a comment - This issue is big because it also limits portability to different databases. For example, having an entity named "User" is not a problem in MySql or Hsql, but is an error in Derby. While quoted names makes SQL less readable, having to name your entities depending on the kind of database or adding table annotations for each entity just to make sure they don't cause errors makes it far more ugly.
          Hide
          Tobias Bergman added a comment -

          I am missing this feature too but for MS SQL Server. SQL Server escapes the table and column names with [abc].
          INSERT INTO [FB_PRIVILEGES] ([comment], [inheritable]...

          Show
          Tobias Bergman added a comment - I am missing this feature too but for MS SQL Server. SQL Server escapes the table and column names with [abc] . INSERT INTO [FB_PRIVILEGES] ( [comment] , [inheritable] ...
          Hide
          Patrick Linskey added a comment -

          While quoting all words would be an easy implementation, it makes the SQL a bit less readable. Should we maybe make an option for whether to quote everything or just quote reserved words?

          Show
          Patrick Linskey added a comment - While quoting all words would be an easy implementation, it makes the SQL a bit less readable. Should we maybe make an option for whether to quote everything or just quote reserved words?
          Hide
          Patrick Linskey added a comment -

          Changing to major because I think that getting rid of the trailing 0 would be very very nice. Backwards-compatibility for people upgrading will need some consideration, though.

          Show
          Patrick Linskey added a comment - Changing to major because I think that getting rid of the trailing 0 would be very very nice. Backwards-compatibility for people upgrading will need some consideration, though.
          Hide
          Roger Keays added a comment -

          A simple way to implement this would be to quote all identifiers. That would also mean less work maintaining a list of reserved words.

          Show
          Roger Keays added a comment - A simple way to implement this would be to quote all identifiers. That would also mean less work maintaining a list of reserved words.

            People

            • Assignee:
              Unassigned
              Reporter:
              Roger Keays
            • Votes:
              6 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:

                Development