ADOdb Library for PHP

V3.40 7 April 2003 (c) 2000-2003 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
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
Example 9: Exporting in CSV or Tab-Delimited Format
Example 10: Custom filters
Using Custom Error Handlers and PEAR_Error
Data Source Names
Caching
Pivot Tables

REFERENCE

Variables: $ADODB_COUNTRECS $ADODB_CACHE_DIR $ADODB_FETCH_MODE 
Constants: ADODB_ASSOC_CASE
ADOConnection
Connections: Connect PConnect NConnect
Executing SQL: Execute CacheExecute SelectLimit CacheSelectLimit Prepare PrepareSP Parameter
              GetOne CacheGetOne GetRow CacheGetRow GetAll CacheGetAll GetCol CacheGetCol Replace
               ExecuteCursor (oci8 only)
Generates SQL: GetUpdateSQL GetInsertSQL
Blobs: UpdateBlob UpdateClob UpdateBlobFile BlobEncode BlobDecode
Paging/Scrolling: PageExecute CachePageExecute
Cleanup: CacheFlush Close
Transactions: BeginTrans CommitTrans RollbackTrans StartTrans CompleteTrans
Fetching Data: SetFetchMode
Strings: concat qstr quote
Dates: DBDate DBTimeStamp UnixDate UnixTimeStamp OffsetDate SQLDate
Rows Management: Affected_Rows Insert_ID GenID CreateSequence DropSequence
Error Handling: ErrorMsg ErrorNo MetaError MetaErrorMsg
Data Dictionary (metadata): MetaDatabases MetaTables MetaColumns MetaColumnNames MetaPrimaryKeys ServerInfo
Statistics and Query-Rewriting: fnExecute and fnCacheExecute
Deprecated: Bind BlankRecordSet

ADORecordSet
Returns one row:FetchRow FetchInto FetchObject FetchNextObject FetchObj FetchNextObj
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_RecordSet NextRecordSet
Field Info:FieldCount FetchField MetaType
Cleanup: Close
Deprecated: GetRowAssoc Fields

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, Interbase (Firebird and Borland variants), 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.

Also read http://php.weblogs.com/portable_sql (also available as tips_portable_sql.htm in the release) 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.
• PHP4 session support. See adodb-session.php.

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.

• 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.my or posted to the ADOdb Help forums at http://phplens.com/lens/lensforum/topics.php?id=4.

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, try modifying some of the tutorial examples. Make sure you customize the connection settings correctly. You can debug using:

<?php
	include('adodb/adodb.inc.php');
	$db = ADONewConnection($database);
	$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>";
?>

Code Initialization

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. You will use $conn->Connect() or $conn->PConnect() to perform the actual connection.

See the examples below in the Tutorial.

Databases Supported

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.

Tutorial

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,".$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 dsn
$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().

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

$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
# Note that field names are case-insensitive
$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
# Note that field names are case-insensitive
$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

The following code creates a very simple recordset pager, where you can scroll from page to page of a recordset.

include_once('../adodb.inc.php');
include_once('../adodb-pager.inc.php');
session_start();

$db = NewADOConnection('mysql');

$db->Connect('localhost','root','','xphplens');

$sql = "select * from adoxyz ";

$pager = new ADODB_Pager($db,$sql);
$pager->Render($rows_per_page=5);

This will create a basic record pager that looks like this:

|<   <<   >>   >|

ID First Name Last Name Date Created 36  Alan  Turing  Sat 06, Oct 2001  37  Serena  Williams  Sat 06, Oct 2001  38  Yat Sun  Sun  Sat 06, Oct 2001  39  Wai Hun  See  Sat 06, Oct 2001  40  Steven  Oey  Sat 06, Oct 2001

Page 8/10

The number of rows to display at one time is controled by the Render($rows) method. If you do not pass any value to Render(), ADODB_Pager will default to 10 records per page.

You can control the column titles by modifying your SQL (supported by most databases):

$sql = 'select id as "ID", firstname as "First Name", 
		  lastname as "Last Name", created as "Date Created" 
		from adoxyz';

The above code can be found in the adodb/tests/testpaging.php example included with this release, and the class ADODB_Pager in adodb/adodb-pager.inc.php. The ADODB_Pager code can be adapted by a programmer so that the text links can be replaced by images, and the dull white background be replaced with more interesting colors.

You can also allow display of html by setting $pager->htmlSpecialChars = false.

Some of the code used here was contributed by Iván Oliva and Cornel G.

Example 9: Exporting in CSV or Tab-Delimited Format

We provide some helper functions to export in comma-separated-value (CSV) and tab-delimited formats:

include_once('/path/to/adodb/toexport.inc.php');
include_once('/path/to/adodb/adodb.inc.php');

$db = &NewADOConnection('mysql');
$db->Connect($server, $userid, $password, $database);

$rs = $db->Execute('select fname as "First Name", surname as "Surname" from table');

print "<pre>";
print rs2csv($rs); # return a string, CSV format
print '<hr>';

