SQL Execute - how to identify the record just added

[Tony_Barry]

This may be a simple problem, and with an obvious answer, but at present I cannot see it.

I have an SQLite database as part of a web app. The web app occasionally adds a new record to the web app using SQLExecute.

Given that the web app may have concurrent users, who may concurrently add new records …
how do I reliably identify the record ID that was created from that SQLExecute ?

The SQLExecute prepared statement is

sql = “INSERT INTO Participants (” + _
"EnrolDate, " + _
"Function, " + _
"DataField, " + _
"PhoneHash, " + _
"AUIDhash) " + _
“VALUES (?, ?, ?, ?, ?)” //5 fields

and it does not return a value.

Regards,
Tony Barry
Sydney

[Joost_Rongen]

Have a look at last_insert_rowid() and Core Functions

[Joost_Rongen]

BTW: in PostgreSQL I by default use Returning right in the INSERT INTO statement.

[Tony_Barry]

Thank you Joost ! I shall stare into last_insert_rowid.

It looks good from the SQLite docs - hopefully it is presented by Xojo for general use.

Regards,
Tony Barry
Sydney

[Tim_Hare]

It’s built into the SqliteDatabase class. See LastRowID.

[Tony_Barry]

Thank you Tim. I have found it.

Regards,
Tony Barry
Sydney

[Tony_Barry]

The plot thickens. LastRowID is not guaranteed to provide the last row ID in a threaded environment.

https://sqlite.org/c3ref/last_insert_rowid.html

Now the acid question - is the threaded environment different from the web session environment ? That is, in a web session with a database, is the session a thread, or something else ?

This StackOverflow answer seems to indicate that the session (cursor in the stackOverflow answer) is not a thread.

http://stackoverflow.com/questions/6242756/how-to-retrieve-inserted-id-after-inserting-row-in-sqlite-using-python

Regards,
Tony Barry
Sydney

[Joost_Rongen]

@322882:@Tony Barry

The plot thickens. LastRowID is not guaranteed to provide the last row ID in a threaded environment.

If your application is so busy and critical, I would consider using server based database like PostgreSQL (free). , where you could use the RETURNING keyword to get the last added record-id right away without a timing risk.

INSERT INTO tbl_attachment (title, shortnote, filename, file_extension, file_binary, file_md5, fk_createdby, ts_created, fk_modifiedby, ts_modified, obsolete) VALUES ($1, $2, $3, $4, $5, $6, $7, now(), $7, now(), False) RETURNING ID

[Emile_Schwarz]

What about using your own ID in all code where you use SQLExecute (say 1 for the first SQLExecute line in your code, 2 for the second SQLExecute line in your code, etc.) ?

So, you will always know where the last added Row comes from (you can add a Date Variable too, to get the time of the addition).

[Joost_Rongen]

@Tony Barry - I now see your question was already answered yesterday.

[Tony_Barry]

Hi Joost, Emile,

Thank you for your thoughtful replies. My application is not busy but I do need to be sure of the row ID. I cannot be certain that there will not come a time when two users do things and getting the wrong ID would be a very unwelcome scenario.

The session environment is the test point. If the session database connection (by some means) is not a thread, but some other construct, then last_rowID is safe.

I will make up a test case and try it out.

Regards,
Tony Barry

[Tim_Hare]

@322900:@Tony Barry

My application is not busy but I do need to be sure of the row ID. I cannot be certain that there will not come a time when two users do things and getting the wrong ID would be a very unwelcome scenario.

Xojo’s cooperative threading model makes this safe.

[Norman_P]

Or dont try to figure out what was just added and instead make sure you have a means of generating unique keys
The code to do that I’ve posted several times on the forums

basically you have a table that is JUST a list of table names and the current unique id number
By fetching & incrementing this in a transaction you can be sure that you will not get duplicates (unlike using “max” )

basically you do

1. create a table “UniqueKeys” ( tableName , currentID )
2. for each table that you need to do this you insert a row (and if there is existing data you can insert the name & the max from the table)
3. the you use sql like
   begin transaction
   update uniquekeys set currentID = currentID + 1 where tablename = ? <<<<<< tablename is passed in as a parameter
   select currentID from uniquekeys where tablename = ? <<<<<< tablename is passed in as a parameter
   commit transaction

because this is done in a transaction and the update occurs FIRST you are assured that in ANY ACID compliant db that you will never have 2 threads creating and using the same ID

It works in every DBMS I’ve ever touched that was properly ACID compliant

[Jean-Yves_Pochez]

I do this using unique indexes and catching the error. is it safe ?

[Norman_P]

there are lots of ways to achieve this in different db’s - and many do have a means to allocate a unique id for you to use before hand rather than catching it after (serials in PostgreSQL for instance and you can get ‘nextval’ so its preallocated and wont be duplicated)

unique indexes mean you catch the error after it happens

the code I gave lets you avoid it before it occurs
so instead of asking the DB “what did you just do” you’re saying “heres what you are going to do”

I like to avoid errors beforehand if I can - and this one is possible to avoid

[Hal_Gumbert]

One way is to generate a UUID for the primary key and insert the UUID along with the data. Then you’ll have the key to that record.

[scott_boss]

@323645:@Hal Gumbert

One way is to generate a UUID for the primary key and insert the UUID along with the data. Then you’ll have the key to that record.

I use an integer as PK (Primary Key) as that is what is needed by ActiveRecord (and other tools). Then I have a UUID field that I use to reference the record. I dont rely on the PK for anything. Just the UUID field. I make the UUID field to be unique restraint so I know that I have the one and only record that relates to that record.

So for the OP’s issue, using a UUID field they can do a SELECT for the record based on the UUID and get the PK number.

[Norman_P]

you just described “primary key” in a lot more words & with a lot more work

[scott_boss]

@323661:@Norman Palardy

you just described “primary key” in a lot more words & with a lot more work

something need the PK to be an INTEGER. I dont use INTEGERS as PK. Like I dont use ROWID either. I use a UUID that I can control.

[Norman_P]

PK has no inherent requirement to be an integer - certain databases, like Sqlite, impose that requirement
But they should never be “meaningful” (ie ssn, invoice number, etc)
They should be “the key” - and thats it
UUID is just a lot of bits for “uniquely identifying a row in a table” which IS “primary key” by definition
UUID really shouldn’t be necessary as the PK in a well designed db
An alternate primary key - sure - lots of tables may have several alternates

UUID, unless its a numeric type, requires different processing by the db engine to identify rows when doing queries using the UUID as an indexed value. On big tables that has a measurable cost.

EDIT : this is one analysis of using UUID vs integer primary keys
http://kccoder.com/mysql/uuid-vs-int-insert-performance/

To be clear, I’m not saying UUID’s are not useful. When doing multimaster replication they can be incredibly useful (although I’ve worked on some pretty big db’s that did not use them and we never had issues but …)
I just would not use them as “primary key” - an alternate key for a row - fine
As I said there are often several candidate keys on rows in a db and a UUID could be one of those