how to handle autogenerated numbers in oracle using CMP

[Nova Bhojwani]

i am using CMP entity beans and am using oracle as my database, i beleive that oracle does not support autonumber generated fields as does SQL Server.
when using CMP we do not write any SQL code so how do i make a column autogenerated ...for example in a table users the userID should get incremented on it's own.....
if possible send through a snippet of the code of how it is handled.
thanks......

[Desai Sandeep]

Hi,
I believe Oracle supports autonumber generation using SEQUENCES.You have to create a sequence using CREATE SEQUENCE DDL statement.This may be done using SQL PLUS.Then you can autogenerate numbers using it using SEQUENCE_NAME.NEXTVAL in your SQL query statement.
Hope this helps.
Regards,
Sandeep

• Sun Certified Programmer for Java 2 Platform Scored 93 per cent
  
• Oracle JDeveloper Rel. 3.0 - Develop Database Applications with Java Scored 56 out of 59
  
• IBM Enterprise Connectivity with J2EE Scored 72 per cent
  
• Enterprise Development on the Oracle Internet Platform Scored 44 out of 56
  

[This message has been edited by Desai Sandeep (edited May 17, 2001).]

[Peter den Haan]

Sandeep's right. To elaborate just a little bit:
You can use that sequence in your EJB to retrieve the primary key before executing the actual INSERT statement. A frequently used alternative is to generate the key in an Oracle row-level insert trigger.
Since ejbCreate() needs to return the primary key value, doing a separate "SELECT sequence.NEXTVAL FROM DUAL" just before the INSERT is probably most convenient. If you would generate it in a trigger you would have to figure out how to get the generated value so you can return it. The only reliable ways as far as I know would be using an alternative primary key in your table (if there is one) to reread the record, or to add a RETURNING clause to the INSERT statement. I'm not sure how to do the latter using JDBC (anyone?).
Sequences can be a bit harder to use than SQL Server identity columns but they're much more flexible.
- Peter

[syed mraza]

hi guys
i've a query, in CMP we dont write sql statements then how to handle this situation??? i have a table whose PK is autognerated(seq) so whn i'm calling ejbCreate(not_pk_field, some_other_not_pk_field)i'm not able to create a row in the table. the reason: CMP didnt somehow generated the key first. any suggestions how i can let it know tht it has a sequence to generated PK? i know there is some tags to be added if one is using weblogic 6.1 but i;m using 6.0, those tags dont work in 6.0.
any help in this regard would be appriciated.
rgds

[Anonymous]

In CMP you would create a method getNextID for example which gets a connection from the pool, and sets the value of the id field in the ejbCreate method of the bean.
However, WLS 6.1 has a framework for key generation for (AFAIR) Oracle, Sybase and DB2.

[Jerome Lumpkin]

It seems a little odd to go with CMP and then place SQL code in your bean or helper class to access the database for a unique id.
Also, this type of access will ruin your portability as it is an Oracle Method, not a universal solution.
Why don't you consider either having an entity bean with one persistent field, an int/long that just increases by one or either perhaps consider Local Objects.