ADODB Library for PHP4

V1.61 22 Nov 2001 (c) 2000-2001 John Lim (jlim@natsoft.com.my)

This software is dual licensed using BSD-Style and LGPL. Where there is any discrepancy, the BSD-Style license will take precedence. This means you can use it in proprietary and commercial products.

Introduction
Unique Features
Change Log
How People are using ADODB
Feature Requests and Bug Reports
Installation
Initializing Code ADONewConnection NewADOConnection
Supported Databases
Tutorial
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
Using Custom Error Handlers and PEAR_Error
Caching

Reference
ADOConnection: $ADODB_FETCH_MODE Connect PConnect Execute CacheExecute SelectLimit CacheSelectLimit CacheFlush Close Concat DBDate DBTimeStamp qstr BeginTrans CommitTrans RollbackTrans Affected_Rows Insert_ID ErrorMsg ErrorNo MetaDatabases MetaTables BlankRecordSet MetaColumns GenID GetUpdateSQL GetInsertSQL UpdateBlob PageExecute CachePageExecute
ADORecordSet: GetAssoc GetArray GetRows GetMenu GetMenu2 UserDate UserTimeStamp UnixDate UnixTimeStamp Move MoveNext MoveFirst MoveLast GetRowAssoc Fields FetchField CurrentRow AbsolutePosition FieldCount RecordCount FetchObject FetchNextObject Close MetaType AtFirstPage AtLastPage AbsolutePage
rs2html  example
Differences between ADODB and ADO
Database Driver Guide

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 databases (encapsulate the differences) so we can easily switch databases.

We currently support MySQL, Interbase, Oracle, Microsoft SQL Server, Sybase, PostgreSQL, FrontBase, Foxpro, Access, ADO and ODBC. We have had successful reports of connecting to Progress and DB2 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.

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.
• PHP4 session support. See adodb-session.php.

Change Log

1.61

Added PO_RecordCount() and PO_Insert_ID(). PO stands for portable. Pablo Roca [pabloroca@mvps.org]

M'soft ADO we now correctly close recordset in _close().

MSSQL now supports GenID(). It generates a 16-byte GUID.

Changed ereg_replace to preg_replace in SelectLimit. This is a fix for mssql. Ereg doesn't support \t or \n!

Added $recordset->connection. This is the ADOConnection object for the recordset. Works with cached and normal recordsets. Surprisingly, this had no affect on performance!

1.54 15 Nov 2001

More speedups of SelectLimit() for DB2, Oci8, access, vfp, mssql.

1.53 7 Nov 2001

Tuned GetRowAssoc(false) in postgresql and mysql.

Stephen Van Dyke contributed adodb icon, accepted with some minor mods.

Enabled Affected_Rows() for postgresql

Speedup for Concat() using implode() - Benjamin Curtis ben_curtis@yahoo.com

Fixed some more bugs in PageExecute() to prevent infinite loops

1.52 5 Nov 2001

Added fixes for parsing [ and ] in GetUpdateSQL().

1.51 5 Nov 2001

Oci8 SelectLimit() speedup by using OCIFetch().

Oci8 was mistakenly reporting errors when $db->debug = true.

If a connection failed with ODBC, it was not correctly reported - fixed.

_connectionID was inited to -1, changed to false.

Added $rs->FetchRow(), to simplify API, ala PEAR DB

Added PEAR DB compat mode, which is still faster than PEAR! See adodb-pear.inc.php.

Removed postgres pconnect debugging statement.

1.50 31 Oct 2001

ADODBConnection renamed to ADOConnection, and ADODBFieldObject to ADOFieldObject.

PageExecute() now checks for empty $rs correctly, and the errors in the docs on this subject have been fixed.

odbc_error() does not return 6 digit error correctly at times. Implemented workaround.

Added ADORecordSet_empty class. This will speedup INSERTS/DELETES/UPDATES because the return object created is much smaller.

