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:
Qt Code:
  1. teeth
To copy to clipboard, switch view to plain text mode 

MySQL Table columns:
Qt Code:
  1. [int(11)]idteeth,
  2. [int(11)]patients_idpatient,
  3. [int(11)]location,
  4. [float]screw_start_x,
  5. [float]screw_start_y,
  6. [float]screw_start_z,
  7. [float]screw_end_x,
  8. [float]screw_end_y,
  9. [float]screw_end_z,
  10. [float]center_x,
  11. [float]center_y,
  12. [float]center_z,
  13. [int(11)]screws_idscrews,
  14. [mediumblob]screwPolyTransform,
  15. [int(1)]isEditedByDentist,
  16. [int(1)]isCustomImplant
To copy to clipboard, switch view to plain text mode 

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

Qt Code:
  1. //database driver set to MySQL
  2. QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
  3.  
  4. QSqlQuery query;
  5.  
  6. //SQL Server details.
  7. db.setHostName(_TARGET_SERVER_SQL_HOST_);
  8. db.setPort(QString(_TARGET_SERVER_SQL_PORT_).toInt());
  9. db.setDatabaseName(_TARGET_SERVER_SQL_DB_);
  10. db.setUserName(_TARGET_SERVER_SQL_USER_);
  11. db.setPassword(_TARGET_SERVER_SQL_PASS_);
  12.  
  13.  
  14. if(db.open())
  15. {
  16. //db opened successfully
  17.  
  18. teeth_count = currentPatientRecord->getImplantsCount();
  19.  
  20. //repeat the UPDATE query for all the patient's implants and while there is no error
  21. while(i < teeth_count && isQuerySuccess)
  22. {
  23. current_tooth = currentPatientRecord->getTeethList()->at(i);
  24.  
  25. //update only the records of implants with complete specifications
  26. if(current_tooth->isScrewTypeSet())
  27. {
  28. //if the implant has no record in the database then create one by issuing an INSERT query
  29. //otherwise simply update the existing record
  30. if(!current_tooth->getHasDatabaseRecord())
  31. {
  32. query.prepare("INSERT INTO teeth "
  33. "(patients_idpatient, location, screw_start_x, screw_start_y, screw_start_z, "
  34. "screw_end_x, screw_end_y, screw_end_z, center_x, center_y, center_z, "
  35. "screws_idscrews, screwPolyTransform, isEditedByDentist, isCustomImplant) "
  36. "VALUES (:id_patient, :dentist_location_id, :screwSx, :screwSy, :screwSz, "
  37. ":screwEx, :screwEy, :screwEz, :screwCx, :screwCy, :screwCz, :screw_type_id, "
  38. ":screwPolyTransformStream, :is_edited_by_dentist, :is_custom)");
  39.  
  40. //bind values which are exclusive to the INSERT query
  41. query.bindValue(":dentist_location_id",(int)current_tooth->getLocationID());
  42. }
  43. else
  44. {
  45. query.prepare("UPDATE teeth SET screw_start_x=:screwSx, screw_start_y=:screwSy, screw_start_z=:screwSz, "
  46. "screw_end_x=:screwEx, screw_end_y=:screwEy, screw_end_z=:screwEz, center_x=:screwCx, "
  47. "center_y=:screwCy, center_z=:screwCz, screws_idscrews=:screw_type_id, "
  48. "screwPolyTransform=:screwPolyTransformStream, isEditedByDentist=:is_edited_by_dentist "
  49. "WHERE idteeth=:tooth_uid_db AND patients_idpatient=:id_patient");
  50.  
  51. //bind values which are exclusive to the UPDATE query
  52. query.bindValue(":tooth_uid_db",current_tooth->getTeethID());
  53. }
  54.  
  55. //bind values which are shared between the INSERT and UPDATE query
  56. query.bindValue(":screwSx",(float)current_tooth->getScrewStart_x_mm());
  57. query.bindValue(":screwSy",(float)current_tooth->getScrewStart_y_mm());
  58. query.bindValue(":screwSz",(float)current_tooth->getScrewStart_z_mm());
  59. query.bindValue(":screwEx",(float)current_tooth->getScrewEnd_x_mm());
  60. query.bindValue(":screwEy",(float)current_tooth->getScrewEnd_y_mm());
  61. query.bindValue(":screwEz",(float)current_tooth->getScrewEnd_z_mm());
  62. query.bindValue(":screwCx",(float)current_tooth->getScrewCenter_x_mm());
  63. query.bindValue(":screwCy",(float)current_tooth->getScrewCenter_y_mm());
  64. query.bindValue(":screwCz",(float)current_tooth->getScrewCenter_z_mm());
  65. query.bindValue(":screw_type_id",(int)current_tooth->getScrewTypeID()>0? current_tooth->getScrewTypeID():0);
  66. query.bindValue(":is_edited_by_dentist",(int)current_tooth->isScrewTypeSet());
  67. query.bindValue(":is_custom",(int)current_tooth->getIsCustomImplant());
  68. query.bindValue(":id_patient",(int)currentPatientRecord->getPatientsID());
  69. query.bindValue(":screwPolyTransformStream",qmatrixBA); //qmatrixBA is a QByteArray
  70.  
  71. if(!query.exec())
  72. {
  73. //query unsuccessful
  74. isQuerySuccess = false;
  75. error_message = "Database Text: " + db.lastError().databaseText()
  76. + ",Driver Text: " + db.lastError().driverText();
  77.  
  78. emit error(error_message);
  79. emit finished(isQuerySuccess);
  80. }
  81.  
  82. //----------------------------------------------------------------------------//
  83. //The following code prints the executed query with the variables replaced by actual values
  84. QString str = query.lastQuery();
  85. QMapIterator<QString, QVariant> it(query.boundValues());
  86. while (it.hasNext())
  87. {
  88. it.next();
  89. str.replace(it.key(), it.value().toString());
  90. }
  91. qDebug()<<str;
  92. //----------------------------------------------------------------------------//
  93. }
  94.  
  95. i++;
  96. }
  97.  
  98. //if we reached this point then all the queries have been executed without error
  99. emit finished(isQuerySuccess);
  100.  
  101. db.close();
  102. }
  103. else
  104. {
  105. //db failed to open
  106. isQuerySuccess = false;
  107. error_message = "Database Text: " + db.lastError().databaseText()
  108. + "Driver Text: " + db.lastError().driverText();
  109.  
  110. emit error(error_message);
  111. emit finished(isQuerySuccess);
  112. }
To copy to clipboard, switch view to plain text mode