How to work with Blobs when using PostgreSQL and PHP

 

“Blob” stands for “binary large object” and refers to raw binary data stored in a database. Blobs can be images, audios, or other large file formats. Databases handle blobs differently, and here we will see an example of how an image is processed in PostgreSQL and PHP.

Prerequisites

For this demonstration we will be using the following software versions:

1. CentOS Linux release 7.5.1804

2. PostgreSQL 12

3. Apache 2.4

4. PHP 7

 

Example of using a blob in a PHP application

1. Description of the example program

The example PHP program will insert an image, by reading it in chunks, to a PostgreSQL database as a pg_largeobject. The program will then read the inserted image data in chunks and will save it in a new image file.

The program will then display both the images. The OID (object identifier) of the inserted image will be stored in the following table:

CREATE TABLE tab_logos(

  logo_id       int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  logo_name     VARCHAR(255) NOT NULL UNIQUE,

  logo_img_oid  OID

);

 

2. Settings to allow new file creation

Be sure you have the following settings configured in the Apache server:

sudo chown -R apache:apache /var/www/html/

sudo chcon -R -t httpd_sys_content_t /var/www/html/

sudo chcon -R -t httpd_sys_rw_content_t /var/www/html/

sudo systemctl restart httpd

 

3. Example programs

Now, let’s create the program using PHP.

[abbas@localhost html]$ pwd

/var/www/html

 

Create the following file pg_lo_img_to_db.php at the above location with the content as shown below:

pg_lo_img_to_db.php

<?php

 $dbconn = pg_connect("host=127.0.0.1 user=postgres dbname=postgres port=5432")

 or die('Could not connect: ' . pg_last_error());

 $logo_file = 'pg.png';

 $logo_handle = fopen($logo_file, 'rb') or die('Cannot open file:  '.$logo_file);

 $chunk_size = 10000;

 pg_query($dbconn, "BEGIN") or die('BEGIN failed: ' . pg_last_error());

 $logo_oid = pg_lo_create($dbconn);

 echo "<p>Logo OID : $logo_oid</p>";

 $lo_handle = pg_lo_open($dbconn, $logo_oid, "w") 

or die('pg_lo_open failed: ' . pg_last_error());

 echo "<p>LO Handle : $lo_handle</p>";

 $logo_name = '\'pg_logo\'';

 $query = 'INSERT INTO tab_logos(logo_name, logo_img_oid) VALUES(' . $logo_name . 

',' . $logo_oid . ')';

 $result = pg_query($query) or die('Insert to logos table failed: ' . 

pg_last_error());

 pg_free_result($result);

 echo "<p>Insert to table done</p>";

 while (true)

 {

  $logo_data = fread($logo_handle, $chunk_size) 

or die('Cannot read file:  '.$logo_file);

  if ($logo_data === false)

   break;

  $data_len = strlen($logo_data);

  echo "<p>Chunk Length: $data_len</p>";

  pg_lo_write($lo_handle, $logo_data, $data_len) 

or die('pg_lo_write failed: ' . pg_last_error());

  $offset = pg_lo_tell($lo_handle);

  echo "<p>Seek position is: $offset</p>";

  if ($data_len < $chunk_size)

   break;

 }

 fclose($logo_handle) or die('Cannot close file:  '.$logo_file);

 pg_lo_close($lo_handle) or die('pg_lo_close failed: ' . pg_last_error());

 pg_query($dbconn, "COMMIT;")  or die('COMMIT failed: ' . pg_last_error());

 pg_close($dbconn);

 echo "<p>Image inserted into large object</p>";

?>

[abbas@localhost html]$ pwd

/var/www/html

 

Now, create the following file pg_lo_img_from_db.php at the above location with the content as shown below:

pg_lo_img_from_db.php

