ADOdb Library for PHP

V5.06 16 Oct 2008 (c) 2000-2008 John Lim (jlim#natsoft.com)

This software is dual licensed using BSD-Style and LGPL. This means you can use it in compiled proprietary and commercial products.

Useful ADOdb links: Download Other Docs

Introduction
Unique Features
How People are using ADOdb
Feature Requests and Bug Reports
Installation
Minimum Install
Initializing Code and Connectioning to Databases
Data Source Name (DSN) Support Connection Examples
High Speed ADOdb - tuning tips
Hacking and Modifying ADOdb Safely
PHP5 Features
foreach iterators exceptions
Supported Databases
Tutorials
Example 1: Select
Example 2: Advanced Select
Example 3: Insert
Example 4: Debugging rs2html example
Example 5: MySQL and Menus
Example 6: Connecting to Multiple Databases at once
Example 7: Generating Update and Insert SQL
Example 8: Implementing Scrolling with Next and Previous
Example 9: Exporting in CSV or Tab-Delimited Format
Example 10: Custom filters
Example 11: Smart Transactions

Using Custom Error Handlers and PEAR_Error
Data Source Names
Caching
MemCache
Caching API
Pivot Tables

REFERENCE

Variables: $ADODB_COUNTRECS $ADODB_ANSI_PADDING_OFF $ADODB_CACHE_DIR
$ADODB_FORCE_TYPE $ADODB_FETCH_MODE $ADODB_LANG ADODB_QUOTE_FIELDNAMES
Constants: ADODB_ASSOC_CASE
ADOConnection
Connections: Connect PConnect NConnect IsConnected
Executing SQL: Execute CacheExecute SelectLimit CacheSelectLimit Param Prepare PrepareSP InParameter OutParameter AutoExecute
GetOne CacheGetOne GetRow CacheGetRow GetAll CacheGetAll GetCol CacheGetCol GetAssoc CacheGetAssoc Replace GetMedian
ExecuteCursor (oci8 only)
Generates SQL strings: GetUpdateSQL GetInsertSQL Concat IfNull length random substr qstr Param OffsetDate SQLDate DBDate DBTimeStamp BindDate BindTimeStamp
Blobs: UpdateBlob UpdateClob UpdateBlobFile BlobEncode BlobDecode
Paging/Scrolling: PageExecute CachePageExecute
Cleanup: CacheFlush Close
Transactions: StartTrans CompleteTrans FailTrans HasFailedTrans BeginTrans CommitTrans RollbackTrans SetTransactionMode
Fetching Data: SetFetchMode
Strings: concat length qstr quote substr
Dates: DBDate DBTimeStamp UnixDate BindDate BindTimeStamp UnixTimeStamp OffsetDate SQLDate
Row Management: Affected_Rows Insert_ID RowLock GenID CreateSequence DropSequence
Error Handling: ErrorMsg ErrorNo MetaError MetaErrorMsg IgnoreErrors
Data Dictionary (metadata): MetaDatabases MetaTables MetaColumns MetaColumnNames MetaPrimaryKeys MetaForeignKeys ServerInfo
Statistics and Query-Rewriting: LogSQL fnExecute and fnCacheExecute
Deprecated: Bind BlankRecordSet Parameter
ADORecordSet
Returns one field: Fields
Returns one row:FetchRow FetchInto FetchObject FetchNextObject FetchObj FetchNextObj GetRowAssoc
Returns all rows:GetArray GetRows GetAssoc
Scrolling:Move MoveNext MoveFirst MoveLast AbsolutePosition CurrentRow AtFirstPage AtLastPage AbsolutePage
Menu generation:GetMenu GetMenu2
Dates:UserDate UserTimeStamp UnixDate UnixTimeStamp
Recordset Info:RecordCount PO_RecordCount NextRecordSet
Field Info:FieldCount FetchField MetaType
Cleanup: Close

rs2html example
Differences between ADOdb and ADO
Database Driver Guide
Change Log

Introduction

PHP's database access functions are not standardised. This creates a need for a database class library to hide the differences between the different database API's (encapsulate the differences) so we can easily switch databases. PHP 4.0.5 or later is now required (because we use array-based str_replace).

We currently support MySQL, Oracle, Microsoft SQL Server, Sybase, Sybase SQL Anywhere, Informix, PostgreSQL, FrontBase, SQLite, Interbase (Firebird and Borland variants), Foxpro, Access, ADO, DB2, SAP DB and ODBC. We have had successful reports of connecting to Progress and CacheLite via ODBC. We hope more people will contribute drivers to support other databases.

PHP4 supports session variables. You can store your session information using ADOdb for true portability and scalability. See adodb-session.php for more information.

Also read tips_portable_sql.htm for tips on writing portable SQL.

Unique Features of ADOdb

• Easy for Windows programmers to adapt to because many of the conventions are similar to Microsoft's ADO.
• Unlike other PHP database classes which focus only on select statements, we provide support code to handle inserts and updates which can be adapted to multiple databases quickly. Methods are provided for date handling, string concatenation and string quoting characters for differing databases.
• A metatype system is built in so that we can figure out that types such as CHAR, TEXT and STRING are equivalent in different databases.
• Easy to port because all the database dependant code are stored in stub functions. You do not need to port the core logic of the classes.
• Portable table and index creation with the datadict classes.
• Database performance monitoring and SQL tuning with the performance monitoring classes.
• Database-backed sessions with the session management classes. Supports session expiry notification.
• Object-Relational Mapping using ADOdb_Active_Record classes.

How People are using ADOdb

Here are some examples of how people are using ADOdb (for a much longer list, visit adodb-cool-apps):

• PhpLens 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.
• PHAkt: PHP Extension for DreamWeaver Ultradev allows you to script PHP in the popular Web page editor. Database handling provided by ADOdb.
• Analysis Console for Intrusion Databases (ACID): 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.
• PostNuke 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.
• EasyPublish CMS is another free content management system for managing information and integrated modules on your internet, intranet- and extranet-sites. From Norway.
• NOLA is a full featured accounting, inventory, and job tracking application. It is licensed under the GPL, and developed by Noguska.

Feature Requests and Bug Reports

Feature requests and bug reports can be emailed to jlim#natsoft.com or posted to the ADOdb Help forums at http://phplens.com/lens/lensforum/topics.php?id=4.

Installation Guide

Make sure you are running PHP 4.0.5 or later. Unpack all the files into a directory accessible by your webserver.

To test, try modifying some of the tutorial examples. Make sure you customize the connection settings correctly. You can debug using $db->debug = true as shown below:

<?php

 include('adodb/adodb.inc.php');

 $db = ADONewConnection($dbdriver); # eg 'mysql' or 'postgres'

 $db->debug = true;

 $db->Connect($server, $user, $password, $database);

 $rs = $db->Execute('select * from some_small_table');

 print "<pre>";

 print_r($rs->GetRows());

 print "</pre>";

?>

Minimum Install

For developers who want to release a minimal install of ADOdb, you will need:

• adodb.inc.php
• adodb-lib.inc.php
• adodb-time.inc.php
• drivers/adodb-$database.inc.php
• license.txt (for legal reasons)
• adodb-php4.inc.php
• adodb-iterator.inc.php (php5 functionality)

Optional:

• adodb-error.inc.php and lang/adodb-$lang.inc.php (if you use MetaError())
• adodb-csvlib.inc.php (if you use cached recordsets - CacheExecute(), etc)
• adodb-exceptions.inc.php and adodb-errorhandler.inc.php (if you use adodb error handler or php5 exceptions).
• adodb-active-record.inc.php if you use Active Records.

Code Initialization Examples

When running ADOdb, at least two files are loaded. First is adodb/adodb.inc.php, which contains all functions used by all database classes. The code specific to a particular database is in the adodb/driver/adodb-????.inc.php file.

For example, to connect to a mysql database:

include('/path/to/set/here/adodb.inc.php');

$conn = &ADONewConnection('mysql');

Whenever you need to connect to a database, you create a Connection object using the ADONewConnection($driver) function. NewADOConnection($driver) is an alternative name for the same function.

At this point, you are not connected to the database (no longer true if you pass in a dsn). You will first need to decide whether to use persistent or non-persistent connections. The advantage of persistent connections is that they are faster, as the database connection is never closed (even when you call Close()). Non-persistent connections take up much fewer resources though, reducing the risk of your database and your web-server becoming overloaded.

For persistent connections, use $conn->PConnect(), or $conn->Connect() for non-persistent connections. Some database drivers also support NConnect(), which forces the creation of a new connection.

Connection Gotcha: If you create two connections, but both use the same userid and password, PHP will share the same connection. This can cause problems if the connections are meant to different databases. The solution is to always use different userid's for different databases, or use NConnect().

Data Source Name (DSN) Support

Since ADOdb 4.51, you can connect to a database by passing a dsn to NewADOConnection() (or ADONewConnection, which is the same function). The dsn format is:

 $driver://$username:$password@hostname/$database?options[=value]

NewADOConnection() calls Connect() or PConnect() internally for you. If the connection fails, false is returned.

 # non-persistent connection
 $dsn = 'mysql://root:pwd@localhost/mydb'; 
 $db = NewADOConnection($dsn);
 if (!$db) die("Connection failed"); 
 
 # no need to call connect/pconnect!
 $arr = $db->GetArray("select * from table");
 
 # persistent connection
 $dsn2 = 'mysql://root:pwd@localhost/mydb?persist'; 

If you have special characters such as /:?_ in your dsn, then you need to rawurlencode them first:

 $pwd = rawurlencode($pwd);

 $dsn = "mysql://root:$pwd@localhost/mydb";
 $dsn2=rawurlencode("sybase_ase")."://user:pass@host/path?query";

Legal options are:

For all drivers	'persist', 'persistent', 'debug', 'fetchmode', 'new'
Interbase/Firebird	'dialect','charset','buffers','role'
M'soft ADO	'charpage'
MySQL	'clientflags'
MySQLi	'port', 'socket', 'clientflags'
Oci8	'nls_date_format','charset'

For all drivers, when the options persist or persistent are set, a persistent connection is forced; similarly, when new is set, then a new connection will be created using NConnect if the underlying driver supports it. The debug option enables debugging. The fetchmode calls SetFetchMode(). If no value is defined for an option, then the value is set to 1.

ADOdb DSN's are compatible with version 1.0 of PEAR DB's DSN format.

Examples of Connecting to Databases

MySQL and Most Other Database Drivers

MySQL connections are very straightforward, and the parameters are identical to mysql_connect:

 $conn = &ADONewConnection('mysql'); 

 $conn->PConnect('localhost','userid','password','database');

 

 # or dsn 
 $dsn = 'mysql://user:pwd@localhost/mydb'; 
 $conn = ADONewConnection($dsn); # no need for Connect()
 
 # or persistent dsn
 $dsn = 'mysql://user:pwd@localhost/mydb?persist'; 
 $conn = ADONewConnection($dsn); # no need for PConnect()
 
 # a more complex example:
 $pwd = urlencode($pwd);
 $flags = MYSQL_CLIENT_COMPRESS;
 $dsn = "mysql://user:$pwd@localhost/mydb?persist&clientflags=$flags";
 $conn = ADONewConnection($dsn); # no need for PConnect()
 

For most drivers, you can use the standard function: Connect($server, $user, $password, $database), or a DSN since ADOdb 4.51. Exceptions to this are listed below.

PDO

PDO, which only works with PHP5, accepts a driver specific connection string:

 
 $conn =& NewADConnection('pdo');
 $conn->Connect('mysql:host=localhost',$user,$pwd,$mydb);
 $conn->Connect('mysql:host=localhost;dbname=mydb',$user,$pwd);
 $conn->Connect("mysql:host=localhost;dbname=mydb;username=$user;password=$pwd");

The DSN mechanism is also supported:

 
 $conn =& NewADConnection("pdo_mysql://user:pwd@localhost/mydb?persist"); # persist is optional

PostgreSQL

PostgreSQL 7 and 8 accepts connections using:

a. the standard connection string:

 $conn = &ADONewConnection('postgres'); 

 $conn->PConnect('host=localhost port=5432 dbname=mary');

b. the classical 4 parameters:

 $conn->PConnect('localhost','userid','password','database');

 

c. dsn:

 $dsn = 'postgres://user:pwd@localhost/mydb?persist'; # persist is optional
 $conn = ADONewConnection($dsn); # no need for Connect/PConnect

LDAP

Here is an example of querying a LDAP server. Thanks to Josh Eldridge for the driver and this example:

 
require('/path/to/adodb.inc.php');
 
/* Make sure to set this BEFORE calling Connect() */
$LDAP_CONNECT_OPTIONS = Array(
 Array ("OPTION_NAME"=>LDAP_OPT_DEREF, "OPTION_VALUE"=>2),
 Array ("OPTION_NAME"=>LDAP_OPT_SIZELIMIT,"OPTION_VALUE"=>100),
 Array ("OPTION_NAME"=>LDAP_OPT_TIMELIMIT,"OPTION_VALUE"=>30),
 Array ("OPTION_NAME"=>LDAP_OPT_PROTOCOL_VERSION,"OPTION_VALUE"=>3),
 Array ("OPTION_NAME"=>LDAP_OPT_ERROR_NUMBER,"OPTION_VALUE"=>13),
 Array ("OPTION_NAME"=>LDAP_OPT_REFERRALS,"OPTION_VALUE"=>FALSE),
 Array ("OPTION_NAME"=>LDAP_OPT_RESTART,"OPTION_VALUE"=>FALSE)
);
$host = 'ldap.baylor.edu';
$ldapbase = 'ou=People,o=Baylor University,c=US';
 
$ldap = NewADOConnection( 'ldap' );
$ldap->Connect( $host, $user_name='', $password='', $ldapbase );
 
echo "<pre>";
 
print_r( $ldap->ServerInfo() );
$ldap->SetFetchMode(ADODB_FETCH_ASSOC);
$userName = 'eldridge';
$filter="(|(CN=$userName*)(sn=$userName*)(givenname=$userName*)(uid=$userName*))";
 
$rs = $ldap->Execute( $filter );
if ($rs)
 while ($arr = $rs->FetchRow()) {
  print_r($arr); 
 }
 
$rs = $ldap->Execute( $filter );
if ($rs) 
 while (!$rs->EOF) {
  print_r($rs->fields); 
 $rs->MoveNext();
 } 
 
print_r( $ldap->GetArray( $filter ) );
print_r( $ldap->GetRow( $filter ) );
 
$ldap->Close();
echo "</pre>";

Using DSN:

 
$dsn = "ldap://ldap.baylor.edu/ou=People,o=Baylor University,c=US";
$db = NewADOConnection($dsn);

Interbase/Firebird

You define the database in the $host parameter:

 $conn = &ADONewConnection('ibase'); 

 $conn->PConnect('localhost:c:\ibase\employee.gdb','sysdba','masterkey');

Or dsn:

 $dsn = 'firebird://user:pwd@localhost/mydb?persist&dialect=3'; # persist is optional

 $conn = ADONewConnection($dsn); # no need for Connect/PConnect

SQLite

Sqlite will create the database file if it does not exist.

 $conn = &ADONewConnection('sqlite');
 $conn->PConnect('c:\path\to\sqlite.db'); # sqlite will create if does not exist

Or dsn:

 $path = urlencode('c:\path\to\sqlite.db');
 $dsn = "sqlite://$path/?persist"; # persist is optional
 $conn = ADONewConnection($dsn); # no need for Connect/PConnect

Oracle (oci8)

With oci8, you can connect in multiple ways. Note that oci8 works fine with newer versions of the Oracle, eg. 9i and 10g.

a. PHP and Oracle reside on the same machine, use default SID.

 $conn->Connect(false, 'scott', 'tiger');

b. TNS Name defined in tnsnames.ora (or ONAMES or HOSTNAMES), eg. 'myTNS'

 $conn->PConnect(false, 'scott', 'tiger', 'myTNS');

or

  $conn->PConnect('myTNS', 'scott', 'tiger');

c. Host Address and SID

 
 # with adodb 5.06 or 4.991 and later
 $conn->Connect('192.168.0.1', 'scott', 'tiger', "SID=$SID");
 
 # OR with all versions of ADOdb
 $conn->connectSID = true;
 $conn->Connect('192.168.0.1', 'scott', 'tiger', $SID);
 

d. Host Address and Service Name

 $conn->Connect('192.168.0.1', 'scott', 'tiger', 'servicename');

e. Oracle connection string:

 $cstr = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$host)(PORT=$port))

 (CONNECT_DATA=(SID=$sid)))";

 $conn->Connect($cstr, 'scott', 'tiger');

