Retrieving output of MySQL stored procedures with QSqlQuery

Question

I have a MySQL server up and running. It contains database with procedures. This is one of them:

CREATE PROCEDURE `handshake` ()
BEGIN
    DECLARE `is_new` INT;
    SELECT `value` INTO `is_new` FROM `Defaults` WHERE `key` = 'new_db';
    SELECT (SELECT CASE 
        WHEN `is_new` = 1 THEN 1
        WHEN `is_new` = 0 THEN 0
        WHEN `is_new` = NULL THEN -1
        ELSE -2
    END) AS 'state';
END

It's working via MySQL Workbench and returns one-column (state) table with one row of value 1. But when I'm trying to use it like that:

QSqlQuery test_query(test_db);
if (test_query.prepare("CALL handshake();")) {
    qDebug() << "one";
    if (test_query.exec()) {
        qDebug() << "two";
        if (test_query.first()) {
            qDebug() << "Yay!";
        }
        else {
            qDebug() << test_query.isValid() << test_query.size() << test_query.lastError().text();
        }
    }
}

it returns:

false -1 " "

which means: not valid and no records but no errors... I was trying to google it but with no luck. Does QSqlQuery even support this? I could create OUT parameter and "SELECT CASE into it" but I want to create other procedures with return tables, not only single parameters...

score 0 · Accepted Answer · answered Apr 13 '15 at 03:55

A procedure returns a void. It only has a processing role. You should use an output parameter for your procedure :

create procedure handshake (OUT state INT)
BEGIN
    SELECT `value` INTO `is_new` FROM `Defaults` WHERE `key` = 'new_db';
    set state = (SELECT CASE 
        WHEN `is_new` = 1 THEN 1
        WHEN `is_new` = 0 THEN 0
        WHEN `is_new` = NULL THEN -1
        ELSE -2
    END);
END

And you could call the procedure and read the value like :

QSqlQuery q;
q.exec("call handshake (@outval1)");
q.exec("select @outval1");
q.next();
qDebug() << q.value(0);

Retrieving output of MySQL stored procedures with QSqlQuery

1 Answers1