12 Uploading and Displaying BLOBs
This chapter contains the following topics:
Creating a Table to Store the Logo
The PHP OCI8 extension easily allows LOB data to be manipulated. A BLOB will be used in the AnyCo application to store a company logo which will be displayed on each web page.
In SQL*Plus create a table PICTURES to store the logo:
CREATE TABLE pictures (id NUMBER, pic BLOB);
CREATE SEQUENCE pictures_seq;
CREATE TRIGGER pictures_trig BEFORE INSERT ON pictures FOR EACH ROW
BEGIN
:NEW.id := pictures_seq.NEXTVAL;
END;
/
Uploading Images in PHP OCI8
Create a new PHP file ac_logo_upload.php. The initial contents are:
<?php
/**
* ac_logo_upload.php: Upload a new company logo
* @package Logo
*/
session_start();
require('ac_db.inc.php');
require('ac_equip.inc.php');
$sess = new \Equipment\Session;
$sess->getSession();
if (!isset($sess->username) || empty($sess->username)
|| !$sess->isPrivilegedUser()) {
header('Location: index.php');
exit;
}
$page = new \Equipment\Page;
$page->printHeader("AnyCo Corp. Upload Logo");
$page->printMenu($sess->username, $sess->isPrivilegedUser());
printcontent($sess);
$page->printFooter();
// Functions
?>
Add the printcontent() function:
/**
* Print the main body of the page
*
* @param Session $sess
*/
function printcontent($sess) {
echo "<div id='content'>";
if (!isset($_FILES['lob_upload'])) {
printform();
} else {
$blobdata = file_get_contents($_FILES['lob_upload']['tmp_name']);
if (!$blobdata) {
// N.b. this test could be enhanced to confirm the image is a JPEG
printform();
} else {
$db = new \Oracle\Db("Equipment", $sess->username);
$sql = 'INSERT INTO pictures (pic)
VALUES(EMPTY_BLOB()) RETURNING pic INTO :blobbind';
$db->insertBlob($sql, 'Insert Logo BLOB', 'blobbind', $blobdata);
echo '<p>New logo was uploaded</p>';
}
}
echo "</div>"; // content
}
This is in the now familiar two part structure with an HTML form and a form-handler. The INSERT statement uses a bind value to represent the BLOB. The new Db class insertBlob() will associate the BLOB data with the bind variable and commit the record. The uploaded image will be added to the PICTURES table.
Complete ac_logo_upload.php by adding the form function printform():
/**
* Print the HTML form to upload the image
*
* Adding CSRF protection is an exercise for the reader
*/
function printform() {
echo <<<EOF
Upload new company logo:
<form action="ac_logo_upload.php" method="POST" enctype="multipart/form-data">
<div>
Image file name: <input type="file" name="lob_upload">
<input type="submit" value="Upload"
</div>
<form
EOF;
}
Note:
The 'EOF;' token must be at the start of a line and not have trailing white space.When this form is submitted the PHP web server will be able to access uploaded BLOB data in the temporary file $_FILES['lob_upload']['tmp_name'], as seen in printcontent().
PHP has various options controlling locations and upper sizes of files, refer to the PHP documentation. The AnyCo application will use the default values.
Edit ac_db.inc.php and add the insertBlob() method to the Db class:
/**
* Insert a BLOB
*
* $sql = 'INSERT INTO BTAB (BLOBID, BLOBDATA)
* VALUES(:MYBLOBID, EMPTY_BLOB()) RETURNING BLOBDATA INTO :BLOBDATA'
* Db::insertblob($sql, 'do insert for X', myblobid',
* $blobdata, array(array(":p", $p, -1)));
*
* $sql = 'UPDATE MYBTAB SET blobdata = EMPTY_BLOB()
* RETURNING blobdata INTO :blobdata'
* Db::insertblob($sql, 'do insert for X', 'blobdata', $blobdata);
*
* @param string $sql An INSERT or UPDATE statement that returns a LOB locator
* @param string $action Action text for End-to-End Application Tracing
* @param string $blobbindname Bind variable name of the BLOB in the statement
* @param string $blob BLOB data to be inserted
* @param array $otherbindvars Bind variables. An array of tuples
*/
public function insertBlob($sql, $action, $blobbindname, $blob,
$otherbindvars = array()) {
$this->stid = oci_parse($this->conn, $sql);
$dlob = oci_new_descriptor($this->conn, OCI_D_LOB);
oci_bind_by_name($this->stid, $blobbindname, $dlob, -1, OCI_B_BLOB);
foreach ($otherbindvars as $bv) {
// oci_bind_by_name(resource, bv_name, php_variable, length)
oci_bind_by_name($this->stid, $bv[0], $bv[1], $bv[2]);
}
oci_set_action($this->conn, $action);
oci_execute($this->stid, OCI_NO_AUTO_COMMIT);
if ($dlob->save($blob)) {
oci_commit($this->conn);
}
}
The insertBlob() method accepts a final option parameter for normal bind variables. This is not used when it is called in printcontent() in ac_logo_upload.php.
The BLOB is bound as a special type, similar to how a REF CURSOR was bound in the Chapter 6, "Showing Equipment Records by Using a REF CURSOR." PHP OCI8 also has a OCI_B_CLOB constant which can be used for binding CLOBs. The LOB descriptor is an instance of PHP OCI8's OCI-Lob class, which has various methods for uploading and reading data. When oci_execute() is processed on the SQL INSERT statement the OCI_NO_AUTO_COMMIT flag is used. This is because the database transaction must remain open until the $dlob->save() method inserts the data. Finally, an explicit oci_commit() commits the BLOB.
Run the AnyCo application in a browser and log in Administrator. Click the Upload Logo link in the left hand menu. Locate a JPEG image on your computer and select it. The next section of this chapter will display the image in the page header with the title, so choose an image of 15 to 20 pixels in height.
Click the Upload button.
Fetching the Logo and Creating an Image
Displaying the logo is similar in concept to how the graph image was displayed in the previous chapter. However since the BLOB is already in JPEG format the GD extension is not required.
Create a new PHP file ac_logo_img.php. The file contains:
<?php
/**
* ac_logo_img.php: Create a JPEG image of the company logo
*
* Do not put any text or white space before the "<?php" tag because it will
* be incorporated into the image stream and corrupt the picture.
*
* @package Logo
*/
session_start();
require('ac_db.inc.php');
require('ac_equip.inc.php');
$sess = new \Equipment\Session;
$sess->getSession();
if (isset($sess->username) && !empty($sess->username)) {
$username = $sess->username;
} else { // index.php during normal execution, or other external caller
$username = "unknown-logo";
}
$db = new \Oracle\Db("Equipment", $username);
$sql = 'SELECT pic FROM pictures WHERE id = (SELECT MAX(id) FROM pictures)';
$img = $db->fetchOneLob($sql, "Get Logo", "pic");
header("Content-type: image/jpg");
echo $img;
?>
This queries the most recent logo and sends it back as a JPEG stream. If the image appears corrupted, comment out the header() and echo function calls and check if any text or white space is being emitted by the script.
The user name check differs from those used in previous sections. The logo is displayed on all pages including the login page before the web user name is known. Because Db accepts a user name for end-to-end tracing, ac_logo_img.php uses a bootstrap user name unknown-logo.
Edit ac_db.inc.php and add the fetchOneLob() method to the Db class:
/**
* Runs a query that fetches a LOB column
* @param string $sql A query that include a LOB column in the select list
* @param string $action Action text for End-to-End Application Tracing
* @param string $lobcolname The column name of the LOB in the query
* @param array $bindvars Bind variables. An array of tuples
* @return string The LOB data
*/
public function fetchOneLob($sql, $action, $lobcolname, $bindvars = array()) {
$col = strtoupper($lobcolname);
$this->stid = oci_parse($this->conn, $sql);
foreach ($bindvars as $bv) {
// oci_bind_by_name(resource, bv_name, php_variable, length)
oci_bind_by_name($this->stid, $bv[0], $bv[1], $bv[2]);
}
oci_set_action($this->conn, $action);
oci_execute($this->stid);
$row = oci_fetch_array($this->stid, OCI_RETURN_NULLS);
$lob = null;
if (is_object($row[$col])) {
$lob = $row[$col]->load();
$row[$col]->free();
}
$this->stid = null;
return($lob);
}
The oci_fetch_array() options could have included the OCI_RETURN_LOBS flag to indicate the data should be returned as a PHP string. The code here shows the column being returned as a locator instead. This shows how a locator can be operated on, here using the load() to read all the data and free() method to free up resources. If you had an application with very large data, the locator read() method could be used to process the LOB in chunks, which would be a memory efficient way of processing large data streams.
Unlike insertBlob() which bound using the OCI_B_BLOB type and was therefore specific for BLOBs, the fetchOneLob() can be used for both BLOB and CLOB data.
If an application processes multiple images (or chunks of an image) sequentially in a loop, for example:
while (($img = $db->fetchOneLob($sql, "Get Logo", "pic")) != null ) {
dosomething($img);
}
then you can reduce PHP's peak memory usage by explicitly un-setting $img at the foot of the loop:
dosomething($img);
$unset($img);
This allows the memory allocated for the current $img to be reused for the next image data stream. Otherwise the original image memory is only freed after PHP constructs the second image and is ready to assign it to $img. This optimization is not needed by the AnyCo application.
Displaying the Logo
To display an uploaded logo in the AnyCo application, edit ac_equip.inc.php and un-comment the LOGO_URL definition:
define('LOGO_URL', 'http://localhost/ac_logo_img.php');
Make sure the URL is correct for your environment.
The logo is displayed in Page::printHeader(). Every standard page of the application will show the logo. Rerun the application to verify this:
Keeping images in the database allows the complete application data to be backed up and shared across all applications. However for performance you could consider implementing a caching technique that writes the logo to disk so it can be streamed directly without requiring the overhead of database access. The upload form could regenerate the disk file each time a new image is uploaded.