Added Prepare() to odbc, and oci8 (but doesn't work properly for oci8 still).

Made pgsql a synonym for postgre7, and changed SELECT LIMIT to use OFFSET for compat with postgres 7.2.

Revised adodb-cryptsession.php thanks to Ari.

Set resources to false on _close, to force freeing of resources.

Added adodb-errorhandler.inc.php, adodb-errorpear.inc.php and raiseErrorFn on Freek's urging.

GetRowAssoc($toUpper=true): $toUpper added as default.

Errors when connecting to a database were not captured formerly. Now we do it correctly.

1.40 19 September 2001

PageExecute() to implement page scrolling added. Code and idea by Iván Oliva.

Some minor postgresql fixes.

Added sequence support using GenID() for postgresql, oci8, mysql, interbase.

Added UpdateBlob support for interbase (untested).

Added encrypted sessions (see adodb-cryptsession.php). By Ari Kuorikoski

1.31 21 August 2001

Many bug fixes thanks to "GaM3R (Cameron)" . Some session changes due to Gam3r.

Fixed qstr() to quote \ also.

rs2html() now pretty printed.

Jonathan Younger jyounger@unilab.com contributed the great idea GetUpdateSQL() and GetInsertSQL() which generates SQL to update and insert into a table from a recordset. Modify the recordset fields array, then can this function to generate the SQL (the SQL is not executed).

"Nicola Fankhauser" found some bugs in date handling for mssql.

Added minimal Oracle support for LOBs. Still under development.

PostgreSQL properly closes recordsets now. Reported by several people.

Added UpdateBlob() for Oracle. A hack to make it easier to save blobs.

Oracle timestamps did not display properly. Fixed.

1.20 6 June 2001

Now Oracle can connect using tnsnames.ora or server and service name

Extensive Oci8 speed optimizations. Oci8 code revised to support variable binding, and /*+ FIRST_ROWS */ hint.

Worked around some 4.0.6 bugs in odbc_fetch_into().

Paolo S. Asioli paolo.asioli@libero.it suggested GetRowAssoc().

Escape quotes for oracle wrongly set to \'. Now '' is used.

Variable binding now works in ODBC also.

Jumped to version 1.20 because I don't like 13 :-)

1.12 6 June 2001

Changed $ADODB_DIR to ADODB_DIR constant to plug a security loophole.

Changed _close() to close persistent connections also. Prevents connection leaks.

Major revision of oracle and oci8 drivers. Added OCI_RETURN_NULLS and OCI_RETURN_LOBS to OCIFetchInto(). BLOB, CLOB and VARCHAR2 recognition in MetaType() improved. MetaColumns() returns columns in correct sort order.

Interbase timestamp input format was wrong. Fixed.

1.11 20 May 2001

Improved file locking for Windows.

Probabilistic flushing of cache to avoid avalanche updates when cache timeouts.

Cached recordset timestamp not saved in some scenarios. Fixed.

1.10 19 May 2001

Added caching. CacheExecute() and CacheSelectLimit().

Added csv driver. See http:#php.weblogs.com/adodb_csv.

Fixed SelectLimit(), SELECT TOP not working under certain circumstances.

Added better Frontbase support of MetaTypes() by Frank M. Kromann.

1.01 24 April 2001

Fixed SelectLimit bug. \ not quoted properly.

SelectLimit: SELECT TOP -1 * FROM TABLE not support by Microsoft. Fixed.

GetMenu improved by glen.davies@cce.ac.nz to support multiple hilited items

FetchNextObject() did not work with only 1 record returned. Fixed bug reported by $tim@orotech.net

Fixed mysql field max_length problem. Fix suggested by Jim Nicholson (jnich@att.com)

1.00 16 April 2001

Given some brilliant suggestions on how to simplify ADODB by akul. You no longer need to setup $ADODB_DIR yourself, and ADOLoadCode() is automatically called by ADONewConnection(), simplifying the startup code.

FetchNextObject() added. Suggested by Jakub Marecek. This makes FetchObject() obsolete, as this is more flexible and powerful.

Misc fixes to SelectLimit() to support Access (top must follow distinct) and Fields() in the array recordset. From Reinhard Balling.

0.96 27 Mar 2001

ADOConnection Close() did not return a value correctly. Thanks to akul@otamedia.com.

When the horrible magic_quotes is enabled, back-slash (\) is changed to double-backslash (\\). This doesn't make sense for Microsoft/Sybase databases. We fix this in qstr().

Fixed Sybase date problem in UnixDate() thanks to Toni Tunkkari. Also fixed MSSQL problem in UnixDate() - thanks to milhouse31@hotmail.com.

MoveNext() moved to leaf classes for speed in MySQL/PostgreSQL. 10-15% speedup.

Added null handling in bindInputArray in Execute() -- Ron Baldwin suggestion.

Fixed some option tags. Thanks to john@jrmstudios.com.

0.95 13 Mar 2001

Added postgres7 database driver which supports LIMIT and other version 7 stuff in the future.

Added SelectLimit to ADOConnection to simulate PostgreSQL's "select * from table limit 10 offset 3". Added helper function GetArrayLimit() to ADORecordSet.

Fixed mysql metacolumns bug. Thanks to Freek Dijkstra (phpeverywhere@macfreek.com).

Also many PostgreSQL changes by Freek. He almost rewrote the whole PostgreSQL driver!

Added fix to input parameters in Execute for non-strings by Ron Baldwin.

Added new metatype, X for TeXt. Formerly, metatype B for Blob also included text fields. Now 'B' is for binary/image data. 'X' for textual data.

Fixed $this->GetArray() in GetRows().

Oracle and OCI8: 1st parameter is always blank -- now warns if it is filled.

Now hasLimit and hasTop added to indicate whether SELECT * FROM TABLE LIMIT 10 or SELECT TOP 10 * FROM TABLE are supported.

0.94 04 Feb 2001

Added ADORecordSet::GetRows() for compatibility with Microsoft ADO. Synonym for GetArray().

Added new metatype 'R' to represent autoincrement numbers.

Added ADORecordSet.FetchObject() to return a row as an object.

Finally got a Linux box to test PostgreSql. Many fixes.

Fixed copyright misspellings in 0.93.

Fixed mssql MetaColumns type bug.

Worked around odbc bug in PHP4 for sessions.

Fixed many documentation bugs (affected_rows, metadatabases, qstr).

Fixed MySQL timestamp format (removed comma).

Interbase driver did not call ibase_pconnect(). Fixed.

0.93 18 Jan 2001

Fixed GetMenu bug.

Simplified Interbase commit and rollback.

Default behaviour on closing a connection is now to rollback all active transactions.

Added field object handling for array recordset for future XML compatibility.

Added arr2html() to convert array to html table.

0.92 2 Jan 2001

Interbase Commit and Rollback should be working again.

Changed initialisation of ADORecordSet. This is internal and should not affect users. We are doing this to support cached recordsets in the future.

Implemented ADORecordSet_array class. This allows you to simulate a database recordset with an array.

Added UnixDate() and UnixTimeStamp() to ADORecordSet.

0.91 21 Dec 2000

Fixed ODBC so ErrorMsg() is working.

Worked around ADO unrecognised null (0x1) value problem in COM.

Added Sybase support for FetchField() type

Removed debugging code and unneeded html from various files

Changed to javadoc style comments to adodb.inc.php.

Added maxsql as synonym for mysqlt

Now ODBC downloads first 8K of blob by default

0.90 15 Nov 2000

Lots of testing of Microsoft ADO. Should be more stable now.

Added $ADODB_COUNTREC. Set to false for high speed selects.

Added Sybase support. Contributed by Toni Tunkkari (toni.tunkkari@finebyte.com). Bug in Sybase API: GetFields is unable to determine date types.

Changed behaviour of RecordSet.GetMenu() to support size parameter (listbox) properly.

Added emptyDate and emptyTimeStamp to RecordSet class that defines how to represent empty dates.

Added MetaColumns($table) that returns an array of ADOFieldObject's listing the columns of a table.

Added transaction support for PostgresSQL -- thanks to "Eric G. Werk" egw@netguide.dk.

Added adodb-session.php for session support.

0.80 30 Nov 2000

Added support for charSet for interbase. Implemented MetaTables for most databases. PostgreSQL more extensively tested.

0.71 22 Nov 2000

Switched from using require_once to include/include_once for backward compatability with PHP 4.02 and earlier.

0.70 15 Nov 2000

Calls by reference have been removed (call_time_pass_reference=Off) to ensure compatibility with future versions of PHP, except in Oracle 7 driver due to a bug in php_oracle.dll.

PostgreSQL database driver contributed by Alberto Cerezal (acerezalp@dbnet.es).

Oci8 driver for Oracle 8 contributed by George Fourlanos (fou@infomap.gr).

Added mysqlt database driver to support MySQL 3.23 which has transaction support.

Oracle default date format (DD-MON-YY) did not match ADODB default date format (which is YYYY-MM-DD). Use ALTER SESSION to force the default date.

Error message checking is now included in test suite.

MoveNext() did not check EOF properly -- fixed.

0.60 Nov 8 2000

Fixed some constructor bugs in ODBC and ADO. Added ErrorNo function to ADOConnection class.

0.51 Oct 18 2000

Fixed some interbase bugs.

0.50 Oct 16 2000

Interbase commit/rollback changed to be compatible with PHP 4.03.

CommitTrans( ) will now return true if transactions not supported.

Conversely RollbackTrans( ) will return false if transactions not supported.

0.46 Oct 12

0.40 Sept 26

Many bug fixes

Now Code for BeginTrans, CommitTrans and RollbackTrans is working. So is the Affected_Rows and Insert_ID. Added above functions to test.php.

ADO type handling was busted in 0.30. Fixed.

Generalised Move( ) so it works will all databases, including ODBC.

0.30 Sept 18

Renamed ADOLoadDB to ADOLoadCode. This is clearer.

Added BeginTrans, CommitTrans and RollbackTrans functions.

Added Affected_Rows() and Insert_ID(), _affectedrows() and _insertID(), ListTables(), ListDatabases(), ListColumns().

Need to add New_ID() and hasInsertID and hasAffectedRows, autoCommit

0.20 Sept 12

Added support for Microsoft's ADO.

Added new field to ADORecordSet -- canSeek

Added new parameter to _fetch($ignore_fields = false). Setting to true will not update fields array for faster performance.

Added new field to ADORecordSet/ADOConnection -- dataProvider to indicate whether a class is derived from odbc or ado.

Changed class ODBCFieldObject to ADOFieldObject -- not documented currently.

Added benchmark.php and testdatabases.inc.php to the test suite.

Added to ADORecordSet FastForward( ) for future high speed scrolling. Not documented.

Realised that ADO's Move( ) uses relative positioning. ADODB uses absolute.

0.10 Sept 9 2000

First release

How People are using ADODB

• 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.
• CCC is a web based inventory /contact management / POS / job tracking / job billing system specially designed for computer service shops, but should be useful to anyone that needs accountability of any of any of the things listed. By Ryan Fox.

Feature Requests and Bug Reports

Feature requests and bug reports can be emailed to jlim@natsoft.com.my or posted to http:#php.weblogs.com/discuss/msgReader$96.

Installation Guide

Make sure you are running PHP4.01pl2 or later (it uses require_once and include_once). Unpack all the files into a directory accessible by your webserver.

To test you will need a database. Open testdatabases.inc.php and modify the connection parameters to suit your database. This script will create a new table in the database and perform various tests.

That's it!

Code Initialization

When running ADODB, at least two files are loaded. First is adodb.inc.php, which contains all functions used by all database classes. The code specific to a particular database is in the 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( ) function. ADONewConnection accepts one optional parameter, the <database-name-here>. If no parameter is specified, it will create a new ADOConnection for the last database loaded by ADOLoadCode( ). NewADOConnection( ) is the same function.

At this point, you are not connected to the database. You will use $conn->Connect() or $conn->PConnect() to perform the actual connection.

See the examples below in the Tutorial.

Databases Supported

RecordCount() usable indicates whether RecordCount() returns -1 instead of the number of rows when a SELECT statement is executed. For highest performance, disable RecordCount() by setting the global variable $ADODB_COUNTRECS=false. For the reason why, see the notes to http:#php.net/manual/function.sybase-num-rows.php

Other database drivers used for testing ADODB.

Tutorial

Example 1: Select Statement

Task: Connect to the Access Northwind database, 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 db
$recordSet = &$conn->Execute('select * from products');

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.

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 $ADODB_FETCH_MODE global variable.

	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
	$rs1 = $db->Execute('select * from table');
	$ADODB_FETCH_MODE = 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 db
$recordSet = &$conn->Execute('select CustomerID,OrderDate from Orders');

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)
• 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 db

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

