Results 1 to 6 of 6

Thread: MySQL INSERT query fails in Qt

  1. 13th June 2014, 12:19 #1
    ochampao
    ochampao is offline Beginner
    Join Date
    Jun 2014
    Posts
    4
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows

    Default MySQL INSERT query fails in Qt

    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 
    Reply With Quote Reply With Quote
  2. 13th June 2014, 13:31 #2
    Lesiok
    Lesiok is offline Expert
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,536
    Thanked 284 Times in 279 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: MySQL INSERT query fails in Qt

    Take a look into the server log.
    Reply With Quote Reply With Quote
  3. 13th June 2014, 17:02 #3
    ochampao
    ochampao is offline Beginner
    Join Date
    Jun 2014
    Posts
    4
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows

    Default Re: MySQL INSERT query fails in Qt

    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'.

    Qt Code: 
    1. 36 Connect	myUsername@myDomainName.com on myDatabase
    2. 36 Init DB	myDatabase
    3. 36 Query	SET NAMES utf8
    4.  
    5. 36 Prepare	UPDATE teeth SET screw_start_x=?, screw_start_y=?, 
    6.    screw_start_z=?, screw_end_x=?, screw_end_y=?, screw_end_z=?, 
    7.    center_x=?, center_y=?, center_z=?, screws_idscrews=?, 
    8.    screwPolyTransform=?, isEditedByDentist=? 
    9.    WHERE idteeth=? AND patients_idpatient=?
    10. 36 Reset stmt	
    11. 36 Execute	UPDATE teeth SET screw_start_x='94.4849', 
    12.    screw_start_y='63.4829', screw_start_z='27.4593', 
    13.    screw_end_x='94.4849', screw_end_y='63.4829', screw_end_z='17.4593', 
    14.    center_x='94.4849', center_y='63.4829', center_z='22.4593', 
    15.    screws_idscrews=13, screwPolyTransform='myBlob data', isEditedByDentist=1 
    16.    WHERE idteeth=136 AND patients_idpatient=30
    17. 36 Close stmt	
    18.  
    19. 36 Prepare	UPDATE teeth SET screw_start_x=?, screw_start_y=?, 
    20.    screw_start_z=?, screw_end_x=?, screw_end_y=?, screw_end_z=?, 
    21.    center_x=?, center_y=?, center_z=?, screws_idscrews=?, 
    22.    screwPolyTransform=?, isEditedByDentist=? 
    23.    WHERE idteeth=? AND patients_idpatient=?
    24. 36 Reset stmt	
    25. 36 Execute	UPDATE teeth SET screw_start_x='96.5366', 
    26.    screw_start_y='69.6727', screw_start_z='26.6544', 
    27.    screw_end_x='96.5366', screw_end_y='69.6727', screw_end_z='15.6544', 
    28.    center_x='96.5366', center_y='69.6727', center_z='21.1544', 
    29.    screws_idscrews=20, screwPolyTransform='myBlob data', isEditedByDentist=1 
    30.    WHERE idteeth=137 AND patients_idpatient=30
    31. 36 Close stmt	
    32.  
    33. 36 Prepare	UPDATE teeth SET screw_start_x=?, screw_start_y=?, 
    34.    screw_start_z=?, screw_end_x=?, screw_end_y=?, screw_end_z=?, 
    35.    center_x=?, center_y=?, center_z=?, screws_idscrews=?, 
    36.    screwPolyTransform=?, isEditedByDentist=? 
    37.    WHERE idteeth=? AND patients_idpatient=?
    38. 36 Reset stmt	
    39. 36 Execute	UPDATE teeth SET screw_start_x='99.6178', 
    40.    screw_start_y='74.014', screw_start_z='22.1863', 
    41.    screw_end_x='100.883', screw_end_y='77.8087', screw_end_z='15.2581', 
    42.    center_x='100.25', center_y='75.9113', center_z='18.7222', 
    43.    screws_idscrews=7, screwPolyTransform='myBlob data', isEditedByDentist=1 
    44.    WHERE idteeth=138 AND patients_idpatient=30
    45. 36 Close stmt	
    46.  
    47. 36 Prepare	INSERT INTO teeth (patients_idpatient, location, screw_start_x, 
    48.    screw_start_y, screw_start_z, screw_end_x, screw_end_y, screw_end_z, 
    49.    center_x, center_y, center_z, screws_idscrews, screwPolyTransform, 
    50.    isEditedByDentist, isCustomImplant) 
    51.    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    52. 36 Reset stmt
    53.  
    54. 36 Quit
    To copy to clipboard, switch view to plain text mode 
    Reply With Quote Reply With Quote
  4. 13th June 2014, 22:56 #4
    jefftee
    jefftee is offline Advanced user
    Join Date
    Dec 2009
    Location
    New Orleans, Louisiana
    Posts
    791
    Thanks
    13
    Thanked 153 Times in 150 Posts
    Qt products
    Qt5
    Platforms
    MacOS X

    Default Re: MySQL INSERT query fails in Qt

    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
    Reply With Quote Reply With Quote
  5. 15th June 2014, 02:06 #5
    ochampao
    ochampao is offline Beginner
    Join Date
    Jun 2014
    Posts
    4
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows

    Default Re: MySQL INSERT query fails in Qt

    Quote Originally Posted by jthomps View Post
    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.
    Reply With Quote Reply With Quote
  6. 22nd June 2014, 19:25 #6
    ochampao
    ochampao is offline Beginner
    Join Date
    Jun 2014
    Posts
    4
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows

    Default Re: MySQL INSERT query fails in Qt

    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:
    Qt Code: 
    1. 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
    Qt Code: 
    1. [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:
    Qt Code: 
    1. 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:
    Qt Code:
    To copy to clipboard, switch view to plain text mode 
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Similar Threads

  1. How to know not insert or delete query?
    By ramazangirgin in forum Qt Programming
    Replies: 2
    Last Post: 29th June 2010, 13:43
  2. Select query using mysql for searching
    By sinha.ashish in forum Qt Programming
    Replies: 4
    Last Post: 10th April 2008, 06:14
  3. INSERT query with MySQL problem
    By timmyg in forum Qt Programming
    Replies: 10
    Last Post: 20th March 2008, 21:52
  4. Mysql query question
    By twells55555 in forum Qt Programming
    Replies: 1
    Last Post: 29th June 2007, 23:41
  5. MYSQL insert
    By allensr in forum Newbie
    Replies: 4
    Last Post: 14th August 2006, 16:55

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules

Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.