OpenJPA
  1. OpenJPA
  2. OPENJPA-1189

Column names do not contain delimiters when obtained from DatabaseMetaData

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0.0-beta
    • Fix Version/s: 2.0.0-beta
    • Component/s: jdbc
    • Labels:
      None
    • Environment:
      DB2 (could exist on other databases)

      Description

      The DatabaseMetaData returned by DB2 does not include delimiters for column names which include whitespace. This causes potential duplicate column names if we merge into an existing schema.

      The problem can be illustrated by running the TestManualDelimitedJoinAnnotations test twice sequentially. The first time the test is executed the database will be clean (presumably) and everything will work. Subsequent executions read the existing column information from the database (non delimited) and end up creating a duplicate column.

      Example stacktrace :
      <openjpa-2.0.0-SNAPSHOT-r422266:794075M nonfatal general error> org.apache.openjpa.persistence.PersistenceException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=);entityCs_c id OTHER;<references_spec>, DRIVER=3.50.152

      {stmnt 10751758 CREATE TABLE "delim id"."c d" ("entityCs_c id" INTEGER, ENTITYDS_ID INTEGER, entityCs_c id OTHER)}

      [code=-104, state=42601]
      at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:553)
      at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:453)
      <snip>

      To resolve the problem we can detect a column / table / schema name which contains whitespace and add the appropriate delimiters (when the name is provided by the database). One place to make this change is in DBDictionary.newColumn()
      protected Column newColumn(ResultSet colMeta)
      throws SQLException {
      Column c = new Column();
      c.setSchemaName(colMeta.getString("TABLE_SCHEM"));
      c.setTableName(colMeta.getString("TABLE_NAME"));
      c.setName(colMeta.getString("COLUMN_NAME")); // detect whether we need delimiters and add them

      It's also worth noting that the testcase drops the offending table and then recreates it. There may be a larger issue where the metadata is cached improperly (could be DB2 specific).

      1. patch.txt
        17 kB
        Dianne Richards

        Activity

        Hide
        Dianne Richards added a comment -

        >To resolve the problem we can detect a column / table / schema name which contains whitespace and add the appropriate delimiters (when the name is provided by the database).

        It probably won't be this simple. There could be other characters (other than whitespace) that need delimiting. Also, there could be lowercase characters.

        Show
        Dianne Richards added a comment - >To resolve the problem we can detect a column / table / schema name which contains whitespace and add the appropriate delimiters (when the name is provided by the database). It probably won't be this simple. There could be other characters (other than whitespace) that need delimiting. Also, there could be lowercase characters.
        Hide
        Michael Dick added a comment -

        Hi Dianne,

        I figured you'd know when we need delimiters better than I do (although I'm surprised that mixed case would be one of them). I imagine DBDictionary could do with a boolean needsDelimiters(String identifier) method, or String delimitIfNeeded(String identifier). A quick grep through DBDictionary didn't turn one up (I could have missed it though).

        Show
        Michael Dick added a comment - Hi Dianne, I figured you'd know when we need delimiters better than I do (although I'm surprised that mixed case would be one of them). I imagine DBDictionary could do with a boolean needsDelimiters(String identifier) method, or String delimitIfNeeded(String identifier). A quick grep through DBDictionary didn't turn one up (I could have missed it though).
        Hide
        Dianne Richards added a comment -

        Attaching patch for review and possibly commit.

        This problem occurred when a column name retrieved from the DB metadata was passed as a parameter to the Table.getColumn(String name) to retrieve the Column object. The column name retrieved from the DB does not contain delimiters and this info apparently cannot be obtained. The Table object was created using input metadata from annotations and xml, and might contain delimiters for the Map keys. Therefore, the name might not be found. So, the fix is to delimit the input name if it is initially not found.

        Changes were made in the Table getColumn() and containsColumn() methods to take DBDictionary as a parameter, from which the DB-specific delimiter is retrieved. However, some calling methods do not currently have access to the DBDictioinary and pass in null. In this scenario, the double-quote is used as the delimiter. While this is spec-complient, this may not work for some dbs, which is not good. So, I've opened jira OPENJPA-1236 to handle this in the future. I suspect some chaining of changes in method signatures to allow a DBDictionary parameter will fix this.

        Show
        Dianne Richards added a comment - Attaching patch for review and possibly commit. This problem occurred when a column name retrieved from the DB metadata was passed as a parameter to the Table.getColumn(String name) to retrieve the Column object. The column name retrieved from the DB does not contain delimiters and this info apparently cannot be obtained. The Table object was created using input metadata from annotations and xml, and might contain delimiters for the Map keys. Therefore, the name might not be found. So, the fix is to delimit the input name if it is initially not found. Changes were made in the Table getColumn() and containsColumn() methods to take DBDictionary as a parameter, from which the DB-specific delimiter is retrieved. However, some calling methods do not currently have access to the DBDictioinary and pass in null. In this scenario, the double-quote is used as the delimiter. While this is spec-complient, this may not work for some dbs, which is not good. So, I've opened jira OPENJPA-1236 to handle this in the future. I suspect some chaining of changes in method signatures to allow a DBDictionary parameter will fix this.
        Hide
        Jeremy Bauer added a comment -

        This issue was resolved under OPENJPA-1115. The OpenJPA naming utility detects when identifiers returned from dbmd require delimiters.

        Show
        Jeremy Bauer added a comment - This issue was resolved under OPENJPA-1115 . The OpenJPA naming utility detects when identifiers returned from dbmd require delimiters.
        Hide
        Donald Woods added a comment -

        show as being fixed in 2.0.0-beta

        Show
        Donald Woods added a comment - show as being fixed in 2.0.0-beta

          People

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

            Dates

            • Created:
              Updated:
              Resolved:

              Development