Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2010
    Location
    Edmonton -> Raleigh
    Posts
    3

    Unanswered: Last Insert ID ( RowID )

    Although I have been around SQL databases for quite a few years, I am very new to Informix, and have encountered an issue in a project that I have inherited.

    I need to retrieve the value of the RowID of the last record inserted so that I can ( informally ) link that table to another. ( Not an explicit constraint in the database, but the business logic would like to know. )


    Any suggestions?


    Thanks,
    Brian

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    one foolproof and surefire way is to query back the row using the value of some other candidate key

    one of the benefits of this method is that you do not need to use transaction locking

    if your table has no candidate keys other than the surrogate autonumber/serial column, then you're in bigger trouble than you think
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2010
    Location
    Edmonton -> Raleigh
    Posts
    3

    Bailing

    Thanks for the reply r937 ( Rudy? ).

    Unfortunately, while I agree with your thought, and would use it under "normal" circumstances, I am working in the middle of "genericizing" code, wrapper code, and wanted to find an generic solution that would be able to give me that value as a side effect of the Insert.

    I guess what I could do would be to double up all of the "save()"s ( which could be potentially either Inserts or Updates ) and follow them with Selects, if that is the Informix way.


    Brian

  4. #4
    Join Date
    Sep 2010
    Location
    Germany, Brunswick
    Posts
    55
    I don't understand you completely, but you should never, never, NEVER work with rowids!

    Rowids are unique in table but not consistent, they can change!

    You can use a serial column, get the highest number with a select ( select max(serial_col) from tab; ) and you have your last inserted row.

    You can also use a trigger which inserts the row in another table.

    And never work with rowids
    WilhelmW

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by InformixWilli View Post
    select max(serial_col) from tab;
    bzzzzt, wrong

    that only works in a single-user environment
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Sep 2010
    Location
    Germany, Brunswick
    Posts
    55
    You want your own last inserted row and not the last one in general?

    It could be possible with SQL Trace.
    WilhelmW

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by InformixWilli View Post
    You want your own last inserted row and not the last one in general?
    no, i'm talking about a race condition

    user one adds a row, it gets assigned id 52
    user two adds a row, it gets assigned id 53
    user one does his SELECT MAX, and gets 53 (wrong!!)
    user two does his SELECT MAX, and gets 53 (right!! a coincidence!!)

    see tocttou

    there are two options: use transaction locking, or find a different method

    hint: requerying with a candidate key is lock-free and guaranteed safe against race conditions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Oct 2010
    Location
    Edmonton -> Raleigh
    Posts
    3

    A solution

    I wound up with a version of R937's solution, because there are only a few places where the "Last Inserted ID" is needed for other uses.

    As it happens, because not all of the tables in this system have Serial fields for their IDs, the Insert wrapper code does a "Lock Table Exclusive" followed by "select NVL( id, 0 ) from table" before any Insert that it is doing, so that solution is similar to another one in this thread.

    Thank you all for the suggestions.

    Brian

  9. #9
    Join Date
    Mar 2011
    Location
    Smederevo, Srbija (Serbia)
    Posts
    1
    Try this:

    RowID = SQLCA.SQLERRD[2]

    where RowID is integer type.

    Best regards.

Tags for this Thread

Posting Permissions

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