December 22, 2014
Hot Topics:

Combining an Oracle Database with PHP to Manage Data

  • October 5, 2007
  • By Deepak Vohra
  • Send Email »
  • More Articles »

Which Connection Method to Use

The OCI8 PHP class library provides the oci_connect(), oci_pconnect(), and oci_new_connection() methods to connect to the Oracle database. If a non-persistent connection is required that is cached, use the oci_connect() method. When the oci_connect() method is invoked with the same parameters more than once in the same script, the same connection resource is returned. Caching of connections improves connection efficiency. The cache used by oci_connect() is deallocated when a script has run or when the connection resource is closed. If transactional isolation between two sets of queries is required, use the oci_new_connection() method; this returns a new connection. A connection returned by oci_new_connection() is not cached. If a persistent connection that is cached across requests is required, use the oci_pconnect() method. A persistent connection does not get closed when the script has run or with the oci_close(resource connection) method. A persistent connection reduces the overhead of opening and closing connections, thus improving performance. However, if an application connects to the Oracle database using a different set of credentials for different web users, the number of connections in the persistent cache increases to the point that the performance of the Oracle server decreases due to the many idle connections. Tune the persistent connections by setting a limit on the maximum number of persistent connections in the cache with the oci8.max_persistent configuration directive, and reducing the number of idle persistent connections with the oci8.persistent_timeout configuration directive.

Creating a Database Table

In this section, you will create an Oracle database table. Create a PHP script, createOracleTable.php, in the C:/Program Files/Apache Group/Apache2/htdocs directory, the document root directory of Apache web server. Define variables $username, $password, and $db for the Oracle database username, password, and database. Specify the $db variable value as the database SID value in the <Oracle10g>/NETWORK/ADMIN/tnsnames.ora file.

$username='OE';
$password='pw';

$db='(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
   )
)';

If the HOST, PORT, or SERVICE_NAME are different than those specified in the PHP script, modify the settings in the script. Use the oci_connect() method to connect with the database.

$connection = oci_connect($username, $password, $db);

If an error occurs in obtaining a connection with the Oracle database, retrieve the error using the oci_error() function and output an error message.


if (!$connection) {
   $e = oci_error();
   echo htmlentities($e['message']);
}

Prepare an Oracle statement to create a database table using the oci_prepare() function.


$stmt = oci_parse($connection,
   "CREATE TABLE OE.Catalog(CatalogId VARCHAR(25) PRIMARY KEY,
   Journal VARCHAR(25), Publisher Varchar(25), Edition VARCHAR(25),
   Title Varchar(45), Author Varchar(25))");

Execute the SQL statement using the oci_execute() method.

$r = oci_execute($stmt);

If an error occurs in generating the table, retrieve the error using the oci_error() function and output an error message.


if (!$r) {
   $e = oci_error($stmt);
   echo htmlentities($e['message']);
}else{

   echo  " Created table\n\n";
}

Prepare an SQL INSERT statement to add a row of data to the Catalog table using the oci_prepare() statement.


$sql = "INSERT INTO OE.Catalog VALUES('catalog1', 'Oracle Magazine',
    'Oracle Publishing', 'Nov-Dec 2004', 'Database Resource Manager',
    'Kimberly Floss')";
$stmt = oci_parse($connection, $sql);

Run the SQL statement using the oci_execute() function.

$r = oci_execute($stmt);

Similarly, add another row of data. The PHP script, createOracleTable.php, that creates an Oracle database table is presented in Listing 1.

Listing 1: createOracleTable.php

<?php
$username='OE';
$password='pw';

$db='(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
   )
)';

$connection = oci_connect($username, $password, $db);

if (!$connection) {
   $e = oci_error();
   echo htmlentities($e['message']);
}

$stmt = oci_parse($connection,
   "CREATE TABLE OE.Catalog(CatalogId VARCHAR(25) PRIMARY KEY,
   Journal VARCHAR(25), Publisher Varchar(25), Edition VARCHAR(25),
   Title Varchar(45), Author Varchar(25))");
if (!$stmt) {
   $e = oci_error($connection);
   echo htmlentities($e['message']);
}
$r = oci_execute($stmt);
if (!$r) {
   $e = oci_error($stmt);
   echo htmlentities($e['message']);
}else{

   echo  " Created table\n\n";
}

$sql = "INSERT INTO OE.Catalog VALUES('catalog1', 'Oracle Magazine',
   'Oracle Publishing', 'Nov-Dec 2004', 'Database Resource Manager',
   'Kimberly Floss')";
   $stmt = oci_parse($connection, $sql);
if (!$stmt) {
   $e = oci_error($connection);
   echo htmlentities($e['message']);
}

$r = oci_execute($stmt);
if (!$r) {
   $e = oci_error($stmt);
   echo htmlentities($e['message']);
}else{

   echo " Added a row\n\n";
}
$sql = "INSERT INTO OE.Catalog VALUES('catalog2', 'Oracle Magazine',
   'Oracle Publishing', 'March-April 2005',
   'Starting with Oracle ADF ', 'Steve Muench')";

   $stmt = oci_parse($connection, $sql);

if (!$stmt) {
   $e = oci_error($connection);
   echo htmlentities($e['message']);
}
$r = oci_execute($stmt);
if (!$r) {
   $e = oci_error($stmt);
   echo htmlentities($e['message']);
}else{

   echo " Added a row\n\n";
}

?>




Page 3 of 5



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Rocket Fuel