2

Before this is closed as a duplicate out of hand, let me say that there are a few questions that ask this on Stack Overflow, but none of them have marked answers, and the unmarked ones didn't work for me.

In my MySql database, I have a simple procedure:

DELIMITER //
CREATE PROCEDURE foo (
  IN a INT,
  OUT b INT
) BEGIN
  SELECT a INTO b;
END //
DELIMITER ;

If I call this in phpMyAdmin, I get what I expect:

SELECT 2 INTO @b;
CALL foo(1, @b);
SELECT @b;

| @b |
+----+
|  1 |

When I try to call this from my php code, however, stmt->execute() returns false.

$a = 1;
$pdoLink = get_pdo_connection($db);

$stmt = $pdoLink->prepare('CALL `mydb`.`foo`(:a, :b)');

$stmt->bindParam(':a', $a);
$stmt->bindParam(':b', $b, PDO::PARAM_INT, 4);
// I've also tried length values of 1, 2, and 16

$stmt->execute(); // returns false

I thought maybe it had to do with calling a procedure in the first place, so I replaced my procedure with:

DELIMITER //
CREATE PROCEDURE foo (
  IN a INT,
  IN b INT
) BEGIN
  SELECT a, b;
END //
DELIMITER ;

This, however, does work from php, so it must be something having to do with output parameters. I'm using code that's only slightly altered from the PDO Documentation, so I really have no idea what's wrong with it.

I am not looking for an answer that uses mysqli's multi_query to do something like

$sql = "CALL `mydb`.`foo`(1, @b);
        SELECT @b;";
$link->multi_query($sql);
// ...

I'm looking for how to do this using PDO and a single query.

Someone tried to mark this as a duplicate of Calling stored procedure with Out parameter using PDO. The marked answer to this question uses multiple queries.

dx_over_dt
  • 13,240
  • 17
  • 54
  • 102
  • Possible duplicate of [Calling stored procedure with Out parameter using PDO](https://stackoverflow.com/questions/13382922/calling-stored-procedure-with-out-parameter-using-pdo) – Pinke Helga Feb 04 '19 at 03:01
  • See, that very much looks like how to do it with multiple queries--which I am not looking for. – dx_over_dt Feb 04 '19 at 03:02
  • 1
    I'm afraid there is no better solution with MySql drivers. I would be glad to see the opposit. – Pinke Helga Feb 04 '19 at 03:11
  • Oh, how I long to be working with something other than PHP/MySql. :( – dx_over_dt Feb 04 '19 at 03:12
  • In the software development you've got the choice between pest or cholera. Each system has it's own childhood diseases. Ok, PHP has all of them. xD – Pinke Helga Feb 04 '19 at 03:16
  • Maybe I'm biased, but node.js/SQL Server Express seems to just *work* the way I want it to. Sure there's room for improvement, but at least there's active development going on. – dx_over_dt Feb 04 '19 at 03:18
  • This was a solution for me: `$stmt->bindParam(':b', $b, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT, PDO::SQLSRV_PARAM_OUT_DEFAULT_SIZE);`. – Zhorov Feb 04 '19 at 06:56

1 Answers1

0

You say you used phpMyAdmin to test if your query works, but phpMyAdmin is built in PHP and uses mySQL Improved, so the problem is not on PHP. Or better, to talk with MySQL you have to go through a driver that interprets the data between PHP and a system library; usually the libmysql, this interfaces with the MySQL server. It's all too complicated! Many information on the web, say to set the PDO :: ATTR_EMULATE_PREPARES to false, to prevent the PHP emulates the preparation of the parameters passed to the query. Other information, always taken from the web, they say to do it only if the PHP is compiled through the php5-mysqlnd module. In practice I noticed that the best way for me was to cast the variables that carry non-string values. So your code could become

... $stmt->bindParam(':a', (int)$a); $stmt->bindParam(':b', (int)$b, PDO::PARAM_INT, 4); ...

My two cents

Marco Grazia
  • 1
  • 1