using OUT in PDO and PHP

[jrahma]

Hi,

I am trying to use OUT parameter with PDO and PHP to return an email address from my stored pocedure but getting this error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1414 OUT or INOUT argument 13 for routine hrms.sp_web_add_new_employee_leave is not a variable or NEW pseudo-variable in BEFORE trigger' in /home/jassimuser/mybluefile.com/submit_leave.php:37 Stack trace: #0 /home/jassimuser/mybluefile.com/submit_leave.php(37): PDOStatement->execute() #1 {main} thrown in /home/jassimuser/mybluefile.com/submit_leave.php on line 37

this is my code:

$mysql_query = $mysql_connection->prepare('CALL sp_web_add_new_employee_leave(:param_employee_id, :param_is_paid, :param_category, :param_payment, :param_date_from, :param_date_to, :param_total_days, :param_flight_entitlement, :param_flight_destination, :param_address_while_traveling, :param_leave_remarks, :param_created_user, :reporting_to_email)');

$mysql_query->bindParam(':param_employee_id', $_SESSION["employee_number"], PDO::PARAM_STR);
$mysql_query->bindParam(':param_is_paid', $leave_paid, PDO::PARAM_BOOL);
$mysql_query->bindParam(':param_category', $leave_category, PDO::PARAM_STR);
$mysql_query->bindParam(':param_payment', $leave_payment, PDO::PARAM_STR);
$mysql_query->bindParam(':param_date_from', $leave_from, PDO::PARAM_STR);
$mysql_query->bindParam(':param_date_to', $leave_to, PDO::PARAM_STR);
$mysql_query->bindParam(':param_total_days', $leave_days, PDO::PARAM_STR);
$mysql_query->bindParam(':param_flight_entitlement', $flight_entitlement, PDO::PARAM_STR);
$mysql_query->bindParam(':param_flight_destination', $flight_destination, PDO::PARAM_STR);
$mysql_query->bindParam(':param_address_while_traveling', $address_while_traveling, PDO::PARAM_STR);
$mysql_query->bindParam(':param_leave_remarks', $leave_comment, PDO::PARAM_STR);
$mysql_query->bindParam(':param_created_user', $_SESSION["employee_number"], PDO::PARAM_STR);
$mysql_query->bindParam(':reporting_to_email', $reporting_to_email, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 255);
// $mysql_query->bindParam(':param_created_ip', $_SERVER['REMOTE_ADDR'], PDO::PARAM_STR);

$mysql_query->execute();

and line 37 is the ->execute()

How can I fix this please?

[johanafm]

PHP PDO does not currently seem to return data into out or inout parameters. Therefor I suppose it is possible that ” reporting_to_email” is not actually set to INOUT by the underlying driver. You could try replacing the call by

# If both input and output, you will need to set @reporting_to_email first
$db->query(SET @reporting_to_email := 'the value');

$proc->$db->prepare('CALL …, :param_created_user, @reporting_to_email)');
// ...
$proc->execute();

# may possibly need to close cursor before retrieving value
$proc->closeCursor();

$stmt = $db->query('SELECT @reporting_to_email');
print_r($stmt->fetchAll(PDO::FETCH_ASSOC));

[NogDog]

Could you use bindParam() and bindColumn() on the same variable? (I have no idea, just asking. 🙂 )