Book Image

PHP Oracle Web Development: Data processing, Security, Caching, XML, Web Services, and Ajax

By : Yuli Vasiliev
Book Image

PHP Oracle Web Development: Data processing, Security, Caching, XML, Web Services, and Ajax

By: Yuli Vasiliev

Overview of this book

Oracle Database gets high marks for performance, reliability, and scalability. Building and deploying your PHP applications on Oracle Database enables you to combine the power and robustness of Oracle and the ease of use, short development time, and high performance of PHP. When used in a complementary way, PHP and Oracle allow you to build high-performance, scalable, and reliable data-driven Web applications with a minimum of effort.When building a PHP/Oracle application, you have two general options. The first is to use an Oracle database just to store data, performing all the operations on that data on the client side; the other is to use the database not only to store data, but also to process it, thus moving data processing to the data. While building the key business logic of a database-driven PHP application inside the database is always a good idea, you should bear in mind that not all of the databases available today allow you to do. The Oracle database, which offers record-breaking performance, scalability, and reliability, does. The partnership of Oracle and the open-source scripting language PHP is an excellent solution for building high-performance, scalable, and reliable data-driven web applications.This 100% practical book is crammed full of easy-to-follow examples. It provides all the tools a PHP/Oracle developer needs to take advantage of the winning combination. It addresses the needs of a wide spectrum of PHP/Oracle developers, placing the emphasis on the most up-to-date topics, such as new PHP and Oracle Database features, stored procedure programming, handling transactions, security, caching, web services, and Ajax.
Table of Contents (16 chapters)
PHP Oracle Web Development
Credits
About the Author
About the Reviewer
Preface

Creating Your First PHP/Oracle Application


Using the information provided in the What You Need to Start section earlier in this chapter, as well as the information provided in Appendix A Installing PHP and Oracle Software, you can easily install all the required pieces of software in your system. Once you are done with it, you are ready to create your first PHP/Oracle applications. The example provided in this section consists of one PHP script: dbtime.php. All this simple script does is display the current time obtained from the database. The result is a single string that should look like this:

The current time is 07:30:20

The following figure shows what this looks like in a web browser:

Despite the simplicity of the results produced, this PHP script is a good example of how a PHP application can interact with Oracle by means of PHP's OCI8 functions. To display a simple string representing the current time, dbtime.php performs the following sequence of steps:

  • Connects to the Oracle database

  • Executes a SELECT query against the database

  • Fetches the received result and then displays it to the user

Now that you know what the script does behind the scene in order to display a simple string representing the current time, you might want to look at the code. The dbtime.php script code is shown below:

<?php
//File: dbtime.php
$dbHost = "localhost";
$dbHostPort="1521";
$dbServiceName = "orcl";
$usr = "hr";
$pswd = "hr";
$dbConnStr = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=".$dbHost.")(PORT=".$dbHostPort.")) (CONNECT_DATA=(SERVICE_NAME=".$dbServiceName.")))";
if(!$dbConn = oci_connect($usr,$pswd,$dbConnStr)) {
$err = oci_error();
trigger_error(‘Could not establish a connection: ‘ . $err[‘message'], E_USER_ERROR);
};
$strSQL = "SELECT TO_CHAR(SYSDATE, ‘HH:MI:SS') ctime FROM DUAL";
$stmt = oci_parse($dbConn,$strSQL);
if (!oci_execute($stmt)) {
$err = oci_error($stmt);
trigger_error(‘Query failed: ‘ . $err[‘message'], E_USER_ERROR);
};
oci_fetch($stmt);
$rslt = oci_result($stmt, ‘CTIME');
print "<h3>The current time is ".$rslt."</h3>";
?>

As you can see in the code, the connect descriptor contains location details for the database you want to connect to. In particular, it consists of the host name, the port on which the Oracle Net listener process is running, and the SID of the database. For further discussion, see the Connecting to a Database subsection later in this section.

Note

This example assumes that you have HR/HR demonstration schema installed in your database. For the sake of this example, though, you could use any other database schema. For this, you simply set the $usr and $pswd variables to appropriate values.

To establish a connection to the Oracle Database server, you use the oci_connect function. This function returns a connection identifier that is then used in the other OCI8 calls in this script.

Note

Besides oci_connect, there are two other OCI8 functions that you can use to establish a connection to the database: oci_new_connect and oci_pconnect. For detailed discussion of the OCI8 connection functions see Chapter 4

Another important thing to note in the script is the use of the oci_error function. When used to obtain a connection error, the oci_error function is invoked without a parameter. Otherwise, you pass an appropriate connection identifier returned by the oci_connect function.

In this example, the trigger_error function triggers an error and then stops execution because you pass predefined constant E_USER_ERROR as the third parameter.

Note

The trigger_error function is covered in more detail in Chapter 2, section Using the trigger_error Function.

The query discussed in this example contains two standard Oracle SQL functions: SYSDATE, used here to obtain the current time from the operating system on which the database resides, and TO_CHAR, used here to convert a DATE value returned by SYSDATE to a string of characters.

Another thing to note here is the use of the column alias in the query. This will allow you to refer to the query result by its alias, in this example: ctime, later in the oci_result function. Otherwise, you would have to deal with a column name, which in this case is TO_CHAR(SYSDATE, ‘HH:MI:SS').

After the query string is defined, you use the oci_parse function that prepares the SQL statement for execution.

Note

oci_parse doesn't look for errors in the SQL query. You have to execute the query to check if it is valid.

oci_execute returns a Boolean value: true on success and false on failure. Using the IF-THEN statement allows you to take appropriate steps in case of a failure. In this example, if oci_execute returns false, the script generates the error and stops execution.

Normally you use the oci_fetch function in a loop to fetch the next row into the result buffer. In this example, however, the result consists of one row only.

After the current row has been fetched by oci_fetch, you use oci_result to obtain the field's value from that row. As mentioned, in this particular case the query result consists of one row that contains one field.

Note

Regardless of the way in which you specified a column name or a column alias name in the query, Oracle returns all field names in uppercase. So, you must specify all field names in uppercase when calling the oci_return function. Specifically, in this example you must use CTIME instead of ctime.

Connecting to a Database

No doubt you have realized that before your application can make use of the database data, it must first connect to the database. While the PHP OCI8 extension provides the oci_connect function for just this purpose, Oracle in fact allows you to configure connectivity information in several ways. This section discusses how to use the Local Naming and Easy Connect Naming Oracle methods when connecting to an Oracle database from PHP.

Using the Local Naming Method

By specifying localhost as the host name in the connect descriptor in the above example, you tell oci_connect that the database you are connecting to is local. In a real‑world situation, however, you might need to establish a connection to a remote database. To do this, you can use either the IP address or network name of the host machine on which the Oracle Net listener is running. For example, if your Oracle database server resides on the computer whose network name is MyServer and IP address is 192.168.100.1, you might use either HOST = MyServer or HOST = 192.168.100.1 in the connect descriptor.

Note

Using a connect descriptor is a common way of providing information required for establishing a connection to a remote database.

To make using connect descriptors easier, you might define them in the tnsnames.ora file, which is normally located in the ORACLE_HOME/network/admin directory on the client machine. Once you have a connect descriptor defined in the tnsnames.ora file, you can then refer to that descriptor by name. Doing so saves you the trouble of defining descriptor connections in your application code. For example, you might define the following connect descriptor in tnsnames.ora:

ORCL10gR2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

Once you have the above entry saved in the tnsnames.ora file, you can then pass the name that maps to the connect descriptor, namely, ORCL10gR2, as the third parameter to oci_connect, rather than passing the string representing the connect descriptor itself:

$dbDescName = "ORCLR10gR2";
$dbConn = oci_connect($usr,$pswd,$dbDescName);

It is important to note that you may not pass the third parameter to oci_connect at all if the database you are connecting to is local.

Using the Easy Connect Method

For TCP/IP environments, you might make use of Oracle Database 10g's Easy Connect Naming Method feature, which can eliminate the need for service name lookup in the tnsnames.ora file. We touched on this feature in the Using Oracle SQL*Plus section earlier in this chapter, when discussing some of the ways you can connect to a remote database with SQL*Plus. If you recall, the Easy Connect Naming method enables you to connect to an Oracle database server by simply providing the database user/password combination and the server computer's host name along with two optional parameters, namely, the service name of the database and the port on which the listener will accept connections. Note, however, that you cannot omit the service name when it comes to defining the third parameter of oci_connect:

$dbEasyConn = "//MyDbServer/orcl";
$dbConn = oci_connect($usr,$pswd,$dbEasyConn);

To specify an optional port, you use the following syntax:

$dbEasyConn = "//MyDbServer:1521/orcl";

It is important to realize that orcl in the above examples is not the name of a connect descriptor defined in the tnsnames.ora file, but the service name of the database.

Note

The service name defaults to the global database name—a name consisting of the database name and domain name, which are specified during the installation or database creation.

As you can see, the Easy Connect Naming method provides an easy-to-use syntax enabling you to connect to a database server without any configuration.

Issuing an SQL Statement Against the Database

In the example discussed earlier in this chapter, you used the oci_execute function to execute a SELECT statement against the database. It is important to note that oci_execute's use is not limited to QUERY operations—you can use this OCI8 function to execute any SQL or PL/SQL statements performing QUERY, DML, and DDL operations.

As mentioned, oci_execute returns a Boolean value: true on success and false on failure. In the case of a failure, you likely will want your script to perform certain actions in response. For example, you might use the trigger_error PHP function to either generate an error and stop execution or generate a warning and continue execution. Whether error_trigger stops script execution or not depends on what predefined error level constant you are passing to this function as the second parameter. For example, if you want the script to generate a warning message and continue execution, you must use the E_USER_WARNING predefined constant:

$err = oci_error();
trigger_error(‘Query failed: ‘ . $err[‘message'], E_USER_WARNING);

To terminate execution, you use the E_USER_ERROR constant.

Fetching and Displaying Results

If the oci_execute call returns true, you can then move on to fetching the results. Of course, this makes sense only when you are dealing with a QUERY operation—that is, you are issuing a SELECT statement.

There are several ways to fetch the result data using the PHP OCI8 extension. The example discussed earlier in this chapter demonstrates the use of oci_fetch in conjunction with oci_result:

oci_fetch($stmt);
$rslt = oci_result($stmt, ‘CTIME');
print "<h3>The current time is ".$rslt."</h3>";

You use oci_fetch to fetch the next row from the result data into the internal result buffer. Then, you use oci_result to retrieve the CTIME field's value from the fetched row.

It is important to note that with oci_fetch and oci_result you are not limited to using associative indices—you can use numeric indices as well. So, you might rewrite the above code as follows:

oci_fetch($stmt);
$rslt = oci_result($stmt, 1);
print "<h3>The current time is ".$rslt."</h3>";

While using oci_fetch assumes that you will then use oci_result to retrieve the fetched data from the internal result buffer, there are OCI8 fetch functions that fetch the next row from the result data directly into a PHP array, thus eliminating the need to use oci_result. One of these functions is oci_fetch_array. With this function, you can reduce the above three lines of code to the following two:

$rslt = oci_fetch_array($stmt, OCI_ASSOC);
print "<h3>The current time is ".$rslt[‘CTIME']."</h3>";

Or by using numeric indices:

$rslt = oci_fetch_array($stmt, OCI_NUM);
print "<h3>The current time is ".$rslt[0]."</h3>";

For further discussion on OCI8 fetch functions, see Chapter 2