|
|
Posted by Louis-Philippe Huberdeau on 01/10/06 14:58
I have always used named parameters with PDO. I don't like having to rely on
the parameter order.
$stmt = $db->prepare( "SELECT foo FROM bar WHERE baz = :baz" );
$stmt->bindParam( ':baz', 123 );
$stmt->execute();
Did you try using PDO with a simple query first using your @? symbol? The
problem might not be with the stored procedure, but with the parameter
binding.
--
Louis-Philippe Huberdeau
James wrote:
> Hey everyone...
> I'm having an issue with a seemingly simple piece of PHP/MySQL
>
> I have a stored procedure in MySQL5 as such:
>
> SQL:
> --------------
> DELIMITER $$;
> DROP PROCEDURE IF EXISTS `test`.`sp_returns_string`$$
> CREATE PROCEDURE `test`.`sp_returns_string`(OUT vOutput varchar(32))
> BEGIN
> SET vOutput = 'It Worked';
> END$$
> DELIMITER ;$$
> --------------
>
> And a piece of PHP that uses PDO to call the Stored Proc as such:
>
> PHP:
> --------------
> <?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_host = "localhost"; // the hostname of the database server
> $DB_user = "root"; // YOUR username to connect with
> $DB_pass = "password"; // YOUR user's password
> $DB_dbName = "test"; // the name of the database to connect to
> $DB_dbType = "mysqli"; // the type of database server.
>
> $DB_Con = "mysql:host=$DB_host;dbname=$DB_dbName";
> $dbOptions = array();
>
> //Create a DB connection
> $db = new PDO($DB_Con, $DB_user, $DB_pass, $dbOptions);
>
> $return_value = '';
> $stmt = $db->prepare("CALL sp_returns_string(@?)");
> $stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000);
> $stmt->execute();
> print_r($stmt);
> print "<br/>Returned: $return_value<br/><br/>\r\n";
> ?>
> --------------
>
> This should print something like this:
> --------------
> PDOStatement Object ( [queryString] => CALL sp_returns_string(@?) )
> Returned: It Worked!
> --------------
>
> But unfortunately it NEVER returns anything.
> The Stored Proc works fine and returns the string ok, but only when I'm
> calling it from an SQL console.
>
> The PHP PDO documentation says to use:
> --------------
> $return_value = '';
> $stmt = $db->prepare("CALL sp_returns_string(?)");
> $stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000);
> --------------
> Note the missing "@".
> But when I do this, it complains that the parameter isn't a variable and
> the call dies.
>
> (see: http://www.php.net/pdo/ for the examples)
>
> Any help would be great!
>
> Thanks.
> James.
Navigation:
[Reply to this message]
|