Re: [Dbi-interbase-devel] DBD and Interbase functionality

[Chris W. <ch...@cw...>]

On Tue, 2002-04-30 at 11:53, Mark D. Anderson wrote:
> > There seems to be one difference: you cannot get the "current" value
> > from a generator. For instance, from a sequence you can do:
> > 
> >  INSERT INTO foo ( id ) VALUES ( NEXTVAL( seq ) );
> >  SELECT CURRVAL( seq );
> 
> Yes, and no, i think: you can do that also in interbase, but with both
> oracle and interbase, that approach only works if you wrap it all in one transactional
> context, for example in a stored procedure body.

Or I assume in the context of a declared transaction:
BEGIN TRAN;
insert...
select currval...
commit;
 
> To read the current value of an interbase generator
>       SELECT GEN_ID(seq,0) from RDB$DATABASE
> will get you the current value of the generator called "seq" 
> (last i checked interbase does not support a select statement without a "from" clause).

Ah, of course! 

> In my own home-grown SPOPS, i work around the problem of a standards-based insert
> not returning anything, by issuing one sql request to get a unique value of a generator,
> and then just using that value in a later insert (in a separate transaction context).
> generators are guaranteed (like sequences) to never repeat a value.
> this approach has the downside of having two roundtrips, but it has the upside of working on
> every rdbms.

My approach also -- in almost every scenario you need two roundtrips:

 - Identity: Do insert, then retrieve "environment" variable (e.g.,
'@@identity')
 - Sequence A: Retrieve sequence value and use in SQL for insert
 - Sequence B: Do insert with SQL to insert next value automatically,
then retrieve current sequence value
 - Auto-increment: Client library contains 'last incrment created' value
after an insert so you do not need another roundtrip.

> it also has the characteristic of potentially skipping some values in the sequence, but i
> don't care about that.

Me neither, just that they're unique. Relying on other properties of
automatically generated keys is bound to get you into trouble at some
point.

Thanks for the information!

Chris

-- 
Chris Winters (ch...@cw...)
Building enterprise-capable snack solutions since 1988.