Update of /cvsroot/mantisbt/mantisbt/core/adodb/docs
In directory sc8-pr-cvs16.sourceforge.net:/tmp/cvs-serv28226/docs
Modified Files:
docs-active-record.htm docs-adodb.htm docs-session.htm
old-changelog.htm readme.htm tute.htm
Log Message:
Upgrade to ADODB 4.96 (via tarball at
http://downloads.sourceforge.net/adodb/adodb496.tgz)
Index: docs-adodb.htm
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/adodb/docs/docs-adodb.htm,v
retrieving revision 1.5
retrieving revision 1.6
diff -u -d -r1.5 -r1.6
--- docs-adodb.htm 22 May 2007 05:59:37 -0000 1.5
+++ docs-adodb.htm 27 Sep 2007 22:55:06 -0000 1.6
@@ -531,6 +531,7 @@
<p></p><p>Don't forget to call the constructor of the parent class in
your constructor. If you want to use the default ADOdb drivers return
false in the above hack_factory() function.
+ Also you can define your own ADORecordSet_empty() class, by defining a class
$$this->rsPrefix.'empty' since 4.96/5.02.
<a name="php5"></a>
</p><h2>PHP5 Features</h2>
ADOdb 4.02 or later will transparently determine which version of PHP you are
using.
@@ -2113,7 +2114,8 @@
</font></p><p><font><b>GetOne<a
name="getone"></a>($sql,$inputarr=false)</b></font></p>
<p><font>Executes the SQL and returns the first field of the first row. The
recordset
and remaining rows are discarded for you automatically. If an error occur,
false
- is returned.</font></p>
+ is returned; use ErrorNo() or ErrorMsg() to get the error details.
+ Since 4.96, we return null if no records were found.</font></p>
<p><font><b>GetRow<a name="getrow"></a>($sql,$inputarr=false)</b></font></p>
<p><font>Executes the SQL and returns the first row as an array. The recordset
and remaining
rows are discarded for you automatically. If an error occurs, false is
returned.</font></p>
@@ -2927,6 +2929,26 @@
<h2><font>Change Log<a name="Changes"></a><a name="changes"></a><a
name="changelog"></a></font></h2>
+<p><a name="4.96"></a><b>4.96/5.02 ? ? 2007</b>
+<p>Fix adodb count optimisation. Preg_match did not work properly.
+<p>SelectLimit for oci8 not optimal for large recordsets when offset=0.
Changed $nrows check.
+<p>Rewrote the ORDER BY stripping code in _adodb_getcount(), adodb-lib.inc.php.
+<p>Now GetOne returns null if EOF (no records found), and false if error
occurs. Use ErrorMsg()/ErrorNo() to get the error.
+<p>Added BIT data type support to adodb-ado.inc.php and adodb-ado5.inc.php.
+<p>Ldap driver did not return actual ldap error messages. Fixed.
+<p>Implemented GetRandRow($sql, $inputarr). Optimized for Oci8.
+<p>Changed adodb5 active record to use static SetDatabaseAdapter() and removed
php4 constructor. Bas van Beek bas.vanbeek#gmail.com.
+<p>Also in adodb5, changed adodb-session2 to use static function declarations
in class. Thx Daniel Berlin.
+<p>Added "Clear SQL Log" to bottom of Performance screen.
+<p>Sessions2 code echo'ed directly to the screen in debug mode. Now uses
ADOConnection::outp().
+<p>In mysql/mysqli, qstr(null) will return the string "null" instead of empty
quoted string "''".
+<p>postgresql optimizeTable in perf-postgres.inc.php added by Daniel Berlin
(mail#daniel-berlin.de)
+<p>Added 5.2.1 compat code for oci8.
+<p>Changed @@identity to SCOPE_IDENTITY() for multiple mssql drivers. Thx
Stefano Nari.
+<p>Code sanitization introduced in 4.95 caused problems in European locales
(as float 3.2 was typecast to 3,2). Now we only sanitize if is_numeric fails.
+<p>Added support for customizing ADORecordset_empty using
$this->rsPrefix.'empty'. By Josh Truwin.
+<p>Added proper support for ALterColumnSQL for Postgresql in datadict code.
Thx. Josh Truwin.
+<p>Added better support for MetaType() in mysqli when using an array recordset.
<p><a name="4.95"></a><b>4.95/5.01 17 May 2007</b>
<p>CacheFlush debug outp() passed in invalid parameters. Fixed.
<p>Added Thai language file for adodb. Thx Trirat Petchsingh
rosskouk#gmail.com and Marcos Pont</p>
@@ -2940,7 +2962,7 @@
<p>For ADOdb 5.01, fixed some adodb-datadict.inc.php MetaType compat issues
with PHP5.
<p>The $argHostname was wiped out in adodb-ado5.inc.php. Fixed.
<p>Adodb5 version, added iterator support for adodb_recordset_empty.
-
+<p>Adodb5 version,more error checking code now will use exceptions if
available.
<p><a name="4.94"></a><b>4.94 23 Jan 2007</b>
<p>Active Record: $ADODB_ASSOC_CASE=2 did not work properly. Fixed. Thx
gmane#auxbuss.com.
<p>mysqli had bugs in BeginTrans() and EndTrans(). Fixed.</p>
Index: old-changelog.htm
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/adodb/docs/old-changelog.htm,v
retrieving revision 1.5
retrieving revision 1.6
diff -u -d -r1.5 -r1.6
--- old-changelog.htm 22 May 2007 05:59:37 -0000 1.5
+++ old-changelog.htm 27 Sep 2007 22:55:06 -0000 1.6
@@ -1,822 +1,822 @@
-<html><title>Old Changelog: ADOdb</title><body>
-<h3>Old Changelog</h3>
-
-</p><p><b>3.92 22 Sept 2003</b>
-</p><p>Added GetAssoc and CacheGetAssoc to connection object.
-</p><p>Removed TextMax and CharMax functions from adodb.inc.php.
-</p><p>HasFailedTrans() returned false when trans failed. Fixed.
-</p><p>Moved perf driver classes into adodb/perf/*.php.
-</p><p>Misc improvements to performance monitoring, including UI().
-</p><p>RETVAL in mssql Parameter(), we do not append @ now.
-</p><p>Added Param($name) to connection class, returns '?' or ":$name", for
defining
[...1613 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-session.htm
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/adodb/docs/docs-session.htm,v
retrieving revision 1.5
retrieving revision 1.6
diff -u -d -r1.5 -r1.6
--- docs-session.htm 22 May 2007 05:59:37 -0000 1.5
+++ docs-session.htm 27 Sep 2007 22:55:06 -0000 1.6
@@ -267,6 +267,40 @@
record has been created, you will need to modify any session variable
to force a database record update.
</p>
+<h3>Synchronizing Page Frames</h3>
+<p>Sometimes you need to load multiple pages in a frameset and make sure that
the session variables are synchronized.
+For example we have a page frame.php with an iframe child1.php:
+<pre>
+ frame.php
+ child1.php
+</pre>
+<p>Assume frame.php receives some $_GET variables and it updates the $_SESSION
variables in memory. However the $_SESSION variables are only
+updated in the database when the page ends. So is possible that child1.php
+will still have the old $_SESSION variables.
+<ul>
+<li> $_GET variables are posted to frame.php
+<li> frame.php updates $_SESSION variables, but not the database
+<li> child1.php is sent to browser, using old $_SESSION variables it loads
from database
+<li> frame.php completes it's processing and saves new $_SESSION variables
into database -- too late!
+</ul>
+ <p>We can fix this by using the php function <a
href=http://php.net/session_commit>session_commit</a>:
+<pre>
+<?php
+session_start();
+.
+# do any required processing...
+
+session_commit();
+
+# now session variables have been saved to the database, and child1.php will
get the latest $_SESSION variables.
+?>
+<html>
+ <body>
+ <iframe src=child1.php>
+ </iframe>
+ </body>
+</html>
+</pre>
<h3>Neat Notification Tricks</h3>
<p><i>ExpireRef</i> normally holds the user id of the current session.
</p>
Index: docs-active-record.htm
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/adodb/docs/docs-active-record.htm,v
retrieving revision 1.2
retrieving revision 1.3
diff -u -d -r1.2 -r1.3
--- docs-active-record.htm 22 May 2007 05:59:37 -0000 1.2
+++ docs-active-record.htm 27 Sep 2007 22:55:06 -0000 1.3
@@ -501,6 +501,9 @@
<p>PHP5 specific: Change PHP5 implementation of Active Record to use __get()
and __set() for better performance.
<h3> Change Log</h3>
+<p>0.08
+Added support for assoc arrays in Set().
+
<p>0.07
<p>$ADODB_ASSOC_CASE=2 did not work properly. Fixed.
<p>Added === check in ADODB_SetDatabaseAdapter for $db,
adodb-active-record.inc.php. Thx Christian Affolter.
Index: tute.htm
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/adodb/docs/tute.htm,v
retrieving revision 1.5
retrieving revision 1.6
diff -u -d -r1.5 -r1.6
--- tute.htm 22 May 2007 05:59:38 -0000 1.5
+++ tute.htm 27 Sep 2007 22:55:06 -0000 1.6
@@ -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: readme.htm
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/adodb/docs/readme.htm,v
retrieving revision 1.5
retrieving revision 1.6
diff -u -d -r1.5 -r1.6
--- readme.htm 22 May 2007 05:59:38 -0000 1.5
+++ readme.htm 27 Sep 2007 22:55:06 -0000 1.6
@@ -1,68 +1,68 @@
-<html>
-<head>
-<title>ADODB Manual</title>
-<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
-<XSTYLE
- body,td {font-family:Arial,Helvetica,sans-serif;font-size:11pt}
- pre {font-size:9pt}
- .toplink {font-size:8pt}
- />
-</head>
-<body bgcolor="#FFFFFF">
-
-<h3>ADOdb Library for PHP</h3>
-<p>ADOdb is a suite of database libraries that allow you to connect to
multiple
- databases in a portable manner. Download from <a
href=http://adodb.sourceforge.net/>http://adodb.sourceforge.net/</a>.
-<ul><li>The ADOdb documentation has moved to <a
href=docs-adodb.htm>docs-adodb.htm</a>
- This allows you to query, update and insert records using a portable
API.
-<p><li>The ADOdb data dictionary docs are at <a
href=docs-datadict.htm>docs-datadict.htm</a>.
- This allows you to create database tables and indexes in a portable
manner.
-<p><li>The ADOdb database performance monitoring docs are at <a
href=docs-perf.htm>docs-perf.htm</a>.
- This allows you to perform health checks, tune and monitor your
database.
-<p><li>The ADOdb database-backed session docs are at <a
href=docs-session.htm>docs-session.htm</a>.
-</ul>
-<p>
-<h3>Installation</h3>
-Make sure you are running PHP4.0.4 or later. Unpack all the files into a
directory accessible by your webserver.
-<p>
-To test, try modifying some of the tutorial examples. Make sure you customize
the connection settings correctly. You can debug using:
-<pre>
-<?php
-include('adodb/adodb.inc.php');
-
-$db = <b>ADONewConnection</b>($driver); # eg. 'mysql' or 'oci8'
-$db->debug = true;
-$db-><b>Connect</b>($server, $user, $password, $database);
-$rs = $db-><b>Execute</b>('select * from some_small_table');
-print "<pre>";
-print_r($rs-><b>GetRows</b>());
-print "</pre>";
-?>
-</pre>
-<h3>How are people using ADOdb</h3>
-Here are some examples of how people are using ADOdb:
-<ul>
- <li> <strong>PhpLens</strong> is a commercial data grid component that
allows
- both cool Web designers and serious unshaved programmers to
develop and
- maintain databases on the Web easily. Developed by the author
of ADOdb.
- </li>
- <li> <strong>PHAkt</strong>: PHP Extension for DreamWeaver Ultradev
allows
- you to script PHP in the popular Web page editor. Database
handling provided
- by ADOdb. </li>
- <li> <strong>Analysis Console for Intrusion Databases (ACID)</strong>:
PHP-based
- analysis engine to search and process a database of security
incidents
- generated by security-related software such as IDSes and
firewalls (e.g.
- Snort, ipchains). By Roman Danyliw. </li>
- <li> <strong>PostNuke</strong> is a very popular free content
management system
- and weblog system. It offers full CSS support, HTML 4.01
transitional
- compliance throughout, an advanced blocks system, and is fully
multi-lingual
- enabled. </li>
- <li><strong> EasyPublish CMS</strong> is another free content
management system
- for managing information and integrated modules on your
internet, intranet-
- and extranet-sites. From Norway. </li>
- <li> <strong>NOLA</strong> is a full featured accounting, inventory,
and job
- tracking application. It is licensed under the GPL, and
developed by Noguska.
- </li>
-</ul>
-</body>
-</html>
+<html>
+<head>
+<title>ADODB Manual</title>
+<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
+<XSTYLE
+ body,td {font-family:Arial,Helvetica,sans-serif;font-size:11pt}
+ pre {font-size:9pt}
+ .toplink {font-size:8pt}
+ />
+</head>
+<body bgcolor="#FFFFFF">
+
+<h3>ADOdb Library for PHP</h3>
+<p>ADOdb is a suite of database libraries that allow you to connect to
multiple
+ databases in a portable manner. Download from <a
href=http://adodb.sourceforge.net/>http://adodb.sourceforge.net/</a>.
+<ul><li>The ADOdb documentation has moved to <a
href=docs-adodb.htm>docs-adodb.htm</a>
+ This allows you to query, update and insert records using a portable
API.
+<p><li>The ADOdb data dictionary docs are at <a
href=docs-datadict.htm>docs-datadict.htm</a>.
+ This allows you to create database tables and indexes in a portable
manner.
+<p><li>The ADOdb database performance monitoring docs are at <a
href=docs-perf.htm>docs-perf.htm</a>.
+ This allows you to perform health checks, tune and monitor your
database.
+<p><li>The ADOdb database-backed session docs are at <a
href=docs-session.htm>docs-session.htm</a>.
+</ul>
+<p>
+<h3>Installation</h3>
+Make sure you are running PHP4.0.4 or later. Unpack all the files into a
directory accessible by your webserver.
+<p>
+To test, try modifying some of the tutorial examples. Make sure you customize
the connection settings correctly. You can debug using:
+<pre>
+<?php
+include('adodb/adodb.inc.php');
+
+$db = <b>ADONewConnection</b>($driver); # eg. 'mysql' or 'oci8'
+$db->debug = true;
+$db-><b>Connect</b>($server, $user, $password, $database);
+$rs = $db-><b>Execute</b>('select * from some_small_table');
+print "<pre>";
+print_r($rs-><b>GetRows</b>());
+print "</pre>";
+?>
+</pre>
+<h3>How are people using ADOdb</h3>
+Here are some examples of how people are using ADOdb:
+<ul>
+ <li> <strong>PhpLens</strong> is a commercial data grid component that
allows
+ both cool Web designers and serious unshaved programmers to
develop and
+ maintain databases on the Web easily. Developed by the author
of ADOdb.
+ </li>
+ <li> <strong>PHAkt</strong>: PHP Extension for DreamWeaver Ultradev
allows
+ you to script PHP in the popular Web page editor. Database
handling provided
+ by ADOdb. </li>
+ <li> <strong>Analysis Console for Intrusion Databases (ACID)</strong>:
PHP-based
+ analysis engine to search and process a database of security
incidents
+ generated by security-related software such as IDSes and
firewalls (e.g.
+ Snort, ipchains). By Roman Danyliw. </li>
+ <li> <strong>PostNuke</strong> is a very popular free content
management system
+ and weblog system. It offers full CSS support, HTML 4.01
transitional
+ compliance throughout, an advanced blocks system, and is fully
multi-lingual
+ enabled. </li>
+ <li><strong> EasyPublish CMS</strong> is another free content
management system
+ for managing information and integrated modules on your
internet, intranet-
+ and extranet-sites. From Norway. </li>
+ <li> <strong>NOLA</strong> is a full featured accounting, inventory,
and job
+ tracking application. It is licensed under the GPL, and
developed by Noguska.
+ </li>
+</ul>
+</body>
+</html>
-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2005.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
|
