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.
*/

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.
*/

• php
• pdo
• php-pgsql

Share Improve this question Follow edited Nov 18, 2024 at 7:51 Your Common Sense 158k4242 gold badges225225 silver badges368368 bronze badges asked Nov 17, 2024 at 13:45 proaproa 1322 bronze badges 6

• Looks like a bug in pgsqlLOBOpen. why don't you just make it $lob = $ft['image'] without any bindColumn and call it a day? – Your Common Sense Commented Nov 17, 2024 at 14:21
• OR, it seems you made a mistake. the man page says you need to use PDO::PARAM_STR not PDO::PARAM_LOB – Your Common Sense Commented Nov 17, 2024 at 17:12
• I tried many combinations for bindColumn: PARAMS_LOB, PARAMS_STR, and for fetch: FETCH_OBJ, FETCH_BOUND, FETCH_ASSOC. I always get the resource. The problem is how to get stream from this resource. – proa Commented Nov 17, 2024 at 17:28
• What happens if you fetch that column JUST like any other, WITHOUT bindColumn? – Your Common Sense Commented Nov 17, 2024 at 18:24
• In any case, resource always comes for the [binary data] column. – proa Commented Nov 17, 2024 at 21:00

 |  Show 1 more comment

2 Answers 2

Sorted by: Reset to default 0

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:

• if you need to get it as a variable (to base64 encode it for example)

    $image = stream_get_contents($row['image']);
  

• if you need to store it as a file

    $fp = fopen('image.png', 'w');
    stream_copy_to_stream($row['image'], $fp);
  

• if you need to stream it right into output

    fpassthru($row['image']);
  

Another solution:

$dsn = "host=localhost dbname=preview user=postgres password=3333";
$conn = pg_connect($dsn);
$result = pg_query_params($conn, 'select * from image where bid=$1',[$id]);
$data = pg_fetch_assoc($result);
$image = $data['image']; //like: \xffd8ffe000104a46494600
$imgstr = substr($image, 2); // remove: \x
$bin = hex2bin($imgstr); //like: ����JFIF``�
$base64 = base64_encode($bin); //like: \/9j\/4AAQSkZJRg
$data['image'] = $base64;
$json = json_encode($data);
echo $json;