MySQL Stored Procedure, accessing a single result set
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.
I found the answer:
Instead of preparing the query, and then executing it. Just execute the query.
qry->exec("CALL getInventoryAvailableByWarehouse ()"); model->setQuery(*qry);
qry->prepare("CALL getInventoryAvailableByWarehouse ()"); qry->exec(); model->setQuery(*qry);
There’s also no need to allocate the query on the heap as the QSqlQueryModel::setQuery method parameter suggests.