Released under both BSD license and Lesser GPL library license. You can choose which license you prefer.
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.
We currently support MySQL, Oracle, Microsoft SQL Server, Sybase, Sybase SQL Anywhere, DB2, Informix, PostgreSQL, FrontBase, Interbase (Firebird and Borland variants), Foxpro, Access, ADO, SAP DB, SQLite and ODBC. We have had successful reports of connecting to Progress and other databases via ODBC.
Latest Download at http://adodb.sourceforge.net/ Set ADODB_DIR to the directory where this file resides... This constant was formerly called $ADODB_RootPath
Accepts $src and $dest arrays, replacing string $data
ADODB version as a string.
Determines whether recordset->RecordCount() is used. Set to false for highest performance – RecordCount() will always return -1 then for databases that provide "virtual" recordcounts...
Helper class for FetchFields – holds info on a column
Private function to erase all of the files and subdirectories in a directory.
Just specify the directory, and tell it if you want to delete the directory or just clear it out. Note: $kill_top_level is used internally in the function to flush subdirectories.
Connection object. For connecting to databases, and executing queries.
RDBMS currently in use, eg. odbc, mysql, mssql
Name of database to be used.
The hostname of the database server
The username which is used to connect to the database server.
Password for the username. For security, we no longer store it.
if set to true will output sql statements
maximum size of blobs or large text fields (262144 = 256K)– some db's die otherwise like foxpro
default concat operator – change to || for Oracle/Interbase
substring operator
string length ofperator
random function
uppercase function
used by DBDate() as the default date format used by the database
used by DBTimeStamp as the default timestamp fmt.
string that represents TRUE for a database
string that represents FALSE for a database
string to use to replace quotes
string to use to quote identifiers and names
character set to use - only for interbase, postgres and oci8
All order by columns have to be unique
supports autoincrement ID?
supports affected rows for update/delete?
support mssql/access SELECT TOP 10 * FROM TABLE
support pgsql/mysql SELECT * FROM TABLE LIMIT 10
this is a readonly database - used by phpLens
has ability to run MoveFirst(), scrolling backwards
can generate sequences using GenID();
has transactions
sequence id used by GenID();
error function to call
accepts dates in ISO format
cache for 1 hour
should we use memCache instead of caching in files
memCache host
memCache port
Use 'true' to store the item compressed (uses zlib)
name of function that returns the current date
name of function that returns the current timestamp
name of class used to generate array recordsets, which are pre-downloaded recordsets
oracle specific stuff - if true ensures that '' is converted to ' '
indicates that all fields in order by must be unique
operator to use for left outer join in WHERE clause
operator to use for right outer join in WHERE clause
whether ansi outer join syntax supported
do not modify this yourself - actually private
temporarily disable transactions
count of nested transactions
The returned link identifier whenever a successful database connection is made.
A variable which was used to keep the returned last error message. The value will
then returned by the errorMsg() function
Last error code, not guaranteed to be used - only by oci8
This variable keeps the last created result link identifier
A boolean variable to state whether its a persistent connection or normal connection. */
set to true if ADOConnection.Execute() permits binding of array parameters.
Constructor
Get server version info...
All error messages go through this bottleneck function. You can define your own handler by defining the function name in ADODB_OUTP.
Connect to database
| [argHostname] | Host to connect to |
| [argUsername] | Userid to login |
| [argPassword] | Associated password |
| [argDatabaseName] | database |
| [forceNew] | force new connection |
Always force a new connection to database - currently only works with oracle
| [argHostname] | Host to connect to |
| [argUsername] | Userid to login |
| [argPassword] | Associated password |
| [argDatabaseName] | database |
Establish persistent connect to database
| [argHostname] | Host to connect to |
| [argUsername] | Userid to login |
| [argPassword] | Associated password |
| [argDatabaseName] | database |
Should prepare the sql statement and return the stmt resource. For databases that do not support this, we return the $sql. To ensure compatibility with databases that do not support prepare:
$stmt = $db->Prepare("insert into table (id, name) values (?,?)"); $db->Execute($stmt,array(1,'Jill')) or die('insert failed'); $db->Execute($stmt,array(2,'Joe')) or die('insert failed');
| sql | SQL to send to database |
Some databases, eg. mssql require a different function for preparing stored procedures. So we cannot use Prepare().
Should prepare the stored procedure and return the stmt resource. For databases that do not support this, we return the $sql. To ensure compatibility with databases that do not support prepare:
| sql | SQL to send to database |
PEAR DB Compat
Requested by "Karsten Dambekalns" k.dambekalns@fishfarm.de
PEAR DB Compat - do not use internally.
PEAR DB Compat - do not use internally.
Lock a row, will escalate and lock the table if row locking not supported will normally free the lock at the end of the transaction
| $table | name of table to lock |
| $where | where clause to use, eg: "WHERE row=12". If left empty, will escalate to table lock |
PEAR DB Compat - do not use internally.
The fetch modes for NUMERIC and ASSOC for PEAR DB and ADODB are identical for easy porting :-)
| mode | The fetchmode ADODB_FETCH_ASSOC or ADODB_FETCH_NUM |
PEAR DB Compat - do not use internally.
PEAR DB Compat - do not use internally
PEAR DB Compat - do not use internally
Improved method of initiating a transaction. Used together with CompleteTrans(). Advantages include:
a. StartTrans/CompleteTrans is nestable, unlike BeginTrans/CommitTrans/RollbackTrans. Only the outermost block is treated as a transaction.
b. CompleteTrans auto-detects SQL errors, and will rollback on errors, commit otherwise.
c. All BeginTrans/CommitTrans/RollbackTrans inside a StartTrans/CompleteTrans block are disabled, making it backward compatible.
Used together with StartTrans() to end a transaction. Monitors connection for sql errors, and will commit or rollback as appropriate.
if true, monitor sql errors and commit and rollback as appropriate, and if set to false force rollback even if no SQL error detected.
Check if transaction has failed, only for Smart Transactions.
Execute SQL
| sql | SQL statement to execute, or possibly an array holding prepared statement ($sql[0] will hold sql text) |
| [inputarr] | holds the input data to bind to. Null elements will be set to null. |
Generates a sequence id and stores it in $this->genID; GenID is only available if $this->hasGenID = true;
| seqname | name of sequence to use |
| startID | if sequence does not exist, start at this ID |
| $table | string name of the table, not needed by all databases (eg. mysql), default '' |
| $column | string name of the column, not needed by all databases (eg. mysql), default '' |
Portable Insert ID. Pablo Roca <pabloroca::mvps.org>
Choose a database to connect to. Many databases do not support this.
| dbName | is the name of the database to select |
Will select, getting rows from $offset (1-based), for $nrows. This simulates the MySQL "select * from table limit $offset,$nrows" , and the PostgreSQL "select * from table limit $nrows offset $offset". Note that MySQL and PostgreSQL parameter ordering is the opposite of the other. eg. SelectLimit('select * from table',3); will return rows 1 to 3 (1-based) SelectLimit('select * from table',3,2); will return rows 3 to 5 (1-based)
Uses SELECT TOP for Microsoft databases (when $this->hasTop is set) BUG: Currently SelectLimit fails with $sql with LIMIT or TOP clause already set
| sql | |
| [offset] | is the row to start calculations from (1-based) |
| [nrows] | is the number of rows to get |
| [inputarr] | array of bind variables |
| [secs2cache] | is a private parameter only used by jlim |
Create serializable recordset. Breaks rs link to connection.
| rs | the recordset to serialize |
Convert database recordset to an array recordset input recordset's cursor should be at beginning, and old $rs will be closed.
| rs | the recordset to copy |
| [nrows] | number of rows to retrieve (optional) |
| [offset] | offset by number of rows (optional) |
Return first element of first row of sql statement. Recordset is disposed for you.
| sql | SQL statement |
| [inputarr] | input bind array |
| sql | SQL statement |
| [inputarr] | input bind array |
Return one row of sql statement. Recordset is disposed for you. Note that SelectLimit should not be called.
| sql | SQL statement |
| [inputarr] | input bind array |
Insert or replace a single record. Note: this is not the same as MySQL's replace. ADOdb's Replace() uses update-insert semantics, not insert-delete-duplicates of MySQL. Also note that no table locking is done currently, so it is possible that the record be inserted twice by two programs...
$this->Replace('products', array('prodname' =>"'Nails'","price" => 3.99), 'prodname');
$table table name $fieldArray associative array of data (you must quote strings yourself). $keyCol the primary key field name or if compound key, array of field names autoQuote set to true to use a hueristic to quote strings. Works with nulls and numbers but does not work with dates nor SQL functions. has_autoinc the primary key is an auto-inc field, so skip in insert.
Currently blob replace not supported
returns 0 = fail, 1 = update, 2 = insert
Will select, getting rows from $offset (1-based), for $nrows. This simulates the MySQL "select * from table limit $offset,$nrows" , and the PostgreSQL "select * from table limit $nrows offset $offset". Note that MySQL and PostgreSQL parameter ordering is the opposite of the other. eg. CacheSelectLimit(15,'select * from table',3); will return rows 1 to 3 (1-based) CacheSelectLimit(15,'select * from table',3,2); will return rows 3 to 5 (1-based)
BUG: Currently CacheSelectLimit fails with $sql with LIMIT or TOP clause already set
| [secs2cache] | seconds to cache data, set to 0 to force query. This is optional |
| sql | |
| [offset] | is the row to start calculations from (1-based) |
| [nrows] | is the number of rows to get |
| [inputarr] | array of bind variables |
Flush cached recordsets that match a particular $sql statement. If $sql == false, then we purge all files in the cache.
Flush cached recordsets that match a particular $sql statement. If $sql == false, then we purge all files in the cache.
Private function to generate filename for caching. Filename is generated based on:
When not in safe mode, we create 256 sub-directories in the cache directory ($ADODB_CACHE_DIR). Assuming that we can have 50,000 files per directory with good performance, then we can scale to 12.8 million unique cached recordsets. Wow!
Execute SQL, caching recordsets.
| [secs2cache] | seconds to cache data, set to 0 to force query. This is an optional parameter. |
| sql | SQL statement to execute |
| [inputarr] | holds the input data to bind to |
Generates an Update Query based on an existing recordset. $arrFields is an associative array of fields with the value that should be assigned.
Note: This function should only be used on a recordset that is run against a single table and sql should only be a simple select stmt with no groupby/orderby/limit
"Jonathan Younger" jyounger@unilab.com
Generates an Insert Query based on an existing recordset. $arrFields is an associative array of fields with the value that should be assigned.
Note: This function should only be used on a recordset that is run against a single table.
Update a blob column, given a where clause. There are more sophisticated blob handling functions that we could have implemented, but all require a very complex API. Instead we have chosen something that is extremely simple to understand and use.
Note: $blobtype supports 'BLOB' and 'CLOB', default is BLOB of course.
Usage to update a $blobvalue which has a primary key blob_id=1 into a field blobtable.blobcolumn:
UpdateBlob('blobtable', 'blobcolumn', $blobvalue, 'blob_id=1');
Insert example:
$conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1, null)');
$conn->UpdateBlob('blobtable','blobcol',$blob,'id=1');
Usage: UpdateBlob('TABLE', 'COLUMN', '/path/to/file', 'ID=1');
$blobtype supports 'BLOB' and 'CLOB'
$conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1, null)'); $conn->UpdateBlob('blobtable','blobcol',$blobpath,'id=1');
Usage: UpdateClob('TABLE', 'COLUMN', $var, 'ID=1', 'CLOB');
$conn->Execute('INSERT INTO clobtable (id, clobcol) VALUES (1, null)'); $conn->UpdateClob('clobtable','clobcol',$clob,'id=1');
Change the SQL connection locale to a specified locale. This is used to get the date formats written depending on the client locale.
GetActiveRecordsClass Performs an 'ALL' query
| mixed | $class | This string represents the class of the current active record |
| mixed | $table | Table used by the active record object |
| mixed | $whereOrderBy | Where, order, by clauses |
| mixed | $bindarr | |
| mixed | $primkeyArr | |
| array | $extra | Query extras: limit, offset... |
| mixed | $relations | Associative array: table's foreign name, "hasMany", "belongsTo" public |
Close Connection
Begin a Transaction. Must be followed by CommitTrans() or RollbackTrans().
If database does not support transactions, always return true as data always commited
| $ok | set to false to rollback transaction, true to commit |
If database does not support transactions, rollbacks always fail, so return false
return the databases that the driver can connect to. Some databases will return an empty array.
List procedures or functions in an array.
| procedureNamePattern | a procedure name pattern; must match the procedure name as it is stored in the database |
| catalog | a catalog name; must match the catalog name as it is stored in the database; |
| schemaPattern | a schema name pattern; |
Array (
[name_of_procedure] => Array
(
[type] => PROCEDURE or FUNCTION
[catalog] => Catalog_name
[schema] => Schema_name
[remarks] => explanatory comment on the procedure
)
)
| ttype | can either be 'VIEW' or 'TABLE' or false. If false, both views and tables are returned. "VIEW" returns only views "TABLE" returns only tables |
| showSchema | returns the schema/user with the table name, eg. USER.TABLE |
| mask | is the input mask - only supported by oci8 and postgresql |
List columns in a database as an array of ADOFieldObjects. See top of file for definition of object.
| $table | table name to query |
| $normalize | makes table name case-insensitive (required by some databases) is optional database schema to use - not supported by all databases. |
List indexes on a table as an array.
| table | table name to query |
| primary | true to only show primary keys. Not actually used for most databases |
Array (
[name_of_index] => Array
(
[unique] => true or false
[columns] => Array
(
[0] => firstname
[1] => lastname
)
)
List columns names in a table as an array.
| table | table name to query |
Different SQL databases used different methods to combine strings together. This function provides a wrapper.
param s variable number of string parameters
Usage: $db->Concat($str1,$str2);
Converts a date "d" to a string that the database can understand.
| d | a date in Unix date time format. |
Converts a timestamp "ts" to a string that the database can understand.
| ts | a timestamp in Unix date time format. |
Also in ADORecordSet.
| $v | is a date string in YYYY-MM-DD format |
Also in ADORecordSet.
| $v | is a timestamp string in YYYY-MM-DD HH-NN-SS format |
Also in ADORecordSet.
Format database date based on user defined format.
| v | is the character date in YYYY-MM-DD format, returned by database |
| fmt | is the format to apply to it, using date() |
| v | is the character timestamp in YYYY-MM-DD hh:mm:ss format |
| fmt | is the format to apply to it, using date() |
Quotes a string, without prefixing nor appending quotes.
Correctly quotes a string so that all strings are escaped. We prefix and append to the string single-quotes. An example is $db->qstr("Don't bother",magic_quotes_runtime());
| s | the string to quote |
| [magic_quotes] | if $s is GET/POST var, set to get_magic_quotes_gpc(). This undoes the stupidity of magic quotes for GPC. |
Will select the supplied $page number from a recordset, given that it is paginated in pages of $nrows rows per page. It also saves two boolean values saying if the given page is the first and/or last one of the recordset. Added by Ivn Oliva to provide recordset pagination.
See readme.htm::ex8 for an example of usage.
| sql | |
| nrows | is the number of rows per page to get |
| page | is the page number to get (1-based) |
| [inputarr] | array of bind variables |
| [secs2cache] | is a private parameter only used by jlim |
NOTE: phpLens uses a different algorithm and does not use PageExecute().
Will select the supplied $page number from a recordset, given that it is paginated in pages of $nrows rows per page. It also saves two boolean values saying if the given page is the first and/or last one of the recordset. Added by Ivn Oliva to provide recordset pagination.
| secs2cache | seconds to cache data, set to 0 to force query |
| sql | |
| nrows | is the number of rows per page to get |
| page | is the page number to get (1-based) |
| [inputarr] | array of bind variables |
Internal placeholder for record objects. Used by ADORecordSet->FetchObj().
Lightweight recordset when there are no records to be returned
RecordSet class that represents the dataset returned by the database. To keep memory overhead low, this class holds only the current row in memory. No prefetching of data is done, so the RecordCount() can return -1 ( which means recordcount not known).
holds the current row data
any varchar/char field this size or greater is treated as a blob
in other words, we use a text area for editing.
indicates that seek is supported
sql text
Indicates that the current record position is after the last record in a Recordset object.
what to display when $time==0
what to display when $time==0
datetime in Unix format rs created – for cached recordsets
used by Fields() to hold array - should be private?
default fetch mode
the parent connection
number of rows, or -1
number of fields in recordset
This variable keeps the result link identifier.
This variable keeps the current row in the Recordset.
has recordset been closed
Init() should only be called once
Used by FetchObj
Used by FetchObj
Added by Ivn Oliva to implement recordset pagination
Added by Ivn Oliva to implement recordset pagination
Added by Ivn Oliva to implement recordset pagination
Constructor
| queryID | this is the queryID returned by ADOConnection->_query() |
Generate a SELECT tag string from a recordset, and return the string. If the recordset has 2 cols, we treat the 1st col as the containing the text to display to the user, and 2nd col as the return value. Default strings are compared with the FIRST column.
| name | name of SELECT tag |
| [defstr] | the value to hilite. Use an array for multiple hilites for listbox. |
| [blank1stItem] | true to leave the 1st item in list empty |
| [multiple] | true for listbox, false for popup |
| [size] | #rows to show for listbox. not used by popup |
| [selectAttr] | additional attributes to defined for SELECT tag. useful for holding javascript onChange='...' handlers. & |
| [compareFields0] | when we have 2 cols in recordset, we compare the defstr with column 0 (1st col) if this is true. This is not documented. |
changes by glen.davies@cce.ac.nz to support multiple hilited items
Generate a SELECT tag string from a recordset, and return the string. If the recordset has 2 cols, we treat the 1st col as the containing the text to display to the user, and 2nd col as the return value. Default strings are compared with the SECOND column.
return recordset as a 2-dimensional array.
| [nRows] | is the number of rows to return. -1 means every row. |
return recordset as a 2-dimensional array. Helper function for ADOConnection->SelectLimit()
| offset | is the row to start calculations from (1-based) |
| [nrows] | is the number of rows to return |
Synonym for GetArray() for compatibility with ADO.
| [nRows] | is the number of rows to return. -1 means every row. |
return whole recordset as a 2-dimensional associative array if there are more than 2 columns. The first column is treated as the key and is not included in the array. If there is only 2 columns, it will return a 1 dimensional array of key-value pairs unless $force_array == true.
| [force_array] | has only meaning if we have 2 data columns. If false, a 1 dimensional array is returned, otherwise a 2 dimensional array is returned. If this sounds confusing, read the source. |
| [first2cols] | means if there are more than 2 cols, ignore the remaining cols and instead of returning array[col0] => array(remaining cols), return array[col0] => col1 |
| v | is the character timestamp in YYYY-MM-DD hh:mm:ss format |
| fmt | is the format to apply to it, using date() |
| v | is the character date in YYYY-MM-DD format, returned by database |
| fmt | is the format to apply to it, using date() |
| $v | is a date string in YYYY-MM-DD format |
| $v | is a timestamp string in YYYY-MM-DD HH-NN-SS format |
PEAR DB Compat - do not use internally
PEAR DB compat, number of rows
PEAR DB compat, number of cols
Fetch a row, returning false if no more rows. This is PEAR DB compat mode.
Fetch a row, returning PEAR_Error if no more rows. This is PEAR DB compat mode.
Move to the first row in the recordset. Many databases do NOT support this.
Move to the last row in the recordset.
Move to next record in the recordset.
Random access to a specific row in the recordset. Some databases do not support access to previous rows in the databases (no scrolling backwards).
| rowNumber | is the row to move to (0-based) |
Get the value of a field in the current row by column name. Will not work if ADODB_FETCH_MODE is set to ADODB_FETCH_NUM.
| colname | is the field to access |
Builds the bind array associating keys to recordset fields
| int | $upper | Case for the array keys, defaults to uppercase (see ADODB_ASSOC_CASE_xxx constants) |
Use associative array to get fields array for databases that do not support associative arrays. Submitted by Paolo S. Asioli paolo.asioli::libero.it
| int | $upper | Case for the array keys, defaults to uppercase (see ADODB_ASSOC_CASE_xxx constants) |
Clean up recordset
synonyms RecordCount and RowCount
synonyms RecordCount and RowCount
Portable RecordCount. Pablo Roca pabloroca@mvps.org
But aware possible problems in multiuser environments. For better speed the table must be indexed by the condition. Heavy test this before deploying.
synonym for CurrentRow – for ADO compat
Get the ADOFieldObject of a specific column.
| fieldoffset | is the column position to access(0-based). |
Get the ADOFieldObjects of all columns in an array.
Return the fields array of the current row as an object for convenience. The default case is lowercase field names.
Return the fields array of the current row as an object for convenience. The default case is uppercase.
| $isupper | to set the object property names to uppercase |
Return the fields array of the current row as an object for convenience. The default is lower-case field names.
Fixed bug reported by tim@orotech.net
Return the fields array of the current row as an object for convenience. The default is upper case field names.
| $isupper | to set the object property names to uppercase |
Fixed bug reported by tim@orotech.net
Get the metatype of the column. This is used for formatting. This is because many databases use different names for the same type, so we transform the original type to our standardised version which uses 1 character codes:
| t | is the type passed in. Normally is ADOFieldObject->type. |
| len | is the maximum length of that field. This is because we treat character fields bigger than a certain size as a 'B' (blob). |
| fieldobj | is the field object returned by the database driver. Can hold additional info (eg. primary_key for mysql). |
set/returns the current recordset page when paginating
set/returns the status of the atFirstPage flag when paginating
set/returns the status of the atLastPage flag when paginating
This class encapsulates the concept of a recordset created in memory as an array. This is useful for the creation of cached recordsets.
Note that the constructor is different from the standard ADORecordSet
Constructor
Setup the array.
| array | is a 2-dimensional array holding the data. The first row should hold the column names unless paramter $colnames is used. |
| typearr | holds an array of types. These are the same types used in MetaTypes (C,B,L,I,N). |
| [colnames] | array of column names. If set, then the first row of $array should not hold the column names. |
Setup the Array and datatype file objects
| array | is a 2-dimensional array holding the data. The first row should hold the column names unless paramter $colnames is used. |
| fieldarr | holds an array of ADOFieldObject's. |
Synonym for ADOLoadCode. Private function. Do not use.
Load the code for a specific database driver. Private function. Do not use.
synonym for ADONewConnection for people like me who cannot remember the correct name
Instantiate a new Connection class for a specific database driver.
| [db] | is the database Connection object to create. If undefined, use the last database driver that was loaded by ADOLoadCode(). |
1.8.4
Aktuelle Beiträge