$rs->MoveFirst(); # note, some databases do not support MoveFirst
print rs2tab($rs,false); # return a string, tab-delimited
						 # false == suppress field names in first line
print '<hr>';
$rs->MoveFirst();
rs2tabout($rs); # send to stdout directly (there is also an rs2csvout function)
print "</pre>";

$rs->MoveFirst();
$fp = fopen($path, "w");
if ($fp) {
  rs2csvfile($rs, $fp); # write to file (there is also an rs2tabfile function)
  fclose($fp);
}

Carriage-returns or newlines are converted to spaces. Field names are returned in the first line of text. Strings containing the delimiter character are quoted with double-quotes. Double-quotes are double-quoted again. This conforms to Excel import and export guide-lines.

All the above functions take as an optional last parameter, $addtitles which defaults to true. When set to false field names in the first line are suppressed.

Example 10: Recordset Filters

Sometimes we want to pre-process all rows in a recordset before we use it. For example, we want to ucwords all text in recordset.

include_once('adodb/rsfilter.inc.php');
include_once('adodb/adodb.inc.php');

// ucwords() every element in the recordset
function do_ucwords(&$arr,$rs)
{
	foreach($arr as $k => $v) {
		$arr[$k] = ucwords($v);
	}
}

$db = NewADOConnection('mysql');
$db->PConnect('server','user','pwd','db');

$rs = $db->Execute('select ... from table');
$rs = RSFilter($rs,'do_ucwords');

The RSFilter function takes 2 parameters, the recordset, and the name of the filter function. It returns the processed recordset scrolled to the first record. The filter function takes two parameters, the current row as an array, and the recordset object. For future compatibility, you should not use the original recordset object.

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
(a) Connect() or PConnect() fails, or
(b) a function that executes SQL statements such as Execute() or SelectLimit() has an error.
(c) GenID() appears to go into an infinite loop.

The $errorstring is generated by ADOdb and will contain useful debugging information similar to the error.log data generated below. This file adodb-errorhandler.inc.php 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>';
}
?>

You can use a PEAR_Error derived class by defining the constant ADODB_PEAR_ERROR_CLASS before the adodb-errorpear.inc.php file is included. For easy debugging, you can set the default error handler in the beginning of the PHP script to PEAR_ERROR_DIE, which will cause an error message to be printed, then halt script execution:

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

Note that we do not explicitly return a PEAR_Error object to you when an error occurs. We return false instead. You have to call ADODB_Pear_Error() to get the last error or use the PEAR_ERROR_DIE technique.

Error Messages

Error messages are outputted using the static method ADOConnnection::outp($msg,$newline=true). By default, it sends the messages to the client. You can override this to perform error-logging.

Data Source Names

We now support connecting using PEAR style DSN's. A DSN is a connection string of the form:

$dsn = "$driver://$username:$password@$hostname/$databasename";

You pass the DSN to the static class function DB::Connect. An example:

   include_once('../adodb/adodb-pear.inc.php');
   $username = 'root';
   $password = '';
   $hostname = 'localhost';
   $databasename = 'xphplens';
   $driver = 'mysql';
   $dsn = "$driver://$username:$password@$hostname/$databasename";

   $db = DB::Connect($dsn);
   $rs = $db->Execute('select firstname,lastname from adoxyz');
   $cnt = 0;
   while ($arr = $rs->FetchRow()) {
		print_r($arr); print "<br>";
   }

This requires PEAR to be installed and in the default include path in php.ini.

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:

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

The first parameter is the number of seconds to cache the query. Subsequent calls to that query will used the cached version stored in $ADODB_CACHE_DIR. To force a query to execute and flush the cache, call CacheExecute() with the first parameter set to zero. Alternatively, use the CacheFlush($sql) call.

For the sake of security, we recommend you set register_globals=off in php.ini if you are using $ADODB_CACHE_DIR.

In ADOdb 1.80 onwards, the secs2cache parameter is optional in CacheSelectLimit() and CacheExecute(). If you leave it out, it will use the $connection->cacheSecs parameter, which defaults to 60 minutes.

	$conn->Connect(...);
	$conn->cacheSecs = 3600*24; # cache 24 hours
	$rs = $conn->CacheExecute('select * from table');

Please note that magic_quotes_runtime should be turned off. More info.

Pivot Tables

Since ADOdb 2.30, we support the generation of SQL to create pivot tables, also known as cross-tabulations. For further explanation read this DevShed Cross-Tabulation tutorial. We assume that your database supports the SQL case-when expression.

# Query the main "product" table
# Set the rows to CompanyName
# and the columns to the values of Categories
# and define the joins to link to lookup tables 
# "categories" and "suppliers"
#
 include "adodb/pivottable.php";
 $sql = PivotTableSQL(
 	$gDB,                                      # adodb connection
 	'products p ,categories c ,suppliers s',   # tables
	'CompanyName',                             # rows (multiple fields allowed)
	'CategoryName',                            # column to pivot on 
	'p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID' # joins/where
);

This will generate the following SQL:

