Oracle and PHP
This page demonstrates how to upload, store, and manage CLOBs (Character Large OBjects) and BLOBs (Binary Large OBjects). It provides some instructions that supplement Chapter 8 in my Oracle Database 11g PL/SQL Programming book.
Before you begin these steps, you should have already installed Zend Server Community Edition. If you haven’t done so, please click here for instructions.
Create directories or folders, and position code ↓
This section provides you with instructions on how to position the code components in Windows, at least for the newbie. If you’re on Linux, you probably know how to do most if not all of this already. Likewise, if you already know how to put things in the right place, please choose your own locations.
- Create a
LOB
directory for the PHP files inside thehtdocs
directory.
- You can down the PHP Upload LOB Web Code zip file and unzip it into the directory you just created.
Load an Oracle CLOB column to the Oracle database ↓
This is a copy of the three files required to load a large string to an Oracle database into a CLOB
data type. The code is in clear text because somebody asked for it. They’re nervous about zip files. Click the title above to expand all the code text.
Credentials.inc
If you’re using Oracle Database 10g Express Edition, you should use the following in your Credentials.inc
file:
1 2 3 4 5 6 | <?php // Connection variables. define('SCHEMA',"student"); define('PASSWD',"student"); define('TNS_ID',"localhost/xe"); ?> |
If you’re using Oracle Database 11g, you should use the following in your Credentials.inc
file:
1 2 3 4 5 6 | <?php // Connection variables. define('SCHEMA',"student"); define('PASSWD',"student"); define('TNS_ID',"localhost/orcl"); ?> |
UploadItemDescriptionForm.htm
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | <html> <! UploadItemDescriptionForm.htm > <! Date: 05-Jul-2009 > <! Author: Michael McLaughlin > <! > <! This script demonstrates an HTML FORM used to upload files > <! local files to the UploadItemDesc.php server-side program. > <head> <title> Chapter 8 : UploadItemDescriptionForm.htm </title> </head> <body> <form id="uploadForm" action="UploadItemDescription.php" enctype="multipart/form-data" method="post"> <table border=0 cellpadding=0 cellspacing=0> <tr> <td width=125>Item ID</td> <td> <input id="id" name="id" type="text"> </td> </tr> <tr> <td width=125>Item Title</td> <td> <input id="title" name="title" type="text"> </td> </tr> <tr> <td width=125>Select File</td> <td> <input id="uploadfilename" name="userfile" type="file"> </td> </tr> <tr> <td width=125>Click Button to</td> <td><input type="submit" value="Upload File"></td> </tr> </table> </form> </body> </html> |
UploadItemDescription.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 | <?php // Set database credentials. include_once("Credentials.inc"); // Displayed moved file in web page. $item_desc = process_uploaded_file(); // Return successful attempt to connect to the database. if ($c = @oci_connect(SCHEMA,PASSWD,TNS_ID)) { // Declare input variables. (isset($_POST['id'])) ? $id = (int) $_POST['id'] : $id = 1021; (isset($_POST['title'])) ? $title = $_POST['title'] : $title = "Harry #1"; // Declare a PL/SQL execution command. $stmt = "BEGIN web_load_clob_from_file(:id,:item_desc); END;"; // Strip special characters to avoid ORA-06550 and PLS-00103 errors. $stmt = strip_special_characters($stmt); // Parse a query through the connection. $s = oci_parse($c,$stmt); // Define a descriptor for a CLOB. $rlob = oci_new_descriptor($c,OCI_D_LOB); // Define a variable name to map to CLOB descriptor. oci_define_by_name($s,':item_desc',$rlob,SQLT_CLOB); // Bind PHP variables to the OCI types. oci_bind_by_name($s,':id',$id); oci_bind_by_name($s,':item_desc',$rlob,-1,SQLT_CLOB); // Execute the PL/SQL statement. if (oci_execute($s,OCI_DEFAULT)) { $rlob->save($item_desc); oci_commit($c); query_insert($id,$title); } // Release statement resources. oci_free_statement($s); // Disconnect from database. oci_close($c); } else { // Assign the OCI error and format double and single quotes. $errorMessage = oci_error(); print htmlentities($errorMessage['message'])."<br />"; die(); } // Query results afret an insert. function query_insert($id,$title) { // Return successful attempt to connect to the database. if ($c = @oci_new_connect(SCHEMA,PASSWD,TNS_ID)) { // Declare a SQL SELECT statement returning a CLOB. $stmt = "SELECT item_desc FROM item WHERE item_id = :id"; // Parse a query through the connection. $s = oci_parse($c,$stmt); // Bind PHP variables to the OCI types. oci_bind_by_name($s,':id',$id); // Execute the PL/SQL statement. if (oci_execute($s)) { // Return a LOB descriptor as the value. while (oci_fetch($s)) { for ($i = 1;$i <= oci_num_fields($s);$i++) if (is_object(oci_result($s,$i))) { if ($size = oci_result($s,$i)->size()) $data = oci_result($s,$i)->read($size); else $data = " "; } else { if (oci_field_is_null($s,$i)) $data = " "; else $data = oci_result($s,$i); } } // End of the while(oci_fetch($s)) loop. // Format HTML table to display biography. $out = '<table border="1" cellpadding="3" cellspacing="0">'; $out .= '<tr>'; $out .= '<td align="center" class="e">'.$title.'</td>'; $out .= '</tr>'; $out .= '<tr>'; $out .= '<td class="v">'.$data.'</td>'; $out .= '</tr>'; $out .= '</table>'; } // Print the HTML table. print $out; // Release statement resources. oci_free_statement($s); // Disconnect from database. oci_close($c); } else { // Assign the OCI error and format double and single quotes. $errorMessage = oci_error(); print htmlentities($errorMessage['message'])."<br />"; } } // Manage file upload and return file as string. function process_uploaded_file() { // Declare a variable for file contents. $contents = ""; // Define the upload file name for Windows or Linux. if (preg_match(".Win32.",$_SERVER["SERVER_SOFTWARE"])) $upload_file = getcwd()."\\temp\\".$_FILES['userfile']['name']; else $upload_file = getcwd()."/temp/".$_FILES['userfile']['name']; // Check for and move uploaded file. if (is_uploaded_file($_FILES['userfile']['tmp_name'])) move_uploaded_file($_FILES['userfile']['tmp_name'],$upload_file); // Open a file handle and suppress an error for a missing file. if ($fp = @fopen($upload_file,"r")) { // Read until the end-of-file marker. while (!feof($fp)) $contents .= fgetc($fp); // Close an open file handle. fclose($fp); } // Return file content as string. return $contents; } // Strip special characters, like carriage or line returns and tabs. function strip_special_characters($str) { $out = ""; for ($i = 0;$i < strlen($str);$i++) if ((ord($str[$i]) != 9) && (ord($str[$i]) != 10) && (ord($str[$i]) != 13)) $out .= $str[$i]; // Return pre-parsed SQL statement. return $out; } ?> |
Load a BLOB column to the Oracle database ↓
This is a copy of the four files required to load a large image to an Oracle database with a BLOB
data type. The fourth file reads the binary image and translates it into an HTML header and image that can be read through a call to the src
attribute of an img
tag. You can find the call to the forth file in the UploadItemBlob.php
.
The code is in clear text because somebody asked for it. They’re nervous about zip files. Click the title above to expand all the code text.
Credentials.inc
As discussed with large strings, you should use the following in your Credentials.inc
file. If you deploy these in same directory as where you tested the code for large text files, there’s no need to update the copy in the current directory.
1 2 3 4 5 6 | <?php // Connection variables. define('SCHEMA',"student"); define('PASSWD',"student"); define('TNS_ID',"localhost/xe"); ?> |
If you’re using Oracle Database 11g, you should use the following in your Credentials.inc
file:
1 2 3 4 5 6 | <?php // Connection variables. define('SCHEMA',"student"); define('PASSWD',"student"); define('TNS_ID',"localhost/orcl"); ?> |
UploadItemBlobForm.htm
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | <html> <! Program: UploadItemBlobForm.htm > <! Date: 05-Jul-2009 > <! Author: Michael McLaughlin > <! > <! This script demonstrates an HTML FORM used to upload > <! local files to the UploadItemBlob.php server-side program, > <! and store the file in a BLOB column. > <head> <title> UploadItemBlobForm.htm </title> </head> <body> <form id="uploadForm" action="UploadItemBlob.php" enctype="multipart/form-data" method="post"> <table border=0 cellpadding=0 cellspacing=0> <tr> <td width=125>Item Number</td> <td> <input id="id" name="id" type="text"> </td> </tr> <tr> <td width=125>Item Title</td> <td> <input id="title" name="title" type="text"> </td> </tr> <tr> <td width=125>Select File</td> <td> <input id="uploadfilename" name="userfile" type="file"> </td> </tr> <tr> <td width=125>Click Button to</td> <td><input type="submit" value="Upload File"></td> </tr> </table> </form> </body> </html> |
UploadItemBlob.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 | <?php // Set database credentials. include_once("Credentials.inc"); // Displayed moved file in web page. $item_blob = process_uploaded_file(); // Return successful attempt to connect to the database. if ($c = @oci_connect(SCHEMA,PASSWD,TNS_ID)) { // Declare input variables. (isset($_POST['id'])) ? $id = (int) $_POST['id'] : $id = 1021; (isset($_POST['title'])) ? $title = $_POST['title'] : $title = "Harry #1"; // Declare a PL/SQL execution command. $stmt = "BEGIN web_load_blob_from_file(:id,:item_blob); END;"; // Strip special characters to avoid ORA-06550 and PLS-00103 errors. $stmt = strip_special_characters($stmt); // Parse a query through the connection. $s = oci_parse($c,$stmt); // Define a descriptor for a BLOB. $rlob = oci_new_descriptor($c,OCI_D_LOB); // Define a variable name to map to CLOB descriptor. oci_define_by_name($s,':item_blob',$rlob,SQLT_BLOB); // Bind PHP variables to the OCI types. oci_bind_by_name($s,':id',$id); oci_bind_by_name($s,':item_blob',$rlob,-1,SQLT_BLOB); // Execute the PL/SQL statement. if (oci_execute($s,OCI_DEFAULT)) { $rlob->save($item_blob); oci_commit($c); oci_free_statement($s); query_insert($id,$title); } // Release statement resources. oci_free_statement($s); // Disconnect from database. oci_close($c); } else { // Assign the OCI error and format double and single quotes. $errorMessage = oci_error(); print htmlentities($errorMessage['message'])."<br />"; } // Query results afret an insert. function query_insert($id,$title) { // Return successful attempt to connect to the database. if ($c = @oci_new_connect(SCHEMA,PASSWD,TNS_ID)) { // Declare a SQL SELECT statement returning a CLOB. $stmt = "SELECT item_desc FROM item WHERE item_id = :id"; // Parse a query through the connection. $s = oci_parse($c,$stmt); // Bind PHP variables to the OCI types. oci_bind_by_name($s,':id',$id); // Execute the PL/SQL statement. if (oci_execute($s)) { // Return a LOB descriptor as the value. while (oci_fetch($s)) { for ($i = 1;$i <= oci_num_fields($s);$i++) if (is_object(oci_result($s,$i))) { if ($size = oci_result($s,$i)->size()) { $data = oci_result($s,$i)->read($size); } else $data = " "; } else { if (oci_field_is_null($s,$i)) $data = " "; else $data = oci_result($s,$i); } } // End of the while(oci_fetch($s)) loop. // Format HTML table to display BLOB photo and CLOB description. $out = '<table border="1" cellpadding="5" cellspacing="0">'; $out .= '<tr>'; $out .= '<td align="center" class="e">'.$title.'</td>'; $out .= '</tr>'; $out .= '<tr><td class="v">'; $out .= '<div>'; $out .= '<div style="margin-right:5px;float:left">'; $out .= '<img src="ConvertBlobToImage.php?id='.$id.'">'; $out .= '</div>'; $out .= '<div style="position=relative;">'.$data.'</div>'; $out .= '</div>'; $out .= '</td></tr>'; $out .= '</table>'; } // Print the HTML table. print $out; // Release statement resources. oci_free_statement($s); // Disconnect from database. oci_close($c); } else { // Assign the OCI error and format double and single quotes. $errorMessage = oci_error(); print htmlentities($errorMessage['message'])."<br />"; } } // Manage file upload and return file as string. function process_uploaded_file() { // Declare a variable for file contents. $contents = ""; // Define the upload file name for Windows or Linux. if (preg_match(".Win32.",$_SERVER["SERVER_SOFTWARE"])) $upload_file = getcwd()."\\temp\\".$_FILES['userfile']['name']; else $upload_file = getcwd()."/temp/".$_FILES['userfile']['name']; // Check for and move uploaded file. if (is_uploaded_file($_FILES['userfile']['tmp_name'])) move_uploaded_file($_FILES['userfile']['tmp_name'],$upload_file); // Open a file handle and suppress an error for a missing file. if ($fp = @fopen($upload_file,"r")) { // Read until the end-of-file marker. while (!feof($fp)) $contents .= fgetc($fp); // Close an open file handle. fclose($fp); } // Return file content as string. return $contents; } // Strip special characters, like carriage or line returns and tabs. function strip_special_characters($str) { $out = ""; for ($i = 0;$i < strlen($str);$i++) if ((ord($str[$i]) != 9) && (ord($str[$i]) != 10) && (ord($str[$i]) != 13)) $out .= $str[$i]; // Return pre-parsed SQL statement. return $out; } ?> |
ConvertBlobToImage.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | <?php // Database credentials must be set manually because an include_once() function // call puts something ahead of the header, which causes a failure when rendering // an image. // Return successful attempt to connect to the database. if ($c = @oci_new_connect("student","student","localhost/orcl")) { // Declare input variables. (isset($_GET['id'])) ? $id = (int) $_GET['id'] : $id = 1023; // Declare a SQL SELECT statement returning a CLOB. $stmt = "SELECT item_blob FROM item WHERE item_id = :id"; // Parse a query through the connection. $s = oci_parse($c,$stmt); // Bind PHP variables to the OCI types. oci_bind_by_name($s,':id',$id); // Execute the PL/SQL statement. if (oci_execute($s)) { // Return a LOB descriptor and free resource as the value. while (oci_fetch($s)) { for ($i = 1;$i <= oci_num_fields($s);$i++) if (is_object(oci_result($s,$i))) { if ($size = oci_result($s,$i)->size()) { $data = oci_result($s,$i)->read($size); } else $data = " "; } else { if (oci_field_is_null($s,$i)) $data = " "; else $data = oci_result($s,$i); } } // End of the while(oci_fetch($s)) loop. // Free statement resources. oci_free_statement($s); // Print the header first. header('Content-type: image/x-png'); imagepng(imagecreatefromstring($data)); } // Disconnect from database. oci_close($c); } else { // Assign the OCI error and format double and single quotes. $errorMessage = oci_error(); print htmlentities($errorMessage['message'])."<br />"; } ?> |
- Create a
temp
directory for the upload target location, as qualified in the PHP code. The PHP code works regardless of whether you’re on Windows or Linux, but it does depend on the creation of this directory.
- Create a directory or folder for the large file source directories. This directory is probably on your test machine (laptop) but it mimics a client laptop and would work if your server was on a different machine.
- Inside the Upload directory, you should create the following two directories:
- You should download the CLOB Text File zip file and unzip it into the
textfiles
directory; then download the BLOB Image File zip file and unzip it into the imagefiles
directory.- The last directory or folder that you’ll need to create is for a couple stored procedures. These procedures let you pass a reference to the Oracle database server, which then lets you upload both CLOB and BLOB files.
- After creating the directory, you can download the Oracle LOB Processing Stored Procedure files and unzip them into the directory.
Assuming you’ve downloaded the zip files and extracted them into the correct locations, this section is done.
Prepare the Oracle database ↓
This section provides you with instructions on how to ensure everything will work once the PHP programs call the database. Even if you have one of my sample Video Store databases, you should verify and add appropriate columns. This post assumes you’ve downloaded the code for the Oracle Database 11g PL/SQL Programming book, but it’ll work with the code form the Oracle Database 10g Express Edition PHP Web Programming book too.
- Navigate to the directory where you extracted the PL/SQL Stored Procedures, which according to blog page is:
C:\Data\SQL
. In that directory, connect as thestudent
user, or whichever account you’re using. You should confirm that you have aitem_desc
column ofCLOB
data type, and anitem_blob
column ofBLOB
type in theitem
table. If you don’t have those columns, you can add them with the following statement:
ALTER TABLE item ADD (item_desc CLOB) ADD (item_blob BLOB); |
- After ensuring that you have those two columns, create the two stored procedures, like
SQL> @C:\DATA\SQL\create_web_clob_loading.sql SQL> @C:\DATA\SQL\create_web_blob_loading.sql |
Test the Configuration ↓
This section shows you how to test all that you’ve done. It works provided you created the directories and extracted the zip file contents to their respective directories. The virtual URL actually maps to the C:\Program Files\Zend\Apache2\htdocs\lob
directory.
- Enter the
http://localhost/lob/UploadItemDescriptionForm.htm
URL, and complete the form by choosing a validitem_id
column value and text file from yourC:\Data\Upload\TextFiles
directory. Then, click the Upload File button (you can see a larger version of the image by clicking on it).
- This page displays after you successfully upload the text file to the database.
- Enter the
http://localhost/lob/UploadItemBLOBForm.htm
URL, and complete the form by choosing a validitem_id
column value and image file from yourC:\Data\Upload\ImageFiles
directory. Then, click the Upload File button (you can see a larger version of the image by clicking on it).
- This page displays after you successfully upload the image file to the database.
Troubleshooting the Configuration ↓
This section shows you how to check why something isn’t working.
- The first thing to check are the credentials. They’re in the
credentials.inc
file. They’re posted with astudent
user,student
password, andXE
TNS alias.
- Not to be funny, but the second thing to check are credentials. Specifically, you need to check the credentials in the
ConvertBlobToImage.php
file. They’re individually entered in the connect string of this file because otherwise they put something in front of the header, which is disallowed to render the image.
- Check to see if the text or image file made it to the
C:\Program Files\Zend\Apache2\htdocs\lob\temp
directory. If they made it that far but no further, check to see if you have valid procedures in thestudent
schema.
- If the stored procedures are invalid, verify that you have the required
item_desc
anditem_blob
columns in theitem
table.
- Check whether the CLOB or BLOB are loaded into the database. You use the
GETLENGTH
function from theDBMS_LOB
package, like this:
SELECT i.item_id , dbms_lob.getlength(i.item_desc) , dbms_lob.getlength(i.item_blob) FROM item i WHERE i.item_desc IS NOT NULL OR i.item_blob IS NOT NULL; |
- Check if the
item_id
value is found in the list of values.
- If you’re stumped, add a comment and explain what’s up.
If you find any problems, please let me know. I’ll be happy to fix them.
Thanks for the troubleshooting section! This helped a lot!
Jared
25 Feb 10 at 12:03 pm
Is it just me or does the Zend Community edition not come with Zend Core? My installation did not include it; is there a link for it somewhere that I missed?
Sam Johansen
29 Mar 10 at 2:57 pm
The “Zend Server Community Edition” looks really different from the pictures shown under “Configure the Zend Engine”
We had to click Server setup and then Extensions to check to make sure the image settings were set.
Clay Mullins
29 Mar 10 at 9:35 pm
Yes, unfortunately the Zend Community Server continues to evolve. They screen shots give you the general flow.
michaelmclaughlin
29 Mar 10 at 9:45 pm
That should have been removed. It is removed now.
michaelmclaughlin
29 Mar 10 at 9:47 pm
Agreed with Jared
Andrew
30 Mar 10 at 9:28 am
The text is uploading fine, and the database query is returning the picture id, but the picture itself isn’t displaying in the browser with the text. It has a broken image icon.
Dane Christiansen
30 Mar 10 at 11:45 am
Have you confirmed that the image is written to the database row that you’re querying?
michaelmclaughlin
30 Mar 10 at 12:01 pm
I am getting this error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
I have tried all the troubleshooting steps above. No luck. Any Ideas?
Kyle Redfearn
31 Mar 11 at 1:40 pm
Is your database started?
michaelmclaughlin
2 May 11 at 10:57 am
I got the same error that Kyle did last semester. The one which says: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor. My database was started and I used the troubleshooting steps but they didn’t work. Then I realized that it might have been a problem with one of the zip files.
I went into the Credentials.inc file and realized that by default it is setup for those using the Oracle 11g Database and I am running the Oracle 10g Database. So I just changed the “define” end bit under the connection variables from …”localhost/orcl” to …”localhost/xe” and now everything works just fine!
Victoria Cole
7 Dec 11 at 1:04 pm
I’m having the same issue that Kyle had. From what I’ve seen so far, it has something to do with not being able to use port 80. I had to change my server to use port 81 to even have the webpage appear. However, when you click upload file, it thinks for a really long time, and then the error listed above by Kyle appears. I tried to turn my firewall off and and change my server to use port 80, but the webpage won’t appear, so I know that its not my firewall causing the problem. Any suggestions Brother McLaughlin?
Jonathan Kirkham
31 Mar 12 at 2:27 pm
Enter the URL like this:
http://localhost:81/file_name.php
michaelmclaughlin
5 May 12 at 12:19 am