Retrieving and using last inserted row ID in sqlite

193 Views Asked by At

I am aware how to retrieve the last inserted row id in sqlite (by LAST_INSERT_ROWID()).

Assume there is a business logic class Customer with a string name and an int id field.

Consider this piece of pseudo code/MWE:

Customer newCust = Customer();
newCust.setName("Mr. Happy");
createNewCustomer(newCust);
print(newCust.id); // would print "3"

void createNewCustomer(Customer& cust) {
    insertNewCustomer(cust.name); // [pseudo code] inserts a new row in DB; id automatically assigned
    int id = sqlite3::LAST_INSERT_ROWID(); // [pseudo code] retrieve last inserted row id
    cust.id = id;
    return;
}

The Customer object is passed by reference, so that its id field can be updated after the query and used in the rest of the program.

Should I wrap the two steps (inserting and retrieving the last id) in a transaction in order to guarantee the last retrieved id is really the one from the query before (think about a scenario with multiple connections accessing in WAL mode for instance).

Is this even a sophisticated approach?

0

There are 0 best solutions below