php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #56794 bindParam does not work with OUT/INOUT Parameters on stored procedures
Submitted: 2006-01-24 07:54 UTC Modified: 2006-04-09 03:13 UTC
From: jprice at netspace dot net dot au Assigned:
Status: Not a bug Package: PDO_MYSQL (PECL)
PHP Version: 5.1.1 OS: Windows
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: jprice at netspace dot net dot au
New email:
PHP Version: OS:

 

 [2006-01-24 07:54 UTC] jprice at netspace dot net dot au
Description:
------------
Using a one-line Stored Procedure and a fairly simple PHP/PDO test calling a stored procedure, we should see the variable $retun_value change from "Before" to "After"...
All the MySQL Stored Procedure does is set the OUT parameter to the text "After"...

(** I'm using/tried PHP 5.0.5, 5.1.1 and 5.1.2 with MySQL 5.0.15 for the purpose of these tests)


MySQL v5.x:
==============
DELIMITER $$;
DROP PROCEDURE IF EXISTS `test`.`sp_returns_string`$$
CREATE PROCEDURE `test`.`sp_returns_string`(OUT vOutput varchar(32))
BEGIN
    SET vOutput = 'After';
END$$
DELIMITER ;$$
==============

Calling the SP from the command line does EXACTLY as expected, it returns the text "After"...
But it fails from PHP/PDO.

Variations i've tried on the code below include using a named parameter (e.g. :MyVar) but this fails just the same, I've tried NOT including the '@' but this fails with "OUT or INOUT argument 1 for routine test.sp_returns_string is not a variable", and various other changes including making the param in the stored proc an INOUT instead of OUT but nothing works... same error each time.


Reproduce code:
---------------
<?php

//First - check/load PDO!
if (!extension_loaded('pdo_mysql')) {
    // If not loaded we could try loading it manually
    $prefix = (PHP_SHLIB_SUFFIX == 'dll') ? 'php_' : '';
    if (!@dl($prefix . 'pdo_mysql.' . PHP_SHLIB_SUFFIX)) {
        die('pdo_mysql unavailable');
    }
}

$DB_Con = "mysql:host=localhost;dbname=test";
$db = new PDO($DB_Con, 'root', 'password', aray());

$return_value = 'Before';
print "Return_Value = $return_value<br/><br/>\r\n";
$stmt = $db->prepare("CALL sp_returns_string(@?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 32);
$stmt->execute();
print "Return_Value = $return_value<br/><br/>\r\n";
?>

Expected result:
----------------
Return_Value = Before
Return_Value = After

Actual result:
--------------
Return_Value = Before
Return_Value = Before

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-04-09 01:32 UTC] wez@php.net
reclassify
 [2006-04-09 03:13 UTC] wez@php.net
It appears that the only way to bind output parameters is to bind them to SQL level variables, otherwise you get this error:

PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 1414 OUT or INOUT argument 1 for routine test.sp_returns_string is not a variable

The following is the workaround:

$db = new PDO('mysql:dbname=test', 'root', '');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$stmt = $db->prepare("CALL sp_returns_string(@a)");
$stmt->execute();
print_r($db->query("SELECT @a")->fetchAll());

In the future, mysql might provide better C API support for this kind of thing; until that happens, there is not anything that can be done to fix it in PHP.
 [2011-09-05 21:45 UTC] iowamiker at hotmail dot com
I don't think it is right to say this is a MySQL problem.  In C#, I cna get the output variables no problem.  If it works in C#, then PHP ought to be able to make it work.

In C#, this works:
 cmd.Parameters.Add("@AssignedPK", SqlDbType.Int).Direction = ParameterDirection.Output;
 i = cmd.ExecuteNonQuery();
 dtox.cmfileID = long.Parse(cmd.Parameters["@AssignedPK"].Value.ToString());

--Mike Rains, BS/Computer Science, MCSE+I, MCSD, IBM CSD, Sun JCP
 
PHP Copyright © 2001-2019 The PHP Group
All rights reserved.
Last updated: Thu Nov 14 12:01:36 2019 UTC