Help with auto-increment and entity-command

[sysuser1]

Hi, I'm using the auto-increment and entity-command to generate unique primary keys. I customize the JDBCPkSqlCreateCommand with a SQL statement to get a value out of an Oracle Database Sequence. So I have:

<entity-commands>
<entity-command name="CustomerOraSeq"
class="org.jboss.ejb.plugins.cmp.jdbc.JDBCPkSqlCreateCommand">
select CUSTOMER_ORA_SEQ.nextval from DUAL
</entity-command>
</entity-commands>

In my entity, I tried 2 things ... both did not work.

The first approach was simply adding <auto-increment/> to my primary key:
<cmp-field>
<field-name>id</field-name>
<column-name>id</column-name>
<auto-increment/>
</cmp-field>

and specifying the entity-command:
<entity-command name="CustomerOraSeq"/>

The second approach was using unknown-pk
<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="CustomerOraSeq"/>

Both approaches do not seem to be recognizing the auto-increment and/or the pk-sql call since I was always getting ...
00:51:34,093 ERROR [PatientEJB] Could not create entity
java.sql.SQLException: ORA-01400: cannot insert NULL into ("ABC"."CUSTOMER"."ID")

I purposely made the sql in pksql incorrect to check if its executing the sql statement for the Oracle sequence. It complained about invalid SQL syntax, so it was attempting to execute it. But I dont know why my primary key was not getting the generated values ? Am I doing anything wrong ?

[sysuser1]

When I turned on debug, it shows that the pk-sql for the sequence was being executed and the pk sequence number was being generated. But it was not used for the primary key when doing the insert. Has anyone been successful with this auto-increment ?

The debug shows as follows:

01:09:49,408 DEBUG [AdminSessionEJB] Add Customer: Customer[Id: null | Name: Troy
Jones | DOB: 08/04/1975 | Gender: Male | SSN: 111111111 | Phone:
1111111111 | Email: [e-mail] | Address[Id: null | StreeName1: 1st st |
StreetName2: | City: NY | State: NY | ZipCode: 11111 | Country: USA]]
01:09:49,408 DEBUG [ServiceLocator] Getting new local home for java:comp/env/ejb
/local/address
01:09:49,408 DEBUG [EJBHomeFactory] Getting local home: java:comp/env/ejb/local/
address, com.bea.medrec.entities.AddressLocalHome
01:09:49,418 DEBUG [findByEmail] Executing SQL: SELECT t0_o.id FROM CUSTOMER t0_o
WHERE t0_o.email = ?
01:09:49,538 DEBUG [AdminSessionEJB] Customer not found. Creating new customer.
01:09:49,538 DEBUG [ServiceLocator] Getting cached local home for java:comp/env/
ejb/local/address
01:09:49,538 DEBUG [CustomerEJB] Executing SQL: select CUSTOMER_ORA_SEQ.nextval fr
om DUAL
01:09:49,659 DEBUG [CustomerEJB] Create: pk=9
01:09:49,659 DEBUG [CustomerEJB] Executing SQL: SELECT COUNT(*) FROM CUSTOMER WHER
E id=?
01:09:49,809 DEBUG [CustomerEJB] [CustomerEJB.ejbCreate] Executing SQL: INSERT INT
O CUSTOMER (id, address_id, dob, email, first_name, gender, last_name, middle_nam
e, phone, ssn) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
01:09:49,909 ERROR [CustomerEJB] Could not create entity
java.sql.SQLException: ORA-01400: cannot insert NULL into ("ABC"."CUSTOMER"."ID")

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1892)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:109
3)

[bwilly]

Any updates on this? Thanks.

[marcoyeung]

hi,
I have successfully generated primary key by using JBOSS 3.2.3 's entity-commands features.

jbosscmp-jdbc looks like this:

<enterprise-beans>

.......
<cmp-field>
<field-name>id</field-name>
<column-name>ID</column-name>
<auto-increment/>
</cmp-field>
........
<entity-command name="Oracle-sequence"/>

</enterprise-beans>

<entity-commands>
<entity-command name="Oracle-sequence" class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCOracleCreateCommand">
SEQUENCE_EJB
</entity-command>
</entity-commands>


SEQUENCE_EJB is a sequence in my Oracle 8.1.7 db.

Which version of JBOSS are you using??, I think the above code suitable for JBOSS 3.2.2. and 3.2.3.

==Hope the above helps, here comes my problem, pls HELP===

If my table has a trigger to generate primary key for SQL statment, when BEAN get a sequence and then insert back to this table, it will fire the trigger in the table too. cause the primary key number advance 1 more, so in my table, I have primary key like : 1, 3, 5, 7 .........

is there any one know how to solve this if I what to use JBOSS auto-key generation for BEAN and also trigger auto-key generation for normal SQL statement in one table.

Pls HELP, my system can't lost primary key sequence.


many thanks
Marco

[sbrbot]

You cannot just define <unknown-pk> for your PK field in DD file. <unknown-pk> requires diferent approach in bean class development defining primary key as an java.lang.Object.