Help required to generate primary key sequence

[Former Member]

Hello All,

I need your help in generating a sequence for primary key in my db table created using java dictionary project in my NWDS 7.3.

The table column EMPL_ID is a primary and needs to be a sequence. The code in the JPA entity is as below. The underlying database is DB2 on Linux. Kindly help me resolve this issue.

/**
 * The persistent class for the TMP_EMPL_DATA database table.
 * 
 */
@Entity
@Table(name="TMP_EMPL_DATA")
public class TmpEmplData implements Serializable {
	private static final long serialVersionUID = 1L;

	@Id
	@SequenceGenerator(name="EMPLID_GENERATOR", sequenceName="EMPID_SEQ", initialValue = 10000, allocationSize = 1)
	@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="EMPLID_GENERATOR")
	@Column(name="EMPL_ID")
	private String _emplId_;

My bean class contains a public method as follows.

 public TmpEmplData createEmployee(String employeeName, int age, String location)
    {
    	TmpEmplData emplData = new TmpEmplData();
    	emplData.set_emplName_(employeeName);
    	emplData.setAge(age);
    	emplData.setLocation(location);
    	entityManager.persist(emplData);
    	
    	entityManager.find(TmpEmplData.class, emplData.get_emplId_());
    	
    	return emplData;
    }

During execution, i am getting an error as below.

Caused by: javax.persistence.PersistenceException: The generated SQL statement is not valid for the underlying database platform (platform no. 99). For more details see attached exception. SQL statement is SELECT NEXT VALUE FOR "EMPID_SEQ" FROM ( VALUES (1) )

at com.sap.engine.services.orpersistence.sqlmapper.mapping.JPASQLMapperImpl.createSequenceIDGeneratorSelectText(JPASQLMapperImpl.java:1239)

at com.sap.engine.services.orpersistence.entitymanager.CachedJPASQLMapper.createSequenceIDGeneratorSelectText(CachedJPASQLMapper.java:259)

at com.sap.engine.services.orpersistence.generator.GeneratorFactory.<init>(GeneratorFactory.java:156)

at com.sap.engine.services.orpersistence.entitymanager.EntityManagerFactoryImpl$MetaDataImpl.<init>(EntityManagerFactoryImpl.java:323)

at com.sap.engine.services.orpersistence.entitymanager.EntityManagerFactoryImpl.<init>(EntityManagerFactoryImpl.java:137)

at com.sap.engine.services.orpersistence.entitymanager.JtaEntityManagerFactoryImpl.<init>(JtaEntityManagerFactoryImpl.java:39)

at com.sap.engine.services.orpersistence.provider.PersistenceProviderImpl.createJtaEntityManagerFactory(PersistenceProviderImpl.java:251)

at com.sap.engine.services.orpersistence.provider.PersistenceProviderImpl.createContainerEntityManagerFactory(PersistenceProviderImpl.java:236)

at com.sap.engine.services.orpersistence.container.EMFInstanceCreator.getEntityManagerFactory(EMFInstanceCreator.java:134)

at com.sap.engine.services.orpersistence.container.ORPersistenceObjectFactory.getEMF(ORPersistenceObjectFactory.java:300)

at com.sap.engine.services.orpersistence.container.ORPersistenceObjectFactory.getObjectInstance(ORPersistenceObjectFactory.java:73)

at com.sap.engine.lib.injection.ReferenceObjectFactory.getObject(ReferenceObjectFactory.java:96)

at com.sap.engine.lib.injection.FieldInjector.inject(FieldInjector.java:113)

... 122 more

Caused by: com.sap.engine.services.orpersistence.sqlmapper.generate.SQLMappingException: SQL statement is not valid for underlying database platform.

at com.sap.engine.services.orpersistence.sqlmapper.common.CommonSQLMappingResult.getStatementString(CommonSQLMappingResult.java:237)

at com.sap.engine.services.orpersistence.sqlmapper.mapping.JPASQLMapperImpl.createSequenceIDGeneratorSelectText(JPASQLMapperImpl.java:1237)

... 134 more

Caused by: com.sap.sql.tree.StringRepresentationFailureException: Cannot represent next value expression as string.

at com.sap.sql.tree.impl.OpenSqlTextExpert.nextValueExpressionToText(OpenSqlTextExpert.java:185)

at com.sap.sql.tree.impl.NextValueExpressionBuilderImpl$NextValueExpressionImpl.toSqlTxt(NextValueExpressionBuilderImpl.java:140)

at com.sap.sql.tree.impl.AbstractSqlTextExpert.selectSublistToText(AbstractSqlTextExpert.java:130)

at com.sap.sql.tree.impl.SelectSublistImpl.toSqlTxt(SelectSublistImpl.java:150)

at com.sap.sql.tree.impl.QuerySpecificationImpl.toSqlTxt(QuerySpecificationImpl.java:361)

at com.sap.sql.tree.impl.SelectStatementImpl.toSqlTxt(SelectStatementImpl.java:139)

at com.sap.sql.tree.impl.SQLStatementImpl.toSqlString(SQLStatementImpl.java:75)

at com.sap.engine.services.orpersistence.sqlmapper.common.CommonSQLMappingResult.getStatementString(CommonSQLMappingResult.java:233)

... 135 more

[rolf_paulsen · Accepted solution]

Hi,

does the sequence exist? SAP JPA does not support creation of sequences, you have to create it manually.

Maybe SAP JPA also does not support DB2 sequences with "Open SQL" correctly.

- Tryp to set your data source to "native" or "vendor".

- Try to turn on oracle compatibility:

db2set DB2_COMPATIBILITY_VECTOR=ORA

Regards

Rolf

[Former Member · Accepted solution]

The issue was due to the mapping file orm.xml. Resolved by adding necessary parameters.

[Former Member · Accepted solution]

Hi,

Am facing the same issue. Please let me know how you solved the issue.