glassfish
  1. glassfish
  2. GLASSFISH-18907

Missing schema name in generated SQL using glassfish 3.1.2 (build 23)

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Blocker Blocker
    • Resolution: Won't Fix
    • Affects Version/s: 3.1.2_b23
    • Fix Version/s: None
    • Component/s: cmp
    • Labels:
      None
    • Environment:

      local: Windows XP with GF 3.1.2 (build 23) + JDK 1.7
      server: Oracle Database 10.2.05

      Description

      I've got a big Problem with the usage of glassfish and don't know if it's a bug or a configuration mistake.
      For our business software we use actually the weblogic application server.
      We want to switch to glassfish V3.
      Now I check local for ToDos.

      My JNDI - DataSource Connection has been established and together with the correct *.dbschema-File I'am able to deploy.
      All SessionBeans with connection to the database are working fine.
      My Problem are the EntityBeans.

      From my point of view the problem ist, that my DBUser is different to the Schema-User.
      The result of performing a function ist this stack trace:

      [#|2012-07-16T09:36:47.117+0200|WARNING|glassfish3.1.2|javax.enterprise.system.container.ejb.entity.finder|_ThreadID=79;_ThreadName=Thread-2;|JDO74004: Bean 'InfoUpdateBean' Methode ejbFindByPrimaryKey:
      com.sun.jdo.api.persistence.support.JDODataStoreException: JDO76400: JDBC-SQLException beim Ausführen der SQL-Anweisung:
      SQL-Anweisung<select t0."AUTO_KEY", t0."FIKT_LIEF_NR", t0."INFOTEXT_NR", t0."ANZEIGEN_KZ", t0."GELADEN_AM", t0."LETZTE_AKTUALISIERUNG", t0."BENUTZER_ID" from "LIS_ZUORDNUNG" t0 where t0."AUTO_KEY" = ?> mit Eingabewerten:java.lang.Long:1159049.
      Weitere Informationen finden Sie in der SQLException.
      NestedException: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

      So the Problem is, that the table "LIS_ZUORDNUNG" doesn't exist at the point of view from the current User.
      The SQL has to include a pretended schema name like NDA."LIS_ZUORDNUNG".
      Then the SQL works fine inside the SQL Developer...

        Activity

        sredelin created issue -
        Hide
        marina vatkina added a comment -

        did you specify your schema name in the <schema> element of sun-cmp-mappings.xml?

        Show
        marina vatkina added a comment - did you specify your schema name in the <schema> element of sun-cmp-mappings.xml?
        marina vatkina made changes -
        Field Original Value New Value
        Assignee marina vatkina [ mvatkina ] Mitesh Meswani [ mm110999 ]
        Component/s cmp [ 10591 ]
        Component/s ejb_container [ 10596 ]
        Hide
        marina vatkina added a comment -

        And try removing dbschema file if it doesn't contain the correct schema. This file will be generated automatically if it's not present.

        Show
        marina vatkina added a comment - And try removing dbschema file if it doesn't contain the correct schema. This file will be generated automatically if it's not present.
        Hide
        sredelin added a comment -

        The schema is bound as <schema>NDA</schema>.
        The NDA.dbschema File is correctly placed at META-INF.
        I think I have to use a manual generated schema file (generated with capture-schema), because my User for the DataSource is NDA_ONLINE and the oracle db schema is NDA.
        So my Problem is, that the generated SQL doesn't contain the Schema name like Schema.Table as normal as it is for using oracle databases...

        Show
        sredelin added a comment - The schema is bound as <schema>NDA</schema>. The NDA.dbschema File is correctly placed at META-INF. I think I have to use a manual generated schema file (generated with capture-schema), because my User for the DataSource is NDA_ONLINE and the oracle db schema is NDA. So my Problem is, that the generated SQL doesn't contain the Schema name like Schema.Table as normal as it is for using oracle databases...
        Hide
        sredelin added a comment -

        And if the table doesn't exist in the schema file, glassfish will show me a deployment-exception.

        Show
        sredelin added a comment - And if the table doesn't exist in the schema file, glassfish will show me a deployment-exception.
        Hide
        marina vatkina added a comment -

        If dbschema file is present, it wins. So if dbschema doesn't contain correct schema name, it won't be read from the xml file.

        Show
        marina vatkina added a comment - If dbschema file is present, it wins. So if dbschema doesn't contain correct schema name, it won't be read from the xml file.
        Hide
        sredelin added a comment -

        the schema file is present and got the right context. It was generated by the following code and my user has the analyze all rights.
        capture-schema -username NDA_ONLINE -password ******* -dburl jdbc:oracle:thin:@url:1521/SID -driver oracle.jdbc.driver.OracleDriver -schemaname NDA -out NDA

        So the main Problem is, that while deploying the glassfish is knowing the Table LIS_ZUORDNUNG in schema NDA, but while running a function the gernerated SQL doesn't match the schema NDA. I think it thought, that the Table is also visible under my username, but this is not a requirement in oracle databases...

        Show
        sredelin added a comment - the schema file is present and got the right context. It was generated by the following code and my user has the analyze all rights. capture-schema -username NDA_ONLINE -password ******* -dburl jdbc:oracle:thin:@url:1521/SID -driver oracle.jdbc.driver.OracleDriver -schemaname NDA -out NDA So the main Problem is, that while deploying the glassfish is knowing the Table LIS_ZUORDNUNG in schema NDA, but while running a function the gernerated SQL doesn't match the schema NDA. I think it thought, that the Table is also visible under my username, but this is not a requirement in oracle databases...
        Hide
        sredelin added a comment -

        If there is no theoreticly solution for question, can you test the described situation / process at yours? I can't believe, that nobody else had this problem before?

        Show
        sredelin added a comment - If there is no theoreticly solution for question, can you test the described situation / process at yours? I can't believe, that nobody else had this problem before?
        Hide
        marina vatkina added a comment -

        Can you try changing <table-name>ZUORDNUNG</table-name> to <table-name>NDA.ZUORDNUNG</table-name> in the sun-cmp-mappings.xml

        Show
        marina vatkina added a comment - Can you try changing <table-name>ZUORDNUNG</table-name> to <table-name>NDA.ZUORDNUNG</table-name> in the sun-cmp-mappings.xml
        Hide
        sredelin added a comment -

        To extend the tablename with the schema doesn't change anything. The deploying is also successful and inside the generated sql the tablename is displayed without the schemaname. I tried a second time to change the tablename in table-tag inside the dbschema file, but there is one time more no effect.

        Show
        sredelin added a comment - To extend the tablename with the schema doesn't change anything. The deploying is also successful and inside the generated sql the tablename is displayed without the schemaname. I tried a second time to change the tablename in table-tag inside the dbschema file, but there is one time more no effect.
        Hide
        marina vatkina added a comment -

        this is really strange. try this: modify the xml file to

        a) have an empty <schema> element
        b) use schemaname.tablename in all relevant <table> elements

        Then remove .dbschema file from the cmp module, repackage and redeploy.

        Show
        marina vatkina added a comment - this is really strange. try this: modify the xml file to a) have an empty <schema> element b) use schemaname.tablename in all relevant <table> elements Then remove .dbschema file from the cmp module, repackage and redeploy.
        Hide
        sredelin added a comment -

        Unfortunately it doesn't change anything...
        I tried with an empty schema-Tag <schema></schema> and also with an closed schema-Tag <schema/>.
        I did remove the schema-File from EAR build and added the schema NDA as prefix for tablename.
        The deployment failed due to missing table in schema.
        Some lines from the log (in german):
        Fehler beim Deployment: Exception while preparing the app : JDO74046: JDOCodeGenerator: MappingConversionException beim Laden oder Erstellen des Zuordnungsmodells für Anwendung 'wsi' Modul 'wsi' abgefangen: JDO71002: Die Schemadatei wsi_wsi enthält keine Tabelle mit dem Namen LIS_ZUORDNUNG. Weitere Informationen finden Sie unter server.log

        Show
        sredelin added a comment - Unfortunately it doesn't change anything... I tried with an empty schema-Tag <schema></schema> and also with an closed schema-Tag <schema/>. I did remove the schema-File from EAR build and added the schema NDA as prefix for tablename. The deployment failed due to missing table in schema. Some lines from the log (in german): Fehler beim Deployment: Exception while preparing the app : JDO74046: JDOCodeGenerator: MappingConversionException beim Laden oder Erstellen des Zuordnungsmodells für Anwendung 'wsi' Modul 'wsi' abgefangen: JDO71002: Die Schemadatei wsi_wsi enthält keine Tabelle mit dem Namen LIS_ZUORDNUNG. Weitere Informationen finden Sie unter server.log
        Hide
        marina vatkina added a comment -

        Please attach a reproducible test. It shouldn't happen if the <table> element contains NDA.LIS_ZUORDNUNG

        Show
        marina vatkina added a comment - Please attach a reproducible test. It shouldn't happen if the <table> element contains NDA.LIS_ZUORDNUNG
        Hide
        sredelin added a comment -

        I'm not able to create a reproducible testproject, because our business software is such complex..
        but if you've got a small project you can reproduce it by moving the database tables in another schema and try to get them.
        My solution in this case will be:
        Opt. A
        Use a default persistece Framework like Hibernate / JPA
        Opt. B
        Create manual Data Access in DAO

        Show
        sredelin added a comment - I'm not able to create a reproducible testproject, because our business software is such complex.. but if you've got a small project you can reproduce it by moving the database tables in another schema and try to get them. My solution in this case will be: Opt. A Use a default persistece Framework like Hibernate / JPA Opt. B Create manual Data Access in DAO
        Hide
        Mitesh Meswani added a comment -

        GlassFish CMP implementation works only against default schema. Which unfortunately implies that your use case is not supported if you are using CMP. This will be an RFE for CMP. I am marking this as "won't fix" because we are not spending resources on RFE for GlassFish CMP.

        As you might be aware, the preferred JavaEE solution for persistence is JPA. If you are writing a new application, please consider using GlassFish JPA. It will offer you richer modelling and better performance.

        Show
        Mitesh Meswani added a comment - GlassFish CMP implementation works only against default schema. Which unfortunately implies that your use case is not supported if you are using CMP. This will be an RFE for CMP. I am marking this as "won't fix" because we are not spending resources on RFE for GlassFish CMP. As you might be aware, the preferred JavaEE solution for persistence is JPA. If you are writing a new application, please consider using GlassFish JPA. It will offer you richer modelling and better performance.
        Mitesh Meswani made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Won't Fix [ 2 ]

          People

          • Assignee:
            Mitesh Meswani
            Reporter:
            sredelin
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: