PHP Operations Oracle Database Guide-i original, experience summary, can not help but see

oracle| Data | database | Original I use the Oracle database for working relationship, found that there are not many people here, but often found that someone mentioned in the PHP operation of the Oracle database problem is not answered, I have asked a few, but no response, So I decided to dedicate some of the skills and experience I have accumulated in my work to help people who use Oracle database.
First, configure the environment:
Oracle8 Call-interface (OCI8) is required to access databases above Oracle8. This extension module requires a ORACLE8 client function library, so you need to connect to a remote Oracle database and install Oracle's client software--free to download on the Oracle Web site-http:// Www.oracle.com, this is often overlooked by many beginners, so if you read this article, do not mention in the forum "Why I am not connected to Oracle database" problems.
(1) First confirm the installation of the Oracle8i client, and then use the Net8 Assistant (client software provided) to establish a service name, attention to clothing

The service name is the SID of the Oracle database, which can be queried by the server_names in the Initsid file.
(2) in php.ini; extension=php_oci8.dll the preceding annotation symbol ";" Remove to enable PHP to load modules that support Oracle

。 and copy the Php_oci8.dll to the System32 subdirectory of your Windows2000 server installation directory. such as D:\winnt\system32, heavy

New start of your machine.
(3) Write a test file to try whether the correct connection (if the service name SID is test):
This is where the Scott user is from Oracle, so just put the following file in your Web root directory. If you display

Shows the data in the database, the connection is normal, if not, you have to check where the previous steps have done wrong.
test.php
?
$dbconn =ocilogon ("Scott", "Tiger", "test");
$sql = "SELECT * from EMP";
$stmt = Ociparse ($dbconn, $sql);
if (! $stmt) {
echo "Exit
}
Ociexecute ($stmt);
while (Ocifetchinto ($stmt & $result _array))
{
Echo

"Empno= $result _array[0]; Ename= $result _array[1]; job= $result _array[2]; mgr= $result _array[3]<br> ";
}
?>

