OpenJPA
  1. OpenJPA
  2. OPENJPA-472

iSeries DB2 problem with using @GeneratedValue(strategy=GenerationType.SEQUENCE ...)

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.0.1
    • Fix Version/s: 1.0.2, 1.1.0
    • Component/s: sql
    • Labels:
      None
    • Environment:
      iSeries V5R4, DB2, jt400.jar

      Description

      I get the following error when I try to insert new row to a table that uses sequence object for identity.

      <openjpa-1.0.1-r420667:592145 fatal store error> org.apache.openjpa.persistence.RollbackException: [SQL0029] INTO clause missing from embedded statement.

      {VALUES NEXTVAL FOR SEC_FAPKDMC} [code=-29, state=42601]
      at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:419)
      at xxx.xxx.xxx.xxx..main(JPA_test_03.java:66)
      Caused by: <openjpa-1.0.1-r420667:592145 nonfatal general error> org.apache.openjpa.persistence.PersistenceException: [SQL0029] INTO clause missing from embedded statement. {VALUES NEXTVAL FOR SEC_FAPKDMC}

      [code=-29, state=42601]
      at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:3938)
      at org.apache.openjpa.jdbc.sql.DB2Dictionary.newStoreException(DB2Dictionary.java:464)
      at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:97)
      at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:83)
      at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:59)
      at org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:65)
      at org.apache.openjpa.util.ImplHelper.generateValue(ImplHelper.java:160)
      at org.apache.openjpa.util.ImplHelper.generateFieldValue(ImplHelper.java:144)
      at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(JDBCStoreManager.java:557)
      at org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:450)
      at org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:426)
      at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(JDBCStoreManager.java:541)
      at org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(DelegatingStoreManager.java:134)
      at org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(StateManagerImpl.java:501)
      at org.apache.openjpa.kernel.StateManagerImpl.preFlush(StateManagerImpl.java:2770)
      at org.apache.openjpa.kernel.PNewState.beforeFlush(PNewState.java:39)
      at org.apache.openjpa.kernel.StateManagerImpl.beforeFlush(StateManagerImpl.java:940)
      at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:1892)
      at org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:1852)
      at org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1770)
      at org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
      at org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1292)
      at org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:861)
      at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:408)
      ... 1 more
      Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: [SQL0029] INTO clause missing from embedded statement.

      {VALUES NEXTVAL FOR SEC_FAPKDMC}

      [code=-29, state=42601]
      at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:201)
      at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$000(LoggingConnectionDecorator.java:57)
      at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:228)
      at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:163)
      at org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:140)
      at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:152)
      at org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.getSequence(NativeJDBCSeq.java:274)
      at org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.nextInternal(NativeJDBCSeq.java:211)
      at org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:60)
      ... 19 more

      I have the following in persistence.xml

      <property name="openjpa.jdbc.DBDictionary" value="db2"/>
      <property name="openjpa.ConnectionDriverName" value="com.ibm.as400.access.AS400JDBCDriver"/>

      I was able to fix the problem by modifying the following code.

      I added:
      nextSequenceQuery = "SELECT NEXTVAL FOR

      {0}

      FROM "
      + "SYSIBM.SYSDUMMY1";

      to:
      package: org.apache.openjpa.jdbc.sql
      Class: DB2Dictionary
      method: public void connectedConfiguration(Connection conn) throws SQLException

      right after the code:
      case db2ISeriesV5R4OrLater:

      iSeries that have earlier versions might have the same problem.
      If there is a problem, the same code should probably be added to "case db2ISeriesV5R3OrEarlier: " section.

      I'm new to OpenJPA, so it will be great if some one can verify that the fix was done appropriately.

      Thank you,

      Ken Maruyama

        Activity

          People

          • Assignee:
            Catalina Wei
            Reporter:
            Ken Maruyama
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development