0

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?

Community
  • 1
  • 1
wmarbut
  • 4,595
  • 7
  • 42
  • 72

2 Answers2

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.

Francis Avila
  • 31,233
  • 6
  • 58
  • 96
  • Thanks! Could I see any collisions with PDO's persistent connections? – wmarbut Sep 19 '12 at 19:30
  • 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. – Francis Avila Sep 19 '12 at 19:37
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)
ForeverZer0
  • 2,379
  • 1
  • 24
  • 32
Luis Wizh Angel
  • 7