Remote DBA Plans
Remote DBA Service

Querying Database and Fetching Rows

Oracle Tips by Burleson Consulting

Example 20 illustrates executing a query and fetching results. The following is a brief recap:

First, the SQL statement is executed in the variable $sql using the query() method from the DB connection class, getting a result set as a result. This is line 10 of example20.php:

$sth=$db->query($sql);

Then, the resulting result set (which is not the same as an Oracle cursor) is examined using the tableInfo() method from the DB connection class on line 14:

$cols=$db->tableInfo($sth);

Finally, the result set $sth is fetched into a variable called $row:

while ($sth->fetchinto($row))

This is the normal flow of the programming logic. PEAR result sets are objects, belonging to a class and have methods such as fetchinto().

This is precisely the reason it is not possible to return a cursor and use it as a “statement handle” in the same fashion shown in the OCI8 module example. PEAR DB does not support the use of dynamic cursors.

The SQL statement does not need to be parsed and executed in two separate steps; the query() method does it all. If the prepare step is skipped, how is the bind operation performed if needed? In regards to the OCI8 module, Example 15 presents the bind operation in which all records are selected from the EMP table satisfying the condition “where job like 'C%'. There is a placeholder “:job” in the SQL statement and a variable $job which is bound to it. The SQL command looks like the following:

    $SQL="select ename,job,hiredate,sal
          from   emp
          where job like :job
          order by sal";

Using the PEAR DB module, Example 15 is rewritten as follows:

Example 21

#!/usr/local/bin/php
<?php
require_once('DB.php');
$DSN="oci8://scott:tiger@local";
$db=DB::connect($DSN);
if (DB::iserror($db)) {
   die($db->getUserInfo());
}
// Without the following, the numRows() method
// wouldn't work
$db->setOption('portability',DB_PORTABILITY_NUMROWS);
$SQL="select ename,job,hiredate,sal
      from   emp
      where job like ?
      order by sal";
$sth=$db->query($SQL,array("C%"));
if (DB::iserror($sth)) {
   die($sth->getUserInfo());
}
// Number of rows needs to be computed
// before the 1st fetch
$nr=$sth->numRows();
while ($sth->fetchinto($row)) {
      foreach ($row as $val) {
              echo "$val\t";
      }
      echo "\n";
}
echo "This statement returned $nr rows.\n";
?>

When executed, this example outputs exactly the same as the output of Example 15 in Chapter 3:

$ example21.php
SMITH   CLERK   17-DEC-80       800
JAMES   CLERK   03-DEC-81       950
ADAMS   CLERK   23-MAY-87       1100
MILLER CLERK   23-JAN-82       1300
This statement returned 4 rows.

The first thing to note is that a question mark “?” is used for the placeholder and that an array is used as an argument to the query method. The explanation for this is that PEAR DB does not use named binds as OCI8; it uses positional binds.

PEAR DB counts question marks in the SQL statement and replaces each of them with a bind variable such as :1, :2, :3, or whatever the sequence number. The query method then takes the given array and binds the nth member of the array to the created :n placeholder. In other words, the array elements are bound to placeholders according to their position, not their names; thus, the name “positional binds”.

Positional binds are much simpler than the named binds, but they have their shortcomings as well. For the SQL statement below, one named bind and two positional binds are needed:

select ename,job, hiredate
from emp
where hiredate between :hdate and :hdate+30

The only way to rewrite this where condition using the positional bind is shown below:

where hiredate between ? and ?+30

This means the invocation of the query method will look like the following:

$db->query($SQL, array($hdate,$hdate));

This is not very pretty and is a weakness of the positional binding. Yes, question marks need to be counted. Although PEAR reports an error, it does not execute the query if the number of bind values is different from the number of placeholders.

What about the types? Although, REF cursors cannot be used for bind variables, everything else, without special considerations about the types can be used. This means a user does not have to perform any special descriptor allocation to work with LOB variables, but it also means the useful methods such as lob->savefile used when loading a LOB object into database cannot be utilized. This is referenced in Chapter 3, Example 18.

The next example retrieves the LOB that is stored in the database in Chapter 3. To do this, the form used for logging in must be modified. Throughout Chapter 3, the OCI_Session class and the Example13a form to log in is used. PEAR DB is already an object superstructure on top of the PHP OCI8 module, so the OCI_Session class should be forgotten when dealing with PEAR DB.

Example 22, shown below is Example 13a modified