$sql = "insert into orders (customerID,EmployeeID,OrderDate,ShipName) ";
$sql .= "values ('ANATR',2,".$conn->DBDate(time()).",$shipto)";

if ($conn->Execute($sql) === false) {
	print 'error inserting: '.$conn->ErrorMsg().'<BR>';
}
?>

In this example, we see the advanced date and quote handling facilities of ADODB. The unix timestamp (which is a long integer) is appropriately formated for Access with DBDate(), and the right escape character is used for quoting the John's Old Shoppe, which is John''s Old Shoppe and not PHP's default John\'s Old Shoppe with qstr().

Observe the error-handling of the Execute statement. False is returned by Execute() if an error occured. The error message for the last error that occurred is displayed in ErrorMsg(). Note: php_track_errors might have to be enabled for error messages to be saved.

Example 4: Debugging

<?

include('adodb.inc.php');       # load code common to ADODB
$conn = &ADONewConnection('access');    # create a connection
$conn->PConnect('northwind');   # connect to MS-Access, northwind db
$shipto = $conn->qstr("John's Old Shoppe");
$sql = "insert into orders (customerID,EmployeeID,OrderDate,ShipName) ";
$sql .= "values ('ANATR',2,".$conn->FormatDate(time()).",$shipto)";
$conn->debug = true;
if ($conn->Execute($sql) === false) print 'error inserting';

