Q23747835 - PHP -retrieve-variable-from-stored-proc

<?php
include __DIR__ .'/__bootstrap__.php';

/* ---------------------------------------------------------------------------
 * Procedure testing start
 */
$db = appDIC('getDbConnection', 'default'); // get the default db connection
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

// setup the PHP variables that will be used
$phpInParam     = 5;
$phpInOutParam  = 404;          /* PHP InOut variable  ==> read and should be changed  */
$phpOutParam    = null;         /* PHP Out   variable  ==> should be changed           */


/* ----------------------------
 * prepare the procedure...
 */
$sql = "call demoSpInOut(:phpInParam,
                         @varInOutParam, /* mysql variable name will be read and update */
                         @varOutParam)"; /* mysql variable name that will be written to    */

$stmt = $db->prepare($sql);


/* ------------------------------------
 * set all the parameters to the Query
 * 1) bind the PHP variables
 * 2) Set the SQL User INOUT variables
 */

$stmt->bindParam(':phpInParam', $phpInParam, PDO::PARAM_INT);

$db->exec("SET @varInOutParam = $phpInOutParam"); // This is safe as it does not execute.

/*
 * Execute the query
 */
$allOk = $stmt->execute();

/* ------------------------------------------------------------
 * get all the output from the procedure into the PHP variables
 */
$sql = "SELECT @varInOutParam AS phpInOutParam,
               @varOutParam   AS phpOutParam
        FROM dual";
$results = current($db->query($sql)->fetchAll());

$phpInOutParam = $results['phpInOutParam'];
$phpOutParam   = $results['phpOutParam'];

/* --------------------------------------------------------------------
 * show values of php variables after execution
 */
\Kint::dump($phpInParam, $phpInOutParam, $phpOutParam);

/* Output:
┌──────────────────────────────────────────────────────────────────────────────┐
│                                 $phpInParam                                  │
└──────────────────────────────────────────────────────────────────────────────┘
string (1) "5"
┌──────────────────────────────────────────────────────────────────────────────┐
│                                $phpInOutParam                                │
└──────────────────────────────────────────────────────────────────────────────┘
string (3) "409"
┌──────────────────────────────────────────────────────────────────────────────┐
│                                 $phpOutParam                                 │
└──────────────────────────────────────────────────────────────────────────────┘
string (3) "-15"
▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄
Called from +57 K:\developer\testmysql\pip2_startapp\shell\pdoCallProcedureSqlVars.php
*/