CODEWITHSUNDEEP
phpmysqlstored-proceduresmysqli
Ed Heal
Ed Heal

Reputation: 60017

PHP stored procedure parameters

I have created a test database as follows:

USE test;
DROP TABLE IF EXISTS test;
DROP PROCEDURE IF EXISTS testparams;
CREATE TABLE test
(
  inparm INT,
  inoutparam INT,
  outparm INT
);

INSERT INTO test VALUES (0, 0, 0);

DELIMITER $$
CREATE PROCEDURE testparams(IN i INT, INOUT io INT, OUT o INT)
BEGIN
   UPDATE test SET inoutparm = io + 1, outparm = FLOOR(RAND() * 1000)
               WHERE inparm = i; 

   SELECT outparm INTO o FROM test WHERE inparm = i;
   SELECT inoutparam INTO io FROM test WHERE inparm = i;

END $$

I have also got a PHP script:

<?php

$c = new mysqli('localhost', 'test', 'test', 'test') or die('Cannot connect');
echo "Connected\n";

$in = 0;
$out = -1;
$inout = 3;

echo "In: $in Out: $out: Inout: $inout\n";

$s = $c->prepare('CALL testparams(?, ?, ?)') or die('Unable to prepare: ' . $c->error);
$s->bind_param('iii', $in, $inout, $out);
#$s->bind_result($out, $inout);
$s->execute();

echo "After execute SP\nIn: $in Out: $out: Inout: $inout\n";

echo "End of SP\n";
$s = $c->prepare('SELECT inparm, inoutparam, outparm FROM test');

# $s->bind_result($in, $inout, $out); - Get a error here

$s->execute();
while ($s->fetch())
{
    echo "In: $in Out: $out: Inout: $inout\n";
}

?>

My problem is that the stored procedure does not appear to be called. Is this due to using OUT and INOUT parameters? If so how do you use these parameters with PHP?

This is demonstrated by the script generating the following output

Connected
In: 0 Out: -1: Inout: 3
End of SP
In: 0 Out: 0: Inout: 0

Upvotes: 0

Views: 8322

Answers (3)

peterm
peterm

Reputation: 92815

CALL Syntax

For programs written in a language that provides a MySQL interface, there is no native method for directly retrieving the results of OUT or INOUT parameters from CALL statements. To get the parameter values, pass user-defined variables to the procedure in the CALL statement and then execute a SELECT statement to produce a result set containing the variable values. To handle an INOUT parameter, execute a statement prior to the CALL that sets the corresponding user variable to the value to be passed to the procedure.

That seems to be changed in MySQL 5.6

In MySQL 5.6, C programs can use the prepared-statement interface to execute CALL statements and access OUT and INOUT parameters. ... Languages that provide a MySQL interface can use prepared CALL statements to directly retrieve OUT and INOUT procedure parameters.

That will do it

<?php

$c = new mysqli('localhost', 'test', 'test', 'test') or die('Cannot connect');
echo "<pre>Connected<br>";

$in = 0;
$out = -1;
$inout = 3;

echo "Before executing SP<br>In: $in Inout: $inout Out: $out<br>";

$s = $c->prepare('SET @i = ?, @io = ?') or die('Unable to prepare: ' . $c->error);
$s->bind_param('ii', $in, $inout);
$s->execute();

$s = $c->prepare("CALL testparams(@i, @io, @o)") or die('Unable to prepare: ' . $c->error);
$s->execute();

$s = $c->prepare('SELECT @io, @o');
$s->execute();
$s->bind_result($inout, $out);
$s->fetch();

echo "After execute SP<br>In: $in Inout: $inout Out: $out<br>";

echo "End of SP<br></pre>";

?>

Output

Connected
Before executing SP
In: 0 Inout: 3 Out: -1
After execute SP
In: 0 Inout: 4 Out: 851
End of SP

BTW, there is a typo in SP. This line

UPDATE test SET inoutparm = io + 1, outparm = FLOOR(RAND() * 1000)

should be

UPDATE test SET inoutparam = io + 1, outparm = FLOOR(RAND() * 1000)
                        ^

Upvotes: 1

Amir
Amir

Reputation: 4111

I think $s->bind_param('sss', $in, $inout, $out); should be

$s->bind_param('iii', $in, $inout, $out);

Upvotes: 1

ckim
ckim

Reputation: 1004

The 'sss' should be 'iii' since you're dealing with integers, not strings.

Upvotes: 0

Related Questions