Very useful information. I didn't know that.
Thanks,
Dennis
"Zetie, Carl" wrote:
> 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".
===========================================================================
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".
