CODEWITHSUNDEEP
phpstored-procedurespdo
Chris Harland
Chris Harland

Reputation: 476

PDO stored procedure call

Okay, not too sure what I've done wrong, but I am having problems running a stored procedure using PDO. The procedure looks a little like this and runs perfectly as a stand alone.

CREATE PROCEDURE [dbo].[user_UserAdd]
            @FirstName      nvarchar(100),
            @Surname        nvarchar(100),
            @EMail          nvarchar(200),
            @Password       nvarchar(16)
AS
BEGIN

DECLARE @UserId     uniqueidentifier
SET     @UserId = NEWID()

INSERT INTO user_Data
VALUES  (@UserId,
         @FirstName,
         @Surname,
         @EMail,
         @Password)

END

I know that the database connection works correctly as a select query returns the correct answers.

My php file contains the following :-

$stpro = $conn->prepare('EXECUTE user_UserAdd ?, ?, ?, ?');
$stpro->bindParam(1, $firstname, PDO::PARAM_STR, 100);
$stpro->bindParam(2, $surname, PDO::PARAM_STR, 100);
$stpro->bindParam(3, $email, PDO::PARAM_LOB, 200);
$stpro->bindParam(4, $password, PDO::PARAM_STR, 16);

// call the stored procedure
$returnvalue = $stpro->execute();

if (!$returnvalue)
{
    return $stpro->errorInfo();
}

This always returns the same error message

 ["2"] = "An invalid PHP type was specified as an output parameter. 
 DateTime objects, NULL values, and streams cannot be specified as output parameters."

I have changed EXECUTE to just EXEC and to CALL and just get the same message. On checking the database it is definitely not inserting the new line of data, but at the same time the php page loads properly and does not kick any error messages regarding the stored procedure not running.

Upvotes: 2

Views: 2760

Answers (1)

meda
meda

Reputation: 45490

Sound like a binding error , although code looks correct.

You can try binding without specifying the type and leave it up to PDO:

$query = "EXECUTE user_UserAdd :firstname, :surname, :email, :password";
$stpro = $conn->prepare($query);
$stpro->bindParam(':firstname', $firstname);
$stpro->bindParam(':surname', $surname);
$stpro->bindParam(':email', $email);
$stpro->bindParam(':password', $password);

// call the stored procedure
$returnvalue = $stpro->execute();

Or just don't bind at all and see if it works:

$query = "EXECUTE user_UserAdd :firstname, :surname, :email, :password";
$stpro = $conn->prepare($query);

// call the stored procedure
$returnvalue = $stpro->execute(array(
                                    ':firstname'=> $firstname,
                                    ':surname'=> $surname,
                                    ':email'=> $email,
                                    ':password'=> $password,     
             ));

Upvotes: 4

Related Questions