Retrieving Last Inserted Record ID

[teks]

Hi, all!

Right now I am trying to develop a database front-end to a simple accounts system. Ultimately, there will be 2 versions of the system: a single user, and a multiple-user version. For various reasons, SQLite will be used as the database backend for the single-user version, and PostgreSQL for the multi-user, networked version. In order to implement a consistent user interface for both versions - with minimum of code duplication - I have been trying to program the front end in Revolution, using Rev's Database Library.

I have been able to retrieve, update and delete records so far without any problems, but have come across a pesky little problem while creating a new record. When I create the record, in order to update the interface with the new record ID, I need to know what this ID is. The ID of the record is automatically created by the database - that is, it is an autoincrement integer primary key.

I was wondering if there is a known technique to retrieve the autoincrement id of this last inserted record in a table. I know that different RDBMSs have different internal functions or workarounds that can provide that, but I was wondering whether Revolution had something in its arsenal which was database-independent. Or perhaps one of the Revolution gurus has already worked out a failsafe technique?...

Many thanks in advance for any guidance.

[teks]

A little follow-up on my own question.

After a bit of searching, it seems that there is no built-in way - as yet - to do this in Revolution's Database Library. Ideally, the database should have a function like:

Code: Select all

revLastInsertID(dbConnection[,tablename, idcolumnname])

This would return an integer - the id of the last record successfully created by an INSERT statement into the database, for that connection. Please note, that AFAIK many databases *already* have a connection-safe function for returning this, so it is just a matter of knowing how each database implements the function, and calling it depending on the database engine used.

As you might be able to guess, the hardest thing about writing such a function is to actually do the research to find out how all the different "SELECT" statements you'd have to issue for all the different database systems, to handle this. In order to (hopefully) get the Runtime folks started, here is what I've found in my own wanders through the 'net:

SQLite:

Code: Select all

SELECT last_insert_rowid();

PostgreSQL:

Code: Select all

SELECT currval(pg_get_serial_sequence('my_tbl_name','id_col_name'));

MySQL:

Code: Select all

SELECT last_insert_id();

I hope this may help others - and motivate the Revolution developers to add a much-needed function to the already great arsenal that is the Database Library.