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 •
• •
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.
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 theo 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.
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 Transactions.
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. 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.
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.
the field's value from that row. As mentioned, in this particular case the query result consists of one row that contains one field.
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.