PDA

View Full Version : MySQL INSERT query fails in Qt



ochampao
13th June 2014, 13:19
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
13th June 2014, 14:31
Take a look into the server log.

ochampao
13th June 2014, 18:02
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
13th June 2014, 23:56
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
15th June 2014, 03:06
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
22nd June 2014, 20:25
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)