Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

QSqlQuery, stored procedure, lastInsertId



  • I have a stored procedure that inserts a record to the database.

    QSqlQuery query;
    query.prepare("CALL addThreat(?,?,?);");
    query.addBindValue(muintSelectedSDid);
    query.addBindValue(bytaryJSON);
    query.addBindValue(QVariant::String);
    if ( Trainer::queryDB(query, false, &uintNewThreatID) == true ) {
    }
    

    The third parameter is optional and defaults to nullptr if not supplied. Internally the query executes the stored procedure and this works, but also internal to the function queryDB, it calls:

    QVariant varInsertId(rQuery.lastInsertId());
    *puintInsertID = varInsertId.toUInt();
    

    puintInsertID is assigned 0. Is this a limitation, can I get the insert ID from the insert operation in the stored procedure?



  • @SPlatten
    Hope the following helps, not sure but...

    I don't know about rQuery.lastInsertId().

    If you can program the stored procedure there is a SQL variable (depends on which SQL) something like @LAST_INSERT_ID which you can access after the INSERT.

    You will have to play with how to get that back to your code. A while ago i vaguely remember someone here saying they could not get IN/OUT "VAR" parameters to work from the driver or Qt or whatever. If that is the case, I think I ended up telling him he had to do a SELECT @LAST_INSERT_ID in the SQL s.p. as the only way to be able to get it back to Qt SQL.



  • @JonB , thank you, I have since changed the stored procedure to a function that returns the new ID.


Log in to reply