 |
|
Executing SQL
Queries
Oracle Tips by Burleson Consulting |
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:
$sth=$db->Execute($SQL,
$bind_array);
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:
$db->SetFetchMode (ADODB_FETCH_NUM);
-- or --
$db->SetFetchMode(ADODB_FETCH_ASSOC)
;
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.
#!/usr/local/bin/php
<?php
require('adodb/adodb.inc.php');
require('adodb/adodb-exceptions.inc.php');
$DSN="oci8://scott:tiger@local";
try {
$db = NewADOConnection("$DSN");
$SQL="select ename,job,empno,sal
from emp
where job like :job";
$rs = $db->Execute($SQL, array("JOB"=>"C%"));
$nfields=$rs->FieldCount() ;
}
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();
}
?>
The most important part of the code is the while
loop which demonstrates how to loop through a result set returned by
Oracle RDBMS. This should be very
familiar to any programmer who has ever used Microsoft ADO.
The while loop tests for the EOF condition, which
becomes TRUE when there is no more data to be fetched and moves
forward within the loop by using the MoveNext()method. These iterator methods should not be used in the
same loop with the fetchRow()
and fetchInto() methods because the result set would lose track
of its position. The MoveNext() method does an automatic fetch.
When executing the script above, the result looks like the following:
$
./example26.php
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
$ncols=$sth->FieldCount();
echo "This result has ".$ncols." columns\n";
foreach (range(0,$ncols-1) as $ind) {
$col=$sth->FetchField($ind);
echo $col->name."\t";
}
echo "\n";
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:
#!/usr/local/bin/php
<?php
require('adodb/adodb.inc.php');
$db = NewADOConnection("oci8");
$db->Connect($_ENV["TWO_TASK"], "scott", "tiger");
$rs = $db->Execute("select * from emp where rownum<0");
$nfields=$rs->FieldCount() ;
for($i=0;$i<$nfields;$i++) {
$col=$rs->FetchField($i);
echo "Column name:".$col->name." ";
echo "Column type:".$col->type." ";
echo "Column length:".$col->max_length." ";
echo "Not Null:". $col->not_null ? 'Y':'N',"\n";
}
?>
When executed, this code, which is nothing more
then a PHP version of the SQL*Plus “describe” command, gives the
following result:
$ ./example27.php
Column name:EMPNO
Column type:INT Column length:22 Not Null:N
Column name:ENAME Column type:VARCHAR
Column length:10 Not Null:N
Column name:JOB Column type:VARCHAR Column length:9 Not Null:N
Column name:MGR Column type:INT Column length:22 Not Null:N
Column name:HIREDATE Column type:DATE Column length:7 Not
Null:N
Column name:SAL Column type:NUMBER Column length:22 Not Null:N
Column name:COMM Column type:NUMBER Column length:22 Not
Null:N
Column name:DEPTNO Column type:INT Column length:22 Not Null:N
All columns are described as nullable, though
Oracle’s SQL*Plus utility disagrees:
SQL> desc emp
Name Null? Type
--------------- -------- ----------------------------
EMPNO NOT NULL
NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
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
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|