query.execBatch() does nothing

[Saviz]

I am working on a project to design an application that connects to a MySQL database and manages patient records for doctors. A critical requirement is to allow doctors to assign multiple treatments to their patients. To achieve this, I designed the following database architecture:

CREATE TABLE IF NOT EXISTS `treatments` (
  `treatment_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'The primary key for the table to uniquely identify each treatment.',
  `name` varchar(100) NOT NULL COMMENT 'The name of the treatment. (100 characters should be more than enough)',
  
  PRIMARY KEY (`treatment_id`)
);


CREATE TABLE IF NOT EXISTS `patients` (
  `patient_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'The primary key for the table to uniquely identify each patient.',
  `first_name` VARCHAR(30) NOT NULL COMMENT 'The first name of the patient.',
  `last_name` VARCHAR(30) NOT NULL COMMENT 'The last name of the patient.',
  
  PRIMARY KEY (`patient_id`)
);


-- A Many-to-Many relationship
CREATE TABLE IF NOT EXISTS `patient_treatments` (
  `patient_id` INT unsigned NOT NULL COMMENT 'A foreign key reference to the "patients" table primary key.',
  `treatment_id` INT unsigned NOT NULL COMMENT 'A foreign key reference to the ''treatments'' table primary key.',
  
  PRIMARY KEY (`patient_id`,`treatment_id`),
  
  KEY `patient_treatments_patient_fk_index` (`patient_id`),
  KEY `patient_treatments_treatment_fk_index` (`treatment_id`),
  
  CONSTRAINT `patient_treatments_patient_fk` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`patient_id`) ON DELETE CASCADE,
  CONSTRAINT `patient_treatments_treatment_fk` FOREIGN KEY (`treatment_id`) REFERENCES `treatments` (`treatment_id`) ON DELETE CASCADE
);

My main challenge is figuring out how to handle updates to the list of treatments. To address this, I retrieve the most up-to-date list from the front-end QML and pass it to my back-end for updating. Below is the implementation of my update function:

// A cople of notes regarding the function:
//
// 1- NOTE: The 'QVariantList' of 'newTreatments' is supplied via QML. Therefore, the function is 'Q_INVOKABLE'.
// 2- NOTE: The connection is already established and drivers also work correctly by the time this function is called.
// 3- NOTE: The current 'patient_id' is cached in my class to be used to find the correct patient. ('patient_id' is a 'quint64' stored in 'm_PatientDataMap'. 'm_PatientDataMap' is a QVariantMap)

bool Database::updateTreatments(const QVariantList &newTreatments)
{
#ifdef QT_DEBUG
    for (const QVariant &item : newTreatments)
    {
        qDebug() << "treatment_id: " << item.toString();
    }
    
    // Output of debug (In this sample the list was of size 2):
    //
    // treatment_id: "27"
    // treatment_id: "13"
#endif



    // DELETE PHASE: I start by deleting all treatments associated with the current 'patient_id'. This makes it easier to insert the new list into the database.
    QSqlQuery queryDelete(m_QSqlDatabase);

    queryDelete.prepare("DELETE FROM patient_treatments WHERE patient_id = :patient_id");
    queryDelete.bindValue(":patient_id", m_PatientDataMap["patient_id"].toULongLong());



    if (!queryDelete.exec())
    {
#ifdef QT_DEBUG
        qDebug() << queryDelete.lastError().text();
#endif

        return (false);
    }



    if (newTreatments.isEmpty())
    {
#ifdef QT_DEBUG
        qDebug() << "Treatment list received is empty! Patient requires no treatments.";
#endif

        return (true); // If the list is empty, then the doctor decided to assign no treatments to the current patient. Technically, the operation is not a failure.
    }



    // INSERT PHASE: I go through the list and bind the 'patient_id' with 'treatment_id' by using a batch.
    QString queryString = "INSERT INTO patient_treatments (patient_id, treatment_id) VALUES (?, ?)";
    


    QSqlQuery queryInsert(m_QSqlDatabase);
    queryInsert.prepare(queryString);



    for (std::size_t index = 0; index < newTreatments.length(); ++index)
    {
        queryInsert.addBindValue(m_PatientDataMap["patient_id"].toULongLong());
        queryInsert.addBindValue(newTreatments[index].toULongLong());
    }



    if (!queryInsert.execBatch())
    {
#ifdef QT_DEBUG
        qDebug() << queryInsert.lastError().text();
#endif

        return (false);
    }



#ifdef QT_DEBUG
    qDebug() << "New treatments were inserted!";
#endif



    return (true);
}

The general idea is to delete all existing entries associated with the patient_id and then insert the new treatments into the table. I have ensured that all arguments and other necessary data are correctly populated and ready when the function is called. When the function executes, it prints "New treatments were inserted!" without any errors from the database or elsewhere. However, when I check the database, there are no treatments associated with the patient. The delete section is certainly working, but the insertion part is not.

Could someone help me understand what I might be doing wrong?

EXTRA NOTES
OS = Windows 10 64-bit
Qt version = 6.7.3
Database = MySQL

[Christian Ehrlicher]

Please follow the documentation on how to use execBatch():

https://doc.qt.io/qt-6/qsqlquery.html#execBatch

You must call addBindValue() with a QVariantList containing all the values as shown there.

[Saviz]

@Christian-Ehrlicher said in query.execBatch() does nothing:

Please follow the documentation on how to use execBatch():

In the documentation, I see a QVariantList containing ints and another containing names. Since my newTreatments list consists of IDs in the form of strings (received from QML), would you recommend converting these IDs to a new list of quint64 types before passing them to the addBindValue() call? Or will Qt do the conversion automatically?

[Christian Ehrlicher]

@Saviz said in query.execBatch() does nothing:

Or will Qt do the conversion automatically?

A QString can be converted to a int and a QVariant might also do this because canConvert() returns true for this case ( https://doc.qt.io/qt-6/qvariant.html#canConvert ) but you should convert them to your desired data type before so the QSql driver knows the correct datatype.

[Saviz]

@Christian-Ehrlicher I agree. I think it is better to be explicit and convert to correct data types so that the driver does not complain. Here is the corrected code and it works:

QVariantList patientIDs;
QVariantList treatmentIDs;

for (const QVariant &item : newTreatments)
{
     patientIDs.append(m_PatientDataMap["patient_id"].toULongLong());
     treatmentIDs.append(item.toULongLong());
}

queryInsert.addBindValue(patientIDs);
queryInsert.addBindValue(treatmentIDs);

Thank you for your help.