[jbossseam-issues] [JBoss JIRA] Closed: (JBSEAM-1805) Enabling "Contact List" sample application on Oracle

Enabling "Contact List" sample application on Oracle ---------------------------------------------------- Key: JBSEAM-1805 URL: http://jira.jboss.com/jira/browse/JBSEAM-1805 Project: JBoss Seam Issue Type: Feature Request Components: Examples Affects Versions: 2.0.0.BETA1 Environment: OS: Windows Vista 6.0,x86 Database: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production Java VM: Java HotSpot(TM) Server VM 1.5.0_11-b03,Sun Microsystems Inc. Seam: jboss-seam-2.0.0.BETA1 Reporter: Ali Sadik Kumlali Priority: Optional Attachments: contactlist.zip Contact List sample application does not run against Oracle out-of-the-box. So I made some changes and managed it to run on Oracle. Since many enterprise developers must go with Oracle, I wanted to share the steps I followed through the way. Hope I didn't miss the point and this work helps someone else. Regards, Ali Sadik Kumlali -------------------- - Created a user, SEAM, and granted required privileges to it [1] - Created jboss-seam-contactlist-ds.xml file which defines a datasource whose jndi-name is ContactlistDS - Put the jboss-seam-contactlist-ds.xml file under resources folder of the sample - In persistence.xml, changed jta-data-source definition DefaultDS to ContactlistDS - Since Oracle considers the word "COMMENT" as reserved, it doesn't allow a table named "COMMENT" [2]. So, in Comment.java, I added @Table(name = "T_COMMENT") annotation below the @Entity. Thus, I let the Seam map Comment bean to T_COMMENT table. - To preserve consistency, I did the same for Contact.java. - import.sql cannot be used as is, since it doesn't have ID column filled a valid value[3]. So, I - Added sequence generator annotations in Contact.java and Comment.java as follows. Thus guaranteed the Seam to create the required sequences on start-up. @Id @SequenceGenerator(name = "generator", sequenceName = "COMMENT_SEQ") @GeneratedValue(strategy = SEQUENCE, generator = "generator") private Long id; - Changed each line of import.sql as follows: insert into T_Contact ( id, firstName, ... ) values ( Contact_Seq.nextval, 'Jacob', ...) [1] -- You may/should change the tablespace SYSTEM to preference of your -- own (e.g. SEAM) create user "SEAM" profile "DEFAULT" identified by "SEAM" default tablespace "SYSTEM" temporary tablespace "TEMP" quota unlimited on "SYSTEM" account unlock; grant create session to "SEAM"; grant create any table to "SEAM"; grant create any sequence to "SEAM"; [2] 2007-08-15 16:13:09,521 DEBUG [org.hibernate.tool.hbm2ddl.SchemaExport] create table SEAM.Comment (id number(19,0) not null, text varchar2(1500 char) not null, created timestamp not null, contact_id number(19,0) not null, primary key (id)) 2007-08-15 16:13:09,522 ERROR [org.hibernate.tool.hbm2ddl.SchemaExport] Unsuccessful: create table SEAM.Comment (id number(19,0) not null, text varchar2(1500 char) not null, created timestamp not null, contact_id number(19,0) not null, primary key (id)) 2007-08-15 16:13:09,524 ERROR [org.hibernate.tool.hbm2ddl.SchemaExport] ORA-00903: invalid table name 2007-08-15 16:13:09,525 DEBUG [org.hibernate.tool.hbm2ddl.SchemaExport] create table SEAM.Contact (id number(19,0) not null, firstName varchar2(50 char), lastName varchar2(50 char), address varchar2(250 char), city varchar2(50 char), state varchar2(50 char), zip varchar2(6 char), country varchar2(50 char), homePhone varchar2(20 char), businessPhone varchar2(20 char), cellPhone varchar2(20 char), version number(10,0) not null, primary key (id)) 2007-08-15 16:13:10,706 DEBUG [org.hibernate.tool.hbm2ddl.SchemaExport] alter table SEAM.Comment add constraint FK9BDE863F5C903077 foreign key (contact_id) references SEAM.Contact 2007-08-15 16:13:10,707 ERROR [org.hibernate.tool.hbm2ddl.SchemaExport] Unsuccessful: alter table SEAM.Comment add constraint FK9BDE863F5C903077 foreign key (contact_id) references SEAM.Contact 2007-08-15 16:13:10,709 ERROR [org.hibernate.tool.hbm2ddl.SchemaExport] ORA-00903: invalid table name 2007-08-15 16:13:10,711 DEBUG [org.hibernate.tool.hbm2ddl.SchemaExport] create sequence SEAM.hibernate_sequence 2007-08-15 16:13:10,778 INFO [org.hibernate.tool.hbm2ddl.SchemaExport] Executing import script: /import.sql 2007-08-15 16:13:10,780 DEBUG [org.hibernate.tool.hbm2ddl.SchemaExport] insert into Contact ( firstName, lastName, cellPhone, address, city, state, zip, country, version ) values ( 'Gavin', 'King', null, 'Peachtree Rd', 'Atlanta', 'GA', '30305', 'USA', 1 ) 2007-08-15 16:13:10,867 ERROR [org.hibernate.tool.hbm2ddl.SchemaExport] schema export unsuccessful org.hibernate.JDBCException: Error during import script execution at org.hibernate.tool.hbm2ddl.SchemaExport.importScript(SchemaExport.java:258) at org.hibernate.tool.hbm2ddl.SchemaExport.execute(SchemaExport.java:192) at org.hibernate.tool.hbm2ddl.SchemaExport.create(SchemaExport.java:133) at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:311) ... [3] 19:18:22,053 ERROR [SchemaExport] schema export unsuccessful org.hibernate.JDBCException: Error during import script execution at org.hibernate.tool.hbm2ddl.SchemaExport.importScript(SchemaExport.java:258) at org.hibernate.tool.hbm2ddl.SchemaExport.execute(SchemaExport.java:192) at org.hibernate.tool.hbm2ddl.SchemaExport.create(SchemaExport.java:133) at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:311) ... Caused by: java.sql.SQLException: ORA-01400: cannot insert NULL into ("SEAM"."T_CONTACT"."ID") at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288) ...