Search code examples
mysqlqtstored-procedures

Calling server stored procedure using binding

I am trying to call a MySQL server stored procedure using Qt. The procedure returns multiple rows of data I want to retrieve.

The first snippet I tried works fine:

QSqlQuery query("CALL GetOrderItems(323)", dataBase);
qDebug() << query.first();

It also returns the desired data and query.first() is true like expected.

Then I tried to insert the parameter using parameter binding like the Qt documentation proposes. I tried the following snippets. The first uses index placeholder the second keyword placeholder.

QSqlQuery query(dataBase);
qDebug() << query.prepare("CALL GetOrderItems(?)");
query.bindValue(0, 323);
qDebug() << query.exec();
qDebug() << query.first();

QSqlQuery query(dataBase);
qDebug() << query.prepare("CALL GetOrderItems(:myparam)");
query.bindValue(":myparam", 323);
qDebug() << query.exec();
qDebug() << query.first();

Both of these queries execute fine. But query.first() returns false so I don't know how the get the results.

Can I get the result from the binding queries in some way? Why doesn't this work?

Solution

  • There are two sentences that can be found in the Qt documentation:

    "MySQL 5 introduces stored procedure support at the SQL level, but no API to control IN, OUT and INOUT parameters." (from here, Thanks Mat)

    "Stored procedures that uses the return statement to return values, or return multiple result sets, are not fully supported." (from here)

    So obviously binding parameters in Qt/MySQL is pretty useless. See also this about batch mode.