calling stored procedure with prepared query vs execute

dahman

Consider these two functions

void test1()
{
    QSqlQuery query;
    if (!query.exec("CALL test()"))
    {
        qDebug() << query.lastError().text();
        return;
    }
    qDebug() << "query.size(): " << query.size();
    while (query.next())
    {
        qDebug() << query.value(0).toInt();
    }
}

void test2()
{
    QSqlQuery query;
    query.prepare("CALL test()");
    if (!query.exec())
    {
        qDebug() << query.lastError().text();
        return;
    }
    qDebug() << "query.size(): " << query.size();

    while (query.next())
    {
        qDebug() << query.value(0).toInt();
    }
}
Where test is a stored procedure with just a simple SELECT * FROM table_name.
The table contains 3 rows/1 column with 1, 2 and 3 values.

With the first function, I get what I expect:
query.size(): 3
1 2 3

while with the second one just:
query.size(): 0

I am using MariaDB10.3 with Windows 7 64bit.

Can someone give me some hint why this difference?
Thanks

Dahman

Kent-Dorfman

two things:

what is the return value of the prepare() statement? It returns a success code as a bool
prepared statements are highly database and driver dependent. Also, they are generally for statements where you are doing parameter substitution, as arguably there is no benefit in preparing a statement with fixed parameter values. Prepared statements are a good example of an optimization that people use without totally understanding why.

See https://en.wikipedia.org/wiki/Prepared_statement

dahman

prepare return true value.
Yes, I now that but I wrore just a simple example to show the problem.
In my real project, all my stored procedure need parameters.

Thank you

Kent-Dorfman

@dahman said in calling stored procedure with prepared query vs execute:

Yes, I now that but I wrore just a simple example to show the problem.
In my real project, all my stored procedure need parameters.

So the next question to ask is whether the query planner is smart enough to disect your stored procedure during the prepare operation, or does the prepare EXPECT a basic SQL query to plan for. If you are expecting a prepared transaction to analyze the target of the stored procedure then you might expect too much out of the query planner. Does it make sense to prepare() a call SQL statement?

JonB

@dahman
I imagine that QSqlSquery::prepare() is just a thin wrapper over PREPARE statement. https://mariadb.com/kb/en/library/prepare-statement/ indicates that CALL statements are preparable, so I'm not sure why yours does not do what you want. Assuming you have first verified that QSqlSquery::prepare() is working for some other, non-CALL statements and/or with parameters, I can only suggest that you check what is going by using PREPARE & CALL directly inside whatever MariaDB provides for "MySQL Workbench`....

dahman

Well, I tested this

SET @query = CONCAT('CALL test(' , 2, ')');
PREPARE statm FROM @query;
EXECUTE statm;
DEALLOCATE PREPARE statm;

and it works, the result is 2.

Stored procedure test:

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(IN t int(11))
BEGIN

    SET @test_query = CONCAT('SELECT _id FROM _table where _id=' , t);
    PREPARE test_statm FROM @test_query;
    EXECUTE test_statm;
    DEALLOCATE PREPARE test_statm;

END

Kent-Dorfman

What you need to consider is the purpose of the stored procedure and prepared statement, as well as where they live in the RDBMS world. Both stored procedures and prepared statements live on the database server, as an efficiency mechanism. It is expensive to execute a SQL statement over and over again by sending it from the client to the server every time (maybe with different parameters). That is why prepared statements exist. The query planner can figure out a best course of action for the SQL and cache it so that it works with near equal results regardless of the parameter values that might change. Since a stored procedure is simply a server-side wrapper around an SQL transaction, it is IRRELEVANT AND REDUNDANT to wrap it in a prepared statement on the client.

IMHO, your whole use of wrapping a call to a stored procedure in prepare() doesn't make sense.

Kent-Dorfman

This is what you should be doing instead...
https://stackoverflow.com/questions/5895383/mysql-prepare-statement-in-stored-procedures

dahman

@Kent-Dorfman said in calling stored procedure with prepared query vs execute:

Since a stored procedure is simply a server-side wrapper around an SQL transaction, it is IRRELEVANT AND REDUNDANT to wrap it in a prepared statement on the client.

Yes, I start to understand what you mean.
I will study in deep this argument.

Thanks a lot for your precious comments.