MySQL Stored Procedure, accessing a single result set

DaveK 0

Hi,

I am trying to run a stored procedure on a MySQL 5.6 database that returns a result set. Is it possible to access this result set in a QT Application?

I see that the QSqlQuery class does not support MULTUPLE result sets. Does that mean that it does support calling a stored procedure that returns only a SINGLE result set? How would you access that result set since it is not the same as an "Output Variable"?

Here is an example of a stored procedure I would like to access the result of in QT:

USE `shipping`;
DROP procedure IF EXISTS `getInventoryAvailableByWarehouse`;

DELIMITER $$
USE `shipping`$$
CREATE PROCEDURE `getInventoryAvailableByWarehouse` ()
BEGIN

    ## This is just an example to return a result set, the real stored procedure would have logic here.
    SELECT * FROM inventory;

END$$

DELIMITER ;

What I would like to do in my Qt Application:

    QSqlQuery* qry = new QSqlQuery(m_db);
    QSqlQueryModel *model = new QSqlQueryModel;

    qry->prepare("CALL getInventoryAvailableByWarehouse ()");
    qry->exec();
    model->setQuery(*qry);

    ui->tableView->setModel(model);

What I do not understand is how to get the result set from the stored procedure, if it is possible.

DaveK 0

I found the answer:

Instead of preparing the query, and then executing it. Just execute the query.

THIS:

    qry->exec("CALL getInventoryAvailableByWarehouse ()");
    model->setQuery(*qry);

NOT THIS:

    qry->prepare("CALL getInventoryAvailableByWarehouse ()");
    qry->exec();
    model->setQuery(*qry);

SGaist

Hi,

There’s also no need to allocate the query on the heap as the QSqlQueryModel::setQuery method parameter suggests.