SELECT CompanyName,
SUM(CASE WHEN CategoryName='Beverages' THEN 1 ELSE 0 END) AS "Beverages",
SUM(CASE WHEN CategoryName='Condiments' THEN 1 ELSE 0 END) AS "Condiments",
SUM(CASE WHEN CategoryName='Confections' THEN 1 ELSE 0 END) AS "Confections",
SUM(CASE WHEN CategoryName='Dairy Products' THEN 1 ELSE 0 END) AS "Dairy Products",
SUM(CASE WHEN CategoryName='Grains/Cereals' THEN 1 ELSE 0 END) AS "Grains/Cereals",
SUM(CASE WHEN CategoryName='Meat/Poultry' THEN 1 ELSE 0 END) AS "Meat/Poultry",
SUM(CASE WHEN CategoryName='Produce' THEN 1 ELSE 0 END) AS "Produce",
SUM(CASE WHEN CategoryName='Seafood' THEN 1 ELSE 0 END) AS "Seafood",
SUM(1) as Total
FROM products p ,categories c ,suppliers s WHERE p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID
GROUP BY CompanyName

You can also pivot on numerical columns and generate totals by using ranges. This code was revised in ADODB 2.41 and is not backward compatible. The second example shows this:

 $sql = PivotTableSQL(
 	$gDB,                                       # adodb connection
 	'products p ,categories c ,suppliers s',    # tables
	'CompanyName',                              # rows (multiple fields allowed)
   array(                                       # column ranges
	' 0 '      => 'UnitsInStock <= 0',
	"1 to 5"   => '0 < UnitsInStock and UnitsInStock <= 5',
	"6 to 10"  => '5 < UnitsInStock and UnitsInStock <= 10',
	"11 to 15" => '10 < UnitsInStock and UnitsInStock <= 15',
	"16+"      => '15 < UnitsInStock'
	),
	' p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID', # joins/where
	'UnitsInStock',                             # sum this field
	'Sum '                                      # sum label prefix
);

Which generates:

SELECT CompanyName,
SUM(CASE WHEN UnitsInStock <= 0 THEN UnitsInStock ELSE 0 END) AS "Sum 0 ",
SUM(CASE WHEN 0 < UnitsInStock and UnitsInStock <= 5 THEN UnitsInStock ELSE 0 END) AS "Sum 1 to 5",
SUM(CASE WHEN 5 < UnitsInStock and UnitsInStock <= 10 THEN UnitsInStock ELSE 0 END) AS "Sum 6 to 10",
SUM(CASE WHEN 10 < UnitsInStock and UnitsInStock <= 15 THEN UnitsInStock ELSE 0 END) AS "Sum 11 to 15",
SUM(CASE WHEN 15 < UnitsInStock THEN UnitsInStock ELSE 0 END) AS "Sum 16+",
SUM(UnitsInStock) AS "Sum UnitsInStock",
SUM(1) as Total,
FROM products p ,categories c ,suppliers s WHERE p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID
GROUP BY CompanyName

Global Variables

$ADODB_COUNTRECS

If the database driver API does not support counting the number of records returned in a SELECT statement, the function RecordCount() is emulated when the global variable $ADODB_COUNTRECS is set to true, which is the default. We emulate this by buffering the records, which can take up large amounts of memory for big recordsets. Set this variable to false for the best performance. This variable is checked every time a query is executed, so you can selectively choose which recordsets to count.

$ADODB_CACHE_DIR

chown -R apache /path/to/adodb/cache
chgrp -R apache /path/to/adodb/cache

define('ADODB_FETCH_DEFAULT',0);
define('ADODB_FETCH_NUM',1);
define('ADODB_FETCH_ASSOC',2);
define('ADODB_FETCH_BOTH',3);

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

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

 # $oraname in tnsnames.ora/ONAMES/HOSTNAMES
 $conn->Connect(false, 'scott', 'tiger', $oraname); 
 $conn->Connect('server:1521', 'scott', 'tiger', 'ServiceName'); # bypass tnsnames.ora

 $conn = &NewADOConnection('mysql');
 $conn->autoRollback = true; # default is false
 $conn->PConnect(...); # rollback here

Then when doing a persistent connection with PConnect( ), ADOdb will perform a rollback first. This is because it is documented that PHP is not guaranteed to rollback existing failed transactions when persistent connections are used. This is implemented in Oracle, MySQL, PgSQL, MSSQL, ODBC currently.

Since ADOdb 3.11, you can force non-persistent connections even if PConnect is called by defining the constant ADODB_NEVER_PERSIST before you call PConnect.

NConnect($host,[$user],[$password],[$database])

Always force new connection. In contrast, PHP sometimes reuses connections when you use Connect() or PConnect(). Currently works only on mysql (PHP 4.3.0 or later) and oci8-derived drivers. For other drivers, NConnect() works like Connect().