f. ADOdb dsn:

 $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);

You can also set the charSet for Oracle 9.2 and later, supported since PHP 4.3.2, ADOdb 4.54:

 $conn->charSet = 'we8iso8859p1';

 $conn->Connect(...);

 

 # or

 $dsn = 'oci8://user:pwd@tnsname/?charset=WE8MSWIN1252';

 $db = ADONewConnection($dsn);

DSN-less ODBC ( Access, MSSQL and DB2 examples)

ODBC DSN's can be created in the ODBC control panel, or you can use a DSN-less connection.To use DSN-less connections with ODBC you need PHP 4.3 or later.

For Microsoft Access:

 $db =& ADONewConnection('access');

 $dsn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=d:\\northwind.mdb;Uid=Admin;Pwd=;";
 $db->Connect($dsn);

For Microsoft SQL Server:

 $db =& ADONewConnection('odbc_mssql');

 $dsn = "Driver={SQL Server};Server=localhost;Database=northwind;";

 $db->Connect($dsn,'userid','password');

or if you prefer to use the mssql extension (which is limited to mssql 6.5 functionality):

 $db =& ADONewConnection('mssql');

 $db->Execute('localhost', 'userid', 'password', 'northwind');

For DB2:

 
 $dbms = 'db2'; # or 'odbc_db2' if db2 extension not available
 $db =& ADONewConnection($dbms);
 $dsn = "driver={IBM db2 odbc DRIVER};Database=sample;hostname=localhost;port=50000;protocol=TCPIP;".
 "uid=root; pwd=secret";

 $db->Connect($dsn);

