Informix with EJB's

Hi, Has anyone used informix and created entities? I've got the following sample table: CREATE TABLE dpparent ( tab_id SERIAL NOT NULL, parent_name CHAR(20) PRIMARY KEY(tab_id) ); CREATE TABLE dpchild ( child_id SERIAL NOT NULL, tab_id INTEGER, child_name CHAR(20), FOREIGN KEY (tab_id) REFERENCES dpparent(tab_id) ); I managed to create the entities in eclipse/jboss: @Entity public class Dpparent implements Serializable { @Id @Column(name="tab_id") @GeneratedValue(strategy = GenerationType.AUTO) private Integer tabId; @Column(name="parent_name") private String parentName; private static final long serialVersionUID = 1L; public Dpparent() { super(); } public Integer getTabId() { return this.tabId; } public void setTabId(int tabId) { this.tabId = tabId; } public String getParentName() { return this.parentName; } public void setParentName(String parentName) { this.parentName = parentName; } public Integer getVersion() { return this.version; } public void setVersion(int version) { this.version = version; } } When I try to use the entity manager to find a row, I get the following error: 14:01:22,781 INFO [STDOUT] Hibernate: select dpparent0_.tab_id as tab1_11_0_, dpparent0_.VERSION as VERSION11_0_, dpparent0_.parent_name as parent3_11_0_ from uat3.reece.dpparent dpparent0_ where dpparent0_.tab_id=? 14:01:22,781 WARN [JDBCExceptionReporter] SQL Error: -201, SQLState: 42000 14:01:22,781 ERROR [JDBCExceptionReporter] A syntax error has occurred. 14:01:22,781 INFO [DefaultLoadEventListener] Error performing load command org.hibernate.exception.SQLGrammarException: could not load an entity: [au.com.reece.Dpparent#1] I've tried running the SQL generated by hibernate and it works IF I removed the schema/catelog info from the table. (not sure if this is an issue.) ie select dpparent0_.tab_id as tab1_123_0_, dpparent0_.VERSION as VERSION123_0_, dpparent0_.parent_name as parent3_123_0_ from Dpparent dpparent0_ where dpparent0_.tab_id=2; If I used the following code: Query query = em.createNativeQuery( "SELECT * FROM Dpparent WHERE tab_id = ?1", Dpparent.class); query.setParameter(1, 1); Dpparent p = (Dpparent) query.getSingleResult(); The sql generate is: SELECT * FROM Dpparent WHERE tab_id = ? and this works fine. I cannot seem to find any similar issues on google etc. Anyone have any suggestions as I'm quite stuck. Thanks David

Our application is running on different JBoss and Informix versions successfully. So yes, this works if JBoss / Hibernate is configured correctly. Use @GeneratedValue( strategy = GenerationType.IDENTITY ) for serial columns. Did you specify "org.hibernate.dialect.InformixDialect" in your persistence.xml? There is no database column named version and there is no field named version in your class, but the class has get/set-Method for version? It seems to me that you did not show the original code. DAVID PLEYDELL wrote: > Hi, > > Has anyone used informix and created entities? I've got the following sample > table: > > CREATE TABLE dpparent> > ( tab_id SERIAL NOT NULL, > > parent_name CHAR(20) > > PRIMARY KEY(tab_id) > > ); > > CREATE TABLE dpchild> > ( child_id SERIAL NOT NULL, > > tab_id INTEGER, > > child_name CHAR(20), > > FOREIGN KEY (tab_id) REFERENCES dpparent(tab_id) > > ); > > I managed to create the entities in eclipse/jboss: > > @Entity > public class Dpparent implements Serializable { > > @Id > > @Column(name="tab_id") > > @GeneratedValue(strategy = GenerationType.AUTO) > > private Integer tabId; > > @Column(name="parent_name") > > private String parentName; > > private static final long serialVersionUID = 1L; > > public Dpparent() { > > super(); > > } > > public Integer getTabId() { > > return this.tabId; > > } > > public void setTabId(int tabId) { > > this.tabId = tabId; > > } > > public String getParentName() { > > return this.parentName; > > } > > public void setParentName(String parentName) { > > this.parentName = parentName; > > } > > public Integer getVersion() { > > return this.version; > > } > > public void setVersion(int version) { > > this.version = version; > > } > > } > > When I try to use the entity manager to find a row, I get the following error: > > 14:01:22,781 INFO [STDOUT] Hibernate: select dpparent0_.tab_id as tab1_11_0_, > dpparent0_.VERSION as VERSION11_0_, dpparent0_.parent_name as parent3_11_0_ > from uat3.reece.dpparent dpparent0_ where dpparent0_.tab_id=? > 14:01:22,781 WARN [JDBCExceptionReporter] SQL Error: -201, SQLState: 42000 > 14:01:22,781 ERROR [JDBCExceptionReporter] A syntax error has occurred. > 14:01:22,781 INFO [DefaultLoadEventListener] Error performing load command > org.hibernate.exception.SQLGrammarException: could not load an entity: > [au.com.reece.Dpparent#1] > > I've tried running the SQL generated by hibernate and it works IF I removed > the schema/catelog info from the table. (not sure if this is an issue.) > ie > select dpparent0_.tab_id as tab1_123_0_,> > dpparent0_.VERSION as VERSION123_0_, > > dpparent0_.parent_name as parent3_123_0_ > from Dpparent dpparent0_ > where dpparent0_.tab_id=2; > > If I used the following code: > Query query = em.createNativeQuery( "SELECT * FROM Dpparent WHERE tab_id = > ?1", Dpparent.class); > query.setParameter(1, 1); > Dpparent p = (Dpparent) query.getSingleResult(); > > The sql generate is: > SELECT * FROM Dpparent WHERE tab_id = ? > and this works fine.> > I cannot seem to find any similar issues on google etc. Anyone have any > suggestions as I'm quite stuck. > > Thanks > David > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > >

My persistence file: <?xml version="1.0" encoding="UTF-8"?> <persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence"; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">; <persistence-unit name="GetProductDetails"> <provider>org.hibernate.ejb.HibernatePersistence</provider> <jta-data-source>java:/InformixDS</jta-data-source> <mapping-file>META-INF/orm.xml</mapping-file> <properties> <property name="hibernate.connection.driver_class" value="com.informix.jdbc.IfxDriver" /> <property name="hibernate.connection.url" value="jdbc:informix-sqli://d0101:8933/uat3:INFORMIXSERVER=reeceuat3" /> <property name="hibernate.connection.username" value="bea" /> <property name="hibernate.connection.password" value="bea" /> <property name="hibernate.dialect" value="org.hibernate.dialect.InformixDialect" /> <property name="hibernate.show_sql" value ="true" /> </properties> </persistence-unit> </persistence> I found with eclipse that to use the JPA at design time i had to specify the schema: ie <?xml version="1.0" encoding="UTF-8"?> <entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm"; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_1_0.xsd"; version="1.0"> <persistence-unit-metadata> <persistence-unit-defaults> <schema>reece</schema> <catalog>uat3</catalog> </persistence-unit-defaults> </persistence-unit-metadata> </entity-mappings> If I do this then at design time my entities get mapped to the database. However at runtime it appends the uat3.reece to the table which makes it fail. So if I remove the schema/catalog details from the orm.xml file it works correctly at run time but at design time I cannot map the entities variables to the database fields. David