?>

In the above example, we have turned on debugging by setting debug = true. This will display the SQL statement before execution, and also show any error messages. There is no need to call ErrorMsg() in this case. For displaying the recordset, see the rs2html() example.

Also see the section on Custom Error Handlers.

Example 5: MySQL and Menus

Connect to MySQL database agora, and generate a <select> menu from an SQL statement where the <option> captions are in the 1st column, and the value to send back to the server is in the 2nd column.

<?
include('adodb.inc.php'); # load code common to ADODB
$conn = &ADONewConnection('mysql');  # create a connection
$conn->PConnect('localhost','userid','','agora');# connect to MySQL, agora db
$sql = 'select CustomerName, CustomerID from customers';
$rs = $conn->Execute($sql);
print $rs->GetMenu('GetCust','Mary Rosli');
?>

Here we define a menu named GetCust, with the menu option 'Mary Rosli' selected. See GetMenu(). We also have functions that return the recordset as an array: GetArray(), and as an associative array with the key being the first column: GetAssoc().

Aslo new to ADODB 1.50 or later is the global variable $ADODB_FETCH_MODE. This allows you to define whether you want associative or numeric indexing for your arrays.

Example 6: Connecting to 2 Databases At Once

<?
include('adodb.inc.php');     # load code common to ADODB
$conn1 = &ADONewConnection('mysql');  # create a mysql connection
$conn2 = &ADONewConnection('oracle');  # create a oracle connection