<?php

 $dbconn = pg_connect("host=127.0.0.1 user=postgres dbname=postgres port=5432")

 or die('Could not connect: ' . pg_last_error());

 $logo_file = 'pg_copy.png';

 $logo_handle = fopen($logo_file, 'wb') or die('Cannot open file:  '.$logo_file);

 $chunk_size = 10000;

 pg_query($dbconn, "BEGIN") or die('BEGIN failed: ' . pg_last_error());

 $logo_name = '\'pg_logo\'';

 $query = 'SELECT logo_img_oid FROM tab_logos WHERE logo_name = ' . $logo_name . ';';

 $result = pg_query($query) or die('Select from logos table failed: ' .

pg_last_error());

 $row = pg_fetch_row($result);

 $logo_oid = $row[0];

 pg_free_result($result); 

 echo "<p>Logo OID : $logo_oid</p>";

 $lo_handle = pg_lo_open($dbconn, $logo_oid, "r") or die('pg_lo_open failed: ' .

pg_last_error());

 echo "<p>LO Handle : $lo_handle</p>";

 while (true)

 {

  $logo_data = pg_lo_read($lo_handle, $chunk_size) or die('pg_lo_read failed: ' .

pg_last_error());

  if ($logo_data === false)

   break;

  $data_len = strlen($logo_data);

  echo "<p>Chunk Length: $data_len</p>";

  fwrite($logo_handle, $logo_data, $data_len) 

or die('Cannot write to file:  '.$logo_file);

  $offset = pg_lo_tell($lo_handle);

  echo "<p>Seek position is: $offset</p>";



  if ($data_len < $chunk_size)

   break;

 }

 fclose($logo_handle) or die('Cannot close file:  '.$logo_file);

 pg_lo_close($lo_handle) or die('pg_lo_close failed: ' . pg_last_error());

 pg_query($dbconn, "COMMIT;")  or die('COMMIT failed: ' . pg_last_error());

 pg_close($dbconn);

 echo "<p>Image file created by reading large object</p>";

?>



[abbas@localhost html]$ pwd

/var/www/html

 

Create the following file disp_imgs.html at the above location with the content as shown below:

disp_imgs.html 

<html>

<head>

<title> PostgreSQL Large Objects in PHP </title>

</head>

<body>

<p>Original Image</p>

<p><img src="pg.png" alt="Original Image"></p>

<p>Image got from Large Object</p>

<p><img src="pg_copy.png" alt="Image got from Large Object"></p>

</body>

</html>

[abbas@localhost html]$ pwd

/var/www/html

 

Update the file index.html at the above location with the content as shown below:

index.html 

<html>

 <head>

  <title> PostgreSQL Large Objects in PHP </title>

 </head>

 <body>

  <p>This program inserts PostgreSQL logo in pg_largeobjects catalog table.</p>

  <p>The image is read chunks by chunk.</p>

  <p>The program then reads the inserted data and creates another image files</p>

  <p>The program then displays both the images</p>

  <p><a href="example_1.php">Check PHP Version</a></p>

  <p><a href="pg_ver.php">Check PostgreSQL version</a></p>

  <p><a href="pg_lo_img_to_db.php">Copy image as a large object in the database</a></p>

  <p><a href="pg_lo_img_from_db.php">Get image as large object from the database</a></p>

  <p><a href="disp_imgs.html">Display Both Images</p>

 </body>

</html>

[abbas@localhost html]$ pwd

/var/www/html

 

Download the Postgres logo from the following site https://commons.wikimedia.org/wiki/File:Postgresql_elephant.svg and place the image file as pg.png at /var/www/html.

 

4. Running the example programs

Copying image from file system to large object:

 

Click on the “Copy image as a large object in the database” link:

Now check the inserted image in the database:

select * from tab_logos;

 logo_id | logo_name | logo_img_oid 

---------+-----------+--------------

       1 | pg_logo   |        16475

(1 row)

 

Reading the image from the large object and copying it to a file:

Click on the “Get image as large object from the database” link:

Displaying both images:

Click on the “Display Both Images” link: