Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Posts
    10

    Unanswered: how to read primary key

    Hi,
    i want to write a stored procedure that inserts into a table
    which has primary key of type SERIAL8, and then retrieves
    this new key (how?) and returns it as returned value.
    I cannot use SELECT...WHERE statement since some of the fields
    are TEXT etc. Is it possible to get the newly inserted key from DBINFO?
    Can I use ROWID perhaps to get primary key?

    thnx,
    bran

  2. #2
    Join Date
    Aug 2002
    Location
    Bonn/Germany
    Posts
    152
    You have to use the dbinfo() function for
    this.

    create procedure p1() returning int;

    define my_serial int;

    insert into t_serial values(0);
    let my_serial = dbinfo("sqlca.sqlerrd1");

    return my_serial;

    end procedure


    HTH.

    Best regards

    Eric
    --
    IT-Consulting Herber
    WWW: http://www.herber-consulting.de
    Email: eric@herber-consulting.de

    ***********************************************
    Download the IFMX Database-Monitor for free at:
    http://www.herber-consulting.de/BusyBee
    ***********************************************

  3. #3
    Join Date
    Jun 2002
    Posts
    10
    I tried this but unfortunatelly on my system (Informix DS 9.3)
    DBINFO( "sqlca.sqlerrd1" ) returns 0 always, i.e. stored procedure
    inserts correct SERIAL8 primary key but DBINFO always returns 0?!

    I traced this with following statements to confirm:

    SET DEBUG FILE TO '/tmp/trace';
    TRACE OFF;
    TRACE 'primary key = ' || DBINFO("sqlca.sqlerrd1");


    I dont know what could be wrong??

    thanks,,

  4. #4
    Join Date
    Aug 2002
    Location
    Bonn/Germany
    Posts
    152
    Hi,

    this was my fault.

    You need to used dbinfo('serial8') instead of
    dbinfo('sqlca.sqlerrd1').

    I didn't recognize that it was a serial8, which has a larger
    range of values than a normal serial.

    Sorry.

    Best regards

    Eric
    --
    IT-Consulting Herber
    WWW: http://www.herber-consulting.de
    Email: eric@herber-consulting.de

    ***********************************************
    Download the IFMX Database-Monitor for free at:
    http://www.herber-consulting.de/BusyBee
    ***********************************************

    Best regards

    Eric
    --
    IT-Consulting Herber
    WWW: http://www.herber-consulting.de
    Email: eric@herber-consulting.de

    ***********************************************
    Download the IFMX Database-Monitor for free at:
    http://www.herber-consulting.de/BusyBee
    ***********************************************

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •