0 Replies Latest reply on Nov 17, 2004 2:59 PM by Dave

    auto-increment problem on PostgreSQL

    Dave Newbie

      I think I read every last post, every last set of instructions, every snippet of anything I could find in the forums as well as some external articles published on how to get auto-increment (auto generated primary keys to work) but none of them seemed to work with my configuration:

      JBOSS 4.0
      Postgres 7.3.x

      JbossCMP would not generate a new key so the insert to the database would fail with a not null constraint violation ...

      I tried all of the instructions about removing the cmp-field for my primary key and using prim-key-class of java.lang.Object and changing my bean class' ejbCreate to return an Object ... still, no luck ... but I stumbled onto this workaround by accident and hope that it will help some other people ... It basically comes down to using the 'pk-sql' EntityCommand instead of the 'postgresql-fetch-seq' EntityCommand ... Here is my example:


      ejb-jar.xml

      <ejb-jar>
       <enterprise-beans>
       <entity>
       <ejb-name>Patron</ejb-name>
       <home>xxx.PatronHome</home>
       <remote>xxx.Patron</remote>
       <ejb-class>xxx.PatronBean</ejb-class>
       <persistence-type>Container</persistence-type>
       <prim-key-class>java.lang.Integer</prim-key-class>
       <reentrant>False</reentrant>
       <cmp-version>2.x</cmp-version>
       <abstract-schema-name>patron</abstract-schema-name>
      
       <cmp-field><field-name>patron_id</field-name></cmp-field>
       <cmp-field><field-name>organization_id</field-name></cmp-field>
       <cmp-field><field-name>patron_status</field-name></cmp-field>
      
       <primkey-field>patron_id</primkey-field>
      
      [snip]
       </entity>
      
       </enterprise-beans>
      
      </ejb-jar>
      


      jbosscmp-jdbc.xml

      <jbosscmp-jdbc>
       <defaults>
       <datasource>@jndi_datasource@</datasource>
       <datasource-mapping>PostgreSQL</datasource-mapping>
      
       <create-table>false</create-table>
       <remove-table>false</remove-table>
      
       <entity-command name="pk-sql">
       <attribute name="pk-sql">SELECT NEXTVAL('SEQ_PATRON') AS next_id</attribute>
       </entity-command>
       </defaults>
      
      
       <enterprise-beans>
       <entity>
       <ejb-name>Patron</ejb-name>
       <cmp-field>
       <field-name>patron_id</field-name>
       <auto-increment/>
       </cmp-field>
       </entity>
       </enterprise-beans>
      </jbosscmp-jdbc>
      


      I did a cursory review of the JDBCPkSqlCreateCommand and JDBCPostgreSQLCreateCommand classes and it appears as though they implement different methods ... so I can't explain why my new value from the sequence is being inserted properly into the SQL that goes to the DB using the pk-sql entity command and why it isn't working for the postgresql-fetch-seq command ...

      So anyway, hopefully this workaround will save other people hours of searching too ...