14 Replies - 696 Views - Last Post: 16 March 2014 - 05:33 PM Rate Topic: -----

#1 Galaxy_Stranger  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 100
  • Joined: 07-February 06

PDO and Stored Procedures.

Posted 02 December 2013 - 09:36 PM

I'm having trouble finding tutorials on using PDO with stored procedures.

I understand how to use the prepared SQL statements with bindparam(), but what I'd like to do is use stored procedures instead. So, here's what I've got. The following code is inside my Database class. The stored procedure contains a select statement that uses "user_id":
$this->dbConnection = new PDO("mysql:host=$dbHost;port=$dbPort;dbname=$dbName", $dbUser, $dbPass) ;
$statement = $this->dbConnection->prepare("CALL MY_ROUTINE('user_id')") ;
$returnValue = $statement->fetchAll(PDO::FETCH_OBJ) ;


When I var_dump($returnValue), I get "array(0) { } " even when I use a user id that exists in the table.

My first question is, "Does my Routine need to place the select statement into an output variable?".
My second question is, "What's the standard way of handling data sets returned by stored procedures?".

TIA.

Is This A Good Question/Topic? 0
  • +

Replies To: PDO and Stored Procedures.

#2 astonecipher  Icon User is offline

  • Major DIC Head
  • member icon

Reputation: 621
  • View blog
  • Posts: 2,792
  • Joined: 03-December 12

Re: PDO and Stored Procedures.

Posted 02 December 2013 - 09:44 PM

You have two ways I know of.


1.bindParam(1, $email);

Where the ? is the place holder and the number corresponds to the left to right orientation.

2. bindValue("@name", $name);

Where you use :name or @name as the place holder.

There is also a SO discussion on the difference.

The link isn't showing but,

http://www.stackover...ersus-bindvalue

This post has been edited by astonecipher: 02 December 2013 - 09:46 PM

Was This Post Helpful? 1
  • +
  • -

#3 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2934
  • View blog
  • Posts: 10,151
  • Joined: 08-August 08

Re: PDO and Stored Procedures.

Posted 03 December 2013 - 08:15 AM

Have you read this tutorial?
Was This Post Helpful? 1
  • +
  • -

#4 Galaxy_Stranger  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 100
  • Joined: 07-February 06

Re: PDO and Stored Procedures.

Posted 10 March 2014 - 10:29 PM

Thanks for the replies. I know - timeline. But real-life got in the way.

Ok, I've figured out how to pass arguments to a procedure and get the record set back. But I'd also like to grab output variables from the procedure. This is what I have:
$data = array($postUserName, $postPassword) ;			//	Put parameters into an array.
$sql= "CALL USER_AUTH_ROUTINE(?, ?, @errorMessage)" ;			//	Put Placeholders for parameters.
$statement = $connection->prepare($sql) ;				//	Prepare the statement.
$statement->bindParam(errorMessage, $ERROR_MESSAGE, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 500) ;		//	Grab the Error string.
$statement->execute($data) ;						//	Execute Call with array of parameters.


Later on in the code, I do this to see what I've got:
echo "<br />Error Message: " . $ERROR_MESSAGE ;


Everything runs and I get no errors and I can manipulate the record set. BUT, $ERROR_MESSAGE has nothing in it. Inside the procedure, I assign it a string value:

DELIMITER $$

CREATE DEFINER=`blah`@`blah` PROCEDURE `USER_AUTH_ROUTINE`(IN userName VARCHAR(45), IN userPassword VARCHAR(45), OUT errorMessage VARCHAR(500))
BEGIN
	SELECT *
	FROM USERS a
	WHERE a.USER_NAME = userName
	AND a.USER_PASSWORD = userPassword ;

	SET errorMessage = "TEST" ;
END


I've tried placing $ERROR_MESSAGE in the $data array and replacing "@errorMessage" with another question mark, but that didn't work. The only other solution I've seen is performing another query just to get the output variable - which seems too inefficient to me.

How can I do this while putting the parameters in the array and then passing that during execute()?

This post has been edited by Galaxy_Stranger: 11 March 2014 - 06:09 PM

Was This Post Helpful? 0
  • +
  • -

#5 ArtificialSoldier  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 368
  • View blog
  • Posts: 1,323
  • Joined: 15-January 14

Re: PDO and Stored Procedures.

Posted 11 March 2014 - 09:51 AM

The bindParam line isn't correct, you're telling PHP that you're using a constant called errorMessage. That should be a string, not a constant, and you might have to include the @ before the name.
Was This Post Helpful? 1
  • +
  • -

#6 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3525
  • View blog
  • Posts: 10,171
  • Joined: 08-June 10

Re: PDO and Stored Procedures.

Posted 11 March 2014 - 10:13 AM

