PHP & Oracle

COMP231 Database Management Systems Spring 2009 Lab 9 PHP & Oracle COMP231 Database Management Systems Spring 2009, Lab 9 1 Getting Start    Use SSH to connect “comp231.cs.ust.hk” Create “public_html” under your home directory The premission of the following directory should be “executable” to “others” (i.e. chmod 705): ◦ /homes/[your_account] ◦ /homes/[your_account]/public_html ◦ The .php files COMP231 Database Management Systems Spring 2009, Lab 9 2 First PHP file  Create test.php with the following code:   Upload test.php to your public_html directory. Open IE and browse: http://comp231.cse.ust.hk/~[your_account]/test.php  Congratulation if you see “Hello world” in IE. COMP231 Database Management Systems Spring 2009, Lab 9 3 Oracle Call Interface   To access Oracle database in PHP, we can use OCI functions. Common OCI functions: ◦ ocilogon() and ociconnect() – Establish a connection to Oracle database ◦ ociparse() – prepare an Oracle statement with SQL query ◦ ociexecute() – Execute an oracle statement ◦ ocifetch() – Fetch a row from the data returned from the database ◦ ociresult() – Retrieve a value of a specific field ◦ ocinewcursor() – Create a cursor for storing data returned from the database ◦ ocibindbyname() – Bind a cursor to an output cursor parameter of an Oracle’s stored procedure ◦ oci_feltch_array() – Fetch a row into an array COMP231 Database Management Systems Spring 2009, Lab 9 4 Establishing a Connection to Oracle  To establish a connection to Oracle database, you can use: ◦ resource oci_connect(string $username, string $password, string $db) ◦ resource ocilogon(string $username, string $password, string $db)   oci_connect and ocilogon are identical. Example: $conn = ocilogon(“comp231”, “ab1234”, “comp231.cse.ust.hk”);  You can find the example code COMP231 Database Management Systems Spring 2009, Lab 9 5 Executing Query through PHP (1/2)  After established a connection to Oracle database, we run execute any SQL query like using SQL*Plus. To execute an query, we need to prepare Oracle statement by using ociparse(). $parse = ociparse($conn, $query);   And then use ociexecute() to execute the oracle statement. ociexecute($parse, OCI_COMMIT_ON_SUCCESS); You can control whether PHP will commit the statement by using OCI_COMMIT_ON_SUCCESS You may not want to commit the statement immediate, so you use OCI_DEFAULT and use oci_commit() later.   COMP231 Database Management Systems Spring 2009, Lab 9 6 Executing Query through PHP (2/2)  With ociparise() and ociexecute(), you can do the followings: ◦ Create / drop tables ◦ Insert / update / delete records ◦ Create procedures ◦ Etc. COMP231 Database Management Systems Spring 2009, Lab 9 7 Retrieving Data using SELECT statement  To retrieve data from the database, we need to use SELECT statement. So, we need to perform the following steps: 1. Establish a connection to the data using ocilogon(). $conn = ocilogon(“comp231”, “ab1234”, “comp231.cse.ust.hk”); 2. Prepare the query using ociparse() and ociexecute(): $query = “select table_name from user_tables”; $parse = ociparse($conn, $query); $ociexecute($parse, OCI_DEFAULT); 3. Fetch a row from the database returns using ocifetch(): ocifetch($parse);  4. Retrieve specific field from the fetched row using ociresult(): echo ociresult($parse, “TABLE_NAME”);  You can find an example code here Retrieving Data using Cursor with Stored Procedure (1/2)  We usually create stored procedures in the database for data retrieval. To use the data returned from a procedure, we need to perform the following steps: ◦ Establish a connection using ocilogon() ◦ Create a cursor object using ocinewcursor() $curs = ocinewcursor($conn);  ◦ Prepare Oracle statement using ociparse() $parse = ociparse($conn, “begin showprod(:data); end;”); // showprod is a procedure; // :data is a output cursor parameter Retrieving Data using Cursor with Stored Procedure (2/2) ◦ Bind the cursor object to the procedure parameter using ocibindbyname() ocibindbyname($parse, “data”, $curs, -1, OCI_B_CURSOR); ◦ Execute the Oracle statements using ociexecute() ociexecute($parse); ociexecute($curs); ◦ Fetch a row from procedure returns using oci_fetch_row() $row = oci_fetch_row($curs); echo $row[0]; // first field echo $row[1]; // second field  You can find an example code here Suggestion  To increase your implementation speed of your project, I suggest you to install software on your own PC. 1. Oracle Express Edition http://www.oracle.com/technology/software/products/ database/xe/htdocs/102xewinsoft.html 2. Apache http server http://ftp.cuhk.edu.hk/pub/packages/apache.org/http d/binaries/win32/apache_2.2.11-win32-x86-no_ssl.msi 3. PHP package http://hk.php.net/get/php-5.2.9-2-win32installer.msi/from/this/mirror  You need to install Oracle first, and then Apache, and finally PHP package. COMP231 Database Management Systems Spring 2009, Lab 9 11 PHP Package Installation  For the PHP package installation, you need to ensure: ◦ Select “Apache 2.2.X Module” ◦ Select “Will be installed on local hard disk” for “Oracle (8)” item under “Externsions”  If you want to use short open tags (