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?
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)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments