alternative to last_insert_id compatible between databses?

Scriptcase 9 General
#1

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?

#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.

#3

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

#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

#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.

#6

Ok, I will wait to rr comments about this :wink:

#7

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

#8

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

#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/migration-mssql-data-types.html
http://www.postgresql.org/docs/9.1/static/sql-createtrigger.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?