PHP

Connecting to Oracle

Two PHP extensions are available for Oracle, but only one is actively maintained and also works with more recent versions of the RDBMS. To install it, configure PHP with the switch with-oci8. The environment variable ORACLE_HOME must be set so that PHP can find the client libraries.

@oci_connect()

Windows users need the php.ini directive extension=php_oci8.dll. In addition, PHP requires read access to the client libraries. Then, oci_connect() tries to establish a connection to the server. The order of the parameters is a bit strange: first username and password, then the name of the service (that has been configured using the configuration assistant or is part of the tnsnames.ora file). The return value is a handle to the connection and is required by further operations in the database.

Connecting to Oracle
<?php
  if ($db = @oci_connect('scott', 'tiger', 'orcl'))
    {
    echo 'Connected to the database.';
    oci_close($db);
  } else {
    echo 'Connection failed.';
  }
?>

In PHP 4, oci_connect() did not exist yet, so for this version, ocilogon() has to be used. However, this is considered deprecated in PHP 5. Therefore, all Oracle details in this chapter use the PHP 5 syntax; however, files with the extension .php4 are included in the download repository that work on older PHP versions.

Sending SQL to Oracle

oci_execute()

This section again uses the quotes table, which also includes an identity column; however, this is a bit more complicated to implement with Oracle. Refer to the script quotes.oracle.sql in the download archive for more information.

Sending SQL to Oracle
<?php
  if ($db = @oci_connect('scott', 'tiger', 'orcl'))
    {
    require_once 'stripFormSlashes.inc.php';
    $sql = 'INSERT INTO quotes (quote, author, year)
      VALUES (:quote, :author, :year)';
    $stmt = oci_parse($db, $sql);
    oci_bind_by_name($stmt, ':quote',
      $_POST['quote']);
    oci_bind_by_name($stmt, ':author',
      $_POST['author']);
    oci_bind_by_name($stmt, ':year',
      intval($_POST['year']));
    oci_execute($stmt, OCI_COMMIT_ON_SUCCESS);
    echo 'Quote saved.';
    oci_close($db);
  } else {
    echo 'Connection failed.';
  }
?>

To send SQL to Oracle, two steps are required. First, a call to oci_parse() parses an SQL string and returns a resource that can then be executed using oci_execute(). The second parameter of oci_execute() is quite important. Several constants are allowed, but most of the time, OCI_DEFAULT is used. Despite the name, that's not the default value, but means "no autocommit." On the other hand, OCI_COMMIT_ON_SUCCESS commits the pending transaction when no error has occurred. And this is, indeed, the default value.

Unfortunately, there is no such thing as oci_escape_string() to escape special characters for use in an SQL statement. Therefore, prepared statements are a mustbut are also very easy to implement. For this, the SQL statement must contain placeholders that start with a colon:

$sql = 'INSERT INTO quotes (quote, author, year)
  VALUES (:quote, :author, :year)';

Then, these placeholders have to be filled with values. For this, oci_bind_by_name() must be used:

oci_bind_by_name($stmt, ':quote', $_POST['quote']);

The preceding code sends some form data to the database. No worries about special characters because oci_bind_by_name takes care of that.

When you are using OCI_DEFAULT as the commit mode, the changes must be written to the database using oci_commit($db); oci_rollback($db) performs a rollback.

By the way, if you want to retrieve the autovalue of the most recent INSERT operation, you have to do it within a transaction and execute SELECT quotes_id.CURVAL AS id FROM DUAL, where quotes_id is the name of the sequence you are using.

by BrainBellupdated
Advertisement: