PDOStatement::bindParam and PDO::PARAM_INPUT_OUTPUT for (IN)OUT parameters for stored procedures

[ruebenchandler]

From manual page: https://php.net/pdostatement.bindparam

---

PostgreSQL version: 14
PHP version: 8.1.2

Take the following PostgreSQL PLpg/SQL procedure:

CREATE OR REPLACE PROCEDURE business_layer_thick_db.double
   ( IN OUT  p_value  INTEGER
   )
LANGUAGE plpgsql
AS
$PROC$
   BEGIN
      p_value := p_value * 2;
   END;
$PROC$

When executed directly against the database the following happens:

DO
$$
DECLARE
   v_test  INTEGER  := 2;
BEGIN
   RAISE NOTICE '%', v_test; -- outputs: 2
   CALL business_layer_thick_db.double (p_value => v_test);
   RAISE NOTICE '%', v_test; -- outputs: 4
END;
$$

However, if the procedure is called by a PDO instance in PHP, the following happens:

<?php
   $v_user     = "postgres";
   $v_password = "<<redacted>>";
   $v_dsn      = "pgsql:host=192.168.0.14;port=5432;dbname=dms_t847_22k;user=$v_user;password=$v_password";
   $v_options  =  [  PDO::ATTR_ERRMODE             => PDO::ERRMODE_EXCEPTION,
                     PDO::ATTR_DEFAULT_FETCH_MODE  => PDO::FETCH_ASSOC,
                     PDO::ATTR_EMULATE_PREPARES    => false
                  ];
   
   try
   {
      $v_pdo = new PDO ($v_dsn, $v_user, $v_password, $v_options);
   }
   catch (\PDOException $v_exception)
   {
      throw new \PDOException ($v_exception->getMessage (), (int)$v_exception->getCode ());
   }
   
   $v_test = 2;
      echo $v_test; // outputs: 2
   
   $v_stmt = $v_pdo->prepare
      ("CALL business_layer_thick_db.double (p_value => :v_test)");
   $v_stmt->bindParam
      ( param:     ":v_test",
        var:       $v_test,
        type:      PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT,
        maxLength: 32767
      );
   $v_stmt->execute ();
   
   echo $v_test; // outputs: 2
?>

Basically, the modified value is not returned to the variable binded to the parameter following execution.

The only way to utilise (IN)OUT parameters within stored parameters using the PDO class is as follows:

<?php
   $v_user     = "postgres";
   $v_password = "<<redacted>>";
   $v_dsn      = "pgsql:host=192.168.0.14;port=5432;dbname=dms_t847_22k;user=$v_user;password=$v_password";
   $v_options  =  [  PDO::ATTR_ERRMODE             => PDO::ERRMODE_EXCEPTION,
                     PDO::ATTR_DEFAULT_FETCH_MODE  => PDO::FETCH_ASSOC,
                     PDO::ATTR_EMULATE_PREPARES    => false
                  ];
   
   try
   {
      $v_pdo = new PDO ($v_dsn, $v_user, $v_password, $v_options);
   }
   catch (\PDOException $v_exception)
   {
      throw new \PDOException ($v_exception->getMessage (), (int)$v_exception->getCode ());
   }
   
   $v_test = 2;
      echo $v_test; // outputs: 2
   
   $v_stmt = $v_pdo->prepare
      ("CALL business_layer_thick_db.double (p_value => :v_test)");
   $v_stmt->bindParam
      ( param:     ":v_test",
        var:       $v_test,
        type:      PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT,
        maxLength: 32767
      );
   $v_stmt->execute ();
   
   
   // Get (IN)OUT returned parameters
   // (workaround for PDO::PARAM_INPUT_OUTPUT bug)
   $v_out_params = $v_stmt->fetch ();
   var_dump ($v_out_params); // outputs: array (1) { ["p_value"]=>int(4) }
   $v_test = $v_out_params ("p_value");
   
   
   echo $v_test; // outputs: 4
?>