Is there any way I can 'stream' a PDO query result 'into' the output buffer, instead of storing it into a string?

Question

There's little to be added, if you see the title of this question.

I've got a query that retrieves a single row from a MySQL table, and I'm interested in a particular column, which is a BLOB. I would like PHP to write it into the output buffer, instead of storing ~500 KB into a string (which furthermore I'm not sure would be binary-safe).

PDOStatement functions like:

string PDOStatement::fetchColumn ([ int $column_number = 0 ] )

don't help me.

Can you help giving me at least a direction? Thanks in advance.

P.S.: I know storing ~500 KB stuff inside a DB table is not good, but it's not my choice, I just have to stick with it.

Answer 1

I strongly believe the batch processing with Doctrine or any kind of iterations with MySQL (PDO or mysqli) are just an illusion.

@dimitri-k provided a nice explanation especially about unit of work. The problem is the miss leading: "$query->iterate()" which doesn't really iterate over the data source. It's just an \Traversable wrapper around already fully fetched data source.

An example demonstrating that even removing Doctrine abstraction layer completely from the picture, we will still run into memory issues:

echo 'Starting with memory usage: ' . memory_get_usage(true) / 1024 / 1024 . " MB \n";

$pdo  = new \PDO("mysql:dbname=DBNAME;host=HOST", "USER", "PW");
$stmt = $pdo->prepare('SELECT * FROM my_big_table LIMIT 100000');
$stmt->execute();

while ($rawCampaign = $stmt->fetch()) {
    // echo $rawCampaign['id'] . "\n";
}

echo 'Ending with memory usage: ' . memory_get_usage(true) / 1024 / 1024 . " MB \n";

Output:

Starting with memory usage: 6 MB 
Ending with memory usage: 109.46875 MB

Here, the disappointing getIterator() method:

namespace Doctrine\DBAL\Driver\Mysqli\MysqliStatement

/**
 * {@inheritdoc}
 */
public function getIterator()
{
    $data = $this->fetchAll();

    return new \ArrayIterator($data);
}

You can use my little library to actually stream heavy tables using PHP Doctrine or DQL or just pure SQL. However you find appropriate: https://github.com/EnchanterIO/remote-collection-stream

Answer 2

See this page. This loads the data into a stream, which can then be used with f* functions, including outputting directly to the browser with fpassthru. This is the example code from that page:

<?php
$db = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2');
$stmt = $db->prepare("select contenttype, imagedata from images where id=?");
$stmt->execute(array($_GET['id']));
$stmt->bindColumn(1, $type, PDO::PARAM_STR, 256);
$stmt->bindColumn(2, $lob, PDO::PARAM_LOB);
$stmt->fetch(PDO::FETCH_BOUND);

header("Content-Type: $type");
fpassthru($lob);
?>

The key here is that after $stmt->execute(), you call $stmt->bindColumn('columnName', $stream, PDO::PARAM_LOB);, then call $stmt->fetch(PDO::FETCH_BOUND) to get the row (where the values are stored into the bound PHP variables). This is how I used it in Drupal, tested and working; it includes a lot of extra cache handling that should speed up your clients and only requires you to keep track of the Last-Modified time of your blobs:

<?php
$rfc2822_format = 'D, d M Y H:i:s e';

// This is basically the Drupal 7 way to create and execute a prepared
// statement; the `->execute()` statement returns a PDO::Statement object.
// This is the equivalent SQL:
//   SELECT f.fileType,f.fileSize,f.fileData,f.lastModified
//   FROM mfiles AS f WHERE fileID=:fileID
// (with :fileID = $fileID)
$statement = db_select('mfiles', 'f')
  ->fields('f', array('fileType', 'fileSize', 'fileData', 'lastModified'))
  ->condition('fileID', $fileID, '=')
  ->execute();
// All of the fields need to be bound to PHP variables with this style.
$statement->bindColumn('fileType', $fileType, PDO::PARAM_STR, 255);
$statement->bindColumn('fileSize', $fileSize, PDO::PARAM_INT);
$statement->bindColumn('fileData', $fileData, PDO::PARAM_LOB);
$statement->bindColumn('lastModified', $lastModified, PDO::PARAM_STR, 19);

$success = false;

// If the row was fetched successfully...
if ($statement->fetch(PDO::FETCH_BOUND)) {
  // Allow [public] caching, but force all requests to ask the server if
  // it's been modified before serving a cache [no-cache].
  header('Cache-Control: public no-cache');

  // Format the Last-Modified time according to RFC 2822 and send the
  // Last-Modified HTTP header to aid in caching.
  $lastModified_datetime = DateTime::createFromFormat('Y-m-d H:i:s',
    $lastModified, new DateTimeZone('UTC'));
  $lastModified_formatted = $lastModified_datetime->format($rfc2822_format);
  header('Last-Modified: ' . $lastModified_formatted);

  // If the client requested If-Modified-Since, and the specified date/time
  // is *after* $datetime (the Last-Modified date/time of the API call), give
  // a HTTP/1.1 304 Not Modified response and exit (do not output the rest of
  // the page).
  if (array_key_exists('HTTP_IF_MODIFIED_SINCE', $_SERVER)) {
    // Ignore anything after a semicolon (old browsers sometimes added stuff
    // to this request after a semicolon).
    $p = explode(';', $_SERVER['HTTP_IF_MODIFIED_SINCE'], 2);
    // Parse the RFC 2822-formatted date.
    $since = DateTime::createFromFormat($rfc2822_format, $p[0]);

    if ($lastModified_datetime <= $since) {
      header('HTTP/1.1 304 Not Modified');
      exit;
    }
  }

  // Create an ETag from the hash of it and the Last-Modified time, and send 
  // it in an HTTP header to aid in caching.
  $etag = md5($lastModified_formatted . 'mfile:' . $fileID);
  header('ETag: "' . $etag . '"');

  // If the client requested If-None-Match, and the specified ETag is the
  // same as the hashed ETag, give a HTTP/1.1 304 Not Modified response and
  // exit (do not output the rest of the page).
  if (array_key_exists('HTTP_IF_NONE_MATCH', $_SERVER) && $etag ==
      str_replace('"', '', stripslashes($_SERVER['HTTP_IF_NONE_MATCH']))) {
    header('HTTP/1.1 304 Not Modified');
    exit;
  }

  // Set the content type so that Apache or whatever doesn't send it as
  // text/html.
  header('Content-Type: ' . $fileType);

  // Set the content length so that download dialogs can estimate how long it
  // will take to load the file.
  header('Content-Length: ' . $fileSize);

  // According to some comments on the linked page, PDO::PARAM_LOB might
  // create a string instead of a stream.
  if (is_string($fileData)) {
    echo $fileData;
    $success = true;
  } else {
    $success = (fpassthru($fileData) !== false);
  }
}
?>

Aside: If you need to provide a filename, a quick and dirty solution is to add the filename to the actual URL referencing the file; for http://example.com/fileurl.php, use http://example.com/fileurl.php/filename.jpg. This may not work if something is already interpreting the path info (like Drupal); the "better" solution is to send the header Content-Disposition: attachment; filename=filename.jpg, but this also prevents clients from viewing the image directly in the browser (although this may be a good thing depending on your situation).