 |
|
ss
Oracle Tips by Burleson |
Auxiliary OCI8 Functions
There are several more functions that describe
result sets and the values fetched. Here they are, directly from the
on-line manual:
DESCRIPTION
oci_num_fields()
returns the number of columns in the statement.
oci_num_rows()
returns number of rows affected during statement execution.
oci_field_name()
returns the name of the field corresponding to the field number
(1-based).
oci_set_prefetch--Sets
number of rows to be pre-fetched
These functions, all except the last one, are used
to describe results of a query execution. Information can be learned
regarding how many columns were requested in the query (oci_num_fields()),
the names of those columns (oci_field_name()) and how many rows
have been fetched so far (oci_num_rows()).
It is important to emphasize that oci_num_rows()
does not return the number of rows returned by the query, but the
number of rows fetched so far. The oci_num_rows() function is
the PHP implementation of the %ROWCOUNT cursor attribute in PL/SQL. To
determine the number of rows in the result set, the Oracle count
function should be called.
The oci_num_rows() function is pure performance
improvement. It simply defines how many rows have been fetched with
each trip to the database. It is the PHP implementation of the feature
that Oracle Corp. calls “Oracle Array Interface” and is another
performance optimization available with the OCI8 module.
Now, what can be done with functions that describe
a result set? These functions give a great deal of flexibility and
can be used in a function like the following:
<?php
function displayQueryAsTable($sth,$table_attr)
{
$ncols=oci_num_fields($sth);
?>
<table <?=$table_attr?>>
<tr align="center"
valign="top"
style="background-color:
#ADD8E6; ">
<?php
for ($i=1;$i<=$ncols;$i++) {
$col=oci_field_name($sth,$i); ?>
<th><?=$col?></th>
<?php } ?>
</tr>
<?php
oci_set_prefetch($sth,32);
while ($row=oci_fetch_array($sth,OCI_NUM)) {
print "<tr>\n";
foreach($row as $val) {
printf("<td>%s</td>\n",$val);
}
print "</tr>\n";
}
print "</table>";
}
?>
This function takes a statement handle, describes
it and displays it as a HTML table. All it needs is an executed
statement handle. A closer look at Example 13a is merited:
<html>
<head>
<title>Query1</title>
</head>
<body>
<center>
<?php
require_once('OCI_Session.php');
require_once('displayQueryAsTable.php');
session_start();
$dbh=$_SESSION['dbh'];
$SQL="select
e.ename,e.empno,d.dname,d.loc,e.sal
from emp e, dept d
where e.deptno=d.deptno
order by e.deptno,ename";
try {
$dbh->refresh();
if (!$dbh->db) {
$e=$dbh->err;
throw new Exception ('CONN:'.$e['message']);
}
$sth=oci_parse($dbh->db,$SQL);
if (!$sth) {
$e=oci_error($sth);
throw new Exception ('PARSE:'.$e['message']);
}
if (!oci_execute($sth)) {
$e=oci_error($sth);
throw new Exception ('EXEC:'.$e['message']);
}
}
catch (Exception $e) {
print $e->getMessage();
}
displayQueryAsTable($sth,'frame="border"
border="3"');
?>
What is so interesting about this example? Why
revisit it? Now that the new utility function is implemented, there is
nothing query-specific in this example. The previous version had
explicit column titles and $row array requiring knowledge of
the number of columns returned by the query.
This example does not require anything. It would
work for any working query substituted in the $SQL variable. A
complete template is presented to produce quick, single SQL statement
reports from the database, using nothing more than a cut & paste.
Having this information available allows the user
to quickly assemble little applications using only PHP and OCI
examples from this book alone.
See
code depot for complete scripts
The above book excerpt is from:
Easy Oracle
PHP
Create Dynamic Web Pages with Oracle Data
ISBN
0-9761573-0-6
Mladen Gogala
http://www.rampant-books.com/book_2005_2_php_oracle.htm
|