DSN-less Connections with ADO
If you are using versions of PHP earlier than PHP 4.3.0, DSN-less connections only work with Microsoft's ADO, which is Microsoft's COM based API. An example using the ADOdb library and Microsoft's ADO:

<?php

 include('adodb.inc.php'); 

 $db = &ADONewConnection("ado_mssql");

 print "<h1>Connecting DSN-less $db->databaseType...</h1>";

 

 $myDSN="PROVIDER=MSDASQL;DRIVER={SQL Server};"

 . "SERVER=flipper;DATABASE=ai;UID=sa;PWD=;" ;
 $db->Connect($myDSN);
 
 $rs = $db->Execute("select * from table");
 $arr = $rs->GetArray();
 print_r($arr);
?>

High Speed ADOdb - tuning tips

ADOdb is a big class library, yet it consistently beats all other PHP class libraries in performance. This is because it is designed in a layered fashion, like an onion, with the fastest functions in the innermost layer. Stick to the following functions for best performance:

Innermost Layer

Connect, PConnect, NConnect
Execute, CacheExecute
SelectLimit, CacheSelectLimit
MoveNext, Close
qstr, Affected_Rows, Insert_ID

The fastest way to access the field data is by accessing the array $recordset->fields directly. Also set the global variables $ADODB_FETCH_MODE = ADODB_FETCH_NUM, and (for oci8, ibase/firebird and odbc) $ADODB_COUNTRECS = false before you connect to your database.

