Psuedo Guru Blog by a wannabe guru with spelling issues.

Adding Dali (JPA) to an existing web project in Eclipse

Adding Dali (JPA perspective) to an existing web project in Eclipse.

• Add GlassFish TopLink Essentials jars (or other JPA implementation) to the WEB-INF/lib folder
• Right click on the project, select properties
• Select Project Facets
• Check the JPA facet

...and your done! You can now go to the JPA perspective, setup your datasources, create entities, etc.

Table/View 'SEQUENCE' does not exist

While experimenting with the glassfish JPA implementation (toplink essentials), I ran into the following problem while trying to persist an entity with @GeneratedValue specified for an id:

Local Exception Stack:
Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.1 (Build b11-fcs (12/04/2007))): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Table/View 'SEQUENCE' does not exist.
Error Code: 20000
Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
 bind => [50, SEQ_GEN]
Query: DataModifyQuery()
 at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:319)
 at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:566)
 at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:452)
 at oracle.toplink.essentials.internal.sessions.AbstractSession.executeCall(AbstractSession.java:690)
...yada yada yada

Turns out that once you set an ID to autogenerate, toplink requires that you have a "sequence" table created, and it does not create it automatically. Found that little tidbit here.

Here is the SQL you need to run against your database to make the problem go away:

-- needed for autogenerated primary keys
CREATE TABLE SEQUENCE (SEQ_NAME VARCHAR(50), SEQ_COUNT DECIMAL(15));
INSERT INTO SEQUENCE(SEQ_NAME, SEQ_COUNT) values ('SEQ_GEN', 0);