MySQL procedure not recognized as SELECT statement
-
wrote on 2 Dec 2024, 11:20 last edited by
I got a simple procedure like that:
CREATE PROCEDURE Test() BEGIN SELECT * FROM myTable; END;
But when i want to call it in the code:
QSqlQuery myQuery; myQuery.exec("CALL Test()");
Then the query is not handled like a SELECT
myQuery.isSelect(); // return false
So every abstract helper around
next()
,first()
,seek()
etc... won't work because they start by looking if the query is a SELECT statement by calling theisSelect()
I know I can use themyQuery.result()->handle()
to then work around theMYSQL_STMT*
, but this should work without that... I think -
wrote on 2 Dec 2024, 15:35 last edited by
I've rewriten a little the QMYSQLDriver to handle that case, what I do is simple:
I've added a private boolean variable to know if a query is a procedure:
class QMYSQLResultPrivate: public QSqlResultPrivate { ... bool isProcedure = false; };
Then in the
prepare(const QString&)
i've added:bool QMYSQLResult::prepare(const QString& query) { Q_D(QMYSQLResult); if (!driver()) return false; if (query.startsWith("CALL", Qt::CaseInsensitive)) d->isProcedure = true; ... }
and then in the
exec()
, I recalculate thebindInValues()
if the!d->meta
and ifd->isProcedure
bool QMYSQLResult::exec() { ... r = mysql_stmt_execute(d->stmt); ... // recalculate the bindInValues after executing the statement if (!d->meta && d->isProcedure) d->bindInValues(); //if there is meta-data there is also data setSelect(d->meta); ... }
Like that I can call my procedure and also benefit from the QSqlResult handling is there is any result
-
I don't see what the Qt mysql driver can do here. It's getting the information if it is a select or not from
int numFields = mysql_field_count(d->drv_d_func()->mysql); setSelect(numFields != 0);
So the driver does not tell us that it is a select.
See https://dev.mysql.com/doc/c-api/8.4/en/mysql-field-count.html
-
wrote on 2 Dec 2024, 15:35 last edited by
I've rewriten a little the QMYSQLDriver to handle that case, what I do is simple:
I've added a private boolean variable to know if a query is a procedure:
class QMYSQLResultPrivate: public QSqlResultPrivate { ... bool isProcedure = false; };
Then in the
prepare(const QString&)
i've added:bool QMYSQLResult::prepare(const QString& query) { Q_D(QMYSQLResult); if (!driver()) return false; if (query.startsWith("CALL", Qt::CaseInsensitive)) d->isProcedure = true; ... }
and then in the
exec()
, I recalculate thebindInValues()
if the!d->meta
and ifd->isProcedure
bool QMYSQLResult::exec() { ... r = mysql_stmt_execute(d->stmt); ... // recalculate the bindInValues after executing the statement if (!d->meta && d->isProcedure) d->bindInValues(); //if there is meta-data there is also data setSelect(d->meta); ... }
Like that I can call my procedure and also benefit from the QSqlResult handling is there is any result
-
wrote on 2 Dec 2024, 15:47 last edited by
For some reason
mysql_stmt_result_metadata
return a valid MYSQL_RES* for a calling procedure after the execution, this is why I set-up all this things to enable that "re-calculation" -
-
-
1/4