Actually that's a very common misconception, but if it were true CURRVAL
would not be nearly as useful as it is. On Oracle at least, CURRVAL returns
the last sequence number fetched with NEXTVAL by your particular session -
not the last value fetched from the sequence by *any* session. In other
words, Oracle designed NEXTVAL/CURRVAL to give you just the behavior you're
looking for. That's why, incidentally, you can't select CURRVAL until you've
selected NEXTVAL at least once in a given session.
(Conversely, you can't rely on CURRVAL to tell you anything about the next
value in the sequence that your next call to NEXTVAL would return as lots of
other sequence numbers may have been used up since you last got one. In fact
the name SEQUENCE is a bit of a misnomer - they aren't so much sequential as
monotonic).
At least, that's how its designed to work with Oracle. YRDBMSMV.
regards,
Carl
> -----Original Message-----
> From: Dennis Djenfer [mailto:[EMAIL PROTECTED]]
> Sent: Friday, August 24, 2001 6:20 PM
> To: [EMAIL PROTECTED]
> Subject: Re: [EJB-INT] Primary Key Generation
>
>
> Thanks Carl,
>
> but I don't think that solution will work in a environment
> with clustered
> application servers. When EJB1 on app-server A inserts a
> record into table "emp"
> and then subsequently selects the current value from the
> sequence, EJB2 on
> app-server B may have made a similar insert into the same
> table between the two
> calls from EJB1. The result is that EJB1 retrieves the wrong
> sequence number.
> That's one of the reasons why I want to create and retrieve
> the primary key in
> one go.
>
> // Dennis
>
>
> "Zetie, Carl" wrote:
>
> > Dennis,
> >
> > If for any reason you can't get the binding value from the
> Returning clause,
> > you could retrieve it directly with a statement like
> >
> > select empseq.currval into :bnd1 from sys.dual;
> >
> > empseq.currval will give you the value you just inserted, without
> > incrementing the sequence.
> >
> > regards,
> > Carl
> >
> > > -----Original Message-----
> > > From: Dennis Djenfer [mailto:[EMAIL PROTECTED]]
> > > Sent: Friday, August 24, 2001 4:51 PM
> > > To: [EMAIL PROTECTED]
> > > Subject: [EJB-INT] Primary Key Generation
> > >
> > >
> > > Hi all,
> > >
> > > One way to generate a primary key, insert a new row and
> retrieve the
> > > generated key in one call is to use the following SQL
> > > sentence (at leat
> > > in a Oracle database):
> > >
> > > INSERT INTO emp VALUES (empseq.nextval, 'LEWIS', 'CLARK')
> RETURNING id
> > > INTO :bnd1
> > >
> > > How do I read the binding variable (:bnd1) that contains my
> > > primary key
> > > (from column "id") from java? Will the binding variable be
> > > presented in
> > > the resultset like:
> > >
> > > primaryKey = rs.getInt(bnd1)
> > >
> > > or how do I get the value from the Returning clause?
> > >
> > > If somebody wonder why I don't test it myself it's because I
> > > don't have
> > > access to a development environment right now
> > >
> > >
> > > // Dennis
> > >
> > > ==============================================================
> > > =============
> > > To unsubscribe, send email to [EMAIL PROTECTED] and
> > > include in the body
> > > of the message "signoff EJB-INTEREST". For general help,
> > > send email to
> > > [EMAIL PROTECTED] and include in the body of the
> message "help".
> > >
> > >
>
> ==============================================================
> =============
> To unsubscribe, send email to [EMAIL PROTECTED] and
> include in the body
> of the message "signoff EJB-INTEREST". For general help,
> send email to
> [EMAIL PROTECTED] and include in the body of the message "help".
>
>
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
