php / php/doc-en

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

Open
#2,309 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Extension: pdo
Dominant language
XML
Stars
596
Forks
890
Avg merge
1d 15h
Merged PRs (30d)
55

Description

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
?>

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the PDOStatement::bindParam manual page linked in the issue and review its documentation of PDO::PARAM_INPUT_OUTPUT. Reproduce the PostgreSQL stored-procedure example, then update the page so the observed behavior and fetch-based workaround are accurately documented.

Written by the indexing model from the issue text.

Assessment

Tech stack
php, postgresql
Domain
documentation
Issue type
Documentation
Difficulty
2/5
Estimated time
1-3 hours
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.