How to get a (MaxDB generated) unique key?

[Former Member]

Hi, I have an table with a key type serial. When my application inserts new rows, I need to know, which unique key value was generated for the last row.

Do I have a statement like "SELECT @@IDENTITY" for other RDBMS?

TIA

Christian

[TTK · Accepted solution]

The current value can be accessed with <table_name>.currval.

Example:

create table mytable (id int default serial, t char(10))

insert into mytable set id=10,t='ten'

insert into mytable set t='eleven'

select * from mytable

select mytable.currval from dual

Regards Thomas

[Former Member · Accepted solution]

Hi,

select mytable.currval from dual

What does 'dual' mean?

I get an error "CURRVAL is not yet defined in this session".

Rgds.

[Former Member]

Hi,

two topics:

first one: dual is a table always existing, having exactly (unchangeable) one row and one column. This table exists to have the opportunity to check things like date or time or user or sequence-values as you try to do.

second one: (BTW: please specify the errornumber and not only the text, its easier to handle for the others) You got error -2029.

And as the message-manual tells us:

2029: <sequence_name>.CURRVAL is not yet defined in this session

Explanation:

You attempted to execute <sequence_name>.CURRVAL (Specifying Values (extended_value_spec)) before using <sequence_name>.NEXTVAL in the current database session.

User Response:

Execute <sequence_name>.NEXTVAL first, and then <sequence_name>.CURRVAL.

You have to use the sequence with NEXTVAL BEFORE using the sequence with CURRVAL within the same session.

If you do not use a normal sequence, but the serial-datatype, then you must do an insert into this table before you can do this CURRVAL-check within the same session.

Elke

[Former Member]

Danke, Elke.