$conn1->PConnect($server, $userid, $password, $database);
$conn2->PConnect(false, $ora_userid, $ora_pwd, $tnsname);

$conn1->Execute('insert ...');
$conn2->Execute('update ...');
?>

Example 7: Generating Update and Insert SQL

We show how the functions can be used when accessing a table with the following fields: (ID, FirstName, LastName, Created).

Before these functions can be called, you need to initialize the recordset by performing a select on the table. Idea and code by Jonathan Younger jyounger@unilab.com.

<?
#==============================================
# SAMPLE GetUpdateSQL() and GetInsertSQL() code
#==============================================
include('adodb.inc.php');
include('tohtml.inc.php');

#==========================
# This code tests an insert

$sql = "SELECT * FROM ADOXYZ WHERE id = -1"; 
# Select an empty record from the database 

$conn = &ADONewConnection("mysql");  # create a connection
$conn->debug=1;
$conn->PConnect("localhost", "admin", "", "test"); # connect to MySQL, testdb
$rs = $conn->Execute($sql); # Execute the query and get the empty recordset

$record = array(); # Initialize an array to hold the record data to insert

# Set the values for the fields in the record
$record["firstname"] = "Bob";
$record["lastname"] = "Smith";
$record["created"] = time();

# Pass the empty recordset and the array containing the data to insert
# into the GetInsertSQL function. The function will process the data and return
# a fully formatted insert sql statement.
$insertSQL = $conn->GetInsertSQL($rs, $record);

