Manipulating Oracle's LOB type data in PHP

oracle| data, Php+oracle (OCI), discusses how to use the OCI function in PHP to connect to an Oracle database, execute some SQL queries, and close the database connection. This article will tell you another slightly more difficult question: using the PHP OCI function to manipulate Oracle's LOB fields. Reading this article requires the knowledge of the Php+oracle (OCI) preliminary.

As anyone who has ever used Oracle knows, Oracle has a data type called VARCHAR2, which is used to indicate an indefinite length of string. VARCHAR2 is also the type that Oracle companies recommend to use. But there is a problem with VARCHAR2: The maximum can only represent 4,000 characters, and it is equivalent to 2000 characters. If the value of a character in your program is greater than 20,002 characters, you cannot satisfy the requirement with VARCHAR2. At this point, you have two choices, one is represented by multiple VARCHAR2, and the other is in LOB fields. Here we take a look at the second option.

Let's get a rough look at Oracle's LOB fields. Oracle's LOB types are divided into three categories: Blob,clob and bfile. Clob is called character Lob,blob and bfile is used to store binary data. The maximum length of the CLOB and BLOBs is 4GB, which holds the values in the Oracle database. Bfile is similar to a BLOB, but it places the data in an external file, so it is also known as an external blob (External blob).
I think we should be familiar with MySQL. MySQL also has similar data types, such as text and blobs. In PHP's MySQL function, the operation of the Text/blob is straightforward, just like other types of data. But in Oracle, things are different. Oracle treats LOB as a special data type and cannot operate in a conventional way. For example, you cannot insert a value directly into a LOB field in an INSERT statement, nor can you find it with like.

Here are a few examples to illustrate how to insert, retrieve, and query LOB data using PHP's OCI function.

Insert

You cannot insert a value directly into a LOB field with an INSERT statement. In general, like the following steps:
1 First parse an INSERT statement and return a LOB descriptor
2 generating a local LOB object with the OCI function
3 Bind the LOB object to the LOB descriptor
4 Executing INSERT statement
5 assigning values to LOB objects
6 releasing LOB objects and SQL statement handles

The following example puts a picture file uploaded by a user into a blob (or bfile) with a slightly different operation.
The first step is to build a table with the following structure:
CREATE TABLE PICTURES (
ID number,
DESCRIPTION VARCHAR2 (100),
MIME VARCHAR2 (128),
Picture BLOB
);
If you want to implement an automatic increase in IDs, build another sequence:
CREATE SEQUENCE Pic_seq;

Then it is the PHP program code used to process the data.
<?php

Establishing an Oracle database connection
$conn = Ocilogon ($user, $password, $SID);
Submitting SQL statements to Oracle
Here are two points to note: one is to use the Empty_blob () function. This is an Oracle internal function that returns a LOB's locator. When you insert a lob, you can only use this method to make an empty LOB locator, and then manipulate the locator character. The Empty_blob () function is for a BLOB type, and corresponds to the CLOB of Empty_clob (). The second part is the returning, the picture back, so that the PHP OCI function can be processed.
$stmt = Ociparse ($conn, INSERT into PICTURES (ID, description, picture)
VALUES (Pic_seq. Nextval, ' $description ', ' $lob _upload_type ', Empty_blob ()) returning picture into:P icture ");
Generates a descriptor for a local LOB object. Note the second parameter of the function: Oci_d_lob, which represents the generation of a LOB object. Other possible oci_d_file and Oci_d_rowid, respectively, correspond to the bfile and ROWID objects.
$lob = Ocinewdescriptor ($conn, Oci_d_lob);
Binds the generated LOB object to the locator character returned by the preceding SQL statement.
Ocibindbyname ($stmt, ':P icture ', & $lob,-1, Oci_b_blob);
Ociexecute ($stmt);
To deposit data into a lob object. Because the source data here is a file, the SaveFile () method of the LOB object is used directly. Other methods of LOB objects are: Save () and load (), which are used to save and retrieve data, respectively. But only one way to bfile type is save ()
if ($lob->savefile ($lob _upload)) {
Ocicommit ($conn);
echo "Upload success <br>";
}else{
echo "Upload failure <br>";
}
Releasing LOB objects
Ocifreedesc ($LOB);
Ocifreestatement ($stmt);
Ocilogoff ($conn);

?>

Another area to note: LOB fields have a minimum value of 1 characters, so before save () or SaveFile (), make sure the value cannot be empty. Otherwise, Oracle will make an error.

Remove

There are two ways to get data out of a lob. One is to generate a LOB object, then bind to the locator character returned by a SELECT statement, and then use the LOB object's load () method to fetch the data; the second is to use PHP's ocifetch*** function directly. The first method is much more troublesome than the second one, so I'll just talk about the second approach.

Or use the above table.

<?php
$conn = Ocilogon ($user, $password, $SID);
$stmt = Ociparse ($conn, "SELECT * from PICTURES WHERE id= $pictureid");
Ociexecute ($stmt);
The secret is on the third parameter of Pcifetchinfo: Oci_return_lobs. The third parameter is the mode of the fetch, and if oci_return_lobs, the LOB's value is placed directly in the result array, not the LOB locator, and the LOB object's load () method is not used.
if (Ocifetchinto ($stmt, $result, Oci_assoc+oci_return_lobs))
{
echo "Content-type:". Stripslashes ($result [MIME]);
Echo stripslashes ($result [picture]);
}
Ocifreestatement ($stmt);
Ocilogoff ($conn);
?>

This program is used to display the data (Pictures) placed in the LOB. Invoke method (assuming script name is getpicture.php):


Inquire

As mentioned earlier, the LOB fields for Oracle cannot be matched with like. What do we do? In fact, Oracle has an anonymous package called Dbms_lob, which has all the processes required to manipulate the LOB.

Suppose there is such a table as this:
CREATE TABLE Articles (
ID number,
TITLE VARCHAR2 (100),
CONTENT CLOB
);

The contents of the article are placed in the Content field.

Now we're going to find out what the content contains the "PHP Chinese user" article, so you can do this:

<?php
$conn = Ocilogon ($user, $password, $SID);
The WHERE clause is dbms_lob. InStr process. It has four parameters, the first two representing the LOB's locator (which can be represented directly in a field) and the string to look for, and the following two representing the starting offset and the number of occurrences, respectively. Note that it must be judged by its return value, which is greater than 0.
$stmt = Ociparse ($conn, "SELECT * from articles WHERE Dbms_lob.") INSTR (CONTENT, ' php Chinese user ', 1, 1) > 0 ");
Ociexecute ($stmt);
if (Ocifetchinto ($stmt, $result, Oci_assoc+oci_return_lobs))
{
...
}
Ocifreestatement ($stmt);
Ocilogoff ($conn);
?>

Oracle also provides a number of procedures for manipulating LOB data, such as length, substr, and so on. For their detailed usage, Oracle's development manual can be considered.

There is so much to say about the operation of LOB-type data in an Oracle database. Since I am not in touch with Oracle for a long time, there may be errors in this article, you are welcome to criticize.