you need to tell PDO that you want to use an output parameter explicitly. bitwise OR PDO::PARAM_INPUT_OUTPUT to the data type constant. (cf. http://www.php.net/m....bindparam.php)
Was This Post Helpful? 1
  • +
  • -

#7 Galaxy_Stranger  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 100
  • Joined: 07-February 06

Re: PDO and Stored Procedures.

Posted 12 March 2014 - 10:03 PM

Thanks for the replies.

Ok, I guess it won't let me edit the other post, so here's what I've got:
$myData = array($postUserName, $postPassword) ;           //  Put parameters into an array.
$sql= "CALL USER_AUTH_ROUTINE(?, ?, :myError)" ;           //  Put Placeholders for parameters.
$statement = $connection->prepare($sql) ;                //  Prepare the statement.
$statement->bindParam(':myError', $ERROR_MESSAGE, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 500) ;       //  Grab the Error string.
$statement->execute($myData) ;                     //  Execute Call with array of parameters.


I'm sorry - I'm just lost on this. I can't find any examples of using bindParam() while passing an array to execute(). I don't understand how I should use the syntax for bindParam in this context. I've tried using a number placeholder and all of the other standard methods. I believe I have correctly altered the data type.

To check the procedure call, I logged-in to my MySQL server with the command-line client and was able to run the call and then select the output variable - and it had the correct value, so I know the procedure is correctly assigning the value.

I changed the variable name in case it was reserved or something. In its current state, it runs, but the Error Message value isn't returned. It must have something to do with "myError", but I don't understand what's wrong.

This post has been edited by Galaxy_Stranger: 13 March 2014 - 10:19 AM

Was This Post Helpful? 0
  • +
  • -

#8 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3525
  • View blog
  • Posts: 10,171
  • Joined: 08-June 10

Re: PDO and Stored Procedures.

Posted 12 March 2014 - 10:06 PM

personally I doubt that PDO recognises @myError as a placeholder at all. AFAIK only ? and :NAME are valid.
Was This Post Helpful? 1
  • +
  • -

#9 ArtificialSoldier  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 368
  • View blog
  • Posts: 1,323
  • Joined: 15-January 14

Re: PDO and Stored Procedures.

Posted 13 March 2014 - 09:25 AM

I think you're right, the manual has a few examples:

http://www.php.net/p...ared-statements

Look at examples 4 and 5. It looks like you need to use ? as the placeholder for all variables, and it might be better to use bindParam for all of them just for the sake of consistency instead of passing the array to execute.
Was This Post Helpful? 1
  • +
  • -

#10 Galaxy_Stranger  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 100
  • Joined: 07-February 06

Re: PDO and Stored Procedures.

Posted 13 March 2014 - 10:39 AM

Ok, so it looks like I can't mix and match passing an array to execute() with bindParam(). I think I can deal with this. My big hang-up is that I'm trying to genericize a single method that will take any number of arguments and return the results. Passing the array to execute() makes that easy, I just can't get any return variables back, if needed.

But I think I can get this to work the way I'd like. I'll post my results when I do.
Was This Post Helpful? 0
  • +
  • -

#11 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3525
  • View blog
  • Posts: 10,171
  • Joined: 08-June 10

Re: PDO and Stored Procedures.

Posted 13 March 2014 - 11:32 AM

View PostGalaxy_Stranger, on 13 March 2014 - 06:39 PM, said:

My big hang-up is that I'm trying to genericize a single method that will take any number of arguments and return the results.

what you describe is an Adapter.

as an example the one I have written for PDO:
(note: doesn’t work stand-alone)
<?php
/*
 *      DBPS.php
 *      
 *      Copyright 2009 - 2011 Bertold von Dormilich <Dormilich@netscape.net>
 *      
 *      This program is free software; you can redistribute it and/or modify
 *      it under the terms of the GNU General Public License as published by
 *      the Free Software Foundation; either version 2 of the License, or
 *      (at your option) any later version.
 *      
 *      This program is distributed in the hope that it will be useful,
 *      but WITHOUT ANY WARRANTY; without even the implied warranty of
 *      MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *      GNU General Public License for more details.
 *      
 *      You should have received a copy of the GNU General Public License
 *      along with this program; if not, write to the Free Software
 *      Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
 *      MA 02110-1301, USA.
 */

namespace Dormilich\DB;
      use Dormilich\Interfaces            as IFC;
      use Dormilich\Errors\CheckException as CError;
      use Dormilich\Errors\InitException  as IError;

/**
 * Responsible for DB interaction using the aDB class. Main focus is a 
 * simplified fetching method that is directed using class constants.
 * implements an Iterator to be used in foreach().
 *
 * @author Bertold von Dormilich <Dormilich at netscape dot net>
 */
class DBPS extends DBResult implements \IteratorAggregate, IFC\iDB_Handler
{
    /**
     * @var (int) $exe             how many times self::execute() has been called
     * @var (Iterator) $par        input array for any "result looping"
     */
    protected 
          $exe = 0
        , $par = NULL
    ;
    
    /* __construct(), cf. DBResult::__construct() */

    /**
     * print a summary of this object's actions.
     *
     * @return (string)            HTML formatted output
     */
    public function __toString()
    {
        return sprintf(
            PHP_EOL . "<pre>[ exec: %d ] [ fetch: %d ]</pre>" . PHP_EOL,
            $this->exe, 
            $this->res
        );
    }

    /**
     * implementation of the IteratorAggregate interface. it provides
     * the object that in a foreach() is looped over.
     *
     * @return (Traversable)       traversable/iterateable object
     */
    public function getIterator()
    {
        // if the result set is iterable 
        if (NULL !== $this->par)
        {
            return new DBIterator($this, $this->par);
        }
        // if the result set is traversable
        // execute, if not done yet (requires parameterless query)
        // if a parameter is required, call execute() beforehand
        if (0 === $this->exe)
        {
            parent::getIterator()->execute();
            $this->exe++;
        }
        // traversability is checked in the constructor
        return parent::getIterator();
    }
	
    /**
     * set the parameter array that is to be passed to the Iterator
     *
     * @param (Iterator) $params   parameter array to execute while looping
     * @return (DBPS)              current object for chaining
     * @throws (CheckException)    parameters are not iterable
     */
    public function setParameter(
        $params
    )
    {
        if (is_array($params))
        {
            $this->par = new \ArrayIterator($params);
        }
        elseif ($params instanceof \Iterator)
        {
            $this->par = $params;
        }
        elseif ($params instanceof \Traversable)
        {
            $this->par = new \NoRewindIterator(
            	new \IteratorIterator($params)
            );
        }
        else
        {
            $emsg = "Die Parameter müssen iterierbar sein (Array).";
            throw new CError(11, 1, __METHOD__, $emsg, debug_backtrace());
        }
        return $this;
    }

    /**
     * get a PDO_PARAM_* type for the parameter value. supported types
     * are: string, integer, boolean & null (blob for PHP 6). 
     *
     * @param (mixed) $value       value whose type is to be determined
     * @return (int)               PDO_PARAM_*
     * @throws (CheckException)    data type is not detectable
     */
    private function getValueType(
            $value
    )
    {
        // integers
        if (is_int($value))
        {
            return \PDO::PARAM_INT;
        }
        // strings, decimals
        if (is_string($value) or is_numeric($value)) 
        {
            return \PDO::PARAM_STR;
        }
        // booleans
        if (is_bool($value))
        {
            return \PDO::PARAM_BOOL;
        }
        // null
        if (is_null($value))
        {
            return \PDO::PARAM_NULL;
        }
        // LOBs
        if (is_resource($value))
        {
            return \PDO::PARAM_LOB;
        }
        $emsg = "Der Datentyp des Parameters (" . gettype($value) . ") wird derzeit nicht unterstützt.";
        throw new CError(11, 1, __METHOD__, $emsg, debug_backtrace());
    }

    /**
     * convert input into an array. should input not be an array by default,
     * it is checked, whether it is a single valid value, which is then put into
     * an array.
     *
     * @param (mixed) $data        input value
     * @return (array)
     */
    private function convert(
            $data
    )
    {
        if (is_array($data) or ($data instanceof \Iterator))
        {
            return $data;
        }
        // convert non-array input to array, if possible
        return array($data);
    }

    /**
     * bind the values to the Prepared Statement. currently supports Traversable 
     * and Array input types.
     *
     * @param (Array) $params      parameters to bind
     * @return (void)
     */
    private function bind(
            $params
    )
    {
        // prepare parameters
        foreach ($params as $key => $val)
        {
            // correct numeric input key
            if (is_int($key)) 
            { 
                ++$key; 
            }
            // get PDO_PARAM_* type
            $type = $this->getValueType($val);
            // set value
            $chk = parent::getIterator()->bindValue($key, $val, $type);
        }
    }

    /**
     * execute the Prepared Statement. LOBs need to be passed as resource.
     *
     * @param (array) $param       parameters for the Prepared Statement
     * @return (DBPS)              object instance for chaining
     */
    public function execute(
            $input = array()
    )
    {
        // convert input to array or exit if type mismatch
        $param = $this->convert($input);
        // bind the values to the PS or exit if type mismatch
        $this->bind($param);
        // execute the PS
        parent::getIterator()->execute();
        // increment counter
        $this->exe++;
        return $this;
    }
}


Was This Post Helpful? 1
  • +
  • -

#12 Galaxy_Stranger  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 100
  • Joined: 07-February 06

Re: PDO and Stored Procedures.

Posted 15 March 2014 - 01:14 AM

Ok, still trying to just get it to work. I got rid of the array and replaced everything:
$postUserName = $_POST['userName'] ;
$postUserPassword = $_POST['password'] ;
$ERROR_MESSAGE = 'TEST ERROR' ;

$sql= 'CALL USER_AUTH_ROUTINE(?, ?, ?)' ;           //  Put Placeholders for parameters.
$statement = $connection->prepare($sql) ;                //  Prepare the statement.
$statement->bindParam(1, $postUserName, PDO::PARAM_STR, 45) ;
$statement->bindParam(2, $postUserPassword, PDO::PARAM_STR, 45) ;
$statement->bindParam(3, $ERROR_MESSAGE, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 500) ;		//	Grab the Error string.
$statement->execute() ;                     //  Execute Call.


I'm getting Error 42000 - SQL syntax issue. Shouldn't this be right?


-=[EDIT]=-
Ok, the examples I've seen say this should work. But I'm going to try returning two selects and putting my string information in that second select and grabbing that. Other examples show doing a separate procedure call specifically for the output variable and that just seems inefficient.

This post has been edited by Galaxy_Stranger: 15 March 2014 - 09:53 AM

Was This Post Helpful? 0
  • +
  • -

#13 Galaxy_Stranger  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 100
  • Joined: 07-February 06

Re: PDO and Stored Procedures.

Posted 16 March 2014 - 12:12 AM

Ok - thanks for all the help guys. I found a solution that works for me. I switched gears a bit and it actually simplified things. Here's what I wound up with:

Stored Procedure:
DELIMITER $$

CREATE DEFINER=`blah`@`blah` PROCEDURE `USER_AUTH_ROUTINE`(IN userName VARCHAR(45), IN userPassword VARCHAR(45))
BEGIN

	/*	Grab the user's account information where the password matches in USER_AUTH:     */

	/*		Create local variables:		*/
	DECLARE errorMessage VARCHAR(4000) ;		/*	Used to grab error-correction to display to application.	*/

	/*		Select user information that matches the user name and password provided:	*/
	SELECT *
	FROM LPF_USERS a
	WHERE a.USER_NAME = userName
	AND a.USER_PASSWORD = userPassword ;

	
	SET errorMessage := "SPAWWWWWWWWCK!!!!" ;

	/*		Return error message if there is one:	*/
	SELECT errorMessage ;
END


PHP Code:
		$myData = array($postUserName, $postUserPassword) ;						//	Put parameters into an array.
		$sql= 'CALL USER_AUTH_ROUTINE(?, ?)' ;										//	Put Placeholders for parameters.
		$statement = $connection->prepare($sql) ;								//	Sanitize the statement.
		$statement->execute($myData) ;																//	Execute Call with array of parameters.

		if($statement->rowCount() == 1)
		{
			$statement->setFetchMode(PDO::FETCH_OBJ) ;
			
			//	Grab the record set from the object:
			while( $row = $statement->fetch() )
			{
				echo "<br />UserID: " . $row->USER_ID ;
				echo "<br />First Name: " . $row->USER_FIRST_NAME ;
				echo "<br />Last Name: " . $row->USER_LAST_NAME . "<hr />" ;
			}
			
			//	Grab the error string:
			$statement->nextRowset() ;
			while( $row = $statement->fetch() )
			{
				$ERROR_MESSAGE = $row->errorMessage ;
			}
			
			
			echo "<br />Error Message: " . $ERROR_MESSAGE ;
			
			unset($statement) ;
		}
		else
		{
			echo "There were no results." ;
			unset($statement) ;
		}


Again, thanks for all the help. Now I can take this and clean it up.

This post has been edited by Galaxy_Stranger: 16 March 2014 - 12:16 AM

Was This Post Helpful? 0
  • +
  • -

#14 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3525
  • View blog
  • Posts: 10,171
  • Joined: 08-June 10

Re: PDO and Stored Procedures.

Posted 16 March 2014 - 01:00 PM

Quote

Now I can take this and clean it up.

you could remove all those while() loops since you only have one result set.

and you will always print an empty error message line.
Was This Post Helpful? 1
  • +
  • -

#15 Galaxy_Stranger  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 100
  • Joined: 07-February 06

Re: PDO and Stored Procedures.

Posted 16 March 2014 - 05:33 PM

Thanks for pointing that out. It never occurred to me not to loop through a single record.

But the error message variable is set inside the stored procedure and it's printing. The idea was to figure out how to manually output messages when I wanted to do that.

The next thing is to figure out proper error handling inside the stored procedure and then output proper responses for prompting the user and debugging.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1