Qt's odbc driver does not support LastInsertId

Question

Im using Qt 4.8.3 and the MySQL ODBC 3.51 Driver. The driver does not seem to support LastInsertId which I test like this:

db.driver()->hasFeature(QSqlDriver::LastInsertId)

I need it to find the auto increment id I just inserted

query.exec("INSERT INTO myTable (MyVal, Created) VALUES ('1', NOW())");
auto insertId = query.lastInsertId().toString();

What is the best workaround?

EDIT: there will be concurrent access to this table from multiple clients.

Answer 1

According to MySQL documentation, you can call:

SELECT LAST_INSERT_ID();

after the insert query to get the latest id inserted for the current connection, even when using ODBC.

Answer 2

Use OUTPUT INSERTED.[Field Name] as in code below. Read the returned ID with next() function.

bool RepeatsRecognizer::InsertEvent(RepEvent* event){
QSqlDatabase db = AppManager::DB_Output()->ThreadDatabase();
QSqlQuery query(db);
query.prepare("INSERT INTO RepEvents (ChannelID, StatsRangeUID, MinStartDateTime, MaxDuration, Fixed) OUTPUT INSERTED.ID"
              " VALUES (?, ?, ?, ?, ?)");
query.bindValue(0, event->ChannelID);
query.bindValue(1, event->StatsRangeUID.toString());
query.bindValue(2, event->MinStartDateTime);
query.bindValue(3, event->MaxDuration);
query.bindValue(4, false);


if (!query.exec())
    return false;

if (!query.next())
    return false;

event->ID = query.value(0).toLongLong();
qDebug() << __FUNCTION__ << "Event ID:" << event->ID;

}

Answer 3

You could do something like:

query.exec("SELECT id FROM myTable ORDER BY id DESC LIMIT 1");

to get the largest id in your table - this should be the one you just inserted.

If you are writing an application that can have multiple concurrent users you'll have to look into creating a lock on your table and then releasing the lock after you run this second query.