Using PHP to manipulate Oracle's LOB type data _php tutorial

Article Source: It computer tutorials

Anyone who has used Oracle knows that Oracle has a data type called VARCHAR2, which is used to represent an indefinite string. VARCHAR2 is also a recommended type for Oracle companies. But there is a problem with VARCHAR2: The maximum is only 4,000 characters, which 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 for multiple VARCHAR2, and the other for LOB fields. Here we take a look at the second approach.

Take a general look at the LOB fields of Oracle. Oracle's LOB types are divided into three kinds: Blob,clob and bfile. Clob called characters Lob,blob and bfile are used to store binary data. The maximum length of clob and BLOBs is 4GB, which stores the values in the Oracle database. Bfile is similar to a blob, but it puts the data in an external file, so it is also called an external blob (External blob).

I don't think we should be unfamiliar with MySQL. There are similar data types in MySQL, such as text and blobs. In PHP's MySQL function, the operation of Text/blob is straightforward, just like any other type of data. But in Oracle, things are different. Oracle treats LOBs as a special type of data and cannot be manipulated in a conventional way. For example, you cannot insert a value directly into a LOB field in an INSERT statement, or you can find it with like.

Below are some examples of how to insert, remove, and query LOB data using the OCI function of PHP.

Insert

You cannot insert a value directly into the LOB field with the INSERT statement.　　In general, as in the next steps:

1, parse an INSERT statement, return a LOB descriptor

2, generate a local LOB object with the OCI function

3, bind the LOB object to the LOB descriptor

4. Execute the INSERT statement

5, assign a value to the LOB object

6, Release the LOB object, and the SQL statement handle

The following example is to store the image file uploaded by the user in a blob (or bfile, with a slightly different operation). The first 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 increment of the ID, build another sequence:

CREATE SEQUENCE Pic_seq;

Then there is the PHP program code to process the data.

<?php Establishing an Oracle database connection $conn = Ocilogon ($user, $password, $SID); Submit SQL statement to Oracle Here are two points to note: one is to use the Empty_blob () function. This is an intrinsic function of Oracle that returns a locator for the lob. When you insert a lob, you can only use this method as an empty LOB locator, and then manipulate the locator. The Empty_blob () function is for BLOB type, which corresponds to CLOB empty_clob (). The second is the part behind the returning, which returns the picture, allowing the OCI function of PHP to 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 bfile and ROWID objects. $lob = Ocinewdescriptor ($conn, Oci_d_lob); Binds the resulting LOB object to the locator returned by the preceding SQL statement. Ocibindbyname ($stmt,:P icture, & $lob,-1, Oci_b_blob); Ociexecute ($stmt); The data is stored in the 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 fetch data. But there is only one way to bfile type is save () if ($lob->savefile ($lob _upload)) { Ocicommit ($conn); echo "Upload succeeded <br>"; }else{ echo "Upload failed <br>"; } Releasing LOB objects Ocifreedesc ($LOB); Ocifreestatement ($stmt); Ocilogoff ($conn); ? >

There is one more point to note: The value of the LOB field is at least 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 take data out of a lob. One is to generate a LOB object, bind to a locator returned by a SELECT statement, and then fetch the data with the LOB object's load () method, and the ocifetch*** function directly with PHP. The first method is much more troublesome than the second one, so I'll just talk about the second approach.

Or use the table above.

$conn = Ocilogon ($user, $password, $SID); $stmt = Ociparse ($conn, "select * from PICTURES where id= $pictureid"); Ociexecute ($stmt); The secret is on Pcifetchinfo's third argument: Oci_return_lobs. The third parameter is the fetch pattern, which, if Oci_return_lobs, puts the LOB's value directly into the result array instead of the LOB locator, without the load () method of the LOB object. 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 data (images) that are placed in the LOB. Call the method (assuming the script name is getpicture.php):


　　 Enquiry

As already mentioned, LOB fields for Oracle cannot be matched with like. What do we do? It's not complicated, Oracle has an anonymous package called Dbms_lob, which has all the necessary procedures for manipulating lobs.

Suppose there is a table like this:

CREATE TABLE Articles ( ID number, TITLE VARCHAR2 (100), CONTENT CLOB );

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

Now we want to find out that the content contains the "PHP Chinese user" article, you can do this:

$conn = Ocilogon ($user, $password, $SID); Dbms_lob is used in the WHERE clause. InStr process. It has four parameters, the first two are the locators that represent the LOB (which can be represented directly in the field) and the string to find, and the next two indicate the starting offset and the number of occurrences. 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, you can consider Oracle's development manuals.

There are so many things to do with data on LOB types in an Oracle database. Since I have not been in touch with Oracle for a long time, there may be errors in this article, and you are welcome to criticize.

http://www.bkjia.com/PHPjc/532663.html www.bkjia.com true http://www.bkjia.com/PHPjc/532663.html techarticle Article source: It computer tutorials people who have used Oracle know that Oracle has a data type called VARCHAR2, which is used to represent an indefinite string. VARCHAR2 is also the recommended class for Oracle companies ...

