Ulf Wendel

PDO_MYSQLND: Calling Stored Procedures works fine with mysqlnd

Mike is asking I`m hoping that MySQL stored procedures will work properly. Especially multiple calls in one request. in reply to PDO_MYSQLND: R[a|u]mbling and a breeze of progress. Mike, it depends what you mean by "properly". If you use the API properly it works fine with mysqlnd. Read on for code examples.

  mysqlnd libmysql
  Emulated PS Native PS Emulated PS Native PS
buffered fetch PASS PASS PASS* PASS*
unbuffered fetch PASS PASS PASS* PASS*
  * See below!

Executing a basic stored procedure twice

Code take from the internal test pdo_mysql_attr_oracle_nulls.phpt. Status: PASS. NOTE the use of $stmt->nextRowset(). If you do not call $stmt->nextRowset() in a loop to fetch all result sets you will get the usual 2014 error. MySQLs Stored Procedures always return n (here: 1) result sets for the actual data followed by another result set with the status code. You must use PDOStatement::nextRowset() to fetch them.


/*
Set as required to check all four permuations
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0|1);
$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false|true);
 */
if ($have_procedures && (false !== $db->exec('DROP PROCEDURE IF EXISTS p')) &&
  (false !== $db->exec('CREATE PROCEDURE p() BEGIN SELECT NULL as z, "" AS a, " " AS b, TRIM(" ") as c, " d" AS d, " e" AS e; END;'))) {

  // requires MySQL 5+
  $stmt = $db->prepare('CALL p()');
  $stmt->execute();
  $expected = array(
    array(
      "z" => NULL,
      "a" => NULL,
      "b" => " ",
      "c" => NULL,
      "d" => " d",
      "e" => " e",
    ),
  );
  do {
    $tmp = $stmt->fetchAll(PDO::FETCH_ASSOC);
    if ($tmp != $expected) {
      printf("[004] Expecting %s got %s\n",
        var_export($expected, true), var_export($tmp, true));
    }
  } while ($stmt->nextRowset());

  $stmt->execute();
  do {
    $tmp = $stmt->fetchAll(PDO::FETCH_ASSOC);
    if ($tmp != $expected) {
      printf("[005] Expecting %s got %s\n",
        var_export($expected, true), var_export($tmp, true));
    }
  } while ($stmt->nextRowset());

}

Using a SELECT and a Stored Procedure not returning a result set

Code take from the internal test pdo_mysql_stmt_nextrowset.phpt. Status: PASS with mysqlnd. The MySQL native driver for PHP can handle this. This will not work with the MySQL Client Library (AKA libmysql) and it will not work with PDO_MYSQL as PDO_MYSQL only supports the MySQL Client Library.

NOTE about function test_proc1: in case of unbuffered fetches (PDO default: buffered) you must clean the line by fetching all results from the SELECT before creating a new result set! If you do not use the API properly, you will get a 2014. This is not a bug. If you want unbuffered fetches the rule is that YOU take care of fetching all data before sending any new commands to the server – see also my 2050 race condition example.


function test_proc1($db) {

  $stmt = $db->query('SELECT @VERSION as _version');
  $tmp = $stmt->fetch(PDO::FETCH_ASSOC);
  assert($tmp['_version'] === NULL);
  while ($stmt->fetch()) ;

  $db->exec('DROP PROCEDURE IF EXISTS p');
  $db->exec('CREATE PROCEDURE p(OUT ver_param VARCHAR(25)) BEGIN SELECT VERSION() INTO ver_param; END;');
  $db->exec('CALL p(@VERSION)');
  $stmt = $db->query('SELECT @VERSION as _version');
  var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
  var_dump($stmt->nextRowSet());

}

function test_proc2($db) {

  $db->exec('DROP PROCEDURE IF EXISTS p');
  $db->exec('CREATE PROCEDURE p() BEGIN SELECT id FROM test ORDER BY id ASC LIMIT 3; SELECT id, label FROM test WHERE id < 4 ORDER BY id DESC LIMIT 3; END;');
  $stmt = $db->query('CALL p()');
  do {
    var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
  } while ($stmt->nextRowSet());
  var_dump($stmt->nextRowSet());

}

Stored Procedure returning a different number of columns upon each execution

Code take from the internal test pdo_mysql_stmt_variable_column_count.phpt. Status: PASS


// What will happen if a PS returns a different number of result set column upon each execution?
$db->exec('DROP PROCEDURE IF EXISTS p');
$db->exec('CREATE PROCEDURE p() BEGIN IF RAND() > 0.5 THEN SELECT 1 AS "one"; ELSE SELECT 1 AS "one", 2 AS "two"; END IF; END;');

// Emulates PS first
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);
$column_count = $last_column_count = null;
$stmt = $db->query('CALL p()');
do {
  do {
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
  } while ($stmt->nextRowSet());

  if (empty($row)) {
    printf("[002] Results seem wrong, %s\n",
      var_export($stmt->errorInfo(), true));
    break;
  }

  if (!isset($row['one']) || ($row['one'] != 1)) {
    printf("[003] Expecting array('one' => 1), got %s\n", var_export($row, true));
    break;
  }

  if ((count($row) == 2) &&
      (!isset($row['two']) || ($row['two'] != 2))) {
    printf("[004] Expecting array('one' => 1, 'two' => 2), got %s\n", var_export($row, true));
    break;
  }

  $stmt->execute();
  if ($last_column_count == NULL) {
    $last_column_count = $column_count = count($row);
    continue;
  }
  $column_count = count($row);

} while ($last_column_count == $column_count);

// Native PS
$db = MySQLPDOTest::factory();
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);
$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, 1);
$column_count = $last_column_count = null;
$stmt = $db->prepare('CALL p()');
do {
  $stmt->execute();
  do {
    while ($tmp = $stmt->fetch(PDO::FETCH_ASSOC))
      $row = $tmp;
  } while ($stmt->nextRowSet());

  if (empty($row)) {
    printf("[005] Results seem wrong, %s\n",
      var_export($stmt->errorInfo(), true));
    break;
  }

  if (!isset($row['one']) || ($row['one'] != 1)) {
    printf("[006] Expecting array('one' => 1), got %s\n", var_export($row, true));
    break;
  }

  if ((count($row) == 2) &&
      (!isset($row['two']) || ($row['two'] != 2))) {
    printf("[007] Expecting array('one' => 1, 'two' => 2), got %s\n", var_export($row, true));
    break;
  }

  if ($last_column_count == NULL) {
    $last_column_count = $column_count = count($row);
    continue;
  }
  $column_count = count($row);

} while ($last_column_count == $column_count);

All of the above examples work fine with mysqlnd. Go mysqlnd – with ext/mysql, ext/mysqli and in the future with PDO_MYSQLND as well.

4 Comments