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......
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 PlatformScored 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).]
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
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
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.
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.
subject: how to handle autogenerated numbers in oracle using CMP