Member Avatar for Tinnin

Hi all,

I'm trying to run a stored procedure:

BEGIN

SELECT YFR_Master_Fundraisers.FRName, YFR_Master_Fundraisers.FRActive
FROM YFR_Master
INNER JOIN YFR_Master_Fundraisers
ON YFR_Master.FundRaiser = YFR_Master_Fundraisers.FRNameCode
GROUP BY YFR_Master.FundRaiser
HAVING MAX(YFR_Master.CollectionDate) < DATE_SUB(CURDATE(),INTERVAL 2 MONTH)
AND YFR_Master_Fundraisers.FRActive = 1;

END

But I'm getting the following error:

Procedure execution failed
1312 - PROCEDURE db355379982.Deactivate can't return a result set in the given context

Does anyone know why I might be getting this?

I've never used stored procedures before.

Thanks

  • 2 Contributors
  • 11 Replies
  • 201 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by Tinnin

Dani AI

Generated

Most likely cause: the procedure is emitting row results but the call site (tool/driver) isn’t prepared to receive them. MySQL allows SELECTs inside procedures, but if a procedure would stream result sets into a caller that forbids or can’t handle them (examples: functions/triggers, or client libraries that don’t request multi-result support) the server blocks the result and returns the error condition described in the thread. (dev.mysql.com)

The fact that ’s routine runs from PHP but fails in Navicat strongly points to a client/driver issue rather than a syntax bug. Client libraries must declare support for multiple-result processing (the C API uses CLIENT_MULTI_RESULTS / CLIENT_MULTI_STATEMENTS); if that’s not enabled the server will refuse to return procedure result sets. Reproduce the call from the mysql CLI or MySQL Workbench — success there but failure in Navicat means Navicat (or its connector) is the culprit, so updating the tool or contacting Navicat (as suggested) is reasonable. (dev.mysql.com)

Two practical approaches:

  • Change the procedure so it doesn’t stream result sets to the caller (use OUT parameters or fill a TEMPORARY table and SELECT from it after the CALL). The manual documents SELECT … INTO / OUT parameter patterns for this. (dev.mysql.com)
  • Make the client consume multiple results. Example PHP patterns:
// mysqli: consume all result sets returned by a CALL
$mysqli->multi_query("CALL my_proc()");
do {
  if ($res = $mysqli->store_result()) {
    while ($r = $res->fetch_assoc()) { /* process row */ }
    $res->free();
  }
} while ($mysqli->more_results() && $mysqli->next_result());
// PDO: advance through rowsets
$stmt = $pdo->query("CALL my_proc()");
do {
  $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
  /* process $rows */
} while ($stmt->nextRowset());

See the PHP docs for mysqli::multi_query and PDOStatement::nextRowset for details. (php.net)

Quick checklist: confirm the CALL in mysql CLI/Workbench; if CLI works, test a newer Navicat or another admin client; if the CALL must be invoked from a restricted context, refactor to OUT parameters or temp tables.

Recommended Answers

All 11 Replies

Member Avatar for pritaeas

Do you get this when you run the stored proc in phpMyAdmin (or another client), or when you run it from PHP? Based on this thread on SO.

Edited by pritaeas
Member Avatar for Tinnin

It runs fine from php. I'm using Navicat for MySQL. Could that be the problem?

MySQL Version 5.1.67-log
PHP Version 5.2.17

Member Avatar for pritaeas

It runs fine from php.

So where do you get the error?

Member Avatar for Tinnin

In Navicat

Member Avatar for Tinnin

The SP is called Deactivate

I run

CALL Deactivate

Member Avatar for pritaeas

Hard to say if it works from PHP. Are you using the same credentials?

Member Avatar for Tinnin

Sorry, what do you mean by "credentials"?

Member Avatar for pritaeas

Same user as in PHP. If you are using a different one, with different priviliges, that could cause issues.

Member Avatar for Tinnin

Oh I see. Yes, Same user.

Member Avatar for pritaeas

Strange, I'd contact Navicat support.

Member Avatar for Tinnin

Yeah. It might be time to. Thanks for the help.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.