php Question posted in
Official manual can be found here.

Php – 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.
*/
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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;

  2. 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']);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search