Announcement

Collapse
No announcement yet.

alternative to last_insert_id compatible between databses?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • alternative to last_insert_id compatible between databses?

    This is not related directly with SC but....

    In one of our projects we use " select lastid" with MySQL to obtain the id of the inserted record, but this is not compatible with other RDBMS.

    This project should work with other RDBMS and I would like to know a secure way to obtain this ID and compatible with firebird and/or postgres.

    Some idea?
    /Giuseppe

    Professional Scriptcase Services
    Some Customers opinions

  • #2
    You need to create a library procedure with the database as input or something similar as all databases do it differently. I don't think that you can extract the currently used database from a macro.
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #3
      In this case you can create a function with same name for each database, and it must returns the LastID for it dbms.

      Comment


      • #4
        I know a "SELECT MAX(ID) FROM table" works almost on each RDBMS, but don't know If it's secure as last_id is
        /Giuseppe

        Professional Scriptcase Services
        Some Customers opinions

        Comment


        • #5
          At least in Oracle it works differently. Last-insert-id is not very safe imho. But SC is using it too and rr made some comments on that.
          Albert Drent
          aducom software netherlands
          scriptcase partner, reseller, support and (turn-key) development
          www.scriptcase.eu / www.scriptcase.nl

          Comment


          • #6
            Ok, I will wait to rr comments about this
            /Giuseppe

            Professional Scriptcase Services
            Some Customers opinions

            Comment


            • #7
              On Mysql use field auto-increment ID, Oracle test with a timestamp field.

              Comment


              • #8
                Originally posted by Boly64 View Post
                On Mysql use field auto-increment ID, Oracle test with a timestamp field.
                I use autoincrement, but when you create records I need the ID for populate FK fields in related tables.

                And as I mentioned, I search a safe way to get the latest ID, and be crossdatabase, for those installations where our APP is note deployed using MySQL but Firebird for example
                /Giuseppe

                Professional Scriptcase Services
                Some Customers opinions

                Comment


                • #9
                  In oracle it is rowid but it is a thing with some oddities that are not publically known. If you resequence or if you delete rows then the rowid can become and old rowid that once existed. Tho rowid is unique it is not uique over all the historical rowid's.
                  If you want a unique number you are better of using triggers. This generally workd on all databases. But you have to write each trigger yourself for each database.

                  http://www.firebirdsql.org/manual/mi...ata-types.html
                  http://www.postgresql.org/docs/9.1/s...tetrigger.html

                  etc.. most databases do have triggers and with those you can make an independant mechanism specially when you use an extra table with a last-id per per table.
                  e.g. a table with (in oracle terms):
                  tbl varchar(128),
                  lastid number <----start with 1 and just do an update on this value via the trigger mechanism.

                  Using a after or before insert trigger (provided the database supports it) you can make it rather independant.
                  The disadvantage is that once you have saved the record that you need to get the id again using a select and if you put it in your webform field you need to update that field immediately after the insert. I foresee some problems but I think they are solveable.

                  An alternative is:
                  Since you are working in php you could of course also use a combination of md5(uniqid('myapp',true)) or even if you insist md5(uniqid('mytable',true)) and just set counter id to that
                  string. Inserting would be easy then, and updating is never needed.
                  Since it is in php your id would work on all databases.
                  How about that attempt?

                  Comment

                  Working...
                  X