12.2 Working with PostgreSQL BLOBs Using PHP


PHP's functions related to PostgreSQL and BLOBs are comfortable and easy to use. Functions are available for all important operations such as importing, exporting, creating a new object, or writing data into an object. As we have already seen, SQL offers counterparts to the functions provided by PHP. However, in this section you will learn to use PHP's onboard functions.

The next example shows how a file is imported into a database and deleted again:

 <?php         $dbh = pg_connect("host=localhost user=postgres dbname=phpbook");         if      (!$dbh) { echo "error while connecting.<br>\n"; }         pg_exec($dbh, "BEGIN");         $oid = pg_loimport('/etc/passwd', $dbh);         if      (!$oid)         {                 echo "an error has occurred while importing<br>\n";                 exit;         }         echo "Object id: $oid<br>\n";         $status = pg_lounlink($dbh, $oid);         if      (!$status)         {                 echo "the file could not be deleted<br>\n";         }         pg_exec($dbh, "COMMIT"); ?> 

PHP's functions for dealing with BLOBs can only be used inside a transaction. Therefore, a transaction is started before the file is inserted into the database by using pg_loimport. Just pass the name of the file you want to import and the connection handle to the function; if you are inside a transaction, the operation will succeed unless there is an error with user rights or things like that.

pg_loimport returns the object id of the new object, which is displayed on the screen using an echo command. Finally, the new object is deleted:

 Object id: 44322 

When working with multimedia data, it is often necessary to send data in a file stored in a PostgreSQL database directly to the browser. Therefore PHP provides a command called pg_loreadall. To use this function, the file must be opened first using pg_loopen:

 <?php         $dbh = pg_connect("host=localhost user=postgres dbname=phpbook");         if      (!$dbh) { echo "error while connecting.<br>\n"; }         pg_exec($dbh, "BEGIN");         $oid = pg_loimport('/etc/passwd', $dbh);         if      (!$oid)         {                 echo "an error has occurred while importing<br>\n";                 exit;         }         $file = pg_loopen($dbh, $oid, "rw");         pg_loreadall($file);         $status = pg_lounlink($dbh, $oid);         if      (!$status)         {                 echo "the file could not be deleted<br>\n";         }         pg_exec($dbh, "COMMIT"); ?> 

pg_loreadall returns void and does not return the data as a variable the content of the BLOB is sent to the browser directly. Figure 12.1 shows what the result will look like.

Figure 12.1. The /etc/passwd file.

graphics/12fig01.jpg

You can import and export files, but that's not all you can do. In many situations, the BLOB must be modified. Therefore, PHP and PostgreSQL provide functions that are similar to C functions used for interacting with files. To create an empty object, check out pg_locreate. To write data to the BLOB, pg_lowrite must be used. The next example shows how the word SUSI is added to an empty BLOB:

 <?php         $dbh = pg_connect("host=localhost user=postgres dbname=phpbook");         if      (!$dbh) { echo "error while connecting.<br>\n"; }         pg_exec($dbh, "BEGIN");         $oid = pg_locreate($dbh);         if      (!$oid)         {                 echo "an error has occurred while creating object<br>\n";                 exit;         }         echo "object id: $oid<br>\n";         $file = pg_loopen($dbh, $oid, "rw");         pg_lowrite($file, "SUSI");         pg_loclose($file);         pg_exec($dbh, "COMMIT"); ?> 

Let's see what data can be found in the file. The BLOB must be exported to a file:

 phpbook=# SELECT lo_export(44336, '/tmp/test_lowrite.txt');  lo_export -----------          1 (1 row) 

cat lists the content of the file on the screen:

 [postgres@athlon html]$ cat /tmp/test_lowrite.txt; echo \ SUSI 

SUSI has been added to the BLOB and the file has been closed correctly.

To retrieve the data from the BLOB, it is also possible to write a short PHP script:

 <?php         $dbh = pg_connect("host=localhost user=postgres dbname=phpbook");         if      (!$dbh) { echo "error while connecting.<br>\n"; }         pg_exec($dbh, "BEGIN");         $file = pg_loopen($dbh, 44336, "r");         $data = pg_loread($file, 4);         echo "data: $data<br>\n";         pg_loclose($file);         pg_exec($dbh, "COMMIT"); ?> 

First the BLOB is opened and four bytes of data are retrieved from the BLOB using pg_loread. Then this data is displayed on the screen:

 data: SUSI 

As you can see, the BLOB has been processed successfully.



PHP and PostgreSQL. Advanced Web Programming2002
PHP and PostgreSQL. Advanced Web Programming2002
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 201

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net