Consider using bind parameters if your database supports it, as it improves query plan reuse. Use ADOdb's performance tuning system to identify bottlenecks quickly. At the time of writing (Dec 2003), this means oci8 and odbc drivers.

Lastly make sure you have a PHP accelerator cache installed such as APC, Turck MMCache, Zend Accelerator or ionCube.

Some examples:

Fastest data retrieval using PHP	Fastest data retrieval using ADOdb extension
$rs =& $rs->Execute($sql); while (!$rs->EOF) { var_dump($rs->fields); $rs->MoveNext(); }	$rs =& $rs->Execute($sql); $array = adodb_getall($rs); var_dump($array);

Advanced Tips

If you have the ADOdb C extension installed, you can replace your calls to $rs->MoveNext() with adodb_movenext($rs). This doubles the speed of this operation. For retrieving entire recordsets at once, use GetArray(), which uses the high speed extension function adodb_getall($rs) internally.

Execute() is the default way to run queries. You can use the low-level functions _Execute() and _query() to reduce query overhead. Both these functions share the same parameters as Execute().

If you do not have any bind parameters or your database supports binding (without emulation), then you can call _Execute() directly. Calling this function bypasses bind emulation. Debugging is still supported in _Execute().

If you do not require debugging facilities nor emulated binding, and do not require a recordset to be returned, then you can call _query. This is great for inserts, updates and deletes. Calling this function bypasses emulated binding, debugging, and recordset handling. Either the resultid, true or false are returned by _query().

