Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Posts
    98

    Unanswered: datatype serial problem

    is there a way to extract the current serial value?
    for example

    create table temp
    ( idx serial primary key
    idx_int integer default serial.current);

    is there such thing like serial.current in informix?

  2. #2
    Join Date
    Nov 2002
    Posts
    10

    Re: datatype serial problem

    As I knew that there is no such thing for the serial.current.
    There is three ways I have been using.
    1. Stupid way, find the max(idx) from the table to get the next serial number because serial value is not an updated value.
    2. from the SQLCA.SQLERRD[2], you can find the next serial number
    3. use the SQL command to alter the starting value:
    alter table temp modify idx serial (100);

    Good Luck


    Originally posted by mchih
    is there a way to extract the current serial value?
    for example

    create table temp
    ( idx serial primary key
    idx_int integer default serial.current);

    is there such thing like serial.current in informix?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    just curious, mchih, but why do you want to know what the next number might be?

    rudy

  4. #4
    Join Date
    Nov 2002
    Posts
    10
    Sometime is required for example, data migration is one good example. serial number mainly used for the unique index, conflict of the primary key may occur if you don't know what is the last one.

    Originally posted by r937
    just curious, mchih, but why do you want to know what the next number might be?

    rudy

  5. #5
    Join Date
    Nov 2002
    Posts
    98
    The reason i want to extract the current serial value is that I have a trigger set up to use the current index value and store it to another table, which also has the serial datatype as primary key, as foreign key. I am researching a way to quickly find out the current serial value of the both tables, so I can find the latest inserted records and update them right away.

    it seems max(idx)+1 would be the easiest way at the moment

    Mark

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    easiest, but wrong

    you will surely assign the wrong number one day

    there is another way, and that is to query back the row inserted using the values of the "real" primary key (the serial is a surrogate)

    see Retrieving last row inserted

    rudy

Posting Permissions

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