I think this is a perfect way of knowing what record is added.
Actually, I ignore (or even exclude) the RowID or any autoincrement functionalities.
I do use a field in both MySQL and SQLite databases with the column-name UUID. I make this my Primary Key.
Like you said, I assign it when creating the record (SQL INSERT). This way LAST_INSERT_ID is never needed. When the INSERT succeeds, I know my Primary Key value, since I generated it.
I simply use a method I called save
Public Function Save( extends db as SqliteDatabase, recordData as Object ) As String
The recordData object is simply a Class with properties. Each property represents a column in the database table. The table-name is the same as the recordData object name, I pass to this save method.
With introspection I iterate through the properties and somehow build my SQL query, based on the public properties inside this class.
Inside this Save function, I look for the UUID property and use a SQL SELECT to see if such a record exists. If so, the Save code will simply update the record. If it doesn’t exist, it will do a SQL INSERT.
When it succeeds, it will return the UUID. If it fails, the Save function will return an empty string.
Works perfectly fine.
I also use Introspection for creating the Database Tables. It saves tons of time to build the queries myself. I simply make a Class with the name of a table I want to create. I give it the properties I need in that table, and run my little introspection function. Done…