Update of /cvsroot/mantisbt/mantisbt/core/adodb/docs
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv20789
Modified Files:
tute.htm tips_portable_sql.htm readme.htm old-changelog.htm
docs-session.htm docs-perf.htm docs-oracle.htm
docs-datadict.htm docs-adodb.htm
Added Files:
docs-active-record.htm
Log Message:
Sync: V4.80 8 Mar 2006 of adodb
Index: docs-oracle.htm
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/adodb/docs/docs-oracle.htm,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -d -r1.3 -r1.4
--- docs-oracle.htm 12 Feb 2005 20:01:24 -0000 1.3
+++ docs-oracle.htm 22 Apr 2006 11:09:48 -0000 1.4
@@ -25,7 +25,7 @@
<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>
+<p><font size="1">(c)2004-2005 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>
@@ -48,21 +48,21 @@
</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);
+$conn = OCILogon("scott","tiger", $tnsName);
-$stmt = OCIParse($conn,"select * from emp where empno > :emp order by empno");
+$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>";
-}
+ 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] => 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>
@@ -135,7 +135,7 @@
<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>
+ <td>Yes. You can install the optional ADOdb extension, which reimplements
the most frequently used parts of ADOdb as fast C code. Note that the source
code version of ADOdb runs just fine without this extension, and only makes use
of the extension if detected.</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>
@@ -146,7 +146,7 @@
$db = NewADOConnection("oci8");
$db->Connect($tnsName, "scott", "tiger");
-$rs = $db->Execute("select * from emp where empno>:emp order by empno",
+$rs = $db->Execute("select * from emp where empno>:emp order by empno",
array('emp' => 7900));
while ($arr = $rs->FetchRow()) {
print_r($arr);
@@ -162,21 +162,18 @@
</tr>
<tr valign="top" bgcolor="#CCCCCC">
<td><pre><font size="1">$stmt = <b>OCIParse</b>($conn,
- "select * from emp where empno > :emp");
+ "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>";
+ echo "<hr>";
} </font></pre></td>
- <td><pre><font size="1">$recordset = $db-><b>Execute</b>("select * from
emp where empno>:emp",
+ <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>";
@@ -213,7 +210,7 @@
</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.
+<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>
@@ -251,7 +248,7 @@
<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)
+$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>
@@ -259,7 +256,7 @@
</pre>
<p>and the analogous CLOB example:
<pre>
-$ok = $db->Execute("insert into aTable (id, name, aclob)
+$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');
@@ -279,7 +276,7 @@
$db->StartTrans();
$ok = $db->Execute($stmt);
$db->CompleteTrans();
-</pre>
+</pre>
<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>
@@ -330,7 +327,7 @@
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.
+<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;";
@@ -375,38 +372,38 @@
<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
+<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>
+ <tr>
<td width=30%><b>Function</b></td>
<td><b>Description</b></td>
</tr>
- <tr>
+ <tr>
<td>DBDate($date)</td>
- <td>Pass in a UNIX timestamp or ISO date and it will convert it to a date
+ <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>
+ <tr>
<td>DBTimeStamp($date)</td>
- <td>Pass in a UNIX timestamp or ISO date and it will convert it to a
timestamp
+ <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>
+ <tr>
<td>SQLDate($date, $fmt)</td>
- <td>Portably generate a date formatted using $fmt mask, for use in SELECT
+ <td>Portably generate a date formatted using $fmt mask, for use in SELECT
statements.</td>
</tr>
- <tr>
+ <tr>
<td>OffsetDate($date, $ndays)</td>
<td>Portably generate a $date offset by $ndays.</td>
</tr>
- <tr>
+ <tr>
<td>Concat($s1, $s2, ...)</td>
- <td>Portably concatenate strings. Alternatively, for mssql use mssqlpo
driver,
+ <td>Portably concatenate strings. Alternatively, for mssql use mssqlpo
driver,
which allows || operator.</td>
</tr>
- <tr>
+ <tr>
<td>IfNull($fld, $replaceNull)</td>
<td>Returns a string that is the equivalent of MySQL IFNULL or Oracle
NVL.</td>
</tr>
@@ -475,7 +472,9 @@
<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>
+<pre>
+ $conn->connectSID = true;
+ $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:
@@ -487,10 +486,10 @@
<pre>
$dsn = 'oci8://user:pwd@tnsname/?persist'; # persist is optional
$conn = ADONewConnection($dsn); # no need for Connect/PConnect
-
+
$dsn = 'oci8://user:pwd@host/sid';
$conn = ADONewConnection($dsn);
-
+
$dsn = 'oci8://user:pwd@/'; # oracle on local machine
$conn = ADONewConnection($dsn);</pre>
<p>With ADOdb data source names,
@@ -505,7 +504,7 @@
}
if (!$db->Connect($tns, $usr, $pwd)) InvokeErrorHandler();
-$rs = $db->Execute("select * from emp where empno>:emp order by empno",
+$rs = $db->Execute("select * from emp where empno>:emp order by empno",
array('emp' => 7900));
if (!$rs) return InvokeErrorHandler();
while ($arr = $rs->FetchRow()) {
@@ -516,6 +515,14 @@
<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>Handling Large Recordsets (added 27 May 2005)</h3>
+The oci8 driver does not support counting the number of records returned in a
SELECT statement, so the function RecordCount()
+is emulated when the global variable $ADODB_COUNTRECS is set to true, which is
the default.
+We emulate this by buffering all the records. This can take up large amounts
of memory for big recordsets.
+ Set $ADODB_COUNTRECS to false for the best performance.
+ <p>
+This variable is checked every time a query is executed, so you can
selectively choose which recordsets to count.
+<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>
@@ -526,6 +533,7 @@
<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>OTN article on <a
href=http://www.oracle.com/technology/pub/articles/deployphp/lim_deployphp.html>Optimizing
PHP and Oracle</a> by this author.
<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>.
Index: docs-perf.htm
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/adodb/docs/docs-perf.htm,v
retrieving revision 1.2
retrieving revision 1.3
diff -u -d -r1.2 -r1.3
--- docs-perf.htm 27 Jan 2005 14:43:42 -0000 1.2
+++ docs-perf.htm 22 Apr 2006 11:09:48 -0000 1.3
@@ -18,20 +18,10 @@
</head>
<body>
<h3>The ADOdb Performance Monitoring Library</h3>
-<p>V4.60 24 Jan 2005 (c) 2000-2005 John Lim (jlim#natsoft.com.my)</p>
+<p>V4.80 8 Mar 2006 (c) 2000-2006 John Lim (jlim#natsoft.com.my)</p>
<p><font size="1">This software is dual licensed using BSD-Style and
LGPL. This means you can use it in compiled proprietary and commercial
products.</font></p>
-<table border="1">
- <tbody>
- <tr>
- <td><font color="red">Kindly note that the ADOdb home page has
-moved to <a
href="http://adodb.sourceforge.net/";>http://adodb.sourceforge.net/</a>
-because of the persistent unreliability of http://php.weblogs.com. <b>Please
-change your links</b>!</font></td>
- </tr>
- </tbody>
-</table>
<p>Useful ADOdb links: <a
href="http://adodb.sourceforge.net/#download";>Download</a>
<a href="http://adodb.sourceforge.net/#docs";>Other Docs</a>
</p>
Index: tute.htm
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/adodb/docs/tute.htm,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -d -r1.3 -r1.4
--- tute.htm 12 Feb 2005 20:01:24 -0000 1.3
+++ tute.htm 22 Apr 2006 11:09:48 -0000 1.4
@@ -1,290 +1,290 @@
-<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
-
-<html>
-<head>
- <title>Tutorial: Moving from MySQL to ADODB</title>
-</head>
-
-<body bgcolor=white>
-<h1>Tutorial: Moving from MySQL to ADODB</h1>
-
-<pre> You say eether and I say eyether,
- You say neether and I say nyther;
- Eether, eyether, neether, nyther -
- Let's call the whole thing off !
-<br>
- You like potato and I like po-tah-to,
- You like tomato and I like to-mah-to;
- Potato, po-tah-to, tomato, to-mah-to -
- Let's call the whole thing off !
-</pre>
-<p>I love this song, especially the version with Louis Armstrong and Ella
singing
- duet. It is all about how hard it is for two people in love to be compatible
- with each other. It's about compromise and finding a common ground, and
that's
- what this article is all about.
-<p>PHP is all about creating dynamic web-sites with the least fuss and the most
- fun. To create these websites we need to use databases to retrieve login
information,
- to splash dynamic news onto the web page and store forum postings. So let's
- say we were using the popular MySQL database for this. Your company has done
- such a fantastic job that the Web site is more popular than your wildest
dreams.
- You find that MySQL cannot scale to handle the workload; time to switch
databases.
-<p> Unfortunately in PHP every database is accessed slightly differently. To
connect
- to MySQL, you would use <i>mysql_connect()</i>; when you decide to upgrade to
- Oracle or Microsoft SQL Server, you would use <i>ocilogon() </i>or
<i>mssql_connect()</i>
- respectively. What is worse is that the parameters you use for the different
- connect functions are different also.. One database says po-tato, the other
- database says pota-to. Oh-oh.
-<h3>Let's NOT call the whole thing off</h3>
-<p>A database wrapper library such as ADODB comes in handy when you need to
ensure portability. It provides
- you with a common API to communicate with any supported database so you
don't have to call things off. <p>
-
-<p>ADODB stands for Active Data Objects DataBase (sorry computer guys are
sometimes
- not very original). ADODB currently supports MySQL, PostgreSQL, Oracle,
Interbase,
- Microsoft SQL Server, Access, FoxPro, Sybase, ODBC and ADO. You can download
- ADODB from <a href=http://php.weblogs.com/adodb></a><a
href="http://php.weblogs.com/adodb";>http://php.weblogs.com/adodb</a>.
-<h3>MySQL Example</h3>
-<p>The most common database used with PHP is MySQL, so I guess you should be
familiar
- with the following code. It connects to a MySQL server at <i>localhost</i>,
- database <i>mydb</i>, and executes an SQL select statement. The results are
- printed, one line per row.
-<pre><font color="#666600">$db = <b>mysql_connect</b>("localhost",
"root", "password");
-<b>mysql_select_db</b>("mydb",$db);</font>
-<font color="#660000">$result = <b>mysql_query</b>("SELECT * FROM
employees",$db)</font><code><font color="#663300">;
-if ($result === false) die("failed");</font></code>
-<font color="#006666"><b>while</b> ($fields =<b> mysql_fetch_row</b>($result))
{
- <b>for</b> ($i=0, $max=sizeof($fields); $i < $max; $i++) {
- <b>print</b> $fields[$i].' ';
- }
- <b>print</b> "<br>\n";
-}</font>
-</pre>
-<p>The above code has been color-coded by section. The first section is the
connection
- phase. The second is the execution of the SQL, and the last section is
displaying
- the fields. The <i>while</i> loop scans the rows of the result, while the
<i>for</i>
- loop scans the fields in one row.</p>
-<p>Here is the equivalent code in ADODB</p>
-<pre><b><font color="#666600">
include("adodb.inc.php");</font></b><font color="#666600">
- $db = <b>NewADOConnection</b>('mysql');
- $db-><b>Connect</b>("localhost", "root",
"password", "mydb");</font>
- <font color="#663300">$result = $db-><b>Execute</b>("SELECT * FROM
employees");
- </font><font color="#663300"></font><code><font color="#663300">if ($result
=== false) die("failed")</font></code><code><font
color="#663300">;</font></code>
- <font color="#006666"><b>while</b> (!$result->EOF) {
- <b>for</b> ($i=0, $max=$result-><b>FieldCount</b>(); $i < $max;
$i++)
- <b>print</b> $result->fields[$i].' ';
- $result-><b>MoveNext</b>();
- <b>print</b> "<br>\n";
- }</font> </pre>
-<p></p>
-<p>Now porting to Oracle is as simple as changing the second line to
<code>NewADOConnection('oracle')</code>.
- Let's walk through the code...</p>
-<h3>Connecting to the Database</h3>
-<p></p>
-<pre><b><font
color="#666600">include("adodb.inc.php");</font></b><font
color="#666600">
-$db = <b>NewADOConnection</b>('mysql');
-$db-><b>Connect</b>("localhost", "root",
"password", "mydb");</font></pre>
-<p>The connection code is a bit more sophisticated than MySQL's because our
needs
- are more sophisticated. In ADODB, we use an object-oriented approach to
managing
- the complexity of handling multiple databases. We have different classes to
- handle different databases. If you aren't familiar with object-oriented
programing,
- don't worry -- the complexity is all hidden away in the<code>
NewADOConnection()</code>
- function.</p>
-<p>To conserve memory, we only load the PHP code specific to the database you
- are connecting to. We do this by calling
<code>NewADOConnection(databasedriver)</code>.
- Legal database drivers include <i>mysql, mssql, oracle, oci8, postgres,
sybase,
- vfp, access, ibase </i>and many others.</p>
-<p>Then we create a new instance of the connection class by calling
<code>NewADOConnection()</code>.
- Finally we connect to the database using <code>$db->Connect(). </code></p>
-<h3>Executing the SQL</h3>
-<p><code><font color="#663300">$result = $db-><b>Execute</b>("SELECT *
- FROM employees");<br>
- if ($result === false) die("failed")</font></code><code><font
color="#663300">;</font></code>
- <br>
-</p>
-<p>Sending the SQL statement to the server is straight forward. Execute() will
- return a recordset object on successful execution. You should check $result
- as we do above.
-<p>An issue that confuses beginners is the fact that we have two types of
objects
- in ADODB, the connection object and the recordset object. When do we use
each?
-<p>The connection object ($db) is responsible for connecting to the database,
- formatting your SQL and querying the database server. The recordset object
($result)
- is responsible for retrieving the results and formatting the reply as text or
- as an array.
-<p>The only thing I need to add is that ADODB provides several helper functions
- for making INSERT and UPDATE statements easier, which we will cover in the
Advanced
- section.
-<h3>Retrieving the Data<br>
-</h3>
-<pre><font color="#006666"><b>while</b> (!$result->EOF) {
- <b>for</b> ($i=0, $max=$result-><b>FieldCount</b>(); $i < $max; $i++)
- <b>print</b> $result->fields[$i].' ';
- $result-><b>MoveNext</b>();
- <b>print</b> "<br>\n";
-}</font></pre>
-<p>The paradigm for getting the data is that it's like reading a file. For
every
- line, we check first whether we have reached the end-of-file (EOF). While not
- end-of-file, loop through each field in the row. Then move to the next line
- (MoveNext) and repeat.
-<p>The <code>$result->fields[]</code> array is generated by the PHP database
- extension. Some database extensions do not index the array by field name.
- To force indexing by name - that is associative arrays -
- use the $ADODB_FETCH_MODE global variable.
-<pre>
- $<b>ADODB_FETCH_MODE</b> = ADODB_FETCH_NUM;
- $rs1 = $db->Execute('select * from table');
- $<b>ADODB_FETCH_MODE</b> = ADODB_FETCH_ASSOC;
- $rs2 = $db->Execute('select * from table');
- print_r($rs1->fields); // shows <i>array([0]=>'v0',[1] =>'v1')</i>
- print_r($rs2->fields); // shows <i>array(['col1']=>'v0',['col2']
=>'v1')</i>
-</pre>
-<p>
-As you can see in the above example, both recordsets store and use different
fetch modes
-based on the $ADODB_FETCH_MODE setting when the recordset was created by
Execute().</p>
-<h2>ADOConnection<a name="ADOConnection"></a></h2>
-<p>Object that performs the connection to the database, executes SQL statements
- and has a set of utility functions for standardising the format of SQL
statements
- for issues such as concatenation and date formats.</p>
-
-<h3>Other Useful Functions</h3>
-<p><code>$recordset->Move($pos)</code> scrolls to that particular row.
ADODB supports forward
- scrolling for all databases. Some databases will not support backwards
scrolling.
- This is normally not a problem as you can always cache records to simulate
backwards
- scrolling.
-<p><code>$recordset->RecordCount()</code> returns the number of records
accessed by the
- SQL statement. Some databases will return -1 because it is not supported.
-<p><code>$recordset->GetArray()</code> returns the result as an array.
-<p><code>rs2html($recordset)</code> is a function that is generates a HTML
table based on the
- $recordset passed to it. An example with the relevant lines in bold:
-<pre> include('adodb.inc.php');
- <b>include('tohtml.inc.php');</b> /* includes the rs2html function */
- $conn = &ADONewConnection('mysql');
- $conn->PConnect('localhost','userid','password','database');
- $rs = $conn->Execute('select * from table');
- <b> rs2html($rs)</b>; /* recordset to html table */ </pre>
-<p>There are many other helper functions that are listed in the documentation
available at <a href="http://php.weblogs.com/adodb_manual";></a><a
href="http://php.weblogs.com/adodb_manual";>http://php.weblogs.com/adodb_manual</a>.
-<h2>Advanced Material</h2>
-<h3>Inserts and Updates </h3>
-<p>Let's say you want to insert the following data into a database.
-<p><b>ID</b> = 3<br>
- <b>TheDate</b>=mktime(0,0,0,8,31,2001) /* 31st August 2001 */<br>
- <b>Note</b>= sugar why don't we call it off
-<p>When you move to another database, your insert might no longer work.</p>
-<p>The first problem is that each database has a different default date format.
- MySQL expects YYYY-MM-DD format, while other databases have different
defaults.
- ADODB has a function called DBDate() that addresses this issue by converting
- converting the date to the correct format.</p>
-<p>The next problem is that the <b>don't</b> in the Note needs to be quoted. In
- MySQL, we use <b>don\'t</b> but in some other databases (Sybase, Access,
Microsoft
- SQL Server) we use <b>don''t. </b>The qstr() function addresses this
issue.</p>
-<p>So how do we use the functions? Like this:</p>
-<pre>$sql = "INSERT INTO table (id, thedate,note) values ("
- . $<b>ID</b> . ','
- . $db->DBDate($<b>TheDate</b>) .','
- . $db->qstr($<b>Note</b>).")";
-$db->Execute($sql);</pre>
-<p>ADODB also supports <code>$connection->Affected_Rows()</code> (returns
the
- number of rows affected by last update or delete) and
<code>$recordset->Insert_ID()</code>
- (returns last autoincrement number generated by an insert statement). Be
forewarned
- that not all databases support the two functions.<br>
-</p>
-<h3>MetaTypes</h3>
-<p>You can find out more information about each of the fields (I use the words
- fields and columns interchangebly) you are selecting by calling the recordset
- method <code>FetchField($fieldoffset)</code>. This will return an object with
- 3 properties: name, type and max_length.
-<pre>For example:</pre>
-<pre>$recordset = $conn->Execute("select adate from
table");<br>$f0 = $recordset->FetchField(0);
-</pre>
-<p>Then <code>$f0->name</code> will hold <i>'adata'</i>,
<code>$f0->type</code>
- will be set to '<i>date'</i>. If the max_length is unknown, it will be set to
- -1.
-<p>One problem with handling different databases is that each database often
calls
- the same type by a different name. For example a <i>timestamp</i> type is
called
- <i>datetime</i> in one database and <i>time</i> in another. So ADODB has a
special
- <code>MetaType($type, $max_length)</code> function that standardises the
types
- to the following:
-<p>C: character and varchar types<br>
- X: text or long character (eg. more than 255 bytes wide).<br>
- B: blob or binary image<br>
- D: date<br>
- T: timestamp<br>
- L: logical (boolean)<br>
- I: integer<br>
- N: numeric (float, double, money)
-<p>In the above date example,
-<p><code>$recordset = $conn->Execute("select adate from
table");<br>
- $f0 = $recordset->FetchField(0);<br>
- $type = $recordset->MetaType($f0->type, $f0->max_length);<br>
- print $type; /* should print 'D'</code> */
-<p>
-<p><b>Select Limit and Top Support</b>
-<p>ADODB has a function called $connection->SelectLimit($sql,$nrows,$offset)
that allows
-you to retrieve a subset of the recordset. This will take advantage of native
-SELECT TOP on Microsoft products and SELECT ... LIMIT with PostgreSQL and
MySQL, and
-emulated if the database does not support it.
-<p><b>Caching Support</b>
-<p>ADODB allows you to cache recordsets in your file system, and only requery
the database
-server after a certain timeout period with
$connection->CacheExecute($secs2cache,$sql) and
-$connection->CacheSelectLimit($secs2cache,$sql,$nrows,$offset).
-<p><b>PHP4 Session Handler Support</b>
-<p>ADODB also supports PHP4 session handlers. You can store your session
variables
- in a database for true scalability using ADODB. For further information,
visit
- <a href="http://php.weblogs.com/adodb-sessions";></a><a
href="http://php.weblogs.com/adodb-sessions";>http://php.weblogs.com/adodb-sessions</a>
-<h3>Commercial Use Encouraged</h3>
-<p>If you plan to write commercial PHP applications that you want to resell,
you should consider ADODB. It has been released using the lesser GPL, which
means you can legally include it in commercial applications, while keeping your
code proprietary. Commercial use of ADODB is strongly encouraged! We are using
it internally for this reason.<p>
-
-<h2>Conclusion</h2>
-<p>As a thank you for finishing this article, here are the complete lyrics for
- <i>let's call the whole thing off</i>.<br>
- <br>
-<pre>
- Refrain
-<br>
- You say eether and I say eyether,
- You say neether and I say nyther;
- Eether, eyether, neether, nyther -
- Let's call the whole thing off !
-<br>
- You like potato and I like po-tah-to,
- You like tomato and I like to-mah-to;
- Potato, po-tah-to, tomato, to-mah-to -
- Let's call the whole thing off !
-<br>
-But oh, if we call the whole thing off, then we must part.
-And oh, if we ever part, then that might break my heart.
-<br>
- So, if you like pajamas and I like pa-jah-mas,
- I'll wear pajamas and give up pa-jah-mas.
- For we know we
- Need each other, so we
- Better call the calling off off.
- Let's call the whole thing off !
-<br>
- Second Refrain
-<br>
- You say laughter and I say lawfter,
- You say after and I say awfter;
- Laughter, lawfter, after, awfter -
- Let's call the whole thing off !
-<br>
- You like vanilla and I like vanella,
- You, sa's'parilla and I sa's'parella;
- Vanilla, vanella, choc'late, strawb'ry -
- Let's call the whole thing off !
-<br>
-But oh, if we call the whole thing off, then we must part.
-And oh, if we ever part, then that might break my heart.
-<br>
- So, if you go for oysters and I go for ersters,
- I'll order oysters and cancel the ersters.
- For we know we
- Need each other, so we
- Better call the calling off off.
- Let's call the whole thing off !
- </pre>
-<p><font size=2>Song and lyrics by George and Ira Gershwin, introduced by Fred
Astaire and Ginger Rogers
-in the film "Shall We Dance?" </font><p>
-<p>
-(c)2001-2002 John Lim.
-
-</body>
-</html>
+<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
+
+<html>
+<head>
+ <title>Tutorial: Moving from MySQL to ADODB</title>
+</head>
+
+<body bgcolor=white>
+<h1>Tutorial: Moving from MySQL to ADODB</h1>
+
+<pre> You say eether and I say eyether,
+ You say neether and I say nyther;
+ Eether, eyether, neether, nyther -
+ Let's call the whole thing off !
+<br>
+ You like potato and I like po-tah-to,
+ You like tomato and I like to-mah-to;
+ Potato, po-tah-to, tomato, to-mah-to -
+ Let's call the whole thing off !
+</pre>
+<p>I love this song, especially the version with Louis Armstrong and Ella
singing
+ duet. It is all about how hard it is for two people in love to be compatible
+ with each other. It's about compromise and finding a common ground, and
that's
+ what this article is all about.
+<p>PHP is all about creating dynamic web-sites with the least fuss and the
most
+ fun. To create these websites we need to use databases to retrieve login
information,
+ to splash dynamic news onto the web page and store forum postings. So let's
+ say we were using the popular MySQL database for this. Your company has done
+ such a fantastic job that the Web site is more popular than your wildest
dreams.
+ You find that MySQL cannot scale to handle the workload; time to switch
databases.
+<p> Unfortunately in PHP every database is accessed slightly differently. To
connect
+ to MySQL, you would use <i>mysql_connect()</i>; when you decide to upgrade
to
+ Oracle or Microsoft SQL Server, you would use <i>ocilogon() </i>or
<i>mssql_connect()</i>
+ respectively. What is worse is that the parameters you use for the different
+ connect functions are different also.. One database says po-tato, the other
+ database says pota-to. Oh-oh.
+<h3>Let's NOT call the whole thing off</h3>
+<p>A database wrapper library such as ADODB comes in handy when you need to
ensure portability. It provides
+ you with a common API to communicate with any supported database so you
don't have to call things off. <p>
+
+<p>ADODB stands for Active Data Objects DataBase (sorry computer guys are
sometimes
+ not very original). ADODB currently supports MySQL, PostgreSQL, Oracle,
Interbase,
+ Microsoft SQL Server, Access, FoxPro, Sybase, ODBC and ADO. You can download
+ ADODB from <a href=http://php.weblogs.com/adodb></a><a
href="http://php.weblogs.com/adodb";>http://php.weblogs.com/adodb</a>.
+<h3>MySQL Example</h3>
+<p>The most common database used with PHP is MySQL, so I guess you should be
familiar
+ with the following code. It connects to a MySQL server at <i>localhost</i>,
+ database <i>mydb</i>, and executes an SQL select statement. The results are
+ printed, one line per row.
+<pre><font color="#666600">$db = <b>mysql_connect</b>("localhost",
"root", "password");
+<b>mysql_select_db</b>("mydb",$db);</font>
+<font color="#660000">$result = <b>mysql_query</b>("SELECT * FROM
employees",$db)</font><code><font color="#663300">;
+if ($result === false) die("failed");</font></code>
+<font color="#006666"><b>while</b> ($fields =<b> mysql_fetch_row</b>($result))
{
+ <b>for</b> ($i=0, $max=sizeof($fields); $i < $max; $i++) {
+ <b>print</b> $fields[$i].' ';
+ }
+ <b>print</b> "<br>\n";
+}</font>
+</pre>
+<p>The above code has been color-coded by section. The first section is the
connection
+ phase. The second is the execution of the SQL, and the last section is
displaying
+ the fields. The <i>while</i> loop scans the rows of the result, while the
<i>for</i>
+ loop scans the fields in one row.</p>
+<p>Here is the equivalent code in ADODB</p>
+<pre><b><font color="#666600">
include("adodb.inc.php");</font></b><font color="#666600">
+ $db = <b>NewADOConnection</b>('mysql');
+ $db-><b>Connect</b>("localhost", "root",
"password", "mydb");</font>
+ <font color="#663300">$result = $db-><b>Execute</b>("SELECT * FROM
employees");
+ </font><font color="#663300"></font><code><font color="#663300">if ($result
=== false) die("failed")</font></code><code><font
color="#663300">;</font></code>
+ <font color="#006666"><b>while</b> (!$result->EOF) {
+ <b>for</b> ($i=0, $max=$result-><b>FieldCount</b>(); $i < $max;
$i++)
+ <b>print</b> $result->fields[$i].' ';
+ $result-><b>MoveNext</b>();
+ <b>print</b> "<br>\n";
+ }</font> </pre>
+<p></p>
+<p>Now porting to Oracle is as simple as changing the second line to
<code>NewADOConnection('oracle')</code>.
+ Let's walk through the code...</p>
+<h3>Connecting to the Database</h3>
+<p></p>
+<pre><b><font
color="#666600">include("adodb.inc.php");</font></b><font
color="#666600">
+$db = <b>NewADOConnection</b>('mysql');
+$db-><b>Connect</b>("localhost", "root",
"password", "mydb");</font></pre>
+<p>The connection code is a bit more sophisticated than MySQL's because our
needs
+ are more sophisticated. In ADODB, we use an object-oriented approach to
managing
+ the complexity of handling multiple databases. We have different classes to
+ handle different databases. If you aren't familiar with object-oriented
programing,
+ don't worry -- the complexity is all hidden away in the<code>
NewADOConnection()</code>
+ function.</p>
+<p>To conserve memory, we only load the PHP code specific to the database you
+ are connecting to. We do this by calling
<code>NewADOConnection(databasedriver)</code>.
+ Legal database drivers include <i>mysql, mssql, oracle, oci8, postgres,
sybase,
+ vfp, access, ibase </i>and many others.</p>
+<p>Then we create a new instance of the connection class by calling
<code>NewADOConnection()</code>.
+ Finally we connect to the database using <code>$db->Connect(). </code></p>
+<h3>Executing the SQL</h3>
+<p><code><font color="#663300">$result = $db-><b>Execute</b>("SELECT *
+ FROM employees");<br>
+ if ($result === false) die("failed")</font></code><code><font
color="#663300">;</font></code>
+ <br>
+</p>
+<p>Sending the SQL statement to the server is straight forward. Execute() will
+ return a recordset object on successful execution. You should check $result
+ as we do above.
+<p>An issue that confuses beginners is the fact that we have two types of
objects
+ in ADODB, the connection object and the recordset object. When do we use
each?
+<p>The connection object ($db) is responsible for connecting to the database,
+ formatting your SQL and querying the database server. The recordset object
($result)
+ is responsible for retrieving the results and formatting the reply as text
or
+ as an array.
+<p>The only thing I need to add is that ADODB provides several helper
functions
+ for making INSERT and UPDATE statements easier, which we will cover in the
Advanced
+ section.
+<h3>Retrieving the Data<br>
+</h3>
+<pre><font color="#006666"><b>while</b> (!$result->EOF) {
+ <b>for</b> ($i=0, $max=$result-><b>FieldCount</b>(); $i < $max; $i++)
+ <b>print</b> $result->fields[$i].' ';
+ $result-><b>MoveNext</b>();
+ <b>print</b> "<br>\n";
+}</font></pre>
+<p>The paradigm for getting the data is that it's like reading a file. For
every
+ line, we check first whether we have reached the end-of-file (EOF). While
not
+ end-of-file, loop through each field in the row. Then move to the next line
+ (MoveNext) and repeat.
+<p>The <code>$result->fields[]</code> array is generated by the PHP
database
+ extension. Some database extensions do not index the array by field name.
+ To force indexing by name - that is associative arrays -
+ use the $ADODB_FETCH_MODE global variable.
+<pre>
+ $<b>ADODB_FETCH_MODE</b> = ADODB_FETCH_NUM;
+ $rs1 = $db->Execute('select * from table');
+ $<b>ADODB_FETCH_MODE</b> = ADODB_FETCH_ASSOC;
+ $rs2 = $db->Execute('select * from table');
+ print_r($rs1->fields); // shows <i>array([0]=>'v0',[1] =>'v1')</i>
+ print_r($rs2->fields); // shows <i>array(['col1']=>'v0',['col2']
=>'v1')</i>
+</pre>
+<p>
+As you can see in the above example, both recordsets store and use different
fetch modes
+based on the $ADODB_FETCH_MODE setting when the recordset was created by
Execute().</p>
+<h2>ADOConnection<a name="ADOConnection"></a></h2>
+<p>Object that performs the connection to the database, executes SQL
statements
+ and has a set of utility functions for standardising the format of SQL
statements
+ for issues such as concatenation and date formats.</p>
+
+<h3>Other Useful Functions</h3>
+<p><code>$recordset->Move($pos)</code> scrolls to that particular row.
ADODB supports forward
+ scrolling for all databases. Some databases will not support backwards
scrolling.
+ This is normally not a problem as you can always cache records to simulate
backwards
+ scrolling.
+<p><code>$recordset->RecordCount()</code> returns the number of records
accessed by the
+ SQL statement. Some databases will return -1 because it is not supported.
+<p><code>$recordset->GetArray()</code> returns the result as an array.
+<p><code>rs2html($recordset)</code> is a function that is generates a HTML
table based on the
+ $recordset passed to it. An example with the relevant lines in bold:
+<pre> include('adodb.inc.php');
+ <b>include('tohtml.inc.php');</b> /* includes the rs2html function */
+ $conn = &ADONewConnection('mysql');
+ $conn->PConnect('localhost','userid','password','database');
+ $rs = $conn->Execute('select * from table');
+ <b> rs2html($rs)</b>; /* recordset to html table */ </pre>
+<p>There are many other helper functions that are listed in the documentation
available at <a href="http://php.weblogs.com/adodb_manual";></a><a
href="http://php.weblogs.com/adodb_manual";>http://php.weblogs.com/adodb_manual</a>.
+<h2>Advanced Material</h2>
+<h3>Inserts and Updates </h3>
+<p>Let's say you want to insert the following data into a database.
+<p><b>ID</b> = 3<br>
+ <b>TheDate</b>=mktime(0,0,0,8,31,2001) /* 31st August 2001 */<br>
+ <b>Note</b>= sugar why don't we call it off
+<p>When you move to another database, your insert might no longer work.</p>
+<p>The first problem is that each database has a different default date
format.
+ MySQL expects YYYY-MM-DD format, while other databases have different
defaults.
+ ADODB has a function called DBDate() that addresses this issue by converting
+ converting the date to the correct format.</p>
+<p>The next problem is that the <b>don't</b> in the Note needs to be quoted.
In
+ MySQL, we use <b>don\'t</b> but in some other databases (Sybase, Access,
Microsoft
+ SQL Server) we use <b>don''t. </b>The qstr() function addresses this
issue.</p>
+<p>So how do we use the functions? Like this:</p>
+<pre>$sql = "INSERT INTO table (id, thedate,note) values ("
+ . $<b>ID</b> . ','
+ . $db->DBDate($<b>TheDate</b>) .','
+ . $db->qstr($<b>Note</b>).")";
+$db->Execute($sql);</pre>
+<p>ADODB also supports <code>$connection->Affected_Rows()</code> (returns
the
+ number of rows affected by last update or delete) and
<code>$recordset->Insert_ID()</code>
+ (returns last autoincrement number generated by an insert statement). Be
forewarned
+ that not all databases support the two functions.<br>
+</p>
+<h3>MetaTypes</h3>
+<p>You can find out more information about each of the fields (I use the words
+ fields and columns interchangebly) you are selecting by calling the
recordset
+ method <code>FetchField($fieldoffset)</code>. This will return an object
with
+ 3 properties: name, type and max_length.
+<pre>For example:</pre>
+<pre>$recordset = $conn->Execute("select adate from
table");<br>$f0 = $recordset->FetchField(0);
+</pre>
+<p>Then <code>$f0->name</code> will hold <i>'adata'</i>,
<code>$f0->type</code>
+ will be set to '<i>date'</i>. If the max_length is unknown, it will be set
to
+ -1.
+<p>One problem with handling different databases is that each database often
calls
+ the same type by a different name. For example a <i>timestamp</i> type is
called
+ <i>datetime</i> in one database and <i>time</i> in another. So ADODB has a
special
+ <code>MetaType($type, $max_length)</code> function that standardises the
types
+ to the following:
+<p>C: character and varchar types<br>
+ X: text or long character (eg. more than 255 bytes wide).<br>
+ B: blob or binary image<br>
+ D: date<br>
+ T: timestamp<br>
+ L: logical (boolean)<br>
+ I: integer<br>
+ N: numeric (float, double, money)
+<p>In the above date example,
+<p><code>$recordset = $conn->Execute("select adate from
table");<br>
+ $f0 = $recordset->FetchField(0);<br>
+ $type = $recordset->MetaType($f0->type, $f0->max_length);<br>
+ print $type; /* should print 'D'</code> */
+<p>
+<p><b>Select Limit and Top Support</b>
+<p>ADODB has a function called $connection->SelectLimit($sql,$nrows,$offset)
that allows
+you to retrieve a subset of the recordset. This will take advantage of native
+SELECT TOP on Microsoft products and SELECT ... LIMIT with PostgreSQL and
MySQL, and
+emulated if the database does not support it.
+<p><b>Caching Support</b>
+<p>ADODB allows you to cache recordsets in your file system, and only requery
the database
+server after a certain timeout period with
$connection->CacheExecute($secs2cache,$sql) and
+$connection->CacheSelectLimit($secs2cache,$sql,$nrows,$offset).
+<p><b>PHP4 Session Handler Support</b>
+<p>ADODB also supports PHP4 session handlers. You can store your session
variables
+ in a database for true scalability using ADODB. For further information,
visit
+ <a href="http://php.weblogs.com/adodb-sessions";></a><a
href="http://php.weblogs.com/adodb-sessions";>http://php.weblogs.com/adodb-sessions</a>
+<h3>Commercial Use Encouraged</h3>
+<p>If you plan to write commercial PHP applications that you want to resell,
you should consider ADODB. It has been released using the lesser GPL, which
means you can legally include it in commercial applications, while keeping your
code proprietary. Commercial use of ADODB is strongly encouraged! We are using
it internally for this reason.<p>
+
+<h2>Conclusion</h2>
+<p>As a thank you for finishing this article, here are the complete lyrics for
+ <i>let's call the whole thing off</i>.<br>
+ <br>
+<pre>
+ Refrain
+<br>
+ You say eether and I say eyether,
+ You say neether and I say nyther;
+ Eether, eyether, neether, nyther -
+ Let's call the whole thing off !
+<br>
+ You like potato and I like po-tah-to,
+ You like tomato and I like to-mah-to;
+ Potato, po-tah-to, tomato, to-mah-to -
+ Let's call the whole thing off !
+<br>
+But oh, if we call the whole thing off, then we must part.
+And oh, if we ever part, then that might break my heart.
+<br>
+ So, if you like pajamas and I like pa-jah-mas,
+ I'll wear pajamas and give up pa-jah-mas.
+ For we know we
+ Need each other, so we
+ Better call the calling off off.
+ Let's call the whole thing off !
+<br>
+ Second Refrain
+<br>
+ You say laughter and I say lawfter,
+ You say after and I say awfter;
+ Laughter, lawfter, after, awfter -
+ Let's call the whole thing off !
+<br>
+ You like vanilla and I like vanella,
+ You, sa's'parilla and I sa's'parella;
+ Vanilla, vanella, choc'late, strawb'ry -
+ Let's call the whole thing off !
+<br>
+But oh, if we call the whole thing off, then we must part.
+And oh, if we ever part, then that might break my heart.
+<br>
+ So, if you go for oysters and I go for ersters,
+ I'll order oysters and cancel the ersters.
+ For we know we
+ Need each other, so we
+ Better call the calling off off.
+ Let's call the whole thing off !
+ </pre>
+<p><font size=2>Song and lyrics by George and Ira Gershwin, introduced by Fred
Astaire and Ginger Rogers
+in the film "Shall We Dance?" </font><p>
+<p>
+(c)2001-2002 John Lim.
+
+</body>
+</html>
Index: docs-adodb.htm
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/adodb/docs/docs-adodb.htm,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -d -r1.3 -r1.4
--- docs-adodb.htm 12 Feb 2005 20:01:23 -0000 1.3
+++ docs-adodb.htm 22 Apr 2006 11:09:48 -0000 1.4
@@ -3,7 +3,7 @@
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
-
+
<style>
pre {
background-color: #eee;
@@ -12,13 +12,13 @@
border: 1px solid #ddd;
}
[...3692 lines suppressed...]
- to odbc MetaColumns() for vfp and db2 compat.
-</p><p>Added unsigned support to mysql datadict class. Thx to iamsure.
-</p><p>Infinite loop in mssql MoveNext() fixed when ADODB_FETCH_ASSOC used.
Thx to
- Josh R, Night_Wulfe#hotmail.com.
-</p><p>ChangeTableSQL contributed by Florian Buzin.
-</p><p>The odbc_mssql driver now sets CONCAT_NULL_YIELDS_NULL OFF for compat
with
- mssql driver.
-</p><hr>
-<p><strong>0.10 Sept 9 2000</strong> First release
-</p><h3><strong>Old change log history moved to <a
href="old-changelog.htm">old-changelog.htm</a>.
+</p><p>Added _varchar (varchar arrays) support for postgresql. Reported by
PREVOT Stéphane.<hr />
+<p><strong>0.10 Sept 9 2000</strong> First release
+</p><h3><strong>Old change log history moved to <a
href="old-changelog.htm">old-changelog.htm</a>.
</strong></h3>
<p> </p>
-<p>
-</p></body></html>
+<p>
+</p></body></html>
\ No newline at end of file
Index: old-changelog.htm
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/adodb/docs/old-changelog.htm,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -d -r1.3 -r1.4
--- old-changelog.htm 12 Feb 2005 20:01:24 -0000 1.3
+++ old-changelog.htm 22 Apr 2006 11:09:48 -0000 1.4
@@ -1,700 +1,822 @@
-<h3>Old Changelog</h3>
-<p><b>3.50 19 May 2003</b></p>
-<p>Fixed mssql compat with FreeTDS. FreeTDS does not implement
mssql_fetch_assoc().
-<p>Merged back connection and recordset code into adodb.inc.php.
-<p>ADOdb sessions using oracle clobs contributed by achim.gosse#ddd.de. See
adodb-session-clob.php.
-<p>Added /s modifier to preg_match everywhere, which ensures that regex does
not
- stop at /n. Thx Pao-Hsi Huang.
-<p>Fixed error in metacolumns() for mssql.
-<p>Added time format support for SQLDate.
-<p>Image => B added to metatype.
-<p>MetaType now checks empty($this->blobSize) instead of empty($this).
[...1492 lines suppressed...]
+<p>Added BeginTrans, CommitTrans and RollbackTrans functions.</p>
+<p>Added Affected_Rows() and Insert_ID(), _affectedrows() and _insertID(),
ListTables(),
+ ListDatabases(), ListColumns().</p>
+<p>Need to add New_ID() and hasInsertID and hasAffectedRows, autoCommit </p>
+<p><b>0.20 Sept 12</b></p>
+<p>Added support for Microsoft's ADO.</p>
+<p>Added new field to ADORecordSet -- canSeek</p>
+<p>Added new parameter to _fetch($ignore_fields = false). Setting to true will
+ not update fields array for faster performance.</p>
+<p>Added new field to ADORecordSet/ADOConnection -- dataProvider to indicate
whether
+ a class is derived from odbc or ado.</p>
+<p>Changed class ODBCFieldObject to ADOFieldObject -- not documented
currently.</p>
+<p>Added benchmark.php and testdatabases.inc.php to the test suite.</p>
+<p>Added to ADORecordSet FastForward( ) for future high speed scrolling. Not
documented.</p>
+<p>Realised that ADO's Move( ) uses relative positioning. ADOdb uses absolute.
+</p>
+<p><b>0.10 Sept 9 2000</b></p>
+<p>First release</p>
+</body></html>
\ No newline at end of file
Index: docs-datadict.htm
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/adodb/docs/docs-datadict.htm,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -d -r1.3 -r1.4
--- docs-datadict.htm 12 Feb 2005 20:01:24 -0000 1.3
+++ docs-datadict.htm 22 Apr 2006 11:09:48 -0000 1.4
@@ -1,7 +1,7 @@
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
- <title>ADODB Data Dictionary Manual</title>
+ <title>ADOdb Data Dictionary Manual</title>
<meta http-equiv="Content-Type"
content="text/html; charset=iso-8859-1">
<style type="text/css">
@@ -11,7 +11,7 @@
}
pre {
font-size: 9pt;
- background-color: #EEEEEE; padding: .5em; margin: 0px;
+ background-color: #EEEEEE; padding: .5em; margin: 0px;
}
.toplink {
font-size: 8pt;
@@ -20,22 +20,13 @@
</head>
<body style="background-color: rgb(255, 255, 255);">
<h2>ADOdb Data Dictionary Library for PHP</h2>
-<p>V4.60 24 Jan 2005 (c) 2000-2005 John Lim (<a
+<p>V4.80 8 Mar 2006 (c) 2000-2006 John Lim (<a
href="mailto:jlim#natsoft.com.my";>jlim#natsoft.com.my</a>).<br>
AXMLS (c) 2004 ars Cognita, Inc</p>
<p><font size="1">This software is dual licensed using BSD-Style and
LGPL. This means you can use it in compiled proprietary and commercial
products.</font></p>
-<table border="1">
- <tbody>
- <tr>
- <td height="38"><font color="red">Kindly note that the ADOdb home page
has
-moved to <a
href="http://adodb.sourceforge.net/";>http://adodb.sourceforge.net/</a>
-because of the persistent unreliability of http://php.weblogs.com. <b>Please
-change your links</b>!</font></td>
- </tr>
- </tbody>
-</table>
+
<p>Useful ADOdb links: <a
href="http://adodb.sourceforge.net/#download";>Download</a>
<a href="http://adodb.sourceforge.net/#docs";>Other Docs</a>
</p>
@@ -52,7 +43,7 @@
generic ODBC.
</p>
<h3>Example Usage</h3>
-<pre> include_once('adodb.inc.php');<br> <font color="#006600"># First
create a normal connection</font><br> $db->NewADOConnection('mysql');<br>
$db->Connect(...);<br><br> <font
+<pre> include_once('adodb.inc.php');<br> <font color="#006600"># First
create a normal connection</font><br> $db = NewADOConnection('mysql');<br>
$db->Connect(...);<br><br> <font
color="#006600"># Then create a data dictionary object, using this
connection</font><br> $dict = <strong>NewDataDictionary</strong>($db);<br><br>
<font
color="#006600"># We have a portable declarative data dictionary format in
ADOdb, similar to SQL.<br> # Field types use 1 character codes, and fields are
separated by commas.<br> # The following example creates three fields: "col1",
"col2" and "col3":</font><br> $flds = " <br> <font
color="#663300"><strong> col1 C(32) NOTNULL DEFAULT 'abc',<br> col2 I
DEFAULT 0,<br> col3 N(12.2)</strong></font><br> ";<br><br> <font
@@ -83,7 +74,7 @@
field type can be a portable type codes or the actual type for that
database.</p>
<p>Legal portable type codes include:</p>
-<pre> C: varchar<br> X: Largest varchar size <br> XL: For Oracle, returns
CLOB, otherwise same as 'X' above<br><br> C2: Multibyte varchar<br> X2:
Multibyte varchar (largest size)<br><br> B: BLOB (binary large
object)<br><br> D: Date (some databases do not support this, and we return a
datetime type)<br> T: Datetime or Timestamp<br> L: Integer field suitable
for storing booleans (0 or 1)<br> I: Integer (mapped to I4)<br> I1: 1-byte
integer<br> I2: 2-byte integer<br> I4: 4-byte integer<br> I8: 8-byte
integer<br> F: Floating point number<br> N: Numeric or decimal
number<br></pre>
+<pre> C: Varchar, capped to 255 characters.<br> X: Larger varchar, capped
to 4000 characters (to be compatible with Oracle). <br> XL: For Oracle,
returns CLOB, otherwise the largest varchar size.<br><br> C2: Multibyte
varchar<br> X2: Multibyte varchar (largest size)<br><br> B: BLOB (binary
large object)<br><br> D: Date (some databases do not support this, and we
return a datetime type)<br> T: Datetime or Timestamp<br> L: Integer field
suitable for storing booleans (0 or 1)<br> I: Integer (mapped to I4)<br> I1:
1-byte integer<br> I2: 2-byte integer<br> I4: 4-byte integer<br> I8: 8-byte
integer<br> F: Floating point number<br> N: Numeric or decimal
number<br></pre>
<p>The $colsize field represents the size of the field. If a decimal
number is used, then it is assumed that the number following the dot is
the precision, so 6.2 means a number of size 6 digits and 2 decimal
@@ -177,7 +168,7 @@
<pre> RETURNS: 0 if failed, 1 if executed all but with errors, 2 if
executed successfully<br> $sqlarray: an array of strings with sql code (no
semicolon at the end of string)<br> $contOnError: if true, then continue
executing even if error occurs<br></pre>
<p>Executes an array of SQL strings returned by CreateTableSQL or
CreateIndexSQL.</p>
-<hr>
+<hr />
<a name="xmlschema"></a>
<h2>ADOdb XML Schema (AXMLS)</h2>
<p>This is a class contributed by Richard Tango-Lowy and Dan Cech that
@@ -250,7 +241,11 @@
<p>Now that we've defined an XML schema, you need to know how to apply
it to your database. Here's a simple PHP script that shows how to load
the schema.</p>
-<pre class="listing"><pre><?PHP<br>/* You must tell the script where to
find the ADOdb and<br> * the AXMLS libraries.<br> */<br>require(
"path_to_adodb/adodb.inc.php");<br>require(
"path_to_adodb/adodb-xmlschema.inc.php" );<br><br>/* Configuration information.
Define the schema filename,<br> * RDBMS platform (see the ADODB documentation
for valid<br> * platform names), and database connection information here.<br>
*/<br>$schemaFile = 'example.xml';<br>$platform = 'mysql';<br>$dbHost =
'localhost';<br>$dbName = 'database';<br>$dbUser = 'username';<br>$dbPassword =
'password';<br><br>/* Start by creating a normal ADODB connection.<br>
*/<br>$db = ADONewConnection( $platform );<br>$db->Connect( $dbHost,
$dbUser, $dbPassword, $dbName );<br><br>/* Use the database connection to
create a new adoSchema object.<br> */<br>$schema = new adoSchema( $db
);<br><br>/* Call ParseSchema() to buil
d SQL from the XML schema file.<br> * Then call ExecuteSchema() to apply the
resulting SQL
to <br> * the database.<br> */<br>$sql = $schema->ParseSchema( $schemaFile
);<br>$result = $schema->ExecuteSchema();<br>?><br></pre></pre>
+<pre class="listing"><pre><?PHP<br>/* You must tell the script where to
find the ADOdb and<br> * the AXMLS libraries.<br> */
+require( "path_to_adodb/adodb.inc.php");
+require( "path_to_adodb/adodb-xmlschema.inc.php" ); # or
adodb-xmlschema03.inc.php
+
+/* Configuration information. Define the schema filename,<br> * RDBMS platform
(see the ADODB documentation for valid<br> * platform names), and database
connection information here.<br> */<br>$schemaFile =
'example.xml';<br>$platform = 'mysql';<br>$dbHost = 'localhost';<br>$dbName =
'database';<br>$dbUser = 'username';<br>$dbPassword = 'password';<br><br>/*
Start by creating a normal ADODB connection.<br> */<br>$db = ADONewConnection(
$platform );<br>$db->Connect( $dbHost, $dbUser, $dbPassword, $dbName
);<br><br>/* Use the database connection to create a new adoSchema object.<br>
*/<br>$schema = new adoSchema( $db );<br><br>/* Call ParseSchema() to build SQL
from the XML schema file.<br> * Then call ExecuteSchema() to apply the
resulting SQL to <br> * the database.<br> */<br>$sql = $schema->ParseSchema(
$schemaFile );<br>$result = $schema->ExecuteSchema();<br>?><br></pre></
pre>
<p>Let's look at each part of the example in turn. After you manually
create the database, there are three steps required to load (or
upgrade) your schema.</p>
@@ -260,11 +255,11 @@
<p>Second, create the adoSchema object that load and manipulate your
schema. You must pass an ADOdb database connection object in order to
create the adoSchema object.</p>
-<pre class="listing"><pre>$schema = new adoSchema( $db );<br></pre></pre>
+<pre class="listing">$schema = new adoSchema( $db );<br></pre>
<p>Third, call ParseSchema() to parse the schema and then
ExecuteSchema() to apply it to the database. You must pass
ParseSchema() the path and filename of your schema file.</p>
-<pre class="listing"><pre><br>$schema->ParseSchema( $schemaFile );
<br>$schema->ExecuteSchema(); <br></pre></pre>
+<pre class="listing">$schema->ParseSchema( $schemaFile );
<br>$schema->ExecuteSchema();</pre>
<p>Execute the above code and then log into your database. If you've
done all this right, you should see your tables, indexes, and SQL.</p>
<p>You can find the source files for this tutorial in the examples
@@ -272,14 +267,41 @@
documentation for a more detailed description of the adoSchema methods,
including methods and schema elements that are not described in this
tutorial.</p>
+<h3>XML Schema Version 3</h3>
+<p>In March 2006, we added adodb-xmlschema03.inc.php to the release, which
supports version 3 of XML Schema.
+The adodb-xmlschema.inc.php remains the same as previous releases, and
supports version 2 of XML Schema.
+Version 3 provides some enhancements:
+
+<ul>
+ <li> Support for updating table data during an upgrade.
+ <li> Support for platform-specific table options and platform negation.
+ <li> Support for unsigned fields.
+ <li> Fixed opt and constraint support
+ <li> Many other fixes such as OPT tag, which allows you to set optional
platform settings:
+</ul>
+
+<p>Example usage:
+<pre><?xml version="1.0"?>
+<b><schema version="0.3"></b>
+ <table name="ats_kb">
+ <descr>ATS KnowledgeBase</descr>
+ <opt platform="mysql">TYPE=INNODB</opt>
+ <field name="recid" type="I"/>
+ <field name="organization_code" type="I4"/>
+ <field name="sub_code" type="C" size="20"/>
+ etc...
+</pre>
+<p>To use it, change your code to include adodb-xmlschema03.inc.php.
+
<h3>Upgrading</h3>
+<p>
If your schema version is older, than XSLT is used to transform the
schema to the newest version. This means that if you are using an older
XML schema format, you need to have the XSLT extension installed.
If you do not want to require your users to have the XSLT extension
installed, make sure you modify your XML schema to conform to the
latest version.
-<hr>
+<hr />
<address>If you have any questions or comments, please email them to
Richard at richtl#arscognita.com.
</address>
Index: docs-session.htm
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/adodb/docs/docs-session.htm,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -d -r1.3 -r1.4
--- docs-session.htm 12 Feb 2005 20:01:24 -0000 1.3
+++ docs-session.htm 22 Apr 2006 11:09:48 -0000 1.4
@@ -21,24 +21,11 @@
<body style="background-color: rgb(255, 255, 255);">
<h3>ADODB Session Management Manual</h3>
<p>
-V4.60 24 Jan 2005 (c) 2000-2005 John Lim (jlim#natsoft.com.my)
+V4.80 8 Mar 2006 (c) 2000-2006 John Lim (jlim#natsoft.com.my)
</p>
<p> <font size="1">This software is dual licensed using BSD-Style and
LGPL. This means you can use it in compiled proprietary and commercial
products. </font>
-<table border="1">
- <tbody>
- <tr>
- <td><font color="red">Kindly note that the ADOdb home page has
-moved to <a
href="http://adodb.sourceforge.net/";>http://adodb.sourceforge.net/</a>
-because of the persistent unreliability of http://php.weblogs.com. <b>Please
-change your links</b>!</font></td>
- </tr>
- <tr>
- </tr>
- </tbody>
-</table>
-<p></p>
<p>Useful ADOdb links: <a
href="http://adodb.sourceforge.net/#download";>Download</a>
<a href="http://adodb.sourceforge.net/#docs";>Other Docs</a>
</p>
@@ -61,9 +48,12 @@
<li>Need to do special processing of each session</li>
<li>Require notification when a session expires</li>
</ul>
-<p>Then the ADOdb session handler provides you with the above
+<p>The ADOdb session handler provides you with the above
additional capabilities by storing the session information as records
in a database table that can be shared across multiple servers. </p>
+<p>These records will be garbage collected based on the php.ini [session]
timeout settings.
+You can register a notification function to notify you when the record has
expired and
+is about to be freed by the garbage collector.</p>
<p><b>Important Upgrade Notice:</b> Since ADOdb 4.05, the session files
have been moved to its own folder, adodb/session. This is a rewrite
of the session code by Ross Smith. The old session code is in
@@ -92,7 +82,7 @@
<p><pre>
<font
color="#004040"> include('adodb/adodb.inc.php');<br> <br><b>
$ADODB_SESSION_DRIVER='mysql';<br> $ADODB_SESSION_CONNECT='localhost';<br>
$ADODB_SESSION_USER ='scott';<br> $ADODB_SESSION_PWD ='tiger';<br>
$ADODB_SESSION_DB ='sessiondb';</b><br> <br>
<b>include('adodb/session/adodb-session.php');</b><br> session_start();<br>
<br> #<br> # Test session vars, the following should increment on
refresh<br> #<br> $_SESSION['AVAR'] += 1;<br> print
"<p>\$_SESSION['AVAR']={$_SESSION['AVAR']}</p>";<br></font></pre>
-
+
<p>To force non-persistent connections, call adodb_session_open() first before
session_start():
<p>
<pre>
@@ -136,8 +126,22 @@
<h4>Installation</h4>
<p>1. Create this table in your database (syntax might vary depending on your
db):
<p><pre> <a
- name="sessiontab"></a> <font color="#004040"><br> create table sessions
(<br> SESSKEY char(32) not null,<br> EXPIRY int(11) unsigned not
null,<br> EXPIREREF varchar(64),<br> DATA text not null,<br>
primary key (sesskey)<br> );</font><br>
+ name="sessiontab"></a> <font color="#004040"><br> create table sessions
(<br> SESSKEY char(32) not null,<br> EXPIRY int(11) unsigned not
null,<br> EXPIREREF varchar(64),<br> DATA text not null,<br>
primary key (sesskey)<br> );</font><br>
</pre>
+ <p>You may want to rename the 'data' field to 'session_data' as
+ 'data' appears to be a reserved word for one or more of the following:
+ <ul>
+ <li> ANSI SQL
+ <li> IBM DB2
+ <li> MS SQL Server
+ <li> Postgres
+ <li> SAP
+ </ul>
+<p>
+ If you do, then execute:
+<pre>
+ ADODB_Session::dataFieldName('session_data');
+</pre>
<p> For the adodb-session-clob.php version, create this:
<p> <pre>
<font
@@ -149,22 +153,35 @@
</pre><p>
When the session is created, $<b>ADODB_SESS_CONN</b> holds the connection
object.<br> <br> 3. Recommended is PHP 4.0.6 or later. There are documented
session bugs in earlier versions of PHP.
<h3>Notifications</h3>
-<p>If you want to receive notification when a session expires, then tag
-the session record with a <a href="#sessiontab">EXPIREREF</a> tag (see
-the definition of the sessions table above). Before any session record
-is deleted, ADOdb will call a notification function, passing in the
-EXPIREREF.
-</p>
-<p>When a session is first created, we check a global variable
-$ADODB_SESSION_EXPIRE_NOTIFY. This is an array with 2 elements, the
+<p>You can receive notification when your session is cleaned up by the session
garbage collector or
+when you call session_destroy().
+<p>PHP's session extension will automatically run a special garbage collection
function based on
+your php.ini session.cookie_lifetime and session.gc_probability settings. This
will in turn call
+adodb's garbage collection function, which can be setup to do notification.
+<p>
+<pre>
+ PHP Session --> ADOdb Session --> Find all recs --> Send -->
Delete queued
+ GC Function GC Function to be deleted notification
records
+ executed at called by for all recs
+ random time Session Extension queued for
deletion
+</pre>
+<p>When a session is created, we need to store a value in the session record
(in the EXPIREREF field), typically
+the userid of the session. Later when the session has expired, just before
the record is deleted,
+we reload the EXPIREREF field and call the notification function with the
value of EXPIREREF, which
+is the userid of the person being logged off.
+<p>ADOdb use a global variable $ADODB_SESSION_EXPIRE_NOTIFY that you must
predefine before session
+start to store the notification configuratioin.
+$ADODB_SESSION_EXPIRE_NOTIFY is an array with 2 elements, the
first being the name of the session variable you would like to store in
the EXPIREREF field, and the 2nd is the notification function's name. </p>
-<p> Suppose we want to be notified when a user's session has expired,
-based on the userid. The user id in the global session variable
-$USERID. The function name is 'NotifyFn'. So we define: </p>
+<p>For example, suppose we want to be notified when a user's session has
expired,
+based on the userid. When the user logs in, we store the id in the global
session variable
+$USERID. The function name is 'NotifyFn'.
+<p>
+So we define (before session_start() is called): </p>
<pre> <font color="#004040"><br> $ADODB_SESSION_EXPIRE_NOTIFY =
array('USERID','NotifyFn');<br> </font></pre>
-And when the NotifyFn is called (when the session expires), we pass the
-$USERID as the first parameter, eg. NotifyFn($userid, $sesskey). The
+And when the NotifyFn is called (when the session expires), the
+$USERID is passed in as the first parameter, eg. NotifyFn($userid, $sesskey).
The
session key (which is the primary key of the record in the sessions
table) is the 2nd parameter.
<p> Here is an example of a Notification function that deletes some
@@ -173,7 +190,7 @@
<p> NOTE 1: If you have register_globals disabled in php.ini, then you
will have to manually set the EXPIREREF. E.g. </p>
<pre> <font color="#004040">
- $GLOBALS['USERID'] =& $_SESSION['USERID'];
+ $GLOBALS['USERID'] = GetUserID();
$ADODB_SESSION_EXPIRE_NOTIFY = array('USERID','NotifyFn');</font>
</pre>
<p> NOTE 2: If you want to change the EXPIREREF after the session
@@ -202,6 +219,28 @@
<p>These are stackable. E.g.
<p><pre>ADODB_Session::filter(new
ADODB_Compress_Bzip2());<br>ADODB_Session::filter(new
ADODB_Encrypt_MD5());<br></pre>
will compress and then encrypt the record in the database.
+<h3>adodb_session_regenerate_id()</h3>
+<p>Dynamically change the current session id with a newly generated one and
update database. Currently only
+works with cookies. Useful to improve security by reducing the risk of
session-hijacking.
+See this article on <a
href=http://shiflett.org/articles/security-corner-feb2004>Session Fixation</a>
for more info
+on the theory behind this feature. Usage:
+<pre>
+ $ADODB_SESSION_DRIVER='mysql';
+ $ADODB_SESSION_CONNECT='localhost';
+ $ADODB_SESSION_USER ='root';
+ $ADODB_SESSION_PWD ='abc';
+ $ADODB_SESSION_DB ='phplens';
+
+ include('path/to/adodb/session/adodb-session.php');
+
+ session_start();
+ # Every 10 page loads, reset cookie for safety.
+ # This is extremely simplistic example, better
+ # to regenerate only when the user logs in or changes
+ # user privilege levels.
+ if ((rand()%10) == 0) adodb_session_regenerate_id(); </pre>
+<p>This function calls session_regenerate_id() internally or simulates it if
the function does not exist.
+<h2>More Info</h2>
<p>Also see the <a href="docs-adodb.htm">core ADOdb documentation</a>.
</p>
</body>
--- NEW FILE: docs-active-record.htm ---
<html>
<style>
pre {
background-color: #eee;
padding: 0.75em 1.5em;
font-size: 12px;
border: 1px solid #ddd;
}
li,p {
font-family: Arial, Helvetica, sans-serif ;
}
</style>
<title>ADOdb Active Record</title>
<body>
<h1>ADOdb Active Record</h1>
<p> (c) 2000-2006 John Lim (jlim#natsoft.com)</p>
<p><font size="1">This software is dual licensed using BSD-Style and LGPL. This
means you can use it in compiled proprietary and commercial
products.</font></p>
<p><hr>
<ol>
<h3><li>Introduction</h3>
<p>
ADOdb_Active_Record is an Object Relation Mapping (ORM) implementation using
PHP. In an ORM system, the tables and rows of the database are abstracted into
native PHP objects. This allows the programmer to focus more on manipulating
the data and less on writing SQL queries.
<p>
This implementation differs from Zend Framework's implementation in the
following ways:
<ul>
<li>Works with PHP4 and PHP5 and provides equivalent functionality in both
versions of PHP.<p>
<li>ADOdb_Active_Record works when you are connected to multiple databases.
Zend's only works when connected to a default database.<p>
<li>Support for $ADODB_ASSOC_CASE. The field names are upper-cased, lower-cased
or left in natural case depending on this setting.<p>
<li>No field name conversion to camel-caps style, unlike Zend's implementation
which will convert field names such as 'first_name' to 'firstName'.<p>
<li>New ADOConnection::GetActiveRecords() and
ADOConnection::GetActiveRecordsClass() functions in adodb.inc.php.<p>
<li>Caching of table metadata so it is only queried once per table, no matter
how many Active Records are created.<p>
<li>The additional functionality is described <a href=#additional>below</a>.
</ul>
<P>
ADOdb_Active_Record is designed upon the principles of the "ActiveRecord"
design pattern, which was first described by Martin Fowler. The ActiveRecord
pattern has been implemented in many forms across the spectrum of programming
languages. ADOdb_Active_Record attempts to represent the database as closely to
native PHP objects as possible.
<p>
ADOdb_Active_Record maps a database table to a PHP class, and each instance of
that class represents a table row. Relations between tables can also be
defined, allowing the ADOdb_Active_Record objects to be nested.
<p>
<h3><li>Setting the Database Connection</h3>
<p>
The first step to using ADOdb_Active_Record is to set the default connection
that an ADOdb_Active_Record objects will use to connect to a database.
<pre>
require_once('adodb/adodb-active-record.php');
$db = new ADOConnection('mysql://root:pwd@localhost/dbname');
ADOdb_Active_Record::SetDatabaseAdapter($db);
</pre>
<h3><li>Table Rows as Objects</h3>
<p>
First, let's create a temporary table in our MySQL database that we can use for
demonstrative purposes throughout the rest of this tutorial. We can do this by
sending a CREATE query:
<pre>
$db->Execute("CREATE TEMPORARY TABLE `persons` (
`id` int(10) unsigned NOT NULL auto_increment,
`name_first` varchar(100) NOT NULL default '',
`name_last` varchar(100) NOT NULL default '',
`favorite_color` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
");
</pre>
<p>
ADOdb_Active_Record's are object representations of table rows. Each table in
the database is represented by a class in PHP. To begin working with a table as
a ADOdb_Active_Record, a class that extends ADOdb_Active_Records needs to be
created for it.
<pre>
class Person extends ADOdb_Active_Record{}
$person = new Person();
</pre>
<p>
In the above example, a new ADOdb_Active_Record object $person was created to
access the "persons" table. Zend_Db_DataObject takes the name of the class,
pluralizes it (according to American English rules), and assumes that this is
the name of the table in the database.
<p>
This kind of behavior is typical of ADOdb_Active_Record. It will assume as much
as possible by convention rather than explicit configuration. In situations
where it isn't possible to use the conventions that ADOdb_Active_Record
expects, options can be overridden as we'll see later.
<h3><li>Table Columns as Object Properties</h3>
<p>
When the $person object was instantiated, ADOdb_Active_Record read the table
metadata from the database itself, and then exposed the table's columns
(fields) as object properties.
<p>
Our "persons" table has three fields: "name_first", "name_last", and
"favorite_color". Each of these fields is now a property of the $person object.
To see all these properties, use the ADOdb_Active_Record::getAttributeNames()
method:
<pre>
var_dump($person->getAttributeNames());
/**
* Outputs the following:
* array(4) {
* [0]=>
* string(2) "id"
* [1]=>
* string(9) "name_first"
* [2]=>
* string(8) "name_last"
* [3]=>
* string(13) "favorite_color"
* }
*/
</pre>
<p>
One big difference between ADOdb and Zend's implementation is we do not
automatically convert to camelCaps style.
<p>
<h3><li>Inserting and Updating a Record</h3><p>
An ADOdb_Active_Record object is a representation of a single table row.
However, when our $person object is instantiated, it does not reference any
particular row. It is a blank record that does not yet exist in the database.
An ADOdb_Active_Record object is considered blank when its primary key is NULL.
The primary key in our persons table is "id".
<p>
To insert a new record into the database, change the object's properties and
then call the ADOdb_Active_Record::save() method:
<pre>
$person = new Person();
$person->nameFirst = 'Andi';
$person->nameLast = 'Gutmans';
$person->save();
</pre>
<p>
Oh, no! The above code snippet does not insert a new record into the database.
Instead, outputs an error:
<pre>
1048: Column 'name_first' cannot be null
</pre>
<p>
This error occurred because MySQL rejected the INSERT query that was generated
by ADOdb_Active_Record. If exceptions are enabled in ADOdb and you are using
PHP5, an error will be thrown. In the definition of our table, we specified all
of the fields as NOT NULL; i.e., they must contain a value.
<p>
ADOdb_Active_Records are bound by the same contraints as the database tables
they represent. If the field in the database cannot be NULL, the corresponding
property in the ADOdb_Active_Record also cannot be NULL. In the example above,
we failed to set the property $person->favoriteColor, which caused the INSERT
to be rejected by MySQL.
<p>
To insert a new ADOdb_Active_Record in the database, populate all of
ADOdb_Active_Record's properties so that they satisfy the constraints of the
database table, and then call the save() method:
<pre>
/**
* Calling the save() method will successfully INSERT
* this $person into the database table.
*/
$person = new Person();
$person->name_first = 'Andi';
$person->name_last = 'Gutmans';
$person->favorite_color = 'blue';
$person->save();
</pre>
<p>
Once this $person has been INSERTed into the database by calling save(), the
primary key can now be read as a property. Since this is the first row inserted
into our temporary table, its "id" will be 1:
<pre>
var_dump($person->id);
/**
* Outputs the following:
* string(1)
*/
</pre>
<p>
|
