I am calling a stored (postgres) procedure using PDO in php. I am able to get the procedure to work. However, the output parameters from the stored procedure don't seem to be passing their values back to the php variables used in the bindParams statements.
$dbh = new PDO(postgres connection info);
$sql = "CALL schema.package.procedure(:dsid, :type, :val, :desc, :scenID, :success, :msg_id, :msg_type, :msg, :result_id)";
$stmt = $dbh->prepare($sql);
#Binding the input parameters
$stmt->bindParam(':dsid', $unex_dsid, PDO::PARAM_STR);
$stmt->bindParam(':type', $unex_type, PDO::PARAM_STR);
$stmt->bindParam(':val', $unex_val, PDO::PARAM_STR);
$stmt->bindParam(':desc', $unex_desc, PDO::PARAM_STR);
$stmt->bindParam(':scenID', $unex_scenID, PDO::PARAM_INT);
#initializing variables to avoid postgres data type casting errors
$out_success_ind = "";
$out_msg_id = 0;
$out_msg_type = "";
$out_msg = "";
$out_result_id = 0;
#Binding the output parameters
$stmt->bindParam(':success', $out_success_ind, PDO::PARAM_STR, 1);
$stmt->bindParam(':msg_id', $out_msg_id, PDO::PARAM_INT);
$stmt->bindParam(':msg_type', $out_msg_type, PDO::PARAM_STR, 4000);
$stmt->bindParam(':msg', $out_msg, PDO::PARAM_STR, 4000);
$stmt->bindParam(':result_id', $out_result_id, PDO::PARAM_INT);
$stmt->execute();
echo "\nPDOStatement::errorInfo(): ";
$arr = $stmt->errorInfo();
print_r($arr);
echo "success_ind: $out_success_ind<br>";
echo "msg_id: $out_msg_id<br>";
echo "msg_type: $out_msg_type<br>";
echo "msg: $out_msg<br>";
echo "result_id: $out_result_id<br>";
Here is the output from that code:
PDOStatement::errorInfo(): Array ( [0] => 00000 [1] => [2] => )
success_ind:
msg_id: 0
msg_type:
msg:
result_id: 0
The procedure works. I can see the effect of the procedure in the database. I just can't get any of the output back to my application. Am I doing something wrong, or does bindParam() not work the way I think it should with postgres/pdo? Is there a better/different way to do this that will allow me to get the procedure output back to my application?
I've done multiple searches of this site and google, and if the answer is out there, I've missed it (multiple times), so any help you can provide to point me in the right direction would be greatly appreciated. Thanks!
CALL
and packages. Presumably you're using a postgres fork with Oracle-compatible features. Adding the result ofselect version()
should help to know about that. – Daniel Vérité Mar 13 at 23:08