I use the Oracle database because of the working relationship, I found that there are not many people here, but often found that some of the questions about PHP operations Oracle database is not answered, I have asked several, but also no response, so decided to put my work in the accumulation of some of the skills, experience and dedication, You want to be helpful to people who use the Oracle database.
First, configure the environment:
Access to the database above Oracle8 requires Oracle8 call-interface (OCI8). This extension requires a Oracle8 client library, so you need to connect to a remote Oracle database and install Oracle's client software-available for free download on the Oracle website-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 the Oracle database" and so on.
(1) First confirm the installation of the Oracle8i client, and then use NET8 Assistant (client software provided) to establish a service naming, pay attention to the
The service name is the SID of the Oracle database and can be queried for server_names in the Initsid file.
(2) in the php.ini; Extension=php_oci8.dll in front of the annotation symbol ";" Remove to enable PHP to load modules that support Oracle
。 and copy the Php_oci8.dll to the System32 subdirectory under your Windows2000 Server installation directory. such as D:\winnt\system32, heavy
New Start your machine.
(3) Write a test file to try to connect correctly (if the service name SID is test):
The Scott user here is Oracle's own without having to build it yourself, just put the following file in your Web root directory. If the display
Data in the database, the connection is normal, and if not, you have to check what was wrong in the previous steps.
test.php
$dbconn =ocilogon ("Scott", "Tiger", "test");
$sql = "SELECT * from EMP";
$stmt = Ociparse ($dbconn, $sql);
if (! $stmt) {
echo "
Error-could not parse SQL statement.
";
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]
";
}
?>
Second, execute Oracle stored procedure with PHP
(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 the input and output variable;: Out is the output variable, please refer to Oracle PL/SQL manual for specific explanation.
$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 "
";
echo "in=". $in. "
";
echo "inout=". $inout. "
";
echo "out=". $out. "
";
?>
III. Paging of the Oracle database
Although Oracle does not have a limit available as MySQL, it is very convenient, but also has its own processing methods, its special rownum on the paging has a very important
Use. Paging can be a number of ways, the most commonly used is minus.
To display the N1-N2 record can be written as:
(1) SELECT * FROM tablename where rownum <= n2 minus SELECT * from TableName WHERE RowNum < N1
Note: The statement cannot use order by, otherwise an error is added.
(2) The method of moving 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 Ocifetch ($stmt) to retrieve the data that you want to show the record.
(3) For complex query statements with order BY, use the following method to resolve:
Select Table_name,table_type from (select ROWNUM rowseq,x.* from (SELECT * from CAT ORDER by
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 cumbersome, using the Oracle cursor and other things, not very suitable for PHP use.
Iv. Insert processing of special characters
For some characters such as single quotes ' cannot be handled in Oracle with Addslashes, but you can use the CHR function of Oracle or add a single quote
。
Example: 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 a total of two: The default is Oci_commit_on_success, can be omitted. Oci_default means transaction (transation)
Submitted, not automatically submitted.
If you are in the program if you have two operations database statements need to execute successfully at the same time, there is a failure to rollback, you can write:
$conn =ocilogon ($username, $password, $sid);
First sentence
$SQL = "INSERT INTO tablename values ()";
$stmt =ociparse ($conn, $SQL);
$result =ociexecute ($stmt, Oci_default);
if (! $result) {
Ocirollback ($conn);//rollback if unsuccessful
Ocifreestatement ($stmt); Freeing 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); Freeing resources
Ocilogoff ($conn);
}
Ocicommit ($conn);//If all succeeds then submit
Ocifreestatement ($stmt); Freeing resources
Ocilogoff ($conn);
Vi. manipulating Oracle's LOB type data with PHP (with image storage and display processing)
For PHP programmers, the most headache for Oracle is to use lobs to process pictures.
1. PHP Operation Blob:
Set up a table to save the picture first. User-uploaded picture files 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 increment of the ID, 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 an internal letter from Oracle
Number that returns a locator for the lob. When inserting lobs, you can only use this method, Sir, as a
An empty LOB locator, and then manipulate the locator. The Empty_blob () function is a pin
For BLOB type, the corresponding to CLOB is Empty_clob (). The second is the returning behind.
section, returning the picture to allow the OCI function of PHP to be processed.
$stmt = Ociparse ($conn, "INSERT into PICTURES (ID, 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,
Do not 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);
Method One: Deposit data into the LOB object. Because the source data here is a file, the SaveFile () method of the LOB object is used directly. Lob
Other methods of the object are: Save () and load (), which are used to save and retrieve data, respectively. 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〉";
}
Method Two: Save by saving the method
$fp = fopen ($lob _upload, "R");
$File->save (Fread ($fp, FileSize ($lob _upload));
Fclose ($FP);
Releasing LOB objects
Ocifreedesc ($LOB);
Ocifreestatement ($stmt);
Ocilogoff ($conn);
?>
Tip: In Sqlplus, select Dbms_lob.getlength is available from pictures; see if the file has been saved to the data
Library or in a PHP program using the strlen () function.
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 ();
}
Use Strlen ($result [1]->load ()) to view the size of the picture to determine if the picture is correctly stored in the database.
?>
Where you need to display the picture, just:
You can show the picture.
Some online articles are written using a method that returns LOB values rather than descriptors, and I didn't try to succeed, so you 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 denote an indefinite length of 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 length of a string in your program
Degree of 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.
The following is an example (refer to the English version of PHP manual):
The text 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 operation Blob method to implement.
For example: $stmt = Ociparse ($conn, "INSERT into table (ID, Clobtext) VALUES (text. Nextval,,
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 "Submit Success";}
if ($clob->save ($clobtext))
{
Ocicommit ($conn);
echo "Submit Success";
}
Else
{
Print_r (Ocierror ($stmt));
}
Freeing resources
$clob->free ();
Ocifreestatement ($stmt);
?>
