Binding values to stored procedure with MySQL > 5 and QSqlQuery?

Question

I just want to run this type of procedure from Qt side:

DROP PROCEDURE IF EXISTS get_patient_info_brief;
DELIMITER //
CREATE PROCEDURE get_patient_info_brief(IN _id int)
BEGIN
    SELECT age, height, weight, sessions, remaining_sessions, `description` FROM patient_info WHERE `id` = _id;
END //
DELIMITER ;

like this:

const auto q1 = QString("CALL get_patient_info_brief(?);");
const auto q2 = QStringLiteral("SELECT age, height, weight, sessions, remaining_sessions, `description` FROM patient_info WHERE `id` = ?;");

auto db = QSqlDatabase::addDatabase("QMYSQL");
db.setDatabaseName("mydb");
db.setUserName("root");
db.setPassword("pswd");
if (db.open()) {
    QSqlQuery q(db);
    q.prepare(q2);
    q.addBindValue(QVariant(1));
    if (q.exec()) {
        qDebug() << "query executed";
        if (q.size()) {
            while (q.next()) {
                qDebug() << q.record();
            }
        }
    }
}

if I use the q2 the result will be returned back but it's not true with q1

Docs would say:

MySQL 5 introduces stored procedure support at the SQL level, but no API to control IN, OUT, and INOUT parameters. Therefore, parameters have to be set and read using SQL commands instead of QSqlQuery::bindValue().

How can we get manage to do the job with q1 one?

I'm using Qt5.8 + MySQL 8.0.17 + MySQL Connector C 6.1

"Offtopic": Stored procedures and parameterized queries are more or less effectively the same thing, at least as far as the MySQL engine concerned.. Also the stored procedure in this case adds extra unneeded complexity for this simple select .. Mine opinion using a stored procedure is not really justified for this usercase. — Raymond Nijland, Jul 28 '19 at 12:55

score 0 · Answer 1 · edited Jan 23 '20 at 20:06

Using PyQt5 there are two ways to call the stored procedure in PyQt5 and both work. I guess these will also work in Qt even though I haven't tried it.

q.exec("CALL get_patient_info-brief ({})".format(1) or

q.prepare("CALL get_patient_info-brief (:id)")
q.bindValue(":id", 1)
q.exec()

Both work pretty well for me. I understand that when using positional placeholders using named placeholders it's preferable.

Binding values to stored procedure with MySQL > 5 and QSqlQuery?

1 Answers1