BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

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.

Example 26

#!/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

Example 25 is reviewed as follows:

$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:

Example 27

#!/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


The above book excerpt is from  "Easy Oracle PHP: Creating Dynamic Web Pages with Oracle Data". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning 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.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter