PDOStatement::bindParam and PDO::PARAM_INPUT_OUTPUT for (IN)OUT parameters for stored procedures
Nobody has claimed this yet.
- 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
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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