[2] | 1 | <html> |
---|
| 2 | <head> |
---|
| 3 | <title>ADODB Manual</title> |
---|
| 4 | <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> |
---|
| 5 | |
---|
| 6 | |
---|
| 7 | <style> |
---|
| 8 | pre { |
---|
| 9 | background-color: #eee; |
---|
| 10 | padding: 0.75em 1.5em; |
---|
| 11 | font-size: 12px; |
---|
| 12 | border: 1px solid #ddd; |
---|
| 13 | } |
---|
| 14 | </style> |
---|
| 15 | </head> |
---|
| 16 | <body bgcolor="#FFFFFF"> |
---|
| 17 | |
---|
| 18 | <h2>ADOdb Library for PHP</h2> |
---|
| 19 | <p>V4.50 6 July 2004 (c) 2000-2004 John Lim (jlim#natsoft.com)</p> |
---|
| 20 | <p><font size="1">This software is dual licensed using BSD-Style and LGPL. This |
---|
| 21 | means you can use it in compiled proprietary and commercial products.</font></p> |
---|
| 22 | |
---|
| 23 | |
---|
| 24 | <table border=1><tr><td><font color=red>Kindly note that the ADOdb home page has moved to <a href=http://adodb.sourceforge.net/>http://adodb.sourceforge.net/</a> because of the persistent |
---|
| 25 | unreliability of http://php.weblogs.com. <b>Please change your links</b>!</font></td></tr></table> |
---|
| 26 | <p>Useful ADOdb links: <a href=http://adodb.sourceforge.net/#download>Download</a> <a href=http://adodb.sourceforge.net/#docs>Other Docs</a> |
---|
| 27 | |
---|
| 28 | <p><a href="#intro"><b>Introduction</b></a><b><br> |
---|
| 29 | <a href="#features">Unique Features</a><br> |
---|
| 30 | <a href="#users">How People are using ADOdb</a><br> |
---|
| 31 | <a href="#bugs">Feature Requests and Bug Reports</a><br> |
---|
| 32 | </b><b><a href="#install">Installation</a><br> |
---|
| 33 | <a href="#mininstall">Minimum Install</a><br> |
---|
| 34 | <a href="#coding">Initializing Code and Connectioning to Databases</a><br> |
---|
| 35 | </b><font size="2"> <a href=#dsnsupport>Data Source Name (DSN) Support</a></font> <a href=#connect_ex>Connection Examples</a></font> <br> |
---|
| 36 | <b><a href="#speed">High Speed ADOdb - tuning tips</a></b><br> |
---|
| 37 | <b><a href="#hack">Hacking and Modifying ADOdb Safely</a><br> |
---|
| 38 | <a href="#php5">PHP5 Features</a></b><br> |
---|
| 39 | <font size="2"><a href=#php5iterators>foreach iterators</a> <a href=#php5exceptions>exceptions</a></font><br> |
---|
| 40 | <b> <a href="#drivers">Supported Databases</a></b><br> |
---|
| 41 | <b> <a href="#quickstart">Tutorials</a></b><br> |
---|
| 42 | <a href="#ex1">Example 1: Select</a><br> |
---|
| 43 | <a href="#ex2">Example 2: Advanced Select</a><br> |
---|
| 44 | <a href="#ex3">Example 3: Insert</a><br> |
---|
| 45 | <a href="#ex4">Example 4: Debugging</a> <a href="#exrs2html">rs2html |
---|
| 46 | example</a><br> |
---|
| 47 | <a href="#ex5">Example 5: MySQL and Menus</a><br> |
---|
| 48 | <a href="#ex6">Example 6: Connecting to Multiple Databases at once</a> <br> |
---|
| 49 | <a href="#ex7">Example 7: Generating Update and Insert SQL</a> <br> |
---|
| 50 | <a href="#ex8">Example 8: Implementing Scrolling with Next and Previous</a><br> |
---|
| 51 | <a href="#ex9">Example 9: Exporting in CSV or Tab-Delimited Format</a> <br> |
---|
| 52 | <a href="#ex10">Example 10: Custom filters</a><br> |
---|
| 53 | <a href="#ex11">Example 11: Smart Transactions</a><br> |
---|
| 54 | <br> |
---|
| 55 | <b> <a href="#errorhandling">Using Custom Error Handlers and PEAR_Error</a><br> |
---|
| 56 | <a href="#DSN">Data Source Names</a><br> |
---|
| 57 | <a href="#caching">Caching</a><br> |
---|
| 58 | <a href="#pivot">Pivot Tables</a></b> |
---|
| 59 | <p><a href="#ref"><b>REFERENCE</b></a> |
---|
| 60 | <p> <font size="2">Variables: <a href="#adodb_countrecs">$ADODB_COUNTRECS</a> <a href=#adodb_ansi_padding_off>$ADODB_ANSI_PADDING_OFF</a> |
---|
| 61 | <a href="#adodb_cache_dir">$ADODB_CACHE_DIR</a> </font><font size="2"><a href=#adodb_fetch_mode>$ADODB_FETCH_MODE</a> |
---|
| 62 | <a href=#adodb_lang>$ADODB_LANG</a><br> |
---|
| 63 | Constants: </font><font size="2"><a href=#adodb_assoc_case>ADODB_ASSOC_CASE</a> |
---|
| 64 | </font><br> |
---|
| 65 | <a href="#ADOConnection"><b> ADOConnection</b></a><br> |
---|
| 66 | <font size="2">Connections: <a href="#connect">Connect</a> <a href="#pconnect">PConnect</a> |
---|
| 67 | <a href="#nconnect">NConnect</a> <br> |
---|
| 68 | Executing SQL: <a href="#execute">Execute</a> <a href="#cacheexecute"><i>CacheExecute</i></a> |
---|
| 69 | <a href="#SelectLimit">SelectLimit</a> <a href="#cacheSelectLimit"><i>CacheSelectLimit</i></a> |
---|
| 70 | <a href="#param">Param</a> <a href="#prepare">Prepare</a> <a href=#preparesp>PrepareSP</a> |
---|
| 71 | <a href="#inparameter">InParameter</a> <a href="#outparameter">OutParameter</a> |
---|
| 72 | <br> |
---|
| 73 | <a href="#getone">GetOne</a> |
---|
| 74 | <a href="#cachegetone"><i>CacheGetOne</i></a> <a href="#getrow">GetRow</a> <a href="#cachegetrow"><i>CacheGetRow</i></a> |
---|
| 75 | <a href="#getall">GetAll</a> <a href="#cachegetall"><i>CacheGetAll</i></a> <a href="#getcol">GetCol</a> |
---|
| 76 | <a href="#cachegetcol"><i>CacheGetCol</i></a> <a href="#getassoc1">GetAssoc</a> <a href="#cachegetassoc"><i>CacheGetAssoc</i></a> <a href="#replace">Replace</a> |
---|
| 77 | <br> |
---|
| 78 | <a href="#executecursor">ExecuteCursor</a> |
---|
| 79 | (oci8 only)<br> |
---|
| 80 | Generates SQL strings: <a href="#getupdatesql">GetUpdateSQL</a> <a href="#getinsertsql">GetInsertSQL</a> |
---|
| 81 | <a href="#concat">Concat</a> <a href="#ifnull">IfNull</a> <a href="#length">length</a> <a href="#random">random</a> <a href="#substr">substr</a> |
---|
| 82 | <a href="#qstr">qstr</a> <a href="#param">Param</a> |
---|
| 83 | <a href="#prepare"></a><a href="#OffsetDate">OffsetDate</a> <a href="#SQLDate">SQLDate</a> |
---|
| 84 | <a href="#dbdate">DBDate</a> <a href="#dbtimestamp"></a> <a href="#dbtimestamp">DBTimeStamp</a> |
---|
| 85 | <br> |
---|
| 86 | Blobs: <a href="#updateblob">UpdateBlob</a> <a href="#updateclob">UpdateClob</a> |
---|
| 87 | <a href="#updateblobfile">UpdateBlobFile</a> <a href="#blobencode">BlobEncode</a> |
---|
| 88 | <a href="#blobdecode">BlobDecode</a><br> |
---|
| 89 | Paging/Scrolling: <a href="#pageexecute">PageExecute</a> <a href="#cachepageexecute">CachePageExecute</a><br> |
---|
| 90 | Cleanup: <a href="#cacheflush">CacheFlush</a> <a href="#Close">Close</a><br> |
---|
| 91 | Transactions: <a href="#starttrans">StartTrans</a> <a href="#completetrans">CompleteTrans</a> |
---|
| 92 | <a href="#failtrans">FailTrans</a> <a href="#hasfailedtrans">HasFailedTrans</a> |
---|
| 93 | <a href="#begintrans">BeginTrans</a> <a href="#committrans">CommitTrans</a> |
---|
| 94 | <a href="#rollbacktrans">RollbackTrans</a> <br> |
---|
| 95 | Fetching Data: </font> <font size="2"><a href="#setfetchmode">SetFetchMode</a><br> |
---|
| 96 | Strings: <a href="#concat">concat</a> <a href="#length">length</a> <a href="#qstr">qstr</a> <a href="#quote">quote</a> <a href="#substr">substr</a><br> |
---|
| 97 | Dates: <a href="#dbdate">DBDate</a> <a href="#dbtimestamp">DBTimeStamp</a> <a href="#unixdate">UnixDate</a> |
---|
| 98 | <a href="#unixtimestamp">UnixTimeStamp</a> <a href="#OffsetDate">OffsetDate</a> |
---|
| 99 | <a href="#SQLDate">SQLDate</a> <br> |
---|
| 100 | Row Management: <a href="#affected_rows">Affected_Rows</a> <a href="#inserted_id">Insert_ID</a> <a href=#rowlock>RowLock</a> |
---|
| 101 | <a href="#genid">GenID</a> <a href=#createseq>CreateSequence</a> <a href=#dropseq>DropSequence</a> |
---|
| 102 | <br> |
---|
| 103 | Error Handling: <a href="#errormsg">ErrorMsg</a> <a href="#errorno">ErrorNo</a> |
---|
| 104 | <a href="#metaerror">MetaError</a> <a href="#metaerrormsg">MetaErrorMsg</a><br> |
---|
| 105 | Data Dictionary (metadata): <a href="#metadatabases">MetaDatabases</a> <a href="#metatables">MetaTables</a> |
---|
| 106 | <a href="#metacolumns">MetaColumns</a> <a href="#metacolumnames">MetaColumnNames</a> |
---|
| 107 | <a href="#metaprimarykeys">MetaPrimaryKeys</a> <a href="#metaforeignkeys">MetaForeignKeys</a> |
---|
| 108 | <a href="#serverinfo">ServerInfo</a> <br> |
---|
| 109 | Statistics and Query-Rewriting: <a href="#logsql">LogSQL</a> <a href="#fnexecute">fnExecute |
---|
| 110 | and fnCacheExecute</a><br> |
---|
| 111 | </font><font size="2">Deprecated: <a href="#bind">Bind</a> <a href="#blankrecordset">BlankRecordSet</a> |
---|
| 112 | <a href="#parameter">Parameter</a></font> |
---|
| 113 | <a href="#adorecordSet"><b><br> |
---|
| 114 | ADORecordSet</b></a><br> |
---|
| 115 | <font size="2"> |
---|
| 116 | Returns one field: <a href="#fields">Fields</a><br> |
---|
| 117 | Returns one row:<a href="#fetchrow">FetchRow</a> <a href="#fetchinto">FetchInto</a> |
---|
| 118 | <a href="#fetchobject">FetchObject</a> <a href="#fetchnextobject">FetchNextObject</a> |
---|
| 119 | <a href="#fetchobj">FetchObj</a> <a href="#fetchnextobj">FetchNextObj</a> |
---|
| 120 | <a href="#getrowassoc">GetRowAssoc</a> <br> |
---|
| 121 | Returns all rows:<a href="#getarray">GetArray</a> <a href="#getrows">GetRows</a> |
---|
| 122 | <a href="#getassoc">GetAssoc</a><br> |
---|
| 123 | Scrolling:<a href="#move">Move</a> <a href="#movenext">MoveNext</a> <a href="#movefirst">MoveFirst</a> |
---|
| 124 | <a href="#movelast">MoveLast</a> <a href="#abspos">AbsolutePosition</a> <a href="#currentrow">CurrentRow</a> |
---|
| 125 | <a href="#atfirstpage">AtFirstPage</a> <a href="#atlastpage">AtLastPage</a> |
---|
| 126 | <a href="#absolutepage">AbsolutePage</a> </font> <font size="2"><br> |
---|
| 127 | Menu generation:<a href="#getmenu">GetMenu</a> <a href="#getmenu2">GetMenu2</a><br> |
---|
| 128 | Dates:<a href="#userdate">UserDate</a> <a href="#usertimestamp">UserTimeStamp</a> |
---|
| 129 | <a href="#unixdate">UnixDate</a> <a href="#unixtimestamp">UnixTimeStamp<br> |
---|
| 130 | </a>Recordset Info:<a href="#recordcount">RecordCount</a> <a href="#po_recordcount">PO_RecordSet</a> |
---|
| 131 | <a href="#nextrecordset">NextRecordSet</a><br> |
---|
| 132 | Field Info:<a href="#fieldcount">FieldCount</a> <a href="#fetchfield">FetchField</a> |
---|
| 133 | <a href="#metatype">MetaType</a><br> |
---|
| 134 | Cleanup: <a href="#rsclose">Close</a></font> <font size="2"></font> |
---|
| 135 | <p><font size="2"><a href="#rs2html"><b>rs2html</b></a> <a href="#exrs2html">example</a></font><br> |
---|
| 136 | <a href="#adodiff">Differences between ADOdb and ADO</a><br> |
---|
| 137 | <a href="#driverguide"><b>Database Driver Guide<br> |
---|
| 138 | </b></a><b><a href="#changes">Change Log</a></b><br> |
---|
| 139 | </p> |
---|
| 140 | <h2>Introduction<a name="intro"></a></h2> |
---|
| 141 | <p>PHP's database access functions are not standardised. This creates a need for |
---|
| 142 | a database class library to hide the differences between the different database |
---|
| 143 | API's (encapsulate the differences) so we can easily switch databases. PHP 4.0.5 or later |
---|
| 144 | is now required (because we use array-based str_replace).</p> |
---|
| 145 | <p>We currently support MySQL, Oracle, Microsoft SQL Server, Sybase, Sybase SQL Anywhere, Informix, |
---|
| 146 | PostgreSQL, FrontBase, SQLite, Interbase (Firebird and Borland variants), Foxpro, Access, ADO, DB2, SAP DB and ODBC. |
---|
| 147 | We have had successful reports of connecting to Progress and CacheLite via ODBC. We hope more people |
---|
| 148 | will contribute drivers to support other databases.</p> |
---|
| 149 | <p>PHP4 supports session variables. You can store your session information using |
---|
| 150 | ADOdb for true portability and scalability. See adodb-session.php for more information.</p> |
---|
| 151 | <p>Also read <a href="http://php.weblogs.com/portable_sql">http://php.weblogs.com/portable_sql</a> |
---|
| 152 | (also available as tips_portable_sql.htm in the release) for tips on writing |
---|
| 153 | portable SQL.</p> |
---|
| 154 | <h2>Unique Features of ADOdb<a name="features"></a></h2> |
---|
| 155 | <ul> |
---|
| 156 | <li><b>Easy for Windows programmers</b> to adapt to because many of the conventions |
---|
| 157 | are similar to Microsoft's ADO.</li> |
---|
| 158 | <li>Unlike other PHP database classes which focus only on select statements, |
---|
| 159 | <b>we provide support code to handle inserts and updates which can be adapted |
---|
| 160 | to multiple databases quickly.</b> Methods are provided for date handling, |
---|
| 161 | string concatenation and string quoting characters for differing databases.</li> |
---|
| 162 | <li>A<b> metatype system </b>is built in so that we can figure out that types |
---|
| 163 | such as CHAR, TEXT and STRING are equivalent in different databases.</li> |
---|
| 164 | <li><b>Easy to port</b> because all the database dependant code are stored in |
---|
| 165 | stub functions. You do not need to port the core logic of the classes.</li> |
---|
| 166 | <li><b>Portable table and index creation</b> with the <a href=docs-datadict.htm>datadict</a> classes. |
---|
| 167 | <li><b>Database performance monitoring and SQL tuning</b> with the <a href=docs-perf.htm>performance monitoring</a> classes. |
---|
| 168 | <li><b>Database-backed sessions</b> with the <a href=docs-session.htm>session management</a> classes. Supports session expiry notification. |
---|
| 169 | </ul> |
---|
| 170 | <h2>How People are using ADOdb<a name="users"></a></h2> |
---|
| 171 | Here are some examples of how people are using ADOdb (for a much longer list, |
---|
| 172 | visit <a href="http://php.weblogs.com/adodb-cool-applications">http://php.weblogs.com/adodb-cool-applications</a>): |
---|
| 173 | <ul> |
---|
| 174 | <li><a href="http://phplens.com/">PhpLens</a> 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.<p> |
---|
| 175 | |
---|
| 176 | <li><a href="http://www.interakt.ro/phakt/">PHAkt: PHP Extension for DreamWeaver Ultradev</a> allows you to script PHP in the popular Web page editor. Database handling provided by ADOdb.<p> |
---|
| 177 | |
---|
| 178 | <li><a href="http://www.andrew.cmu.edu/~rdanyliw/snort/snortacid.html">Analysis Console for Intrusion Databases</a> (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.<p> |
---|
| 179 | |
---|
| 180 | <li><a href="http://www.postnuke.com/">PostNuke</a> is a very popular free content management |
---|
| 181 | 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. <p> |
---|
| 182 | |
---|
| 183 | <li><a href=http://www.auto-net.no/easypublish.php?page=index&lang_id=2>EasyPublish CMS</a> is another free content management system for managing information and integrated modules on your internet, intranet- and extranet-sites. From Norway.<p> |
---|
| 184 | |
---|
| 185 | <li><a href="http://nola.noguska.com/">NOLA</a> is a full featured accounting, inventory, and job tracking application. It is licensed under the GPL, and developed by Noguska. |
---|
| 186 | </ul><p> |
---|
| 187 | |
---|
| 188 | <h2>Feature Requests and Bug Reports<a name="bugs"></a></h2> |
---|
| 189 | <p>Feature requests and bug reports can be emailed to <a href="mailto:jlim#natsoft.com.my">jlim#natsoft.com.my</a> |
---|
| 190 | or posted to the ADOdb Help forums at <a href="http://phplens.com/lens/lensforum/topics.php?id=4">http://phplens.com/lens/lensforum/topics.php?id=4</a>.</p> |
---|
| 191 | <h2>Installation Guide<a name="install"></a></h2> |
---|
| 192 | <p>Make sure you are running PHP 4.0.5 or later. |
---|
| 193 | Unpack all the files into a directory accessible by your webserver.</p> |
---|
| 194 | <p>To test, try modifying some of the tutorial examples. Make sure you customize |
---|
| 195 | the connection settings correctly. You can debug using <i>$db->debug = true</i> as shown below:</p> |
---|
| 196 | <pre><?php |
---|
| 197 | include('adodb/adodb.inc.php'); |
---|
| 198 | $db = <a href="#adonewconnection">ADONewConnection</a>($dbdriver); # eg 'mysql' or 'postgres' |
---|
| 199 | $db->debug = true; |
---|
| 200 | $db-><a href="#connect">Connect</a>($server, $user, $password, $database); |
---|
| 201 | $rs = $db-><a href="#execute">Execute</a>('select * from some_small_table'); |
---|
| 202 | print "<pre>"; |
---|
| 203 | print_r($rs-><a href="#getrows">GetRows</a>()); |
---|
| 204 | print "</pre>"; |
---|
| 205 | ?></pre> |
---|
| 206 | |
---|
| 207 | <h3>Minimum Install<a name=mininstall></a></h3> |
---|
| 208 | <p>For developers who want to release a minimal install of ADOdb, you will need: |
---|
| 209 | <ul> |
---|
| 210 | <li>adodb.inc.php |
---|
| 211 | <li>adodb-lib.inc.php |
---|
| 212 | <li>adodb-time.inc.php |
---|
| 213 | <li>drivers/adodb-$database.inc.php |
---|
| 214 | <li>license.txt (for legal reasons) |
---|
| 215 | <li>adodb-php4.inc.php |
---|
| 216 | <li>adodb-iterator.inc.php |
---|
| 217 | </ul> |
---|
| 218 | Optional: |
---|
| 219 | <ul> |
---|
| 220 | <li>adodb-error.inc.php and lang/adodb-$lang.inc.php (if you use MetaError()) |
---|
| 221 | <li>adodb-csvlib.inc.php (if you use cached recordsets - CacheExecute(), etc) |
---|
| 222 | <li>adodb-exceptions.inc.php and adodb-errorhandler.inc.php (if you use adodb error handler or php5 exceptions). |
---|
| 223 | </ul> |
---|
| 224 | |
---|
| 225 | <h3>Code Initialization Examples<a name="coding"></a></h3> |
---|
| 226 | <p>When running ADOdb, at least two files are loaded. First is adodb/adodb.inc.php, |
---|
| 227 | which contains all functions used by all database classes. The code specific |
---|
| 228 | to a particular database is in the adodb/driver/adodb-????.inc.php file.</p> |
---|
| 229 | <a name="adonewconnection"></a> |
---|
| 230 | <p>For example, to connect to a mysql database:</p> |
---|
| 231 | <pre> |
---|
| 232 | include('/path/to/set/here/adodb.inc.php'); |
---|
| 233 | $conn = &ADONewConnection('mysql'); |
---|
| 234 | </pre> |
---|
| 235 | <p>Whenever you need to connect to a database, you create a Connection object |
---|
| 236 | using the <b>ADONewConnection</b>($driver) function. |
---|
| 237 | <b>NewADOConnection</b>($driver) is an alternative name for the same function.</p> |
---|
| 238 | |
---|
| 239 | <p>At this point, you are not connected to the database (no longer true if you pass in a <a href=#dsnsupport>dsn</a>). You will first need to decide |
---|
| 240 | whether to use <i>persistent</i> or <i>non-persistent</i> connections. The advantage of <i>persistent</i> |
---|
| 241 | connections is that they are faster, as the database connection is never closed (even |
---|
| 242 | when you call Close()). <i>Non-persistent </i>connections take up much fewer resources though, |
---|
| 243 | reducing the risk of your database and your web-server becoming overloaded. |
---|
| 244 | <p>For persistent connections, |
---|
| 245 | use $conn-><a href="#pconnect">PConnect()</a>, |
---|
| 246 | or $conn-><a href="#connect">Connect()</a> for non-persistent connections. |
---|
| 247 | Some database drivers also support <a href="#nconnect">NConnect()</a>, which forces |
---|
| 248 | the creation of a new connection. |
---|
| 249 | |
---|
| 250 | <a name=connection_gotcha></a> |
---|
| 251 | <p><b>Connection Gotcha</b>: If you create two connections, but both use the same userid and password, |
---|
| 252 | PHP will share the same connection. This can cause problems if the connections are meant to |
---|
| 253 | different databases. The solution is to always use different userid's for different databases, |
---|
| 254 | or use NConnect(). |
---|
| 255 | |
---|
| 256 | <a name=dsnsupport></a> |
---|
| 257 | <h3>Data Source Name (DSN) Support</h3> |
---|
| 258 | <p> Since ADOdb 4.51, you can connect to a database by passing a dsn to NewADOConnection() (or ADONewConnection, which is |
---|
| 259 | the same function). The dsn format is: |
---|
| 260 | <pre> |
---|
| 261 | $driver://$username:$password@hostname/$database?options[=value] |
---|
| 262 | </pre><p> |
---|
| 263 | NewADOConnection() calls Connect() or PConnect() internally for you. If the connection fails, false is returned. |
---|
| 264 | <pre> |
---|
| 265 | <font color=#008000># non-persistent connection</font> |
---|
| 266 | $dsn = 'mysql://root:pwd@localhost/mydb'; |
---|
| 267 | $db = NewADOConnection($dsn); |
---|
| 268 | if (!$db) die("Connection failed"); |
---|
| 269 | |
---|
| 270 | <font color=#008000># no need to call connect/pconnect!</font> |
---|
| 271 | $arr = $db->GetArray("select * from table"); |
---|
| 272 | |
---|
| 273 | <font color=#008000># persistent connection</font> |
---|
| 274 | $dsn2 = 'mysql://root:pwd@localhost/mydb?persist'; |
---|
| 275 | </pre> |
---|
| 276 | <p> |
---|
| 277 | If you have special characters such as /:? in your dsn, then you need to rawurlencode them first: |
---|
| 278 | <pre> |
---|
| 279 | $pwd = rawurlencode($pwd); |
---|
| 280 | $dsn = "mysql://root:$pwd@localhost/mydb"; |
---|
| 281 | </pre> |
---|
| 282 | <p> |
---|
| 283 | Legal options are: |
---|
| 284 | <p> |
---|
| 285 | <table align=center border=1><tr><td>For all drivers<td> |
---|
| 286 | 'persist', 'persistent', 'debug', 'fetchmode' |
---|
| 287 | <tr><td>Interbase/Firebird |
---|
| 288 | <td> |
---|
| 289 | 'dialect', |
---|
| 290 | 'charset', |
---|
| 291 | 'buffers' |
---|
| 292 | <tr><td>M'soft ADO<td> |
---|
| 293 | 'charpage' |
---|
| 294 | |
---|
| 295 | <tr><td>MySQL<td> |
---|
| 296 | 'clientflags' |
---|
| 297 | <tr><td>MySQLi<td> |
---|
| 298 | 'port', 'socket', 'clientflags' |
---|
| 299 | </table> |
---|
| 300 | <p> |
---|
| 301 | For all drivers, when the options <i>persist</i> or <i>persistent</i> are set, a persistent connection is forced. |
---|
| 302 | The <i>debug</i> option enables debugging. The <i>fetchmode</i> calls <a href=#setfetchmode>SetFetchMode()</a>. |
---|
| 303 | If no value is defined for an option, then the value is set to 1. |
---|
| 304 | <p> |
---|
| 305 | ADOdb DSN's are compatible with version 1.0 of PEAR DB's DSN format. |
---|
| 306 | <a name=connect_ex> |
---|
| 307 | <h3>Examples of Connecting to Databases</h3> |
---|
| 308 | <h4>MySQL and Most Other Database Drivers</h4> |
---|
| 309 | <p>MySQL connections are very straightforward, and the parameters are identical |
---|
| 310 | to mysql_connect:</p> |
---|
| 311 | <pre> |
---|
| 312 | $conn = &ADONewConnection('mysql'); |
---|
| 313 | $conn->PConnect('localhost','userid','password','database'); |
---|
| 314 | |
---|
| 315 | <font color=#008000># or dsn </font> |
---|
| 316 | $dsn = 'mysql://user:pwd@localhost/mydb'; |
---|
| 317 | $conn = ADONewConnection($dsn); # no need for Connect() |
---|
| 318 | |
---|
| 319 | <font color=#008000># or persistent dsn</font> |
---|
| 320 | $dsn = 'mysql://user:pwd@localhost/mydb?persist'; |
---|
| 321 | $conn = ADONewConnection($dsn); # no need for PConnect() |
---|
| 322 | |
---|
| 323 | <font color=#008000># a more complex example:</font> |
---|
| 324 | $pwd = urlencode($pwd); |
---|
| 325 | $flags = MYSQL_CLIENT_COMPRESS; |
---|
| 326 | $dsn = "mysql://user:$pwd@localhost/mydb?persist&clientflags=$flags"; |
---|
| 327 | $conn = ADONewConnection($dsn); # no need for PConnect() |
---|
| 328 | </pre> |
---|
| 329 | <p> For most drivers, you can use the standard function: Connect($server, $user, $password, $database), or |
---|
| 330 | a <a href=dsnsupport>DSN</a> since ADOdb 4.51. Exceptions to this are listed below. |
---|
| 331 | <h4>PostgreSQL</h4> |
---|
| 332 | <p>PostgreSQL accepts connections using: </p> |
---|
| 333 | <p>a. the standard connection string:</p> |
---|
| 334 | <pre> |
---|
| 335 | $conn = &ADONewConnection('postgres7'); |
---|
| 336 | $conn->PConnect('host=localhost port=5432 dbname=mary');</pre> |
---|
| 337 | <p> b. the classical 4 parameters:</p> |
---|
| 338 | <pre> |
---|
| 339 | $conn->PConnect('localhost','userid','password','database'); |
---|
| 340 | </pre> |
---|
| 341 | <p>c. dsn: |
---|
| 342 | <pre> |
---|
| 343 | $dsn = 'postgres7://user:pwd@localhost/mydb?persist'; # persist is optional |
---|
| 344 | $conn = ADONewConnection($dsn); # no need for Connect/PConnect |
---|
| 345 | </pre> |
---|
| 346 | <a name=ldap></a> |
---|
| 347 | |
---|
| 348 | <h4>LDAP</h4> |
---|
| 349 | <p>Here is an example of querying a LDAP server. Thanks to Josh Eldridge for the driver and this example: |
---|
| 350 | <pre> |
---|
| 351 | <?php |
---|
| 352 | require('/path/to/adodb.inc.php'); |
---|
| 353 | |
---|
| 354 | $host = 'ldap.baylor.edu'; |
---|
| 355 | $ldapbase = 'ou=People,o=Baylor University,c=US'; |
---|
| 356 | |
---|
| 357 | $ldap = NewADOConnection( 'ldap' ); |
---|
| 358 | $ldap->Connect( $host, $user_name='', $password='', $ldapbase ); |
---|
| 359 | |
---|
| 360 | echo "<pre>"; |
---|
| 361 | |
---|
| 362 | print_r( $ldap->ServerInfo() ); |
---|
| 363 | $ldap->SetFetchMode(ADODB_FETCH_ASSOC); |
---|
| 364 | $userName = 'eldridge'; |
---|
| 365 | $filter="(|(CN=$userName*)(sn=$userName*)(givenname=$userName*)(uid=$userName*))"; |
---|
| 366 | |
---|
| 367 | $rs = $ldap->Execute( $filter ); |
---|
| 368 | if ($rs) |
---|
| 369 | while ($arr = $rs->FetchRow()) { |
---|
| 370 | print_r($arr); |
---|
| 371 | } |
---|
| 372 | |
---|
| 373 | $rs = $ldap->Execute( $filter ); |
---|
| 374 | if ($rs) |
---|
| 375 | while (!$rs->EOF) { |
---|
| 376 | print_r($rs->fields); |
---|
| 377 | $rs->MoveNext(); |
---|
| 378 | } |
---|
| 379 | |
---|
| 380 | print_r( $ldap->GetArray( $filter ) ); |
---|
| 381 | |
---|
| 382 | print_r( $ldap->GetRow( $filter ) ); |
---|
| 383 | |
---|
| 384 | $ldap->Close(); |
---|
| 385 | echo "</pre>"; |
---|
| 386 | ?></pre> |
---|
| 387 | <h4>Interbase/Firebird</h4> |
---|
| 388 | You define the database in the $host parameter: |
---|
| 389 | <pre> |
---|
| 390 | $conn = &ADONewConnection('ibase'); |
---|
| 391 | $conn->PConnect('localhost:c:\ibase\employee.gdb','sysdba','masterkey'); |
---|
| 392 | </pre> |
---|
| 393 | <p>Or dsn: |
---|
| 394 | <pre> |
---|
| 395 | $dsn = 'firebird://user:pwd@localhost/mydb?persist&dialect=3'; # persist is optional |
---|
| 396 | $conn = ADONewConnection($dsn); # no need for Connect/PConnect |
---|
| 397 | </pre> |
---|
| 398 | <h4>SQLite</h4> |
---|
| 399 | Sqlite will create the database file if it does not exist. |
---|
| 400 | <pre> |
---|
| 401 | $conn = &ADONewConnection('sqlite'); |
---|
| 402 | $conn->PConnect('c:\path\to\sqlite.db'); # sqlite will create if does not exist |
---|
| 403 | </pre> |
---|
| 404 | <p>Or dsn: |
---|
| 405 | <pre> |
---|
| 406 | $dsn = 'sqlite://user:pwd@localhost/mydb?persist'; # persist is optional |
---|
| 407 | $conn = ADONewConnection($dsn); # no need for Connect/PConnect |
---|
| 408 | </pre> |
---|
| 409 | <h4>Oracle (oci8)</h4> |
---|
| 410 | <p>With oci8, you can connect in multiple ways. Note that oci8 works fine with |
---|
| 411 | newer versions of the Oracle, eg. 9i and 10g.</p> |
---|
| 412 | <p>a. PHP and Oracle reside on the same machine, use default SID.</p> |
---|
| 413 | <pre> $conn->Connect(false, 'scott', 'tiger');</pre> |
---|
| 414 | <p>b. TNS Name defined in tnsnames.ora (or ONAMES or HOSTNAMES), eg. 'myTNS'</p> |
---|
| 415 | <pre> $conn->PConnect(false, 'scott', 'tiger', 'myTNS');</pre> |
---|
| 416 | <p>or</p> |
---|
| 417 | <pre> $conn->PConnect('myTNS', 'scott', 'tiger');</pre> |
---|
| 418 | <p>c. Host Address and SID</p> |
---|
| 419 | <pre> $conn->Connect('192.168.0.1', 'scott', 'tiger', 'SID');</pre> |
---|
| 420 | <p>d. Host Address and Service Name</p> |
---|
| 421 | <pre> $conn->Connect('192.168.0.1', 'scott', 'tiger', 'servicename');</pre> |
---|
| 422 | <p>e. Oracle connection string: |
---|
| 423 | <pre> $cstr = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$host)(PORT=$port)) |
---|
| 424 | (CONNECT_DATA=(SID=$sid)))"; |
---|
| 425 | $conn->Connect($cstr, 'scott', 'tiger'); |
---|
| 426 | </pre> |
---|
| 427 | <p>f. ADOdb dsn: |
---|
| 428 | <pre> |
---|
| 429 | $dsn = 'oci8://user:pwd@tnsname?persist'; # persist is optional |
---|
| 430 | $conn = ADONewConnection($dsn); # no need for Connect/PConnect |
---|
| 431 | |
---|
| 432 | $dsn = 'oci8://user:pwd@host/sid'; |
---|
| 433 | $conn = ADONewConnection($dsn); |
---|
| 434 | |
---|
| 435 | $dsn = 'oci8://user:pwd@/'; # oracle on local machine |
---|
| 436 | $conn = ADONewConnection($dsn); |
---|
| 437 | </pre> |
---|
| 438 | <a name=dsnless></a> |
---|
| 439 | <h4>DSN-less ODBC (access and mssql examples)</h4> |
---|
| 440 | <p>ODBC DSN's can be created in the ODBC control panel, or you can use a DSN-less |
---|
| 441 | connection.To use DSN-less connections with ODBC you need PHP 4.3 or later. |
---|
| 442 | </p> |
---|
| 443 | <p>For Microsoft Access:</p> |
---|
| 444 | <pre> |
---|
| 445 | $db =& ADONewConnection('access'); |
---|
| 446 | $dsn = <strong>"Driver={Microsoft Access Driver (*.mdb)};Dbq=d:\\northwind.mdb;Uid=Admin;Pwd=;";</strong> |
---|
| 447 | $db->Connect($dsn); |
---|
| 448 | </pre> |
---|
| 449 | For Microsoft SQL Server: |
---|
| 450 | <pre> |
---|
| 451 | $db =& ADONewConnection('odbc_mssql'); |
---|
| 452 | $dsn = <strong>"Driver={SQL Server};Server=localhost;Database=northwind;"</strong>; |
---|
| 453 | $db->Connect($dsn,'userid','password'); |
---|
| 454 | </pre> |
---|
| 455 | or if you prefer to use the mssql extension (which is limited to mssql 6.5 functionality): |
---|
| 456 | <pre> |
---|
| 457 | $db =& ADONewConnection('mssql'); |
---|
| 458 | $db->Execute("localhost', 'userid', 'password', 'northwind'); |
---|
| 459 | </pre> |
---|
| 460 | <b>DSN-less Connections with ADO</b><br> |
---|
| 461 | If you are using versions of PHP earlier than PHP 4.3.0, DSN-less connections |
---|
| 462 | only work with Microsoft's ADO, which is Microsoft's COM based API. An example |
---|
| 463 | using the ADOdb library and Microsoft's ADO: |
---|
| 464 | <pre> |
---|
| 465 | <?php |
---|
| 466 | include('adodb.inc.php'); |
---|
| 467 | $db = &ADONewConnection("ado_mssql"); |
---|
| 468 | print "<h1>Connecting DSN-less $db->databaseType...</h1>"; |
---|
| 469 | |
---|
| 470 | <b>$myDSN="PROVIDER=MSDASQL;DRIVER={SQL Server};" |
---|
| 471 | . "SERVER=flipper;DATABASE=ai;UID=sa;PWD=;" ;</b> |
---|
| 472 | $db->Connect($myDSN); |
---|
| 473 | |
---|
| 474 | $rs = $db->Execute("select * from table"); |
---|
| 475 | $arr = $rs->GetArray(); |
---|
| 476 | print_r($arr); |
---|
| 477 | ?> |
---|
| 478 | </pre><a name=speed></a> |
---|
| 479 | <h2>High Speed ADOdb - tuning tips</h2> |
---|
| 480 | <p>ADOdb is a big class library, yet it <a href=http://phplens.com/lens/adodb/>consistently beats</a> all other PHP class |
---|
| 481 | libraries in performance. This is because it is designed in a layered fashion, |
---|
| 482 | like an onion, with the fastest functions in the innermost layer. Stick to the |
---|
| 483 | following functions for best performance:</p> |
---|
| 484 | <table width="40%" border="1" align="center"> |
---|
| 485 | <tr> |
---|
| 486 | <td><div align="center"><b>Innermost Layer</b></div></td> |
---|
| 487 | </tr> |
---|
| 488 | <tr> |
---|
| 489 | <td><p align="center">Connect, PConnect, NConnect<br> |
---|
| 490 | Execute, CacheExecute<br> |
---|
| 491 | SelectLimit, CacheSelectLimit<br> |
---|
| 492 | MoveNext, Close <br> |
---|
| 493 | qstr, Affected_Rows, Insert_ID</p></td> |
---|
| 494 | </tr> |
---|
| 495 | </table> |
---|
| 496 | <p>The fastest way to access the field data is by accessing the array $recordset->fields |
---|
| 497 | directly. Also set the global variables <a href="#adodb_fetch_mode">$ADODB_FETCH_MODE</a> |
---|
| 498 | = ADODB_FETCH_NUM, and (for oci8, ibase/firebird and odbc) <a href="#adodb_countrecs">$ADODB_COUNTRECS</a> = false |
---|
| 499 | before you connect to your database.</p> |
---|
| 500 | <p>Consider using bind parameters if your database supports it, as it improves |
---|
| 501 | query plan reuse. Use ADOdb's performance tuning system to identify bottlenecks |
---|
| 502 | quickly. At the time of writing (Dec 2003), this means oci8 and odbc drivers.</p> |
---|
| 503 | <p>Lastly make sure you have a PHP accelerator cache installed such as APC, Turck |
---|
| 504 | MMCache, Zend Accelerator or ionCube.</p> |
---|
| 505 | <p><b>Advanced Tips</b> |
---|
| 506 | <p>If you have the <a href=http://adodb.sourceforge.net/#extension>ADOdb C extension</a> installed, |
---|
| 507 | you can replace your calls to $rs->MoveNext() with adodb_movenext($rs). |
---|
| 508 | This doubles the speed of this operation. For retrieving entire recordsets at once, |
---|
| 509 | use GetArray(), which uses the high speed extension function adodb_getall($rs) internally. |
---|
| 510 | <p>Execute() is the default way to run queries. You can use the low-level functions _Execute() and _query() |
---|
| 511 | to reduce query overhead. Both these functions share the same parameters as Execute(). |
---|
| 512 | <p>If you do not have any bind parameters or your database supports binding (without emulation), |
---|
| 513 | then you can call _Execute() directly. Calling this function bypasses bind emulation. Debugging is still supported in _Execute(). |
---|
| 514 | <p>If you do not require debugging facilities nor emulated binding, and do not require a recordset to be returned, then you can call _query. This is great for inserts, updates and deletes. Calling this function |
---|
| 515 | bypasses emulated binding, debugging, and recordset handling. Either the resultid, true or false are returned by _query(). |
---|
| 516 | <p>For Informix, you can disable scrollable cursors with $db->cursorType = 0. |
---|
| 517 | <p><a name=hack></a> </p> |
---|
| 518 | <h2>Hacking ADOdb Safely</h2> |
---|
| 519 | <p>You might want to modify ADOdb for your own purposes. Luckily you can |
---|
| 520 | still maintain backward compatibility by sub-classing ADOdb and using the $ADODB_NEWCONNECTION |
---|
| 521 | variable. $ADODB_NEWCONNECTION allows you to override the behaviour of ADONewConnection(). |
---|
| 522 | ADOConnection() checks for this variable and will call |
---|
| 523 | the function-name stored in this variable if it is defined. |
---|
| 524 | <p>In the following example, new functionality for the connection object |
---|
| 525 | is placed in the <i>hack_mysql</i> and <i>hack_postgres7</i> classes. The recordset class naming convention |
---|
| 526 | can be controlled using $rsPrefix. Here we set it to 'hack_rs_', which will make ADOdb use |
---|
| 527 | <i>hack_rs_mysql</i> and <i>hack_rs_postgres7</i> as the recordset classes. |
---|
| 528 | |
---|
| 529 | |
---|
| 530 | <pre> |
---|
| 531 | class hack_mysql extends adodb_mysql { |
---|
| 532 | var $rsPrefix = 'hack_rs_'; |
---|
| 533 | /* Your mods here */ |
---|
| 534 | } |
---|
| 535 | |
---|
| 536 | class hack_rs_mysql extends ADORecordSet_mysql { |
---|
| 537 | /* Your mods here */ |
---|
| 538 | } |
---|
| 539 | |
---|
| 540 | class hack_postgres7 extends adodb_postgres7 { |
---|
| 541 | var $rsPrefix = 'hack_rs_'; |
---|
| 542 | /* Your mods here */ |
---|
| 543 | } |
---|
| 544 | |
---|
| 545 | class hack_rs_postgres7 extends ADORecordSet_postgres7 { |
---|
| 546 | /* Your mods here */ |
---|
| 547 | } |
---|
| 548 | |
---|
| 549 | $ADODB_NEWCONNECTION = 'hack_factory'; |
---|
| 550 | |
---|
| 551 | function& hack_factory($driver) |
---|
| 552 | { |
---|
| 553 | if ($driver !== 'mysql' && $driver !== 'postgres7') return false; |
---|
| 554 | |
---|
| 555 | $driver = 'hack_'.$driver; |
---|
| 556 | $obj = new $driver(); |
---|
| 557 | return $obj; |
---|
| 558 | } |
---|
| 559 | |
---|
| 560 | include_once('adodb.inc.php'); |
---|
| 561 | </pre> |
---|
| 562 | <p><p>Don't forget to call the constructor of the parent class in your constructor. If you want to use the default ADOdb drivers return false in the above hack_factory() function. |
---|
| 563 | <a name="php5"></a> |
---|
| 564 | <h2>PHP5 Features</h2> |
---|
| 565 | ADOdb 4.02 or later will transparently determine which version of PHP you are using. |
---|
| 566 | If PHP5 is detected, the following features become available: |
---|
| 567 | <ul> |
---|
| 568 | <a name="php5iterators"></a> |
---|
| 569 | <li><b>Foreach iterators</b>: This is a very natural way of going through a recordset: |
---|
| 570 | <pre> |
---|
| 571 | $ADODB_FETCH_MODE = ADODB_FETCH_NUM; |
---|
| 572 | $rs = $db->Execute($sql); |
---|
| 573 | foreach($rs as $k => $row) { |
---|
| 574 | echo "r1=".$row[0]." r2=".$row[1]."<br>"; |
---|
| 575 | } |
---|
| 576 | </pre> |
---|
| 577 | <p> |
---|
| 578 | <a name="php5exceptions"></a> |
---|
| 579 | <li><b>Exceptions</b>: Just include <i>adodb-exceptions.inc.php</i> and you can now |
---|
| 580 | catch exceptions on errors as they occur. |
---|
| 581 | <pre> |
---|
| 582 | <b>include("../adodb-exceptions.inc.php");</b> |
---|
| 583 | include("../adodb.inc.php"); |
---|
| 584 | try { |
---|
| 585 | $db = NewADOConnection("oci8"); |
---|
| 586 | $db->Connect('','scott','bad-password'); |
---|
| 587 | } catch (exception $e) { |
---|
| 588 | var_dump($e); |
---|
| 589 | } |
---|
| 590 | </pre> |
---|
| 591 | <p>Note that reaching EOF is <b>not</b> considered an error nor an exception. |
---|
| 592 | If you want to use the default ADOdb drivers return false. |
---|
| 593 | </ul> |
---|
| 594 | <h3><a name="drivers"></a>Databases Supported</h3> |
---|
| 595 | The <i>name</i> below is the value you pass to NewADOConnection($name) to create a connection object for that database. |
---|
| 596 | <p> |
---|
| 597 | <table width="100%" border="1"> |
---|
| 598 | <tr valign="top"> |
---|
| 599 | <td><b>Name</b></td> |
---|
| 600 | <td><b>Tested</b></td> |
---|
| 601 | <td><b>Database</b></td> |
---|
| 602 | <td><b><font size="2">RecordCount() usable</font></b></td> |
---|
| 603 | <td><b>Prerequisites</b></td> |
---|
| 604 | <td><b>Operating Systems</b></td> |
---|
| 605 | </tr> |
---|
| 606 | <tr valign="top"> |
---|
| 607 | <td><b><font size="2">access</font></b></td> |
---|
| 608 | <td><font size="2">B</font></td> |
---|
| 609 | <td><font size="2">Microsoft Access/Jet. You need to create an ODBC DSN.</font></td> |
---|
| 610 | <td><font size="2">Y/N</font></td> |
---|
| 611 | <td><font size="2">ODBC </font></td> |
---|
| 612 | <td><font size="2">Windows only</font></td> |
---|
| 613 | </tr> |
---|
| 614 | <tr valign="top"> |
---|
| 615 | <td><b><font size="2">ado</font></b></td> |
---|
| 616 | <td><font size="2">B</font></td> |
---|
| 617 | <td><p><font size="2">Generic ADO, not tuned for specific databases. Allows |
---|
| 618 | DSN-less connections. For best performance, use an OLEDB provider. This |
---|
| 619 | is the base class for all ado drivers.</font></p> |
---|
| 620 | <p><font size="2">You can set $db->codePage before connecting.</font></p></td> |
---|
| 621 | <td><font size="2">? depends on database</font></td> |
---|
| 622 | <td><font size="2">ADO or OLEDB provider</font></td> |
---|
| 623 | <td><font size="2">Windows only</font></td> |
---|
| 624 | </tr> |
---|
| 625 | <tr valign="top"> |
---|
| 626 | <td><b><font size="2">ado_access</font></b></td> |
---|
| 627 | <td><font size="2">B</font></td> |
---|
| 628 | <td><font size="2">Microsoft Access/Jet using ADO. Allows DSN-less connections. |
---|
| 629 | For best performance, use an OLEDB provider.</font></td> |
---|
| 630 | <td><font size="2">Y/N</font></td> |
---|
| 631 | <td><font size="2">ADO or OLEDB provider</font></td> |
---|
| 632 | <td><font size="2">Windows only</font></td> |
---|
| 633 | </tr> |
---|
| 634 | <tr valign="top"> |
---|
| 635 | <td><b><font size="2">ado_mssql</font></b></td> |
---|
| 636 | <td><font size="2">B</font></td> |
---|
| 637 | <td><font size="2">Microsoft SQL Server using ADO. Allows DSN-less connections. |
---|
| 638 | For best performance, use an OLEDB provider.</font></td> |
---|
| 639 | <td><font size="2">Y/N</font></td> |
---|
| 640 | <td><font size="2">ADO or OLEDB provider</font></td> |
---|
| 641 | <td><font size="2">Windows only</font></td> |
---|
| 642 | </tr> |
---|
| 643 | <tr valign="top"> |
---|
| 644 | <td height="54"><b><font size="2">db2</font></b></td> |
---|
| 645 | <td height="54"><font size="2">A</font></td> |
---|
| 646 | <td height="54"><font size="2">DB2. Should work reliably as based on ODBC |
---|
| 647 | driver.</font></td> |
---|
| 648 | <td height="54"><font size="2">Y/N</font></td> |
---|
| 649 | <td height="54"><font size="2">DB2 CLI/ODBC interface</font></td> |
---|
| 650 | <td height="54"> <p><font size="2">Unix and Windows. <a href="http://www.faqts.com/knowledge_base/view.phtml/aid/6283/fid/14">Unix |
---|
| 651 | install hints</a>. I have had reports that the $host and $database params have to be reversed in Connect() when using the CLI interface.</font></p></td> |
---|
| 652 | </tr> |
---|
| 653 | <tr valign="top"> |
---|
| 654 | <td><b><font size="2">vfp</font></b></td> |
---|
| 655 | <td><font size="2">A</font></td> |
---|
| 656 | <td><font size="2">Microsoft Visual FoxPro. You need to create an ODBC DSN.</font></td> |
---|
| 657 | <td><font size="2">Y/N</font></td> |
---|
| 658 | <td><font size="2">ODBC</font></td> |
---|
| 659 | <td><font size="2">Windows only</font></td> |
---|
| 660 | </tr> |
---|
| 661 | <tr valign="top"> |
---|
| 662 | <td><b><font size="2">fbsql</font></b></td> |
---|
| 663 | <td><font size="2">C</font></td> |
---|
| 664 | <td><font size="2">FrontBase. </font></td> |
---|
| 665 | <td><font size="2">Y</font></td> |
---|
| 666 | <td><font size="2">?</font></td> |
---|
| 667 | <td> <p><font size="2">Unix and Windows</font></p></td> |
---|
| 668 | </tr> |
---|
| 669 | <tr valign="top"> |
---|
| 670 | <td><b><font size="2">ibase</font></b></td> |
---|
| 671 | <td><font size="2">B</font></td> |
---|
| 672 | <td><font size="2">Interbase 6 or earlier. Some users report you might need |
---|
| 673 | to use this<br> |
---|
| 674 | $db->PConnect('localhost:c:/ibase/employee.gdb', "sysdba", "masterkey") |
---|
| 675 | to connect. Lacks Affected_Rows currently.<br> |
---|
| 676 | <br> |
---|
| 677 | You can set $db->dialect, $db->buffers and $db->charSet before connecting.</font></td> |
---|
| 678 | <td><font size="2">Y/N</font></td> |
---|
| 679 | <td><font size="2">Interbase client</font></td> |
---|
| 680 | <td><font size="2">Unix and Windows</font></td> |
---|
| 681 | </tr> |
---|
| 682 | <tr valign="top"> |
---|
| 683 | <td><b><i><font size="2">firebird</font></i></b></td> |
---|
| 684 | <td><font size="2">C</font></td> |
---|
| 685 | <td><font size="2">Firebird version of interbase.</font></td> |
---|
| 686 | <td><font size="2">Y/N</font></td> |
---|
| 687 | <td><font size="2">Interbase client</font></td> |
---|
| 688 | <td><font size="2">Unix and Windows</font></td> |
---|
| 689 | </tr> |
---|
| 690 | <tr valign="top"> |
---|
| 691 | <td><b><i><font size="2">borland_ibase</font></i></b></td> |
---|
| 692 | <td><font size="2">C</font></td> |
---|
| 693 | <td><font size="2">Borland version of Interbase 6.5 or later. Very sad that |
---|
| 694 | the forks differ.</font></td> |
---|
| 695 | <td><font size="2">Y/N</font></td> |
---|
| 696 | <td><font size="2">Interbase client</font></td> |
---|
| 697 | <td><font size="2">Unix and Windows</font></td> |
---|
| 698 | </tr> |
---|
| 699 | |
---|
| 700 | <tr valign="top"> |
---|
| 701 | <td><b><font size="2">informix</font></b></td> |
---|
| 702 | <td><font size="2">C</font></td> |
---|
| 703 | <td><font size="2">Generic informix driver. Use this if you are using Informix 7.3 or later.</font></td> |
---|
| 704 | <td><font size="2">Y/N</font></td> |
---|
| 705 | <td><font size="2">Informix client</font></td> |
---|
| 706 | <td><font size="2">Unix and Windows</font></td> |
---|
| 707 | </tr> |
---|
| 708 | <tr valign="top"> |
---|
| 709 | <td><b><font size="2">informix72</font></b></td> |
---|
| 710 | <td><font size="2">C</font></td> |
---|
| 711 | <td><font size="2"> Informix databases before Informix 7.3 that do no support |
---|
| 712 | SELECT FIRST.</font></td> |
---|
| 713 | <td><font size="2">Y/N</font></td> |
---|
| 714 | <td><font size="2">Informix client</font></td> |
---|
| 715 | <td><font size="2">Unix and Windows</font></td> |
---|
| 716 | </tr> |
---|
| 717 | <tr valign="top"> |
---|
| 718 | <td><b><font size="2">ldap</font></b></td> |
---|
| 719 | <td><font size="2">C</font></td> |
---|
| 720 | <td><font size="2">LDAP driver. See this example for usage information.</font></td> |
---|
| 721 | <td> </td> |
---|
| 722 | <td><font size="2">LDAP extension</font></td> |
---|
| 723 | <td><font size="2">?</font></td> |
---|
| 724 | </tr> |
---|
| 725 | <tr valign="top"> |
---|
| 726 | <td height="73"><b><font size="2">mssql</font></b></td> |
---|
| 727 | <td height="73"><font size="2">A</font></td> |
---|
| 728 | <td height="73"> <p><font size="2">Microsoft SQL Server 7 and later. Works |
---|
| 729 | with Microsoft SQL Server 2000 also. Note that date formating is problematic |
---|
| 730 | with this driver. For example, the PHP mssql extension does not return |
---|
| 731 | the seconds for datetime!</font></p></td> |
---|
| 732 | <td height="73"><font size="2">Y/N</font></td> |
---|
| 733 | <td height="73"><font size="2">Mssql client</font></td> |
---|
| 734 | <td height="73"> <p><font size="2">Unix and Windows. <br> |
---|
| 735 | <a href="http://phpbuilder.com/columns/alberto20000919.php3">Unix install |
---|
| 736 | howto</a> and <a href=http://linuxjournal.com/article.php?sid=6636&mode=thread&order=0>another |
---|
| 737 | one</a>. </font></p></td> |
---|
| 738 | </tr> |
---|
| 739 | <tr valign="top"> |
---|
| 740 | <td height="73"><b><font size="2">mssqlpo</font></b></td> |
---|
| 741 | <td height="73"><font size="2">A</font></td> |
---|
| 742 | <td height="73"> <p><font size="2">Portable mssql driver. Identical to above |
---|
| 743 | mssql driver, except that '||', the concatenation operator, is converted |
---|
| 744 | to '+'. Useful for porting scripts from most other sql variants that use |
---|
| 745 | ||.</font></p></td> |
---|
| 746 | <td height="73"><font size="2">Y/N</font></td> |
---|
| 747 | <td height="73"><font size="2">Mssql client</font></td> |
---|
| 748 | <td height="73"> <p><font size="2">Unix and Windows. <a href="http://phpbuilder.com/columns/alberto20000919.php3"><br> |
---|
| 749 | Unix install howto</a>.</font></p></td> |
---|
| 750 | </tr> |
---|
| 751 | <tr valign="top"> |
---|
| 752 | <td><b><font size="2">mysql</font></b></td> |
---|
| 753 | <td><font size="2">A</font></td> |
---|
| 754 | <td><font size="2">MySQL without transaction support. You can also set $db->clientFlags |
---|
| 755 | before connecting.</font></td> |
---|
| 756 | <td><font size="2">Y</font></td> |
---|
| 757 | <td><font size="2">MySQL client</font></td> |
---|
| 758 | <td><font size="2">Unix and Windows</font></td> |
---|
| 759 | </tr> |
---|
| 760 | <tr valign="top"> |
---|
| 761 | <td><font size="2"><b>mysqlt</b> or <b>maxsql</b></font></td> |
---|
| 762 | <td><font size="2">A</font></td> |
---|
| 763 | <td> <p><font size="2">MySQL with transaction support. We recommend using |
---|
| 764 | || as the concat operator for best portability. This can be done by running |
---|
| 765 | MySQL using: <br> |
---|
| 766 | <i>mysqld --ansi</i> or <i>mysqld --sql-mode=PIPES_AS_CONCAT</i></font></p></td> |
---|
| 767 | <td><font size="2">Y/N</font></td> |
---|
| 768 | <td><font size="2">MySQL client</font></td> |
---|
| 769 | <td><font size="2">Unix and Windows</font></td> |
---|
| 770 | </tr> |
---|
| 771 | <tr valign="top"> |
---|
| 772 | <td><b><font size="2">oci8</font></b></td> |
---|
| 773 | <td><font size="2">A</font></td> |
---|
| 774 | <td><font size="2">Oracle 8/9. Has more functionality than <i>oracle</i> driver |
---|
| 775 | (eg. Affected_Rows). You might have to putenv('ORACLE_HOME=...') before |
---|
| 776 | Connect/PConnect. </font> <p><font size="2"> There are 2 ways of connecting |
---|
| 777 | - with server IP and service name: <br> |
---|
| 778 | <i>PConnect('serverip:1521','scott','tiger','service'</i>)<br> |
---|
| 779 | or using an entry in TNSNAMES.ORA or ONAMES or HOSTNAMES: <br> |
---|
| 780 | <i>PConnect(false, 'scott', 'tiger', $oraname)</i>. </font> |
---|
| 781 | <p><font size="2">Since 2.31, we support Oracle REF cursor variables directly |
---|
| 782 | (see <a href="#executecursor">ExecuteCursor</a>).</font> </td> |
---|
| 783 | <td><font size="2">Y/N</font></td> |
---|
| 784 | <td><font size="2">Oracle client</font></td> |
---|
| 785 | <td><font size="2">Unix and Windows</font></td> |
---|
| 786 | </tr> |
---|
| 787 | <tr valign="top"> |
---|
| 788 | <td><b><font size="2">oci805</font></b></td> |
---|
| 789 | <td><font size="2">C</font></td> |
---|
| 790 | <td><font size="2">Supports reduced Oracle functionality for Oracle 8.0.5. |
---|
| 791 | SelectLimit is not as efficient as in the oci8 or oci8po drivers.</font></td> |
---|
| 792 | <td><font size="2">Y/N</font></td> |
---|
| 793 | <td><font size="2">Oracle client</font></td> |
---|
| 794 | <td><font size="2">Unix and Windows</font></td> |
---|
| 795 | </tr> |
---|
| 796 | <tr valign="top"> |
---|
| 797 | <td><b><font size="2">oci8po</font></b></td> |
---|
| 798 | <td><font size="2">A</font></td> |
---|
| 799 | <td><font size="2">Oracle 8/9 portable driver. This is nearly identical with |
---|
| 800 | the oci8 driver except (a) bind variables in Prepare() use the ? convention, |
---|
| 801 | instead of :bindvar, (b) field names use the more common PHP convention |
---|
| 802 | of lowercase names. </font> <p><font size="2">Use this driver if porting |
---|
| 803 | from other databases is important. Otherwise the oci8 driver offers better |
---|
| 804 | performance. </font> </td> |
---|
| 805 | <td><font size="2">Y/N</font></td> |
---|
| 806 | <td><font size="2">Oracle client</font></td> |
---|
| 807 | <td><font size="2">Unix and Windows</font></td> |
---|
| 808 | </tr> |
---|
| 809 | <tr valign="top"> |
---|
| 810 | <td><b><font size="2">odbc</font></b></td> |
---|
| 811 | <td><font size="2">A</font></td> |
---|
| 812 | <td><font size="2">Generic ODBC, not tuned for specific databases. To connect, |
---|
| 813 | use <br> |
---|
| 814 | PConnect('DSN','user','pwd'). This is the base class for all odbc derived |
---|
| 815 | drivers.</font></td> |
---|
| 816 | <td><font size="2">? depends on database</font></td> |
---|
| 817 | <td><font size="2">ODBC</font></td> |
---|
| 818 | <td><font size="2">Unix and Windows. <a href="http://phpbuilder.com/columns/alberto20000919.php3?page=4">Unix |
---|
| 819 | hints.</a></font></td> |
---|
| 820 | </tr> |
---|
| 821 | <tr valign="top"> |
---|
| 822 | <td><b><font size="2">odbc_mssql</font></b></td> |
---|
| 823 | <td><font size="2">C</font></td> |
---|
| 824 | <td><font size="2">Uses ODBC to connect to MSSQL</font></td> |
---|
| 825 | <td><font size="2">Y/N</font></td> |
---|
| 826 | <td><font size="2">ODBC</font></td> |
---|
| 827 | <td><font size="2">Unix and Windows. </font></td> |
---|
| 828 | </tr> |
---|
| 829 | <tr valign="top"> |
---|
| 830 | <td><b><font size="2">odbc_oracle</font></b></td> |
---|
| 831 | <td><font size="2">C</font></td> |
---|
| 832 | <td><font size="2">Uses ODBC to connect to Oracle</font></td> |
---|
| 833 | <td><font size="2">Y/N</font></td> |
---|
| 834 | <td><font size="2">ODBC</font></td> |
---|
| 835 | <td><font size="2">Unix and Windows. </font></td> |
---|
| 836 | </tr> |
---|
| 837 | |
---|
| 838 | <tr valign="top"> |
---|
| 839 | <td><b><font size="2">odbtp</font></b></td> |
---|
| 840 | <td><font size="2">C</font></td> |
---|
| 841 | <td><font size="2">Generic odbtp driver. <a href=http://odbtp.sourceforge.net/>Odbtp</a> is a software for |
---|
| 842 | accessing Windows ODBC data sources from other operating systems.</font></td> |
---|
| 843 | <td><font size="2">Y/N</font></td> |
---|
| 844 | <td><font size="2">odbtp</font></td> |
---|
| 845 | <td><font size="2">Unix and Windows</font></td> |
---|
| 846 | </tr> |
---|
| 847 | <tr valign="top"> |
---|
| 848 | <td><b><font size="2">odbtp_unicode</font></b></td> |
---|
| 849 | <td><font size="2">C</font></td> |
---|
| 850 | <td><font size="2">Odtbp with unicode support</font></td> |
---|
| 851 | <td><font size="2">Y/N</font></td> |
---|
| 852 | <td><font size="2">odbtp</font></td> |
---|
| 853 | <td><font size="2">Unix and Windows</font></td> |
---|
| 854 | </tr> |
---|
| 855 | <tr valign="top"> |
---|
| 856 | <td height="34"><b><font size="2">oracle</font></b></td> |
---|
| 857 | <td height="34"><font size="2">C</font></td> |
---|
| 858 | <td height="34"><font size="2">Implements old Oracle 7 client API. Use oci8 |
---|
| 859 | driver if possible for better performance.</font></td> |
---|
| 860 | <td height="34"><font size="2">Y/N</font></td> |
---|
| 861 | <td height="34"><font size="2">Oracle client</font></td> |
---|
| 862 | <td height="34"><font size="2">Unix and Windows</font></td> |
---|
| 863 | </tr> |
---|
| 864 | <tr valign="top"> |
---|
| 865 | <td height="34"><b><font size="2">netezza</font></b></td> |
---|
| 866 | <td height="34"><font size="2">C</font></td> |
---|
| 867 | <td height="34"><font size="2">Netezza driver. Netezza is based on postgres code-base.</font></td> |
---|
| 868 | <td height="34"><font size="2">Y</font></td> |
---|
| 869 | <td height="34"><font size="2">?</font></td> |
---|
| 870 | <td height="34"><font size="2">?</font></td> |
---|
| 871 | </tr> |
---|
| 872 | <tr valign="top"> |
---|
| 873 | <td><b><font size="2">postgres</font></b></td> |
---|
| 874 | <td><font size="2">A</font></td> |
---|
| 875 | <td><font size="2">Generic PostgreSQL driver. Currently identical to postgres7 |
---|
| 876 | driver. </font></td> |
---|
| 877 | <td><font size="2">Y</font></td> |
---|
| 878 | <td><font size="2">PostgreSQL client</font></td> |
---|
| 879 | <td><font size="2">Unix and Windows. </font></td> |
---|
| 880 | </tr> |
---|
| 881 | <tr valign="top"> |
---|
| 882 | <td><b><font size="2">postgres64</font></b></td> |
---|
| 883 | <td><font size="2">A</font></td> |
---|
| 884 | <td><font size="2">For PostgreSQL 6.4 and earlier which does not support LIMIT |
---|
| 885 | internally.</font></td> |
---|
| 886 | <td><font size="2">Y</font></td> |
---|
| 887 | <td><font size="2">PostgreSQL client</font></td> |
---|
| 888 | <td><font size="2">Unix and Windows. </font></td> |
---|
| 889 | </tr> |
---|
| 890 | <tr valign="top"> |
---|
| 891 | <td><b><font size="2">postgres7</font></b></td> |
---|
| 892 | <td><font size="2">A</font></td> |
---|
| 893 | <td><font size="2">PostgreSQL which supports LIMIT and other version 7 functionality.</font></td> |
---|
| 894 | <td><font size="2">Y</font></td> |
---|
| 895 | <td><font size="2">PostgreSQL client</font></td> |
---|
| 896 | <td><font size="2">Unix and Windows. </font></td> |
---|
| 897 | </tr> |
---|
| 898 | <tr valign="top"> |
---|
| 899 | <td><b><font size="2">sapdb</font></b></td> |
---|
| 900 | <td><font size="2">C</font></td> |
---|
| 901 | <td><font size="2">SAP DB. Should work reliably as based on ODBC driver.</font></td> |
---|
| 902 | <td><font size="2">Y/N</font></td> |
---|
| 903 | <td><font size="2">SAP ODBC client</font></td> |
---|
| 904 | <td> <p><font size="2">?</font></p></td> |
---|
| 905 | </tr> |
---|
| 906 | <tr valign="top"> |
---|
| 907 | <td><b><font size="2">sqlanywhere</font></b></td> |
---|
| 908 | <td><font size="2">C</font></td> |
---|
| 909 | <td><font size="2">Sybase SQL Anywhere. Should work reliably as based on ODBC |
---|
| 910 | driver.</font></td> |
---|
| 911 | <td><font size="2">Y/N</font></td> |
---|
| 912 | <td><font size="2">SQL Anywhere ODBC client</font></td> |
---|
| 913 | <td> <p><font size="2">?</font></p></td> |
---|
| 914 | </tr> |
---|
| 915 | <tr valign="top"> |
---|
| 916 | <td height="54"><b><font size="2">sqlite</font></b></td> |
---|
| 917 | <td height="54"><font size="2">B</font></td> |
---|
| 918 | <td height="54"><font size="2">SQLite.</font></td> |
---|
| 919 | <td height="54"><font size="2">Y</font></td> |
---|
| 920 | <td height="54"><font size="2">-</font></td> |
---|
| 921 | <td height="54"> <p><font size="2">Unix and Windows.</font></p></td> |
---|
| 922 | </tr> |
---|
| 923 | <tr valign="top"> |
---|
| 924 | <td height="54"><b><font size="2">sqlitepo</font></b></td> |
---|
| 925 | <td height="54"><font size="2">B</font></td> |
---|
| 926 | <td height="54"><font size="2">Portable SQLite driver. This is because assoc mode does not work like other drivers in sqlite. |
---|
| 927 | Namely, when selecting (joining) multiple tables, the table |
---|
| 928 | names are included in the assoc keys in the "sqlite" driver.<p> |
---|
| 929 | In "sqlitepo" driver, the table names are stripped from the returned column names. |
---|
| 930 | When this results in a conflict, the first field get preference. |
---|
| 931 | </font></td> |
---|
| 932 | <td height="54"><font size="2">Y</font></td> |
---|
| 933 | <td height="54"><font size="2">-</font></td> |
---|
| 934 | <td height="54"> <p><font size="2">Unix and Windows.</font></p></td> |
---|
| 935 | </tr> |
---|
| 936 | |
---|
| 937 | |
---|
| 938 | <tr valign="top"> |
---|
| 939 | <td><b><font size="2">sybase</font></b></td> |
---|
| 940 | <td><font size="2">C</font></td> |
---|
| 941 | <td><font size="2">Sybase. </font></td> |
---|
| 942 | <td><font size="2">Y/N</font></td> |
---|
| 943 | <td><font size="2">Sybase client</font></td> |
---|
| 944 | <td> <p><font size="2">Unix and Windows.</font></p></td> |
---|
| 945 | </tr> |
---|
| 946 | <p> |
---|
| 947 | </table> |
---|
| 948 | |
---|
| 949 | <p>The "Tested" column indicates how extensively the code has been tested |
---|
| 950 | and used. <br> |
---|
| 951 | A = well tested and used by many people<br> |
---|
| 952 | B = tested and usable, but some features might not be implemented<br> |
---|
| 953 | C = user contributed or experimental driver. Might not fully support all of |
---|
| 954 | the latest features of ADOdb. </p> |
---|
| 955 | <p>The column "RecordCount() usable" indicates whether RecordCount() |
---|
| 956 | return the number of rows, or returns -1 when a SELECT statement is executed. |
---|
| 957 | If this column displays Y/N then the RecordCount() is emulated when the global |
---|
| 958 | variable $ADODB_COUNTRECS=true (this is the default). Note that for large recordsets, |
---|
| 959 | it might be better to disable RecordCount() emulation because substantial amounts |
---|
| 960 | of memory are required to cache the recordset for counting. Also there is a |
---|
| 961 | speed penalty of 40-50% if emulation is required. This is emulated in most databases |
---|
| 962 | except for PostgreSQL and MySQL. This variable is checked every time a query |
---|
| 963 | is executed, so you can selectively choose which recordsets to count.</p> |
---|
| 964 | <p> |
---|
| 965 | <hr> |
---|
| 966 | <h1>Tutorials<a name="quickstart"></a></h1> |
---|
| 967 | <h3>Example 1: Select Statement<a name="ex1"></a></h3> |
---|
| 968 | <p>Task: Connect to the Access Northwind DSN, display the first 2 columns of each |
---|
| 969 | row.</p> |
---|
| 970 | <p>In this example, we create a ADOConnection object, which represents the connection |
---|
| 971 | to the database. The connection is initiated with <a href="#pconnect"><font face="Courier New, Courier, mono">PConnect</font></a>, |
---|
| 972 | which is a persistent connection. Whenever we want to query the database, we |
---|
| 973 | call the <font face="Courier New, Courier, mono">ADOConnection.<a href="#execute">Execute</a>()</font> |
---|
| 974 | function. This returns an ADORecordSet object which is actually a cursor that |
---|
| 975 | holds the current row in the array <font face="Courier New, Courier, mono">fields[]</font>. |
---|
| 976 | We use <font face="Courier New, Courier, mono"><a href="#movenext">MoveNext</a>()</font> |
---|
| 977 | to move from row to row.</p> |
---|
| 978 | <p>NB: A useful function that is not used in this example is <font face="Courier New, Courier, mono"><a href="#selectlimit">SelectLimit</a></font>, |
---|
| 979 | which allows us to limit the number of rows shown. |
---|
| 980 | <pre> |
---|
| 981 | <? |
---|
| 982 | <font face="Courier New, Courier, mono"><b>include</b>('adodb.inc.php'); # load code common to ADOdb |
---|
| 983 | $<font color="#660000">conn</font> = &ADONewConnection('access'); # create a connection |
---|
| 984 | $<font color="#660000">conn</font>->PConnect('northwind'); # connect to MS-Access, northwind DSN |
---|
| 985 | $<font color="#660000">recordSet</font> = &$<font color="#660000">conn</font>->Execute('select * from products'); |
---|
| 986 | if (!$<font color="#660000">recordSet</font>) |
---|
| 987 | print $<font color="#660000">conn</font>->ErrorMsg(); |
---|
| 988 | else |
---|
| 989 | <b>while</b> (!$<font color="#660000">recordSet</font>->EOF) { |
---|
| 990 | <b>print</b> $<font color="#660000">recordSet</font>->fields[0].' '.$<font color="#660000">recordSet</font>->fields[1].'<BR>'; |
---|
| 991 | $<font color="#660000">recordSet</font>->MoveNext(); |
---|
| 992 | }</font><font face="Courier New, Courier, mono"> |
---|
| 993 | |
---|
| 994 | $<font color="#660000">recordSet</font>->Close(); # optional |
---|
| 995 | $<font color="#660000">conn</font>->Close(); # optional |
---|
| 996 | </font> |
---|
| 997 | ?> |
---|
| 998 | </pre> |
---|
| 999 | <p>The $<font face="Courier New, Courier, mono">recordSet</font> returned stores |
---|
| 1000 | the current row in the <font face="Courier New, Courier, mono">$recordSet->fields</font> |
---|
| 1001 | array, indexed by column number (starting from zero). We use the <font face="Courier New, Courier, mono"><a href="#movenext">MoveNext</a>()</font> |
---|
| 1002 | function to move to the next row. The <font face="Courier New, Courier, mono">EOF</font> |
---|
| 1003 | property is set to true when end-of-file is reached. If an error occurs in Execute(), |
---|
| 1004 | we return false instead of a recordset.</p> |
---|
| 1005 | <p>The <code>$recordSet->fields[]</code> array is generated by the PHP database |
---|
| 1006 | extension. Some database extensions only index by number and do not index the |
---|
| 1007 | array by field name. To force indexing by name - that is associative arrays |
---|
| 1008 | - use the SetFetchMode function. Each recordset saves and uses whatever fetch |
---|
| 1009 | mode was set when the recordset was created in Execute() or SelectLimit(). |
---|
| 1010 | <pre> |
---|
| 1011 | $db->SetFetchMode(ADODB_FETCH_NUM); |
---|
| 1012 | $rs1 = $db->Execute('select * from table'); |
---|
| 1013 | $db->SetFetchMode(ADODB_FETCH_ASSOC); |
---|
| 1014 | $rs2 = $db->Execute('select * from table'); |
---|
| 1015 | print_r($rs1->fields); # shows <i>array([0]=>'v0',[1] =>'v1')</i> |
---|
| 1016 | print_r($rs2->fields); # shows <i>array(['col1']=>'v0',['col2'] =>'v1')</i> |
---|
| 1017 | </pre> |
---|
| 1018 | <p> </p> |
---|
| 1019 | <p>To get the number of rows in the select statement, you can use <font face="Courier New, Courier, mono">$recordSet-><a href="#recordcount">RecordCount</a>()</font>. |
---|
| 1020 | Note that it can return -1 if the number of rows returned cannot be determined.</p> |
---|
| 1021 | <h3>Example 2: Advanced Select with Field Objects<a name="ex2"></a></h3> |
---|
| 1022 | <p>Select a table, display the first two columns. If the second column is a date |
---|
| 1023 | or timestamp, reformat the date to US format.</p> |
---|
| 1024 | <pre> |
---|
| 1025 | <? |
---|
| 1026 | <font face="Courier New, Courier, mono"><b>include</b>('adodb.inc.php'); # load code common to ADOdb |
---|
| 1027 | $<font color="#660000">conn</font> = &ADONewConnection('access'); # create a connection |
---|
| 1028 | $<font color="#660000">conn</font>->PConnect('northwind'); # connect to MS-Access, northwind dsn |
---|
| 1029 | $<font color="#660000">recordSet</font> = &$<font color="#660000">conn</font>->Execute('select CustomerID,OrderDate from Orders'); |
---|
| 1030 | if (!$<font color="#660000">recordSet</font>) |
---|
| 1031 | print $<font color="#660000">conn</font>->ErrorMsg(); |
---|
| 1032 | else |
---|
| 1033 | <b>while</b> (!$<font color="#660000">recordSet</font>->EOF) { |
---|
| 1034 | $<font color="#660000">fld</font> = <font color="#336600"><b>$</b><font color="#660000">recordSet</font><b>->FetchField</b></font><font color="#006600">(</font>1<font color="#006600">);</font> |
---|
| 1035 | $<font color="#660000">type</font> = <font color="#336600"><b>$</b><font color="#660000">recordSet</font><b>->MetaType</b></font>($fld->type); |
---|
| 1036 | |
---|
| 1037 | <b>if</b> ( $<font color="#660000">type</font> == 'D' || $<font color="#660000">type</font> == 'T') |
---|
| 1038 | <b>print</b> $<font color="#660000">recordSet</font>->fields[0].' '. |
---|
| 1039 | <b><font color="#336600">$</font></b><font color="#660000">recordSet</font><b><font color="#336600">->UserDate</font></b>($<font color="#660000">recordSet</font>->fields[1],'<b>m/d/Y</b>').'<BR>'; |
---|
| 1040 | <b>else </b> |
---|
| 1041 | <b>print</b> $<font color="#660000">recordSet</font>->fields[0].' '.$<font color="#660000">recordSet</font>->fields[1].'<BR>'; |
---|
| 1042 | |
---|
| 1043 | $<font color="#660000">recordSet</font>->MoveNext(); |
---|
| 1044 | }</font><font face="Courier New, Courier, mono"> |
---|
| 1045 | $<font color="#660000">recordSet</font>->Close(); # optional |
---|
| 1046 | $<font color="#660000">conn</font>->Close(); # optional |
---|
| 1047 | </font> |
---|
| 1048 | ?> |
---|
| 1049 | </pre> |
---|
| 1050 | <p>In this example, we check the field type of the second column using <font face="Courier New, Courier, mono"><a href="#fetchfield">FetchField</a>().</font> |
---|
| 1051 | This returns an object with at least 3 fields.</p> |
---|
| 1052 | <ul> |
---|
| 1053 | <li><b>name</b>: name of column</li> |
---|
| 1054 | <li> <b>type</b>: native field type of column</li> |
---|
| 1055 | <li> <b>max_length</b>: maximum length of field. Some databases such as MySQL |
---|
| 1056 | do not return the maximum length of the field correctly. In these cases max_length |
---|
| 1057 | will be set to -1.</li> |
---|
| 1058 | </ul> |
---|
| 1059 | <p>We then use <font face="Courier New, Courier, mono"><a href="#metatype">MetaType</a>()</font> |
---|
| 1060 | to translate the native type to a <i>generic</i> type. Currently the following |
---|
| 1061 | <i>generic</i> types are defined:</p> |
---|
| 1062 | <ul> |
---|
| 1063 | <li><b>C</b>: character fields that should be shown in a <input type="text"> |
---|
| 1064 | tag.</li> |
---|
| 1065 | <li><b>X</b>: TeXt, large text fields that should be shown in a <textarea></li> |
---|
| 1066 | <li><b>B</b>: Blobs, or Binary Large Objects. Typically images. |
---|
| 1067 | <li><b>D</b>: Date field</li> |
---|
| 1068 | <li><b>T</b>: Timestamp field</li> |
---|
| 1069 | <li><b>L</b>: Logical field (boolean or bit-field)</li> |
---|
| 1070 | <li><b>I</b>: Integer field</li> |
---|
| 1071 | <li><b>N</b>: Numeric field. Includes autoincrement, numeric, floating point, |
---|
| 1072 | real and integer. </li> |
---|
| 1073 | <li><b>R</b>: Serial field. Includes serial, autoincrement integers. This works |
---|
| 1074 | for selected databases. </li> |
---|
| 1075 | </ul> |
---|
| 1076 | <p>If the metatype is of type date or timestamp, then we print it using the user |
---|
| 1077 | defined date format with <font face="Courier New, Courier, mono"><a href="#userdate">UserDate</a>(),</font> |
---|
| 1078 | which converts the PHP SQL date string format to a user defined one. Another |
---|
| 1079 | use for <font face="Courier New, Courier, mono"><a href="#metatype">MetaType</a>()</font> |
---|
| 1080 | is data validation before doing an SQL insert or update.</p> |
---|
| 1081 | <h3>Example 3: Inserting<a name="ex3"></a></h3> |
---|
| 1082 | <p>Insert a row to the Orders table containing dates and strings that need to |
---|
| 1083 | be quoted before they can be accepted by the database, eg: the single-quote |
---|
| 1084 | in the word <i>John's</i>.</p> |
---|
| 1085 | <pre> |
---|
| 1086 | <? |
---|
| 1087 | <b>include</b>('adodb.inc.php'); # load code common to ADOdb |
---|
| 1088 | $<font color="#660000">conn</font> = &ADONewConnection('access'); # create a connection |
---|
| 1089 | |
---|
| 1090 | $<font color="#660000">conn</font>->PConnect('northwind'); # connect to MS-Access, northwind dsn |
---|
| 1091 | $<font color="#660000">shipto</font> = <font color="#006600"><b>$conn->qstr</b></font>("<i>John's Old Shoppe</i>"); |
---|
| 1092 | |
---|
| 1093 | $<font color="#660000">sql</font> = "insert into orders (customerID,EmployeeID,OrderDate,ShipName) "; |
---|
| 1094 | $<font color="#660000">sql</font> .= "values ('ANATR',2,".<b><font color="#006600">$conn->DBDate(</font>time()<font color="#006600">)</font></b><font color="#006600">.</font>",$<font color="#660000">shipto</font>)"; |
---|
| 1095 | |
---|
| 1096 | <b>if</b> ($<font color="#660000">conn</font>->Execute($<font color="#660000">sql</font>) <font color="#336600"><b>=== false</b></font>) { |
---|
| 1097 | <b>print</b> 'error inserting: '.<font color="#336600"><b>$conn->ErrorMsg()</b></font>.'<BR>'; |
---|
| 1098 | } |
---|
| 1099 | ?> |
---|
| 1100 | </pre> |
---|
| 1101 | <p>In this example, we see the advanced date and quote handling facilities of |
---|
| 1102 | ADOdb. The unix timestamp (which is a long integer) is appropriately formated |
---|
| 1103 | for Access with <font face="Courier New, Courier, mono"><a href="#dbdate">DBDate</a>()</font>, |
---|
| 1104 | and the right escape character is used for quoting the <i>John's Old Shoppe</i>, |
---|
| 1105 | which is<b> </b><i>John'<b>'</b>s Old Shoppe</i> and not PHP's default <i>John<b>'</b>s |
---|
| 1106 | Old Shoppe</i> with <font face="Courier New, Courier, mono"><a href="#qstr">qstr</a>()</font>. |
---|
| 1107 | </p> |
---|
| 1108 | <p>Observe the error-handling of the Execute statement. False is returned by<font face="Courier New, Courier, mono"> |
---|
| 1109 | <a href="#execute">Execute</a>() </font>if an error occured. The error message |
---|
| 1110 | for the last error that occurred is displayed in <font face="Courier New, Courier, mono"><a href="#errormsg">ErrorMsg</a>()</font>. |
---|
| 1111 | Note: <i>php_track_errors</i> might have to be enabled for error messages to |
---|
| 1112 | be saved.</p> |
---|
| 1113 | <h3> Example 4: Debugging<a name="ex4"></a></h3> |
---|
| 1114 | <pre><? |
---|
| 1115 | <b>include</b>('adodb.inc.php'); # load code common to ADOdb |
---|
| 1116 | $<font color="#663300">conn</font> = &ADONewConnection('access'); # create a connection |
---|
| 1117 | $<font color="#663300">conn</font>->PConnect('northwind'); # connect to MS-Access, northwind dsn |
---|
| 1118 | <font color="#000000">$<font color="#663300">shipto</font> = <b>$conn->qstr</b>("John's Old Shoppe"); |
---|
| 1119 | $<font color="#663300">sql</font> = "insert into orders (customerID,EmployeeID,OrderDate,ShipName) "; |
---|
| 1120 | $<font color="#663300">sql</font> .= "values ('ANATR',2,".$<font color="#663300">conn</font>->FormatDate(time()).",$shipto)"; |
---|
| 1121 | <b><font color="#336600">$<font color="#663300">conn</font>->debug = true;</font></b> |
---|
| 1122 | <b>if</b> ($<font color="#663300">conn</font>->Execute($sql) <b>=== false</b>) <b>print</b> 'error inserting';</font> |
---|
| 1123 | ?> |
---|
| 1124 | </pre> |
---|
| 1125 | <p>In the above example, we have turned on debugging by setting <b>debug = true</b>. |
---|
| 1126 | This will display the SQL statement before execution, and also show any error |
---|
| 1127 | messages. There is no need to call <font face="Courier New, Courier, mono"><a href="#errormsg">ErrorMsg</a>()</font> |
---|
| 1128 | in this case. For displaying the recordset, see the <font face="Courier New, Courier, mono"><a href="#exrs2html">rs2html</a>() |
---|
| 1129 | </font>example.</p> |
---|
| 1130 | <p>Also see the section on <a href=#errorhandling>Custom Error Handlers</a>.</p> |
---|
| 1131 | <h3>Example 5: MySQL and Menus<a name="ex5"></a></h3> |
---|
| 1132 | <p>Connect to MySQL database <i>agora</i>, and generate a <select> menu |
---|
| 1133 | from an SQL statement where the <option> captions are in the 1st column, |
---|
| 1134 | and the value to send back to the server is in the 2nd column.</p> |
---|
| 1135 | <pre><? |
---|
| 1136 | <b>include</b>('adodb.inc.php'); # load code common to ADOdb |
---|
| 1137 | $<font color="#663300">conn</font> = &ADONewConnection('mysql'); # create a connection |
---|
| 1138 | $<font color="#663300">conn</font>->PConnect('localhost','userid','','agora');# connect to MySQL, agora db |
---|
| 1139 | <font color="#000000">$<font color="#663300">sql</font> = 'select CustomerName, CustomerID from customers'; |
---|
| 1140 | $<font color="#663300">rs</font> = $<font color="#663300">conn</font>->Execute($sql); |
---|
| 1141 | <b>print</b> <b><font color="#336600">$<font color="#663300">rs</font>->GetMenu('GetCust','Mary Rosli'); |
---|
| 1142 | ?></font></b></font></pre> |
---|
| 1143 | <p>Here we define a menu named GetCust, with the menu option 'Mary Rosli' selected. |
---|
| 1144 | See <a href="#getmenu"><font face="Courier New, Courier, mono">GetMenu</font></a><font face="Courier New, Courier, mono">()</font>. |
---|
| 1145 | We also have functions that return the recordset as an array: <font face="Courier New, Courier, mono"><a href="#getarray">GetArray</a>()</font>, |
---|
| 1146 | and as an associative array with the key being the first column: <a href="#getassoc1">GetAssoc</a>().</p> |
---|
| 1147 | <h3>Example 6: Connecting to 2 Databases At Once<a name="ex6"></a></h3> |
---|
| 1148 | <pre><? |
---|
| 1149 | <b>include</b>('adodb.inc.php'); # load code common to ADOdb |
---|
| 1150 | $<font color="#663300">conn1</font> = &ADONewConnection('mysql'); # create a mysql connection |
---|
| 1151 | $<font color="#663300">conn2</font> = &ADONewConnection('oracle'); # create a oracle connection |
---|
| 1152 | |
---|
| 1153 | $conn1->PConnect($server, $userid, $password, $database); |
---|
| 1154 | $conn2->PConnect(false, $ora_userid, $ora_pwd, $oraname); |
---|
| 1155 | |
---|
| 1156 | $conn1->Execute('insert ...'); |
---|
| 1157 | $conn2->Execute('update ...'); |
---|
| 1158 | ?></pre> |
---|
| 1159 | <p> |
---|
| 1160 | <h3>Example 7: Generating Update and Insert SQL<a name="ex7"></a></h3> |
---|
| 1161 | ADOdb 1.31 and later supports two new recordset functions: GetUpdateSQL( ) and |
---|
| 1162 | GetInsertSQL( ). This allow you to perform a "SELECT * FROM table query WHERE...", |
---|
| 1163 | make a copy of the $rs->fields, modify the fields, and then generate the SQL to |
---|
| 1164 | update or insert into the table automatically. |
---|
| 1165 | <p> We show how the functions can be used when accessing a table with the following |
---|
| 1166 | fields: (ID, FirstName, LastName, Created). |
---|
| 1167 | <p> Before these functions can be called, you need to initialize the recordset |
---|
| 1168 | by performing a select on the table. Idea and code by Jonathan Younger jyounger#unilab.com. |
---|
| 1169 | Since ADOdb 2.42, you can pass a table name instead of a recordset into |
---|
| 1170 | GetInsertSQL (in $rs), and it will generate an insert statement for that table. |
---|
| 1171 | <p> |
---|
| 1172 | <pre><? |
---|
| 1173 | #============================================== |
---|
| 1174 | # SAMPLE GetUpdateSQL() and GetInsertSQL() code |
---|
| 1175 | #============================================== |
---|
| 1176 | include('adodb.inc.php'); |
---|
| 1177 | include('tohtml.inc.php'); |
---|
| 1178 | |
---|
| 1179 | #========================== |
---|
| 1180 | # This code tests an insert |
---|
| 1181 | |
---|
| 1182 | $sql = "SELECT * FROM ADOXYZ WHERE id = -1"; |
---|
| 1183 | # Select an empty record from the database |
---|
| 1184 | |
---|
| 1185 | $conn = &ADONewConnection("mysql"); # create a connection |
---|
| 1186 | $conn->debug=1; |
---|
| 1187 | $conn->PConnect("localhost", "admin", "", "test"); # connect to MySQL, testdb |
---|
| 1188 | $rs = $conn->Execute($sql); # Execute the query and get the empty recordset |
---|
| 1189 | |
---|
| 1190 | $record = array(); # Initialize an array to hold the record data to insert |
---|
| 1191 | |
---|
| 1192 | # Set the values for the fields in the record |
---|
| 1193 | # Note that field names are case-insensitive |
---|
| 1194 | $record["firstname"] = "Bob"; |
---|
| 1195 | $record["lastNamE"] = "Smith"; |
---|
| 1196 | $record["creaTed"] = time(); |
---|
| 1197 | |
---|
| 1198 | # Pass the empty recordset and the array containing the data to insert |
---|
| 1199 | # into the GetInsertSQL function. The function will process the data and return |
---|
| 1200 | # a fully formatted insert sql statement. |
---|
| 1201 | $insertSQL = $conn->GetInsertSQL($rs, $record); |
---|
| 1202 | |
---|
| 1203 | $conn->Execute($insertSQL); # Insert the record into the database |
---|
| 1204 | |
---|
| 1205 | #========================== |
---|
| 1206 | # This code tests an update |
---|
| 1207 | |
---|
| 1208 | $sql = "SELECT * FROM ADOXYZ WHERE id = 1"; |
---|
| 1209 | # Select a record to update |
---|
| 1210 | |
---|
| 1211 | $rs = $conn->Execute($sql); # Execute the query and get the existing record to update |
---|
| 1212 | |
---|
| 1213 | $record = array(); # Initialize an array to hold the record data to update |
---|
| 1214 | |
---|
| 1215 | # Set the values for the fields in the record |
---|
| 1216 | # Note that field names are case-insensitive |
---|
| 1217 | $record["firstname"] = "Caroline"; |
---|
| 1218 | $record["LasTnAme"] = "Smith"; # Update Caroline's lastname from Miranda to Smith |
---|
| 1219 | |
---|
| 1220 | # Pass the single record recordset and the array containing the data to update |
---|
| 1221 | # into the GetUpdateSQL function. The function will process the data and return |
---|
| 1222 | # a fully formatted update sql statement with the correct WHERE clause. |
---|
| 1223 | # If the data has not changed, no recordset is returned |
---|
| 1224 | $updateSQL = $conn->GetUpdateSQL($rs, $record); |
---|
| 1225 | |
---|
| 1226 | $conn->Execute($updateSQL); # Update the record in the database |
---|
| 1227 | $conn->Close(); |
---|
| 1228 | ?> |
---|
| 1229 | </pre> |
---|
| 1230 | GetInsertSQL/GetUpdateSQL ignore all empty fields (they are not added to the SQL generated). |
---|
| 1231 | To explicitly force a field to be set to "null", set the constant define('ADODB_FORCE_NULLS',1) before |
---|
| 1232 | you call the functions. |
---|
| 1233 | <p> |
---|
| 1234 | <h3>Example 8: Implementing Scrolling with Next and Previous<a name="ex8"></a></h3> |
---|
| 1235 | <p> The following code creates a very simple recordset pager, where you can scroll |
---|
| 1236 | from page to page of a recordset.</p> |
---|
| 1237 | <pre> |
---|
| 1238 | include_once('../adodb.inc.php'); |
---|
| 1239 | include_once('../adodb-pager.inc.php'); |
---|
| 1240 | session_start(); |
---|
| 1241 | |
---|
| 1242 | $db = NewADOConnection('mysql'); |
---|
| 1243 | |
---|
| 1244 | $db->Connect('localhost','root','','xphplens'); |
---|
| 1245 | |
---|
| 1246 | $sql = "select * from adoxyz "; |
---|
| 1247 | |
---|
| 1248 | $pager = new ADODB_Pager($db,$sql); |
---|
| 1249 | $pager->Render($rows_per_page=5);</pre> |
---|
| 1250 | <p>This will create a basic record pager that looks like this: <a name="scr"></a> |
---|
| 1251 | <p> |
---|
| 1252 | <table border=1 bgcolor=beige> |
---|
| 1253 | <tr> |
---|
| 1254 | <td> <a href="#scr"><code>|<</code></a> <a href="#scr"><code><<</code></a> |
---|
| 1255 | <a href="#scr"><code>>></code></a> <a href="#scr"><code>>|</code></a> |
---|
| 1256 | </td> |
---|
| 1257 | </tr> |
---|
| 1258 | <tr> |
---|
| 1259 | <td><TABLE COLS=4 width=100% border=1 bgcolor=white> |
---|
| 1260 | <TH>ID</TH> |
---|
| 1261 | <TH>First Name</TH> |
---|
| 1262 | <TH>Last Name</TH> |
---|
| 1263 | <TH>Date Created</TH> |
---|
| 1264 | <TR> |
---|
| 1265 | <TD align=right>36 </TD> |
---|
| 1266 | <TD>Alan </TD> |
---|
| 1267 | <TD>Turing </TD> |
---|
| 1268 | <TD>Sat 06, Oct 2001 </TD> |
---|
| 1269 | </TR> |
---|
| 1270 | <TR> |
---|
| 1271 | <TD align=right>37 </TD> |
---|
| 1272 | <TD>Serena </TD> |
---|
| 1273 | <TD>Williams </TD> |
---|
| 1274 | <TD>Sat 06, Oct 2001 </TD> |
---|
| 1275 | </TR> |
---|
| 1276 | <TR> |
---|
| 1277 | <TD align=right>38 </TD> |
---|
| 1278 | <TD>Yat Sun </TD> |
---|
| 1279 | <TD>Sun </TD> |
---|
| 1280 | <TD>Sat 06, Oct 2001 </TD> |
---|
| 1281 | </TR> |
---|
| 1282 | <TR> |
---|
| 1283 | <TD align=right>39 </TD> |
---|
| 1284 | <TD>Wai Hun </TD> |
---|
| 1285 | <TD>See </TD> |
---|
| 1286 | <TD>Sat 06, Oct 2001 </TD> |
---|
| 1287 | </TR> |
---|
| 1288 | <TR> |
---|
| 1289 | <TD align=right>40 </TD> |
---|
| 1290 | <TD>Steven </TD> |
---|
| 1291 | <TD>Oey </TD> |
---|
| 1292 | <TD>Sat 06, Oct 2001 </TD> |
---|
| 1293 | </TR> |
---|
| 1294 | </TABLE></td> |
---|
| 1295 | </tr> |
---|
| 1296 | <tr> |
---|
| 1297 | <td><font size=-1>Page 8/10</font></td> |
---|
| 1298 | </tr> |
---|
| 1299 | </table> |
---|
| 1300 | <p>The number of rows to display at one time is controled by the Render($rows) |
---|
| 1301 | method. If you do not pass any value to Render(), ADODB_Pager will default to |
---|
| 1302 | 10 records per page. |
---|
| 1303 | <p>You can control the column titles by modifying your SQL (supported by most |
---|
| 1304 | databases): |
---|
| 1305 | <pre>$sql = 'select id as "ID", firstname as "First Name", |
---|
| 1306 | lastname as "Last Name", created as "Date Created" <br> from adoxyz';</pre> |
---|
| 1307 | <p>The above code can be found in the <i>adodb/tests/testpaging.php</i> example |
---|
| 1308 | included with this release, and the class ADODB_Pager in <i>adodb/adodb-pager.inc.php</i>. |
---|
| 1309 | The ADODB_Pager code can be adapted by a programmer so that the text links can |
---|
| 1310 | be replaced by images, and the dull white background be replaced with more interesting |
---|
| 1311 | colors. |
---|
| 1312 | <p>You can also allow display of html by setting $pager->htmlSpecialChars = false. |
---|
| 1313 | <p>Some of the code used here was contributed by Iván Oliva and Cornel |
---|
| 1314 | G. </p> |
---|
| 1315 | <h3><a name="ex9"></a>Example 9: Exporting in CSV or Tab-Delimited Format</h3> |
---|
| 1316 | <p>We provide some helper functions to export in comma-separated-value (CSV) and |
---|
| 1317 | tab-delimited formats:</p> |
---|
| 1318 | <pre><b>include_once('/path/to/adodb/toexport.inc.php');</b><br>include_once('/path/to/adodb/adodb.inc.php');<br> |
---|
| 1319 | $db = &NewADOConnection('mysql');<br>$db->Connect($server, $userid, $password, $database);<br><br>$rs = $db->Execute('select fname as "First Name", surname as "Surname" from table'); |
---|
| 1320 | |
---|
| 1321 | print "<pre>";<br>print <b>rs2csv</b>($rs); # return a string, CSV format<p>print '<hr>'; |
---|
| 1322 | <br>$rs->MoveFirst(); # note, some databases do not support MoveFirst<br>print <b>rs2tab</b>($rs,<i>false</i>); # return a string, tab-delimited |
---|
| 1323 | # false == suppress field names in first line</p>print '<hr>';<br>$rs->MoveFirst();<br><b>rs2tabout</b>($rs); # send to stdout directly (there is also an rs2csvout function) |
---|
| 1324 | print "</pre>"; |
---|
| 1325 | |
---|
| 1326 | $rs->MoveFirst();<br><b></b>$fp = fopen($path, "w"); |
---|
| 1327 | if ($fp) {<br> <b>rs2csvfile</b>($rs, $fp); # write to file (there is also an rs2tabfile function) |
---|
| 1328 | fclose($fp);<br>} |
---|
| 1329 | </pre> |
---|
| 1330 | <p> Carriage-returns or newlines are converted to spaces. Field names are returned |
---|
| 1331 | in the first line of text. Strings containing the delimiter character are quoted |
---|
| 1332 | with double-quotes. Double-quotes are double-quoted again. This conforms to |
---|
| 1333 | Excel import and export guide-lines. |
---|
| 1334 | <p>All the above functions take as an optional last parameter, $addtitles which |
---|
| 1335 | defaults to <i>true</i>. When set to <i>false</i> field names in the first line |
---|
| 1336 | are suppressed. <br> |
---|
| 1337 | <h3>Example 10: Recordset Filters<a name="ex10"></a></h3> |
---|
| 1338 | <p>Sometimes we want to pre-process all rows in a recordset before we use it. |
---|
| 1339 | For example, we want to ucwords all text in recordset. |
---|
| 1340 | <pre> |
---|
| 1341 | include_once('adodb/rsfilter.inc.php'); |
---|
| 1342 | include_once('adodb/adodb.inc.php'); |
---|
| 1343 | |
---|
| 1344 | // ucwords() every element in the recordset |
---|
| 1345 | function do_ucwords(&$arr,$rs) |
---|
| 1346 | { |
---|
| 1347 | foreach($arr as $k => $v) { |
---|
| 1348 | $arr[$k] = ucwords($v); |
---|
| 1349 | } |
---|
| 1350 | } |
---|
| 1351 | |
---|
| 1352 | $db = NewADOConnection('mysql'); |
---|
| 1353 | $db->PConnect('server','user','pwd','db'); |
---|
| 1354 | |
---|
| 1355 | $rs = $db->Execute('select ... from table'); |
---|
| 1356 | $rs = <b>RSFilter</b>($rs,'do_ucwords'); |
---|
| 1357 | </pre> |
---|
| 1358 | <p>The <i>RSFilter</i> function takes 2 parameters, the recordset, and the name |
---|
| 1359 | of the <i>filter</i> function. It returns the processed recordset scrolled to |
---|
| 1360 | the first record. The <i>filter</i> function takes two parameters, the current |
---|
| 1361 | row as an array, and the recordset object. For future compatibility, you should |
---|
| 1362 | not use the original recordset object. </p> |
---|
| 1363 | <h3>Example 11:<a name="ex11"></a> Smart Transactions</h3> |
---|
| 1364 | The old way of doing transactions required you to use |
---|
| 1365 | <pre> |
---|
| 1366 | $conn-><b>BeginTrans</b>(); |
---|
| 1367 | $ok = $conn->Execute($sql); |
---|
| 1368 | if ($ok) $ok = $conn->Execute($sql2); |
---|
| 1369 | if (!$ok) $conn-><b>RollbackTrans</b>(); |
---|
| 1370 | else $conn-><b>CommitTrans</b>(); |
---|
| 1371 | </pre> |
---|
| 1372 | This is very complicated for large projects because you have to track the error |
---|
| 1373 | status. Smart Transactions is much simpler. You start a smart transaction by calling |
---|
| 1374 | StartTrans(): |
---|
| 1375 | <pre> |
---|
| 1376 | $conn-><b>StartTrans</b>(); |
---|
| 1377 | $conn->Execute($sql); |
---|
| 1378 | $conn->Execute($Sql2); |
---|
| 1379 | $conn-><b>CompleteTrans</b>(); |
---|
| 1380 | </pre> |
---|
| 1381 | CompleteTrans() detects when an SQL error occurs, and will Rollback/Commit as |
---|
| 1382 | appropriate. To specificly force a rollback even if no error occured, use FailTrans(). |
---|
| 1383 | Note that the rollback is done in CompleteTrans(), and not in FailTrans(). |
---|
| 1384 | <pre> |
---|
| 1385 | $conn-><b>StartTrans</b>(); |
---|
| 1386 | $conn->Execute($sql); |
---|
| 1387 | if (!CheckRecords()) $conn-><strong>FailTrans</strong>(); |
---|
| 1388 | $conn->Execute($Sql2); |
---|
| 1389 | $conn-><b>CompleteTrans</b>(); |
---|
| 1390 | </pre> |
---|
| 1391 | <p>You can also check if a transaction has failed, using HasFailedTrans(), which |
---|
| 1392 | returns true if FailTrans() was called, or there was an error in the SQL execution. |
---|
| 1393 | Make sure you call HasFailedTrans() before you call CompleteTrans(), as it is |
---|
| 1394 | only works between StartTrans/CompleteTrans. |
---|
| 1395 | <p>Lastly, StartTrans/CompleteTrans is nestable, and only the outermost block |
---|
| 1396 | is executed. In contrast, BeginTrans/CommitTrans/RollbackTrans is NOT nestable. |
---|
| 1397 | <pre> |
---|
| 1398 | $conn-><strong>StartTrans</strong>(); |
---|
| 1399 | $conn->Execute($sql); |
---|
| 1400 | $conn-><strong>StartTrans</strong>(); <font color="#006600"># ignored</font> |
---|
| 1401 | if (!CheckRecords()) $conn->FailTrans(); |
---|
| 1402 | $conn-><strong>CompleteTrans</strong>(); <font color="#006600"># ignored</font> |
---|
| 1403 | $conn->Execute($Sql2); |
---|
| 1404 | $conn-><strong>CompleteTrans</strong>(); |
---|
| 1405 | </pre> |
---|
| 1406 | <p>Note: Savepoints are currently not supported. |
---|
| 1407 | <h2><a name="errorhandling"></a>Using Custom Error Handlers and PEAR_Error</h2> |
---|
| 1408 | Apart from the old $con->debug = true; way of debugging, ADOdb 1.50 onwards provides |
---|
| 1409 | another way of handling errors using ADOdb's custom error handlers. |
---|
| 1410 | <p> ADOdb provides two custom handlers which you can modify for your needs. The |
---|
| 1411 | first one is in the <b>adodb-errorhandler.inc.php</b> file. This makes use of |
---|
| 1412 | the standard PHP functions <a href=http://php.net/error_reporting>error_reporting</a> |
---|
| 1413 | to control what error messages types to display, and <a href=http://php.net/trigger_error>trigger_error</a> |
---|
| 1414 | which invokes the default PHP error handler. |
---|
| 1415 | <p> Including the above file will cause <i>trigger_error($errorstring,E_USER_ERROR)</i> |
---|
| 1416 | to be called when<br> |
---|
| 1417 | (a) Connect() or PConnect() fails, or <br> |
---|
| 1418 | (b) a function that executes SQL statements such as Execute() or SelectLimit() |
---|
| 1419 | has an error.<br> |
---|
| 1420 | (c) GenID() appears to go into an infinite loop. |
---|
| 1421 | <p> The $errorstring is generated by ADOdb and will contain useful debugging information |
---|
| 1422 | similar to the error.log data generated below. This file adodb-errorhandler.inc.php |
---|
| 1423 | should be included before you create any ADOConnection objects. |
---|
| 1424 | <p> If you define error_reporting(0), no errors will be passed to the error handler. |
---|
| 1425 | If you set error_reporting(E_ALL), all errors will be passed to the error handler. |
---|
| 1426 | You still need to use <b>ini_set("display_errors", "0" or "1")</b> to control |
---|
| 1427 | the display of errors. |
---|
| 1428 | <pre> |
---|
| 1429 | <?php |
---|
| 1430 | <b>error_reporting(E_ALL); # pass any error messages triggered to error handler |
---|
| 1431 | include('adodb-errorhandler.inc.php');</b> |
---|
| 1432 | include('adodb.inc.php'); |
---|
| 1433 | include('tohtml.inc.php'); |
---|
| 1434 | $c = NewADOConnection('mysql'); |
---|
| 1435 | $c->PConnect('localhost','root','','northwind'); |
---|
| 1436 | $rs=$c->Execute('select * from productsz'); #invalid table productsz'); |
---|
| 1437 | if ($rs) rs2html($rs); |
---|
| 1438 | ?> |
---|
| 1439 | </pre> |
---|
| 1440 | <p> If you want to log the error message, you can do so by defining the following |
---|
| 1441 | optional constants ADODB_ERROR_LOG_TYPE and ADODB_ERROR_LOG_DEST. ADODB_ERROR_LOG_TYPE |
---|
| 1442 | is the error log message type (see <a href=http://php.net/error_log>error_log</a> |
---|
| 1443 | in the PHP manual). In this case we set it to 3, which means log to the file |
---|
| 1444 | defined by the constant ADODB_ERROR_LOG_DEST. |
---|
| 1445 | <pre> |
---|
| 1446 | <?php |
---|
| 1447 | <b>error_reporting(E_ALL); # report all errors |
---|
| 1448 | ini_set("display_errors", "0"); # but do not echo the errors |
---|
| 1449 | define('ADODB_ERROR_LOG_TYPE',3); |
---|
| 1450 | define('ADODB_ERROR_LOG_DEST','C:/errors.log'); |
---|
| 1451 | include('adodb-errorhandler.inc.php');</b> |
---|
| 1452 | include('adodb.inc.php'); |
---|
| 1453 | include('tohtml.inc.php'); |
---|
| 1454 | |
---|
| 1455 | $c = NewADOConnection('mysql'); |
---|
| 1456 | $c->PConnect('localhost','root','','northwind'); |
---|
| 1457 | $rs=$c->Execute('select * from productsz'); ## invalid table productsz |
---|
| 1458 | if ($rs) rs2html($rs); |
---|
| 1459 | ?> |
---|
| 1460 | </pre> |
---|
| 1461 | The following message will be logged in the error.log file: |
---|
| 1462 | <pre> |
---|
| 1463 | (2001-10-28 14:20:38) mysql error: [1146: Table 'northwind.productsz' doesn't exist] in |
---|
| 1464 | EXECUTE("select * from productsz") |
---|
| 1465 | </pre> |
---|
| 1466 | The second error handler is <b>adodb-errorpear.inc.php</b>. This will create a |
---|
| 1467 | PEAR_Error derived object whenever an error occurs. The last PEAR_Error object |
---|
| 1468 | created can be retrieved using ADODB_Pear_Error(). |
---|
| 1469 | <pre> |
---|
| 1470 | <?php |
---|
| 1471 | <b>include('adodb-errorpear.inc.php');</b> |
---|
| 1472 | include('adodb.inc.php'); |
---|
| 1473 | include('tohtml.inc.php'); |
---|
| 1474 | $c = NewADOConnection('mysql'); |
---|
| 1475 | $c->PConnect('localhost','root','','northwind'); |
---|
| 1476 | $rs=$c->Execute('select * from productsz'); #invalid table productsz'); |
---|
| 1477 | if ($rs) rs2html($rs); |
---|
| 1478 | else { |
---|
| 1479 | <b>$e = ADODB_Pear_Error(); |
---|
| 1480 | echo '<p>',$e->message,'</p>';</b> |
---|
| 1481 | } |
---|
| 1482 | ?> |
---|
| 1483 | </pre> |
---|
| 1484 | <p> You can use a PEAR_Error derived class by defining the constant ADODB_PEAR_ERROR_CLASS |
---|
| 1485 | before the adodb-errorpear.inc.php file is included. For easy debugging, you |
---|
| 1486 | can set the default error handler in the beginning of the PHP script to PEAR_ERROR_DIE, |
---|
| 1487 | which will cause an error message to be printed, then halt script execution: |
---|
| 1488 | <pre> |
---|
| 1489 | include('PEAR.php'); |
---|
| 1490 | PEAR::setErrorHandling('PEAR_ERROR_DIE'); |
---|
| 1491 | </pre> |
---|
| 1492 | <p> Note that we do not explicitly return a PEAR_Error object to you when an error |
---|
| 1493 | occurs. We return false instead. You have to call ADODB_Pear_Error() to get |
---|
| 1494 | the last error or use the PEAR_ERROR_DIE technique. |
---|
| 1495 | <h4>Error Messages</h4> |
---|
| 1496 | <p>Error messages are outputted using the static method ADOConnnection::outp($msg,$newline=true). |
---|
| 1497 | By default, it sends the messages to the client. You can override this to perform |
---|
| 1498 | error-logging. |
---|
| 1499 | <h2><a name="dsn"></a> Data Source Names</h2> |
---|
| 1500 | <p>We now support connecting using PEAR style DSN's. A DSN is a connection string |
---|
| 1501 | of the form:</p> |
---|
| 1502 | <p>$dsn = <i>"$driver://$username:$password@$hostname/$databasename"</i>;</p> |
---|
| 1503 | <p>An example:</p> |
---|
| 1504 | <pre> |
---|
| 1505 | $username = 'root'; |
---|
| 1506 | $password = ''; |
---|
| 1507 | $hostname = 'localhost'; |
---|
| 1508 | $databasename = 'xphplens'; |
---|
| 1509 | $driver = 'mysql'; |
---|
| 1510 | $dsn = "$driver://$username:$password@$hostname/$databasename" |
---|
| 1511 | $db = NewADOConnection(); |
---|
| 1512 | # DB::Connect($dsn) also works if you include 'adodb/adodb-pear.inc.php' at the top |
---|
| 1513 | $rs = $db->query('select firstname,lastname from adoxyz'); |
---|
| 1514 | $cnt = 0; |
---|
| 1515 | while ($arr = $rs->fetchRow()) { |
---|
| 1516 | print_r($arr); print "<br>"; |
---|
| 1517 | }</pre> |
---|
| 1518 | </p> |
---|
| 1519 | <p> <a href="#dsnsupport">More info and connection examples</a> on the DSN format. |
---|
| 1520 | |
---|
| 1521 | <h2><a name="pear"></a>PEAR Compatibility</h2> |
---|
| 1522 | We support DSN's (see above), and the following functions: |
---|
| 1523 | <pre> |
---|
| 1524 | <b> DB_Common</b> |
---|
| 1525 | query - returns PEAR_Error on error |
---|
| 1526 | limitQuery - return PEAR_Error on error |
---|
| 1527 | prepare - does not return PEAR_Error on error |
---|
| 1528 | execute - does not return PEAR_Error on error |
---|
| 1529 | setFetchMode - supports ASSOC and ORDERED |
---|
| 1530 | errorNative |
---|
| 1531 | quote |
---|
| 1532 | nextID |
---|
| 1533 | disconnect |
---|
| 1534 | |
---|
| 1535 | getOne |
---|
| 1536 | getAssoc |
---|
| 1537 | getRow |
---|
| 1538 | getCol |
---|
| 1539 | |
---|
| 1540 | <b> DB_Result</b> |
---|
| 1541 | numRows - returns -1 if not supported |
---|
| 1542 | numCols |
---|
| 1543 | fetchInto - does not support passing of fetchmode |
---|
| 1544 | fetchRows - does not support passing of fetchmode |
---|
| 1545 | free |
---|
| 1546 | </pre> |
---|
| 1547 | <h2><a name="caching"></a>Caching of Recordsets</h2> |
---|
| 1548 | <p>ADOdb now supports caching of recordsets using the CacheExecute( ), CachePageExecute( |
---|
| 1549 | ) and CacheSelectLimit( ) functions. There are similar to the non-cache functions, |
---|
| 1550 | except that they take a new first parameter, $secs2cache. |
---|
| 1551 | <p> An example: |
---|
| 1552 | <pre> |
---|
| 1553 | <b>include</b>('adodb.inc.php'); # load code common to ADOdb |
---|
| 1554 | $ADODB_CACHE_DIR = '/usr/ADODB_cache'; |
---|
| 1555 | $<font color="#663300">conn</font> = &ADONewConnection('mysql'); # create a connection |
---|
| 1556 | $<font color="#663300">conn</font>->PConnect('localhost','userid','','agora');# connect to MySQL, agora db |
---|
| 1557 | <font color="#000000">$<font color="#663300">sql</font> = 'select CustomerName, CustomerID from customers'; |
---|
| 1558 | $<font color="#663300">rs</font> = $<font color="#663300">conn</font>->CacheExecute(15,$sql);</font></pre> |
---|
| 1559 | <p><font color="#000000"> The first parameter is the number of seconds to cache |
---|
| 1560 | the query. Subsequent calls to that query will used the cached version stored |
---|
| 1561 | in $ADODB_CACHE_DIR. To force a query to execute and flush the cache, call CacheExecute() |
---|
| 1562 | with the first parameter set to zero. Alternatively, use the CacheFlush($sql) |
---|
| 1563 | call. </font></p> |
---|
| 1564 | <p><font color="#000000">For the sake of security, we recommend you set <i>register_globals=off</i> |
---|
| 1565 | in php.ini if you are using $ADODB_CACHE_DIR.</font></p> |
---|
| 1566 | <p>In ADOdb 1.80 onwards, the secs2cache parameter is optional in CacheSelectLimit() |
---|
| 1567 | and CacheExecute(). If you leave it out, it will use the $connection->cacheSecs |
---|
| 1568 | parameter, which defaults to 60 minutes. |
---|
| 1569 | <pre> |
---|
| 1570 | $conn->Connect(...); |
---|
| 1571 | $conn->cacheSecs = 3600*24; # cache 24 hours |
---|
| 1572 | $rs = $conn->CacheExecute('select * from table'); |
---|
| 1573 | </pre> |
---|
| 1574 | <p>Please note that magic_quotes_runtime should be turned off. <a href=http://phplens.com/lens/lensforum/msgs.php?LeNs#LensBM_forummsg>More |
---|
| 1575 | info</a>, and do not change $ADODB_FETCH_MODE (or SetFetchMode) |
---|
| 1576 | as the cached recordset will use the $ADODB_FETCH_MODE set when the query was executed. <font color="#000000"> |
---|
| 1577 | <h2><a name="pivot"></a>Pivot Tables</h2> |
---|
| 1578 | </font> <p><font color="#000000">Since ADOdb 2.30, we support the generation of |
---|
| 1579 | SQL to create pivot tables, also known as cross-tabulations. For further explanation |
---|
| 1580 | read this DevShed <a href=http://www.devshed.com/Server_Side/MySQL/MySQLWiz/>Cross-Tabulation |
---|
| 1581 | tutorial</a>. We assume that your database supports the SQL case-when expression. </font></p> |
---|
| 1582 | <font color="#000000"> |
---|
| 1583 | <p>In this example, we will use the Northwind database from Microsoft. In the |
---|
| 1584 | database, we have a products table, and we want to analyze this table by <i>suppliers |
---|
| 1585 | versus product categories</i>. We will place the suppliers on each row, and |
---|
| 1586 | pivot on categories. So from the table on the left, we generate the pivot-table |
---|
| 1587 | on the right:</p> |
---|
| 1588 | </font> |
---|
| 1589 | <table border="0" cellspacing="2" cellpadding="2" align="center"> |
---|
| 1590 | <tr> |
---|
| 1591 | <td> |
---|
| 1592 | <table border="1" cellspacing="2" cellpadding="2" align="center" width="142"> |
---|
| 1593 | <tr> |
---|
| 1594 | <td><i>Supplier</i></td> |
---|
| 1595 | <td><i>Category</i></td> |
---|
| 1596 | </tr> |
---|
| 1597 | <tr> |
---|
| 1598 | <td>supplier1</td> |
---|
| 1599 | <td>category1</td> |
---|
| 1600 | </tr> |
---|
| 1601 | <tr> |
---|
| 1602 | <td>supplier2</td> |
---|
| 1603 | <td>category1</td> |
---|
| 1604 | </tr> |
---|
| 1605 | <tr> |
---|
| 1606 | <td>supplier2</td> |
---|
| 1607 | <td>category2</td> |
---|
| 1608 | </tr> |
---|
| 1609 | </table> |
---|
| 1610 | </td> |
---|
| 1611 | <td> <font face="Courier New, Courier, mono">--></font></td> |
---|
| 1612 | <td> |
---|
| 1613 | <table border="1" cellspacing="2" cellpadding="2" align="center"> |
---|
| 1614 | <tr> |
---|
| 1615 | <td> </td> |
---|
| 1616 | <td><i>category1</i></td> |
---|
| 1617 | <td><i>category2</i></td> |
---|
| 1618 | <td><i>total</i></td> |
---|
| 1619 | </tr> |
---|
| 1620 | <tr> |
---|
| 1621 | <td><i>supplier1</i></td> |
---|
| 1622 | <td align="right">1</td> |
---|
| 1623 | <td align="right">0</td> |
---|
| 1624 | <td align="right">1</td> |
---|
| 1625 | </tr> |
---|
| 1626 | <tr> |
---|
| 1627 | <td><i>supplier2</i></td> |
---|
| 1628 | <td align="right">1</td> |
---|
| 1629 | <td align="right">1</td> |
---|
| 1630 | <td align="right">2</td> |
---|
| 1631 | </tr> |
---|
| 1632 | </table> |
---|
| 1633 | </td> |
---|
| 1634 | </tr> |
---|
| 1635 | </table> |
---|
| 1636 | <font color="#000000"> |
---|
| 1637 | <p>The following code will generate the SQL for a cross-tabulation: |
---|
| 1638 | <pre> |
---|
| 1639 | # Query the main "product" table |
---|
| 1640 | # Set the rows to CompanyName |
---|
| 1641 | # and the columns to the values of Categories |
---|
| 1642 | # and define the joins to link to lookup tables |
---|
| 1643 | # "categories" and "suppliers" |
---|
| 1644 | # |
---|
| 1645 | include "adodb/pivottable.php"; |
---|
| 1646 | $sql = PivotTableSQL( |
---|
| 1647 | $gDB, # adodb connection |
---|
| 1648 | 'products p ,categories c ,suppliers s', # tables |
---|
| 1649 | 'CompanyName', # rows (multiple fields allowed) |
---|
| 1650 | 'CategoryName', # column to pivot on |
---|
| 1651 | 'p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID' # joins/where |
---|
| 1652 | ); |
---|
| 1653 | </pre> |
---|
| 1654 | </font> |
---|
| 1655 | <p><font color="#000000"> This will generate the following SQL:</font></p> |
---|
| 1656 | <p><code><font size="2">SELECT CompanyName, <br> |
---|
| 1657 | SUM(CASE WHEN CategoryName='Beverages' THEN 1 ELSE 0 END) AS "Beverages", |
---|
| 1658 | <br> |
---|
| 1659 | SUM(CASE WHEN CategoryName='Condiments' THEN 1 ELSE 0 END) AS "Condiments", |
---|
| 1660 | <br> |
---|
| 1661 | SUM(CASE WHEN CategoryName='Confections' THEN 1 ELSE 0 END) AS "Confections", |
---|
| 1662 | <br> |
---|
| 1663 | SUM(CASE WHEN CategoryName='Dairy Products' THEN 1 ELSE 0 END) AS "Dairy |
---|
| 1664 | Products", <br> |
---|
| 1665 | SUM(CASE WHEN CategoryName='Grains/Cereals' THEN 1 ELSE 0 END) AS "Grains/Cereals", |
---|
| 1666 | <br> |
---|
| 1667 | SUM(CASE WHEN CategoryName='Meat/Poultry' THEN 1 ELSE 0 END) AS "Meat/Poultry", |
---|
| 1668 | <br> |
---|
| 1669 | SUM(CASE WHEN CategoryName='Produce' THEN 1 ELSE 0 END) AS "Produce", |
---|
| 1670 | <br> |
---|
| 1671 | SUM(CASE WHEN CategoryName='Seafood' THEN 1 ELSE 0 END) AS "Seafood", |
---|
| 1672 | <br> |
---|
| 1673 | SUM(1) as Total <br> |
---|
| 1674 | FROM products p ,categories c ,suppliers s WHERE p.CategoryID = c.CategoryID |
---|
| 1675 | and s.SupplierID= p.SupplierID <br> |
---|
| 1676 | GROUP BY CompanyName</font></code></p> |
---|
| 1677 | <p> You can also pivot on <i>numerical columns</i> and <i>generate totals</i> |
---|
| 1678 | by using ranges. <font color="#000000">This code was revised in ADODB 2.41 |
---|
| 1679 | and is not backward compatible.</font> The second example shows this:</p> |
---|
| 1680 | <pre> |
---|
| 1681 | $sql = PivotTableSQL( |
---|
| 1682 | $gDB, # adodb connection |
---|
| 1683 | 'products p ,categories c ,suppliers s', # tables |
---|
| 1684 | 'CompanyName', #<font color="#000000"> rows (multiple fields allowed)</font> |
---|
| 1685 | array( # column ranges |
---|
| 1686 | ' 0 ' => 'UnitsInStock <= 0', |
---|
| 1687 | "1 to 5" => '0 < UnitsInStock and UnitsInStock <= 5', |
---|
| 1688 | "6 to 10" => '5 < UnitsInStock and UnitsInStock <= 10', |
---|
| 1689 | "11 to 15" => '10 < UnitsInStock and UnitsInStock <= 15', |
---|
| 1690 | "16+" => '15 < UnitsInStock' |
---|
| 1691 | ), |
---|
| 1692 | ' p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID', # joins/where |
---|
| 1693 | 'UnitsInStock', # sum this field |
---|
| 1694 | 'Sum ' # sum label prefix |
---|
| 1695 | ); |
---|
| 1696 | </pre> |
---|
| 1697 | <p>Which generates: </p> |
---|
| 1698 | <p> <code> <font size="2">SELECT CompanyName, <br> |
---|
| 1699 | SUM(CASE WHEN UnitsInStock <= 0 THEN UnitsInStock ELSE 0 END) AS "Sum |
---|
| 1700 | 0 ", <br> |
---|
| 1701 | SUM(CASE WHEN 0 < UnitsInStock and UnitsInStock <= 5 THEN UnitsInStock |
---|
| 1702 | ELSE 0 END) AS "Sum 1 to 5",<br> |
---|
| 1703 | SUM(CASE WHEN 5 < UnitsInStock and UnitsInStock <= 10 THEN UnitsInStock |
---|
| 1704 | ELSE 0 END) AS "Sum 6 to 10",<br> |
---|
| 1705 | SUM(CASE WHEN 10 < UnitsInStock and UnitsInStock <= 15 THEN UnitsInStock |
---|
| 1706 | ELSE 0 END) AS "Sum 11 to 15", <br> |
---|
| 1707 | SUM(CASE WHEN 15 < UnitsInStock THEN UnitsInStock ELSE 0 END) AS "Sum |
---|
| 1708 | 16+", <br> |
---|
| 1709 | SUM(UnitsInStock) AS "Sum UnitsInStock", <br> |
---|
| 1710 | SUM(1) as Total,<br> |
---|
| 1711 | FROM products p ,categories c ,suppliers s WHERE p.CategoryID = c.CategoryID |
---|
| 1712 | and s.SupplierID= p.SupplierID <br> |
---|
| 1713 | GROUP BY CompanyName</font></code><font size="2"><br> |
---|
| 1714 | </font> </p> |
---|
| 1715 | <font color="#000000"><hr> |
---|
| 1716 | <h1>Class Reference<a name="ref"></a></h1> |
---|
| 1717 | <p>Function parameters with [ ] around them are optional.</p> |
---|
| 1718 | </font> |
---|
| 1719 | <h2>Global Variables</h2> |
---|
| 1720 | <h3><font color="#000000"><a name="adodb_countrecs"></a></font>$ADODB_COUNTRECS</h3> |
---|
| 1721 | <p>If the database driver API does not support counting the number of records |
---|
| 1722 | returned in a SELECT statement, the function RecordCount() is emulated when |
---|
| 1723 | the global variable $ADODB_COUNTRECS is set to true, which is the default. |
---|
| 1724 | We emulate this by buffering the records, which can take up large amounts |
---|
| 1725 | of memory for big recordsets. Set this variable to false for the best performance. |
---|
| 1726 | This variable is checked every time a query is executed, so you can selectively |
---|
| 1727 | choose which recordsets to count.</p> |
---|
| 1728 | <h3><font color="#000000"><a name="adodb_cache_dir"></a>$ADODB_CACHE_DIR</font></h3> |
---|
| 1729 | <font color="#000000"> |
---|
| 1730 | <p>If you are using recordset caching, this is the directory to save your recordsets |
---|
| 1731 | in. Define this before you call any caching functions such as CacheExecute( |
---|
| 1732 | ). We recommend setting <i>register_globals=off</i> in php.ini if you use this |
---|
| 1733 | feature for security reasons.</p> |
---|
| 1734 | <p>If you are using Unix and apache, you might need to set your cache directory |
---|
| 1735 | permissions to something similar to the following:</p> |
---|
| 1736 | </font> |
---|
| 1737 | <p>chown -R apache /path/to/adodb/cache<br> |
---|
| 1738 | chgrp -R apache /path/to/adodb/cache </p> |
---|
| 1739 | <font color="#000000"> |
---|
| 1740 | <h3><a name="adodb_ansi_padding_off"></a>$ADODB_ANSI_PADDING_OFF</h3> |
---|
| 1741 | <p>Determines whether to right trim CHAR fields (and also VARCHAR for ibase/firebird). |
---|
| 1742 | Set to true to trim. Default is false. Currently works for oci8po, ibase and firebird |
---|
| 1743 | drivers. Added in ADOdb 4.01. |
---|
| 1744 | <h3><font color="#000000"><a name="adodb_lang"></a></font>$ADODB_LANG</h3> |
---|
| 1745 | <p>Determines the language used in MetaErrorMsg(). The default is 'en', for English. |
---|
| 1746 | To find out what languages are supported, see the files |
---|
| 1747 | in adodb/lang/adodb-$lang.inc.php, where $lang is the supported langauge. |
---|
| 1748 | <h3><a name="adodb_fetch_mode"></a>$ADODB_FETCH_MODE</h3> |
---|
| 1749 | <p>This is a global variable that determines how arrays are retrieved by recordsets. |
---|
| 1750 | The recordset saves this value on creation (eg. in Execute( ) or SelectLimit( |
---|
| 1751 | )), and any subsequent changes to $ADODB_FETCH_MODE have no affect on existing |
---|
| 1752 | recordsets, only on recordsets created in the future.</p> |
---|
| 1753 | <p>The following constants are defined:</p> |
---|
| 1754 | </font> |
---|
| 1755 | <p><font color="#000000">define('ADODB_FETCH_DEFAULT',0);<br> |
---|
| 1756 | define('ADODB_FETCH_NUM',1);<br> |
---|
| 1757 | define('ADODB_FETCH_ASSOC',2);<br> |
---|
| 1758 | define('ADODB_FETCH_BOTH',3); </font></p> |
---|
| 1759 | <font color="#000000"> |
---|
| 1760 | <p> An example: |
---|
| 1761 | <pre> |
---|
| 1762 | $ADODB_<b>FETCH_MODE</b> = ADODB_FETCH_NUM; |
---|
| 1763 | $rs1 = $db->Execute('select * from table'); |
---|
| 1764 | $ADODB_<b>FETCH_MODE</b> = ADODB_FETCH_ASSOC; |
---|
| 1765 | $rs2 = $db->Execute('select * from table'); |
---|
| 1766 | print_r($rs1->fields); # shows <i>array([0]=>'v0',[1] =>'v1')</i> |
---|
| 1767 | print_r($rs2->fields); # shows <i>array(['col1']=>'v0',['col2'] =>'v1')</i> |
---|
| 1768 | </pre> |
---|
| 1769 | <p> As you can see in the above example, both recordsets store and use different |
---|
| 1770 | fetch modes based on the $ADODB_FETCH_MODE setting when the recordset was |
---|
| 1771 | created by Execute().</p> |
---|
| 1772 | <p>If no fetch mode is predefined, the fetch mode defaults to ADODB_FETCH_DEFAULT. |
---|
| 1773 | The behaviour of this default mode varies from driver to driver, so do not |
---|
| 1774 | rely on ADODB_FETCH_DEFAULT. For portability, we recommend sticking to ADODB_FETCH_NUM |
---|
| 1775 | or ADODB_FETCH_ASSOC. Many drivers do not support ADODB_FETCH_BOTH.</p> |
---|
| 1776 | <p><strong>SetFetchMode Function</strong></p> |
---|
| 1777 | <p>Some programmers prefer to use a more object-oriented solution, where the fetch |
---|
| 1778 | mode is set by a object function, <a href="#setfetchmode">SetFetchMode</a>. |
---|
| 1779 | Once this function is called for a connection object, that connection object |
---|
| 1780 | will ignore the global variable $ADODB_FETCH_MODE and will use the internal |
---|
| 1781 | fetchMode property exclusively.</p> |
---|
| 1782 | <pre> |
---|
| 1783 | $db->SetFetchMode(ADODB_FETCH_NUM); |
---|
| 1784 | $rs1 = $db->Execute('select * from table'); |
---|
| 1785 | $db->SetFetchMode(ADODB_FETCH_ASSOC); |
---|
| 1786 | $rs2 = $db->Execute('select * from table'); |
---|
| 1787 | print_r($rs1->fields); # shows <i>array([0]=>'v0',[1] =>'v1')</i> |
---|
| 1788 | print_r($rs2->fields); # shows <i>array(['col1']=>'v0',['col2'] =>'v1')</i></pre> |
---|
| 1789 | <p>To retrieve the previous fetch mode, you can use check the $db->fetchMode |
---|
| 1790 | property, or use the return value of SetFetchMode( ). |
---|
| 1791 | <p><strong><a name="adodb_assoc_case"></a>ADODB_ASSOC_CASE</strong></p> |
---|
| 1792 | <p>You can control the associative fetch case for certain drivers which behave |
---|
| 1793 | differently. For the sybase, oci8po, mssql, odbc and ibase drivers and all |
---|
| 1794 | drivers derived from them, ADODB_ASSOC_CASE will by default generate recordsets |
---|
| 1795 | where the field name keys are lower-cased. Use the constant ADODB_ASSOC_CASE |
---|
| 1796 | to change the case of the keys. There are 3 possible values:</p> |
---|
| 1797 | <p>0 = assoc lowercase field names. $rs->fields['orderid']<br> |
---|
| 1798 | 1 = assoc uppercase field names. $rs->fields['ORDERID']<br> |
---|
| 1799 | 2 = use native-case field names. $rs->fields['OrderID'] -- this is the |
---|
| 1800 | default since ADOdb 2.90</p> |
---|
| 1801 | <p>To use it, declare it before you incldue adodb.inc.php.</p> |
---|
| 1802 | <p>define('ADODB_ASSOC_CASE', 2); # use native-case for ADODB_FETCH_ASSOC<br> |
---|
| 1803 | include('adodb.inc.php'); </p> |
---|
| 1804 | <hr> |
---|
| 1805 | <h2>ADOConnection<a name="adoconnection"></a></h2> |
---|
| 1806 | <p>Object that performs the connection to the database, executes SQL statements |
---|
| 1807 | and has a set of utility functions for standardising the format of SQL statements |
---|
| 1808 | for issues such as concatenation and date formats.</p> |
---|
| 1809 | <h3>ADOConnection Fields</h3> |
---|
| 1810 | <p><b>databaseType</b>: Name of the database system we are connecting to. Eg. |
---|
| 1811 | <b>odbc</b> or <b>mssql</b> or <b>mysql</b>.</p> |
---|
| 1812 | <p><b>dataProvider</b>: The underlying mechanism used to connect to the database. |
---|
| 1813 | Normally set to <b>native</b>, unless using <b>odbc</b> or <b>ado</b>.</p> |
---|
| 1814 | <p><b>host: </b>Name of server or data source name (DSN) to connect to.</p> |
---|
| 1815 | <p><b>database</b>: Name of the database or to connect to. If ado is used, it |
---|
| 1816 | will hold the ado data provider.</p> |
---|
| 1817 | <p><b>user</b>: Login id to connect to database. Password is not saved for security |
---|
| 1818 | reasons.</p> |
---|
| 1819 | <p><b>raiseErrorFn</b>: Allows you to define an error handling function. See adodb-errorhandler.inc.php |
---|
| 1820 | for an example.</p> |
---|
| 1821 | <p><b>debug</b>: Set to <i>true</i> to make debug statements to appear.</p> |
---|
| 1822 | <p><b>concat_operator</b>: Set to '+' or '||' normally. The operator used to concatenate |
---|
| 1823 | strings in SQL. Used by the <b><a href="#concat">Concat</a></b> function.</p> |
---|
| 1824 | <p><b>fmtDate</b>: The format used by the <b><a href="#dbdate">DBDate</a></b> |
---|
| 1825 | function to send dates to the database. is '#Y-m-d#' for Microsoft Access, |
---|
| 1826 | and ''Y-m-d'' for MySQL.</p> |
---|
| 1827 | <p><b>fmtTimeStamp: </b>The format used by the <b><a href="#dbtimestamp">DBTimeStamp</a></b> |
---|
| 1828 | function to send timestamps to the database. </p> |
---|
| 1829 | <p><b>true</b>: The value used to represent true.Eg. '.T.'. for Foxpro, '1' for |
---|
| 1830 | Microsoft SQL.</p> |
---|
| 1831 | <p><b>false: </b> The value used to represent false. Eg. '.F.'. for Foxpro, '0' |
---|
| 1832 | for Microsoft SQL.</p> |
---|
| 1833 | <p><b>replaceQuote</b>: The string used to escape quotes. Eg. double single-quotes |
---|
| 1834 | for Microsoft SQL, and backslash-quote for MySQL. Used by <a href="#qstr">qstr</a>.</p> |
---|
| 1835 | <p><b>autoCommit</b>: indicates whether automatic commit is enabled. Default is |
---|
| 1836 | true.</p> |
---|
| 1837 | <p><b>charSet</b>: set the default charset to use. Currently only interbase supports |
---|
| 1838 | this.</p> |
---|
| 1839 | <p><b>dialect</b>: set the default sql dialect to use. Currently only interbase |
---|
| 1840 | supports this.</p> |
---|
| 1841 | <p><b>metaTablesSQL</b>: SQL statement to return a list of available tables. Eg. |
---|
| 1842 | <i>SHOW TABLES</i> in MySQL.</p> |
---|
| 1843 | <p><b>genID</b>: The latest id generated by GenID() if supported by the database.</p> |
---|
| 1844 | <p><b>cacheSecs</b>: The number of seconds to cache recordsets if CacheExecute() |
---|
| 1845 | or CacheSelectLimit() omit the $secs2cache parameter. Defaults to 60 minutes.</p> |
---|
| 1846 | <p><b>sysDate</b>: String that holds the name of the database function to call |
---|
| 1847 | to get the current date. Useful for inserts and updates.</p> |
---|
| 1848 | <p><b>sysTimeStamp</b>: String that holds the name of the database function to |
---|
| 1849 | call to get the current timestamp/datetime value.</p> |
---|
| 1850 | <p><b>leftOuter</b>: String that holds operator for left outer join, if known. |
---|
| 1851 | Otherwise set to false.</p> |
---|
| 1852 | <p><b>rightOuter</b>: String that holds operator for left outer join, if known. |
---|
| 1853 | Otherwise set to false.</p> |
---|
| 1854 | <p><b>ansiOuter</b>: Boolean that if true indicates that ANSI style outer joins |
---|
| 1855 | are permitted. Eg. <i>select * from table1 left join table2 on p1=p2.</i></p> |
---|
| 1856 | <p><b>connectSID</b>: Boolean that indicates whether to treat the $database parameter |
---|
| 1857 | in connects as the SID for the oci8 driver. Defaults to false. Useful for |
---|
| 1858 | Oracle 8.0.5 and earlier.</p> |
---|
| 1859 | <p><b>autoRollback</b>: Persistent connections are auto-rollbacked in PConnect( |
---|
| 1860 | ) if this is set to true. Default is false.</p> |
---|
| 1861 | <hr> |
---|
| 1862 | <h3>ADOConnection Main Functions</h3> |
---|
| 1863 | <p><b>ADOConnection( )</b></p> |
---|
| 1864 | <p>Constructor function. Do not call this directly. Use ADONewConnection( ) instead.</p> |
---|
| 1865 | <p><b>Connect<a name="connect"></a>($host,[$user],[$password],[$database])</b></p> |
---|
| 1866 | <p>Non-persistent connect to data source or server $<b>host</b>, using userid |
---|
| 1867 | $<b>user </b>and password $<b>password</b>. If the server supports multiple |
---|
| 1868 | databases, connect to database $<b>database</b>. </p> |
---|
| 1869 | <p>Returns true/false depending on connection success. Since 4.23, null is returned if the extension is not loaded.</p> |
---|
| 1870 | <p>ADO Note: If you are using a Microsoft ADO and not OLEDB, you can set the $database |
---|
| 1871 | parameter to the OLEDB data provider you are using.</p> |
---|
| 1872 | <p>PostgreSQL: An alternative way of connecting to the database is to pass the |
---|
| 1873 | standard PostgreSQL connection string in the first parameter $host, and the |
---|
| 1874 | other parameters will be ignored.</p> |
---|
| 1875 | <p>For Oracle and Oci8, there are two ways to connect. First is to use the TNS |
---|
| 1876 | name defined in your local tnsnames.ora (or ONAMES or HOSTNAMES). Place the |
---|
| 1877 | name in the $database field, and set the $host field to false. Alternatively, |
---|
| 1878 | set $host to the server, and $database to the database SID, this bypassed |
---|
| 1879 | tnsnames.ora. |
---|
| 1880 | <p>Examples: |
---|
| 1881 | <pre> # $oraname in tnsnames.ora/ONAMES/HOSTNAMES |
---|
| 1882 | $conn->Connect(false, 'scott', 'tiger', $oraname); |
---|
| 1883 | $conn->Connect('server:1521', 'scott', 'tiger', 'ServiceName'); # bypass tnsnames.ora</pre> |
---|
| 1884 | <p>There are many examples of connecting to a database. |
---|
| 1885 | See <a href=#connect_ex>Connection Examples</a>, <a href="http://php.weblogs.com/adodb">php.weblogs.com/ADOdb</a>, |
---|
| 1886 | and in the testdatabases.inc.php file included in the release.</p> |
---|
| 1887 | |
---|
| 1888 | <p><b>PConnect<a name="pconnect"></a>($host,[$user],[$password],[$database])</b></p> |
---|
| 1889 | <p>Persistent connect to data source or server $<b>host</b>, using userid $<b>user</b> |
---|
| 1890 | and password $<b>password</b>. If the server supports multiple databases, |
---|
| 1891 | connect to database $<b>database</b>.</p> |
---|
| 1892 | <p>We now perform a rollback on persistent connection for selected databases since |
---|
| 1893 | 2.21, as advised in the PHP manual. See change log or source code for which |
---|
| 1894 | databases are affected. |
---|
| 1895 | <p>Returns true/false depending on connection. Since 4.23, null is returned if the extension is not loaded. |
---|
| 1896 | See Connect( ) above for more info.</p> |
---|
| 1897 | <p>Since ADOdb 2.21, we also support autoRollback. If you set:</p> |
---|
| 1898 | </font> |
---|
| 1899 | <pre> $conn = &NewADOConnection('mysql'); |
---|
| 1900 | $conn->autoRollback = true; # default is false |
---|
| 1901 | $conn->PConnect(...); # rollback here</pre> |
---|
| 1902 | <p> Then when doing a persistent connection with PConnect( ), ADOdb will |
---|
| 1903 | perform a rollback first. This is because it is documented that PHP is |
---|
| 1904 | not guaranteed to rollback existing failed transactions when |
---|
| 1905 | persistent connections are used. This is implemented in Oracle, |
---|
| 1906 | MySQL, PgSQL, MSSQL, ODBC currently. |
---|
| 1907 | <p>Since ADOdb 3.11, you can force non-persistent |
---|
| 1908 | connections even if PConnect is called by defining the constant |
---|
| 1909 | ADODB_NEVER_PERSIST before you call PConnect. |
---|
| 1910 | <p> |
---|
| 1911 | Since 4.23, null is returned if the extension is not loaded. |
---|
| 1912 | <p><b>NConnect<a name="nconnect"></a>($host,[$user],[$password],[$database])</b></p> |
---|
| 1913 | <p>Always force a new connection. In contrast, PHP sometimes reuses connections |
---|
| 1914 | when you use Connect() or PConnect(). Currently works only on mysql (PHP 4.3.0 |
---|
| 1915 | or later), postgresql and oci8-derived drivers. For other drivers, NConnect() works like |
---|
| 1916 | Connect(). |
---|
| 1917 | <font color="#000000"> |
---|
| 1918 | <p><b>Execute<a name="execute"></a>($sql,$inputarr=false)</b></p> |
---|
| 1919 | <p>Execute SQL statement $<b>sql</b> and return derived class of ADORecordSet |
---|
| 1920 | if successful. Note that a record set is always returned on success, even |
---|
| 1921 | if we are executing an insert or update statement. You can also pass in $sql a statement prepared |
---|
| 1922 | in <a href=#prepare>Prepare()</a>.</p> |
---|
| 1923 | <p>Returns derived class of ADORecordSet. Eg. if connecting via mysql, then ADORecordSet_mysql |
---|
| 1924 | would be returned. False is returned if there was an error in executing the |
---|
| 1925 | sql.</p> |
---|
| 1926 | <p>The $inputarr parameter can be used for binding variables to parameters. Below |
---|
| 1927 | is an Oracle example:</p> |
---|
| 1928 | <pre> |
---|
| 1929 | $conn->Execute("SELECT * FROM TABLE WHERE COND=:val", array('val'=> $val)); |
---|
| 1930 | </pre> |
---|
| 1931 | <p>Another example, using ODBC,which uses the ? convention:</p> |
---|
| 1932 | <pre> |
---|
| 1933 | $conn->Execute("SELECT * FROM TABLE WHERE COND=?", array($val)); |
---|
| 1934 | </pre> |
---|
| 1935 | <a name="binding"></a> |
---|
| 1936 | <i>Binding variables</i><p> |
---|
| 1937 | Variable binding speeds the compilation and caching of SQL statements, leading |
---|
| 1938 | to higher performance. Currently Oracle, Interbase and ODBC supports variable binding. |
---|
| 1939 | Interbase/ODBC style ? binding is emulated in databases that do not support binding. |
---|
| 1940 | Note that you do not have to quote strings if you use binding. |
---|
| 1941 | <p> Variable binding in the odbc, interbase and oci8po drivers. |
---|
| 1942 | <pre> |
---|
| 1943 | $rs = $db->Execute('select * from table where val=?', array('10')); |
---|
| 1944 | </pre> |
---|
| 1945 | Variable binding in the oci8 driver: |
---|
| 1946 | <pre> |
---|
| 1947 | $rs = $db->Execute('select name from table where val=:key', |
---|
| 1948 | array('key' => 10)); |
---|
| 1949 | </pre> |
---|
| 1950 | <a name="bulkbind"></a> |
---|
| 1951 | <i>Bulk binding</i> |
---|
| 1952 | <p>Since ADOdb 3.80, we support bulk binding in Execute(), in which you pass in a 2-dimensional array to |
---|
| 1953 | be bound to an INSERT/UPDATE or DELETE statement. |
---|
| 1954 | <pre> |
---|
| 1955 | $arr = array( |
---|
| 1956 | array('Ahmad',32), |
---|
| 1957 | array('Zulkifli', 24), |
---|
| 1958 | array('Rosnah', 21) |
---|
| 1959 | ); |
---|
| 1960 | $ok = $db->Execute('insert into table (name,age) values (?,?)',$arr); |
---|
| 1961 | </pre> |
---|
| 1962 | <p>This provides very high performance as the SQL statement is prepared first. |
---|
| 1963 | The prepared statement is executed repeatedly for each array row until all rows are completed, |
---|
| 1964 | or until the first error. Very useful for importing data. |
---|
| 1965 | |
---|
| 1966 | <p><b>CacheExecute<a name="cacheexecute"></a>([$secs2cache,]$sql,$inputarr=false)</b></p> |
---|
| 1967 | <p>Similar to Execute, except that the recordset is cached for $secs2cache seconds |
---|
| 1968 | in the $ADODB_CACHE_DIR directory, and $inputarr only accepts 1-dimensional arrays. |
---|
| 1969 | If CacheExecute() is called again with the same $sql, $inputarr, |
---|
| 1970 | and also the same database, same userid, and the cached recordset |
---|
| 1971 | has not expired, the cached recordset is returned. |
---|
| 1972 | <pre> |
---|
| 1973 | include('adodb.inc.php'); |
---|
| 1974 | include('tohtml.inc.php'); |
---|
| 1975 | $ADODB_<b>CACHE_DIR</b> = '/usr/local/ADOdbcache'; |
---|
| 1976 | $conn = &ADONewConnection('mysql'); |
---|
| 1977 | $conn->PConnect('localhost','userid','password','database'); |
---|
| 1978 | $rs = $conn-><b>CacheExecute</b>(15, 'select * from table'); # cache 15 secs |
---|
| 1979 | rs2html($rs); /* recordset to html table */ |
---|
| 1980 | </pre> |
---|
| 1981 | <p> Alternatively, since ADOdb 1.80, the $secs2cache parameter is optional:</p> |
---|
| 1982 | <pre> $conn->Connect(...); |
---|
| 1983 | $conn->cacheSecs = 3600*24; // cache 24 hours |
---|
| 1984 | $rs = $conn->CacheExecute('select * from table'); |
---|
| 1985 | </pre> |
---|
| 1986 | If $secs2cache is omitted, we use the value |
---|
| 1987 | in $connection->cacheSecs (default is 3600 seconds, or 1 hour). Use CacheExecute() |
---|
| 1988 | only with SELECT statements. |
---|
| 1989 | <p>Performance note: I have done some benchmarks and found that they vary so greatly |
---|
| 1990 | that it's better to talk about when caching is of benefit. When your database |
---|
| 1991 | server is <i>much slower </i>than your Web server or the database is <i>very |
---|
| 1992 | overloaded </i>then ADOdb's caching is good because it reduces the load on |
---|
| 1993 | your database server. If your database server is lightly loaded or much faster |
---|
| 1994 | than your Web server, then caching could actually reduce performance. </p> |
---|
| 1995 | <p><b>ExecuteCursor<a name="executecursor"></a>($sql,$cursorName='rs',$parameters=false)</b></p> |
---|
| 1996 | <p>Execute an Oracle stored procedure, and returns an Oracle REF cursor variable as |
---|
| 1997 | a regular ADOdb recordset. Does not work with any other database except oci8. |
---|
| 1998 | Thanks to Robert Tuttle for the design. |
---|
| 1999 | <pre> |
---|
| 2000 | $db = ADONewConnection("oci8"); |
---|
| 2001 | $db->Connect("foo.com:1521", "uid", "pwd", "FOO"); |
---|
| 2002 | $rs = $db->ExecuteCursor("begin :cursorvar := getdata(:param1); end;", |
---|
| 2003 | 'cursorvar', |
---|
| 2004 | array('param1'=>10)); |
---|
| 2005 | # $rs is now just like any other ADOdb recordset object<br> rs2html($rs);</pre> |
---|
| 2006 | <p>ExecuteCursor() is a helper function that does the following internally: |
---|
| 2007 | <pre> |
---|
| 2008 | $stmt = $db->Prepare("begin :cursorvar := getdata(:param1); end;", true); |
---|
| 2009 | $db->Parameter($stmt, $cur, 'cursorvar', false, -1, OCI_B_CURSOR); |
---|
| 2010 | $rs = $db->Execute($stmt,$bindarr); |
---|
| 2011 | </pre> |
---|
| 2012 | <p>ExecuteCursor only accepts 1 out parameter. So if you have 2 out parameters, use: |
---|
| 2013 | <pre> |
---|
| 2014 | $vv = 'A%'; |
---|
| 2015 | $stmt = $db->PrepareSP("BEGIN list_tabs(:crsr,:tt); END;"); |
---|
| 2016 | $db->OutParameter($stmt, $cur, 'crsr', -1, OCI_B_CURSOR); |
---|
| 2017 | $db->OutParameter($stmt, $vv, 'tt', 32); # return varchar(32) |
---|
| 2018 | $arr = $db->GetArray($stmt); |
---|
| 2019 | print_r($arr); |
---|
| 2020 | echo " val = $vv"; ## outputs 'TEST' |
---|
| 2021 | </pre> |
---|
| 2022 | for the following PL/SQL: |
---|
| 2023 | <pre> |
---|
| 2024 | TYPE TabType IS REF CURSOR RETURN TAB%ROWTYPE; |
---|
| 2025 | |
---|
| 2026 | PROCEDURE list_tabs(tabcursor IN OUT TabType,tablenames IN OUT VARCHAR) IS |
---|
| 2027 | BEGIN |
---|
| 2028 | OPEN tabcursor FOR SELECT * FROM TAB WHERE tname LIKE tablenames; |
---|
| 2029 | tablenames := 'TEST'; |
---|
| 2030 | END list_tabs; |
---|
| 2031 | </pre> |
---|
| 2032 | <p><b>SelectLimit<a name="selectlimit"></a>($sql,$numrows=-1,$offset=-1,$inputarr=false)</b></p> |
---|
| 2033 | <p>Returns a recordset if successful. Returns false otherwise. Performs a select |
---|
| 2034 | statement, simulating PostgreSQL's SELECT statement, LIMIT $numrows OFFSET |
---|
| 2035 | $offset clause.</p> |
---|
| 2036 | <p>In PostgreSQL, SELECT * FROM TABLE LIMIT 3 will return the first 3 records |
---|
| 2037 | only. The equivalent is <code>$connection->SelectLimit('SELECT * FROM TABLE',3)</code>. |
---|
| 2038 | This functionality is simulated for databases that do not possess this feature.</p> |
---|
| 2039 | <p>And SELECT * FROM TABLE LIMIT 3 OFFSET 2 will return records 3, 4 and 5 (eg. |
---|
| 2040 | after record 2, return 3 rows). The equivalent in ADOdb is <code>$connection->SelectLimit('SELECT |
---|
| 2041 | * FROM TABLE',3,2)</code>.</p> |
---|
| 2042 | <p>Note that this is the <i>opposite</i> of MySQL's LIMIT clause. You can also |
---|
| 2043 | set <code>$connection->SelectLimit('SELECT * FROM TABLE',-1,10)</code> to |
---|
| 2044 | get rows 11 to the last row.</p> |
---|
| 2045 | <p>The last parameter $inputarr is for databases that support variable binding |
---|
| 2046 | such as Oracle oci8. This substantially reduces SQL compilation overhead. |
---|
| 2047 | Below is an Oracle example:</p> |
---|
| 2048 | <pre> |
---|
| 2049 | $conn->SelectLimit("SELECT * FROM TABLE WHERE COND=:val", 100,-1,array('val'=> $val)); |
---|
| 2050 | </pre> |
---|
| 2051 | <p>The oci8po driver (oracle portable driver) uses the more standard bind variable |
---|
| 2052 | of ?: |
---|
| 2053 | <pre> |
---|
| 2054 | $conn->SelectLimit("SELECT * FROM TABLE WHERE COND=?", 100,-1,array('val'=> $val)); |
---|
| 2055 | </pre> |
---|
| 2056 | <p> |
---|
| 2057 | <p>Ron Wilson reports that SelectLimit does not work with UNIONs. |
---|
| 2058 | <p><b>CacheSelectLimit<a name="cacheselectlimit"></a>([$secs2cache,] $sql, $numrows=-1,$offset=-1,$inputarr=false)</b></p> |
---|
| 2059 | <p>Similar to SelectLimit, except that the recordset returned is cached for $secs2cache |
---|
| 2060 | seconds in the $ADODB_CACHE_DIR directory. </p> |
---|
| 2061 | <p>Since 1.80, $secs2cache has been optional, and you can define the caching time |
---|
| 2062 | in $connection->cacheSecs.</p> |
---|
| 2063 | </font> |
---|
| 2064 | <pre><font color="#000000"> $conn->Connect(...); |
---|
| 2065 | $conn->cacheSecs = 3600*24; // cache 24 hours |
---|
| 2066 | $rs = $conn->CacheSelectLimit('select * from table',10);</font></pre> |
---|
| 2067 | <font color="#000000"> |
---|
| 2068 | <p><b>CacheFlush<a name="cacheflush"></a>($sql=false,$inputarr=false)</b></p> |
---|
| 2069 | <p>Flush (delete) any cached recordsets for the SQL statement $sql in $ADODB_CACHE_DIR. |
---|
| 2070 | <p>If no parameter is passed in, then all adodb_*.cache files are deleted. |
---|
| 2071 | <p> If you want to flush all cached recordsets manually, execute the following |
---|
| 2072 | PHP code (works only under Unix): <br> |
---|
| 2073 | <code> system("rm -f `find ".$ADODB_CACHE_DIR." -name |
---|
| 2074 | adodb_*.cache`");</code></p> |
---|
| 2075 | <p>For general cleanup of all expired files, you should use <a href="http://www.superscripts.com/tutorial/crontab.html">crontab</a> |
---|
| 2076 | on Unix, or at.exe on Windows, and a shell script similar to the following:<font face="Courier New, Courier, mono"><br> |
---|
| 2077 | #------------------------------------------------------ <br> |
---|
| 2078 | # This particular example deletes files in the TMPPATH <br> |
---|
| 2079 | # directory with the string ".cache" in their name that <br> |
---|
| 2080 | # are more than 7 days old. <br> |
---|
| 2081 | #------------------------------------------------------ <br> |
---|
| 2082 | AGED=7 <br> |
---|
| 2083 | find ${TMPPATH} -mtime +$AGED | grep "\.cache" | xargs rm -f <br> |
---|
| 2084 | </font> </p> |
---|
| 2085 | <p><b>MetaError<a name="metaerror"></a>($errno=false)</b></p> |
---|
| 2086 | <p>Returns a virtualized error number, based on PEAR DB's error number system. You might |
---|
| 2087 | need to include adodb-error.inc.php before you call this function. The parameter $errno |
---|
| 2088 | is the native error number you want to convert. If you do not pass any parameter, MetaError |
---|
| 2089 | will call ErrorNo() for you and convert it. If the error number cannot be virtualized, MetaError |
---|
| 2090 | will return -1 (DB_ERROR).</p> |
---|
| 2091 | |
---|
| 2092 | <p><b>MetaErrorMsg<a name="metaerrormsg"></a>($errno)</b></p> |
---|
| 2093 | <p>Pass the error number returned by MetaError() for the equivalent textual error message.</p> |
---|
| 2094 | <p><b>ErrorMsg<a name="errormsg"></a>()</b></p> |
---|
| 2095 | <p>Returns the last status or error message. The error message is reset after every |
---|
| 2096 | call to Execute(). |
---|
| 2097 | <p> |
---|
| 2098 | This can return a string even if |
---|
| 2099 | no error occurs. In general you do not need to call this function unless an |
---|
| 2100 | ADOdb function returns false on an error. </p> |
---|
| 2101 | <p>Note: If <b>debug</b> is enabled, the SQL error message is always displayed |
---|
| 2102 | when the <b>Execute</b> function is called.</p> |
---|
| 2103 | <p><b>ErrorNo<a name="errorno"></a>()</b></p> |
---|
| 2104 | <p>Returns the last error number. The error number is reset after every call to Execute(). |
---|
| 2105 | If 0 is returned, no error occurred. |
---|
| 2106 | <p> |
---|
| 2107 | Note that old versions of PHP (pre 4.0.6) do |
---|
| 2108 | not support error number for ODBC. In general you do not need to call this |
---|
| 2109 | function unless an ADOdb function returns false on an error.</p> |
---|
| 2110 | </font> |
---|
| 2111 | <p><font color="#000000"><b>SetFetchMode<a name="setfetchmode"></a>($mode)</b></font></p> |
---|
| 2112 | <p><font color="#000000">Sets the current fetch mode for the connection and stores |
---|
| 2113 | it in $db->fetchMode. Legal modes are ADODB_FETCH_ASSOC and ADODB_FETCH_NUM. |
---|
| 2114 | For more info, see <a href="#adodb_fetch_mode">$ADODB_FETCH_MODE</a>.</font></p> |
---|
| 2115 | <p><font color="#000000">Returns the previous fetch mode, which could be false |
---|
| 2116 | if SetFetchMode( ) has not been called before.</font></p> |
---|
| 2117 | <font color="#000000"> |
---|
| 2118 | <p><b>CreateSequence<a name="createseq"></a>($seqName = 'adodbseq',$startID=1)</b></p> |
---|
| 2119 | <p>Create a sequence. The next time GenID( ) is called, the value returned will |
---|
| 2120 | be $startID. Added in 2.60. |
---|
| 2121 | <p><b>DropSequenceD<a name="dropseq"></a>($seqName = 'adodbseq')</b></p> |
---|
| 2122 | <p>Delete a sequence. Added in 2.60. |
---|
| 2123 | <p><b>GenID<a name="genid"></a>($seqName = 'adodbseq',$startID=1)</b></p> |
---|
| 2124 | <p>Generate a sequence number . Works for interbase, |
---|
| 2125 | mysql, postgresql, oci8, oci8po, mssql, ODBC based (access,vfp,db2,etc) drivers |
---|
| 2126 | currently. Uses $seqName as the name of the sequence. GenID() will automatically |
---|
| 2127 | create the sequence for you if it does not exist (provided the userid has |
---|
| 2128 | permission to do so). Otherwise you will have to create the sequence yourself. |
---|
| 2129 | <p> If your database driver emulates sequences, the name of the table is the sequence |
---|
| 2130 | name. The table has one column, "id" which should be of type integer, or if |
---|
| 2131 | you need something larger - numeric(16). |
---|
| 2132 | <p> For ODBC and databases that do not support sequences natively (eg mssql, mysql), |
---|
| 2133 | we create a table for each sequence. If the sequence has not been defined |
---|
| 2134 | earlier, it is created with the starting value set in $startID.</p> |
---|
| 2135 | <p>Note that the mssql driver's GenID() before 1.90 used to generate 16 byte GUID's.</p> |
---|
| 2136 | <p><b>UpdateBlob<a name="updateblob"></a>($table,$column,$val,$where)</b></p> |
---|
| 2137 | Allows you to store a blob (in $val) into $table into $column in a row at $where. |
---|
| 2138 | <p> Usage: |
---|
| 2139 | <p> |
---|
| 2140 | <pre> |
---|
| 2141 | # for oracle |
---|
| 2142 | $conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1, empty_blob())'); |
---|
| 2143 | $conn->UpdateBlob('blobtable','blobcol',$blobvalue,'id=1'); |
---|
| 2144 | |
---|
| 2145 | # non oracle databases |
---|
| 2146 | $conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1, null)'); |
---|
| 2147 | $conn->UpdateBlob('blobtable','blobcol',$blobvalue,'id=1'); |
---|
| 2148 | </pre> |
---|
| 2149 | <p> Returns true if succesful, false otherwise. Supported by MySQL, PostgreSQL, |
---|
| 2150 | Oci8, Oci8po and Interbase drivers. Other drivers might work, depending on |
---|
| 2151 | the state of development.</p> |
---|
| 2152 | <p>Note that when an Interbase blob is retrieved using SELECT, it still needs |
---|
| 2153 | to be decoded using $connection->DecodeBlob($blob); to derive the original |
---|
| 2154 | value in versions of PHP before 4.1.0. |
---|
| 2155 | <p>For PostgreSQL, you can store your blob using blob oid's or as a bytea field. |
---|
| 2156 | You can use bytea fields but not blob oid's currently with UpdateBlob( ). |
---|
| 2157 | Conversely UpdateBlobFile( ) supports oid's, but not bytea data.<br> |
---|
| 2158 | <br> |
---|
| 2159 | If you do not pass in an oid, then UpdateBlob() assumes that you are storing |
---|
| 2160 | in bytea fields. |
---|
| 2161 | <p><b>UpdateClob<a name="updateclob"></a>($table,$column,$val,$where)</b></p> |
---|
| 2162 | Allows you to store a clob (in $val) into $table into $column in a row at $where. |
---|
| 2163 | Similar to UpdateBlob (see above), but for Character Large OBjects. |
---|
| 2164 | <p> Usage: |
---|
| 2165 | <pre> |
---|
| 2166 | # for oracle |
---|
| 2167 | $conn->Execute('INSERT INTO clobtable (id, clobcol) VALUES (1, empty_clob())'); |
---|
| 2168 | $conn->UpdateBlob('clobtable','clobcol',$clobvalue,'id=1'); |
---|
| 2169 | |
---|
| 2170 | # non oracle databases |
---|
| 2171 | $conn->Execute('INSERT INTO clobtable (id, clobcol) VALUES (1, null)'); |
---|
| 2172 | $conn->UpdateBlob('clobtable','clobcol',$clobvalue,'id=1'); |
---|
| 2173 | </pre> |
---|
| 2174 | <p><b>UpdateBlobFile<a name="updateblobfile"></a>($table,$column,$path,$where,$blobtype='BLOB')</b></p> |
---|
| 2175 | <p>Similar to UpdateBlob, except that we pass in a file path to where the blob |
---|
| 2176 | resides. |
---|
| 2177 | <p>For PostgreSQL, if you are using blob oid's, use this interface. This interface |
---|
| 2178 | does not support bytea fields. |
---|
| 2179 | <p>Returns true if successful, false otherwise. |
---|
| 2180 | <p><b>BlobEncode<a name="blobencode" id="blobencode"></a>($blob)</b> |
---|
| 2181 | <p>Some databases require blob's to be encoded manually before upload. Note if |
---|
| 2182 | you use UpdateBlob( ) or UpdateBlobFile( ) the conversion is done automatically |
---|
| 2183 | for you and you do not have to call this function. For PostgreSQL, currently, |
---|
| 2184 | BlobEncode() can only be used for bytea fields. |
---|
| 2185 | <p>Returns the encoded blob value. |
---|
| 2186 | <p>Note that there is a connection property called <em>blobEncodeType</em> which |
---|
| 2187 | has 3 legal values: |
---|
| 2188 | <p>false - no need to perform encoding or decoding.<br> |
---|
| 2189 | 'I' - blob encoding required, and returned encoded blob is a numeric value |
---|
| 2190 | (no need to quote).<br> |
---|
| 2191 | 'C' - blob encoding required, and returned encoded blob is a character value |
---|
| 2192 | (requires quoting). |
---|
| 2193 | <p>This is purely for documentation purposes, so that programs that accept multiple |
---|
| 2194 | database drivers know what is the right thing to do when processing blobs. |
---|
| 2195 | <p><strong>BlobDecode<a name="blobdecode"></a>($blob)</strong> |
---|
| 2196 | </font><p><font color="#000000">Some databases require blob's to be decoded manually after doing a select statement. |
---|
| 2197 | If the database does not require decoding, then this function will return |
---|
| 2198 | the blob unchanged. Currently BlobDecode is only required for one database, |
---|
| 2199 | PostgreSQL, and only if you are using blob oid's (if you are using bytea fields, |
---|
| 2200 | we auto-decode for you).</font><font color="#000000"> |
---|
| 2201 | <pre>$rs = $db->Execute("select bloboid from postgres_table where id=$key"); |
---|
| 2202 | $blob = $db->BlobDecode( reset($rs->fields) );</pre> |
---|
| 2203 | <p><b>Replace<a name="replace"></a>($table, $arrFields, $keyCols,$autoQuote=false)</b></p> |
---|
| 2204 | <p>Try to update a record, and if the record is not found, an insert statement |
---|
| 2205 | is generated and executed. Returns 0 on failure, 1 if update statement worked, |
---|
| 2206 | 2 if no record was found and the insert was executed successfully. This differs |
---|
| 2207 | from MySQL's replace which deletes the record and inserts a new record. This |
---|
| 2208 | also means you cannot update the primary key. The only exception to this is |
---|
| 2209 | Interbase and its derivitives, which uses delete and insert because of some |
---|
| 2210 | Interbase API limitations. |
---|
| 2211 | <p>The parameters are $table which is the table name, the $keyCols which is an |
---|
| 2212 | associative array where the keys are the field names, and keyCols is the name |
---|
| 2213 | of the primary key, or an array of field names if it is a compound key. If |
---|
| 2214 | $autoQuote is set to true, then Replace() will quote all values that are non-numeric; |
---|
| 2215 | auto-quoting will not quote nulls. Note that auto-quoting will not work if |
---|
| 2216 | you use SQL functions or operators. |
---|
| 2217 | <p>Examples: |
---|
| 2218 | <pre> |
---|
| 2219 | # single field primary key |
---|
| 2220 | $ret = $db->Replace('atable', |
---|
| 2221 | array('id'=>1000,'firstname'=>'Harun','lastname'=>'Al-Rashid'), |
---|
| 2222 | 'id',$autoquote = true); |
---|
| 2223 | # generates UPDATE atable SET firstname='Harun',lastname='Al-Rashid' WHERE id=1000 |
---|
| 2224 | # or INSERT INTO atable (id,firstname,lastname) VALUES (1000,'Harun','Al-Rashid') |
---|
| 2225 | |
---|
| 2226 | # compound key |
---|
| 2227 | $ret = $db->Replace('atable2', |
---|
| 2228 | array('firstname'=>'Harun','lastname'=>'Al-Rashid', 'age' => 33, 'birthday' => 'null'), |
---|
| 2229 | array('lastname','firstname'), |
---|
| 2230 | $autoquote = true); |
---|
| 2231 | |
---|
| 2232 | # no auto-quoting |
---|
| 2233 | $ret = $db->Replace('atable2', |
---|
| 2234 | array('firstname'=>"'Harun'",'lastname'=>"'Al-Rashid'", 'age' => 'null'), |
---|
| 2235 | array('lastname','firstname')); |
---|
| 2236 | </pre> |
---|
| 2237 | <p><b>GetUpdateSQL<a name="getupdatesql"></a>(&$rs, $arrFields, $forceUpdate=false,$magicq=false, $forcenulls=false)</b></p> |
---|
| 2238 | <p>Generate SQL to update a table given a recordset $rs, and the modified fields |
---|
| 2239 | of the array $arrFields (which must be an associative array holding the column |
---|
| 2240 | names and the new values) are compared with the current recordset. If $forceUpdate |
---|
| 2241 | is true, then we also generate the SQL even if $arrFields is identical to |
---|
| 2242 | $rs->fields. Requires the recordset to be associative. $magicq is used |
---|
| 2243 | to indicate whether magic quotes are enabled (see qstr()). The field names in the array |
---|
| 2244 | are case-insensitive.</p> |
---|
| 2245 | <p>Since 3.61, define('ADODB_FORCE_NULLS',1) and all PHP nulls will be auto-converted to SQL nulls. |
---|
| 2246 | Since 4.24, we allow you to pass in $forcenulls as a parameter. This overrides the ADODB_FORCE_NULLS |
---|
| 2247 | constant. |
---|
| 2248 | <p><b>GetInsertSQL<a name="getinsertsql"></a>(&$rs, $arrFields,$magicq=false,$forcenulls=false)</b></p> |
---|
| 2249 | <p>Generate SQL to insert into a table given a recordset $rs. Requires the query |
---|
| 2250 | to be associative. $magicq is used to indicate whether magic quotes are enabled |
---|
| 2251 | (for qstr()). The field names in the array are case-insensitive.</p> |
---|
| 2252 | <p> |
---|
| 2253 | Since 2.42, you can pass a table name instead of a recordset into |
---|
| 2254 | GetInsertSQL (in $rs), and it will generate an insert statement for that table. |
---|
| 2255 | <p>Since 3.61, define('ADODB_FORCE_NULLS',1) and all PHP nulls will be auto-converted |
---|
| 2256 | to SQL nulls. |
---|
| 2257 | Since 4.24, we allow you to pass in $forcenulls as a parameter. This overrides the ADODB_FORCE_NULLS |
---|
| 2258 | constant. |
---|
| 2259 | <p><b>PageExecute<a name="pageexecute"></a>($sql, $nrows, $page, $inputarr=false)</b> |
---|
| 2260 | <p>Used for pagination of recordset. $page is 1-based. See <a href="#ex8">Example |
---|
| 2261 | 8</a>.</p> |
---|
| 2262 | </font> |
---|
| 2263 | <p><font color="#000000"><b>CachePageExecute<a name="cachepageexecute"></a>($secs2cache, |
---|
| 2264 | $sql, $nrows, $page, $inputarr=false)</b> </font></p> |
---|
| 2265 | <p><font color="#000000">Used for pagination of recordset. $page is 1-based. See |
---|
| 2266 | <a href="#ex8">Example 8</a>. Caching version of PageExecute.</font></p> |
---|
| 2267 | <font color="#000000"> |
---|
| 2268 | <p></p> |
---|
| 2269 | <p><b>Close<a name="close"></a>( )</b></p> |
---|
| 2270 | <p>Close the database connection. PHP4 proudly states that we no longer have to |
---|
| 2271 | clean up at the end of the connection because the reference counting mechanism |
---|
| 2272 | of PHP4 will automatically clean up for us.</p> |
---|
| 2273 | <p><b>StartTrans<a name="starttrans"></a>( )</b></p> |
---|
| 2274 | <p>Start a monitored transaction. As SQL statements are executed, ADOdb will monitor |
---|
| 2275 | for SQL errors, and if any are detected, when CompleteTrans() is called, we auto-rollback. |
---|
| 2276 | <p> |
---|
| 2277 | <p> To understand why StartTrans() is superior to BeginTrans(), |
---|
| 2278 | let us examine a few ways of using BeginTrans(). |
---|
| 2279 | The following is the wrong way to use transactions: |
---|
| 2280 | <pre> |
---|
| 2281 | $DB->BeginTrans(); |
---|
| 2282 | $DB->Execute("update table1 set val=$val1 where id=$id"); |
---|
| 2283 | $DB->Execute("update table2 set val=$val2 where id=$id"); |
---|
| 2284 | $DB->CommitTrans(); |
---|
| 2285 | </pre> |
---|
| 2286 | <p>because you perform no error checking. It is possible to update table1 and |
---|
| 2287 | for the update on table2 to fail. Here is a better way: |
---|
| 2288 | <pre> |
---|
| 2289 | $DB->BeginTrans(); |
---|
| 2290 | $ok = $DB->Execute("update table1 set val=$val1 where id=$id"); |
---|
| 2291 | if ($ok) $ok = $DB->Execute("update table2 set val=$val2 where id=$id"); |
---|
| 2292 | if ($ok) $DB->CommitTrans(); |
---|
| 2293 | else $DB->RollbackTrans(); |
---|
| 2294 | </pre> |
---|
| 2295 | <p>Another way is (since ADOdb 2.0): |
---|
| 2296 | <pre> |
---|
| 2297 | $DB->BeginTrans(); |
---|
| 2298 | $ok = $DB->Execute("update table1 set val=$val1 where id=$id"); |
---|
| 2299 | if ($ok) $ok = $DB->Execute("update table2 set val=$val2 where id=$id"); |
---|
| 2300 | $DB->CommitTrans($ok); |
---|
| 2301 | </pre> |
---|
| 2302 | <p> Now it is a headache monitoring $ok all over the place. StartTrans() is an |
---|
| 2303 | improvement because it monitors all SQL errors for you. This is particularly |
---|
| 2304 | useful if you are calling black-box functions in which SQL queries might be executed. |
---|
| 2305 | Also all BeginTrans, CommitTrans and RollbackTrans calls inside a StartTrans block |
---|
| 2306 | will be disabled, so even if the black box function does a commit, it will be ignored. |
---|
| 2307 | <pre> |
---|
| 2308 | $DB->StartTrans(); |
---|
| 2309 | CallBlackBox(); |
---|
| 2310 | $DB->Execute("update table1 set val=$val1 where id=$id"); |
---|
| 2311 | $DB->Execute("update table2 set val=$val2 where id=$id"); |
---|
| 2312 | $DB->CompleteTrans($ok); |
---|
| 2313 | </pre> |
---|
| 2314 | <p>Note that a StartTrans blocks are nestable, the inner blocks are ignored. |
---|
| 2315 | <p><b>CompleteTrans<a name="completetrans"></a>($autoComplete=true)</b></p> |
---|
| 2316 | <p>Complete a transaction called with StartTrans(). This function monitors |
---|
| 2317 | for SQL errors, and will commit if no errors have occured, otherwise it will rollback. |
---|
| 2318 | Returns true on commit, false on rollback. If the parameter $autoComplete is true |
---|
| 2319 | monitor sql errors and commit and rollback as appropriate. Set $autoComplete to false |
---|
| 2320 | to force rollback even if no SQL error detected. |
---|
| 2321 | <p><b>FailTrans<a name="failtrans"></a>( )</b></p> |
---|
| 2322 | <p>Fail a transaction started with StartTrans(). The rollback will only occur when |
---|
| 2323 | CompleteTrans() is called. |
---|
| 2324 | <p><b>HasFailedTrans<a name="hasfailedtrans"></a>( )</b></p> |
---|
| 2325 | <p>Check whether smart transaction has failed, |
---|
| 2326 | eg. returns true if there was an error in SQL execution or FailTrans() was called. |
---|
| 2327 | If not within smart transaction, returns false. |
---|
| 2328 | <p><b>BeginTrans<a name="begintrans"></a>( )</b></p> |
---|
| 2329 | <p>Begin a transaction. Turns off autoCommit. Returns true if successful. Some |
---|
| 2330 | databases will always return false if transaction support is not available. |
---|
| 2331 | Any open transactions will be rolled back when the connection is closed. Among the |
---|
| 2332 | databases that support transactions are Oracle, PostgreSQL, Interbase, MSSQL, certain |
---|
| 2333 | versions of MySQL, DB2, Informix, Sybase, etc.</p> |
---|
| 2334 | <p>Note that <a href=#starttrans>StartTrans()</a> and CompleteTrans() is a superior method of |
---|
| 2335 | handling transactions, available since ADOdb 3.40. For a explanation, see the <a href=#starttrans>StartTrans()</a> documentation. |
---|
| 2336 | |
---|
| 2337 | <p>You can also use the ADOdb <a href=#errorhandling>error handler</a> to die |
---|
| 2338 | and rollback your transactions for you transparently. Some buggy database extensions |
---|
| 2339 | are known to commit all outstanding tranasactions, so you might want to explicitly |
---|
| 2340 | do a $DB->RollbackTrans() in your error handler for safety. |
---|
| 2341 | <h4>Detecting Transactions</h4> |
---|
| 2342 | <p>Since ADOdb 2.50, you are able to detect when you are inside a transaction. Check |
---|
| 2343 | that $connection->transCnt > 0. This variable is incremented whenever BeginTrans() is called, |
---|
| 2344 | and decremented whenever RollbackTrans() or CommitTrans() is called. |
---|
| 2345 | <p><b>CommitTrans<a name="committrans"></a>($ok=true)</b></p> |
---|
| 2346 | <p>End a transaction successfully. Returns true if successful. If the database |
---|
| 2347 | does not support transactions, will return true also as data is always committed. |
---|
| 2348 | </p> |
---|
| 2349 | <p>If you pass the parameter $ok=false, the data is rolled back. See example in |
---|
| 2350 | BeginTrans().</p> |
---|
| 2351 | <p><b>RollbackTrans<a name="rollbacktrans"></a>( )</b></p> |
---|
| 2352 | <p>End a transaction, rollback all changes. Returns true if successful. If the |
---|
| 2353 | database does not support transactions, will return false as data is never rollbacked. |
---|
| 2354 | </p> |
---|
| 2355 | </font><font color="#000000"> |
---|
| 2356 | <p><b>GetAssoc<a name=getassoc1></a>($sql,$inputarr=false,$force_array=false,$first2cols=false)</b></p> |
---|
| 2357 | <p>Returns an associative array for the given query $sql with optional bind parameters |
---|
| 2358 | in $inputarr. If the number of columns returned is greater to two, a 2-dimensional |
---|
| 2359 | array is returned, with the first column of the recordset becomes the keys |
---|
| 2360 | to the rest of the rows. If the columns is equal to two, a 1-dimensional array |
---|
| 2361 | is created, where the the keys directly map to the values (unless $force_array |
---|
| 2362 | is set to true, when an array is created for each value). |
---|
| 2363 | <p> <font color="#000000">Examples:<a name=getassocex></a></font></p> |
---|
| 2364 | </font> |
---|
| 2365 | <p><font color="#000000">We have the following data in a recordset:</font></p> |
---|
| 2366 | <p><font color="#000000">row1: Apple, Fruit, Edible<br> |
---|
| 2367 | row2: Cactus, Plant, Inedible<br> |
---|
| 2368 | row3: Rose, Flower, Edible</font></p> |
---|
| 2369 | <p><font color="#000000">GetAssoc will generate the following 2-dimensional associative |
---|
| 2370 | array:</font></p> |
---|
| 2371 | <p><font color="#000000">Apple => array[Fruit, Edible]<br> |
---|
| 2372 | Cactus => array[Plant, Inedible]<br> |
---|
| 2373 | Rose => array[Flower,Edible]</font></p> |
---|
| 2374 | <p><font color="#000000">If the dataset is:</font></p> |
---|
| 2375 | <p><font color="#000000"><font color="#000000"><font color="#000000">row1: Apple, |
---|
| 2376 | Fruit<br> |
---|
| 2377 | row2: Cactus, Plant<br> |
---|
| 2378 | row3: Rose, Flower</font> </font></font></p> |
---|
| 2379 | <p><font color="#000000"><font color="#000000">GetAssoc will generate the following |
---|
| 2380 | 1-dimensional associative array (with $force_array==false):</font></font></p> |
---|
| 2381 | <p><font color="#000000">Apple => Fruit</font><br> |
---|
| 2382 | Cactus=>Plant<br> |
---|
| 2383 | Rose=>Flower <font color="#000000"><font color="#000000"> </font></font><font color="#000000"><font color="#000000"></font></font></p> |
---|
| 2384 | <p><font color="#000000">The function returns:</font></p> |
---|
| 2385 | <p><font color="#000000">The associative array, or false if an error occurs.</font></p> |
---|
| 2386 | <font color="#000000"> |
---|
| 2387 | <p><b>CacheGetAssoc<a name="cachegetassoc"></a>([$secs2cache,] $sql,$inputarr=false,$force_array=false,$first2cols=false)</b></p> |
---|
| 2388 | </font><font color="#000000"> |
---|
| 2389 | <p>Caching version of <a href=#getassoc1>GetAssoc</a> function above. |
---|
| 2390 | <p><b>GetOne<a name="getone"></a>($sql,$inputarr=false)</b></p> |
---|
| 2391 | <p>Executes the SQL and returns the first field of the first row. The recordset |
---|
| 2392 | and remaining rows are discarded for you automatically. If an error occur, false |
---|
| 2393 | is returned.</p> |
---|
| 2394 | <p><b>GetRow<a name="getrow"></a>($sql,$inputarr=false)</b></p> |
---|
| 2395 | <p>Executes the SQL and returns the first row as an array. The recordset and remaining |
---|
| 2396 | rows are discarded for you automatically. If an error occurs, false is returned.</p> |
---|
| 2397 | <p><b>GetAll<a name="getall"></a>($sql)</b></p> |
---|
| 2398 | </font> |
---|
| 2399 | <p><font color="#000000">Executes the SQL and returns the all the rows as a 2-dimensional |
---|
| 2400 | array. The recordset is discarded for you automatically. If an error occurs, |
---|
| 2401 | false is returned.</font></p> |
---|
| 2402 | <p><b>GetCol<a name="getcol"></a>($sql,$inputarr=false,$trim=false)</b></p> |
---|
| 2403 | |
---|
| 2404 | <p><font color="#000000">Executes the SQL and returns all elements of the first column as a |
---|
| 2405 | 1-dimensional array. The recordset is discarded for you automatically. If an error occurs, |
---|
| 2406 | false is returned.</font></p> |
---|
| 2407 | <p><font color="#000000"><b>CacheGetOne<a name="cachegetone"></a>([$secs2cache,] |
---|
| 2408 | $sql,$inputarr=false), CacheGetRow<a name="cachegetrow"></a>([$secs2cache,] $sql,$inputarr=false), CacheGetAll<a name="cachegetall"></a>([$secs2cache,] |
---|
| 2409 | $sql,$inputarr=false), CacheGetCol<a name="cachegetcol"></a>([$secs2cache,] |
---|
| 2410 | $sql,$inputarr=false,$trim=false)</b></font></p> |
---|
| 2411 | <font color="#000000"> |
---|
| 2412 | <p>Similar to above Get* functions, except that the recordset is serialized and |
---|
| 2413 | cached in the $ADODB_CACHE_DIR directory for $secs2cache seconds. Good for speeding |
---|
| 2414 | up queries on rarely changing data. Note that the $secs2cache parameter is optional. |
---|
| 2415 | If omitted, we use the value in $connection->cacheSecs (default is 3600 seconds, |
---|
| 2416 | or 1 hour).</p> |
---|
| 2417 | <p><b>Prepare<a name="prepare"></a>($sql )</b></p> |
---|
| 2418 | </font> |
---|
| 2419 | <p><font color="#000000">Prepares (compiles) an SQL query for repeated execution. Bind parameters |
---|
| 2420 | are denoted by ?, except for the oci8 driver, which uses the traditional Oracle :varname |
---|
| 2421 | convention. |
---|
| 2422 | </font></p> |
---|
| 2423 | <p><font color="#000000">Returns an array containing the original sql statement |
---|
| 2424 | in the first array element; the remaining elements of the array are driver dependent. |
---|
| 2425 | If there is an error, or we are emulating Prepare( ), we return the original |
---|
| 2426 | $sql string. This is because all error-handling has been centralized in Execute( |
---|
| 2427 | ).</font></p> |
---|
| 2428 | <p><font color="#000000">Prepare( ) cannot be used with functions that use SQL |
---|
| 2429 | query rewriting techniques, e.g. PageExecute( ) and SelectLimit( ).</font></p> |
---|
| 2430 | <p>Example:</p> |
---|
| 2431 | <pre><font color="#000000">$stmt = $DB->Prepare('insert into table (col1,col2) values (?,?)'); |
---|
| 2432 | for ($i=0; $i < $max; $i++)<br></font> $DB-><font color="#000000">Execute($stmt,array((string) rand(), $i)); |
---|
| 2433 | </font></pre> |
---|
| 2434 | <font color="#000000"> |
---|
| 2435 | <p>Also see InParameter(), OutParameter() and PrepareSP() below. Only supported internally by interbase, |
---|
| 2436 | oci8 and selected ODBC-based drivers, otherwise it is emulated. There is no |
---|
| 2437 | performance advantage to using Prepare() with emulation. |
---|
| 2438 | <p> Important: Due to limitations or bugs in PHP, if you are getting errors when |
---|
| 2439 | you using prepared queries, try setting $ADODB_COUNTRECS = false before preparing. |
---|
| 2440 | This behaviour has been observed with ODBC. |
---|
| 2441 | <p><b>IfNull<a name="ifnull"></a>($field, $nullReplacementValue)</b></p> |
---|
| 2442 | <p>Portable IFNULL function (NVL in Oracle). Returns a string that represents |
---|
| 2443 | the function that checks whether a $field is null for the given database, and |
---|
| 2444 | if null, change the value returned to $nullReplacementValue. Eg.</p> |
---|
| 2445 | <pre>$sql = <font color="#993300">'SELECT '</font>.$db->IfNull('name', <font color="#993300">"'- unknown -'"</font>).<font color="#993300"> ' FROM table'</font>;</pre> |
---|
| 2446 | |
---|
| 2447 | <p><b>length<a name="length"></a></b></p> |
---|
| 2448 | <p>This is not a function, but a property. Some databases have "length" and others "len" |
---|
| 2449 | as the function to measure the length of a string. To use this property: |
---|
| 2450 | <pre> |
---|
| 2451 | $sql = <font color="#993300">"SELECT "</font>.$db->length.<font color="#993300">"(field) from table"</font>; |
---|
| 2452 | $rs = $db->Execute($sql); |
---|
| 2453 | </pre> |
---|
| 2454 | |
---|
| 2455 | <p><b>random<a name="random"></a></b></p> |
---|
| 2456 | <p>This is not a function, but a property. This is a string that holds the sql to |
---|
| 2457 | generate a random number between 0.0 and 1.0 inclusive. |
---|
| 2458 | |
---|
| 2459 | <p><b>substr<a name="substr"></a></b></p> |
---|
| 2460 | <p>This is not a function, but a property. Some databases have "substr" and others "substring" |
---|
| 2461 | as the function to retrieve a sub-string. To use this property: |
---|
| 2462 | <pre> |
---|
| 2463 | $sql = <font color="#993300">"SELECT "</font>.$db->substr.<font color="#993300">"(field, $offset, $length) from table"</font>; |
---|
| 2464 | $rs = $db->Execute($sql); |
---|
| 2465 | </pre> |
---|
| 2466 | <p>For all databases, the 1st parameter of <i>substr</i> is the field, the 2nd is the |
---|
| 2467 | offset (1-based) to the beginning of the sub-string, and the 3rd is the length of the sub-string. |
---|
| 2468 | |
---|
| 2469 | |
---|
| 2470 | <p><b>Param<a name="param"></a>($name)</b></p> |
---|
| 2471 | <p>Generates a bind placeholder portably. For most databases, the bind placeholder |
---|
| 2472 | is "?". However some databases use named bind parameters such as Oracle, eg |
---|
| 2473 | ":somevar". This allows us to portably define an SQL statement with bind parameters: |
---|
| 2474 | <pre><font color="#000000">$sql = <font color="#993300">'insert into table (col1,col2) values ('</font>.$DB->Param('a').<font color="#993300">','</font>.$DB->Param('b').<font color="#993300">')'</font>; |
---|
| 2475 | <font color="#006600"># generates 'insert into table (col1,col2) values (?,?)' |
---|
| 2476 | # or 'insert into table (col1,col2) values (:a,:b)</font>' |
---|
| 2477 | $stmt = $DB->Prepare($sql); |
---|
| 2478 | $stmt = $DB->Execute($stmt,array('one','two')); |
---|
| 2479 | </font></pre> |
---|
| 2480 | <font color="#000000"> |
---|
| 2481 | <p></p> |
---|
| 2482 | <p><b>PrepareSP</b><b><a name="preparesp"></a></b><b>($sql, $cursor=false )</b></p> |
---|
| 2483 | <p>When calling stored procedures in mssql and oci8 (oracle), and you might want |
---|
| 2484 | to directly bind to parameters that return values, or for special LOB handling. |
---|
| 2485 | PrepareSP() allows you to do so. |
---|
| 2486 | <p>Returns the same array or $sql string as Prepare( ) above. If you do not need |
---|
| 2487 | to bind to return values, you should use Prepare( ) instead.</p> |
---|
| 2488 | <p>The 2nd parameter, $cursor is not used except with oci8. Setting it to true will |
---|
| 2489 | force OCINewCursor to be called; this is to support output REF CURSORs. |
---|
| 2490 | <p>For examples of usage of PrepareSP( ), see InParameter( ) below. |
---|
| 2491 | <p>Note: in the mssql driver, preparing stored procedures requires a special function |
---|
| 2492 | call, mssql_init( ), which is called by this function. PrepareSP( ) is available |
---|
| 2493 | in all other drivers, and is emulated by calling Prepare( ). </p> |
---|
| 2494 | <p><b> InParameter<a name="inparameter"></a>($stmt, $var, $name, |
---|
| 2495 | $maxLen = 4000, $type = false )</b></p> |
---|
| 2496 | Binds a PHP variable as input to a stored procedure variable. The parameter <i>$stmt</i> |
---|
| 2497 | is the value returned by PrepareSP(), <i>$var</i> is the PHP variable you want to bind, $name |
---|
| 2498 | is the name of the stored procedure variable. Optional is <i>$maxLen</i>, the maximum length of the |
---|
| 2499 | data to bind, and $type which is database dependant. |
---|
| 2500 | Consult <a href=http://php.net/mssql_bind>mssql_bind</a> and <a href=http://php.net/ocibindbyname>ocibindbyname</a> docs |
---|
| 2501 | at php.net for more info on legal values for $type. |
---|
| 2502 | <p> |
---|
| 2503 | InParameter() is a wrapper function that calls Parameter() with $isOutput=false. |
---|
| 2504 | The advantage of this function is that it is self-documenting, because |
---|
| 2505 | the $isOutput parameter is no longer needed. Only for mssql |
---|
| 2506 | and oci8 currently. |
---|
| 2507 | <p>Here is an example using oci8: |
---|
| 2508 | <pre><font color="green"># For oracle, Prepare and PrepareSP are identical</font> |
---|
| 2509 | $stmt = $db->PrepareSP( |
---|
| 2510 | <font color="#993300">"declare RETVAL integer; |
---|
| 2511 | begin |
---|
| 2512 | :RETVAL := </font><font color="#993300">SP_RUNSOMETHING</font><font color="#993300">(:myid,:group); |
---|
| 2513 | end;"</font>); |
---|
| 2514 | $db->InParameter($stmt,$id,'myid'); |
---|
| 2515 | $db->InParameter($stmt,$group,'group',64); |
---|
| 2516 | $db->OutParameter($stmt,$ret,'RETVAL');<br>$db->Execute($stmt); |
---|
| 2517 | </pre> |
---|
| 2518 | <p> The same example using mssql:</p> |
---|
| 2519 | </font> |
---|
| 2520 | <pre><font color="#000000"><font color="green"># @RETVAL = SP_RUNSOMETHING @myid,@group</font> |
---|
| 2521 | $stmt = $db->PrepareSP(<font color="#993333">'<font color="#993300">SP_RUNSOMETHING</font>'</font>); <br><font color="green"># note that the parameter name does not have @ in front!</font> |
---|
| 2522 | $db->InParameter($stmt,$id,'myid'); |
---|
| 2523 | $db->InParameter($stmt,$group,'group',64); |
---|
| 2524 | <font color="green"># return value in mssql - RETVAL is hard-coded name</font> |
---|
| 2525 | $db->OutParameter($stmt,$ret,'RETVAL'); |
---|
| 2526 | $db->Execute($stmt); </font></pre> |
---|
| 2527 | |
---|
| 2528 | <p>Note that the only difference between the oci8 and mssql implementations is $sql.</p> |
---|
| 2529 | <p> |
---|
| 2530 | If $type parameter is set to false, in mssql, $type will be dynamicly determined |
---|
| 2531 | based on the type of the PHP variable passed <font face="Courier New, Courier, mono">(string |
---|
| 2532 | => SQLCHAR, boolean =>SQLINT1, integer =>SQLINT4 or float/double=>SQLFLT8)</font>. |
---|
| 2533 | <p> |
---|
| 2534 | In oci8, $type can be set to OCI_B_FILE (Binary-File), OCI_B_CFILE (Character-File), |
---|
| 2535 | OCI_B_CLOB (Character-LOB), OCI_B_BLOB (Binary-LOB) and OCI_B_ROWID (ROWID). To |
---|
| 2536 | pass in a null, use<font face="Courier New, Courier, mono"> $db->Parameter($stmt, |
---|
| 2537 | $null=null, 'param')</font>. |
---|
| 2538 | <p><b> OutParameter<a name="outparameter"></a>($stmt, $var, $name, |
---|
| 2539 | $maxLen = 4000, $type = false )</b></p> |
---|
| 2540 | Binds a PHP variable as output from a stored procedure variable. The parameter <i>$stmt</i> |
---|
| 2541 | is the value returned by PrepareSP(), <i>$var</i> is the PHP variable you want to bind, <i>$name</i> |
---|
| 2542 | is the name of the stored procedure variable. Optional is <i>$maxLen</i>, the maximum length of the |
---|
| 2543 | data to bind, and <i>$type</i> which is database dependant. |
---|
| 2544 | <p> |
---|
| 2545 | OutParameter() is a wrapper function that calls Parameter() with $isOutput=true. |
---|
| 2546 | The advantage of this function is that it is self-documenting, because |
---|
| 2547 | the $isOutput parameter is no longer needed. Only for mssql |
---|
| 2548 | and oci8 currently. |
---|
| 2549 | <p> |
---|
| 2550 | For an example, see <a href=#inparameter>InParameter</a>. |
---|
| 2551 | |
---|
| 2552 | <p><b> Parameter<a name="parameter"></a>($stmt, $var, $name, $isOutput=false, |
---|
| 2553 | $maxLen = 4000, $type = false )</b></p> |
---|
| 2554 | <p>Note: This function is deprecated, because of the new InParameter() and OutParameter() functions. |
---|
| 2555 | These are superior because they are self-documenting, unlike Parameter(). |
---|
| 2556 | <p>Adds a bind parameter suitable for return values or special data handling (eg. |
---|
| 2557 | LOBs) after a statement has been prepared using PrepareSP(). Only for mssql |
---|
| 2558 | and oci8 currently. The parameters are:<br> |
---|
| 2559 | <br> |
---|
| 2560 | $<i><b>stmt</b></i> Statement returned by Prepare() or PrepareSP().<br> |
---|
| 2561 | $<i><b>var</b></i> PHP variable to bind to. Make sure you pre-initialize it!<br> |
---|
| 2562 | $<i><b>name</b></i> Name of stored procedure variable name to bind to.<br> |
---|
| 2563 | [$<i><b>isOutput</b></i>] Indicates direction of parameter 0/false=IN 1=OUT |
---|
| 2564 | 2= IN/OUT. This is ignored in oci8 as this driver auto-detects the direction.<br> |
---|
| 2565 | [$<b>maxLen</b>] Maximum length of the parameter variable.<br> |
---|
| 2566 | [$<b>type</b>] Consult <a href="http://php.net/mssql_bind">mssql_bind</a> and |
---|
| 2567 | <a href="http://php.net/ocibindbyname">ocibindbyname</a> docs at php.net for |
---|
| 2568 | more info on legal values for type.</p> |
---|
| 2569 | <p>Lastly, in oci8, bind parameters can be reused without calling PrepareSP( ) |
---|
| 2570 | or Parameters again. This is not possible with mssql. An oci8 example:</p> |
---|
| 2571 | <pre>$id = 0; $i = 0; |
---|
| 2572 | $stmt = $db->PrepareSP( <font color="#993300">"update table set val=:i where id=:id"</font>); |
---|
| 2573 | $db->Parameter($stmt,$id,'id'); |
---|
| 2574 | $db->Parameter($stmt,$i, 'i'); |
---|
| 2575 | for ($cnt=0; $cnt < 1000; $cnt++) { |
---|
| 2576 | $id = $cnt; <br> $i = $cnt * $cnt; <font color="green"># works with oci8!</font> |
---|
| 2577 | $db->Execute($stmt); <br>}</pre> |
---|
| 2578 | <p><b>Bind<a name="bind"></a>($stmt, $var, $size=4001, $type=false, $name=false)</b></p> |
---|
| 2579 | </font> |
---|
| 2580 | <p><font color="#000000">This is a low-level function supported only by the oci8 |
---|
| 2581 | driver. <b>Avoid using</b> unless you only want to support Oracle. The Parameter( |
---|
| 2582 | ) function is the recommended way to go with bind variables.</font></p> |
---|
| 2583 | <p><font color="#000000">Bind( ) allows you to use bind variables in your sql |
---|
| 2584 | statement. This binds a PHP variable to a name defined in an Oracle sql statement |
---|
| 2585 | that was previously prepared using Prepare(). Oracle named variables begin with |
---|
| 2586 | a colon, and ADOdb requires the named variables be called :0, :1, :2, :3, etc. |
---|
| 2587 | The first invocation of Bind() will match :0, the second invocation will match |
---|
| 2588 | :1, etc. Binding can provide 100% speedups for insert, select and update statements. |
---|
| 2589 | </font></p> |
---|
| 2590 | <p>The other variables, $size sets the buffer size for data storage, $type is |
---|
| 2591 | the optional descriptor type OCI_B_FILE (Binary-File), OCI_B_CFILE (Character-File), |
---|
| 2592 | OCI_B_CLOB (Character-LOB), OCI_B_BLOB (Binary-LOB) and OCI_B_ROWID (ROWID). |
---|
| 2593 | Lastly, instead of using the default :0, :1, etc names, you can define your |
---|
| 2594 | own bind-name using $name. |
---|
| 2595 | <p><font color="#000000">The following example shows 3 bind variables being used: |
---|
| 2596 | p1, p2 and p3. These variables are bound to :0, :1 and :2.</font></p> |
---|
| 2597 | <pre>$stmt = $DB->Prepare("insert into table (col0, col1, col2) values (:0, :1, :2)"); |
---|
| 2598 | $DB->Bind($stmt, $p1); |
---|
| 2599 | $DB->Bind($stmt, $p2); |
---|
| 2600 | $DB->Bind($stmt, $p3); |
---|
| 2601 | for ($i = 0; $i < $max; $i++) { |
---|
| 2602 | $p1 = ?; $p2 = ?; $p3 = ?; |
---|
| 2603 | $DB->Execute($stmt); |
---|
| 2604 | }</pre> |
---|
| 2605 | <p>You can also use named variables:</p> |
---|
| 2606 | <pre> |
---|
| 2607 | $stmt = $DB->Prepare("insert into table (col0, col1, col2) values (:name0, :name1, :name2)"); |
---|
| 2608 | $DB->Bind($stmt, $p1, "name0"); |
---|
| 2609 | $DB->Bind($stmt, $p2, "name1"); |
---|
| 2610 | $DB->Bind($stmt, $p3, "name2"); |
---|
| 2611 | for ($i = 0; $i < $max; $i++) { |
---|
| 2612 | $p1 = ?; $p2 = ?; $p3 = ?; |
---|
| 2613 | $DB->Execute($stmt); |
---|
| 2614 | }</pre> |
---|
| 2615 | <p><b>LogSQL($enable=true)<a name=logsql></a></b></p> |
---|
| 2616 | Call this method to install a SQL logging and timing function (using fnExecute). |
---|
| 2617 | Then all SQL statements are logged into an adodb_logsql table in a database. If |
---|
| 2618 | the adodb_logsql table does not exist, ADOdb will create the table if you have |
---|
| 2619 | the appropriate permissions. Returns the previous logging value (true for enabled, |
---|
| 2620 | false for disabled). Here are samples of the DDL for selected databases: |
---|
| 2621 | <p> |
---|
| 2622 | <pre> |
---|
| 2623 | <b>mysql:</b> |
---|
| 2624 | CREATE TABLE adodb_logsql ( |
---|
| 2625 | created datetime NOT NULL, |
---|
| 2626 | sql0 varchar(250) NOT NULL, |
---|
| 2627 | sql1 text NOT NULL, |
---|
| 2628 | params text NOT NULL, |
---|
| 2629 | tracer text NOT NULL, |
---|
| 2630 | timer decimal(16,6) NOT NULL |
---|
| 2631 | ) |
---|
| 2632 | |
---|
| 2633 | <b>postgres:</b> |
---|
| 2634 | CREATE TABLE adodb_logsql ( |
---|
| 2635 | created timestamp NOT NULL, |
---|
| 2636 | sql0 varchar(250) NOT NULL, |
---|
| 2637 | sql1 text NOT NULL, |
---|
| 2638 | params text NOT NULL, |
---|
| 2639 | tracer text NOT NULL, |
---|
| 2640 | timer decimal(16,6) NOT NULL |
---|
| 2641 | ) |
---|
| 2642 | |
---|
| 2643 | <b>mssql:</b> |
---|
| 2644 | CREATE TABLE adodb_logsql ( |
---|
| 2645 | created datetime NOT NULL, |
---|
| 2646 | sql0 varchar(250) NOT NULL, |
---|
| 2647 | sql1 varchar(4000) NOT NULL, |
---|
| 2648 | params varchar(3000) NOT NULL, |
---|
| 2649 | tracer varchar(500) NOT NULL, |
---|
| 2650 | timer decimal(16,6) NOT NULL |
---|
| 2651 | ) |
---|
| 2652 | |
---|
| 2653 | <b>oci8:</b> |
---|
| 2654 | CREATE TABLE adodb_logsql ( |
---|
| 2655 | created date NOT NULL, |
---|
| 2656 | sql0 varchar(250) NOT NULL, |
---|
| 2657 | sql1 varchar(4000) NOT NULL, |
---|
| 2658 | params varchar(4000), |
---|
| 2659 | tracer varchar(4000), |
---|
| 2660 | timer decimal(16,6) NOT NULL |
---|
| 2661 | ) |
---|
| 2662 | </pre> |
---|
| 2663 | Usage: |
---|
| 2664 | <pre> |
---|
| 2665 | $conn->LogSQL(); // turn on logging |
---|
| 2666 | : |
---|
| 2667 | $conn->Execute(...); |
---|
| 2668 | : |
---|
| 2669 | $conn->LogSQL(false); // turn off logging |
---|
| 2670 | |
---|
| 2671 | # output summary of SQL logging results |
---|
| 2672 | $perf = NewPerfMonitor($conn); |
---|
| 2673 | echo $perf->SuspiciousSQL(); |
---|
| 2674 | echo $perf->ExpensiveSQL(); |
---|
| 2675 | </pre> |
---|
| 2676 | <p>One limitation of logging is that rollback also prevents SQL from being logged. |
---|
| 2677 | <p> |
---|
| 2678 | If you prefer to use another name for the table used to store the SQL, you can override it by calling |
---|
| 2679 | adodb_perf::table($tablename), where $tablename is the new table name (you will still need to manually |
---|
| 2680 | create the table yourself). An example: |
---|
| 2681 | <pre> |
---|
| 2682 | include('adodb.inc.php'); |
---|
| 2683 | include('adodb-perf.inc.php'); |
---|
| 2684 | adodb_perf::table('my_logsql_table'); |
---|
| 2685 | </pre> |
---|
| 2686 | Also see <a href=docs-perf.htm>Performance Monitor</a>. |
---|
| 2687 | <p><font color="#000000"><b>fnExecute and fnCacheExecute properties<a name="fnexecute" id="fnexecute"></a></b></font></p> |
---|
| 2688 | <p>These two properties allow you to define bottleneck functions for all sql statements |
---|
| 2689 | processed by ADOdb. This allows you to perform statistical analysis and query-rewriting |
---|
| 2690 | of your sql. |
---|
| 2691 | <p><b>Examples of fnExecute</b></p> |
---|
| 2692 | <p>Here is an example of using fnExecute, to count all cached queries and non-cached |
---|
| 2693 | queries, you can do this:</p> |
---|
| 2694 | <pre><font color="#006600"># $db is the connection object</font> |
---|
| 2695 | function CountExecs($db, $sql, $inputarray) |
---|
| 2696 | { |
---|
| 2697 | global $EXECS; |
---|
| 2698 | |
---|
| 2699 | if (!is_array(inputarray)) $EXECS++; |
---|
| 2700 | <font color="#006600"># handle 2-dimensional input arrays</font> |
---|
| 2701 | else if (is_array(reset($inputarray))) $EXECS += sizeof($inputarray); |
---|
| 2702 | else $EXECS++; |
---|
| 2703 | } |
---|
| 2704 | |
---|
| 2705 | <font color="#006600"># $db is the connection object</font> |
---|
| 2706 | function CountCachedExecs($db, $secs2cache, $sql, $inputarray) |
---|
| 2707 | {<br>global $CACHED; $CACHED++; |
---|
| 2708 | } |
---|
| 2709 | <br>$db = NewADOConnection('mysql'); |
---|
| 2710 | $db->Connect(...); |
---|
| 2711 | $db-><strong>fnExecute</strong> = 'CountExecs'; |
---|
| 2712 | $db-><strong>fnCacheExecute</strong> = 'CountCachedExecs'; |
---|
| 2713 | : |
---|
| 2714 | :<br><font color="#006600"># After many sql statements:</font>` |
---|
| 2715 | printf("<p>Total queries=%d; total cached=%d</p>",$EXECS+$CACHED, $CACHED); |
---|
| 2716 | </pre> |
---|
| 2717 | <p>The fnExecute function is called before the sql is parsed and executed, so |
---|
| 2718 | you can perform a query rewrite. If you are passing in a prepared statement, |
---|
| 2719 | then $sql is an array (see <a href="#prepare">Prepare</a>). The fnCacheExecute |
---|
| 2720 | function is only called if the recordset returned was cached.<font color="#000000"> |
---|
| 2721 | The function parameters match the Execute and CacheExecute functions respectively, |
---|
| 2722 | except that $this (the connection object) is passed as the first parameter.</font></p> |
---|
| 2723 | <p>Since ADOdb 3.91, the behaviour of fnExecute varies depending on whether the |
---|
| 2724 | defined function returns a value. If it does not return a value, then the $sql |
---|
| 2725 | is executed as before. This is useful for query rewriting or counting sql queries. |
---|
| 2726 | <p> On the other hand, you might want to replace the Execute function with one |
---|
| 2727 | of your own design. If this is the case, then have your function return a value. |
---|
| 2728 | If a value is returned, that value is returned immediately, without any further |
---|
| 2729 | processing. This is used internally by ADOdb to implement LogSQL() functionality. |
---|
| 2730 | <p> <font color="#000000"> |
---|
| 2731 | <hr> |
---|
| 2732 | <h3>ADOConnection Utility Functions</h3> |
---|
| 2733 | <p><b>BlankRecordSet<a name="blankrecordset"></a>([$queryid])</b></p> |
---|
| 2734 | <p>No longer available - removed since 1.99.</p> |
---|
| 2735 | <p><b>Concat<a name="concat"></a>($s1,$s2,....)</b></p> |
---|
| 2736 | <p>Generates the sql string used to concatenate $s1, $s2, etc together. Uses the |
---|
| 2737 | string in the concat_operator field to generate the concatenation. Override |
---|
| 2738 | this function if a concatenation operator is not used, eg. MySQL.</p> |
---|
| 2739 | <p>Returns the concatenated string.</p> |
---|
| 2740 | <p><b>DBDate<a name="dbdate"></a>($date)</b></p> |
---|
| 2741 | <p>Format the $<b>date</b> in the format the database accepts. This is used in |
---|
| 2742 | INSERT/UPDATE statements; for SELECT statements, use <a href="#sqldate">SQLDate</a>. |
---|
| 2743 | The $<b>date</b> parameter can be a Unix integer timestamp or an ISO format |
---|
| 2744 | Y-m-d. Uses the fmtDate field, which holds the format to use. If null or false |
---|
| 2745 | or '' is passed in, it will be converted to an SQL null.</p> |
---|
| 2746 | <p>Returns the date as a quoted string.</p> |
---|
| 2747 | <p><b>DBTimeStamp<a name="dbtimestamp"></a>($ts)</b></p> |
---|
| 2748 | <p>Format the timestamp $<b>ts</b> in the format the database accepts; this can |
---|
| 2749 | be a Unix integer timestamp or an ISO format Y-m-d H:i:s. Uses the fmtTimeStamp |
---|
| 2750 | field, which holds the format to use. If null or false or '' is passed in, it |
---|
| 2751 | will be converted to an SQL null.</p> |
---|
| 2752 | <p>Returns the timestamp as a quoted string.</p> |
---|
| 2753 | <p><b>qstr<a name="qstr"></a>($s,[$magic_quotes_enabled</b>=false]<b>)</b></p> |
---|
| 2754 | <p>Quotes a string to be sent to the database. The $<b>magic_quotes_enabled</b> |
---|
| 2755 | parameter may look funny, but the idea is if you are quoting a string extracted |
---|
| 2756 | from a POST/GET variable, then pass get_magic_quotes_gpc() as the second parameter. |
---|
| 2757 | This will ensure that the variable is not quoted twice, once by <i>qstr</i> |
---|
| 2758 | and once by the <i>magic_quotes_gpc</i>.</p> |
---|
| 2759 | <p>Eg.<font face="Courier New, Courier, mono"> $s = $db->qstr(HTTP_GET_VARS['name'],get_magic_quotes_gpc());</font></p> |
---|
| 2760 | <p>Returns the quoted string.</p> |
---|
| 2761 | <p><b>Quote<a name="quote"></a>($s)</b></p> |
---|
| 2762 | <p>Quotes the string $s, escaping the database specific quote character as appropriate. |
---|
| 2763 | Formerly checked magic quotes setting, but this was disabled since 3.31 for |
---|
| 2764 | compatibility with PEAR DB. |
---|
| 2765 | <p><b>Affected_Rows<a name="affected_rows"></a>( )</b></p> |
---|
| 2766 | <p>Returns the number of rows affected by a update or delete statement. Returns |
---|
| 2767 | false if function not supported.</p> |
---|
| 2768 | <p>Not supported by interbase/firebird currently. </p> |
---|
| 2769 | <p><b>Insert_ID<a name="inserted_id"></a>( )</b></p> |
---|
| 2770 | <p>Returns the last autonumbering ID inserted. Returns false if function not supported. |
---|
| 2771 | </p> |
---|
| 2772 | <p>Only supported by databases that support auto-increment or object id's, such |
---|
| 2773 | as PostgreSQL, MySQL and MS SQL Server currently. PostgreSQL returns the OID, which |
---|
| 2774 | can change on a database reload.</p> |
---|
| 2775 | <p><b>RowLock<a name="rowlock"></a>($table,$where)</b></p> |
---|
| 2776 | <p>Lock a table row for the duration of a transaction. For example to lock record $id in table1: |
---|
| 2777 | <pre> |
---|
| 2778 | $DB->StartTrans(); |
---|
| 2779 | $DB->RowLock("table1","rowid=$id"); |
---|
| 2780 | $DB->Execute($sql1); |
---|
| 2781 | $DB->Execute($sql2); |
---|
| 2782 | $DB->CompleteTrans(); |
---|
| 2783 | </pre> |
---|
| 2784 | <p>Supported in db2, interbase, informix, mssql, oci8, postgres, sybase. |
---|
| 2785 | <p><b>MetaDatabases<a name="metadatabases"></a>()</b></p> |
---|
| 2786 | <p>Returns a list of databases available on the server as an array. You have to |
---|
| 2787 | connect to the server first. Only available for ODBC, MySQL and ADO.</p> |
---|
| 2788 | <p><b>MetaTables<a name="metatables"></a>($ttype = false, $showSchema = false, |
---|
| 2789 | $mask=false)</b></p> |
---|
| 2790 | <p>Returns an array of tables and views for the current database as an array. |
---|
| 2791 | The array should exclude system catalog tables if possible. To only show tables, |
---|
| 2792 | use $db->MetaTables('TABLES'). To show only views, use $db->MetaTables('VIEWS'). |
---|
| 2793 | The $showSchema parameter currently works only for DB2, and when set to true, |
---|
| 2794 | will add the schema name to the table, eg. "SCHEMA.TABLE". </p> |
---|
| 2795 | <p>You can define a mask for matching. For example, setting $mask = 'TMP%' will |
---|
| 2796 | match all tables that begin with 'TMP'. Currently only mssql, oci8, odbc_mssql |
---|
| 2797 | and postgres* support $mask. |
---|
| 2798 | <p><b>MetaColumns<a name="metacolumns"></a>($table,$toupper=true)</b></p> |
---|
| 2799 | <p>Returns an array of ADOFieldObject's, one field object for every column of |
---|
| 2800 | $table. A field object is a class instance with (name, type, max_length) defined. |
---|
| 2801 | Currently Sybase does not recognise date types, and ADO cannot identify |
---|
| 2802 | the correct data type (so we default to varchar). |
---|
| 2803 | <p> The $toupper parameter determines whether we uppercase the table name |
---|
| 2804 | (required for some databases). |
---|
| 2805 | <p>For schema support, pass in the $table parameter, "$schema.$tablename". This is only |
---|
| 2806 | supported for selected databases. |
---|
| 2807 | <p><b>MetaColumnNames<a name="metacolumnames"></a>($table,$numericIndex=false)</b></p> |
---|
| 2808 | <p>Returns an array of column names for $table. Since ADOdb 4.22, this is an associative array, with the |
---|
| 2809 | keys in uppercase. Set $numericIndex=true if you want the old behaviour of numeric indexes (since 4.23). |
---|
| 2810 | <p> |
---|
| 2811 | e.g. array('FIELD1' => 'Field1', 'FIELD2'=>'Field2') |
---|
| 2812 | <p> |
---|
| 2813 | <p><font color="#000000"><b>MetaPrimaryKeys<a name="metaprimarykeys"></a>($table, |
---|
| 2814 | $owner=false)</b></font></font> |
---|
| 2815 | <p><font color="#000000">Returns an array containing column names that are the |
---|
| 2816 | primary keys of $table. Supported by mysql, odbc (including db2, odbc_mssql, |
---|
| 2817 | etc), mssql, postgres, interbase/firebird, oci8 currently. </font><font color="#000000"> |
---|
| 2818 | <p>Views (and some tables) have primary keys, but sometimes this information is not available from the |
---|
| 2819 | database. You can define a function ADODB_View_PrimaryKeys($databaseType, $database, $view, $owner) that |
---|
| 2820 | should return an array containing the fields that make up the primary key. If that function exists, |
---|
| 2821 | it will be called when MetaPrimaryKeys() cannot find a primary key for a table or view. |
---|
| 2822 | <pre> |
---|
| 2823 | // In this example: dbtype = 'oci8', $db = 'mydb', $view = 'dataView', $owner = false |
---|
| 2824 | function ADODB_View_PrimaryKeys($dbtype,$db,$view,$owner) |
---|
| 2825 | { |
---|
| 2826 | switch(strtoupper($view)) { |
---|
| 2827 | case 'DATAVIEW': return array('DATAID'); |
---|
| 2828 | default: return false; |
---|
| 2829 | } |
---|
| 2830 | } |
---|
| 2831 | |
---|
| 2832 | $db = NewADOConnection('oci8'); |
---|
| 2833 | $db->Connect('localhost','root','','mydb'); |
---|
| 2834 | $db->MetaPrimaryKeys('dataView'); |
---|
| 2835 | </pre> |
---|
| 2836 | <p><font color="#000000"><b>ServerInfo<a name="serverinfo" id="serverinfo"></a>($table)</b></font></font> |
---|
| 2837 | <p><font color="#000000">Returns an array of containing two elements 'description' |
---|
| 2838 | and 'version'. The 'description' element contains the string description of |
---|
| 2839 | the database. The 'version' naturally holds the version number (which is also |
---|
| 2840 | a string).</font><font color="#000000"> |
---|
| 2841 | <p><b>MetaForeignKeys<a name="metaforeignkeys"></a>($table, $owner=false, $upper=false)</b> |
---|
| 2842 | <p>Returns an associate array of foreign keys, or false if not supported. For |
---|
| 2843 | example, if table employee has a foreign key where employee.deptkey points to |
---|
| 2844 | dept_table.deptid, and employee.posn=posn_table.postionid and employee.poscategory=posn_table.category, |
---|
| 2845 | then $conn->MetaForeignKeys('employee') will return |
---|
| 2846 | <pre> |
---|
| 2847 | array( |
---|
| 2848 | 'dept_table' => array('deptkey=deptid'), |
---|
| 2849 | 'posn_table' => array('posn=positionid','poscategory=category') |
---|
| 2850 | ) |
---|
| 2851 | </pre> |
---|
| 2852 | <p>The optional schema or owner can be defined in $owner. If $upper is true, then |
---|
| 2853 | the table names (array keys) are upper-cased. |
---|
| 2854 | <hr> |
---|
| 2855 | <h2>ADORecordSet<a name="adorecordset"></a></h2> |
---|
| 2856 | <p>When an SQL statement successfully is executed by <font face="Courier New, Courier, mono">ADOConnection->Execute($sql),</font>an |
---|
| 2857 | ADORecordSet object is returned. This object contains a virtual cursor so we |
---|
| 2858 | can move from row to row, functions to obtain information about the columns |
---|
| 2859 | and column types, and helper functions to deal with formating the results to |
---|
| 2860 | show to the user.</p> |
---|
| 2861 | <h3>ADORecordSet Fields</h3> |
---|
| 2862 | <p><b>fields: </b>Array containing the current row. This is not associative, but |
---|
| 2863 | is an indexed array from 0 to columns-1. See also the function <b><a href="#fields">Fields</a></b>, |
---|
| 2864 | which behaves like an associative array.</p> |
---|
| 2865 | <p><b>dataProvider</b>: The underlying mechanism used to connect to the database. |
---|
| 2866 | Normally set to <b>native</b>, unless using <b>odbc</b> or <b>ado</b>.</p> |
---|
| 2867 | <p><b>blobSize</b>: Maximum size of a char, string or varchar object before it |
---|
| 2868 | is treated as a Blob (Blob's should be shown with textarea's). See the <a href="#metatype">MetaType</a> |
---|
| 2869 | function.</p> |
---|
| 2870 | <p><b>sql</b>: Holds the sql statement used to generate this record set.</p> |
---|
| 2871 | <p><b>canSeek</b>: Set to true if Move( ) function works.</p> |
---|
| 2872 | <p><b>EOF</b>: True if we have scrolled the cursor past the last record.</p> |
---|
| 2873 | <h3>ADORecordSet Functions</h3> |
---|
| 2874 | <p><b>ADORecordSet( )</b></p> |
---|
| 2875 | <p>Constructer. Normally you never call this function yourself.</p> |
---|
| 2876 | <p><b>GetAssoc<a name="getassoc"></a>([$force_array])</b></p> |
---|
| 2877 | <p>Generates an associative array from the recordset. Note that is this function |
---|
| 2878 | is also <a href="#getassoc1">available</a> in the connection object. More details |
---|
| 2879 | can be found there.</p> |
---|
| 2880 | </font></font><font color="#000000"><font color="#000000"> </font></font><font color="#000000"><font color="#000000"> |
---|
| 2881 | <p><b>GetArray<a name="getarray"></a>([$number_of_rows])</b></p> |
---|
| 2882 | <p>Generate a 2-dimensional array of records from the current cursor position, |
---|
| 2883 | indexed from 0 to $number_of_rows - 1. If $number_of_rows is undefined, till |
---|
| 2884 | EOF.</p> |
---|
| 2885 | <p><b>GetRows<a name="getrows"></a>([$number_of_rows])</b></p> |
---|
| 2886 | Generate a 2-dimensional array of records from the current cursor position. Synonym |
---|
| 2887 | for GetArray() for compatibility with Microsoft ADO. |
---|
| 2888 | <p> <b>GetMenu<a name="getmenu"></a>($name, [$default_str=''], [$blank1stItem=true], |
---|
| 2889 | [$multiple_select=false], [$size=0], [$moreAttr=''])</b></p> |
---|
| 2890 | <p>Generate a HTML menu (<select><option><option></select>). |
---|
| 2891 | The first column of the recordset (fields[0]) will hold the string to display |
---|
| 2892 | in the option tags. If the recordset has more than 1 column, the second column |
---|
| 2893 | (fields[1]) is the value to send back to the web server.. The menu will be given |
---|
| 2894 | the name $<i>name</i>. |
---|
| 2895 | <p> If $<i>default_str</i> is defined, then if $<i>default_str</i> == fields[0], |
---|
| 2896 | that field is selected. If $<i>blank1stItem</i> is true, the first option is |
---|
| 2897 | empty. You can also set the first option strings by setting $blank1stItem = |
---|
| 2898 | "$value:$text".</p> |
---|
| 2899 | <p>$<i>Default_str</i> can be array for a multiple select listbox.</p> |
---|
| 2900 | <p>To get a listbox, set the $<i>size</i> to a non-zero value (or pass $default_str |
---|
| 2901 | as an array). If $<i>multiple_select</i> is true then a listbox will be generated |
---|
| 2902 | with $<i>size</i> items (or if $size==0, then 5 items) visible, and we will |
---|
| 2903 | return an array to a server. Lastly use $<i>moreAttr </i> to add additional |
---|
| 2904 | attributes such as javascript or styles. </p> |
---|
| 2905 | <p>Menu Example 1: <code>GetMenu('menu1','A',true)</code> will generate a menu: |
---|
| 2906 | <select name='menu1'> |
---|
| 2907 | <option> |
---|
| 2908 | <option value=1 selected>A |
---|
| 2909 | <option value=2>B |
---|
| 2910 | <option value=3>C |
---|
| 2911 | </select> |
---|
| 2912 | for the data (A,1), (B,2), (C,3). Also see <a href="#ex5">example 5</a>.</p> |
---|
| 2913 | <p>Menu Example 2: For the same data, <code>GetMenu('menu1',array('A','B'),false)</code> |
---|
| 2914 | will generate a menu with both A and B selected: <br> |
---|
| 2915 | <select name='menu1' multiple size=3> |
---|
| 2916 | <option value=1 selected>A |
---|
| 2917 | <option value=2 selected>B |
---|
| 2918 | <option value=3>C |
---|
| 2919 | </select> |
---|
| 2920 | <p> <b>GetMenu2<a name="getmenu2"></a>($name, [$default_str=''], [$blank1stItem=true], |
---|
| 2921 | [$multiple_select=false], [$size=0], [$moreAttr=''])</b></p> |
---|
| 2922 | <p>This is nearly identical to GetMenu, except that the $<i>default_str</i> is |
---|
| 2923 | matched to fields[1] (the option values).</p> |
---|
| 2924 | <p>Menu Example 3: Given the data in menu example 2, <code>GetMenu2('menu1',array('1','2'),false)</code> |
---|
| 2925 | will generate a menu with both A and B selected in menu example 2, but this |
---|
| 2926 | time the selection is based on the 2nd column, which holds the values to return |
---|
| 2927 | to the Web server. |
---|
| 2928 | <p><b>UserDate<a name="userdate"></a>($str, [$fmt])</b></p> |
---|
| 2929 | <p>Converts the date string $<i>str</i> to another format. The date format is Y-m-d, |
---|
| 2930 | or Unix timestamp format. The default $<i>fmt</i> is Y-m-d.</p> |
---|
| 2931 | <p><b>UserTimeStamp<a name="usertimestamp"></a>($str, [$fmt])</b></p> |
---|
| 2932 | <p>Converts the timestamp string $<b>str</b> to another format. The timestamp |
---|
| 2933 | format is Y-m-d H:i:s, as in '2002-02-28 23:00:12', or Unix timestamp format. |
---|
| 2934 | UserTimeStamp calls UnixTimeStamp to parse $<i>str</i>, and $<i>fmt</i> defaults to Y-m-d H:i:s if not defined. |
---|
| 2935 | </p> |
---|
| 2936 | <p><b>UnixDate<a name="unixdate"></a>($str)</b></p> |
---|
| 2937 | <p>Parses the date string $<b>str</b> and returns it in unix mktime format (eg. |
---|
| 2938 | a number indicating the seconds after January 1st, 1970). Expects the date to |
---|
| 2939 | be in Y-m-d H:i:s format, except for Sybase and Microsoft SQL Server, where |
---|
| 2940 | M d Y is also accepted (the 3 letter month strings are controlled by a global |
---|
| 2941 | array, which might need localisation).</p> |
---|
| 2942 | <p>This function is available in both ADORecordSet and ADOConnection since 1.91.</p> |
---|
| 2943 | <p><b>UnixTimeStamp<a name="unixtimestamp"></a>($str)</b></p> |
---|
| 2944 | <p>Parses the timestamp string $<b>str</b> and returns it in unix mktime format |
---|
| 2945 | (eg. a number indicating the seconds after January 1st, 1970). Expects the date |
---|
| 2946 | to be in "Y-m-d, H:i:s" (1970-12-24, 00:00:00) or "Y-m-d H:i:s" (1970-12-24 00:00:00) or "YmdHis" (19701225000000) format, except for Sybase and Microsoft SQL Server, where |
---|
| 2947 | "M d Y h:i:sA" (Dec 25 1970 00:00:00AM) is also accepted (the 3 letter month strings are controlled by |
---|
| 2948 | a global array, which might need localisation).</p> |
---|
| 2949 | </font> |
---|
| 2950 | <p><font color="#000000">This function is available in both ADORecordSet and ADOConnection |
---|
| 2951 | since 1.91. </font></p> |
---|
| 2952 | <p><font color="#000000"><b>OffsetDate<a name="offsetdate"></a>($dayFraction, |
---|
| 2953 | $basedate=false)</b></font></p> |
---|
| 2954 | <p><font color="#000000"><font color="#000000">Returns a string </font>with the |
---|
| 2955 | native SQL functions to calculate future and past dates based on $basedate in |
---|
| 2956 | a portable fashion. If $basedate is not defined, then the current date (at 12 |
---|
| 2957 | midnight) is used. Returns the SQL string that performs the calculation when |
---|
| 2958 | passed to Execute(). </font></p> |
---|
| 2959 | <p><font color="#000000">For example, in Oracle, to find the date and time that |
---|
| 2960 | is 2.5 days from today, you can use:</font></p> |
---|
| 2961 | <pre><font color="#000000"># get date one week from now |
---|
| 2962 | $fld = $conn->OffsetDate(7); // returns "(trunc(sysdate)+7")</font></pre> |
---|
| 2963 | <pre><font color="#000000"># get date and time that is 60 hours from current date and time |
---|
| 2964 | $fld = $conn->OffsetDate(2.5, $conn->sysTimeStamp); // returns "(sysdate+2.5)"</font> |
---|
| 2965 | |
---|
| 2966 | $conn->Execute("UPDATE TABLE SET dodate=$fld WHERE ID=$id");</pre> |
---|
| 2967 | <p> This function is available for mysql, mssql, oracle, oci8 and postgresql drivers |
---|
| 2968 | since 2.13. It might work with other drivers<font color="#000000"> provided |
---|
| 2969 | they allow performing numeric day arithmetic on dates.</font></p> |
---|
| 2970 | <font color="#000000"> |
---|
| 2971 | <p><font color="#000000"><b>SQLDate<a name="sqldate"></a>($dateFormat, $basedate=false)</b></font></p> |
---|
| 2972 | Returns a string which contains the native SQL functions to format a date or date |
---|
| 2973 | column $basedate. This is used in SELECT statements. For INSERT/UPDATE statements, |
---|
| 2974 | use <a href="#dbdate">DBDate</a>. It uses a case-sensitive $dateFormat, which |
---|
| 2975 | supports: |
---|
| 2976 | <pre> |
---|
| 2977 | Y: 4-digit Year |
---|
| 2978 | Q: Quarter (1-4) |
---|
| 2979 | M: Month (Jan-Dec) |
---|
| 2980 | m: Month (01-12) |
---|
| 2981 | d: Day (01-31) |
---|
| 2982 | H: Hour (00-23) |
---|
| 2983 | h: Hour (1-12) |
---|
| 2984 | i: Minute (00-59) |
---|
| 2985 | s: Second (00-60) |
---|
| 2986 | A: AM/PM indicator</pre> |
---|
| 2987 | <p>All other characters are treated as strings. You can also use \ to escape characters. |
---|
| 2988 | Available on selected databases, including mysql, postgresql, mssql, oci8 and |
---|
| 2989 | DB2. |
---|
| 2990 | <p>This is useful in writing portable sql statements that GROUP BY on dates. For |
---|
| 2991 | example to display total cost of goods sold broken by quarter (dates are stored |
---|
| 2992 | in a field called postdate): |
---|
| 2993 | <pre> |
---|
| 2994 | $sqlfn = $db->SQLDate('Y-\QQ','postdate'); # get sql that formats postdate to output 2002-Q1 |
---|
| 2995 | $sql = "SELECT $sqlfn,SUM(cogs) FROM table GROUP BY $sqlfn ORDER BY 1 desc"; |
---|
| 2996 | </pre> |
---|
| 2997 | <p><b>MoveNext<a name="movenext"></a>( )</b></p> |
---|
| 2998 | <p>Move the internal cursor to the next row. The <i>$this->fields</i> array is |
---|
| 2999 | automatically updated. Returns false if unable to do so (normally because EOF |
---|
| 3000 | has been reached), otherwise true. |
---|
| 3001 | <p> If EOF is reached, then the $this->fields array is set to false (this was |
---|
| 3002 | only implemented consistently in ADOdb 3.30). For the pre-3.30 behaviour of |
---|
| 3003 | $this->fields (at EOF), set the global variable $ADODB_COMPAT_FETCH = true.</p> |
---|
| 3004 | <p>Example:</p> |
---|
| 3005 | <pre>$rs = $db->Execute($sql); |
---|
| 3006 | if ($rs) |
---|
| 3007 | while (!$rs->EOF) { |
---|
| 3008 | ProcessArray($rs->fields); |
---|
| 3009 | $rs->MoveNext(); |
---|
| 3010 | } </pre> |
---|
| 3011 | <p><b>Move<a name="move"></a>($to)</b></p> |
---|
| 3012 | <p>Moves the internal cursor to a specific row $<b>to</b>. Rows are zero-based |
---|
| 3013 | eg. 0 is the first row. The <b>fields</b> array is automatically updated. For |
---|
| 3014 | databases that do not support scrolling internally, ADOdb will simulate forward |
---|
| 3015 | scrolling. Some databases do not support backward scrolling. If the $<b>to</b> |
---|
| 3016 | position is after the EOF, $<b>to</b> will move to the end of the RecordSet |
---|
| 3017 | for most databases. Some obscure databases using odbc might not behave this |
---|
| 3018 | way.</p> |
---|
| 3019 | <p>Note: This function uses <i>absolute positioning</i>, unlike Microsoft's ADO.</p> |
---|
| 3020 | <p>Returns true or false. If false, the internal cursor is not moved in most implementations, |
---|
| 3021 | so AbsolutePosition( ) will return the last cursor position before the Move( |
---|
| 3022 | ). </p> |
---|
| 3023 | <p><b>MoveFirst<a name="movefirst"></a>()</b></p> |
---|
| 3024 | <p>Internally calls Move(0). Note that some databases do not support this function.</p> |
---|
| 3025 | <p><b>MoveLast<a name="movelast"></a>()</b></p> |
---|
| 3026 | <p>Internally calls Move(RecordCount()-1). Note that some databases do not support |
---|
| 3027 | this function.</p> |
---|
| 3028 | <p><b>GetRowAssoc</b><a name="getrowassoc"></a>($toUpper=true)</p> |
---|
| 3029 | <p>Returns an associative array containing the current row. The keys to the array |
---|
| 3030 | are the column names. The column names are upper-cased for easy access. To get |
---|
| 3031 | the next row, you will still need to call MoveNext(). </p> |
---|
| 3032 | <p>For example:<br> |
---|
| 3033 | Array ( [ID] => 1 [FIRSTNAME] => Caroline [LASTNAME] => Miranda [CREATED] => |
---|
| 3034 | 2001-07-05 ) </p> |
---|
| 3035 | <p>Note: do not use GetRowAssoc() with $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC. |
---|
| 3036 | Because they have the same functionality, they will interfere with each other.</p> |
---|
| 3037 | </font> |
---|
| 3038 | <p><font color="#000000"><b>AbsolutePage<a name="absolutepage"></a>($page=-1) |
---|
| 3039 | </b></font></p> |
---|
| 3040 | <p>Returns the current page. Requires PageExecute()/CachePageExecute() to be called. |
---|
| 3041 | See <a href=#ex8>Example 8</a>.</p> |
---|
| 3042 | <font color="#000000"> |
---|
| 3043 | <p><b>AtFirstPage<a name="atfirstpage">($status='')</a></b></p> |
---|
| 3044 | <p>Returns true if at first page (1-based). Requires PageExecute()/CachePageExecute() |
---|
| 3045 | to be called. See <a href=#ex8>Example 8</a>.</p> |
---|
| 3046 | <p><b>AtLastPage<a name="atlastpage">($status='')</a></b></p> |
---|
| 3047 | <p>Returns true if at last page (1-based). Requires PageExecute()/CachePageExecute() |
---|
| 3048 | to be called. See <a href=#ex8>Example 8</a>.</p> |
---|
| 3049 | <p><b>Fields</b><a name="fields"></a>(<b>$colname</b>)</p> |
---|
| 3050 | <p>Returns the value of the |
---|
| 3051 | associated column $<b>colname</b> for the current row. The column name is case-insensitive.</p> |
---|
| 3052 | <p>This is a convenience function. For higher performance, use <a href="#adodb_fetch_mode">$ADODB_FETCH_MODE</a>. </p> |
---|
| 3053 | <p><b>FetchRow</b><a name="fetchrow"></a>()</p> |
---|
| 3054 | </font> |
---|
| 3055 | <p><font color="#000000">Returns array containing current row, or false if EOF. |
---|
| 3056 | FetchRow( ) internally moves to the next record after returning the current |
---|
| 3057 | row. </font></p> |
---|
| 3058 | <p><font color="#000000">Warning: Do not mix using FetchRow() with MoveNext().</font></p> |
---|
| 3059 | <p><font color="#000000">Usage:</font></p> |
---|
| 3060 | <pre><font color="#000000">$rs = $db->Execute($sql); |
---|
| 3061 | if ($rs) |
---|
| 3062 | while ($arr = $rs->FetchRow()) { |
---|
| 3063 | # process $arr |
---|
| 3064 | </font><font color="#000000"> }</font></pre> |
---|
| 3065 | <p><font color="#000000"><b>FetchInto</b><a name="fetchinto"></a>(<b>&$array</b>)</font></p> |
---|
| 3066 | <p><font color="#000000"> Sets $array to the current row. Returns PEAR_Error object |
---|
| 3067 | if EOF, 1 if ok (DB_OK constant). If PEAR is undefined, false is returned when |
---|
| 3068 | EOF. </font><font color="#000000">FetchInto( ) internally moves to the next |
---|
| 3069 | record after returning the current row. </font></p> |
---|
| 3070 | <p><font color="#000000"> FetchRow() is easier to use. See above.</font></p> |
---|
| 3071 | <font color="#000000"> |
---|
| 3072 | <p><b>FetchField<a name="fetchfield"></a>($column_number)</b></p> |
---|
| 3073 | <p>Returns an object containing the <b>name</b>, <b>type</b> and <b>max_length</b> |
---|
| 3074 | of the associated field. If the max_length cannot be determined reliably, it |
---|
| 3075 | will be set to -1. The column numbers are zero-based. See <a href="#ex2">example |
---|
| 3076 | 2.</a></p> |
---|
| 3077 | <p><b>FieldCount<a name="fieldcount"></a>( )</b></p> |
---|
| 3078 | <p>Returns the number of fields (columns) in the record set.</p> |
---|
| 3079 | <p><b>RecordCount<a name="recordcount"></a>( )</b></p> |
---|
| 3080 | <p>Returns the number of rows in the record set. If the number of records returned |
---|
| 3081 | cannot be determined from the database driver API, we will buffer all rows and |
---|
| 3082 | return a count of the rows after all the records have been retrieved. This buffering |
---|
| 3083 | can be disabled (for performance reasons) by setting the global variable $ADODB_COUNTRECS |
---|
| 3084 | = false. When disabled, RecordCount( ) will return -1 for certain databases. |
---|
| 3085 | See the supported databases list above for more details. </p> |
---|
| 3086 | <p> RowCount is a synonym for RecordCount.</p> |
---|
| 3087 | <p><b>PO_RecordCount<a name="po_recordcount"></a>($table, $where)</b></p> |
---|
| 3088 | <p>Returns the number of rows in the record set. If the database does not support |
---|
| 3089 | this, it will perform a SELECT COUNT(*) on the table $table, with the given |
---|
| 3090 | $where condition to return an estimate of the recordset size.</p> |
---|
| 3091 | <p>$numrows = $rs->PO_RecordCount("articles_table", "group=$group");</p> |
---|
| 3092 | <b> NextRecordSet<a name="nextrecordset" id="nextrecordset"></a>()</b> |
---|
| 3093 | <p>For databases that allow multiple recordsets to be returned in one query, this |
---|
| 3094 | function allows you to switch to the next recordset. Currently only supported |
---|
| 3095 | by mssql driver.</p> |
---|
| 3096 | <pre> |
---|
| 3097 | $rs = $db->Execute('execute return_multiple_rs'); |
---|
| 3098 | $arr1 = $rs->GetArray(); |
---|
| 3099 | $rs->NextRecordSet(); |
---|
| 3100 | $arr2 = $rs->GetArray();</pre> |
---|
| 3101 | <p><b>FetchObject<a name="fetchobject"></a>($toupper=true)</b></p> |
---|
| 3102 | <p>Returns the current row as an object. If you set $toupper to true, then the |
---|
| 3103 | object fields are set to upper-case. Note: The newer FetchNextObject() is the |
---|
| 3104 | recommended way of accessing rows as objects. See below.</p> |
---|
| 3105 | <p><b>FetchNextObject<a name="fetchnextobject"></a>($toupper=true)</b></p> |
---|
| 3106 | <p>Gets the current row as an object and moves to the next row automatically. |
---|
| 3107 | Returns false if at end-of-file. If you set $toupper to true, then the object |
---|
| 3108 | fields are set to upper-case.</p> |
---|
| 3109 | <pre> |
---|
| 3110 | $rs = $db->Execute('select firstname,lastname from table'); |
---|
| 3111 | if ($rs) { |
---|
| 3112 | while ($o = $rs->FetchNextObject()) { |
---|
| 3113 | print "$o->FIRSTNAME, $o->LASTNAME<BR>"; |
---|
| 3114 | } |
---|
| 3115 | } |
---|
| 3116 | </pre> |
---|
| 3117 | <p>There is some trade-off in speed in using FetchNextObject(). If performance |
---|
| 3118 | is important, you should access rows with the <code>fields[]</code> array. <b>FetchObj<a name="fetchobj" id="fetchobj"></a>()</b> |
---|
| 3119 | <p>Returns the current record as an object. Fields are not upper-cased, unlike |
---|
| 3120 | FetchObject. </font> |
---|
| 3121 | <p><font color="#000000"><b>FetchNextObj<a name="fetchnextobj" id="fetchnextobj"></a>()</b> |
---|
| 3122 | </font></p> |
---|
| 3123 | <p><font color="#000000">Returns the current record as an object and moves to |
---|
| 3124 | the next record. If EOF, false is returned. Fields are not upper-cased, unlike |
---|
| 3125 | FetctNextObject. </font></p> |
---|
| 3126 | <font color="#000000"> |
---|
| 3127 | <p><b>CurrentRow<a name="currentrow"></a>( )</b></p> |
---|
| 3128 | <p>Returns the current row of the record set. 0 is the first row.</p> |
---|
| 3129 | <p><b>AbsolutePosition<a name="abspos"></a>( )</b></p> |
---|
| 3130 | <p>Synonym for <b>CurrentRow</b> for compatibility with ADO. Returns the current |
---|
| 3131 | row of the record set. 0 is the first row.</p> |
---|
| 3132 | <p><b>MetaType<a name="metatype"></a>($nativeDBType[,$field_max_length],[$fieldobj])</b></p> |
---|
| 3133 | <p>Determine what <i>generic</i> meta type a database field type is given its |
---|
| 3134 | native type $<b>nativeDBType</b> as a string and the length of the field $<b>field_max_length</b>. |
---|
| 3135 | Note that field_max_length can be -1 if it is not known. The field object returned |
---|
| 3136 | by FetchField() can be passed in $<b>fieldobj</b> or as the 1st parameter <b>$nativeDBType</b>. |
---|
| 3137 | This is useful for databases such as <i>mysql</i> which has additional properties |
---|
| 3138 | in the field object such as <i>primary_key</i>. </p> |
---|
| 3139 | <p>Uses the field <b>blobSize</b> and compares it with $<b>field_max_length</b> |
---|
| 3140 | to determine whether the character field is actually a blob.</p> |
---|
| 3141 | For example, $db->MetaType('char') will return 'C'. |
---|
| 3142 | <p>Returns:</p> |
---|
| 3143 | <ul> |
---|
| 3144 | <li><b>C</b>: Character fields that should be shown in a <input type="text"> |
---|
| 3145 | tag. </li> |
---|
| 3146 | <li><b>X</b>: Clob (character large objects), or large text fields that should |
---|
| 3147 | be shown in a <textarea></li> |
---|
| 3148 | <li><b>D</b>: Date field</li> |
---|
| 3149 | <li><b>T</b>: Timestamp field</li> |
---|
| 3150 | <li><b>L</b>: Logical field (boolean or bit-field)</li> |
---|
| 3151 | <li><b>N</b>: Numeric field. Includes decimal, numeric, floating point, and |
---|
| 3152 | real. </li> |
---|
| 3153 | <li><b>I</b>: Integer field. </li> |
---|
| 3154 | <li><b>R</b>: Counter or Autoincrement field. Must be numeric.</li> |
---|
| 3155 | <li><b>B</b>: Blob, or binary large objects.<font color="#000000"> </font></li> |
---|
| 3156 | </ul> |
---|
| 3157 | </font> |
---|
| 3158 | <p><font color="#000000"> Since ADOdb 3.0, MetaType accepts $fieldobj as the first |
---|
| 3159 | parameter, instead of $nativeDBType. </font></p> |
---|
| 3160 | <font color="#000000"> |
---|
| 3161 | <p><b>Close( )<a name="rsclose"></a></b></p> |
---|
| 3162 | <p>Closes the recordset, cleaning all memory and resources associated with the recordset. |
---|
| 3163 | <p> |
---|
| 3164 | If memory management is not an issue, you do not need to call this function as recordsets |
---|
| 3165 | are closed for you by PHP at the end of the script. |
---|
| 3166 | SQL statements such as INSERT/UPDATE/DELETE do not really return a recordset, so you do not have to call Close() |
---|
| 3167 | for such SQL statements.</p> |
---|
| 3168 | <hr> |
---|
| 3169 | <h3>function rs2html<a name="rs2html"></a>($adorecordset,[$tableheader_attributes], |
---|
| 3170 | [$col_titles])</h3> |
---|
| 3171 | <p>This is a standalone function (rs2html = recordset to html) that is similar |
---|
| 3172 | to PHP's <i>odbc_result_all</i> function, it prints a ADORecordSet, $<b>adorecordset</b> |
---|
| 3173 | as a HTML table. $<b>tableheader_attributes</b> allow you to control the table |
---|
| 3174 | <i>cellpadding</i>, <i>cellspacing</i> and <i>border</i> attributes. Lastly |
---|
| 3175 | you can replace the database column names with your own column titles with the |
---|
| 3176 | array $<b>col_titles</b>. This is designed more as a quick debugging mechanism, |
---|
| 3177 | not a production table recordset viewer.</p> |
---|
| 3178 | <p>You will need to include the file <i>tohtml.inc.php</i>.</p> |
---|
| 3179 | <p>Example of rs2html:<b><font color="#336600"><a name="exrs2html"></a></font></b></p> |
---|
| 3180 | <pre><b><font color="#336600"><? |
---|
| 3181 | include('tohtml.inc.php')</font></b>; # load code common to ADOdb |
---|
| 3182 | <b>include</b>('adodb.inc.php'); # load code common to ADOdb |
---|
| 3183 | $<font color="#663300">conn</font> = &ADONewConnection('mysql'); # create a connection |
---|
| 3184 | $<font color="#663300">conn</font>->PConnect('localhost','userid','','agora');# connect to MySQL, agora db |
---|
| 3185 | $<font color="#663300">sql</font> = 'select CustomerName, CustomerID from customers'; |
---|
| 3186 | $<font color="#663300">rs</font> = $<font color="#663300">conn</font>->Execute($sql); |
---|
| 3187 | <font color="#336600"><b>rs2html</b></font><b>($<font color="#663300">rs</font>,'<i>border=2 cellpadding=3</i>',array('<i>Customer Name','Customer ID</i>')); |
---|
| 3188 | ?></b></pre> |
---|
| 3189 | <hr> |
---|
| 3190 | <h3>Differences between this ADOdb library and Microsoft ADO<a name="adodiff"></a></h3> |
---|
| 3191 | <ol> |
---|
| 3192 | <li>ADOdb only supports recordsets created by a connection object. Recordsets |
---|
| 3193 | cannot be created independently.</li> |
---|
| 3194 | <li>ADO properties are implemented as functions in ADOdb. This makes it easier |
---|
| 3195 | to implement any enhanced ADO functionality in the future.</li> |
---|
| 3196 | <li>ADOdb's <font face="Courier New, Courier, mono">ADORecordSet->Move()</font> |
---|
| 3197 | uses absolute positioning, not relative. Bookmarks are not supported.</li> |
---|
| 3198 | <li><font face="Courier New, Courier, mono">ADORecordSet->AbsolutePosition() |
---|
| 3199 | </font>cannot be used to move the record cursor.</li> |
---|
| 3200 | <li>ADO Parameter objects are not supported. Instead we have the ADOConnection::<a href="#parameter">Parameter</a>( |
---|
| 3201 | ) function, which provides a simpler interface for calling preparing parameters |
---|
| 3202 | and calling stored procedures.</li> |
---|
| 3203 | <li>Recordset properties for paging records are available, but implemented as |
---|
| 3204 | in <a href=#ex8>Example 8</a>.</li> |
---|
| 3205 | </ol> |
---|
| 3206 | <hr> |
---|
| 3207 | <h1>Database Driver Guide<a name="driverguide"></a></h1> |
---|
| 3208 | <p>This describes how to create a class to connect to a new database. To ensure |
---|
| 3209 | there is no duplication of work, kindly email me at jlim#natsoft.com.my if you |
---|
| 3210 | decide to create such a class.</p> |
---|
| 3211 | <p>First decide on a name in lower case to call the database type. Let's say we |
---|
| 3212 | call it xbase. </p> |
---|
| 3213 | <p>Then we need to create two classes ADODB_xbase and ADORecordSet_xbase in the |
---|
| 3214 | file adodb-xbase.inc.php.</p> |
---|
| 3215 | <p>The simplest form of database driver is an adaptation of an existing ODBC driver. |
---|
| 3216 | Then we just need to create the class <i>ADODB_xbase extends ADODB_odbc</i> |
---|
| 3217 | to support the new <b>date</b> and <b>timestamp</b> formats, the <b>concatenation</b> |
---|
| 3218 | operator used, <b>true</b> and <b>false</b>. For the<i> ADORecordSet_xbase extends |
---|
| 3219 | ADORecordSet_odbc </i>we need to change the <b>MetaType</b> function. See<b> |
---|
| 3220 | adodb-vfp.inc.php</b> as an example.</p> |
---|
| 3221 | <p>More complicated is a totally new database driver that connects to a new PHP |
---|
| 3222 | extension. Then you will need to implement several functions. Fortunately, you |
---|
| 3223 | do not have to modify most of the complex code. You only need to override a |
---|
| 3224 | few stub functions. See <b>adodb-mysql.inc.php</b> for example.</p> |
---|
| 3225 | <p>The default date format of ADOdb internally is YYYY-MM-DD (Ansi-92). All dates |
---|
| 3226 | should be converted to that format when passing to an ADOdb date function. See |
---|
| 3227 | Oracle for an example how we use ALTER SESSION to change the default date format |
---|
| 3228 | in _pconnect _connect.</p> |
---|
| 3229 | <p><b>ADOConnection Functions to Override</b></p> |
---|
| 3230 | <p>Defining a constructor for your ADOConnection derived function is optional. |
---|
| 3231 | There is no need to call the base class constructor.</p> |
---|
| 3232 | <p>_<b>connect</b>: Low level implementation of Connect. Returns true or false. |
---|
| 3233 | Should set the _<b>connectionID</b>.</p> |
---|
| 3234 | <p>_<b>pconnect:</b> Low level implemention of PConnect. Returns true or false. |
---|
| 3235 | Should set the _<b>connectionID</b>.</p> |
---|
| 3236 | <p>_<b>query</b>: Execute a query. Returns the queryID, or false.</p> |
---|
| 3237 | <p>_<b>close: </b>Close the connection -- PHP should clean up all recordsets. |
---|
| 3238 | </p> |
---|
| 3239 | <p><b>ErrorMsg</b>: Stores the error message in the private variable _errorMsg. |
---|
| 3240 | </p> |
---|
| 3241 | <p><b>ADOConnection Fields to Set</b></p> |
---|
| 3242 | <p>_<b>bindInputArray</b>: Set to true if binding of parameters for SQL inserts |
---|
| 3243 | and updates is allowed using ?, eg. as with ODBC.</p> |
---|
| 3244 | <p><b>fmtDate</b></p> |
---|
| 3245 | <p><b>fmtTimeStamp</b></p> |
---|
| 3246 | <p><b>true</b></p> |
---|
| 3247 | <p><b>false</b></p> |
---|
| 3248 | <p><b>concat_operator</b></p> |
---|
| 3249 | <p><b>replaceQuote</b></p> |
---|
| 3250 | <p><b>hasLimit</b> support SELECT * FROM TABLE LIMIT 10 of MySQL.</p> |
---|
| 3251 | <p><b>hasTop</b> support Microsoft style SELECT TOP 10 * FROM TABLE.</p> |
---|
| 3252 | <p><b>ADORecordSet Functions to Override</b></p> |
---|
| 3253 | <p>You will need to define a constructor for your ADORecordSet derived class that |
---|
| 3254 | calls the parent class constructor.</p> |
---|
| 3255 | <p><b>FetchField: </b> as documented above in ADORecordSet</p> |
---|
| 3256 | <p>_<b>initrs</b>: low level initialization of the recordset: setup the _<b>numOfRows</b> |
---|
| 3257 | and _<b>numOfFields</b> fields -- called by the constructor.</p> |
---|
| 3258 | <p>_<b>seek</b>: seek to a particular row. Do not load the data into the fields |
---|
| 3259 | array. This is done by _fetch. Returns true or false. Note that some implementations |
---|
| 3260 | such as Interbase do not support seek. Set canSeek to false.</p> |
---|
| 3261 | <p>_<b>fetch</b>: fetch a row using the database extension function and then move |
---|
| 3262 | to the next row. Sets the <b>fields</b> array. If the parameter $ignore_fields |
---|
| 3263 | is true then there is no need to populate the <b>fields</b> array, just move |
---|
| 3264 | to the next row. then Returns true or false.</p> |
---|
| 3265 | <p>_<b>close</b>: close the recordset</p> |
---|
| 3266 | <p><b>Fields</b>: If the array row returned by the PHP extension is not an associative |
---|
| 3267 | one, you will have to override this. See adodb-odbc.inc.php for an example. |
---|
| 3268 | For databases such as MySQL and MSSQL where an associative array is returned, |
---|
| 3269 | there is no need to override this function.</p> |
---|
| 3270 | <p><b>ADOConnection Fields to Set</b></p> |
---|
| 3271 | <p>canSeek: Set to true if the _seek function works.</p> |
---|
| 3272 | <h2>ToDo:</h2> |
---|
| 3273 | <p>See the <a href=http://php.weblogs.com/adodb-todo-roadmap>RoadMap</a> article.</p> |
---|
| 3274 | <p>Also see the ADOdb <a href=http://php.weblogs.com/adodb_csv>proxy</a> article |
---|
| 3275 | for bridging Windows and Unix databases using http remote procedure calls. For |
---|
| 3276 | your education, visit <a href=http://palslib.com/>palslib.com</a> for database info, |
---|
| 3277 | and read this article on <a href=http://phplens.com/lens/php-book/optimizing-debugging-php.php>Optimizing |
---|
| 3278 | PHP</a>. </p> |
---|
| 3279 | </font> |
---|
| 3280 | <h2>Change Log<a name="Changes"></a><a name="changes"></a><a name="changelog"></a></h2> |
---|
| 3281 | <p><a name=4.51></a><b>4.51 29 July 2004</b> |
---|
| 3282 | <p>Added adodb-xmlschema 1.0.2. Thx dan and richard. |
---|
| 3283 | <p>Added new adorecordset_ext_* classes. If ADOdb extension installed for mysql, mysqlt and oci8 |
---|
| 3284 | (but not oci8po), we use the superfast ADOdb extension code for movenext. |
---|
| 3285 | <p>Added schema support to mssql and odbc_mssql MetaPrimaryKeys(). |
---|
| 3286 | <p>Patched MSSQL driver to support PHP NULL and Boolean values |
---|
| 3287 | while binding the input array parameters in the _query() function. By Stephen Farmer. |
---|
| 3288 | <p>Added support for clob's for mssql, UpdateBlob(). Thx to gfran#directa.com.br |
---|
| 3289 | <p>Added normalize support for postgresql (true=lowercase table name, or false=case-sensitive table names) |
---|
| 3290 | to MetaColumns($table, $normalize=true). |
---|
| 3291 | <p>PHP5 variant dates in ADO not working. Fixed in adodb-ado.inc.php. |
---|
| 3292 | <p>Constant ADODB_FORCE_NULLS was not working properly for many releases (for GetUpdateSQL). Fixed. |
---|
| 3293 | Also GetUpdateSQL strips off ORDER BY now - thx Elieser Leão. |
---|
| 3294 | <p>Perf Monitor for oci8 now dynamically highlights optimizer_* params if too high/low. |
---|
| 3295 | <p>Added dsn support to NewADOConnection/ADONewConnection. |
---|
| 3296 | <p>Fixed out of page bounds bug in _adodb_pageexecute_all_rows() Thx to "Sergio Strampelli" sergio#rir.it |
---|
| 3297 | <p>Speedup of movenext for mysql and oci8 drivers. |
---|
| 3298 | <p>Moved debugging code _adodb_debug_execute() to adodb-lib.inc.php. |
---|
| 3299 | <p>Fixed postgresql bytea detection bug. See http://phplens.com/lens/lensforum/msgs.php?id=9849. |
---|
| 3300 | <p>Fixed ibase datetimestamp typo in PHP5. Thx stefan. |
---|
| 3301 | <p>Removed whitespace at end of odbtp drivers. |
---|
| 3302 | <p>Added db2 metaprimarykeys fix. |
---|
| 3303 | <p>Optimizations to MoveNext() for mysql and oci8. Misc speedups to Get* functions. |
---|
| 3304 | <p><a name=4.50></a><b>4.50 6 July 2004</b> |
---|
| 3305 | <p>Bumped it to 4.50 to avoid confusion with PHP 4.3.x series. |
---|
| 3306 | <p>Added db2 metatables and metacolumns extensions. |
---|
| 3307 | <p>Added alpha PDO driver. Very buggy, only works with odbc. |
---|
| 3308 | <p>Tested mysqli. Set poorAffectedRows = true. Cleaned up movenext() and _fetch(). |
---|
| 3309 | <p>PageExecute does not work properly with php5 (return val not a variable). Reported Dmytro Sychevsky sych#php.com.ua. Fixed. |
---|
| 3310 | <p>MetaTables() for mysql, $showschema parameter was not backward compatible with older versions of adodb. Fixed. |
---|
| 3311 | <p>Changed mysql GetOne() to work with mysql 3.23 when using with non-select stmts (e.g. SHOW TABLES). |
---|
| 3312 | <p>Changed TRIG_ prefix to a variable in datadict-oci8.inc.php. Thx to Luca.Gioppo#csi.it. |
---|
| 3313 | <p>New to adodb-time code. We allow you to define your own daylights savings function, |
---|
| 3314 | adodb_daylight_sv for pre-1970 dates. If the function is defined |
---|
| 3315 | (somewhere in an include), then you can correct |
---|
| 3316 | for daylights savings. See http://phplens.com/phpeverywhere/node/view/16#daylightsavings |
---|
| 3317 | for more info. |
---|
| 3318 | <p>New sqlitepo driver. This is because assoc mode does not work like other drivers in sqlite. |
---|
| 3319 | Namely, when selecting (joining) multiple tables, in assoc mode the table |
---|
| 3320 | names are included in the assoc keys in the "sqlite" driver. |
---|
| 3321 | In "sqlitepo" driver, the table names are stripped from the returned column names. |
---|
| 3322 | When this results in a conflict, the first field get preference. |
---|
| 3323 | Contributed by Herman Kuiper herman#ozuzo.net |
---|
| 3324 | <p>Added $forcenull parameter to GetInsertSQL/GetUpdateSQL. Idea by Marco Aurelio Silva. |
---|
| 3325 | <p>More XHTML changes for GetMenu. By Jeremy Evans. |
---|
| 3326 | <p>Fixes some ibase date issues. Thx to stefan bogdan. |
---|
| 3327 | <p>Improvements to mysqli driver to support $ADODB_COUNTRECS. |
---|
| 3328 | <p>Fixed adodb-csvlib.inc.php problem when reading stream from socket. We need to poll stream continiously. |
---|
| 3329 | <p><a name=4.23></a><b>4.23 16 June 2004</b> |
---|
| 3330 | <p> |
---|
| 3331 | New interbase/firebird fixes thx to Lester Caine. |
---|
| 3332 | Driver fixes a problem with getting field names in the result array, and |
---|
| 3333 | corrects a couple of data conversions. Also we default to dialect3 for firebird. |
---|
| 3334 | Also ibase sysDate property was wrong. Changed to cast as timestamp. |
---|
| 3335 | <p> |
---|
| 3336 | The datadict driver is set up to give quoted tables and fields as this |
---|
| 3337 | was the only way round reserved words being used as field names in |
---|
| 3338 | TikiWiki. TikiPro is tidying that up, and I hope to be able to produce a |
---|
| 3339 | build of THAT which uses what I consider proper UPPERCASE field and |
---|
| 3340 | table names. The conversion of TikiWiki to ADOdb helped in that, but |
---|
| 3341 | until the database is completely tidied up in TikiPro ... |
---|
| 3342 | <p>Modified _gencachename() to include fetchmode in name hash. |
---|
| 3343 | This means you should clear your cache directory after installing this release as the |
---|
| 3344 | cache name algorithm has changed. |
---|
| 3345 | <p>Now Cache* functions work in safe mode, because we do not create sub-directories in the $ADODB_CACHE_DIR in safe mode. In non-safe mode we still create sub-directories. Done by modifying _gencachename(). |
---|
| 3346 | <p>Added $gmt parameter (true/false) to UserDate and UserTimeStamp in connection class, to force conversion of input (in local time) to be converted to UTC/GMT. |
---|
| 3347 | <p>Mssql datadict did not support INT types properly (no size param allowed). |
---|
| 3348 | Added _GetSize() to datadict-mssql.inc.php. |
---|
| 3349 | <p>For borland_ibase, BeginTrans(), changed:<br> |
---|
| 3350 | <pre> $this->_transactionID = $this->_connectionID;</pre> |
---|
| 3351 | to<br> |
---|
| 3352 | <pre> $this->_transactionID = ibase_trans($this->ibasetrans, $this->_connectionID);</pre> |
---|
| 3353 | |
---|
| 3354 | <p>Fixed typo in mysqi_field_seek(). Thx to Sh4dow (sh4dow#php.pl). |
---|
| 3355 | <p>LogSQL did not work with Firebird/Interbase. Fixed. |
---|
| 3356 | <p>Postgres: made errorno() handling more consistent. Thx to Michael Jahn, Michael.Jahn#mailbox.tu-dresden.de. |
---|
| 3357 | <p>Added informix patch to better support metatables, metacolumns by "Cecilio Albero" c-albero#eos-i.com |
---|
| 3358 | <p>Cyril Malevanov contributed patch to oci8 to support passing of LOB parameters: |
---|
| 3359 | <pre> |
---|
| 3360 | $text = 'test test test'; |
---|
| 3361 | $sql = "declare rs clob; begin :rs := lobinout(:sa0); end;"; |
---|
| 3362 | $stmt = $conn -> PrepareSP($sql); |
---|
| 3363 | $conn -> InParameter($stmt,$text,'sa0', -1, OCI_B_CLOB); |
---|
| 3364 | $rs = ''; |
---|
| 3365 | $conn -> OutParameter($stmt,$rs,'rs', -1, OCI_B_CLOB); |
---|
| 3366 | $conn -> Execute($stmt); |
---|
| 3367 | echo "return = ".$rs."<br>"; |
---|
| 3368 | </pre> |
---|
| 3369 | As he says, the LOBs limitations are: |
---|
| 3370 | <pre> |
---|
| 3371 | - use OCINewDescriptor before binding |
---|
| 3372 | - if Param is IN, uses save() before each execute. This is done automatically for you. |
---|
| 3373 | - if Param is OUT, uses load() after each execute. This is done automatically for you. |
---|
| 3374 | - when we bind $var as LOB, we create new descriptor and return it as a |
---|
| 3375 | Bind Result, so if we want to use OUT parameters, we have to store |
---|
| 3376 | somewhere &$var to load() data from LOB to it. |
---|
| 3377 | - IN OUT params are not working now (should not be a big problem to fix it) |
---|
| 3378 | - now mass binding not working too (I've wrote about it before) |
---|
| 3379 | </pre> |
---|
| 3380 | <p>Simplified Connect() and PConnect() error handling. |
---|
| 3381 | <p>When extension not loaded, Connect() and PConnect() will return null. On connect error, the fns will return false. |
---|
| 3382 | <p>CacheGetArray() added to code. |
---|
| 3383 | <p>Added Init() to adorecordset_empty(). |
---|
| 3384 | <p>Changed postgres64 driver, MetaColumns() to not strip off quotes in default value if :: detected (type-casting of default). |
---|
| 3385 | <p>Added test: if (!defined('ADODB_DIR')) die(). Useful to prevent hackers from detecting file paths. |
---|
| 3386 | <p>Changed metaTablesSQL to ignore Postgres 7.4 information schemas (sql_*). |
---|
| 3387 | <p>New polish language file by Grzegorz Pacan |
---|
| 3388 | <p>Added support for UNION in _adodb_getcount(). |
---|
| 3389 | <p>Added security check for ADODB_DIR to limit path disclosure issues. Requested by postnuke team. |
---|
| 3390 | <p>Added better error message support to oracle driver. Thx to Gaetano Giunta. |
---|
| 3391 | <p>Added showSchema support to mysql. |
---|
| 3392 | <p>Bind in oci8 did not handle $name=false properly. Fixed. |
---|
| 3393 | <p>If extension not loaded, Connect(), PConnect(), NConnect() will return null. |
---|
| 3394 | <p><b>4.22 15 Apr 2004</b> |
---|
| 3395 | <p>Moved docs to own adodb/docs folder. |
---|
| 3396 | <p>Fixed session bug when quoting compressed/encrypted data in Replace(). |
---|
| 3397 | <p>Netezza Driver and LDAP drivers contributed by Josh Eldridge. |
---|
| 3398 | <p>GetMenu now uses rtrim() on values instead of trim(). |
---|
| 3399 | <p>Changed MetaColumnNames to return an associative array, keys being the field names in uppercase. |
---|
| 3400 | <p>Suggested fix to adodb-ado.inc.php affected_rows to support PHP5 variants. Thx to Alexios Fakos. |
---|
| 3401 | <p>Contributed bulgarian language file by Valentin Sheiretsky valio#valio.eu.org. |
---|
| 3402 | <p>Contributed romanian language file by stefan bogdan. |
---|
| 3403 | <p>GetInsertSQL now checks for table name (string) in $rs, and will create a recordset for that |
---|
| 3404 | table automatically. Contributed by Walt Boring. Also added OCI_B_BLOB in bind on Walt's request - hope |
---|
| 3405 | it doesn't break anything :-) |
---|
| 3406 | <p>Some minor postgres speedups in _initrs(). |
---|
| 3407 | <p> ChangeTableSQL checks now if MetaColumns returns empty. Thx Jason Judge. |
---|
| 3408 | <p>Added ADOConnection::Time(), returns current database time in unix timestamp format, or false. |
---|
| 3409 | <p><b>4.21 20 Mar 2004</b> |
---|
| 3410 | <p>We no longer in SelectLimit for VFP driver add SELECT TOP X unless an ORDER BY exists. |
---|
| 3411 | <p>Pim Koeman contributed dutch language file adodb-nl.inc.php. |
---|
| 3412 | <p>Rick Hickerson added CLOB support to db2 datadict. |
---|
| 3413 | <p>Added odbtp driver. Thx to "stefan bogdan" sbogdan#rsb.ro. |
---|
| 3414 | <p>Changed PrepareSP() 2nd parameter, $cursor, to default to true (formerly false). Fixes oci8 backward |
---|
| 3415 | compat problems with OUT params. |
---|
| 3416 | <p>Fixed month calculation error in adodb-time.inc.php. 2102-June-01 appeared as 2102-May-32. |
---|
| 3417 | <p>Updated PHP5 RC1 iterator support. API changed, hasMore() renamed to valid(). |
---|
| 3418 | <p>Changed internal format of serialized cache recordsets. As we store a version number, this should be |
---|
| 3419 | backward compatible. |
---|
| 3420 | <p>Error handling when driver file not found was flawed in ADOLoadCode(). Fixed. |
---|
| 3421 | <p><b>4.20 27 Feb 2004</b> |
---|
| 3422 | <p>Updated to AXMLS 1.01. |
---|
| 3423 | <p>MetaForeignKeys for postgres7 modified by Edward Jaramilla, works on pg 7.4. |
---|
| 3424 | <p>Now numbers accepts function calls or sequences for GetInsertSQL/GetUpdateSQL numeric fields. |
---|
| 3425 | <p>Changed quotes of 'delete from $perf_table' to "". Thx Kehui (webmaster#kehui.net) |
---|
| 3426 | <p>Added ServerInfo() for ifx, and putenv trim fix. Thx Fernando Ortiz. |
---|
| 3427 | <p>Added addq(), which is analogous to addslashes(). |
---|
| 3428 | <p>Tested with php5b4. Fix some php5 compat problems with exceptions and sybase. |
---|
| 3429 | <P>Carl-Christian Salvesen added patch to mssql _query to support binds greater than 4000 chars. |
---|
| 3430 | <p>Mike suggested patch to PHP5 exception handler. $errno must be numeric. |
---|
| 3431 | <p>Added double quotes (") to ADODB_TABLE_REGEX. |
---|
| 3432 | <p>For oci8, Prepare(...,$cursor), $cursor's meaning was accidentally inverted in 4.11. This causes problems with ExecuteCursor() too, which calls Prepare() internally. Thx to William Lovaton. |
---|
| 3433 | <p>Now dateHasTime property in connection object renamed to datetime for consistency. This could break bc. |
---|
| 3434 | <p>Csongor Halmai reports that db2 SelectLimit with input array is not working. Fixed.. |
---|
| 3435 | <p><b>4.11 27 Jan 2004</b> |
---|
| 3436 | <p>Csongor Halmai reports db2 binding not working. Reverted back to emulated binding. |
---|
| 3437 | <p>Dan Cech modifies datadict code. Adds support for DropIndex. Minor cleanups. |
---|
| 3438 | <p>Table misspelt in perf-oci8.inc.php. Changed v$conn_cache_advice to v$db_cache_advice. Reported by Steve W. |
---|
| 3439 | <p>UserTimeStamp and DBTimeStamp did not handle YYYYMMDDHHMMSS format properly. Reported by Mike Muir. Fixed. |
---|
| 3440 | <p>Changed oci8 Prepare(). Does not auto-allocate OCINewCursor automatically, unless 2nd param is set to true. |
---|
| 3441 | This will break backward compat, if Prepare/Execute is used instead of ExecuteCursor. Reported by Chris Jones. |
---|
| 3442 | <p>Added InParameter() and OutParameter(). Wrapper functions to Parameter(), but nicer because they |
---|
| 3443 | are self-documenting. |
---|
| 3444 | <p>Added 'R' handling in ActualType() to datadict-mysql.inc.php |
---|
| 3445 | <p>Added ADOConnection::SerializableRS($rs). Returns a recordset that can be serialized in a session. |
---|
| 3446 | <p>Added "Run SQL" to performance UI(). |
---|
| 3447 | <p>Misc spelling corrections in adodb-mysqli.inc.php, adodb-oci8.inc.php and datadict-oci8.inc.php, from Heinz Hombergs. |
---|
| 3448 | <p>MetaIndexes() for ibase contributed by Heinz Hombergs. |
---|
| 3449 | <p><b>4.10 12 Jan 2004</b> |
---|
| 3450 | <p>Dan Cech contributed extensive changes to data dictionary to support name quoting (with `), and drop table/index. |
---|
| 3451 | <p>Informix added cursorType property. Default remains IFX_SCROLL, but you can change to 0 (non-scrollable cursor) for performance. |
---|
| 3452 | <p>Added ADODB_View_PrimaryKeys() for returning view primary keys to MetaPrimaryKeys(). |
---|
| 3453 | <p>Simplified chinese file, adodb-cn.inc.php from cysoft. |
---|
| 3454 | <p>Added check for ctype_alnum in adodb-datadict.inc.php. Thx to Jason Judge. |
---|
| 3455 | <p>Added connection parameter to ibase Prepare(). Fix by Daniel Hassan. |
---|
| 3456 | <p>Added nameQuote for quoting identifiers and names to connection obj. Requested by Jason Judge. Also the |
---|
| 3457 | data dictionary parser now detects `field name` and generates column names with spaces correctly. |
---|
| 3458 | <p>BOOL type not recognised correctly as L. Fixed. |
---|
| 3459 | <p>Fixed paths in ADODB_DIR for session files, and back-ported it to 4.05 (15 Dec 2003) |
---|
| 3460 | <p>Added Schema to postgresql MetaTables. Thx to col#gear.hu |
---|
| 3461 | <p>Empty postgresql recordsets that had blob fields did not set EOF properly. Fixed. |
---|
| 3462 | <p>CacheSelectLimit internal parameters to SelectLimit were wrong. Thx to Nio. |
---|
| 3463 | <p>Modified adodb_pr() and adodb_backtrace() to support command-line usage (eg. no html). |
---|
| 3464 | <p>Fixed some fr and it lang errors. Thx to Gaetano G. |
---|
| 3465 | <p>Added contrib directory, with adodb rs to xmlrpc convertor by Gaetano G. |
---|
| 3466 | <p>Fixed array recordset bugs when _skiprow1 is true. Thx to Gaetano G. |
---|
| 3467 | <p>Fixed pivot table code when count is false. |
---|
| 3468 | <p> |
---|
| 3469 | |
---|
| 3470 | <p><b>4.05 13 Dec 2003 </b> |
---|
| 3471 | <p>Added MetaIndexes - thx to Dan Cech. |
---|
| 3472 | <p>Rewritten session code by Ross Smith. Moved code to adodb/session directory. |
---|
| 3473 | <p>Added function exists check on connecting to most drivers, so we don't crash with the unknown function error. |
---|
| 3474 | <p>Smart Transactions failed with GenID() when it no seq table has been created because the sql |
---|
| 3475 | statement fails. Fix by Mark Newnham. |
---|
| 3476 | <p>Added $db->length, which holds name of function that returns strlen. |
---|
| 3477 | <p>Fixed error handling for bad driver in ADONewConnection - passed too few params to error-handler. |
---|
| 3478 | <p>Datadict did not handle types like 16.0 properly in _GetSize. Fixed. |
---|
| 3479 | <p>Oci8 driver SelectLimit() bug &= instead of =& used. Thx to Swen Thümmler. |
---|
| 3480 | <p>Jesse Mullan suggested not flushing outp when output buffering enabled. Due to Apache 2.0 bug. Added. |
---|
| 3481 | <p>MetaTables/MetaColumns return ref bug with PHP5 fixed in adodb-datadict.inc.php. |
---|
| 3482 | <p>New mysqli driver contributed by Arjen de Rijke. Based on adodb 3.40 driver. |
---|
| 3483 | Then jlim added BeginTrans, CommitTrans, RollbackTrans, IfNull, SQLDate. Also fixed return ref bug. |
---|
| 3484 | <p>$ADODB_FLUSH added, if true then force flush in debugging outp. Default is false. In earlier |
---|
| 3485 | versions, outp defaulted to flush, which is not compat with apache 2.0. |
---|
| 3486 | <p>Mysql driver's GenID() function did not work when when sql logging is on. Fixed. |
---|
| 3487 | <p>$ADODB_SESSION_TBL not declared as global var. Not available if adodb-session.inc.php included in function. Fixed. |
---|
| 3488 | <p>The input array not passed to Execute() in _adodb_getcount(). Fixed. |
---|
| 3489 | <p><b>4.04 13 Nov 2003 </b> |
---|
| 3490 | <p>Switched back to foreach - faster than list-each. |
---|
| 3491 | <p>Fixed bug in ado driver - wiping out $this->fields with date fields. |
---|
| 3492 | <p>Performance Monitor, View SQL, Explain Plan did not work if strlen($SQL)>max($_GET length). Fixed. |
---|
| 3493 | <p>Performance monitor, oci8 driver added memory sort ratio. |
---|
| 3494 | <p>Added random property, returns SQL to generate a floating point number between 0 and 1; |
---|
| 3495 | <p><b>4.03 6 Nov 2003 </b> |
---|
| 3496 | <p>The path to adodb-php4.inc.php and adodb-iterators.inc.php was not setup properly. |
---|
| 3497 | <p>Patched SQLDate in interbase to support hours/mins/secs. Thx to ari kuorikoski. |
---|
| 3498 | <p>Force autorollback for pgsql persistent connections - |
---|
| 3499 | apparently pgsql did not autorollback properly before 4.3.4. See http://bugs.php.net/bug.php?id=25404 |
---|
| 3500 | <p><b>4.02 5 Nov 2003 </b> |
---|
| 3501 | <p>Some errors in adodb_error_pg() fixed. Thx to Styve. |
---|
| 3502 | <p>Spurious Insert_ID() error was generated by LogSQL(). Fixed. |
---|
| 3503 | <p>Insert_ID was interfering with Affected_Rows() and Replace() when LogSQL() enabled. Fixed. |
---|
| 3504 | <p>More foreach loops optimized with list/each. |
---|
| 3505 | <p>Null dates not handled properly in ADO driver (it becomes 31 Dec 1969!). |
---|
| 3506 | <p>Heinz Hombergs contributed patches for mysql MetaColumns - adding scale, made |
---|
| 3507 | interbase MetaColumns work with firebird/interbase, and added lang/adodb-de.inc.php. |
---|
| 3508 | <p>Added INFORMIXSERVER environment variable. |
---|
| 3509 | <p>Added $ADODB_ANSI_PADDING_OFF for interbase/firebird. |
---|
| 3510 | <p>PHP 5 beta 2 compat check. Foreach (Iterator) support. Exceptions support. |
---|
| 3511 | <p><b>4.01 23 Oct 2003 </b> |
---|
| 3512 | <p>Fixed bug in rs2html(), tohtml.inc.php, that generated blank table cells. |
---|
| 3513 | <p>Fixed insert_id() incorrectly generated when logsql() enabled. |
---|
| 3514 | <p>Modified PostgreSQL _fixblobs to use list/each instead of foreach. |
---|
| 3515 | <p>Informix ErrorNo() implemented correctly. |
---|
| 3516 | <p>Modified several places to use list/each, including GetRowAssoc(). |
---|
| 3517 | <p>Added UserTimeStamp() to connection class. |
---|
| 3518 | <p>Added $ADODB_ANSI_PADDING_OFF for oci8po. |
---|
| 3519 | <p><b>4.00 20 Oct 2003 </b> |
---|
| 3520 | <p>Upgraded adodb-xmlschema to 1 Oct 2003 snapshot. |
---|
| 3521 | <p>Fix to rs2html warning message. Thx to Filo. |
---|
| 3522 | <p>Fix for odbc_mssql/mssql SQLDate(), hours was wrong. |
---|
| 3523 | <p>Added MetaColumns and MetaPrimaryKeys for sybase. Thx to Chris Phillipson. |
---|
| 3524 | <p>Added autoquoting to datadict for MySQL and PostgreSQL. Suggestion by Karsten Dambekalns |
---|
| 3525 | <p><b>3.94 11 Oct 2003 </b> |
---|
| 3526 | <p>Create trigger in datadict-oci8.inc.php did not work, because all cr/lf's must be removed. |
---|
| 3527 | <p>ErrorMsg()/ErrorNo() did not work for many databases when logging enabled. Fixed. |
---|
| 3528 | <p>Removed global variable $ADODB_LOGSQL as it does not work properly with multiple connections. |
---|
| 3529 | <p>Added SQLDate support for sybase. Thx to Chris Phillipson |
---|
| 3530 | <p>Postgresql checking of pgsql resultset resource was incorrect. Fix by Bharat Mediratta bharat#menalto.com. |
---|
| 3531 | Same patch applied to _insertid and _affectedrows for adodb-postgres64.inc.php. |
---|
| 3532 | <p>Added support for NConnect for postgresql. |
---|
| 3533 | <p>Added Sybase data dict support. Thx to Chris Phillipson |
---|
| 3534 | <p>Extensive improvements in $perf->UI(), eg. Explain now opens in new window, we show scripts |
---|
| 3535 | which call sql, etc. |
---|
| 3536 | <p>Perf Monitor UI works with magic quotes enabled. |
---|
| 3537 | <p>rsPrefix was declared twice. Removed. |
---|
| 3538 | <p>Oci8 stored procedure support, eg. "begin func(); end;" was incorrect in _query. Fixed. |
---|
| 3539 | <p>Tiraboschi Massimiliano contributed italian language file. |
---|
| 3540 | <p>Fernando Ortiz, fortiz#lacorona.com.mx, contributed informix performance monitor. |
---|
| 3541 | <p>Added _varchar (varchar arrays) support for postgresql. Reported by PREVOT Stéphane. |
---|
| 3542 | <p><b>3.92 22 Sept 2003</b> |
---|
| 3543 | <p>Added GetAssoc and CacheGetAssoc to connection object. |
---|
| 3544 | <p>Removed TextMax and CharMax functions from adodb.inc.php. |
---|
| 3545 | <p>HasFailedTrans() returned false when trans failed. Fixed. |
---|
| 3546 | <p>Moved perf driver classes into adodb/perf/*.php. |
---|
| 3547 | <p>Misc improvements to performance monitoring, including UI(). |
---|
| 3548 | <p>RETVAL in mssql Parameter(), we do not append @ now. |
---|
| 3549 | <p>Added Param($name) to connection class, returns '?' or ":$name", for defining |
---|
| 3550 | bind parameters portably. |
---|
| 3551 | <p>LogSQL traps affected_rows() and saves its value properly now. Also fixed oci8 |
---|
| 3552 | _stmt and _affectedrows() bugs. |
---|
| 3553 | <p>Session code timestamp check for oci8 works now. Formerly default NLS_DATE_FORMAT |
---|
| 3554 | stripped off time portion. Thx to Tony Blair (tonanbarbarian#hotmail.com). Also |
---|
| 3555 | added new $conn->datetime field to oci8, controls whether MetaType() returns |
---|
| 3556 | 'D' ($this->datetime==false) or 'T' ($this->datetime == true) for DATE type. |
---|
| 3557 | <p>Fixed bugs in adodb-cryptsession.inc.php and adodb-session-clob.inc.php. |
---|
| 3558 | <p>Fixed misc bugs in adodb_key_exists, GetInsertSQL() and GetUpdateSQL(). |
---|
| 3559 | <p>Tuned include_once handling to reduce file-system checking overhead. |
---|
| 3560 | <p><b>3.91 9 Sept 2003</b> |
---|
| 3561 | <p>Only released to InterAkt |
---|
| 3562 | <p>Added LogSQL() for sql logging and $ADODB_NEWCONNECTION to override factory |
---|
| 3563 | for driver instantiation. |
---|
| 3564 | <p>Added IfNull($field,$ifNull) function, thx to johnwilk#juno.com |
---|
| 3565 | <p>Added portable substr support. |
---|
| 3566 | <p>Now rs2html() has new parameter, $echo. Set to false to return $html instead |
---|
| 3567 | of echoing it. |
---|
| 3568 | <p><b>3.90 5 Sept 2003</b> |
---|
| 3569 | <p>First beta of performance monitoring released. |
---|
| 3570 | <p>MySQL supports MetaTable() masking. |
---|
| 3571 | <p>Fixed key_exists() bug in adodb-lib.inc.php |
---|
| 3572 | <p>Added sp_executesql Prepare() support to mssql. |
---|
| 3573 | <p>Added bind support to db2. |
---|
| 3574 | <p>Added swedish language file - Christian Tiberg" christian#commsoft.nu |
---|
| 3575 | <p>Bug in drop index for mssql data dict fixed. Thx to Gert-Rainer Bitterlich. |
---|
| 3576 | <p>Left join setting for oci8 was wrong. Thx to johnwilk#juno.com |
---|
| 3577 | <p><b>3.80 27 Aug 2003</b> |
---|
| 3578 | <p>Patch for PHP 4.3.3 cached recordset csv2rs() fread loop incompatibility. |
---|
| 3579 | <p>Added matching mask for MetaTables. Only for oci8, mssql and postgres currently. |
---|
| 3580 | <p>Rewrite of "oracle" driver connection code, merging with "oci8", by Gaetano. |
---|
| 3581 | <p>Added better debugging for Smart Transactions. |
---|
| 3582 | <p>Postgres DBTimeStamp() was wrongly using TO_DATE. Changed to TO_TIMESTAMP. |
---|
| 3583 | <p>ADODB_FETCH_CASE check pushed to ADONewConnection to allow people to define |
---|
| 3584 | it after including adodb.inc.php. |
---|
| 3585 | <p>Added portugese (brazilian) to languages. Thx to "Levi Fukumori". |
---|
| 3586 | <p>Removed arg3 parameter from Execute/SelectLimit/Cache* functions. |
---|
| 3587 | <p>Execute() now accepts 2-d array as $inputarray. Also changed docs of fnExecute() |
---|
| 3588 | to note change in sql query counting with 2-d arrays. |
---|
| 3589 | <p>Added MONEY to MetaType in PostgreSQL. |
---|
| 3590 | <p>Added more debugging output to CacheFlush(). |
---|
| 3591 | <p><b>3.72 9 Aug 2003</b> |
---|
| 3592 | <p>Added qmagic($str), which is a qstr($str) that auto-checks for magic quotes |
---|
| 3593 | and does the right thing... |
---|
| 3594 | <p>Fixed CacheFlush() bug - Thx to martin#gmx.de |
---|
| 3595 | <p>Walt Boring contributed MetaForeignKeys for postgres7. |
---|
| 3596 | <p>_fetch() called _BlobDecode() wrongly in interbase. Fixed. |
---|
| 3597 | <p>adodb_time bug fixed with dates after 2038 fixed by Jason Pell. http://phplens.com/lens/lensforum/msgs.php?id=6980 |
---|
| 3598 | <p><b>3.71 4 Aug 2003</b> |
---|
| 3599 | <p>The oci8 driver, MetaPrimaryKeys() did not check the owner correctly when $owner |
---|
| 3600 | == false. |
---|
| 3601 | <p>Russian language file contributed by "Cyrill Malevanov" cyrill#malevanov.spb.ru. |
---|
| 3602 | <p>Spanish language file contributed by "Horacio Degiorgi" horaciod#codigophp.com. |
---|
| 3603 | <p>Error handling in oci8 bugfix - if there was an error in Execute(), then when |
---|
| 3604 | calling ErrorNo() and/or ErrorMsg(), the 1st call would return the error, but |
---|
| 3605 | the 2nd call would return no error. |
---|
| 3606 | <p>Error handling in odbc bugfix. ODBC would always return the last error, even |
---|
| 3607 | if it happened 5 queries ago. Now we reset the errormsg to '' and errorno to |
---|
| 3608 | 0 everytime before CacheExecute() and Execute(). |
---|
| 3609 | <p><b>3.70 29 July 2003</b> |
---|
| 3610 | <p>Added new SQLite driver. Tested on PHP 4.3 and PHP 5. |
---|
| 3611 | <p>Added limited "sapdb" driver support - mainly date support. |
---|
| 3612 | <p>The oci8 driver did not identify NUMBER with no defined precision correctly. |
---|
| 3613 | <p>Added ADODB_FORCE_NULLS, if set, then PHP nulls are converted to SQL nulls |
---|
| 3614 | in GetInsertSQL/GetUpdateSQL. |
---|
| 3615 | <p>DBDate() and DBTimeStamp() format for postgresql had problems. Fixed. |
---|
| 3616 | <p>Added tableoptions to ChangeTableSQL(). Thx to Mike Benoit. |
---|
| 3617 | <p>Added charset support to postgresql. Thx to Julian Tarkhanov. |
---|
| 3618 | <p>Changed OS check for MS-Windows to prevent confusion with darWIN (MacOS) |
---|
| 3619 | <p>Timestamp format for db2 was wrong. Changed to yyyy-mm-dd-hh.mm.ss.nnnnnn. |
---|
| 3620 | <p>adodb-cryptsession.php includes wrong. Fixed. |
---|
| 3621 | <p>Added MetaForeignKeys(). Supported by mssql, odbc_mssql and oci8. |
---|
| 3622 | <p>Fixed some oci8 MetaColumns/MetaPrimaryKeys bugs. Thx to Walt Boring. |
---|
| 3623 | <p>adodb_getcount() did not init qryRecs to 0. Missing "WHERE" clause checking |
---|
| 3624 | in GetUpdateSQL fixed. Thx to Sebastiaan van Stijn. |
---|
| 3625 | <p>Added support for only 'VIEWS' and "TABLES" in MetaTables. From Walt Boring. |
---|
| 3626 | <p>Upgraded to adodb-xmlschema.inc.php 0.0.2. |
---|
| 3627 | <p>NConnect for mysql now returns value. Thx to Dennis Verspuij. |
---|
| 3628 | <p>ADODB_FETCH_BOTH support added to interbase/firebird. |
---|
| 3629 | <p>Czech language file contributed by Kamil Jakubovic jake#host.sk. |
---|
| 3630 | <p>PostgreSQL BlobDecode did not use _connectionID properly. Thx to Juraj Chlebec. |
---|
| 3631 | <p>Added some new initialization stuff for Informix. Thx to "Andrea Pinnisi" pinnisi#sysnet.it |
---|
| 3632 | <p>ADODB_ASSOC_CASE constant wrong in sybase _fetch(). Fixed. |
---|
| 3633 | <p><b>3.60 16 June 2003</b> |
---|
| 3634 | <p>We now SET CONCAT_NULL_YIELDS_NULL OFF for odbc_mssql driver to be compat with |
---|
| 3635 | mssql driver. |
---|
| 3636 | <p>The property $emptyDate missing from connection class. Also changed 1903 to |
---|
| 3637 | constant (TIMESTAMP_FIRST_YEAR=100). Thx to Sebastiaan van Stijn. |
---|
| 3638 | <p>ADOdb speedup optimization - we now return all arrays by reference. |
---|
| 3639 | <p>Now DBDate() and DBTimeStamp() now accepts the string 'null' as a parameter. |
---|
| 3640 | Suggested by vincent. |
---|
| 3641 | <p>Added GetArray() to connection class. |
---|
| 3642 | <p>Added not_null check in informix metacolumns(). |
---|
| 3643 | <p>Connection parameters for postgresql did not work correctly when port was defined. |
---|
| 3644 | <p>DB2 is now a tested driver, making adodb 100% compatible. Extensive changes |
---|
| 3645 | to odbc driver for DB2, including implementing serverinfo() and SQLDate(), switching |
---|
| 3646 | to SQL_CUR_USE_ODBC as the cursor mode, and lastAffectedRows and SelectLimit() |
---|
| 3647 | fixes. |
---|
| 3648 | <p>The odbc driver's FetchField() field names did not obey ADODB_ASSOC_CASE. Fixed. |
---|
| 3649 | <p>Some bugs in adodb_backtrace() fixed. |
---|
| 3650 | <p>Added "INT IDENTITY" type to adorecordset::MetaType() to support odbc_mssql |
---|
| 3651 | properly. |
---|
| 3652 | <p>MetaColumns() for oci8, mssql, odbc revised to support scale. Also minor revisions |
---|
| 3653 | to odbc MetaColumns() for vfp and db2 compat. |
---|
| 3654 | <p>Added unsigned support to mysql datadict class. Thx to iamsure. |
---|
| 3655 | <p>Infinite loop in mssql MoveNext() fixed when ADODB_FETCH_ASSOC used. Thx to |
---|
| 3656 | Josh R, Night_Wulfe#hotmail.com. |
---|
| 3657 | <p>ChangeTableSQL contributed by Florian Buzin. |
---|
| 3658 | <p>The odbc_mssql driver now sets CONCAT_NULL_YIELDS_NULL OFF for compat with |
---|
| 3659 | mssql driver. |
---|
| 3660 | <hr> |
---|
| 3661 | <p><strong>0.10 Sept 9 2000</strong> First release |
---|
| 3662 | <h3><strong>Old changelog history moved to <a href=old-changelog.htm>old-changelog.htm</a>. |
---|
| 3663 | </strong></h3> |
---|
| 3664 | <p> </p> |
---|
| 3665 | <p> </font> |
---|
| 3666 | </body> |
---|
| 3667 | </html> |
---|