12 Replies Latest reply on May 5, 2004 4:26 AM by Alexey Loubyansky

    Informix-serial, auto-increment and discrete value

    Dirk Niemeier Newbie

      Hi,
      I am using Informix serial columns with auto-increment funktion well. But there is an problem to insert an known value in the serial field like Infomix allows. In Informix an automatic new value will only be created when I insert a new entry without specify the serial field, or when I insert a value "0" to serial column. How can I use this feature with jboss EJBs ?
      Perhaps any attribute to the auto-increment property?

      TIA
      Dirk

        • 1. Re: Informix-serial, auto-increment and discrete value
          sesques Novice

          Hi,

          You have to use the unknown-pk tag with an entity-command name set to "informix-serial".

          Search in this forum, it is described many times.

          • 2. Re: Informix-serial, auto-increment and discrete value
            Dirk Niemeier Newbie

            Hi,
            I described, that I use "informix-serial" with unknown-pk already. And it works. The problem is to insert an known value in that "auto-increment" field.

            Example the next new Value of the serial value will be 1000. And in the column there are wholes of values below that new value 1000. Perhaps 600. And I have to insert the 600 into the tablecolumn.
            When I use the auto-increment funktionally it is not possible to insert the value 600. The container always generate a new value alway the next akt value.
            (I need this to import old entries from an other existing application.And the value is the unique value for an existing process)

            TIA
            Dirk

            • 3. Re: Informix-serial, auto-increment and discrete value
              sesques Novice

              As far I I know, I think it's impossible to do that. You cannot operate in both modes simulteanously.


              • 4. Re: Informix-serial, auto-increment and discrete value
                Dirk Niemeier Newbie

                Does anybody know this behavior certainty ?

                • 5. Re: Informix-serial, auto-increment and discrete value
                  richaosu Newbie

                  Dirk,

                  FWIW, I'm in the same boat I have a working CMP entity for an Informix table with a serial key column. Inserting new entities generates the the row using the one-up counter. All good.

                  But, like you I also have the need to import rows using existing values. When I turn on the JAWS logging, I see that JAWS does not include the serial field name or value in the INSERT statement. This is fine for the usual auto-increment behavior but there's no way to use it to store an existing value.

                  The only alternative that I can think of is to create another EJB name in the deployment descriptors and bind it to the same entity class but omit the <auto-increment/> and the <entity-command name="informix-serial"> tags. However, this seems bloated when there are many tables that have serial columns.

                  • 7. Re: Informix-serial, auto-increment and discrete value
                    richaosu Newbie

                    Dirk, Here's an idea that may work. Add this method to org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCInformixCreateCommand:

                    // Overrides org.jboss.ejb.plugins.cmp.jdbc.JDBCIdentityColumnCreateCommand.isInsertField(...)
                    protected boolean isInsertField(JDBCFieldBridge field)
                    {
                    // To allow PK fields in the insert.
                    return org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractCreateCommand.isInsertField(field);
                    }

                    The idea is to skip the logic in JDBCIdentityColumnCreateCommand that omits the PK column. As I see it, this would put the PK column back into the INSERT statement for the Informix driver only. Then the programmer can use a 0 value to tell Informix to auto-increment or use a non-zero value for a direct insert.

                    • 8. Re: Informix-serial, auto-increment and discrete value
                      Alexey Loubyansky Master

                      Please, let us know if it works.

                      • 9. Re: Informix-serial, auto-increment and discrete value
                        richaosu Newbie

                        Yes, Alex & Dirk, that worked; although, the implementation was a little different from the idea above.

                        I found that the direct call to isInsertField method in the JDBCAbstractCreateCommand class did not work because the usage non-static method in a static context would not compile. So I just copied the isInsertField into the JDBCInformixCreateCommand class.

                        At runtime, I know see that JAWS includes the previously omitted Identity column (Informix serial column) and I have confirmed that the inserts are using the serial values that I give it rather than auto-incrementing.

                        The steps I followed were to:


                        Get the V3.2.3 source.
                        Add isInsertField method to JDBCInformixCreateCommand to JDBCInformixCreateCommand class (see code below).
                        Build JBoss.
                        Copied new ../server/defaut/jboss.jar to my installed V3.2.3
                        Ran a job that read from source parent / child tables and inserted into a target.
                        Review logs for anticiptaed behavior.
                        Checked the data in the target tables and confimed that it has the same identity keys as the source.


                        /***************************************
                         * *
                         * JBoss: The OpenSource J2EE WebOS *
                         * *
                         * Distributable under LGPL license. *
                         * See terms of license at gnu.org. *
                         * *
                         ***************************************/
                        package org.jboss.ejb.plugins.cmp.jdbc.keygen;
                        
                        import java.sql.PreparedStatement;
                        import java.sql.SQLException;
                        import java.lang.reflect.Method;
                        
                        import javax.ejb.EJBException;
                        
                        import org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCFieldBridge;
                        import org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMRFieldBridge;
                        import org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMPFieldBridge;
                        
                        import org.jboss.ejb.plugins.cmp.jdbc.JDBCIdentityColumnCreateCommand;
                        import org.jboss.ejb.plugins.cmp.jdbc.WrappedStatement;
                        import org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager;
                        import org.jboss.ejb.plugins.cmp.jdbc.metadata.JDBCEntityCommandMetaData;
                        import org.jboss.ejb.EntityEnterpriseContext;
                        import org.jboss.deployment.DeploymentException;
                        
                        /**
                         * Create command for Informix that uses the driver's getSerial method
                         * to retrieve SERIAL values. Also supports SERIAL8 columns if method
                         * attribute of entity-command is set to "getSerial8"
                         *
                         * @author <a href="mailto:jeremy@boynes.com">Jeremy Boynes</a>
                         */
                        public class JDBCInformixCreateCommand extends JDBCIdentityColumnCreateCommand
                        {
                         private static final String NAME = "class-name";
                         private static final String DEFAULT_CLASS = "com.informix.jdbc.IfxStatement";
                         private static final String METHOD = "method";
                         private static final String DEFAULT_METHOD = "getSerial";
                        
                         private String className;
                         private String methodName;
                         private Method method;
                        
                         public void init(JDBCStoreManager manager) throws DeploymentException
                         {
                         super.init(manager);
                         try
                         {
                         Class psClass = Thread.currentThread().getContextClassLoader().loadClass(className);
                         method = psClass.getMethod(methodName, null);
                         }
                         catch(ClassNotFoundException e)
                         {
                         throw new DeploymentException("Could not load driver class: " + className, e);
                         }
                         catch(NoSuchMethodException e)
                         {
                         throw new DeploymentException("Driver does not have method: " + methodName + "()");
                         }
                         }
                        
                         // Override org.jboss.ejb.plugins.cmp.jdbc.JDBCIdentityColumnCreateCommand.isInsertField(...)
                         // See JDBCAbstractCreateCommand.isInsertField(field);
                         protected boolean isInsertField(JDBCFieldBridge field)
                         {
                         // To allow PK fields in the insert.
                         boolean result =
                         !(field instanceof JDBCCMRFieldBridge)
                         && field.getJDBCType() != null
                         && !field.isReadOnly();
                         if(field instanceof JDBCCMPFieldBridge)
                         result = result && !((JDBCCMPFieldBridge) field).isRelationTableField();
                         return result;
                         }
                        
                         protected void initEntityCommand(JDBCEntityCommandMetaData entityCommand) throws DeploymentException
                         {
                         super.initEntityCommand(entityCommand);
                         className = entityCommand.getAttribute(NAME);
                         if(className == null)
                         {
                         className = DEFAULT_CLASS;
                         }
                         methodName = entityCommand.getAttribute(METHOD);
                         if(methodName == null)
                         {
                         methodName = DEFAULT_METHOD;
                         }
                         }
                        
                         protected int executeInsert(PreparedStatement ps, EntityEnterpriseContext ctx) throws SQLException
                         {
                         int rows = ps.executeUpdate();
                        
                         // remove any JCA wrappers
                         while(ps instanceof WrappedStatement)
                         {
                         ps = (PreparedStatement)((WrappedStatement)ps).getUnderlyingStatement();
                         }
                         try
                         {
                         Number pk = (Number)method.invoke(ps, null);
                         pkField.setInstanceValue(ctx, pk);
                         return rows;
                         }
                         catch(RuntimeException e)
                         {
                         throw e;
                         }
                         catch(Exception e)
                         {
                         // throw EJBException to force a rollback as the row has been inserted
                         throw new EJBException("Error extracting generated keys", e);
                         }
                         }
                        }
                        


                        • 10. Re: Informix-serial, auto-increment and discrete value
                          Alexey Loubyansky Master

                          Please, confirm that if you don't assign any value to the primkey-field cmp field the primary key value is generated by Informix and assigned correctly to the instance.
                          I hope it works not with some specific informix driver?

                          • 11. Re: Informix-serial, auto-increment and discrete value
                            Alexey Loubyansky Master

                            Please, confirm that if you don't assign any value to the primkey-field cmp field the primary key value is generated by Informix and assigned correctly to the instance.
                            I hope it works not with some specific informix driver?