$conn->Execute($insertSQL); # Insert the record into the database

#==========================
# This code tests an update

$sql = "SELECT * FROM ADOXYZ WHERE id = 1"; 
# Select a record to update 

$rs = $conn->Execute($sql); # Execute the query and get the existing record to update

$record = array(); # Initialize an array to hold the record data to update

# Set the values for the fields in the record
$record["firstname"] = "Caroline";
$record["lastname"] = "Smith"; # Update Caroline's lastname from Miranda to Smith

# Pass the single record recordset and the array containing the data to update
# into the GetUpdateSQL function. The function will process the data and return
# a fully formatted update sql statement with the correct WHERE clause.
# If the data has not changed, no recordset is returned
$updateSQL = $conn->GetUpdateSQL($rs, $record);

$conn->Execute($updateSQL); # Update the record in the database
$conn->Close();
?>

Example 8: Implementing Scrolling with Next and Previous

Code and idea by Iván Oliva.

We use the HTTP Get variable $next_page to keep track of what page to go next and store the current page in the session variable $curr_page.

We call the connection object's PageExecute() function to generate the appropriate recordset, then use the recordset's AtFirstPage() and AtLastPage() functions to determine whether to display the next and previous buttons.

<?php
include_once('adodb.inc.php');
include_once('tohtml.inc.php');
session_register('curr_page');

$db = NewADOConnection('mysql');
$db->Connect('localhost','root','','xphplens');
$num_of_rows_per_page = 10;
$sql = 'select * from products';

if (isset($HTTP_GET_VARS['next_page']))
	$curr_page = $HTTP_GET_VARS['next_page'];
if (empty($curr_page)) $curr_page = 1; ## at first page

$rs = $db->PageExecute($sql, $num_of_rows_per_page, $curr_page);
if (!$rs) die('Query Failed');

if (!$rs->EOF && (!$rs->AtFirstPage() || !$rs->AtLastPage())) {
	if (!$rs->AtFirstPage()) {
?>
<a href="<?php echo $PHPSELF,'?next_page=',$rs->AbsolutePage() - 1 ?>">Previous page</a>
<?php
	}
	if (!$rs->AtLastPage()) {
?>
<a href="<?php echo $PHPSELF,'?next_page=',$rs->AbsolutePage() + 1 ?>">Next page</a>
<?php
	}
	rs2html($rs);
}
?>

Using Custom Error Handlers and PEAR_Error

ADODB provides two custom handlers which you can modify for your needs. The first one is in the adodb-errorhandler.inc.php file. This makes use of the standard PHP functions error_reporting to control what error messages types to display, and trigger_error which invokes the default PHP error handler.

Including the above file will cause trigger_error($errorstring,E_USER_ERROR) to be called when Connect() or PConnect() fails, or a function that executes SQL statements such as Execute() or SelectLimit() has an error. This file should be included before you create any ADOConnection objects.

If you define error_reporting(0), no errors will be shown. If you set error_reporting(E_ALL), all errors will be displayed on the screen.

<?php
error_reporting(E_ALL); # show any error messages triggered
include('adodb-errorhandler.inc.php');
include('adodb.inc.php');
include('tohtml.inc.php');
$c = NewADOConnection('mysql');
$c->PConnect('localhost','root','','northwind');
$rs=$c->Execute('select * from productsz'); #invalid table productsz');
if ($rs) $rs2html($rs);
?>

If you want to log the error message, you can do so by defining the following optional constants ADODB_ERROR_LOG_TYPE and ADODB_ERROR_LOG_DEST. ADODB_ERROR_LOG_TYPE is the error log message type (see error_log in the PHP manual). In this case we set it to 3, which means log to the file defined by the constant ADODB_ERROR_LOG_DEST.

<?php
error_reporting(0); # do not echo any errors
define('ADODB_ERROR_LOG_TYPE',3);
define('ADODB_ERROR_LOG_DEST','C:\errors.log');
include('adodb-errorhandler.inc.php');
include('adodb.inc.php');
include('tohtml.inc.php');
$c = NewADOConnection('mysql');
$c->PConnect('localhost','root','','northwind');
$rs=$c->Execute('select * from productsz'); #invalid table productsz');
if ($rs) $rs2html($rs);
?>

