mysql procedure call with out parameter not working in PHP

[network18]

Hi,
I have created a test procedure (for the sake of example to get this working) called test_proc like below -

DELIMITER $$

CREATE DEFINER=`tloken`@`%` PROCEDURE `test_proc`(In user_id varchar(100), OUT message varchar(1000))
BEGIN

 set message ='OK';
END

and calling it from php test page like below -

    $sso = $_GET['sso'];
    $ret = '';
    echo $RepExecutQry = "call test_proc(?, ?)";

$stmt = $conn->Prepare($RepExecutQry);


$stmt->bindParam(1, $sso, PDO::PARAM_INT, 10);
$stmt->bindParam(2, $ret, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 32);

$stmt->execute();

print "Values of bound parameters _after_ CALL:\n";
print "  1: {$ret} 2: {$sso}\n"     

Also tried the solution with bindValue(), but the procedure doesn't return anything.
$conn is a pdo connection object and created properly, tested with var_dump($conn) already.
any suggestions would be valued and appreciated. Thank you

[network18]

Below is not working either -

$data = $conn->query("call test_prod(‘502275248’, @message);")->fetchAll(PDO::FETCH_ASSOC);
$message = $conn->query("select @message;")->fetchAll(PDO::FETCH_ASSOC);
print_r($data);
echo '<br />';
echo $message;

[cereal]

Try if it works fine from the mysql client, because it returns this error to me:

ERROR 1414 (42000): OUT or INOUT argument 2 for routine dbase.test_proc is not a variable or NEW pseudo-variable in BEFORE trigger

You can try to bypass the error by setting a variable before calling the procedure, like this:

DROP PROCEDURE IF EXISTS test_proc;
DELIMITER $$
CREATE PROCEDURE `test_proc`(In user_id varchar(100), OUT message varchar(1000))
BEGIN
 set message ='OK';
END $$
DELIMITER ;

-- as suggested here: https://bugs.mysql.com/bug.php?id=25970#c195094
SET @test = 'world';
CALL test_proc('hello', @test);
SELECT @test;

In PDO this becomes:

<?php

    $conn = require './pdo.php';

    $ret = 'world';
    $sso = 'hello';

    $stmt1 = $conn->prepare("SET @test = ?");
    $stmt1->execute(array($ret));

    $stmt2 = $conn->prepare("CALL test_proc(?, @test)");
    $stmt2->execute(array($sso));

    $query = $conn->query("SELECT @test as result")->fetch(PDO::FETCH_OBJ);

    echo $query->result;

Which seems to return fine to me: OK.

[ryantroop]

That... looks very unfamilliar to me in terms of creating and running procedures... try this instead:

delimiter //
drop procedure if exists GetEvents//
create procedure GetEvents(IN iVal1 int,
                           IN iVal2 int)
begin

select * from myTable where Col1 = iVal1 and Col2 = iVal 2;

end//
delimiter ;

then to call...

you will likely need to use mysqli->multi_query()

because of this, you will likely have to manually sanitze your incoming data, using mysql->real_escape_string() on each parameter.

Of course, from there, it's just using the PHP/MySQLi interface and going from there...

You seem to be familliar with how to connect, etc.. so I spared you that bit. If you need any other help, show some new code and we can go from there.

Side note: if I recall, when I started getting back into PHP a while ago, PDO had awful support for stored procedures. THe only interface for them was with mySQLi, unless you were using PDO with a TSQL server (which is microsoft, not mySQL/whoever owns it now). Anyone know if PDO is now a good interface for mySQL and stored procedures?

[network18]

Thank you all for your replies. While I tried the solutions above, below works fine like before from mysql client (workbench) -

SET @test = 'world';
CALL test_prod('hello', @test);
SELECT @test;

and returns 'ok'. but from the php below is still not showing anything -

$ret = 'world';
$sso = 'hello';

$stmt1 = $conn->prepare("SET @test = ?");
$stmt1->execute(array($ret));

$stmt2 = $conn->prepare("CALL test_proc(?, @test)");
$stmt2->execute(array($sso));

