Re: [Firebird-devel] Simplify MySQL migration: LAST_INSERT_ID()

[Norman D. <No...@du...>]

Hi Sean,

>> I'd much prefer something like LAST_GEN_ID(<name>). Oracle has
>> <name>.curval, so it's like something a database-portable application would
>> be capable to use.
> 
> Does this represent the next value, the last value for any transaction or the last value for the current transaction?

Assuming you are asking about Oracle.....

You can select <sequence_name>.curval ONLY whan that sequence has been
used to generate a new value within the current transaction using nextval.

So, this fails:

<start a new transaction>
...
Select sequence_name.curval [into some_variable] from dual;
...
commit;

Whereas this works:

<start a new transaction>
...
Select sequence_name.nextval [into some_variable] from dual;
Select sequence_name.curval [into another_variable] from dual;
...
commit;

It doesn't have to be a 'select', it can be an 'insert' or 'insert ...
returning' etc. But once you have the "new" value using nextval, you can
call curval as often as you like within this transaction.


Cheers,
Norman.

-- 
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767