[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Phpgroupware-cvs] phpgwapi/inc/adodb/docs/docs-oracle.htm, 1.1.2.1
|
From: |
nomail |
|
Subject: |
[Phpgroupware-cvs] phpgwapi/inc/adodb/docs/docs-oracle.htm, 1.1.2.1 |
|
Date: |
Thu, 30 Dec 2004 05:51:48 +0100 |
Update of /phpgwapi/inc/adodb/docs
Added Files:
Branch: proposed-0_9_18-branch
docs-oracle.htm
date: 2004/12/30 04:51:48; author: skwashd; state: Exp; lines: +518 -0
Log Message:
switch to ADOdb
=====================================================================
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<style>
pre {
background-color: #eee;
padding: 0.75em 1.5em;
font-size: 12px;
border: 1px solid #ddd;
}
.greybg {
background-color: #eee;
padding: 0.75em 1.5em;
font-size: 12px;
border: 1px solid #ddd;
}
</style>
<title>ADOdb with PHP and Oracle</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</style>
</head>
<body>
<table width=100%><tr><td>
<h2>Using ADOdb with PHP and Oracle: an advanced tutorial</h2>
</td><td><div align="right"><img src=cute_icons_for_site/adodb.gif width="88"
height="31"></div></tr></table>
<p><font size="1">(c)2004 John Lim. All rights reserved.</font></p>
<h3>1. Introduction</h3>
<p>Oracle is the most popular commercial database used with PHP. There are many
ways of accessing Oracle databases in PHP. These include:</p>
<ul>
<li>The oracle extension</li>
<li>The oci8 extension</li>
<li>PEAR DB library</li>
<li>ADOdb library</li>
</ul>
<p>The wide range of choices is confusing to someone just starting with Oracle
and PHP. I will briefly summarize the differences, and show you the advantages
of using <a href="http://adodb.sourceforge.net/";>ADOdb</a>. </p>
<p>First we have the C extensions which provide low-level access to Oracle
functionality. These C extensions are precompiled into PHP, or linked in
dynamically when the web server starts up. Just in case you need it, here's a
<a
href=http://www.oracle.com/technology/tech/opensource/php/apache/inst_php_apache_linux.html>guide
to installing Oracle and PHP on Linux</a>.</p>
<table width="75%" border="1" align="center">
<tr valign="top">
<td nowrap><b>Oracle extension</b></td>
<td>Designed for Oracle 7 or earlier. This is obsolete.</td>
</tr>
<tr valign="top">
<td nowrap><b>Oci8 extension</b></td>
<td> Despite it's name, which implies it is only for Oracle 8i, this is the
standard method for accessing databases running Oracle 8i, 9i or 10g (and
later).</td>
</tr>
</table>
<p>Here is an example of using the oci8 extension to query the <i>emp</i> table
of the <i>scott</i> schema with bind parameters:
<pre>
$conn = OCILogon("scott","tiger", $tnsName);
$stmt = OCIParse($conn,"select * from emp where empno > :emp order by empno");
$emp = 7900;
OCIBindByName($stmt, ':emp', $emp);
$ok = OCIExecute($stmt);
while (OCIFetchInto($stmt,$arr)) {
print_r($arr);
echo "<hr>";
}
</pre>
<p>This generates the following output:
<div class=greybg>
Array ( [0] => 7902 [1] => FORD [2] => ANALYST [3] => 7566 [4] => 03/DEC/81 [5]
=> 3000 [7] => 20 )
<hr>
Array ( [0] => 7934 [1] => MILLER [2] => CLERK [3] => 7782 [4] => 23/JAN/82
[5] => 1300 [7] => 10 )
</div>
<p>We also have many higher level PHP libraries that allow you to simplify the
above code. The most popular are <a href="http://pear.php.net/";>PEAR DB</a> and
<a href="http://adodb.sourceforge.net/";>ADOdb</a>. Here are some of the
differences between these libraries:</p>
<table width="75%" border="1" align="center">
<tr>
<td><b>Feature</b></td>
<td><b>PEAR DB 1.6</b></td>
<td><b>ADOdb 4.52</b></td>
</tr>
<tr valign="top">
<td>General Style</td>
<td>Simple, easy to use. Lacks Oracle specific functionality.</td>
<td>Has multi-tier design. Simple high-level design for beginners, and also
lower-level advanced Oracle functionality.</td>
</tr>
<tr valign="top">
<td>Support for Prepare</td>
<td>Yes, but only on one statement, as the last prepare overwrites previous
prepares.</td>
<td>Yes (multiple simultaneous prepare's allowed)</td>
</tr>
<tr valign="top">
<td>Support for LOBs</td>
<td>No</td>
<td>Yes, using update semantics</td>
</tr>
<tr valign="top">
<td>Support for REF Cursors</td>
<td>No</td>
<td>Yes</td>
</tr>
<tr valign="top">
<td>Support for IN Parameters</td>
<td>Yes</td>
<td>Yes</td>
</tr>
<tr valign="top">
<td>Support for OUT Parameters</td>
<td>No</td>
<td>Yes</td>
</tr>
<tr valign="top">
<td>Schema creation using XML</td>
<td>No</td>
<td>Yes, including ability to define tablespaces and constraints</td>
</tr>
<tr valign="top">
<td>Provides database portability features</td>
<td>No</td>
<td>Yes, has some ability to abstract features that differ between
databases such as dates, bind parameters, and data types.</td>
</tr>
<tr valign="top">
<td>Performance monitoring and tracing</td>
<td>No</td>
<td>Yes. SQL can be traced and linked to web page it was executed on.
Explain plan support included.</td>
</tr>
<tr valign="top">
<td>Recordset caching for frequently used queries</td>
<td>No</td>
<td>Yes. Provides great speedups for SQL involving complex <i>where,
group-by </i>and <i>order-by</i> clauses.</td>
</tr>
<tr valign="top">
<td>Popularity</td>
<td>Yes, part of PEAR release</td>
<td>Yes, many open source projects are using this software, including
PostNuke, Xaraya, Mambo, Tiki Wiki.</td>
</tr>
<tr valign="top">
<td>Speed</td>
<td>Medium speed.</td>
<td>Very high speed. Fastest database abstraction library available for
PHP. <a href="http://phplens.com/lens/adodb/";>Benchmarks are available</a>.</td>
</tr>
<tr valign="top">
<td>High Speed Extension available</td>
<td>No</td>
<td>Yes. You can install the ADOdb extension, which implements the most
frequently used parts of ADOdb as fast C code.</td>
</tr>
</table>
<p> PEAR DB is good enough for simple web apps. But if you need more power, you
can see ADOdb offers more sophisticated functionality. The rest of this article
will concentrate on using ADOdb with Oracle. You can find out more about <a
href="#connecting">connecting to Oracle</a> later in this guide.</p>
<h4>ADOdb Example</h4>
<p>In ADOdb, the above oci8 example querying the <i>emp</i> table could be
written as:</p>
<pre>
include "/path/to/adodb.inc.php";
$db = NewADOConnection("oci8");
$db->Connect($tnsName, "scott", "tiger");
$rs = $db->Execute("select * from emp where empno>:emp order by empno",
array('emp' => 7900));
while ($arr = $rs->FetchRow()) {
print_r($arr);
echo "<hr>";
}
</pre>
<p>The Execute( ) function returns a recordset object, and you can retrieve the
rows returned using $recordset->FetchRow( ). </p>
<p>If we ignore the initial connection preamble, we can see the ADOdb version
is much easier and simpler:</p>
<table width="100%" border="1">
<tr valign="top" bgcolor="#FFFFFF">
<td width="50%" bgcolor="#e0e0e0"><b>Oci8</b></td>
<td bgcolor="#e0e0e0"><b>ADOdb</b></td>
</tr>
<tr valign="top" bgcolor="#CCCCCC">
<td><pre><font size="1">$stmt = <b>OCIParse</b>($conn,
"select * from emp where empno > :emp");
$emp = 7900;
<b>OCIBindByName</b>($stmt, ':emp', $emp);
$ok = <b>OCIExecute</b>($stmt);
while (<b>OCIFetchInto</b>($stmt,$arr)) {
print_r($arr);
echo "<hr>";
} </font></pre></td>
<td><pre><font size="1">$recordset = $db-><b>Execute</b>("select * from emp
where empno>:emp",
array('emp' => 7900));
while ($arr = $recordset-><b>FetchRow</b>()) {
print_r($arr);
echo "<hr>";
}</font></pre></td>
</tr>
</table>
<p> </p>
<h3>2. ADOdb Query Semantics</h3>
<p>You can also query the database using the standard Microsoft ADO MoveNext( )
metaphor. The data array for the current row is stored in the <i>fields</i>
property of the recordset object, $rs.
MoveNext( ) offers the highest performance among all the techniques for
iterating through a recordset:
<pre>
$rs = $db->Execute("select * from emp where empno>:emp", array('emp' => 7900));
while (!$rs->EOF) {
print_r($rs->fields);
$rs->MoveNext();
}
</pre>
<p>And if you are interested in having the data returned in a 2-dimensional
array, you can use:
<pre>
$arr = $db->GetArray("select * from emp where empno>:emp", array('emp' =>
7900));
</pre>
<p>Now to obtain only the first row as an array:
<pre>
$arr = $db->GetRow("select * from emp where empno=:emp", array('emp' => 7900));
</pre>
<p>Or to retrieve only the first field of the first row:
<pre>
$arr = $db->GetOne("select ename from emp where empno=:emp", array('emp' =>
7900));
</pre>
<p>For easy pagination support, we provide the SelectLimit function. The
following will perform a select query, limiting it to 100 rows, starting from
row 200:
<pre>
$offset = 200; $limitrows = 100;
$rs = $db->SelectLimit('select * from table', $offset, $limitrows);
</pre>
<p>The $limitrows parameter is optional.
<h4>Array Fetch Mode</h4>
<p>When data is being returned in an array, you can choose the type of array
the data is returned in.
<ol>
<li> Numeric indexes - use <font size="2" face="Courier New, Courier,
mono">$connection->SetFetchMode(ADODB_FETCH_NUM).</font></li>
<li>Associative indexes - the keys of the array are the names of the fields
(in upper-case). Use <font size="2" face="Courier New, Courier,
mono">$connection->SetFetchMode(ADODB_FETCH_ASSOC)</font><font face="Courier
New, Courier, mono">.</font></li>
<li>Both numeric and associative indexes - use <font size="2" face="Courier
New, Courier, mono">$connection->SetFetchMode(ADODB_FETCH_BOTH).</font></li>
</ol>
<p>The default is ADODB_FETCH_BOTH for Oracle.</p>
<h4><b>Caching</b></h4>
<p>You can define a database cache directory using $ADODB_CACHE_DIR, and cache
the results of frequently used queries that rarely change. This is particularly
useful for SQL with complex where clauses and group-by's and order-by's. It is
also good for relieving heavily-loaded database servers.</p>
<p>This example will cache the following select statement for 3600 seconds (1
hour):</p>
<pre>
$ADODB_CACHE_DIR = '/var/adodb/tmp';
$rs = $db->CacheExecute(3600, "select names from allcountries order by 1");
</pre>
There are analogous CacheGetArray(
), CacheGetRow( ), CacheGetOne( ) and CacheSelectLimit( ) functions. The
first parameter is the number of seconds to cache. You can also pass a bind
array as a 3rd parameter (not shown above).
<p>There is an alternative syntax for the caching functions. The first
parameter is omitted, and you set the cacheSecs
property of the connection object:
<pre>
$ADODB_CACHE_DIR = '/var/adodb/tmp';
$connection->cacheSecs = 3600;
$rs = $connection->CacheExecute($sql, array('id' => 1));
</pre>
<h3> </h3>
<h3>3. Using Prepare( ) For Frequently Used Statements</h3>
<p>Prepare( ) is for compiling frequently used SQL statement for reuse. For
example, suppose we have a large array which needs to be inserted into an
Oracle database. The following will result in a massive speedup in query
execution (at least 20-40%), as the SQL statement only needs to be compiled
once:</p>
<pre>
$stmt = $db->Prepare('insert into table (field1, field2) values (:f1, :f2)');
foreach ($arrayToInsert as $key => $value) {
$db->Execute($stmt, array('f1' => $key, 'f2' => $val);
}
</pre>
<p> </p>
<h3>4. Working With LOBs</h3>
<p>Oracle treats data which is more than 4000 bytes in length specially. These
are called Large Objects, or LOBs for short. Binary LOBs are BLOBs, and
character LOBs are CLOBs. In most Oracle libraries, you need to do a lot of
work to process LOBs, probably because Oracle designed it to work in systems
with little memory. ADOdb tries to make things easy by assuming the LOB can fit
into main memory. </p>
<p>ADOdb will transparently handle LOBs in <i>select</i> statements. The LOBs
are automatically converted to PHP variables without any special coding.</p>
<p>For updating records with LOBs, the functions UpdateBlob( ) and UpdateClob(
) are provided. Here's a BLOB example. The parameters should be
self-explanatory:
<pre>
$ok = $db->Execute("insert into aTable (id, name, ablob)
values (aSequence.nextVal, 'Name', null)");
if (!$ok) return LogError($db->ErrorMsg());
<font color="#006600"># params: $tableName, $blobFieldName, $blobValue,
$whereClause</font>
$db->UpdateBlob('aTable', 'ablob', $blobValue, 'id=aSequence.currVal');
</pre>
<p>and the analogous CLOB example:
<pre>
$ok = $db->Execute("insert into aTable (id, name, aclob)
values (aSequence.nextVal, 'Name', null)");
if (!$ok) return LogError($db->ErrorMsg());
$db->UpdateClob('aTable', 'aclob', $clobValue, 'id=aSequence.currVal');
</pre>
<p>Note that LogError( ) is a user-defined function, and not part of ADOdb.
<p>
<h3>5. REF CURSORs</h3>
<p>Oracle recordsets can be passed around as variables called REF Cursors. For
example, in PL/SQL, we could define a function <i>open_tab</i> that returns a
REF CURSOR in the first parameter:</p>
<pre>
TYPE TabType IS REF CURSOR RETURN TAB%ROWTYPE;
PROCEDURE open_tab (tabcursor IN OUT TabType,tablenames IN VARCHAR) IS
BEGIN
OPEN tabcursor FOR SELECT * FROM TAB WHERE tname LIKE
tablenames;
END open_tab;
</pre>
<p>In ADOdb, we could access this REF Cursor using the ExecuteCursor()
function. The following will find
all table names that begin with 'A' in the current schema:
<pre>
$rs = $db->ExecuteCursor("BEGIN open_tab(:refc,'A%'); END;",'refc');
while ($arr = $rs->FetchRow()) print_r($arr);
</pre>
<p>The first parameter is the PL/SQL statement, and the second parameter is the
name of the REF Cursor.
</p>
<p> </p>
<h3>6. In and Out Parameters</h3>
<p>The following PL/SQL
stored procedure requires an input variable, and returns a result into an
output variable:
<pre>
PROCEDURE data_out(input IN VARCHAR, output OUT VARCHAR) IS
BEGIN
output := 'I love '||input;
END;
</pre>
<p>The following ADOdb code allows you to call the stored procedure:</p>
<pre>
$stmt = $db->PrepareSP("BEGIN adodb.data_out(:a1, :a2); END;");
$input = 'Sophia Loren';
$db->InParameter($stmt,$input,'a1');
$db->OutParameter($stmt,$output,'a2');
$ok = $db->Execute($stmt);
if ($ok) echo ($output == 'I love Sophia Loren') ? 'OK' : 'Failed';
</pre>
<p>PrepareSP( ) is a special function that knows about bind parameters.
The main limitation currently is that IN OUT parameters do not work.
<h4>Bind Parameters and REF CURSORs</h4>
<p>We could also rewrite the REF CURSOR example to use InParameter (requires
ADOdb 4.53 or later):
<pre>
$stmt = $db->PrepareSP("BEGIN adodb.open_tab(:refc,:tabname); END;");
$input = 'A%';
$db->InParameter($stmt,$input,'tabname');
$rs = $db->ExecuteCursor($stmt,'refc');
while ($arr = $rs->FetchRow()) print_r($arr);
</pre>
<h4>Bind Parameters and LOBs</h4>
<p>You can also operate on LOBs. In this example, we have IN and OUT parameters
using CLOBs.
<pre>
$text = 'test test test';
$sql = "declare rs clob; begin :rs := lobinout(:sa0); end;";
$stmt = $conn -> PrepareSP($sql);
$conn -> InParameter($stmt,$text,'sa0', -1, OCI_B_CLOB); # -1 means
variable length
$rs = '';
$conn -> OutParameter($stmt,$rs,'rs', -1, OCI_B_CLOB);
$conn -> Execute($stmt);
echo "return = ".$rs."<br>";
</pre>
<p>Similarly, you can use the constant OCI_B_BLOB to indicate that you are
using BLOBs.
<h4>Reusing Bind Parameters with CURSOR_SHARING=FORCE</h4>
<p>Many web programmers do not care to use bind parameters, and prefer to enter
the SQL directly. So instead of:</p>
<pre>
$arr = $db->GetArray("select * from emp where empno>:emp", array('emp' =>
7900));
</pre>
<p>They prefer entering the values inside the SQL:
<pre>
$arr = $db->GetArray("select * from emp where empno>7900");
</pre>
<p>This reduces Oracle performance because Oracle will reuse compiled SQL which
is identical to previously compiled SQL. The above example with the values
inside the SQL
is unlikely to be reused. As an optimization, from Oracle 8.1 onwards, you can
set the following session parameter after you login:
<pre>
ALTER SESSION SET CURSOR_SHARING=FORCE
</pre>
<p>This will force Oracle to convert all such variables (eg. the 7900 value)
into constant bind parameters, improving SQL reuse.</p>
<p>More <a
href="http://phplens.com/adodb/code.initialization.html#speed";>speedup
tips</a>.</p>
<p> </p>
<h3>7. Dates and Datetime in ADOdb</h3>
<p>There are two things you need to know about dates in ADOdb. </p>
<p>First, to ensure cross-database compability, ADOdb assumes that dates are
returned in ISO format (YYYY-MM-DD H24:MI:SS).</p>
<p>Secondly, since Oracle treats dates and datetime as the same data type, we
decided not to display the time in the default date format. So on login, ADOdb
will set the NLS_DATE_FORMAT to 'YYYY-MM-DD'. If you prefer to show the date
and time by default, do this:</p>
<pre>
$db = NewADOConnection('oci8');
$db->NLS_DATE_FORMAT = 'RRRR-MM-DD HH24:MI:SS';
$db->Connect($tns, $user, $pwd);
</pre>
<p>Or execute:</p>
<pre>$sql = "ALTER SESSION SET NLS_DATE_FORMAT = 'RRRR-MM-DD
HH24:MI:SS'";
$db->Execute($sql);
</pre>
<p>If you are not concerned about date portability and do not use ADOdb's
portability layer, you can use your preferred date format instead.
<p>
<h3>8. Database Portability Layer</h3>
<p>ADOdb provides the following functions for portably generating SQL functions
as strings to be merged into your SQL statements:</p>
<table width="75%" border="1" align=center>
<tr>
<td width=30%><b>Function</b></td>
<td><b>Description</b></td>
</tr>
<tr>
<td>DBDate($date)</td>
<td>Pass in a UNIX timestamp or ISO date and it will convert it to a date
string formatted for INSERT/UPDATE</td>
</tr>
<tr>
<td>DBTimeStamp($date)</td>
<td>Pass in a UNIX timestamp or ISO date and it will convert it to a
timestamp
string formatted for INSERT/UPDATE</td>
</tr>
<tr>
<td>SQLDate($date, $fmt)</td>
<td>Portably generate a date formatted using $fmt mask, for use in SELECT
statements.</td>
</tr>
<tr>
<td>OffsetDate($date, $ndays)</td>
<td>Portably generate a $date offset by $ndays.</td>
</tr>
<tr>
<td>Concat($s1, $s2, ...)</td>
<td>Portably concatenate strings. Alternatively, for mssql use mssqlpo
driver,
which allows || operator.</td>
</tr>
<tr>
<td>IfNull($fld, $replaceNull)</td>
<td>Returns a string that is the equivalent of MySQL IFNULL or Oracle
NVL.</td>
</tr>
<tr>
<td>Param($name)</td>
<td>Generates bind placeholders, using ? or named conventions as
appropriate.</td>
</tr>
<tr><td>$db->sysDate</td><td>Property that holds the SQL function that
returns today's date</td>
</tr>
<tr><td>$db->sysTimeStamp</td><td>Property that holds the SQL function that
returns the current
timestamp (date+time).
</td>
</tr>
<tr>
<td>$db->concat_operator</td><td>Property that holds the concatenation operator
</td>
</tr>
<tr><td>$db->length</td><td>Property that holds the name of the SQL strlen
function.
</td></tr>
<tr><td>$db->upperCase</td><td>Property that holds the name of the SQL
strtoupper function.
</td></tr>
<tr><td>$db->random</td><td>Property that holds the SQL to generate a random
number between 0.00 and 1.00.
</td>
</tr>
<tr><td>$db->substr</td><td>Property that holds the name of the SQL substring
function.
</td></tr>
</table>
<p>ADOdb also provides multiple oracle oci8 drivers for different scenarios:</p>
<table width="75%" border="1" align="center">
<tr>
<td nowrap><b>Driver Name</b></td>
<td><b>Description</b></td>
</tr>
<tr>
<td>oci805 </td>
<td>Specifically for Oracle 8.0.5. This driver has a slower SelectLimit(
).</td>
</tr>
<tr>
<td>oci8</td>
<td>The default high performance driver. The keys of associative arrays
returned in a recordset are upper-case.</td>
</tr>
<tr>
<td>oci8po</td>
<td> The portable Oracle driver. Slightly slower than oci8. This driver
uses ? instead of :<i>bindvar</i> for binding variables, which is the standard
for other databases. Also the keys of associative arrays are in lower-case
like other databases.</td>
</tr>
</table>
<p>Here's an example of calling the <i>oci8po</i> driver. Note that the bind
variables use question-mark:</p>
<pre>$db = NewADOConnection('oci8po');
$db->Connect($tns, $user, $pwd);
$db->Execute("insert into atable (f1, f2) values (?,?)", array(12,
'abc'));</pre>
<p> <a name=connecting></a>
<h3>9. Connecting to Oracle</h3>
<p>Before you can use ADOdb, you need to have the Oracle client installed and
setup the oci8 extension. This extension comes pre-compiled for Windows (but
you still need to enable it in the php.ini file). For information on compiling
the oci8 extension for PHP and Apache on Unix, there is an excellent guide at
<a
href="http://www.oracle.com/technology/tech/opensource/php/apache/inst_php_apache_linux.html";>oracle.com</a>.
</p>
<h4>Should You Use Persistent Connections</h4>
<p>One question that is frequently asked is should you use persistent
connections to Oracle. Persistent connections allow PHP to recycle existing
connections, reusing them after the previous web pages have completed.
Non-persistent connections close automatically after the web page has
completed. Persistent connections are faster because the cost of reconnecting
is expensive, but there is additional resource overhead. As an alternative,
Oracle allows you to pool and reuse server processes; this is called <a
href="http://www.cise.ufl.edu/help/database/oracle-docs/server.920/a96521/manproc.htm#13132";>Shared
Server</a> (also known as MTS).</p>
<p>The author's benchmarks suggest that using non-persistent connections and
the Shared Server configuration offer the best performance. If Shared Server
is not an option, only then consider using persistent connections.</p>
<h4>Connection Examples</h4>
<p>Just in case you are having problems connecting to Oracle, here are some
examples:</p>
<p>a. PHP and Oracle reside on the same machine, use default SID, with
non-persistent connections:</p>
<pre> $conn = NewADOConnection('oci8');
$conn->Connect(false, 'scott', 'tiger');</pre>
<p>b. TNS Name defined in tnsnames.ora (or ONAMES or HOSTNAMES), eg. 'myTNS',
using persistent connections:</p>
<pre> $conn = NewADOConnection('oci8');
$conn->PConnect(false, 'scott', 'tiger', 'myTNS');</pre>
<p>or</p>
<pre> $conn->PConnect('myTNS', 'scott', 'tiger');</pre>
<p>c. Host Address and SID</p>
<pre> $conn->Connect('192.168.0.1', 'scott', 'tiger', 'SID');</pre>
<p>d. Host Address and Service Name</p>
<pre> $conn->Connect('192.168.0.1', 'scott', 'tiger', 'servicename');</pre>
<p>e. Oracle connection string:
<pre> $cstr = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$host)(PORT=$port))
(CONNECT_DATA=(SID=$sid)))";
$conn->Connect($cstr, 'scott', 'tiger');
</pre>
<p>f. ADOdb data source names (dsn):
<pre>
$dsn = 'oci8://user:address@hidden/?persist'; # persist is optional
$conn = ADONewConnection($dsn); # no need for Connect/PConnect
$dsn = 'oci8://user:address@hidden/sid';
$conn = ADONewConnection($dsn);
$dsn = 'oci8://user:pwd@/'; # oracle on local machine
$conn = ADONewConnection($dsn);</pre>
<p>With ADOdb data source names,
you don't have to call Connect( ) or PConnect( ).
</p>
<p> </p>
<h3>10. Error Checking</h3>
<p>The examples in this article are easy to read but a bit simplistic because
we ignore error-handling. Execute( ) and Connect( ) will return false on error.
So a more realistic way to call Connect( ) and Execute( ) is:
<pre>function InvokeErrorHandler()
{<br>global $db; ## assume global
MyLogFunction($db->ErrorNo(), $db->ErrorMsg());
}
if (!$db->Connect($tns, $usr, $pwd)) InvokeErrorHandler();
$rs = $db->Execute("select * from emp where empno>:emp order by empno",
array('emp' => 7900));
if (!$rs) return InvokeErrorHandler();
while ($arr = $rs->FetchRow()) {
print_r($arr);
echo "<hr>";
}
</pre>
<p>You can retrieve the error message and error number of the last SQL
statement executed from ErrorMsg( ) and ErrorNo( ). You can also <a
href=http://phplens.com/adodb/using.custom.error.handlers.and.pear_error.html>define
a custom error handler function</a>.
ADOdb also supports throwing exceptions in PHP5.
<p> </p>
<h3>11. Other ADOdb Features</h3>
<p><a href="http://phplens.com/lens/adodb/docs-datadict.htm";>Schema
generation</a>. This allows you to define a schema using XML and import it into
different RDBMS systems portably.</p>
<p><a href="http://phplens.com/lens/adodb/docs-perf.htm";>Performance monitoring
and tracing</a>. Highlights of performance monitoring include identification of
poor and suspicious SQL, with explain plan support, and identifying which web
pages the SQL ran on.</p>
<p> </p>
<h3>12. Download</h3>
<p>You can <a href="http://adodb.sourceforge.net/#download";>download ADOdb from
sourceforge</a>. ADOdb uses a BSD style license. That means that it is free for
commercial use, and redistribution without source code is allowed.</p>
<p> </p>
<h3>13. Resources</h3>
<ul>
<li>Oracle's <a
href="http://www.oracle.com/technology/pub/articles/php_experts/index.html";>Hitchhiker
Guide to PHP</a></li>
<li>Oracle has an excellent <a
href="http://www.oracle.com/technology/tech/opensource/php/php_troubleshooting_faq.html";>FAQ
on PHP</a></li>
<li>PHP <a href="http://php.net/oci8";>oci8</a> manual pages</li>
<li><a href=http://phplens.com/lens/lensforum/topics.php?id=4>ADOdb
forums</a>.
</ul>
</body>
</html>
| [Prev in Thread] |
Current Thread |
[Next in Thread] |
- [Phpgroupware-cvs] phpgwapi/inc/adodb/docs/docs-oracle.htm, 1.1.2.1,
nomail <=