Skip to Content
avatar image
Former Member

How to get a (MaxDB generated) unique key?

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Sep 19, 2006 at 09:39 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi,

      select mytable.currval from dual

      What does 'dual' mean?

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

      Rgds.

  • avatar image
    Former Member
    Feb 20, 2008 at 05:00 PM

    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

    Add comment
    10|10000 characters needed characters exceeded