PHP How to manage Oracle LOB data _php Tutorial

We all know that.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.

Let's take a general look at Oracle's LOB fields. 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 think we should not 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.

The following is an example of how to use PHP's OCI functions to insert, remove, and query LOB data through several examples of PHP management of Oracle LOB data.

Insert

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

1. First parse an INSERT statement and 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 INSERT statement

5. Assigning values to LOB objects

6. Releasing LOB objects and SQL statement handles

The following example of PHP for managing Oracle LOB data is to store the image file uploaded by the user in a blob (or bfile, with a slightly different operation). The first one 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 the intrinsic function of Oracle,
Returns a locator for a lob. You can only use this method when inserting lobs into an empty lob set
character, and then manipulate the locator. The Empty_blob () function is for BLOB type,
Corresponding to the CLOB is Empty_clob (). The second is the part behind the returning, putting the picture
Returns, 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 Icture ");
  
  

  
Generates a descriptor for a local LOB object. Note the second parameter of the function: Oci_d_lob,
Represents the generation of a lob object. Other possibilities are oci_d_file and Oci_d_rowid,
Corresponds to bfile and rowID objects, respectively.
  
  

  
$ 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 LOB is used directly
The SaveFile () method of the object. Other methods of LOB objects are: Save () and load (),
Used to save and retrieve data separately. But there is only one way to bfile type is save ()
  
  

  
if ($lob->savefile ($lob _upload)) {
  
  

  
Ocicommit ($conn);
  
  

  
echo "Upload success" 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

PHP manages Oracle LOB data to fetch data from one lob in two ways. 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.

 
 

  
  

  
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 fetch pattern,
If oci_return_lobs, put the LOB value directly into the knot
Instead of the LOB locator, the load () method of the LOB object 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 data (images) that are placed in the LOB. Call the method (assuming the script name is getpicture.php):

 
  
  

  
   
   
SRC = "getpicture.php?" Pictureid =  About
Alt
 
  
  

Inquire

As already mentioned, PHP managed Oracle LOB data cannot be matched with like in LOB fields for Oracle. 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:

 
 

  
  

  
Php
  
  

  
$ Conn  =  Ocilogon ($user, $password, $SID); 
  
  

  
Dbms_lob is used in the WHERE clause. InStr process. It has four parameters,
The first two locators (which can be represented directly in the field), respectively, representing the LOB
And the string to find, followed by two to 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 say about PHP's operations for managing Oracle LOB data. 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/446262.html www.bkjia.com true http://www.bkjia.com/PHPjc/446262.html techarticle we all know that 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 your program ...