For Informix, you can disable scrollable cursors with $db->cursorType = 0.

Hacking ADOdb Safely

You might want to modify ADOdb for your own purposes. Luckily you can still maintain backward compatibility by sub-classing ADOdb and using the $ADODB_NEWCONNECTION variable. $ADODB_NEWCONNECTION allows you to override the behaviour of ADONewConnection(). ADOConnection() checks for this variable and will call the function-name stored in this variable if it is defined.

In the following example, new functionality for the connection object is placed in the hack_mysql and hack_postgres7 classes. The recordset class naming convention can be controlled using $rsPrefix. Here we set it to 'hack_rs_', which will make ADOdb use hack_rs_mysql and hack_rs_postgres7 as the recordset classes.

class hack_mysql extends adodb_mysql {

var $rsPrefix = 'hack_rs_';

 /* Your mods here */

}



class hack_rs_mysql extends ADORecordSet_mysql {

  /* Your mods here */

}



class hack_postgres7 extends adodb_postgres7 {

var $rsPrefix = 'hack_rs_';

 /* Your mods here */

}



class hack_rs_postgres7 extends ADORecordSet_postgres7 {

 /* Your mods here */

}



$ADODB_NEWCONNECTION = 'hack_factory';



function& hack_factory($driver)

{

 if ($driver !== 'mysql' && $driver !== 'postgres7') return false;

 

 $driver = 'hack_'.$driver;

 $obj = new $driver();

 return $obj;

}



