Details

    • Type: Sub-task Sub-task
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.3.0, 2.0.0-M3
    • Component/s: None
    • Labels:
      None
    • Patch Info:
      Patch Available
    1. OPENJPA-946.patch
      25 kB
      Tim McConnell

      Issue Links

        Activity

        Hide
        Tim McConnell added a comment -

        Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ORA-00904: : invalid identifier

        {stmnt 12673179 CREATE TABLE PROX_PC (id NUMBER NOT NULL, date DATE, name VARCHAR2(255), sqlDate DATE, timestamp TIMESTAMP, PRIMARY KEY (id))}

        [code=904, state=42000]

        Show
        Tim McConnell added a comment - Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ORA-00904: : invalid identifier {stmnt 12673179 CREATE TABLE PROX_PC (id NUMBER NOT NULL, date DATE, name VARCHAR2(255), sqlDate DATE, timestamp TIMESTAMP, PRIMARY KEY (id))} [code=904, state=42000]
        Hide
        Tim McConnell added a comment -

        The set of reserved words for the Oracle dictionary is incomplete which is causing this testcase to fail. In this particular testcase a column is named "date", which is reserved but not included in the reservedWordSet. This patch update the Oracle dictionary with all the reserved words.

        Show
        Tim McConnell added a comment - The set of reserved words for the Oracle dictionary is incomplete which is causing this testcase to fail. In this particular testcase a column is named "date", which is reserved but not included in the reservedWordSet. This patch update the Oracle dictionary with all the reserved words.
        Hide
        Milosz Tylenda added a comment -

        Tim, have you checked the patch resolves the issue? I am afraid adding a DATE to Oracle will not help because:

        1. The file sql-keywords.rsrc already contains the DATE word. This file contains reserved words defined by SQL spec and its contents are merged with database-specific reserved words.

        2. Recently I hit a similar problem with Firebird database: many of our tests use entities with a property "value" and VALUE is an SQL reserved word (exists in the sql-keywords.rsrc) and also a Firebird reserved word.

        Maybe we are not checking column names against reserved words or merging SQL words with database-specific ones is broken.

        Show
        Milosz Tylenda added a comment - Tim, have you checked the patch resolves the issue? I am afraid adding a DATE to Oracle will not help because: 1. The file sql-keywords.rsrc already contains the DATE word. This file contains reserved words defined by SQL spec and its contents are merged with database-specific reserved words. 2. Recently I hit a similar problem with Firebird database: many of our tests use entities with a property "value" and VALUE is an SQL reserved word (exists in the sql-keywords.rsrc) and also a Firebird reserved word. Maybe we are not checking column names against reserved words or merging SQL words with database-specific ones is broken.
        Hide
        Tim McConnell added a comment -

        Hi Milosz, I did not know that. Thanks for pointing it out......

        Show
        Tim McConnell added a comment - Hi Milosz, I did not know that. Thanks for pointing it out......
        Hide
        Tim McConnell added a comment -

        Hi Ravi, Please try this patch. It will fix your Oracle "Create Table" errors when reserved keywords are used for column names. Please let me know how it goes. Thanks much

        Show
        Tim McConnell added a comment - Hi Ravi, Please try this patch. It will fix your Oracle "Create Table" errors when reserved keywords are used for column names. Please let me know how it goes. Thanks much
        Hide
        Ravi P Palacherla added a comment -

        Hi Tim,

        I applied the patch in trunk version and

        I am getting a compilation error saying:
        [ERROR] BUILD FAILURE
        [INFO] ------------------------------------------------------------------------
        [INFO] Compilation failure
        D:\openJPAsrc\openjpa_trunk\trunk\openjpa-persistence-jdbc\src\test\java\org\apache\openjpa\persiste
        nce\dynamicschema\TestDynamicSchemas.java:[195,28] cannot find symbol
        symbol : method getReservedWords()
        location: class org.apache.openjpa.jdbc.sql.DBDictionary

        D:\openJPAsrc\openjpa_trunk\trunk\openjpa-persistence-jdbc\src\test\java\org\apache\openjpa\persiste
        nce\dynamicschema\TestDynamicSchemas.java:[195,28] cannot find symbol
        symbol : method getReservedWords()
        location: class org.apache.openjpa.jdbc.sql.DBDictionary

        Also, the fix only contains files in test cases.
        So, will this fix work for an openJPA sample (outside test suites) that uses reserved words.

        Thanks,
        Ravi.

        Show
        Ravi P Palacherla added a comment - Hi Tim, I applied the patch in trunk version and I am getting a compilation error saying: [ERROR] BUILD FAILURE [INFO] ------------------------------------------------------------------------ [INFO] Compilation failure D:\openJPAsrc\openjpa_trunk\trunk\openjpa-persistence-jdbc\src\test\java\org\apache\openjpa\persiste nce\dynamicschema\TestDynamicSchemas.java: [195,28] cannot find symbol symbol : method getReservedWords() location: class org.apache.openjpa.jdbc.sql.DBDictionary D:\openJPAsrc\openjpa_trunk\trunk\openjpa-persistence-jdbc\src\test\java\org\apache\openjpa\persiste nce\dynamicschema\TestDynamicSchemas.java: [195,28] cannot find symbol symbol : method getReservedWords() location: class org.apache.openjpa.jdbc.sql.DBDictionary Also, the fix only contains files in test cases. So, will this fix work for an openJPA sample (outside test suites) that uses reserved words. Thanks, Ravi.
        Hide
        Tim McConnell added a comment -

        Hi Ravi, sorry about the first patch. Please discard it and try this patch. Let me know how it goes. Thanks much

        Show
        Tim McConnell added a comment - Hi Ravi, sorry about the first patch. Please discard it and try this patch. Let me know how it goes. Thanks much
        Hide
        Tim McConnell added a comment -

        This patch contains a JUnit testcase, which may or may not matter to Ravi, but should be the patch that gets committed.....

        Show
        Tim McConnell added a comment - This patch contains a JUnit testcase, which may or may not matter to Ravi, but should be the patch that gets committed.....
        Hide
        Ravi P Palacherla added a comment -

        Thanks for the patch Tim.

        It worked fine for me.
        I was trying to run TestEJBState which is failing because of this error.
        After applying your patch I no more see this issue.

        I am getting a new exception, ORA-02275(different from above), so I will open a new JIRA for this.

        Regards,
        Ravi.

        Show
        Ravi P Palacherla added a comment - Thanks for the patch Tim. It worked fine for me. I was trying to run TestEJBState which is failing because of this error. After applying your patch I no more see this issue. I am getting a new exception, ORA-02275(different from above), so I will open a new JIRA for this. Regards, Ravi.
        Hide
        Michael Dick added a comment -

        Some comments on the patch.

        The method name getReservedColumnNames() is misleading. It's really returning the the set of reserved words - reserved words that can be used as column names. Renaming to getValidColumnNames() makes more sense to me. Javadoc would also help here - I had to go back to the declaration of the instance variable to figure out why the method was correct.

        In addition this method recreates the list each time it's invoked - it'd be better to either cache the list after the first invocation or create the set when the dictionary is initialized.

        I'm not sure I have a good solution at hand, but it looks like a lot of the dictionaries reuse the same set of allowable column names. It'd be nice if we could store that list in a single place instead of potentially maintaining it in each class.

        Otherwise the patch looks good, thanks very much for looking into it Tim. The testcase is very nice and appreciated.

        Show
        Michael Dick added a comment - Some comments on the patch. The method name getReservedColumnNames() is misleading. It's really returning the the set of reserved words - reserved words that can be used as column names. Renaming to getValidColumnNames() makes more sense to me. Javadoc would also help here - I had to go back to the declaration of the instance variable to figure out why the method was correct. In addition this method recreates the list each time it's invoked - it'd be better to either cache the list after the first invocation or create the set when the dictionary is initialized. I'm not sure I have a good solution at hand, but it looks like a lot of the dictionaries reuse the same set of allowable column names. It'd be nice if we could store that list in a single place instead of potentially maintaining it in each class. Otherwise the patch looks good, thanks very much for looking into it Tim. The testcase is very nice and appreciated.
        Hide
        Michael Dick added a comment -

        One other thing : OpenJPA > 1.0.x requires Java 5, so we can use generics. New code should take advantage of them to avoid misleading compiler warnings.

        Show
        Michael Dick added a comment - One other thing : OpenJPA > 1.0.x requires Java 5, so we can use generics. New code should take advantage of them to avoid misleading compiler warnings.
        Hide
        Tim McConnell added a comment - - edited

        Hi Michael, thanks for reviewing and testing it. I'll add some javadoc today and update per your comments. I'm now getting the same ORA-02275 errors that you're seeing. Plus, I'm now getting many of these errors below. Do you know what this error is or what might be causing it ??

        testAttrOverride1(org.apache.openjpa.persistence.embed.attrOverrides.TestAttrOverrides) Time elapsed: 0.157 sec <<< ERROR!
        <openjpa-2.0.0-SNAPSHOT-r422266:774177M fatal store error> org.apache.openjpa.util.StoreException: Cannot create PoolableConnectionFactory (Listener refused the connection with the following error:
        ORA-12518, TNS:listener could not hand off client connection
        The Connection descriptor used by the client was:
        9.51.245.122:1521:XE

        I altered my Oracle database settings via this command but it did not seem to help:

        "ALTER SYSTEM SET PROCESSES=500 SCOPE=SPFILE;"

        Show
        Tim McConnell added a comment - - edited Hi Michael, thanks for reviewing and testing it. I'll add some javadoc today and update per your comments. I'm now getting the same ORA-02275 errors that you're seeing. Plus, I'm now getting many of these errors below. Do you know what this error is or what might be causing it ?? testAttrOverride1(org.apache.openjpa.persistence.embed.attrOverrides.TestAttrOverrides) Time elapsed: 0.157 sec <<< ERROR! <openjpa-2.0.0-SNAPSHOT-r422266:774177M fatal store error> org.apache.openjpa.util.StoreException: Cannot create PoolableConnectionFactory (Listener refused the connection with the following error: ORA-12518, TNS:listener could not hand off client connection The Connection descriptor used by the client was: 9.51.245.122:1521:XE I altered my Oracle database settings via this command but it did not seem to help: "ALTER SYSTEM SET PROCESSES=500 SCOPE=SPFILE;"
        Hide
        Michael Dick added a comment -

        Hi Tim,

        FWIW I've seen that error before and adding a connection pool really helped (tried this before doing the SET PROCESSES step).

        If you're already using a connection pool you can try tweaking the settings (default values in pom.xml might not be perfect for Oracle XE).

        Show
        Michael Dick added a comment - Hi Tim, FWIW I've seen that error before and adding a connection pool really helped (tried this before doing the SET PROCESSES step). If you're already using a connection pool you can try tweaking the settings (default values in pom.xml might not be perfect for Oracle XE).
        Hide
        Tim McConnell added a comment -

        Attached new patch and have incorporated as many of Mike's comments as possible. Please review again and let me know what you think. Thanks much

        Show
        Tim McConnell added a comment - Attached new patch and have incorporated as many of Mike's comments as possible. Please review again and let me know what you think. Thanks much
        Hide
        Donald Woods added a comment -

        also included in 1.3.x as Rev778727

        Show
        Donald Woods added a comment - also included in 1.3.x as Rev778727
        Hide
        Tim McConnell added a comment -

        Reopening as there still appears to be a problem when using the mapping tool (SynchronizeMappings) and the SchemaFactory in its configuration.

        Show
        Tim McConnell added a comment - Reopening as there still appears to be a problem when using the mapping tool (SynchronizeMappings) and the SchemaFactory in its configuration.
        Hide
        Tim McConnell added a comment -

        This patch will handle the case where column names are validated and translated against the database dictionary and multiple schema factories get invoked, including the DynamicSchemaFactory. Additionally, some changes have been made to the Derby (see http://db.apache.org/derby/docs/10.1/ref/rrefkeywords29722.html) and DB2 dictionaries. Thanks Jeremy for the testcase.

        Show
        Tim McConnell added a comment - This patch will handle the case where column names are validated and translated against the database dictionary and multiple schema factories get invoked, including the DynamicSchemaFactory. Additionally, some changes have been made to the Derby (see http://db.apache.org/derby/docs/10.1/ref/rrefkeywords29722.html ) and DB2 dictionaries. Thanks Jeremy for the testcase.
        Hide
        Tim McConnell added a comment -

        Cleaned up dictionaries for these databases:

        1. Derby
        2. DB2
        3. MySQL
        4. Oracle
        5. PostgreSQL

        Show
        Tim McConnell added a comment - Cleaned up dictionaries for these databases: 1. Derby 2. DB2 3. MySQL 4. Oracle 5. PostgreSQL
        Hide
        Donald Woods added a comment -

        Committed patch from 20090622 to 1.3.x and trunk. Thanks Tim.

        Show
        Donald Woods added a comment - Committed patch from 20090622 to 1.3.x and trunk. Thanks Tim.
        Hide
        Alan Raison added a comment -

        This patch seems to include a test case which tries to connect to a Derby server - not much good if you're not using Derby

        See openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/dynamicschema/TestResWordEntity.java

        Show
        Alan Raison added a comment - This patch seems to include a test case which tries to connect to a Derby server - not much good if you're not using Derby See openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/dynamicschema/TestResWordEntity.java
        Hide
        Donald Woods added a comment -

        Thanks for catching that Alan.
        Updated TestResWordEntity.java in trunk as Rev798991 and 1.3.x as Rev798993.

        Show
        Donald Woods added a comment - Thanks for catching that Alan. Updated TestResWordEntity.java in trunk as Rev798991 and 1.3.x as Rev798993.

          People

          • Assignee:
            Donald Woods
            Reporter:
            Tim McConnell
          • Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development