CODEWITHSUNDEEP
mysqlqt
KernelPanic
KernelPanic

Reputation: 2432

SELECT LAST_INSERT_ID() returning zero

I have following Qt/C++ method:

quint64 UeBillModel::ueGetNextPickupIndex()
{
    quint64 pickupIndex=0;

    if(!this->ueDatabase().isOpen())
    {
        this->ueConnectToDatabase();
    }   // if

    this->setQuery(UePosDatabase::UeSqlQueries::UeInsertNewBill::SQL_QUERY_CALC_NEXT_PICKUP_NUMBER,
                   this->ueDatabase());

    if(this->lastError().isValid())
    {
        qDebug() << this->lastError();

        pickupIndex=-1;
    }
    else
    {
        this->setQuery(UePosDatabase::UeSqlQueries::UeInsertNewBill::SQL_QUERY_GET_NEXT_PICKUP_NUMBER,
                       this->ueDatabase());

        if(this->lastError().isValid())
        {
            qDebug() << this->lastError();

            pickupIndex=-1;
        }
        else
        {
            pickupIndex=this->record(0).value(0).toUInt();
        }   // if
    }   // if

    return pickupIndex;
}   // ueGetNextPickupIndex

Once this method is executed, it returns 0. As you can see, it executed two queries:

    static const QString SQL_QUERY_CALC_NEXT_PICKUP_NUMBER="UPDATE "+UePosDatabase::UeTableNames::TABLE_PICKUP_NUMBER+ " SET ID = LAST_INSERT_ID(ID+1)";
    static const QString SQL_QUERY_GET_NEXT_PICKUP_NUMBER="SELECT LAST_INSERT_ID()";

With the first one, I update the filed inside Table PICKUP_NUMBER and with the second I want to get last inserted id. If I execute these two statements inside mysql server directly, I get right value. Why is then method returning 0?

Upvotes: 0

Views: 725

Answers (1)

RobbieE
RobbieE

Reputation: 4360

Firstly, the LAST_INSERT_ID() function in MYSQL has connection scope. This means that, as soon as you close a previous connection, you will lose access to the value. This is why, if you open a brand new connection, there will be no value until you perform an insert.

Secondly, it's not a good idea to store this value or try and calculate anything from it unless you need to use it as a foreign key in another table.

In the time between you insert and the time you use the last ID, there could be another user that has inserted another record into your table, rendering your stored ID redundant, and putting you at risk of a number clash.

Thirdly, the QtSqlQuery class has a function getLastInsertId() for exactly this purpose (as long as the database connection remains open), and can be used for any database that supports this feature, determined by checking return value of QSqlDriver::hasFeature(QSqlDriver::lastInsertId)

Upvotes: 2

Related Questions