5 Replies Latest reply on May 11, 2003 3:23 PM by richaosu

    JBoss 3.2, PostgreSQL and auto-increment

    marcuslarsson

      I'm trying to use the new auto-increment feature of JBoss 3.2. Since I was unable to find any documentation on this (are there any yet?) my first attempt was to simply declare the cmp-field in jbosscmp-jdbc as:

      <cmp-field>
      <field-name>id</field-name>
      <column-name>id</column-name>
      <auto-increment/>
      </cmp-field>

      When I deploy the entity bean I get an exception:
      java.lang.IllegalStateException: auto-increment template not found.

      There are obviously more things that need to be done in order to get this to work. How do I proceed from here?

      /Marcus

        • 1. Re: JBoss 3.2, PostgreSQL and auto-increment
          pup99

          Yeah I had that problem when I didn't have the entity command in my jbosscmp-jdbc.xml file in the entity bean. For mysql it is
          <entity-command name="mysql-get-generated-keys">
          but I don't know what it is for PostgreSQL. I found this here on the forums. So for my file I have


          <ejb-name>Trial</ejb-name>
          <create-table>true</create-table>
          <remove-table>false</remove-table>
          <table-name>temp</table-name>

          <cmp-field>
          <field-name>id</field-name>
          <auto-increment/>
          </cmp-field>

          <entity-command name="mysql-get-generated-keys">
          </entity-command>



          Though I have still not got it working perfectly yet, I have been able to get it to insert into the db correctly, I have just had problems getting it back.

          Pup

          • 2. Re: JBoss 3.2, PostgreSQL and auto-increment
            marcuslarsson

            I've now changed the cmp-field declaration to:

            <cmp-field>
            <field-name>id</field-name>
            <auto-increment/>
            </cmp-field>

            I've inserted create-table and remove-table elements in the entity declaration.

            The default section of my jbosscmp-jdbc reads:


            java:/PostgresDS
            <datasource-mapping>PostgreSQL</datasource-mapping>
            <create-table>true</create-table>
            <remove-table>true</remove-table>
            <pk-constraint>true</pk-constraint>
            <preferred-relation-mapping>foreign-key</preferred-relation-mapping>
            <unknown-pk>
            <unknown-pk-class>java.lang.Integer</unknown-pk-class>
            <field-name>id</field-name>
            <column-name>id</column-name>
            <jdbc-type>INTEGER</jdbc-type>
            <sql-type>INT(11)</sql-type>
            <auto-increment/>
            </unknown-pk>
            <entity-command name="key-generator"/>


            I've tried both the key-generator and pk-sql entity command. I still get: java.lang.IllegalStateException: auto-increment template not found. Any ideas?

            /Marcus

            • 3. Re: JBoss 3.2, PostgreSQL and auto-increment
              loumaus

              thanks for you help .....

              my mysql auto_increment now works very well..
              what for problems do you have getting the pk back ???

              • 4. Re: JBoss 3.2, PostgreSQL and auto-increment
                hoos

                Jboss 3.2.0
                Sun JDK (build 1.4.1_02-b06)
                Xdoclet 1.3bdev ~ generates jboss my jbosscmp-jdbc.xml (3.2)
                Postgresql 7.2

                Hello,

                I have spent a few days trying to get this working and failed to get jboss to generate unknown primary keys in a table that has one unknownpk and an ordinary pk (not my schema I just have to write to it).

                I have tried all the above suggestions and most of the ones on
                http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t= no luck.

                Before I explain my work around here are some of the things that didn't work

                1) Adding
                <auto-increment/>

                <entity-command name="pk-sql">
                SELECT (IDENT_CURRENT('tOrder') + 1)
                </entity-command>

                to jbosscmp-jdbc.xml either to or specificly ot or
                both.

                I noticed that the default command JDBCLoadEntityCommand was always being used no matter what entity command I set in my jbosscmp-jdbc.xml , so as a test I edited the standardjbosscmp-jdbc.xml setting for postgres entity command from default to pk-sql, jboss then picked up the pk-sql command but assigned the return value to the primary key rather than the unknown primary key.

                At this point I found some info on thread 27944 that seemed to suggest the functionality I require was not avaible yet
                and am now using the following workaround:

                I created a stateless session bean called autonumber that has one method getValue which executes a prepared statement 'select nextval ('MySequence') and thus returns the correct incremented sequence number to the caller.

                I then look up this bean in my EntityBeans setEntityContext method and store it in member varible.

                In my Entity beans create method I set the correct id value in the valueobject by retreiving it using autonumber. So far I have used this workaround for several different beans with different sequences and load tested it with random data, creating and deleting approx ten thousand records per bean over 48 hours without any problems.

                From reading the other mesages on this topic I can see there are a few packages that do this sort of thing as well as some classes that JBoss provides. I guess if I find any more time I will try these out but for now this will have to do.

                If anyone can see any major flaws in what I have done here or possible caveats please tell me, I will be continuing my own load tests for a few more days then the thing will have to go live.

                I have used statless session beans with stored procs extensivley on jboss without any problems (apart from development time) this has been my first effort with CMP on jboss and apart from the general learning time the developent time was a lot faster.

                Cheers Hoos.

                • 5. Re: JBoss 3.2, PostgreSQL and auto-increment
                  richaosu

                  To resolve the 'java.lang.IllegalStateException: auto-increment template not found' message, add <auto-increment-template>?1 STUFF</auto-increment-template> to conf/standardjbosscmp-jdbc.xml for your datasource. See the MySQL entry for reference. I do not know what STUFF will work for PostgreSQL.