Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to handle autogenerated numbers in oracle using CMP

 
Nova Bhojwani
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1157
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 18944
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic