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
.
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.
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.
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.
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.
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