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

Recommended Answers

All 11 Replies

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;

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.

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?

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!

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

Docs:

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:

Just click Run.

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) 

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)

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

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

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.

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

Ok, thanks for the update!

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.