Execute($sql,$inputarr=false)

 $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->Connect(...);
   	$conn->cacheSecs = 3600*24; // cache 24 hours
	$rs = $conn->CacheExecute('select * from table');

    $db = ADONewConnection("oci8"); 
    $db->Connect("foo.com:1521", "uid", "pwd", "FOO"); 
    $rs = $db->ExecuteCursor("begin :cursorvar := getdata(:param1); end;", 
					'cursorvar',
					array('param1'=>10)); 
    # $rs is now just like any other ADOdb recordset object
    rs2html($rs);

	$stmt = $db->Prepare("BEGIN :RS := SP_FOO(); END;");
	$db->Parameter($stmt, $cur, 'RS', false, -1, OCI_B_CURSOR);
	$rs = $db->Execute($stmt);

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

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

	$conn->Connect(...);
   $conn->cacheSecs = 3600*24; // cache 24 hours
	$rs = $conn->CacheSelectLimit('select * from table',10);

SetFetchMode($mode)

Sets the current fetch mode for the connection and stores it in $db->fetchMode. Legal modes are ADODB_FETCH_ASSOC and ADODB_FETCH_NUM. For more info, see $ADODB_FETCH_MODE.

Returns the previous fetch mode, which could be false if SetFetchMode( ) has not been called before.

	# for oracle
	$conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1, empty_blob())');
	$conn->UpdateBlob('blobtable','blobcol',$blobvalue,'id=1');
	
	# non oracle databases
	$conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1, null)');
	$conn->UpdateBlob('blobtable','blobcol',$blobvalue,'id=1');

	# for oracle
	$conn->Execute('INSERT INTO clobtable (id, clobcol) VALUES (1, empty_clob())');
	$conn->UpdateBlob('clobtable','clobcol',$clobvalue,'id=1');
	
	# non oracle databases
	$conn->Execute('INSERT INTO clobtable (id, clobcol) VALUES (1, null)');
	$conn->UpdateBlob('clobtable','clobcol',$clobvalue,'id=1');

Some databases require blob's to be decoded manually after doing a select statement. If the database does not require decoding, then this function will return the blob unchanged. Currently BlobDecode is only required for one database, PostgreSQL, and only if you are using blob oid's (if you are using bytea fields, we auto-decode for you).

$rs = $db->Execute("select bloboid from postgres_table where id=$key");
$blob = $db->BlobDecode( reset($rs->fields) );

# single field primary key
$ret = $db->Replace('atable', 
	array('id'=>1000,'firstname'=>'Harun','lastname'=>'Al-Rashid'),
	'id',
	'firstname',$autoquote = true);	
# generates UPDATE atable SET firstname='Harun',lastname='Al-Rashid' WHERE id=1000
# or INSERT INTO atable (id,firstname,lastname) VALUES (1000,'Harun','Al-Rashid')

# compound key
$ret = $db->Replace('atable2', 
	array('firstname'=>'Harun','lastname'=>'Al-Rashid', 'age' => 33, 'birthday' => 'null'),
	array('lastname','firstname'),
	'firstname',$autoquote = true);

# no auto-quoting
$ret = $db->Replace('atable2', 
	array('firstname'=>"'Harun'",'lastname'=>"'Al-Rashid'", 'age' => 'null'),
	array('lastname','firstname'),
	'firstname');	

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

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

$DB->BeginTrans();
$DB->Execute("update table1 set val=$val1 where id=$id");
$DB->Execute("update table2 set val=$val2 where id=$id");
$DB->CommitTrans();

$DB->BeginTrans();
$ok = $DB->Execute("update table1 set val=$val1 where id=$id");
if ($ok) $ok = $DB->Execute("update table2 set val=$val2 where id=$id");
if ($ok) $DB->CommitTrans();
else $DB->RollbackTrans();

$DB->BeginTrans();
$ok = $DB->Execute("update table1 set val=$val1 where id=$id");
if ($ok) $ok = $DB->Execute("update table2 set val=$val2 where id=$id");
$DB->CommitTrans($ok);

$DB->StartTrans();
CallBlackBox();
$DB->Execute("update table1 set val=$val1 where id=$id");
$DB->Execute("update table2 set val=$val2 where id=$id");
$DB->CompleteTrans($ok);

Executes the SQL and returns the all the rows as a 2-dimensional array. The recordset is discarded for you automatically. If an error occurs, false is returned.

GetCol($sql,$inputarr=false,$trim=false)

Executes the SQL and returns all elements of the first column as a 1-dimensional array. The recordset is discarded for you automatically. If an error occurs, false is returned.

CacheGetOne([$secs2cache,] $sql,$inputarr=false), CacheGetRow([$secs2cache,] $sql,$inputarr=false), CacheGetAll([$secs2cache,] $sql,$inputarr=false), CacheGetCol([$secs2cache,] $sql,$inputarr=false,$trim=false)

Prepares an SQL query for repeated execution. Only supported internally by interbase, oci8 and selected ODBC-based drivers, otherwise it is emulated. There is no performance advantage to using Prepare() with emulation.

Returns an array containing the original sql statement in the first array element; the remaining elements of the array are driver dependent. If there is an error, or we are emulating Prepare( ), we return the original $sql string. This is because all error-handling has been centralized in Execute( ).

