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->Close(); # 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();
}
The code shown on the previous page is used to display the first two columns of the table. Once the sql statement is executed, the record set contains the result of the select statement as an array. Using a while loop we are able to traverse through each record and display the first two columns of the table. Once the display part is done, the cursor moves to the next record using the MoveNext() function. Similarly, we can perform any other operation on the record set.
$recordSet->Close(); # optional
$conn->Close(); # optional
Once we finish all operations on the record set, we can close it (though this is optional). Similarly we can close the connection to database (this is also optional).
This completes the explanation of the sample code that is used to connect to the Oracle database and perform operations on it using PHP's ADOdb.
Note: All functions in ADOdb should be easy for Microsoft programmers to use because many of the conventions are similar to Microsoft's own ADO.
Conclusion
In this document we have covered the connection establishment from the PHP script to the Oracle database using an OCI8 extension and an ADOdb abstract library, as well as illustrated a simple operation on the Oracle database. This article is not enough to start developing a full-fledged Open Source web-based application, but it does provide a valuable introduction to Open Source web programming.
References