Description Yiping Zhao 2009-11-23 11:23:08 EST

1. testNamedStoredProcedureQuery() and testNamedStoredProcedureQueryInOut() in torg.eclipse.persistence.testing.tests.jpa.xml.advanced.EntityMappingsAdvancedJUnitTestCase.java failed on H2 and DB2 both with "Internal Exception: org.h2.jdbc.JdbcSQLException: Function alias "PROCEDURE" not found; SQL statement"

2. testSetup() in org.eclipse.persistence.testing.tests.jpa.xml.complexaggregate.EntityMappingsComplexAggregateJUnitTestCase.java failed only on H2 with following error message:

Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.0.qualifier): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.h2.jdbc.JdbcSQLException: Attempt to define a second primary key; SQL statement:
CREATE TABLE XML_CITYSLICKER (ID NUMERIC(18) IDENTITY NOT NULL UNIQUE, FNAME VARCHAR(20) NOT NULL UNIQUE, LNAME VARCHAR(20) NOT NULL UNIQUE, AGE NUMERIC(15) NOT NULL, GENDER VARCHAR(6), WORLD_ID NUMERIC(18), PRIMARY KEY (ID, FNAME, LNAME)) [90017-123]
Error Code: 90017
Call: CREATE TABLE XML_CITYSLICKER (ID NUMERIC(18) IDENTITY NOT NULL UNIQUE, FNAME VARCHAR(20) NOT NULL UNIQUE, LNAME VARCHAR(20) NOT NULL UNIQUE, AGE NUMERIC(15) NOT NULL, GENDER VARCHAR(6), WORLD_ID NUMERIC(18), PRIMARY KEY (ID, FNAME, LNAME))
Query: DataModifyQuery(sql="CREATE TABLE XML_CITYSLICKER (ID NUMERIC(18) IDENTITY NOT NULL UNIQUE, FNAME VARCHAR(20) NOT NULL UNIQUE, LNAME VARCHAR(20) NOT NULL UNIQUE, AGE NUMERIC(15) NOT NULL, GENDER VARCHAR(6), WORLD_ID NUMERIC(18), PRIMARY KEY (ID, FNAME, LNAME))")
 at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
 at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:686)
 at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:529)
 at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:529)
 at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:205)
 at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:191)
 at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelectCall(DatasourceCallQueryMechanism.java:234)
 at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelect(DatasourceCallQueryMechanism.java:214)
 at org.eclipse.persistence.queries.DataModifyQuery.executeDatabaseQuery(DataModifyQuery.java:85)
 at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:670)
 at org.eclipse.persistence.internal.sessions.AbstractSession.internalExecuteQuery(AbstractSession.java:2322)
 at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1225)
 at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1207)
 at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1167)
 at org.eclipse.persistence.internal.sessions.AbstractSession.executeNonSelectingCall(AbstractSession.java:936)
 at org.eclipse.persistence.tools.schemaframework.DatabaseObjectDefinition.createOnDatabase(DatabaseObjectDefinition.java:81)
 at org.eclipse.persistence.tools.schemaframework.TableDefinition.createOnDatabase(TableDefinition.java:904)
 at org.eclipse.persistence.tools.schemaframework.SchemaManager.createObject(SchemaManager.java:188)
 at org.eclipse.persistence.tools.schemaframework.SchemaManager.replaceObject(SchemaManager.java:889)
 at org.eclipse.persistence.tools.schemaframework.TableCreator.replaceTablesAndConstraints(TableCreator.java:297)
 at org.eclipse.persistence.tools.schemaframework.TableCreator.replaceTables(TableCreator.java:248)
 at org.eclipse.persistence.tools.schemaframework.TableCreator.replaceTables(TableCreator.java:240)
 at org.eclipse.persistence.testing.tests.jpa.xml.complexaggregate.EntityMappingsComplexAggregateJUnitTestCase.testSetup(EntityMappingsComplexAggregateJUnitTestCase.java:81)
 at org.eclipse.persistence.testing.framework.junit.JUnitTestCase.runBare(JUnitTestCase.java:428)
Caused by: org.h2.jdbc.JdbcSQLException: Attempt to define a second primary key; SQL statement:
CREATE TABLE XML_CITYSLICKER (ID NUMERIC(18) IDENTITY NOT NULL UNIQUE, FNAME VARCHAR(20) NOT NULL UNIQUE, LNAME VARCHAR(20) NOT NULL UNIQUE, AGE NUMERIC(15) NOT NULL, GENDER VARCHAR(6), WORLD_ID NUMERIC(18), PRIMARY KEY (ID, FNAME, LNAME)) [90017-123]
 at org.h2.message.Message.getSQLException(Message.java:111)
 at org.h2.message.Message.getSQLException(Message.java:122)
 at org.h2.message.Message.getSQLException(Message.java:75)
 at org.h2.message.Message.getSQLException(Message.java:157)
 at org.h2.command.ddl.CreateTable.setPrimaryKeyColumns(CreateTable.java:220)
 at org.h2.command.ddl.CreateTable.addConstraintCommand(CreateTable.java:83)
 at org.h2.command.Parser.parseCreateTable(Parser.java:4646)
 at org.h2.command.Parser.parseCreate(Parser.java:3455)
 at org.h2.command.Parser.parsePrepared(Parser.java:324)
 at org.h2.command.Parser.parse(Parser.java:291)
 at org.h2.command.Parser.parse(Parser.java:263)
 at org.h2.command.Parser.prepareCommand(Parser.java:235)
 at org.h2.engine.Session.prepareLocal(Session.java:416)
 at org.h2.engine.Session.prepareCommand(Session.java:377)
 at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1049)
 at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:73)
 at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:233)
 at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1397)
 at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1346)
 at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:646)
 at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:579)

The fix of the problem:

1. add if (!supportsStoredProcedures()) condition to testNamedStoredProcedureQuery() and testNamedStoredProcedureQueryInOut() since H2 and DB2 don't support store procedure, same tests in org.eclipse.persistence.testing.tests.jpa.advanced.AdvancedJPAJunitTest.java have added this condition already.

2. add replaceTables() in org.eclipse.persistence.testing.models.jpa.xml.complexaggregate.ComplexAggregateTableCreator.java to "remove pk constraint as there is a identity field, and H2, HSQL do not allow this", same table creator class ComplexAggregateTableCreator under org.eclipse.persistence.testing.models.jpa.complexaggregate already has this implementation.