<html>
<head>
<title>Example 22</title>
</head>
<body>
<?php
require_once('DB.php');
require('login_form.php');
session_start();
if (!isset($_POST['user'])) {
            login_form('SCOTT');
}
else {
     try {
                if (empty($_POST['database'])) {
                    $_POST['database']=$_ENV['TWO_TASK'];
                }
                $DSN=array('phptype'=> 'oci8',
                                     'username'=>$_POST['user'],
                                     'password'=>$_POST['passwd'],
                                  'database'=>$_POST['database']);
                if (DB::isError($db))
                  throw new
                      exception($db->getUserInfo());
                   else {
                   $_SESSION['DSN']=$DSN;
                   header('Location: query2.php');
                }
         }
      catch (exception $e) {
      ?>
      <center>
      <?php
         echo "Exception:".$e->getMessage();=
login_form($_POST['user']);
      ?><br>
      </center>
<?php }}?>
</body>
</html>

This example, when accessed from a browser, presents the standard login form used to establish an Oracle session. The header() function is used the same way as in Chapter 3 to redirect the browsers to the target file.

Please note that a session variable is being used to share DSN and not an OCI_Session entity. This means the examples from Chapter 3 cannot be used with the form above. In order to retrieve the file, a new script called query2.php must be created. Before doing so, information present in the database should be sought:

SQL> desc poetry
Name                                      Null?    Type
----------------------------------------- -------- ----------------
FILE_ID                                   NOT NULL NUMBER(5)
FILE_DESC                                 NOT NULL VARCHAR2(255)
FILE_DATA                                          CLOB
SQL> select file_id,file_desc from poetry
2 order by file_id
3 /

   FILE_ID FILE_DESC
---------- ---------------------------------------------------------                  1 E.A. Poe, "The Raven"
         2 R. Frost, "Stopping by woods on snowy evening"
         3 M. Python, "Life of Brian"

The Raven is uploaded, so this is the information retrieved. In the next section, the original example is re-written to upload the file using PEAR DB instead of OCI8.

The next file to rewrite is query1.php which, in Chapter 3, is the file actually performing the query. The new file, written using PEAR DB is named query2.php. The query1.php, in its finalincarnation calls the displayQueryAsTable()function to display results as a table. This function is written using OCI8, and has to be adjusted for PEAR DB.

This time, the function present is used multiple times, outside of this chapter, so a shorter name is merited; csr2html(). This function is written as follows:

Example 22 (cont)

<?php
require_once "HTML/Table.php";
function csr2html($db,$sth) {
   $ncols=$sth->numCols();
   $cols=$db->tableInfo($sth);
   $tableAttrs = array("rules" => "rows,cols",
                                 "border" => "3");
   $hattr=array("style" => "background-color: #ADD8E6");
   $table = new HTML_Table($tableAttrs);
   $table -> setAutoGrow(true);
   $table -> setAutoFill("n/a");
   for($i=0;$i<$ncols;$i++) {
      $table->setHeaderContents(0,$i,$cols[$i]['name']);
   }
   $table->setRowAttributes(0,$hattr);
   while ($row=$sth->fetchRow()) {
      $table->addRow($row);
   }
   ?>
   <center>
   <?=$table->toHTML()?>
   </center>
<?php
}
?>

This function contains an inclusion of the file HTML/Table.php, a part of the PEAR module HTML_Table and serves for creating HTML tables. The PEAR module HTML_Table is briefly explained at the end of this chapter.

There are several differences with this function from the function in Chapter 3. First, this one takes a database connection as an argument in addition to the statement handle, while the OCI8 version takes only the statement handle. Why is this? The column names need displayed prominently as the table headers. In order to find table names, the tableInfo() method is used. It is not a part of the result set/statement handle class, but rather a part of the DB connection class. Therefore, the DB connection is needed as an argument.

Second, the table has a default value for the empty cells; the string “n/a”.

Finally, after all this rewriting, the following example is query2.php written out:

Example 22 (cont)

<html>
<head>
<title>Query2</title>
</head>
<body>
<center>
<?php
require_once('DB.php');
include_once('csr2html.php');
session_start();
$DSN=$_SESSION['DSN'];
$db=DB::connect($DSN);
$db->setOption('portability',DB_PORTABILITY_NUMROWS);

$SQL="select file_data
           from poetry
           where file_id=1";
try {
      $sth=$db->query($SQL);
      if (DB::isError($sth)) {
          throw new Exception ($sth->getUserInfo());
      }
}
catch (Exception $e) {
       die($e->getMessage());
}
csr2html($db,$sth);
?>
</center>
</body>
</html>

This example from Chapter 3 is rewritten to use the PEAR DB module instead of the OCI8 module. The only interesting twist is that it selects a LOB column instead of the usual suspects from the EMP table. It does not have any special handling for the LOB column, it just selects it. When executed, this example displays the famous poem in the now familiar table shape:

SEE CODE DEPOT FOR FULL SCRIPTS