Solution with a database server where multiple users could be connected.
I have some complex writes where the new primary key from the first will be referenced in a bunch more to follow.
I need to be sure of what primary key was just assigned. I know there are easy ways to do this with SQLite, but I need a reliable method that will work on other DBs too.
I would imagine it’s just the highest one in the table, but how would I know if someone else had written one at the same time - between my execute and select?
There must be an easy solution and I’m just to tired to think of it.
And I don’t suppose doing that within a transaction will ensure that the value I got back is associated with my last write and not someone else’s who came moments before…
I almost feel like I need to create a separate table for these values and insert with a unique/random value that I can use to query for the primary key added, but that seems like it shouldn’t be needed. I’m sure I’m overlooking the obvious.
One surefire way is to add a UUID column alongside the primary key and assign it during insert. You can then query for that UUID after the insert to retrieve the inserted record.
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…
This is true of PGSQL too. Plus, it’s transaction safe. The value gets “used” even if the transaction is aborted, so there is no chance of collision. So you call NextVal before your insert, and explicitly assign the returned value in the insert statement.
There is not a nice way to do this with SQLite. It’s possible to find the next value of an auto increment column, but it doesn’t behave in this same transaction safe manner.