include_once('adodb.inc.php');

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.

PHP5 Features

ADOdb 4.02 or later will transparently determine which version of PHP you are using. If PHP5 is detected, the following features become available:

• PDO: PDO drivers are available. See the connection examples. Currently PDO drivers are not as powerful as native drivers, and should be treated as experimental.
• Foreach iterators: This is a very natural way of going through a recordset:

 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;

 $rs = $db->Execute($sql);

 foreach($rs as $k => $row) {

 echo "r1=".$row[0]." r2=".$row[1]."<br>";

 }

• Exceptions: Just include adodb-exceptions.inc.php and you can now catch exceptions on errors as they occur.

 include("../adodb-exceptions.inc.php"); 

 include("../adodb.inc.php");  

 try { 

 $db = NewADOConnection("oci8"); 

 $db->Connect('','scott','bad-password'); 

 } catch (exception $e) { 

 var_dump($e); 

 adodb_backtrace($e->gettrace());

 } 

Note that reaching EOF is not considered an error nor an exception.

Databases Supported

The name below is the value you pass to NewADOConnection($name) to create a connection object for that database.

Name	Tested	Database	RecordCount() usable	Prerequisites	Operating Systems
access	B	Microsoft Access/Jet. You need to create an ODBC DSN.	Y/N	ODBC	Windows only
ado	B	Generic ADO, not tuned for specific databases. Allows DSN-less connections. For best performance, use an OLEDB provider. This is the base class for all ado drivers. You can set $db->codePage before connecting.	? depends on database	ADO or OLEDB provider	Windows only
ado_access	B	Microsoft Access/Jet using ADO. Allows DSN-less connections. For best performance, use an OLEDB provider.	Y/N	ADO or OLEDB provider	Windows only
ado_mssql	B	Microsoft SQL Server using ADO. Allows DSN-less connections. For best performance, use an OLEDB provider.	Y/N	ADO or OLEDB provider	Windows only
db2	C	Uses PHP's db2-specific extension for better performance.	Y/N	DB2 CLI/ODBC interface	Unix and Windows. Requires IBM DB2 Universal Database client.
odbc_db2	C	Connects to DB2 using generic ODBC extension.	Y/N	DB2 CLI/ODBC interface	Unix and Windows. Unix install hints. I have had reports that the $host and $database params have to be reversed in Connect() when using the CLI interface.
vfp	A	Microsoft Visual FoxPro. You need to create an ODBC DSN.	Y/N	ODBC	Windows only
fbsql	C	FrontBase.	Y	?	Unix and Windows
ibase	B	Interbase 6 or earlier. Some users report you might need to use this $db->PConnect('localhost:c:/ibase/employee.gdb', "sysdba", "masterkey") to connect. Lacks Affected_Rows currently. You can set $db->role, $db->dialect, $db->buffers and $db->charSet before connecting.	Y/N	Interbase client	Unix and Windows
firebird	C	Firebird version of interbase.	Y/N	Interbase client	Unix and Windows
borland_ibase	C	Borland version of Interbase 6.5 or later. Very sad that the forks differ.	Y/N	Interbase client	Unix and Windows
informix	C	Generic informix driver. Use this if you are using Informix 7.3 or later.	Y/N	Informix client	Unix and Windows
informix72	C	Informix databases before Informix 7.3 that do no support SELECT FIRST.	Y/N	Informix client	Unix and Windows
ldap	C	LDAP driver. See this example for usage information.		LDAP extension	?
mssql	A	Microsoft SQL Server 7 and later. Works with Microsoft SQL Server 2000 also. Note that date formating is problematic with this driver. For example, the PHP mssql extension does not return the seconds for datetime!	Y/N	Mssql client	Unix and Windows. Unix install howto and another one.
mssqlpo	A	Portable mssql driver. Identical to above mssql driver, except that '||', the concatenation operator, is converted to '+'. Useful for porting scripts from most other sql variants that use ||.	Y/N	Mssql client	Unix and Windows. Unix install howto.
mssqlnative	C	Native mssql driver from M'soft.	?	?	Windows. Tq Garrett Serack of M'soft.
mysql	A	MySQL without transaction support. You can also set $db->clientFlags before connecting.	Y	MySQL client	Unix and Windows
mysqlt or maxsql	A	MySQL with transaction support. We recommend using || as the concat operator for best portability. This can be done by running MySQL using: mysqld --ansi or mysqld --sql-mode=PIPES_AS_CONCAT	Y/N	MySQL client	Unix and Windows
oci8	A	Oracle 8/9. Has more functionality than oracle driver (eg. Affected_Rows). You might have to putenv('ORACLE_HOME=...') before Connect/PConnect. There are 2 ways of connecting - with server IP and service name: PConnect('serverip:1521','scott','tiger','service') or using an entry in TNSNAMES.ORA or ONAMES or HOSTNAMES: PConnect(false, 'scott', 'tiger', $oraname). Since 2.31, we support Oracle REF cursor variables directly (see ExecuteCursor).	Y/N	Oracle client	Unix and Windows
oci805	C	Supports reduced Oracle functionality for Oracle 8.0.5. SelectLimit is not as efficient as in the oci8 or oci8po drivers.	Y/N	Oracle client	Unix and Windows
oci8po	A	Oracle 8/9 portable driver. This is nearly identical with the oci8 driver except (a) bind variables in Prepare() use the ? convention, instead of :bindvar, (b) field names use the more common PHP convention of lowercase names. Use this driver if porting from other databases is important. Otherwise the oci8 driver offers better performance.	Y/N	Oracle client	Unix and Windows
odbc	A	Generic ODBC, not tuned for specific databases. To connect, use PConnect('DSN','user','pwd'). This is the base class for all odbc derived drivers.	? depends on database	ODBC	Unix and Windows. Unix hints.
odbc_mssql	C	Uses ODBC to connect to MSSQL	Y/N	ODBC	Unix and Windows.
odbc_oracle	C	Uses ODBC to connect to Oracle	Y/N	ODBC	Unix and Windows.
odbtp	C	Generic odbtp driver. Odbtp is a software for accessing Windows ODBC data sources from other operating systems.	Y/N	odbtp	Unix and Windows
odbtp_unicode	C	Odtbp with unicode support	Y/N	odbtp	Unix and Windows
oracle	C	Implements old Oracle 7 client API. Use oci8 driver if possible for better performance.	Y/N	Oracle client	Unix and Windows
netezza	C	Netezza driver. Netezza is based on postgres code-base.	Y	?	?
pdo	C	Generic PDO driver for PHP5.	Y	PDO extension and database specific drivers	Unix and Windows.
postgres	A	Generic PostgreSQL driver. Currently identical to postgres7 driver.	Y	PostgreSQL client	Unix and Windows.
postgres64	A	For PostgreSQL 6.4 and earlier which does not support LIMIT internally.	Y	PostgreSQL client	Unix and Windows.
postgres7	A	PostgreSQL which supports LIMIT and other version 7 functionality.	Y	PostgreSQL client	Unix and Windows.
postgres8	A	PostgreSQL which supports version 8 functionality.	Y	PostgreSQL client	Unix and Windows.
sapdb	C	SAP DB. Should work reliably as based on ODBC driver.	Y/N	SAP ODBC client	?
sqlanywhere	C	Sybase SQL Anywhere. Should work reliably as based on ODBC driver.	Y/N	SQL Anywhere ODBC client	?
sqlite	B	SQLite.	Y	-	Unix and Windows.
sqlitepo	B	Portable SQLite driver. This is because assoc mode does not work like other drivers in sqlite. Namely, when selecting (joining) multiple tables, the table names are included in the assoc keys in the "sqlite" driver. In "sqlitepo" driver, the table names are stripped from the returned column names. When this results in a conflict, the first field get preference.	Y	-	Unix and Windows.
sybase	C	Sybase.	Y/N	Sybase client	Unix and Windows.
sybase_ase	C	Sybase ASE.	Y/N	Sybase client	Unix and Windows.

