MySQL INSERT query fails in Qt

[ochampao]

Hello.

I am writing some code in Qt 4.8 using Qt's MySQL driver for accessing a database for adding or updating some entries on a table. The UPDATE query is working fine but the INSERT query fails without giving any error message (i.e. db.lastError().databaseText() and db.lastError().driverText() are empty strings indicating an undefined error number (-1)).

After spending several hours I am still unable to detect the problem. Rearranging the INSERT query in a single line or multiple lines doesn't seem to have any effect. Below I have included the definition of the table's entries and a part of the code performing the INSERT and UPDATE query. I hope someone can spot the problem. Thanks!

Here is the structure of the table:

MySQL Table Name:
teeth

MySQL Table columns:


[int(11)]idteeth,
[int(11)]patients_idpatient,
[int(11)]location,
[float]screw_start_x,
[float]screw_start_y,
[float]screw_start_z,
[float]screw_end_x,
[float]screw_end_y,
[float]screw_end_z,
[float]center_x,
[float]center_y,
[float]center_z,
[int(11)]screws_idscrews,
[mediumblob]screwPolyTransform,
[int(1)]isEditedByDentist,
[int(1)]isCustomImplant

And here is the part of the code performing the UPDATE and INSERT queries



//database driver set to MySQL
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");

QSqlQuery query;

//SQL Server details.
db.setHostName(_TARGET_SERVER_SQL_HOST_);
db.setPort(QString(_TARGET_SERVER_SQL_PORT_).toInt ());
db.setDatabaseName(_TARGET_SERVER_SQL_DB_);
db.setUserName(_TARGET_SERVER_SQL_USER_);
db.setPassword(_TARGET_SERVER_SQL_PASS_);


if(db.open())
{
//db opened successfully

teeth_count = currentPatientRecord->getImplantsCount();

//repeat the UPDATE query for all the patient's implants and while there is no error
while(i < teeth_count && isQuerySuccess)
{
current_tooth = currentPatientRecord->getTeethList()->at(i);

//update only the records of implants with complete specifications
if(current_tooth->isScrewTypeSet())
{
//if the implant has no record in the database then create one by issuing an INSERT query
//otherwise simply update the existing record
if(!current_tooth->getHasDatabaseRecord())
{
query.prepare("INSERT INTO teeth "
"(patients_idpatient, location, screw_start_x, screw_start_y, screw_start_z, "
"screw_end_x, screw_end_y, screw_end_z, center_x, center_y, center_z, "
"screws_idscrews, screwPolyTransform, isEditedByDentist, isCustomImplant) "
"VALUES (:id_patient, :dentist_location_id, :screwSx, :screwSy, :screwSz, "
":screwEx, :screwEy, :screwEz, :screwCx, :screwCy, :screwCz, :screw_type_id, "
":screwPolyTransformStream, :is_edited_by_dentist, :is_custom)");

//bind values which are exclusive to the INSERT query
query.bindValue(":dentist_location_id",(int)current_tooth->getLocationID());
}
else
{
query.prepare("UPDATE teeth SET screw_start_x=:screwSx, screw_start_y=:screwSy, screw_start_z=:screwSz, "
"screw_end_x=:screwEx, screw_end_y=:screwEy, screw_end_z=:screwEz, center_x=:screwCx, "
"center_y=:screwCy, center_z=:screwCz, screws_idscrews=:screw_type_id, "
"screwPolyTransform=:screwPolyTransformStream, isEditedByDentist=:is_edited_by_dentist "
"WHERE idteeth=:tooth_uid_db AND patients_idpatient=:id_patient");

//bind values which are exclusive to the UPDATE query
query.bindValue(":tooth_uid_db",current_tooth->getTeethID());
}

//bind values which are shared between the INSERT and UPDATE query
query.bindValue(":screwSx",(float)current_tooth->getScrewStart_x_mm());
query.bindValue(":screwSy",(float)current_tooth->getScrewStart_y_mm());
query.bindValue(":screwSz",(float)current_tooth->getScrewStart_z_mm());
query.bindValue(":screwEx",(float)current_tooth->getScrewEnd_x_mm());
query.bindValue(":screwEy",(float)current_tooth->getScrewEnd_y_mm());
query.bindValue(":screwEz",(float)current_tooth->getScrewEnd_z_mm());
query.bindValue(":screwCx",(float)current_tooth->getScrewCenter_x_mm());
query.bindValue(":screwCy",(float)current_tooth->getScrewCenter_y_mm());
query.bindValue(":screwCz",(float)current_tooth->getScrewCenter_z_mm());
query.bindValue(":screw_type_id",(int)current_tooth->getScrewTypeID()>0? current_tooth->getScrewTypeID():0);
query.bindValue(":is_edited_by_dentist",(int)current_tooth->isScrewTypeSet());
query.bindValue(":is_custom",(int)current_tooth->getIsCustomImplant());
query.bindValue(":id_patient",(int)currentPatientRecord->getPatientsID());
query.bindValue(":screwPolyTransformStream",qmatrixBA); //qmatrixBA is a QByteArray

if(!query.exec())
{
//query unsuccessful
isQuerySuccess = false;
error_message = "Database Text: " + db.lastError().databaseText()
+ ",Driver Text: " + db.lastError().driverText();

emit error(error_message);
emit finished(isQuerySuccess);
}

//----------------------------------------------------------------------------//
//The following code prints the executed query with the variables replaced by actual values
QString str = query.lastQuery();
QMapIterator<QString, QVariant> it(query.boundValues());
while (it.hasNext())
{
it.next();
str.replace(it.key(), it.value().toString());
}
qDebug()<<str;
//----------------------------------------------------------------------------//
}

i++;
}

//if we reached this point then all the queries have been executed without error
emit finished(isQuerySuccess);

db.close();
}
else
{
//db failed to open
isQuerySuccess = false;
error_message = "Database Text: " + db.lastError().databaseText()
+ "Driver Text: " + db.lastError().driverText();

emit error(error_message);
emit finished(isQuerySuccess);
}