$query = $conn->query("SELECT @test as result")->fetch(PDO::FETCH_OBJ);

var_dump($query);

echo $query->result;

what am I missing!

[cereal]

The user connecting from the PHP script does have the Execute privilege to run procedures?

Docs:

• https://dev.mysql.com/doc/refman/5.6/en/grant.html
• https://dev.mysql.com/doc/refman/5.6/en/privileges-provided.html

You can verify this with a try/catch block:

<?php

    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    try {

        $ret = 'world';
        $sso = 'hello';

        $stmt1 = $conn->prepare("SET @test = ?");
        $stmt1->execute(array($ret));

        $stmt2 = $conn->prepare("CALL test_proc(?, @test)");
        $stmt2->execute(array($sso));

        $query = $conn->query("SELECT @test as result")->fetch(PDO::FETCH_OBJ);
        echo $query->result;

    } catch(PDOException $e) {
        echo $e->getMessage();
    }

If the problem is the privilege then you should get an access violation error. Here's a live test script which seems to work fine:

• http://runnable.com/VT4ZeoLslYQEzmzo/php-pdo-stored-procedure-for-mysql

Just click Run.

[network18]

Thanks for the quick reply 'cereal'. User does have the execute privileges, as its a root user and while same user logged in through workbench could execute the procedure and get the message as 'OK'.

I tried to add try catch like above example -

var_dump($conn);

echo 1;

    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

echo 2;

    try {

        $ret = 'world';
        $sso = 'hello';

        $stmt1 = $conn->prepare("SET @test = ?");
        $stmt1->execute(array($ret));

        $stmt2 = $conn->prepare("CALL test_proc(?, @test)");
        $stmt2->execute(array($sso));

        $query = $conn->query("SELECT @test as result")->fetch(PDO::FETCH_OBJ);
        echo $query->result;

    } catch(PDOException $e) {
        echo $e->getMessage();
    }

Exception is not sent to output but even before that oone strange thing happened, that '1' is sent to output but not '2'. which could mean there's a trouble executing $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); this itself.
while there is no error with $conn. var_dump outputs null error info like -

["connect_errno"]=> int(0) ["connect_error"]=> NULL ["errno"]=> int(0) ["error"]=> string(0) "" ["error_list"]=> array(0) 

[cereal]

Are you setting the connection? For example:

$servername = "localhost";
$dbname     = "test";
$dbusername = "root";
$dbpassword = "";

$conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbusername, $dbpassword);

Otherwise PHP will send a notice for undefined variable $conn and a fatal error:

Fatal error: Call to a member function setAttribute() on a non-object

EDIT:

Also, your var_dump seems more the output of errorInfo(), when creating a connection PDO will return an object on success, errors or exceptions on failure. Can you share your connection code? (No need of credentials)

[network18]

yes the connection is set like below and hence var_dump could output $conn details properly -

$conn = ADONewConnection();

$conn->Connect("$dbhost", "$dbuser", "$dbpassword", "$dbdb");

But there's an error now - "call to undefined method ADODB_mysqli::setAtribute() in .." on command prompt.
looks like it could be adodb version conflict for not having this method?
I have adodb V5.19

[cereal]

Whoops! Sorry, wait a moment. I thought you were using PDO when you started your thread, so I've been referring to it:

• http://php.net/manual/en/book.pdo.php

Your error:

call to undefined method ADODB_mysqli::setAtribute() in

instead refers to MySQLi, which is another API: https://php.net/mysqlinfo.api.choosing

Also ADOdb uses another syntax, so my previous code is almost useless. I'm not familiar with this library, but try:

$ret  = 'world';
$sso  = 'hello';
$dsn  = "mysqli://$dbusername:$dbpassword@$servername/$dbname?persist";
$conn = ADONewConnection($dsn);

$conn->Execute("SET @test = ?", array($ret));
$conn->Execute("CALL test_proc(?, @test)", array($sso));
$result = $conn->getOne("SELECT @test as result");

echo $result;

It works fine for me.

[network18]

thanks cereal. I still couldn't solve it by stored procedure way, so I modified it as a mysql event and its working now.

[cereal]

Ok, thanks for the update!