3 Replies Latest reply on Sep 19, 2003 9:41 AM by Arnold Hahamyan

    PK-SQL with Oracle problem

    Jon Lido Newbie

      I'm trying to use the JDBCPkSqlCreateCommand to create an entity with an incrementing primary key. I've run into some difficulty.

      The primary-key in question is set as non-nullable in the database. In the JBoss log I can see my pk-sql execute and acquire a correct primary key. However, when it attempts to actually insert the row I get a "Could not create entity" error from JBoss and "java.sql.SQLException: ORA-01400: cannot insert NULL into ..." and then my primary key column name.

      Here are the class-level xdoclet tags I'm using that I believe to be relevant. I'll gladly provide more details if someone believes they'll be useful.

      * @jboss.unknown-pk
      * class="java.lang.Long"
      * column-name="VTPEOPLE_UID"
      * jdbc-type="BIGINT"
      * sql-type="NUMBER(19)"
      * auto-increment="true"
      * @jboss.entity-command name="pk-sql"
      * @jboss.entity-command-attribute
      * name="pk-sql"
      * value="SELECT vtregistry.edreg_vtpeople_uid_seq.nextval FROM dual"

      In the the jbosscmp-jdbc.xml this produces the following:
      <unknown-pk>
      <unknown-pk-class>java.lang.Long</unknown-pk-class>
      <column-name>VTPEOPLE_UID</column-name>
      <jdbc-type>BIGINT</jdbc-type>
      <sql-type>NUMBER(19)</sql-type>
      </unknown-pk>
      <entity-command name="pk-sql">
      SELECT vtregistry.edreg_vtpeople_uid_seq.nextval FROM dual
      </entity-command>

      The source for JDBCPkSqlCreateCommand looks like it goes through each CMP field on the entity, and sets the pk on the one that isUnknownPk(). Is the sample I've listed inadequate to do what I am attempting?

      -Jon

        • 1. Re: Solution (with patch)
          Jon Lido Newbie

          In my case, the primary key class is known. So using an unknown-pk is not really what I wanted. I know the field I want is a Long, and I want to have an field accessor for it. So, I specified the the primkey-field to the deployment descriptor, and added removed the unknown-pk stuff.

          I saw no reason why JDBCPkSqlCreateCommand should only work for Unknown Primary Keys, so I simply removed that check.

          http://filebox.vt.edu/users/jlido/pub/JDBCPkSqlCreateCommand.patch

          Comments?

          -Jon

          • 2. Re: Solution (with patch)
            sraj Newbie

            Hi

            Did you find any other way to achieve this without changing the JDBCPkSqlCreateCommand.

            Thanks

            • 3. Re: Solution (with patch)
              Arnold Hahamyan Newbie

              This looks like has been fixed in JBoss 3.2.2 (RC4), all you have to is specify in jbosscmp-jdbc.xml, for the given entity


              <ejb-name>User</ejb-name>
              <table-name>USR</table-name>

              <cmp-field>
              <field-name>userId</field-name>
              <column-name>USER_ID</column-name>
              </cmp-field>
              <entity-command name="get-generated-keys" class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCOracleCreateCommand">
              SEQ_USR
              </entity-command>


              USER_ID is the primary key here generated from the sequence, the ejb-jar.xml specifies for the entity

              <prim-key-class>java.lang.Long</prim-key-class>
              <cmp-field><field-name>userId</field-name></cmp-field>
              <primkey-field>userId</primkey-field>-->

              among the other usual cmp fields. This works good.