Example 25 showed a call to the Execute() method. It is easy to guess that the SQL commands are executed using the Execute() method. The syntax is very simple:

Bind arrays are associative arrays with keys having the same name as the corresponding SQL placeholders, as shown in the illustration below:

$SQL=”select ename,empno,hiredate,sal
             from emp
             where job like :job”;
$sth=$db->Execute($SQL, array(“JOB”=>'C%'));

Names of the placeholders are not case sensitive. The placeholder :job in the SQL statement is lowercase, while the bind variable JOB is uppercase.

The methods for fetching rows, same as in OCI8 module, return both an associative array and an array indexed by numbers. This can be changed by the connection method (belonging to the DB connection class) SetFetchMode.  It is invoked as follows:

The same effect can be achieved by setting the global variable $ADODB_FETCH_MODE to one of the two values shown above. The reason for using the range() function in Example 25 was that the associative array $row was indexed both by column names and the column position.

Another very useful global variable is called $ADODB_COUNTRECS. Setting this variable to TRUE (the default), buffers recordsets in PHP and allows the Remote DBA to perform $rs->RecordCount(). Setting it to FALSE disables the buffering of recordsets, so only the current row is stored, reducing memory constraints.

In contrast to PEAR DB, ADOdb places significant emphasis on the result set and dealing with the result set.  As with PEAR DB, the result set in ADOdb uses identical methods called fetchrow() and fetchinto(). In addition, the result set class in ADOdb contains navigation methods that closely resemble those of arrays. Those methods are:

• Move()

• MoveNext()

• MoveFirst()

• MoveLast()

• CurrentRow()

These methods are iterators and are used to navigate through the result set. Below is an example of how to use them. It is a rewrite of  example 13 from Chapter 3 or example 20 from the Chapter 4 used to illustrate the binding of variables to placeholders.

Example 26

catch (Exception $e) {
    die($e->getMessage());
}
echo “There are “.$rs->RecordCount().” rows in the result set.\n”;
while (!$rs->EOF) {
        for($ind=0; $ind<$nfields; $ind++) {
             echo $rs->fields[$ind]."\t";
       }
       print $rs->CurrentRow();
       print "\n";
       $rs->MoveNext();
}
?>

There are 4 rows in the result set.
SMITH     CLERK   7369    800       0
ADAMS    CLERK   7876    1100     1
JAMES     CLERK   7900    950       2
MILLER    CLERK   7934    1300     3

The last column in the output is printed by the CurrentRow()method.  The RecordCount() method gives the count of records in the result set, making navigation through the result set much easier.  

It is also helpful to know how many columns and their types and names are in the result set.  Examples 25 and 26 uses both the FieldCount() and FetchField()methods. The FieldCount() method is self-explanatory. It returns the number of columns in the result set. On the other hand, the FetchField() method requires more of an explanation.  Before doing so, the way this method was used in

Example 25 is reviewed as follows:

The purpose of this code snippet is to reveal the number of columns in the result set and to write their names as a title. The FetchField() method returns an object not an array, as in the case with PEAR DB containing the column name, type and length. It should also return information regarding whether the column can be NULL. Unfortunately, that part does not yet work, as is shown in the following example:

Example 27

When executed, this code, which is nothing more then a PHP version of the SQL*Plus “describe” command, gives the following result:

$ ./example27.php

All columns are described as nullable, though Oracle’s SQL*Plus utility disagrees:

Not only is the EMPNO column described as NOT NULL, the sizes are wildly different. If the precision is 0, the types are converted from NUMBER to INT. The Remote DBA should trust the FetchField() method as far as the name and type are concerned. The only module that has accurate functions for describing the result set is OCI8.

The ADOdb code that examines whether the column is nullable is actually from the contributed patch for PostgresSQL and is removed from the new versions of ADOdb. This means that in the result of the FetchField()method,  the not_null member will be undefined for the OCI8 connections in ADOdb 4.61 and newer.

This section would not be complete without mentioning the methods for closing both a connection and a result set (a.k.a. “cursor”).  Not surprisingly, both structures are closed by using the Close() method, shown below:

$db->Close();
or
$rs->Close();

Closing a connection or cursor is not frequently used, especially in PHP5 because an object destructor is called automatically when an object falls out of the scope.

SEE CODE DEPOT FOR FULL SCRIPTS

http://www.rampant-books.com/book_2005_2_php_oracle.htm