OpenXava / Discussion / Help (English): Why an identity column as key is NULL

patrick_kg - 2007-10-22

Hi Javi,

I want to use an identity column key instead of (code_siren,code_nic) in my model Recensement,
<component name="Recensement">
<entity>
<property name="id" key="true" type="int" hidden="true">
<default-value-calculator class="org.openxava.calculators.IdentityCalculator" on-create="true"/>
</property>
<property name="codeSIREN" type="String" size="9" required="true"/>
<property name="codeNIC" type="String" size="5" required="true"/>

<entity-mapping table="KLIF.CHR_RECENS_CREA_ETB">
        <property-mapping property="id" column="ID"/>
        <property-mapping property="codeSIREN" column="CODE_SIREN"/>
        <property-mapping property="codeNIC" column="CODE_NIC"/>
</entity-mapping>

Table in Database:
CREATE TABLE CHR_RECENS_CREA_ETB
(
ID NUMBER(10) PRIMARY KEY,
CODE_SIREN     VARCHAR2(9),---PRIMARY KEY,
CODE_NIC     VARCHAR2(5),---PRIMARY KEY,

CODE_SECTEUR         VARCHAR2(10),
TEL                  VARCHAR2(15),
FAX                  VARCHAR2(15)
);

But, I have the error messsage:
SEVERE: ORA-01400: cannot insert NULL into ("KLIF"."CHR_RECENS_CREA_ETB"."ID")
22 oct. 2007 15:03:56 org.openxava.model.impl.POJOPersistenceProviderBase create
SEVERE: could not insert: [org.openxava.baseklif.model.Recensement]
org.hibernate.exception.ConstraintViolationException: could not insert: [org.openxava.baseklif.model.Recensement]
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:40)

It is necessary to have a particular configuration for this ?

Thank you very much.

