I have just installed Doctrine DBAL into a simple test PHP project and created a test database and got some queries happening successfully. Including queries that use both positional and named parameters eg:
echo '<br/>';
echo 'Simple SELECT WHERE Query using prepaired statements with positional paramaters:<br/>';
$Users_FirstName = 'Bill';
$sql = "SELECT * FROM users WHERE Users_FirstName = ?";
$stmt = $conn->prepare($sql);
$stmt->bindValue(1, $Users_FirstName);
$stmt->execute();
while ($row = $stmt->fetch()) {
echo $row['Users_FirstName'].'<br/>';
}
echo '<br/>';
echo 'Simple SELECT WHERE Query using prepaired statements with named paramaters:<br/>';
$Users_FirstName = 'Bill';
$sql = "SELECT * FROM users WHERE Users_FirstName = :Users_FirstName";
$stmt = $conn->prepare($sql);
$stmt->bindValue('Users_FirstName', $Users_FirstName);
$stmt->execute();
while ($row = $stmt->fetch()) {
echo $row['Users_FirstName'].'<br/>';
}
All the above works no problems. Now I am trying to execute a simple stored proc.
The target db is MySQL 5.1.37.
The Stored Proc is as follows:
delimiter //
DROP PROCEDURE IF EXISTS TestProcedure//
CREATE PROCEDURE TestProcedure(INOUT TestParam VARCHAR(50))
BEGIN
SELECT CONCAT('Hi ', TestParam, '!!') INTO TestParam;
END//
I have tested this in MySQL with the following SQL code:
delimiter ;
SET @testParam = 'Bill';
CALL `TestProcedure`(@testParam);
SELECT @testParam;
And it correctly returns the result of
'Hi Bill!!'
Now I am trying to execute the same stored proc form PHP using Doctrine DBAL with the following code:
echo '<br/>';
echo 'Call Stored Procedure with INOUT Parm:<br/>';
$INOUTParam = 'Bill';
$sql = "CALL TestProcedure(?)";
$stmt = $conn->prepare($sql);
$stmt->bindParam(1, $INOUTParam);
$stmt->execute();
echo $INOUTParam;
However this returns the following error:
Call Stored Procedure with INOUT Parm: exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1414 OUT or INOUT argument 1 for routine phptestdb.TestProcedure is not a variable or NEW pseudo-variable in BEFORE trigger' in [PATH REMOVED]\Doctrine\DBAL\Statement.php:138 Stack trace: #0
I have searched all over the place and can not find any example of calling a stored proc using bindParam with INOUT or OUT parameters. Does anyone know if Doctrine has support for stored procs, and if so how do you call one? Can can not find any examples on their site.
Regards,
Scott.