Example:

$stmt = $DB->Prepare('insert into table (col1,col2) values (?,?)');
for ($i=0; $i < $max; $i++)
	$DB->Execute($stmt,array((string) rand(), $i));

# @RETVAL = SP_RUNSOMETHING @myid,@group
$stmt = $db->PrepareSP('SP_RUNSOMETHING'); 
# note that the parameter name does not have @ in front!
$db->Parameter($stmt,$id,'myid'); 
$db->Parameter($stmt,$group,'group',false,64);
# return value in mssql - RETVAL is hard-coded name 
$db->Parameter($stmt,$ret,'RETVAL',true); 
$db->Execute($stmt); 

An oci8 example:

# For oracle, Prepare and PrepareSP are identical
$stmt = $db->PrepareSP(
	"declare RETVAL integer; 
	 begin 
		:RETVAL := SP_RUNSOMETHING(:myid,:group); 
	 end;");
$db->Parameter($stmt,$id,'myid');
$db->Parameter($stmt,$group,'group',false,64);
$db->Parameter($stmt,$ret,'RETVAL',true);
$db->Execute($stmt);

$id = 0; $i = 0;
$stmt = $db->PrepareSP( "update table set val=:i where id=:id");
$db->Parameter($stmt,$id,'id');
$db->Parameter($stmt,$i, 'i');
for ($cnt=0; $cnt < 1000; $cnt++) {
	$id = $cnt; 
	$i = $cnt * $cnt; # works with oci8!
	$db->Execute($stmt); 
}

This is a low-level function supported only by the oci8 driver. Avoid using unless you only want to support Oracle. The Parameter( ) function is the recommended way to go with bind variables.

Bind( ) allows you to use bind variables in your sql statement. This binds a PHP variable to a name defined in an Oracle sql statement that was previously prepared using Prepare(). Oracle named variables begin with a colon, and ADOdb requires the named variables be called :0, :1, :2, :3, etc. The first invocation of Bind() will match :0, the second invocation will match :1, etc. Binding can provide 100% speedups for insert, select and update statements.

The other variables, $size sets the buffer size for data storage, $type is the optional descriptor type OCI_B_FILE (Binary-File), OCI_B_CFILE (Character-File), OCI_B_CLOB (Character-LOB), OCI_B_BLOB (Binary-LOB) and OCI_B_ROWID (ROWID). Lastly, instead of using the default :0, :1, etc names, you can define your own bind-name using $name.

The following example shows 3 bind variables being used: p1, p2 and p3. These variables are bound to :0, :1 and :2.

$stmt = $DB->Prepare("insert into table (col0, col1, col2) values (:0, :1, :2)");
$DB->Bind($stmt, $p1);
$DB->Bind($stmt, $p2);
$DB->Bind($stmt, $p3);
for ($i = 0; $i < $max; $i++) { 
   $p1 = ?; $p2 = ?; $p3 = ?;
   $DB->Execute($stmt);
}

You can also use named variables:

$stmt = $DB->Prepare("insert into table (col0, col1, col2) values (:name0, :name1, :name2)");
$DB->Bind($stmt, $p1, "name0");
$DB->Bind($stmt, $p2, "name1");
$DB->Bind($stmt, $p3, "name2");
for ($i = 0; $i < $max; $i++) { 
   $p1 = ?; $p2 = ?; $p3 = ?;
   $DB->Execute($stmt);
}

fnExecute and fnCacheExecute properties

These two properties allow you to define bottleneck functions for all sql statements processed by ADOdb. This allows you to perform statistical analysis and query-rewriting of your sql. For example, to count all cached queries and non-cached queries, you can do this:

# $db is the connection object
function CountExecs($db, $sql, $inputarray)
{
global $EXECS;  $EXECS++;
}

# $db is the connection object
function CountCachedExecs($db, $secs2cache, $sql, $inputarray)
{
global $CACHED; $CACHED++;
}

$db = NewADOConnection('mysql');
$db->Connect(...);
$db->fnExecute = 'CountExecs';
$db->fnCacheExecute = 'CountCachedExecs';
 :
 :
# After many sql statements:`
printf("<p>Total queries=%d; total cached=%d</p>",$EXECS+$CACHED, $CACHED);

The fnExecute function is called before the sql is parsed and executed, so you can perform a query rewrite. If you are passing in a prepared statement, then $sql is an array (see Prepare). The fnCacheExecute function is only called if the recordset returned was cached. The function parameters match the Execute and CacheExecute functions respectively, except that $this (the connection object) is passed as the first parameter.

MetaPrimaryKeys($table)

Returns an array containing column names that are the primary keys of $table. Only supported by mysql, postgres, oci8 currently.

ServerInfo($table)

Returns an array of containing two elements 'description' and 'version'. The 'description' element contains the string description of the database. The 'version' naturally holds the version number (which is also a string).

This function is available in both ADORecordSet and ADOConnection since 1.91.

OffsetDate($dayFraction, $basedate=false)

Allows you to calculate future and past dates based on $basedate in a portable fashion. If $basedate is not defined, then the current date (at 12 midnight) is used. Returns the SQL string that performs the calculation when passed to Execute().

For example, in Oracle, to find the date and time that is 2.5 days from today, you can use:

# get date one week from now
$fld = $conn->OffsetDate(7); // returns "(trunc(sysdate)+7")

# get date and time that is 60 hours from current date and time
$fld = $conn->OffsetDate(2.5, $conn->sysTimeStamp);	// returns "(sysdate+2.5)"

$conn->Execute("UPDATE TABLE SET dodate=$fld WHERE ID=$id");

This function is available for mysql, mssql, oracle, oci8 and postgresql drivers since 2.13. It might work with other drivers provided they allow performing numeric day arithmetic on dates.

 Y: 4-digit Year
 Q: Quarter (1-4)
 M: Month (01-12)
 D: Day (01-31)

 $sqlfn = $db->SQLDate('Y-\QQ','postdate'); # get sql that formats postdate to output 2002-Q1
 $sql = "SELECT $sqlfn,SUM(cogs) FROM table GROUP BY $sqlfn ORDER BY 1 desc";
 

$rs = $db->Execute($sql);
if ($rs) 
	while (!$rs->EOF) {
 		ProcessArray($rs->fields);	
		$rs->MoveNext();
	} 

AbsolutePage($page=-1)

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

Returns array containing current row, or false if EOF. FetchRow( ) internally moves to the next record after returning the current row.

Warning: Do not mix using FetchRow() with MoveNext().

Usage:

$rs = $db->Execute($sql);
if ($rs)
	while ($arr = $rs->FetchRow()) {
	     # process $arr	
	}

FetchInto(&$array)

Sets $array to the current row. Returns PEAR_Error object if EOF, 1 if ok (DB_OK constant). If PEAR is undefined, false is returned when EOF. FetchInto( ) internally moves to the next record after returning the current row.

FetchRow() is easier to use. See above.

$rs = $db->Execute('execute return_multiple_rs');
$arr1 = $rs->GetArray();
$rs->NextRecordSet();
$arr2 = $rs->GetArray();

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

Returns the current record as an object. Fields are not upper-cased, unlike FetchObject.

FetchNextObj()

Returns the current record as an object and moves to the next record. If EOF, false is returned. Fields are not upper-cased, unlike FetctNextObject.

Since ADOdb 3.0, MetaType accepts $fieldobj as the first parameter, instead of $nativeDBType.

<?
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'));
?>

Change Log

3.40 ?? March 2003

Modified postgresql UpdateBlobFile() because it did not work in safe mode.

Now connection object is passed to raiseErrorFn as last parameter. Needed by StartTrans().

Added StartTrans() and CompleteTrans(). It is recommended that you do not modify transOff, but use the above functions.

oci8po now obeys ADODB_ASSOC_CASE settings.

Added virtualized error codes, using PEAR DB equivalents. Requires you to manually include adodb-error.inc.php yourself, with MetaError() and MetaErrorMsg($errno).

GetRowAssoc for mysql and pgsql were flawed. Fix by Ross Smith.

Added to datadict types I1, I2, I4 and I8. Changed datadict type 'T' to map to timestamp instead of datetime for postgresql.

Error handling in ExecuteSQLArray(), adodb-datadict.inc.php did not work.

We now auto-quote postgresql connection parameters when building connection string.

Added session expiry notification.

We now test with odbc mysql - made some changes to odbc recordset constructor.

MetaColumns now special cases access and other databases for odbc.

3.31 17 March 2003

Added row checking for _fetch in postgres.

Added Interval type to MetaType for postgres.

Remapped postgres driver to call postgres7 driver internally.

Adorecordset_array::getarray() did not return array when nRows >= 0.

Postgresql: at times, no error message returned by pg_result_error() but error message returned in pg_last_error(). Recoded again.

Interbase blob's now use chunking for updateblob.

Move() did not set EOF correctly. Reported by Jorma T.

We properly support mysql timestamp fields when we are creating mysql tables using the data-dict interface.

Table regex includes backticks character now.

3.30 3 March 2003

Added $ADODB_EXTENSION and $ADODB_COMPAT_FETCH constant.

Made blank1stItem configurable using syntax "value:text" in GetMenu/GetMenu2. Thx to Gabriel Birke.

Previously ADOdb differed from the Microsoft standard because it did not define what to set $this->fields when EOF was reached. Now at EOF, ADOdb sets $this->fields to false for all databases, which is consist with Microsoft's implementation. Postgresql and mysql have always worked this way (in 3.11 and earlier). If you are experiencing compatibility problems (and you are not using postgresql nor mysql) on upgrading to 3.30, try setting the global variables $ADODB_COUNTRECS = true (which is the default) and $ADODB_FETCH_COMPAT = true (this is a new global variable).

We now check both pg_result_error and pg_last_error as sometimes pg_result_error does not display anything. Iman Mayes

We no longer check for magic quotes gpc in Quote().

Misc fixes for table creation in adodb-datadict.inc.php. Thx to iamsure.

Time calculations use adodb_time library for all negative timestamps due to problems in Red Hat 7.3 or later. Formerly, only did this for Windows.

In mssqlpo, we now check if $sql in _query is a string before we change || to +. This is to support prepared stmts.

Move() and MoveLast() internals changed to support to support EOF and $this->fields change.

Added ADODB_FETCH_BOTH support to mssql. Thx to Angel Fradejas afradejas#mediafusion.es

We now check if link resource exists before we run mysql_escape_string in qstr().

Before we flock in csv code, we check that it is not a http url.

3.20 17 Feb 2003

Added new Data Dictionary classes for creating tables and indexes. Warning - this is very much alpha quality code. The API can still change. See adodb/tests/test-datadict.php for more info.

We now ignore $ADODB_COUNTRECS for mysql, because PHP truncates incomplete recordsets when mysql_unbuffered_query() is called a second time.

Now postgresql works correctly when $ADODB_COUNTRECS = false.

Changed _adodb_getcount to properly support SELECT DISTINCT.

Discovered that $ADODB_COUNTRECS=true has some problems with prepared queries - suspect PHP bug.

Now GetOne and GetRow run in $ADODB_COUNTRECS=false mode for better performance.

Added support for mysql_real_escape_string() and pg_escape_string() in qstr().

Added an intermediate variable for mysql _fetch() and MoveNext() to store fields, to prevent overwriting field array with boolean when mysql_fetch_array() returns false.

Made arrays for getinsertsql and getupdatesql case-insensitive. Suggested by Tim Uckun" tim#diligence.com

3.11 11 Feb 2003

Added check for ADODB_NEVER_PERSIST constant in PConnect(). If defined, then PConnect() will actually call non-persistent Connect().

Modified interbase to properly work with Prepare().

Added $this->ibase_timefmt to allow you to change the date and time format.

Added support for $input_array parameter in CacheFlush().

Added experimental support for dbx, which was then removed when i found that it was slower than using native calls.

Added MetaPrimaryKeys for mssql and ibase/firebird.

Added new $trim parameter to GetCol and CacheGetCol

Uses updated adodb-time.inc.php 0.06.

3.10 27 Jan 2003

Added adodb_date(), adodb_getdate(), adodb_mktime() and adodb-time.inc.php.

For interbase, added code to handle unlimited number of bind parameters. From Daniel Hasan daniel#hasan.cl.

Added BlobDecode and UpdateBlob for informix. Thx to Fernando Ortiz.

Added constant ADODB_WINDOWS. If defined, means that running on Windows.

Added constant ADODB_PHPVER which stores php version as a hex num. Removed $ADODB_PHPVER variable.

Felho Bacsi reported a minor white-space regular expression problem in GetInsertSQL.

Modified ADO to use variant to store _affectedRows

Changed ibase to use base class Replace(). Modified base class Replace() to support ibase.

Changed odbc to auto-detect when 0 records returned is wrong due to bad odbc drivers.

Changed mssql to use datetimeconvert ini setting only when 4.30 or later (does not work in 4.23).

ExecuteCursor($stmt, $cursorname, $params) now accepts a new $params array of additional bind parameters -- William Lovaton walovaton#yahoo.com.mx.

Added support for sybase_unbuffered_query if ADODB_COUNTRECS == false. Thx to chuck may.

Fixed FetchNextObj() bug. Thx to Jorma Tuomainen.

We now use SCOPE_IDENTITY() instead of @@IDENTITY for mssql - thx to marchesini#eside.it

Changed postgresql movenext logic to prevent illegal row number from being passed to pg_fetch_array().

Postgresql initrs bug found by "Bogdan RIPA" bripa#interakt.ro $f1 accidentally named $f

3.00 6 Jan 2003

Fixed adodb-pear.inc.php syntax error.

Improved _adodb_getcount() to use SELECT COUNT(*) FROM ($sql) for languages that accept it.

Fixed _adodb_getcount() caching error.

Added sql to retrive table and column info for odbc_mssql.

2.91 3 Jan 2003

Revised PHP version checking to use $ADODB_PHPVER with legal values 0x4000, 0x4050, 0x4200, 0x4300.

Added support for bytea fields and oid blobs in postgres by allowing BlobDecode() to detect and convert non-oid fields. Also added BlobEncode to postgres when you want to encode oid blobs.

Added blobEncodeType property for connections to inform phpLens what encoding method to use for blobs.

Added BlobDecode() and BlobEncode() to base ADOConnection class.

Added umask() to _gencachename() when creating directories.

Added charPage for ado drivers, so you can set the code page.

$conn->charPage = CP_UTF8;
$conn->Connect($dsn);

Modified _seek in mysql to check for num rows=0.

Added to metatypes new informix types for IDS 9.30. Thx Fernando Ortiz.

_maxrecordcount returned in CachePageExecute $rsreturn

Fixed sybase cacheselectlimit( ) problems

MetaColumns() max_length should use precision for types X and C for ms access. Fixed.

Speedup of odbc non-SELECT sql statements.

Added support in MetaColumns for Wide Char types for ODBC. We halve max_length if unicode/wide char.

Added 'B' to types handled by GetUpdateSQL/GetInsertSQL.

Fixed warning message in oci8 driver with $persist variable when using PConnect.

2.90 11 Dec 2002

Mssql and mssqlpo and oci8po now support ADODB_ASSOC_CASE.

Now MetaType() can accept a field object as the first parameter.

New $arr = $db->ServerInfo( ) function. Returns $arr['description'] which is the string description, and $arr['version'].

PostgreSQL and MSSQL speedups for insert/updates.

Implemented new SetFetchMode() that removes the need to use $ADODB_FETCH_MODE. Each connection has independant fetchMode.

ADODB_ASSOC_CASE now defaults to 2, use native defaults. This is because we would break backward compat for too many applications otherwise.

Patched encrypted sessions to use replace()

The qstr function supports quoting of nulls when escape character is \

Rewrote bits and pieces of session code to check for time synch and improve reliability.

Added property ADOConnection::hasTransactions = true/false;

Added CreateSequence and DropSequence functions

Found misplaced MoveNext() in adodb-postgres.inc.php. Fixed.

Sybase SelectLimit not reliable because 'set rowcount' not cached - fixed.

Moved ADOConnection to adodb-connection.inc.php and ADORecordSet to adodb-recordset.inc.php. This allows us to use doxygen to generate documentation. Doxygen doesn't like the classes in the main adodb.inc.php file for some mysterious reason.

2.50, 14 Nov 2002

Added transOff and transCnt properties for disabling (transOff = true) and tracking transaction status (transCnt>0).

Added inputarray handling into _adodb_pageexecute_all_rows - "Ross Smith" RossSmith#bnw.com.

Fixed postgresql inconsistencies in date handling.

Added support for mssql_fetch_assoc.

Fixed $ADODB_FETCH_MODE bug in odbc MetaTables() and MetaPrimaryKeys().

Accidentally declared UnixDate() twice, making adodb incompatible with php 4.3.0. Fixed.

Fixed pager problems with some databases that returned -1 for _currentRow on MoveLast() by switching to MoveNext() in adodb-lib.inc.php.

Also fixed uninited $discard in adodb-lib.inc.php.

2.43, 25 Oct 2002

Added support for NConnect() for oracle OCINLogin.

Fixed NumCols() bug.

Changed session handler to use Replace() on write.

Fixed oci8 SelectLimit aggregate function bug again.

Rewrote pivoting code.

2.42, 4 Oct 2002

Fixed ibase_fetch() problem with nulls. Also interbase now does automatic blob decoding, and is backward compatible. Suggested by Heinz Hombergs heinz#hhombergs.de.

Fixed postgresql MoveNext() problems when called repeatedly after EOF. Also suggested by Heinz Hombergs.

PageExecute() does not rewrite queries if SELECT DISTINCT is used. Requested by hans#velum.net

Added additional fixes to oci8 SelectLimit handling with aggregate functions - thx to Christian Bugge for reporting the problem.

2.41, 2 Oct 2002

Fixed ADODB_COUNTRECS bug in odbc. Thx to Joshua Zoshi jzoshi#hotmail.com.

Increased buffers for adodb-csvlib.inc.php for extremely long sql from 8192 to 32000.

Revised pivottable.inc.php code. Added better support for aggregate fields.

Fixed mysql text/blob types problem in MetaTypes base class - thx to horacio degiorgi.

Added SQLDate($fmt,$date) function, which allows an sql date format string to be generated - useful for group by's.

Fixed bug in oci8 SelectLimit when offset>100.

2.40 4 Sept 2002

Added new NLS_DATE_FORMAT property to oci8. Suggested by Laurent NAVARRO ln#altidev.com

Now use bind parameters in oci8 selectlimit for better performance.

Fixed interbase replaceQuote for dialect != 1. Thx to "BEGUIN Pierre-Henri - INFOCOB" phb#infocob.com.

Added white-space check to QA.

Changed unixtimestamp to support fractional seconds (we always round down/floor the seconds). Thanks to beezly#beezly.org.uk.

Now you can set the trigger_error type your own user-defined type in adodb-errorhandler.inc.php. Suggested by Claudio Bustos clbustos#entelchile.net.

Added recordset filters with rsfilter.inc.php.

$conn->_rs2rs does not create a new recordset when it detects it is of type array. Some trickery there as there seems to be a bug in Zend Engine

Added render_pagelinks to adodb-pager.inc.php. Code by "Pablo Costa" pablo#cbsp.com.br.

MetaType() speedup in adodb.inc.php by using hashing instead of switch. Best performance if constant arrays are supported, as they are in PHP5.

adodb-session.php now updates only the expiry date if the crc32 check indicates that the data has not been modified.

0.10 Sept 9 2000 First release

Old changelog history moved to old-changelog.htm.