Patrick

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- Javier Paniza - 2007-10-23
  
  Hi Patrick,
  
  your OX component is OK.
  The problem is in the table declaration,
  you must declare ID as an autoincrement column.
  
  For example, in AS/400 this is done in this way:
  ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY
  
  Look at your database documentation for the case of your database.
  
  Cheers
  Javi
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
  - Trifon (An ADempiere founder) - 2007-10-23
    
    Hi Javi,
    
    >your OX component is OK.
    >The problem is in the table declaration,
    >you must declare ID as an autoincrement column.
    >
    >For example, in AS/400 this is done in this way:
    >ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY
    
    I though that Hibernate will create proper DB script?
    Why then it is necessary column to be specified as identity in OpenXava xml file?
    
    Kind regards,
    Trifon
    
    If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
    - Javier Paniza - 2007-10-24
      
      Hi Trifon,
      
      > I though that Hibernate will create proper DB script?
      Yes. You are right
      If you execute the updateSchema ant target of OX,
      that uses hibernate, it generates the correct schema.
      For example, if you are using AS/400 dialect, hibernate
      will generate a line as the above one.
      
      > Why then it is necessary column to be specified as identity in OpenXava xml file?
      If in your OX componentes you write:
      <property name="number" type="Integer" key="true" size="5" required="false">
          <default-value-calculator class="org.openxava.calculators.IdentityCalculator" on-create="true"/>
      </property>
      
      OpenXava generates a .hbm.xml with the next content:
      <id name="number" column="NUMBER" access="field" type='java.lang.Integer' length='5'>
          <generator class='identity'/>
      </id>
      
      In Hibernate you must indicate your id genaration strategy, therefore
      you also need to do it in OpenXava.
      If not, how do you choose the generation strategy for your key ?
      
      Cheers
      Javi
      
      If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
      - Trifon (An ADempiere founder) - 2007-10-24
        
        Hi Javi,
        
        >In Hibernate you must indicate your id genaration strategy, therefore
        >you also need to do it in OpenXava.
        >If not, how do you choose the generation strategy for your key ?
        
        Yes, I agree with you that we need to specify strategy in OpenXava if hibernate can manage key generation. I asked second question as i assumed that hibernate do not manage sequences.
        
        Thank you for your detailed answer and description!
        
        Kind regards,
        Trifon
        
        If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- patrick_kg - 2007-10-24
  
  Hi Javi,
  
  Indeed, the Oracle Database Management System doesn't have an auto-increment column type.
  Do you have another solution in Hibernate ?
  
  Best regards,
  
  Patrick
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
  - Javier Paniza - 2007-10-25
    
    Hi,
    
    > the Oracle Database Management System doesn't have an auto-increment column type
    You are right. Oracle does not support identity columns, but it support sequences.
    Just use sequence.
    
    In OpenXava you can define your key property in this way:
    
    <property name="id" key="true" type="int" hidden="true">
        <default-value-calculator
            class="org.openxava.calculators.SequenceCalculator" on-create="true">
            <set property="sequence" value="XAVATEST_SIZE_ID_SEQ"/>
        </default-value-calculator>
    </property>
    
    Look at section 3.8.5 of reference guide.
    
    About the definition in your database look at the Oracle documentation,
    or, as alternative, you can execute the ant target executeUpdate, and see
    the table created by the tool, that is a Hibernate utility.
    
    Cheers
    Javi
    
    If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- patrick_kg - 2007-10-25
  
  Hi Javi,
  
  In
  <set property="sequence" value="XAVATEST_SIZE_ID_SEQ"/>
  the "XAVATEST_SIZE_ID_SEQ" is parameter or a table name in Database ?
  
  Kind regards,
  
  Patrick
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- patrick_kg - 2007-10-25
  
  Hi Javi,
  
  Ok, this is the sequence name. I add a sequence Oracle: ID_SEQ_KLIF_USER and
  the SQL request functions in Oracle:
  SELECT ID_SEQ_KLIF_USER.NEXTVAL FROM dual;
  1
  
  But, for creating a recensement in our OpenXava application,
  I have the following error messages:
  25 oct. 2007 14:13:45 org.hibernate.util.JDBCExceptionReporter logExceptions
  SEVERE: ORA-00923: FROM keyword not found where expected
  
  25 oct. 2007 14:13:45 org.openxava.model.impl.POJOPersistenceProviderBase create
  SEVERE: could not get next sequence value
  org.hibernate.exception.SQLGrammarException: could not get next sequence value
      at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
      at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
      at org.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:96)
      at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:99)
      at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:187)
      at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
      at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:172)
      at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
      at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
      at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:535)
      at org.hibernate.impl.SessionImpl.save(SessionImpl.java:523)
      at org.hibernate.impl.SessionImpl.save(SessionImpl.java:519)
      at org.openxava.model.impl.HibernatePersistenceProvider.persist(HibernatePersistenceProvider.java:27)
      at org.openxava.model.impl.POJOPersistenceProviderBase.create(POJOPersistenceProviderBase.java:148)
      at org.openxava.model.impl.MapFacadeBean.create(MapFacadeBean.java:704)
      at org.openxava.model.impl.MapFacadeBean.create(MapFacadeBean.java:42)
      at org.openxava.model.MapFacade.create(MapFacade.java:78)
      at org.openxava.actions.SaveAction.execute(SaveAction.java:27)
      at org.openxava.controller.ModuleManager.executeAction(ModuleManager.java:328)
      at org.openxava.controller.ModuleManager.executeAction(ModuleManager.java:257)
      at org.openxava.controller.ModuleManager.execute(ModuleManager.java:224)
      at org.apache.jsp.xava.module_jsp._jspService(module_jsp.java:335)
      at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
      at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:332)
      at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
      at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
      at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
      at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
      at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
      at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
      at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
      at java.lang.Thread.run(Thread.java:595)
  Caused by: java.sql.SQLException: ORA-00923: FROM keyword not found where expected
  
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
      at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
      at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:582)
      at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1986)
      at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:880)
      at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2516)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2850)
      at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:609)
      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:537)
      at com.p6spy.engine.logging.P6LogPreparedStatement.executeQuery(P6LogPreparedStatement.java:171)
      at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
      at org.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:75)
      ... 39 more
  
  I observe the log of sql:
  25/10/07 13:13:45,312|31|1|statement|select next value for KLIF.ID_SEQ_KLIF_USER from dual_KLIF.ID_SEQ_KLIF_USER|select next value for KLIF.ID_SEQ_KLIF_USER from dual_KLIF.ID_SEQ_KLIF_USER
  25/10/07 13:13:45,343|15|1|rollback||
  
  It seems for me that the word next poses the problem, instead of NEXTVAL ?
  
  Best regards,
  Patrick
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
  - Javier Paniza - 2007-10-26
    
    Hi Patrick,
    
    > his is the sequence name
    Correct!
    
    > SEVERE: ORA-00923: FROM keyword not found where expected
    
    Uhhmm!
    Are you using the correct hibernate dialect, that is
    'org.hibernate.dialect.Oracle9Dialect' ?
    Revise it!
    Maybe you are using Hypersonic dialect or so...
    
    Think that OX uses Hibernate and Hibernate supports
    sequences in Oracle. Moreover there are OX developers
    that uses Oracle sequence with OpenXava.
    
    Tell me if this works for you.
    
    Cheers
    Javi
    
    If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- patrick_kg - 2007-10-29
  
  Hi Javi,
  
  I use the OX portal for my application.
  Here my current file of configuration (hibernate.cfg.xml):
  <hibernate-configuration>
      <session-factory>
          <property name="hibernate.connection.datasource">@datasource.prefix@/KLIFDS</property>
          <property name="hibernate.dialect">@hibernate.dialect@</property>
          <property name="hibernate.show_sql">false</property>
  
          @hibernate.properties@
      </session-factory>
  </hibernate-configuration>
  
  Where (in which file) to add this line :
  hibernate.dialect=org.hibernate.dialect.Oracle9Dialect
  
  Kind regards
  Patrick
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- patrick_kg - 2007-10-29
  
  Hi Javi,
  
  Ok, I add this line in tomcat-hypersonic.properties
  and this works well.
  
  Best regards
  Patrick
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
  - Javier Paniza - 2007-10-30
    
    Hi Patrick,
    
    > I add this line in tomcat-hypersonic.properties
    
    Maybe you have to rename this file to tomcat-oracle.properties.
    
    Why do not put an entry in the wiki (in how-to section), about
    "how to use an autogenerated key with Oracle in OpenXava ?" or so ?
    
    I think it would be a good contribution.
    
    Cheers
    Javi
    
    If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- patrick_kg - 2007-10-30
  
  Hi Javi,
  
  >Maybe you have to rename this file to tomcat-oracle.properties.
  I try to do what you wish
  -rename the file tomcat-hypersonic.properties to tomcat-oracle.properties
  -modify in build.xml
  <property name="configuration" value="tomcat-oracle" />
  
  It goes still.
  
  Kind regards
  Patrick
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Why an identity column as key is NULL

Automatic frontend engine for Java

Forums

Help

Why an identity column as key is NULL

Why an identity column as key is NULL

Automatic frontend engine for Java

Forums

Help

Why an identity column as key is NULL document.SUBSCRIPTION_OPTIONS = { "thing": "topic", "subscribed": false, "url": "subscribe", "icon": { "css": "fa fa-envelope-o" } };

Why an identity column as key is NULL