PostgreSQL – PDO reading and writing binary content to the database

[adsense id=”0514458240″ width=”468″ height=”60″]

PDO is a PHP framework that allows us to easily interchange underlying database technologies without the need to change our code. This works very much like JDBC for Java. We had previously written code which allows us to write Binary LOB content into a MySQL database using PDO. Alternatively you can use the pg framework in PHP. Click here

Here we are demonstrating that with minimal changes the same code can be reused to write and read from a PostgreSQL database.

CARE must be taken that you use standard SQL and not proprietary statements to make this work across multiple databases.

This paper provides the code needed to both write binary content to a PostgreSQL table and to read the same data back. In our example, we will create two scripts, one that reads the contents of a PDF file and adds this into a table. The second script will read the contents of the same record in PostgreSQL and output it to a file. We will then show that the two files are identical.

The table that we created is as follows:

$ psql test
psql (9.3.5)
Type "help" for help.

test=# CREATE TABLE testblob(
test(#   id INT,
test(#   data BYTEA);
test=# 

The first script reads the contents of a PDF file called testimage.jpg.

<?php
	define('DBUSERNAME','user');
	define('DBPASSWORD','');
	define('DBHOST','localhost');
	define('DBNAME','test');
	define('DATASOURCE','pgsql');

	$conn = NULL;

	// Enable Database connection
	function getConnectionString() {
		$options = array();
		if(DBHOST != "") {
			$options[] = "host=" . DBHOST;
		}
		if(DBNAME != "") {
			$options[] = "dbname=" . DBNAME;
		}
		return implode(";", $options);
	}

	try {
		$conn = new PDO(DATASOURCE . ":" . getConnectionString(), DBUSERNAME, DBPASSWORD);
		$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	} catch(PDOException $e) {
		// If we get an exception then we need to terminate
		die('ERROR: ' . $e->getMessage() . "\n");
	}

	$data = file_get_contents("testimage.jpg");

	// Now let's insert an entry into the database
	try {
		$sql = "INSERT INTO testblob(id, data) values (:id, :data)";
		$stmt = $conn->prepare($sql);
		$stmt->bindValue(1, "1", PDO::PARAM_INT);
		$stmt->bindValue(2, $data, PDO::PARAM_LOB);
		$stmt->execute();
	} catch (PDOException $e) {
		die('ERROR: ' . $e->getMessage() . "\n");
	}
?>

It is very important to note that the following statements are added:

• $stmt->bindValue(1, “1”, PDO::PARAM_INT);
• $stmt->bindValue(2, $data, PDO::PARAM_LOB);

If we do not specifically indicate that the second value is of type PARAM_LOB because PostgreSQL does not allow binary data to be passed as a regular ‘string’ value. If you do not do this then this will fail with the following error:

$ php pdo_ins_psql.php 
ERROR: SQLSTATE[22021]: Character not in repertoire: 7 ERROR:  invalid byte sequence for encoding "UTF8": 0xff

If we run this we should now get no error and response from the running process

$ php pdo_ins_psql.php 
$

At this point the binary has been added into the database. We now need a second script which will read this content and write it back out to a separate file:

<?php
	define('DBUSERNAME','user');
	define('DBPASSWORD','');
	define('DBHOST','localhost');
	define('DBNAME','test');
	define('DATASOURCE','pgsql');

	$conn = NULL;

	// Enable Database connection
	function getConnectionString() {
		$options = array();
		if(DBHOST != "") {
			$options[] = "host=" . DBHOST;
		}
		if(DBNAME != "") {
			$options[] = "dbname=" . DBNAME;
		}
		return implode(";", $options);
	}

	try {
		$conn = new PDO(DATASOURCE . ":" . getConnectionString(), DBUSERNAME, DBPASSWORD);
		$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	} catch(PDOException $e) {
		// If we get an exception then we need to terminate
		die('ERROR: ' . $e->getMessage() . "\n");
	}

	$sql = "SELECT data FROM testblob WHERE id = 1";
	$stmt = $conn->prepare($sql);
	$stmt->execute();
	while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
		$data = $row['data'];
	}
	$stmt->closeCursor();
	file_put_contents("testblobpdo.jpg", $data);
?>

To see this in action:

$ php pdo_sel_psql.php 
$ psql test
psql (9.3.5)
Type "help" for help.

test=> select count(1) from testblob;
 count 
-------
     0
(1 row)

test=> \q


$ php writeblob.php 
$ psql test
psql (9.3.5)
Type "help" for help.

test=> select count(1) from testblob;
 count 
-------
     1
(1 row)

test=> \q
$ php readblob.php 


$ ls -ltr
-rw------- 1 rcashell users 229943 Jan 19 12:57 testimage.jpg
-rw-r--r-- 1 rcashell users 229943 Jan 21 17:13 testblobpdo.jpg

[googleplus]