Second, the use of PHP to execute the Oracle stored procedures
(1) After connecting with Sqlplus, establish a stored procedure:
CREATE OR REPLACE PROCEDURE Inoutdemo (
Par_in in VARCHAR2,
Par_in_out in Out VARCHAR2,
Par_out out VARCHAR2)
Is
BEGIN
Par_out: = par_in;
Par_in_out: = par_in | | ' ' || Par_in_out;
End;
(2) PHP file:
sptest.php
?
: In is the input variable;: InOut is an input and output variable;: Out is an output variable, please refer to Oracle's Pl/sql manual
$conn =ocilogon ("Scott", "Tiger", "test");
$stmt = Ociparse ($conn, "BEGIN Inoutdemo" (: in,:inout,:out); end; ");
Ocibindbyname ($stmt, ": In", $in, 32);
Ocibindbyname ($stmt, ": InOut", $inout, 32);
Ocibindbyname ($stmt, ": Out", $out, 32);

$in = "Hello";
$inout = "world!";

Ociexecute ($stmt);
echo "<BR><BR>";
echo "in=". $in. " <BR> ";
echo "inout=". $inout. " <BR> ";
echo "out=". $out. " <BR> ";
?>

Third, the Oracle database paging
Oracle, although not as limit available, very convenient, but also has its own processing methods, it has a special rownum on the paging has a very important

Use. There are a number of ways to page pagination, the most common of which is to use minus.
To display the N1-N2 record, write as:
(1) SELECT * FROM tablename where rownum <= n2 minus select * FROM TableName where RowNum < N1
Note: This statement cannot use order by, or an error.
(2) The way to move the pointer down is as follows:
Where: $page is the current page; $pagesize is the number of records displayed per page
For ($i =0 $i < ($page-1) * $pagesize; $i + +)
{
@ocifetch ($stmt);
}
And then use the data from Ocifetch ($STMT) is the record you want to display.
(3) For complex query statements and ordered by, use the following method to resolve:
Select Table_name,table_type from (select RowNum rowseq,x.* from (SELECT * from CAT

TABLE_TYPE) X) WHERE rowseq BETWEEN n1+1 and N2;
My favorite is the third kind, also recommended that everyone use, very convenient AH. Oh.
Other methods are not introduced, very troublesome, using the Oracle cursor and so on, not very suitable for PHP use.

Insert processing of special characters
For some characters such as single quotes ' cannot be handled addslashes in Oracle, but you can use the Oracle CHR function or add a single quote

。
such as: Sql>insert into table values (' It ' | | Chr (39) | | s a test '));
or Sql>insert into table values (' It ' s a Test '));
Show:
It ' s a test.

V. Transaction processing for PHP and Oracle
Ociexecute () function: int ociexecute (int statement [, int mode])
The second parameter mode has two: The default is Oci_commit_on_success, which can be omitted. Oci_default representation with transaction (transation)

Submit, not automatically submitted.
If you have two operation database statements in the program that need to be executed at the same time, there is a failure to rollback, you can write:

$conn =ocilogon ($username, $password, $sid);
The sentence
$SQL = "INSERT INTO tablename values ()";
$stmt =ociparse ($conn, $SQL);
$result =ociexecute ($stmt, Oci_default);
if (! $result) {
Ocirollback ($conn);//rollback if unsuccessful
Ocifreestatement ($stmt); Releasing resources
Ocilogoff ($conn);
}

Second sentence
$SQL = "UPDATE tablename set ...";
$stmt =ociparse ($conn, $SQL);
$result =ociexecute ($stmt, Oci_default);
if (! $result) {
Ocirollback ($conn);//rollback if unsuccessful
Ocifreestatement ($stmt); Releasing resources
Ocilogoff ($conn);
}


Ocicommit ($conn);//If successful, submit
Ocifreestatement ($stmt); Releasing resources
Ocilogoff ($conn);



Vi. manipulating Oracle's LOB type data with PHP (with image storage and display processing)
For PHP programmers, Oracle's biggest headache is using lob to process pictures.
1. PHP Operation Blob:
First set up a table to save the picture. Image files uploaded by the user are stored in a blob
CREATE TABLE PICTURES (
ID number,
Imgtype, VARCHAR2 (60),
DESCRIPTION VARCHAR2 (100),
Picture BLOB
);
If you want to implement an automatic increase in IDs, build another sequence:
CREATE SEQUENCE Pic_seq;

PHP Program-Insert Section:
?
$conn =ocilogon ($username, $password, $sid);
Here are two points to note: one is to use the Empty_blob () function. This is Oracle's internal letter.
Number, returns the locator character of a lob. This is the only way to insert a lob, sir.
Empty LOB Locator, and then operate on the locator character. Empty_blob () function is a needle
For a BLOB type, corresponds to the CLOB of the Empty_clob (). Two is the returning behind the
section, put the picture back, so that the PHP OCI function can be processed.
$stmt = Ociparse ($conn, INSERT into PICTURES (IDs, imgtype,description, picture) VALUES

(Pic_seq. Nextval, ' $imgtype ', ' $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,
Represents the generation of a lob object. Other possibilities are oci_d_file and Oci_d_rowid, divided
Do not correspond to 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);

Method One: 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. Lob

The other methods of the object are: Save () and load (), which are used to save and retrieve the data separately. But only one way to bfile type is save ()
if ($lob-〉savefile ($lob _upload)) {
Ocicommit ($conn);
echo "Upload successful 〈br〉";
}else{
echo "Upload failure 〈br〉";
}

Method Two: Save with the method of saving
$fp = fopen ($lob _upload, "R");
$File->save (Fread ($fp, FileSize ($lob _upload)));
Fclose ($FP);

Releasing LOB objects
Ocifreedesc ($LOB);
Ocifreestatement ($stmt);
Ocilogoff ($conn);
?>
Tip: Select Dbms_lob.getlength from pictures in Sqlplus and see if the file has been saved to data

Library or use the strlen () function in a PHP program to view it.

PHP Program-Display section (getpicture.php):
?
$conn = Ocilogon ($username, $password, $sid);
$stmt = Ociparse ($conn, "select Imgtype,picture from PICTURES WHERE id= $pictureid");
if (Ocifetchinto ($stmt, $result))
{
Header ("Content-type:". $result [0]);
echo $result [1]->load ();
}
You can use strlen ($result [1]->load ()) to see the size of the picture to determine if the picture is properly stored in the database.

?>

Where you want to display the picture, just:


You can show the picture.
Some online articles are written in a way that returns the LOB value instead of the descriptor, and I didn't try to succeed, so we can try.
The code is as follows:
if (Ocifetchinto ($stmt, $result, Oci_assoc+oci_return_lobs))
{
echo "Content-type:". Stripslashes ($result [Imgtype]);
Echo stripslashes ($result [picture]);
}

2. PHP Operation Clob:
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 length of a string in your program

Degree to more than 2000 characters, with VARCHAR2 can not meet the requirements. At this point, you can try to use CLOB. Maximum length of CLOB and blobs

Is 4GB.

Here is an example (refer to the manual in the English version of PHP):
?
The text you want to save
$clobtext = "different DR2";

DB connection
$conn = Ocilogon ("User", "PW", "TNS");

Here the original example uses a stored procedure, you can also use the above method of manipulating blobs to implement.
For example: $stmt = Ociparse ($conn, INSERT into table (ID, Clobtext) VALUES (text. Nextval, the

Empty_clob ()) returning Clobtext Into:clob ");


$sql = "Begin Tempclobtest_package.saveclob (: CLOB); end; ";

$clob = Ocinewdescriptor ($conn, Oci_d_lob);

$stmt = Ociparse ($conn, $sql);

Ocibindbyname ($stmt, ': Clob ', & $clob, -1,oci_b_clob);

if (! Ociexecute ($stmt, Oci_default)) {Print_r (Ocierror ($stmt));}
Else{echo "commit successfully";}

if ($clob->save ($clobtext))
{
Ocicommit ($conn);
echo "submitted successfully";
}
Else
{
Print_r (Ocierror ($stmt));
}

Releasing resources
$clob->free ();
Ocifreestatement ($stmt);
?>