(2001-10-28 14:20:38) mysql error: [1146: Table 'northwind.productsz' doesn't exist] in
 EXECUTE("select * from productsz")

<?php
include('adodb-errorpear.inc.php');
include('adodb.inc.php');
include('tohtml.inc.php');
$c = NewADOConnection('mysql');
$c->PConnect('localhost','root','','northwind');
$rs=$c->Execute('select * from productsz'); #invalid table productsz');
if ($rs) $rs2html($rs);
else {
	$e = ADODB_Pear_Error();
	echo '<p>',$e->message(),'</p>';
}
?>

include('PEAR.php');
PEAR::setErrorHandling('PEAR_ERROR_DIE');

Caching of Recordsets

ADODB now supports caching of recordsets using the CacheExecute( ), CachePageExecute( ) and CacheSelectLimit( ) functions. There are similar to the non-cache functions, except that they take a new first parameter, $secs2cache.

An example of use is the following:

include('adodb.inc.php'); # load code common to ADODB
$ADODB_CACHE_DIR = '/usr/adodb_cache';
$conn = &ADONewConnection('mysql');  # create a connection
$conn->PConnect('localhost','userid','','agora');# connect to MySQL, agora db
$sql = 'select CustomerName, CustomerID from customers';
$rs = $conn->CacheExecute(15,$sql);

	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
	$rs1 = $db->Execute('select * from table');
	$ADODB_FETCH_MODE = 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')

 $conn->Connect(false, 'scott', 'tiger', 'OracleSID');
 $conn->Connect('server:1521', 'scott', 'tiger', 'OracleDB');

 $conn->Execute("SELECT * FROM TABLE WHERE COND=:val", array('val'=> $val));
 

  $conn->Execute("SELECT * FROM TABLE WHERE COND=?", array($val));

$rs = $db->Execute('select * from table where val=?', array('10'));

$rs = $db->Execute('select name from table where val=:key', 
  array('key' => 10));

  include('adodb.inc.php'); 
  include('tohtml.inc.php');
  $ADODB_CACHE_DIR = '/usr/local/adodbcache';
  $conn = &ADONewConnection('mysql'); 
  $conn->PConnect('localhost','userid','password','database');
  $rs = $conn->CacheExecute(15, 'select * from table'); # cache 15 secs
  rs2html($rs); /* recordset to html table */  

 $conn->SelectLimit("SELECT * FROM TABLE WHERE COND=:val", 100,-1,array('val'=> $val));
 

>In addition, I found a way to structure the Select Union so that it will
> work with the optimization in place.  It works under MS-SQL, don't know
> about the others...  When updating the help file, you could use this as an
> example of how to structure the SQL --
No it doesn't work with MySQL.
> 
> Change:
>  Select column1 From table1
>  Union
>  Select column2 From table2
> 
> To:
>  Select * From (
>   Select column1 From table1
>   Union
>   Select column2 From table2
>   )
>  As dummytable
> 
> Ron

	$conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1, null)');
	$conn->UpdateBlob('blobtable','blobcol',$blobvalue,'id=1');

CachePageExecute($secs2cache, $sql, $nrows, $page, $inputarr=false)

Used for pagination of recordset. $page is 1-baed. See Example 8. Caching version of PageExecute.

AbsolutePage($page=-1)

Returns the current page. Requires PageExecute()/CachePageExecute() to be called. See Example 8.

$rs = $db->Execute('select firstname,lastname from table');
if ($rs) {
	while ($o = $rs->FetchNextObject()) {
		print "$o->FIRSTNAME, $o->LASTNAME<BR>";
	}
}

<?
include('tohtml.inc.php'); # load code common to ADODB 
include('adodb.inc.php'); # load code common to ADODB 
$conn = &ADONewConnection('mysql');   # create a connection 
$conn->PConnect('localhost','userid','','agora');# connect to MySQL, agora db
$sql = 'select CustomerName, CustomerID from customers'; 
$rs   = $conn->Execute($sql); 
rs2html($rs,'border=2 cellpadding=3',array('Customer Name','Customer ID'));
?>