Using the ADOdb library with Oracle and PHP

ADOdb Library

Last week we discussed the use of the OCI8 extension and connection methods for using ADOdb to connect to an Oracle database. This week we’ll delve further into the ADOdb library and will disect some example code to get you up to speed!

To connect to the database, the function ADONewConnection($Driver) is used. Although similar to OCI8, ADOdb uses a different type of connection process.

PConnect(): This function always creates a persistent connection. As above, it improves the performance as it reduces the overhead of opening and closing the database connection.

Connect() : This function is used in cases where a non-persistent connection is required. Similar to OCI8, if this function is called more than once in the script, the same connection is used, provided the connection is not yet closed.

NConnect() : Oracle, as well as some database drivers, also supports this function which creates a new connection. Opening and closing a database connection is always system intensive, but in some scenarios it is necessary to create a new connection.

$conn = &ADONewConnection('oci8');
$conn -> PConnect(false, $dbuser, $dbpassword, $database);

The code shown above is used to connect to Oracle using ADOdb. Parameters that are passed to the PConnect() are username, password and the Oracle database SID from the tnsname.ora file.

In general, we can use the function shown below to connect to just about any database.

Connect($server, $user, $password, $database)

Sample code using ADOdb

Now we will show you a sample of code that allows you to use PHP to connect to an Oracle database using the ADOdb library. This code executes a select statement which selects all records and displays the first two fields on a web page.

<?

include('adodb.inc.php');	  

$dbuser = "dbuser";
$dbpassword = "dbpassword";
$db = "oracle_sid";

$conn = &ADONewConnection('oci8');

$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;

$conn -> NConnect(false, $dbuser, $dbpassword, $db);

if (FALSE == $conn)
        {
                echo "Connection error";
	   exit;
        }
$sql = "SELECT??*??FROM??SAMPLE_TABLE";

$recordSet=$conn->Execute($sql);

if (!$recordSet) 
	echo $conn->ErrorMsg();
else
while (!$recordSet->EOF) {
	echo $recordSet->fields[0].' '.$recordSet->fields[1].'<BR>';
	$recordSet->MoveNext();
}

$recordSet->Close(); # optional
$conn-&gtClose(); # optional

?>

Code Walkthrough

include('adodb.inc.php');

It is very important to include the ADOdb library path. Once you install the ADOdb library for PHP, you need to specify the path of the adodb.inc.php file. This will enable all ADOdb related calls and methods that need to be invoked. In the code shown above, our assumption is that the adodb.inc.php file is present in the current working directory, but you will need to specify the absolute or relative path if that is not the case.

$dbuser = "dbuser";
$dbpassword = "dbpassword";
$db = "oracle_sid";

$dbuser : The Oracle user name.
$dbpassword  : The password for oracle user.
$db : the database SID value in the tnsname.ora file

$conn = &ADONewConnection('oci8');

The ADONewConnection function shown above is used to create a new ADO connection to a database.

$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;

$ADODB_FETCH_MODE is a global variable that determines how arrays are retrieved by record sets. If no fetch mode is defined, the fetch mode by default is ADODB_FETCH_DEFAULT. The behaviour of this default mode varies from driver to driver so it is advised to explicitly specify the $ADODB_FETCH_MODE according to your preference.

$conn -> NConnect(false, $dbuser, $dbpassword, $db);

This is a non-persistent connection to a database “$db” using userid $dbuser and password $dbpassword. It returns true or false depending on connection success or failure.

$recordSet=$conn->Execute($sql);
This executes SQL statement $sql and returns the record set if successful
echo $conn->ErrorMsg();
The above function returns the last status or error message. The error message 
is reset after every call to Execute().
while (!$recordSet->EOF) {
	echo $recordSet->fields[0].' '.$recordSet->fields[1].'<BR>';
	$recordSet->MoveNext();
}