The "Tested" column indicates how extensively the code has been tested and used.
A = well tested and used by many people
B = tested and usable, but some features might not be implemented
C = user contributed or experimental driver. Might not fully support all of the latest features of ADOdb.

The column "RecordCount() usable" indicates whether RecordCount() return the number of rows, or returns -1 when a SELECT statement is executed. If this column displays Y/N then the RecordCount() is emulated when the global variable $ADODB_COUNTRECS=true (this is the default). Note that for large recordsets, it might be better to disable RecordCount() emulation because substantial amounts of memory are required to cache the recordset for counting. Also there is a speed penalty of 40-50% if emulation is required. This is emulated in most databases except for PostgreSQL and MySQL. This variable is checked every time a query is executed, so you can selectively choose which recordsets to count.

---

Tutorials

Example 1: Select Statement

Task: Connect to the Access Northwind DSN, display the first 2 columns of each row.

In this example, we create a ADOConnection object, which represents the connection to the database. The connection is initiated with PConnect, which is a persistent connection. Whenever we want to query the database, we call the ADOConnection.Execute() function. This returns an ADORecordSet object which is actually a cursor that holds the current row in the array fields[]. We use MoveNext() to move from row to row.

NB: A useful function that is not used in this example is SelectLimit, which allows us to limit the number of rows shown.

