MySQL INSERT query fails in Qt

[jefftee]

Hi, QSqlQuery:repare, 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:repare, 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:repare 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);

query.bindValue(":screw_type_id",(int)current_tooth->getScrewTypeID()>0? current_tooth->getScrewTypeID():0);

To copy to clipboard, switch view to plain text mode 

and it is related to the column

[int(11)]screws_idscrews,

[int(11)]screws_idscrews,

To copy to clipboard, switch view to plain text mode 

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));

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

To copy to clipboard, switch view to plain text mode 

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)

QVariant(QVariant::Int)

To copy to clipboard, switch view to plain text mode