getting a value from a mysql Stored Function in PDO, having trouble

[arianhojat]

Hey all,

Im having no problems with stored procedures in PDO, but stored functions are giving me hell...

 

I have this mysql stored func to test that just returns simple value:

 

DELIMITER $$
DROP FUNCTION IF EXISTS `simple_operation` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `simple_operation`(price int) RETURNS int(11)
RETURN price*1000 $$
DELIMITER ;

 

and here is my code to actually get the value...

The 2 ways that Im trying to query + get the value work fine standalone in the mysql query browser, so how the heck i do this in php?

 

<?php

$user = "yoink";
$pass = "yoink";
$pdo = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

//none of these work, but the queries work fine standalone in the mysql query browser

foreach($pdo->query( 'SELECT simple_operation(5) as x' ) as $row)
{
  print_r($o);
}


$stmt = $pdo->prepare("SELECT simple_operation(?) as x");
$val=5;
$stmt->bindParam(1, $val);
$stmt->execute();
while ($row = $stmt->fetch()) {
   echo print_r($row);
}


$pdo->prepare("SET @x=simple_operation(5)");
foreach($pdo->query( 'SELECT @x' ) as $row)
{
  print_r($o);
}

 

for comparision, the stored procs are easy to call:

 

print '<h3>PDO: calling sp with 'out' variables</h3>';
$pdo->query( 'CALL get_user(1, @first, @last)' );
foreach($pdo->query( 'SELECT @first, @last' ) as $row)
{
  debug($row);
}

print '<h3>PDO: calling sp returning a recordset</h3>';
foreach($pdo->query( 'CALL get_users()' ) as $row)
{
  debug($row);
}

 

[arianhojat]

Think it has to do with i have some select queries above it...

 

if i try to spit out error with:

$stmt = $pdo->query( 'SELECT simple_operation(5) as x' );

if (!$stmt) {

    echo "\nPDO::errorInfo():\n". $pdo->errorCode().'...';

    print_r($pdo->errorInfo()); 

}

it says:

"DO::errorInfo(): HY000...Array ( [0] => HY000 [1] => 2014 [2] => Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. )"

 

Setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY as recommended didnt work either or using fetchAll's for all queries

Hmmmm, will take a look....

 

http://www.phpbuilder.com/board/showthread.php?t=10360219

http://bugs.php.net/bug.php?id=44081