<?

include('adodb.inc.php');  # load code common to ADOdb

$conn = &ADONewConnection('access'); # create a connection

$conn->PConnect('northwind'); # connect to MS-Access, northwind DSN

$recordSet = &$conn->Execute('select * from products');

if (!$recordSet) 

 print $conn->ErrorMsg();

else

while (!$recordSet->EOF) {

 print $recordSet->fields[0].' '.$recordSet->fields[1].'<BR>';

 $recordSet->MoveNext();

}
 
$recordSet->Close(); # optional

$conn->Close(); # optional




?>

The $recordSet returned stores the current row in the $recordSet->fields array, indexed by column number (starting from zero). We use the MoveNext() function to move to the next row. The EOF property is set to true when end-of-file is reached. If an error occurs in Execute(), we return false instead of a recordset.

The $recordSet->fields[] array is generated by the PHP database extension. Some database extensions only index by number and do not index the array by field name. To force indexing by name - that is associative arrays - use the SetFetchMode function. Each recordset saves and uses whatever fetch mode was set when the recordset was created in Execute() or SelectLimit().

 $db->SetFetchMode(ADODB_FETCH_NUM);

 $rs1 = $db->Execute('select * from table');

 $db->SetFetchMode(ADODB_FETCH_ASSOC);

 $rs2 = $db->Execute('select * from table');

 print_r($rs1->fields); # shows array([0]=>'v0',[1] =>'v1')
 print_r($rs2->fields); # shows array(['col1']=>'v0',['col2'] =>'v1')

To get the number of rows in the select statement, you can use $recordSet->RecordCount(). Note that it can return -1 if the number of rows returned cannot be determined.

Example 2: Advanced Select with Field Objects

Select a table, display the first two columns. If the second column is a date or timestamp, reformat the date to US format.

<?

include('adodb.inc.php');  # load code common to ADOdb

$conn = &ADONewConnection('access'); # create a connection

$conn->PConnect('northwind'); # connect to MS-Access, northwind dsn

$recordSet = &$conn->Execute('select CustomerID,OrderDate from Orders');

if (!$recordSet) 

 print $conn->ErrorMsg();

else

while (!$recordSet->EOF) {

 $fld = $recordSet->FetchField(1);
 $type = $recordSet->MetaType($fld->type);



 if ( $type == 'D' || $type == 'T') 

 print $recordSet->fields[0].' '.

 $recordSet->UserDate($recordSet->fields[1],'m/d/Y').'<BR>';

 else 
 print $recordSet->fields[0].' '.$recordSet->fields[1].'<BR>';



 $recordSet->MoveNext();

}
$recordSet->Close(); # optional

$conn->Close(); # optional




?>

In this example, we check the field type of the second column using FetchField(). This returns an object with at least 3 fields.

• name: name of column
• type: native field type of column
• max_length: maximum length of field. Some databases such as MySQL do not return the maximum length of the field correctly. In these cases max_length will be set to -1.

We then use MetaType() to translate the native type to a generic type. Currently the following generic types are defined:

• C: character fields that should be shown in a <input type="text"> tag.
• X: TeXt, large text fields that should be shown in a <textarea>
• B: Blobs, or Binary Large Objects. Typically images.
• D: Date field
• T: Timestamp field
• L: Logical field (boolean or bit-field)
• I:  Integer field
• N: Numeric field. Includes autoincrement, numeric, floating point, real and integer.
• R: Serial field. Includes serial, autoincrement integers. This works for selected databases.

If the metatype is of type date or timestamp, then we print it using the user defined date format with UserDate(), which converts the PHP SQL date string format to a user defined one. Another use for MetaType() is data validation before doing an SQL insert or update.

Example 3: Inserting

Insert a row to the Orders table containing dates and strings that need to be quoted before they can be accepted by the database, eg: the single-quote in the word John's.

<?

include('adodb.inc.php');  # load code common to ADOdb

$conn = &ADONewConnection('access'); # create a connection



$conn->PConnect('northwind'); # connect to MS-Access, northwind dsn

$shipto = $conn->qstr("John's Old Shoppe");



$sql = "insert into orders (customerID,EmployeeID,OrderDate,ShipName) ";

$sql .= "values ('ANATR',2,".