[GLASSFISH-18907] Missing schema name in generated SQL using glassfish 3.1.2 (build 23) Created: 16/Jul/12  Updated: 26/Jul/12  Resolved: 26/Jul/12

Status: Resolved
Project: glassfish
Component/s: cmp
Affects Version/s: 3.1.2_b23
Fix Version/s: None

Type: Bug Priority: Blocker
Reporter: sredelin Assignee: Mitesh Meswani
Resolution: Won't Fix Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

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


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...

Comment by marina vatkina [ 16/Jul/12 ]

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

Comment by marina vatkina [ 16/Jul/12 ]

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

Comment by sredelin [ 17/Jul/12 ]

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...

Comment by sredelin [ 17/Jul/12 ]

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

Comment by marina vatkina [ 17/Jul/12 ]

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.

Comment by sredelin [ 18/Jul/12 ]

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...

Comment by sredelin [ 18/Jul/12 ]

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?

Comment by marina vatkina [ 18/Jul/12 ]

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

Comment by sredelin [ 18/Jul/12 ]

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.

Comment by marina vatkina [ 18/Jul/12 ]

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.

Comment by sredelin [ 20/Jul/12 ]

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

Comment by marina vatkina [ 20/Jul/12 ]

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

Comment by sredelin [ 23/Jul/12 ]

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

Comment by Mitesh Meswani [ 26/Jul/12 ]

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.

Generated at Wed Jun 03 07:10:41 UTC 2015 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.