phppdophp-pgsql

PDO pgsql: fetch bytea column

In PostgreSql there is a table with a column of type 'bytea' with jpeg. In pgAdmin this column is displayed as [binary data]. In php script I need to get binary data from this column and translate it into base64 string to pass it to json object.

$pdo = new PDO($dsn);
$query = 'select image from image where bid=' . $id . ';';
$stm = $pdo->query($query);
$ok = $stm->execute();
$ok = $stm->bindColumn('image', $lob, PDO::PARAM_LOB);
$ft = $stm->fetch(PDO::FETCH_BOUND);
if ($ft && is_resource($lob))
  {
     //content of $lob: resource id='10' type='stream'

     $stream = $pdo->pgsqlLOBOpen($lob, 'r');    
  }
/*
  Exception has occurred.
  TypeError: PDO::pgsqlLOBOpen(): Argument #1 ($oid) must be of type string, resource given

  I've tried replacing $lob with strings like this: '10' or 'id=10' but got an error.
*/
Solution

  • As far as I can tell, pgsqlLOBOpen() is needed when your LOB is stored in the dedicated LOB storage. In this case you have to follow the code featured on the man page.

    But when it's stored right in the table, then you just fetch it away. Only it needs to be read from a stream.

    // Note that you should always use placeholders
// instead of stuffing any data right into the query!
$query = 'select image from image where bid=?';
$stmt = $pdo->prepare($query);
$stmt->execute([$id]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);

    And now you have a resource type variable in $row['image']. And so when in Resource, do as Resourceans do: