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

  1. Thanks for this clarification.

    In Zend Framework There is an issue:
    http://framework.zend.com/issues/browse/ZF-2101

    Basically, I can`t use many SP CALLs in one request because I receive:
    “SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.”. It happens on windows machines only, as far as I know. It looks like a bug in pdo driver.
    To avoid this error you have to close current connection and start again before next SP CALL. It seems to have nothing to do with framework.

    I haven`t tested PDO_MYSQLND yet, but I sure do so as quick as I finish my ongoing project.

    Regards,
    Mike

  2. It will probably fail with the old PDO_MYSQLND alpha release as well. We’ve kicked out this and other similar bugs not before some two weeks ago. But it works with the current code base.

    Anyway, I don’t get why Zend Framework has choosen PDO_MYSQL as a starting point. Frankly, using PDO is fishing for trouble. AFAIK Zend Framework is the abstraction layer. Usually the abstraction layer is there to hide details of the vendor specific interfaces. For the user of the abstraction layer it must not matter which extension gets used under the hood. The MySQL flagship extension is ext/mysqli – no pain with PDO bugs, access to all MySQL features.

    I’m writing on another posting about nextRowset() currently. Its a pain to map MySQL features to the PDO API. And some features cannot be properly mapped at all.

    If you want to get the most out of MySQL, go ext/mysqli. If you want portability ask yourself how much portability PDO you really gives and what the price is for the “portability”.

  3. It`s not that Zend Framework has choosen something. You may use other drivers as well http://framework.zend.com/manual/en/zend.db.html#zend.db.adapter
    However, as far as I remember the same problem was with mysqli.

    Thanks for your time, I`ll do some tests asap.

    Regards,
    Mike

  4. Regarding the title of this post.

    What i miss is a way to call a stored procedure with multiple INT´s.

    For example:

    CREATE PROCEDURE GetNames(IN items ???????)
    BEGIN
    SELECT name FROM table WHERE id IN(items);
    END

    $dbh = $stmt->prepare(“CALL GetNames(?)”);

    $dbh->bindValue(1, implode(“,”, $array_of_ints), PDO::PARAM_???);

    Same goes for MySQLi a.f.a.i.k. ?