postgresql: reading binary blobs from php

Question

When i've try to read blob data from database, i've something like this:

xffd8ffe000104a46494600010201006000600000...

It's only on windows OS, on linux it works as it should be

sql:

select lob from table where id = ...

tried to use ::bytea, nothing changed

in php just fetch with PDO and file_put_contents

I can convert data using convert_from(lob, 'UTF8') and it works with xml, but i need a solution for binary data (such as zip)

upd: php code

$db = new PDO('pgsql:...');
$pds = $db->prepare("select lob from table where id = :id");
$pds->bindParam('id', $id);
$pds->execute();
$r = $pds->fetch(PDO::FETCH_ASSOC);
file_put_contents('Chrysanthemum.jpg', $r['lob']);

Answer 1

The hex string shown in the question is generated by PostgreSQL 9.0 or higher when the bytea_output parameter is set to hex.

A plausible reason for the problem is that your PHP on Windows is linked with a pre-9.0 libpq. These older versions won't decode these contents.

Either you can upgrade to a newer libpq, or as a workaround you could do before selecting any bytea value:

$db->query("SET bytea_output=escape");

That will revert (for the current session) the bytea text representation to the old way that both older and newer versions of libpq can decode.