PHP MySQL PDO stored procedures and INOUT parameters

Question

I am using some stored procedures against a datastore shared by more than one application and need to call a stored procedure that has an INOUT parameter.

From documentation this would work something like

$prob_param = null;
$stmt = $pdo->prepare('CALL obnoxiousStoredProc(:problem_param)');
$stmt->bindParam(':problem_param', $prob_param, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 11);
$stmt->execute();
echo 'Prob param: ' . var_export($prob_param, true);

And we would see something like

Prob param: int(420)

BUT There is a bug that prevents this from working as expected See this answer.

So instead we get

Syntax error or access violation: 1414 OUT or INOUT argument $parameter_number for routine $procedure_name is not a variable or NEW pseudo-variable

The workaround, as described in the answer is two-fold, separate your input and output parameters (which I can get my DBA to do) and use local variables

e.g.

$stmt = $pdo->prepare('CALL obnoxiousStoredProc(@problem_param)');

and then use a second query

SELECT @problem_param

To get your value.

My question is: what is the scope of this "local variable" in PDO? Am I setting myself for a race condition if my php code is getting simultaneously?

Francis Avila · Accepted Answer · 2012-09-19 19:35:41Z

1

User-defined variables are connection-specific.

See the documentation.

So no, there is no possibility of a race condition in a php execution context.

edited Sep 19, 2012 at 19:35

answered Sep 19, 2012 at 19:29

Francis Avila

31.8k7 gold badges63 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

wmarbut Over a year ago

Thanks! Could I see any collisions with PDO's persistent connections?

Francis Avila Over a year ago

You won't see a "collision"--the variable may have an old value, but your query will give it a new value anyway. That said, you should avoid persistent connections in MySQL because connection state may persist (e.g. table left locked, transaction left open, etc.), which is a subtle source of bugs.

ForeverZer0 · Accepted Answer · 2020-07-25 04:31:41Z

0

This solution worked for me:

$pdo = new PDO(...);
$stmt = $pdo->prepare('CALL obnoxiousStoredProc(@problem_param)');
$stmt->execute();

$stmt2 = $pdo->prepare('SELECT @problem_param');
$stmt2->execute();
$var = $stmt->fetch();

var_dump($var)

edited Jul 25, 2020 at 4:31

ForeverZer0

2,5301 gold badge28 silver badges36 bronze badges

answered Jul 25, 2020 at 0:01

Luis Wizh Angel

7

Collectives™ on Stack Overflow

PHP MySQL PDO stored procedures and INOUT parameters

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related