[Lesiok]

Take a look into the server log.

[ochampao]

Hello. I have extracted a section of SQL's log file showing the statements being executed. You can see 3 UPDATE queries successfully executed and 1 INSERT query failing. Note that I have split the statements in multiple lines for readability and replaced the contents of the blob with 'myBlob data'.




36 Connect myUsername@myDomainName.com on myDatabase
36 Init DB myDatabase
36 Query SET NAMES utf8

36 Prepare UPDATE teeth SET screw_start_x=?, screw_start_y=?,
screw_start_z=?, screw_end_x=?, screw_end_y=?, screw_end_z=?,
center_x=?, center_y=?, center_z=?, screws_idscrews=?,
screwPolyTransform=?, isEditedByDentist=?
WHERE idteeth=? AND patients_idpatient=?
36 Reset stmt
36 Execute UPDATE teeth SET screw_start_x='94.4849',
screw_start_y='63.4829', screw_start_z='27.4593',
screw_end_x='94.4849', screw_end_y='63.4829', screw_end_z='17.4593',
center_x='94.4849', center_y='63.4829', center_z='22.4593',
screws_idscrews=13, screwPolyTransform='myBlob data', isEditedByDentist=1
WHERE idteeth=136 AND patients_idpatient=30
36 Close stmt

36 Prepare UPDATE teeth SET screw_start_x=?, screw_start_y=?,
screw_start_z=?, screw_end_x=?, screw_end_y=?, screw_end_z=?,
center_x=?, center_y=?, center_z=?, screws_idscrews=?,
screwPolyTransform=?, isEditedByDentist=?
WHERE idteeth=? AND patients_idpatient=?
36 Reset stmt
36 Execute UPDATE teeth SET screw_start_x='96.5366',
screw_start_y='69.6727', screw_start_z='26.6544',
screw_end_x='96.5366', screw_end_y='69.6727', screw_end_z='15.6544',
center_x='96.5366', center_y='69.6727', center_z='21.1544',
screws_idscrews=20, screwPolyTransform='myBlob data', isEditedByDentist=1
WHERE idteeth=137 AND patients_idpatient=30
36 Close stmt

36 Prepare UPDATE teeth SET screw_start_x=?, screw_start_y=?,
screw_start_z=?, screw_end_x=?, screw_end_y=?, screw_end_z=?,
center_x=?, center_y=?, center_z=?, screws_idscrews=?,
screwPolyTransform=?, isEditedByDentist=?
WHERE idteeth=? AND patients_idpatient=?
36 Reset stmt
36 Execute UPDATE teeth SET screw_start_x='99.6178',
screw_start_y='74.014', screw_start_z='22.1863',
screw_end_x='100.883', screw_end_y='77.8087', screw_end_z='15.2581',
center_x='100.25', center_y='75.9113', center_z='18.7222',
screws_idscrews=7, screwPolyTransform='myBlob data', isEditedByDentist=1
WHERE idteeth=138 AND patients_idpatient=30
36 Close stmt

36 Prepare INSERT INTO teeth (patients_idpatient, location, screw_start_x,
screw_start_y, screw_start_z, screw_end_x, screw_end_y, screw_end_z,
center_x, center_y, center_z, screws_idscrews, screwPolyTransform,
isEditedByDentist, isCustomImplant)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
36 Reset stmt

36 Quit

[jefftee]

Hi, QSqlQuery::prepare, and QSqlQuery::exec return boolean results indicating success or failure. I see that you are only checking the result of the query.exec() call. Try examining the result of the prepare methods to ensure they are all successful.

Good luck.

Jeff

[ochampao]

Hi, QSqlQuery::prepare, and QSqlQuery::exec return boolean results indicating success or failure. I see that you are only checking the result of the query.exec() call. Try examining the result of the prepare methods to ensure they are all successful.

Good luck.

Jeff

Hello. I have checked the return values of the QSqlQuery::prepare as you recommended. The query is prepared successfully i.e. query.prepare() returns true.

[ochampao]

After days of struggling I ve managed to solve the issue. It was a stupid mistake I had done in the code shown above and unfortunately quite difficult to detect since I was not getting any kind of error message, except from the INSERT query failing. I am posting the solution below:

The problem was caused by line 65 of the code:

query.bindValue(":screw_type_id",(int)current_tooth->getScrewTypeID()>0? current_tooth->getScrewTypeID():0);
and it is related to the column
[int(11)]screws_idscrews, of the SQL table. This column is a foreign key to another table. Therefore, it is automatically constraint not to take any values that will point to non-existent entries in the other table. As a result, the conditional statement above failed when it returned 0, since the foreign key cannot take the value 0 as it was setup. The correct value that the statement should return is NULL. To do this, line 65 was changed to this:

query.bindValue(":screw_type_id",current_tooth->getScrewTypeID()>0? current_tooth->getScrewTypeID():QVariant(QVariant::Int));
This solved the problem.

Simply using NULL as the 2nd argument to QSqlQuery::bindValue() does not work. According to the documentation of QSqlQuery::bindValue()

To bind a NULL value, use a null QVariant.
Here, the column screws_idscrews is of type INT so the following must be used to get a NULL INT:

QVariant(QVariant::Int)