3 global $ADODB_INCLUDED_LIB;
\r
4 $ADODB_INCLUDED_LIB = 1;
\r
7 @version V4.22 15 Apr 2004 (c) 2000-2004 John Lim (jlim\@natsoft.com.my). All rights reserved.
\r
8 Released under both BSD license and Lesser GPL library license.
\r
9 Whenever there is any discrepancy between the two licenses,
\r
10 the BSD license will take precedence. See License.txt.
\r
11 Set tabs to 4 for best viewing.
\r
13 Less commonly used functions are placed here to reduce size of adodb.inc.php.
\r
17 // Force key to upper.
\r
18 // See also http://www.php.net/manual/en/function.array-change-key-case.php
\r
19 function _array_change_key_case($an_array)
\r
21 if (is_array($an_array)) {
\r
22 foreach($an_array as $key=>$value)
\r
23 $new_array[strtoupper($key)] = $value;
\r
31 function _adodb_replace(&$zthis, $table, $fieldArray, $keyCol, $autoQuote, $has_autoinc)
\r
33 if (count($fieldArray) == 0) return 0;
\r
37 if (!is_array($keyCol)) {
\r
38 $keyCol = array($keyCol);
\r
40 foreach($fieldArray as $k => $v) {
\r
41 if ($autoQuote && !is_numeric($v) and strncmp($v,"'",1) !== 0 and strcasecmp($v,'null')!=0) {
\r
42 $v = $zthis->qstr($v);
\r
43 $fieldArray[$k] = $v;
\r
45 if (in_array($k,$keyCol)) continue; // skip UPDATE if is key
\r
55 foreach ($keyCol as $v) {
\r
56 if ($where) $where .= " and $v=$fieldArray[$v]";
\r
57 else $where = "$v=$fieldArray[$v]";
\r
60 if ($uSet && $where) {
\r
61 $update = "UPDATE $table SET $uSet WHERE $where";
\r
63 $rs = $zthis->Execute($update);
\r
65 if ($zthis->poorAffectedRows) {
\r
67 The Select count(*) wipes out any errors that the update would have returned.
\r
68 http://phplens.com/lens/lensforum/msgs.php?id=5696
\r
70 if ($zthis->ErrorNo()<>0) return 0;
\r
72 # affected_rows == 0 if update field values identical to old values
\r
73 # for mysql - which is silly.
\r
75 $cnt = $zthis->GetOne("select count(*) from $table where $where");
\r
76 if ($cnt > 0) return 1; // record already exists
\r
78 if (($zthis->Affected_Rows()>0)) return 1;
\r
81 // print "<p>Error=".$this->ErrorNo().'<p>';
\r
83 foreach($fieldArray as $k => $v) {
\r
84 if ($has_autoinc && in_array($k,$keyCol)) continue; // skip autoinc col
\r
95 $insert = "INSERT INTO $table ($iCols) VALUES ($iVals)";
\r
96 $rs = $zthis->Execute($insert);
\r
97 return ($rs) ? 2 : 0;
\r
100 // Requires $ADODB_FETCH_MODE = ADODB_FETCH_NUM
\r
101 function _adodb_getmenu(&$zthis, $name,$defstr='',$blank1stItem=true,$multiple=false,
\r
102 $size=0, $selectAttr='',$compareFields0=true)
\r
106 if ($multiple or is_array($defstr)) {
\r
107 if ($size==0) $size=5;
\r
108 $attr = " multiple size=$size";
\r
109 if (!strpos($name,'[]')) $name .= '[]';
\r
110 } else if ($size) $attr = " size=$size";
\r
113 $s = "<select name=\"$name\"$attr $selectAttr>";
\r
114 if ($blank1stItem)
\r
115 if (is_string($blank1stItem)) {
\r
116 $barr = explode(':',$blank1stItem);
\r
117 if (sizeof($barr) == 1) $barr[] = '';
\r
118 $s .= "\n<option value=\"".$barr[0]."\">".$barr[1]."</option>";
\r
119 } else $s .= "\n<option></option>";
\r
121 if ($zthis->FieldCount() > 1) $hasvalue=true;
\r
122 else $compareFields0 = true;
\r
125 while(!$zthis->EOF) {
\r
126 $zval = rtrim(reset($zthis->fields));
\r
127 if (sizeof($zthis->fields) > 1) {
\r
128 if (isset($zthis->fields[1]))
\r
129 $zval2 = rtrim($zthis->fields[1]);
\r
131 $zval2 = rtrim(next($zthis->fields));
\r
133 $selected = ($compareFields0) ? $zval : $zval2;
\r
135 if ($blank1stItem && $zval=="") {
\r
136 $zthis->MoveNext();
\r
140 $value = ' value="'.htmlspecialchars($zval2).'"';
\r
142 if (is_array($defstr)) {
\r
144 if (in_array($selected,$defstr))
\r
145 $s .= "<option selected$value>".htmlspecialchars($zval).'</option>';
\r
147 $s .= "\n<option".$value.'>'.htmlspecialchars($zval).'</option>';
\r
150 if (strcasecmp($selected,$defstr)==0)
\r
151 $s .= "<option selected$value>".htmlspecialchars($zval).'</option>';
\r
153 $s .= "\n<option".$value.'>'.htmlspecialchars($zval).'</option>';
\r
155 $zthis->MoveNext();
\r
158 return $s ."\n</select>\n";
\r
162 Count the number of records this sql statement will return by using
\r
163 query rewriting techniques...
\r
165 Does not work with UNIONs.
\r
167 function _adodb_getcount(&$zthis, $sql,$inputarr=false,$secs2cache=0)
\r
171 if (preg_match("/^\s*SELECT\s+DISTINCT/is", $sql) || preg_match('/\s+GROUP\s+BY\s+/is',$sql)) {
\r
172 // ok, has SELECT DISTINCT or GROUP BY so see if we can use a table alias
\r
173 // but this is only supported by oracle and postgresql...
\r
174 if ($zthis->dataProvider == 'oci8') {
\r
176 $rewritesql = preg_replace('/(\sORDER\s+BY\s.*)/is','',$sql);
\r
177 $rewritesql = "SELECT COUNT(*) FROM ($rewritesql)";
\r
179 } else if ( $zthis->databaseType == 'postgres' || $zthis->databaseType == 'postgres7') {
\r
181 $info = $zthis->ServerInfo();
\r
182 if (substr($info['version'],0,3) >= 7.1) { // good till version 999
\r
183 $rewritesql = preg_replace('/(\sORDER\s+BY\s.*)/is','',$sql);
\r
184 $rewritesql = "SELECT COUNT(*) FROM ($rewritesql) _ADODB_ALIAS_";
\r
188 // now replace SELECT ... FROM with SELECT COUNT(*) FROM
\r
190 $rewritesql = preg_replace(
\r
191 '/^\s*SELECT\s.*\s+FROM\s/Uis','SELECT COUNT(*) FROM ',$sql);
\r
193 // fix by alexander zhukov, alex#unipack.ru, because count(*) and 'order by' fails
\r
194 // with mssql, access and postgresql. Also a good speedup optimization - skips sorting!
\r
195 $rewritesql = preg_replace('/(\sORDER\s+BY\s.*)/is','',$rewritesql);
\r
198 if (isset($rewritesql) && $rewritesql != $sql) {
\r
200 // we only use half the time of secs2cache because the count can quickly
\r
201 // become inaccurate if new records are added
\r
202 $qryRecs = $zthis->CacheGetOne($secs2cache/2,$rewritesql,$inputarr);
\r
205 $qryRecs = $zthis->GetOne($rewritesql,$inputarr);
\r
207 if ($qryRecs !== false) return $qryRecs;
\r
210 //--------------------------------------------
\r
211 // query rewrite failed - so try slower way...
\r
213 // strip off unneeded ORDER BY
\r
214 $rewritesql = preg_replace('/(\sORDER\s+BY\s.*)/is','',$sql);
\r
215 $rstest = &$zthis->Execute($rewritesql,$inputarr);
\r
217 $qryRecs = $rstest->RecordCount();
\r
218 if ($qryRecs == -1) {
\r
219 global $ADODB_EXTENSION;
\r
220 // some databases will return -1 on MoveLast() - change to MoveNext()
\r
221 if ($ADODB_EXTENSION) {
\r
222 while(!$rstest->EOF) {
\r
223 adodb_movenext($rstest);
\r
226 while(!$rstest->EOF) {
\r
227 $rstest->MoveNext();
\r
230 $qryRecs = $rstest->_currentRow;
\r
233 if ($qryRecs == -1) return 0;
\r
240 Code originally from "Cornel G" <conyg@fx.ro>
\r
242 This code will not work with SQL that has UNION in it
\r
244 Also if you are using CachePageExecute(), there is a strong possibility that
\r
245 data will get out of synch. use CachePageExecute() only with tables that
\r
248 function &_adodb_pageexecute_all_rows(&$zthis, $sql, $nrows, $page,
\r
249 $inputarr=false, $secs2cache=0)
\r
251 $atfirstpage = false;
\r
252 $atlastpage = false;
\r
255 // If an invalid nrows is supplied,
\r
256 // we assume a default value of 10 rows per page
\r
257 if (!isset($nrows) || $nrows <= 0) $nrows = 10;
\r
259 $qryRecs = false; //count records for no offset
\r
261 $qryRecs = _adodb_getcount($zthis,$sql,$inputarr,$secs2cache);
\r
262 $lastpageno = (int) ceil($qryRecs / $nrows);
\r
263 $zthis->_maxRecordCount = $qryRecs;
\r
265 // If page number <= 1, then we are at the first page
\r
266 if (!isset($page) || $page <= 1) {
\r
268 $atfirstpage = true;
\r
271 // ***** Here we check whether $page is the last page or
\r
272 // whether we are trying to retrieve
\r
273 // a page number greater than the last page number.
\r
274 if ($page >= $lastpageno) {
\r
275 $page = $lastpageno;
\r
276 $atlastpage = true;
\r
279 // We get the data we want
\r
280 $offset = $nrows * ($page-1);
\r
281 if ($secs2cache > 0)
\r
282 $rsreturn = &$zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $offset, $inputarr);
\r
284 $rsreturn = &$zthis->SelectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
\r
287 // Before returning the RecordSet, we set the pagination properties we need
\r
289 $rsreturn->_maxRecordCount = $qryRecs;
\r
290 $rsreturn->rowsPerPage = $nrows;
\r
291 $rsreturn->AbsolutePage($page);
\r
292 $rsreturn->AtFirstPage($atfirstpage);
\r
293 $rsreturn->AtLastPage($atlastpage);
\r
294 $rsreturn->LastPageNo($lastpageno);
\r
299 // Iván Oliva version
\r
300 function &_adodb_pageexecute_no_last_page(&$zthis, $sql, $nrows, $page, $inputarr=false, $secs2cache=0)
\r
303 $atfirstpage = false;
\r
304 $atlastpage = false;
\r
306 if (!isset($page) || $page <= 1) { // If page number <= 1, then we are at the first page
\r
308 $atfirstpage = true;
\r
310 if ($nrows <= 0) $nrows = 10; // If an invalid nrows is supplied, we assume a default value of 10 rows per page
\r
312 // ***** Here we check whether $page is the last page or whether we are trying to retrieve a page number greater than
\r
313 // the last page number.
\r
314 $pagecounter = $page + 1;
\r
315 $pagecounteroffset = ($pagecounter * $nrows) - $nrows;
\r
316 if ($secs2cache>0) $rstest = &$zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $pagecounteroffset, $inputarr);
\r
317 else $rstest = &$zthis->SelectLimit($sql, $nrows, $pagecounteroffset, $inputarr, $secs2cache);
\r
319 while ($rstest && $rstest->EOF && $pagecounter>0) {
\r
320 $atlastpage = true;
\r
322 $pagecounteroffset = $nrows * ($pagecounter - 1);
\r
324 if ($secs2cache>0) $rstest = &$zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $pagecounteroffset, $inputarr);
\r
325 else $rstest = &$zthis->SelectLimit($sql, $nrows, $pagecounteroffset, $inputarr, $secs2cache);
\r
327 if ($rstest) $rstest->Close();
\r
329 if ($atlastpage) { // If we are at the last page or beyond it, we are going to retrieve it
\r
330 $page = $pagecounter;
\r
331 if ($page == 1) $atfirstpage = true; // We have to do this again in case the last page is the same as the first
\r
332 //... page, that is, the recordset has only 1 page.
\r
335 // We get the data we want
\r
336 $offset = $nrows * ($page-1);
\r
337 if ($secs2cache > 0) $rsreturn = &$zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $offset, $inputarr);
\r
338 else $rsreturn = &$zthis->SelectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
\r
340 // Before returning the RecordSet, we set the pagination properties we need
\r
342 $rsreturn->rowsPerPage = $nrows;
\r
343 $rsreturn->AbsolutePage($page);
\r
344 $rsreturn->AtFirstPage($atfirstpage);
\r
345 $rsreturn->AtLastPage($atlastpage);
\r
350 function _adodb_getupdatesql(&$zthis,&$rs, $arrFields,$forceUpdate=false,$magicq=false)
\r
353 printf(ADODB_BAD_RS,'GetUpdateSQL');
\r
357 $fieldUpdatedCount = 0;
\r
358 $arrFields = _array_change_key_case($arrFields);
\r
360 $hasnumeric = isset($rs->fields[0]);
\r
363 // Loop through all of the fields in the recordset
\r
364 for ($i=0, $max=$rs->FieldCount(); $i < $max; $i++) {
\r
365 // Get the field from the recordset
\r
366 $field = $rs->FetchField($i);
\r
368 // If the recordset field is one
\r
369 // of the fields passed in then process.
\r
370 $upperfname = strtoupper($field->name);
\r
371 if (adodb_key_exists($upperfname,$arrFields)) {
\r
373 // If the existing field value in the recordset
\r
374 // is different from the value passed in then
\r
375 // go ahead and append the field name and new value to
\r
376 // the update query.
\r
378 if ($hasnumeric) $val = $rs->fields[$i];
\r
379 else if (isset($rs->fields[$upperfname])) $val = $rs->fields[$upperfname];
\r
380 else if (isset($rs->fields[$field->name])) $val = $rs->fields[$field->name];
\r
381 else if (isset($rs->fields[strtolower($upperfname)])) $val = $rs->fields[strtolower($upperfname)];
\r
385 if ($forceUpdate || strcmp($val, $arrFields[$upperfname])) {
\r
386 // Set the counter for the number of fields that will be updated.
\r
387 $fieldUpdatedCount++;
\r
389 // Based on the datatype of the field
\r
390 // Format the value properly for the database
\r
391 $type = $rs->MetaType($field->type);
\r
393 // is_null requires php 4.0.4
\r
394 if ((defined('ADODB_FORCE_NULLS') && is_null($arrFields[$upperfname])) ||
\r
395 $arrFields[$upperfname] === 'null') {
\r
396 $setFields .= $field->name . " = null, ";
\r
398 if ($type == 'null') {
\r
401 //we do this so each driver can customize the sql for
\r
402 //DB specific column types.
\r
403 //Oracle needs BLOB types to be handled with a returning clause
\r
404 //postgres has special needs as well
\r
405 $setFields .= _adodb_column_sql($zthis, 'U', $type, $upperfname,
\r
406 $arrFields, $magicq);
\r
412 // If there were any modified fields then build the rest of the update query.
\r
413 if ($fieldUpdatedCount > 0 || $forceUpdate) {
\r
414 // Get the table name from the existing query.
\r
415 preg_match("/FROM\s+".ADODB_TABLE_REGEX."/is", $rs->sql, $tableName);
\r
417 // Get the full where clause excluding the word "WHERE" from
\r
418 // the existing query.
\r
419 preg_match('/\sWHERE\s(.*)/is', $rs->sql, $whereClause);
\r
422 // not a good hack, improvements?
\r
424 preg_match('/\s(LIMIT\s.*)/is', $whereClause[1], $discard);
\r
426 $whereClause = array(false,false);
\r
429 $whereClause[1] = substr($whereClause[1], 0, strlen($whereClause[1]) - strlen($discard[1]));
\r
431 $sql = 'UPDATE '.$tableName[1].' SET '.substr($setFields, 0, -2);
\r
432 if (strlen($whereClause[1]) > 0)
\r
433 $sql .= ' WHERE '.$whereClause[1];
\r
442 function adodb_key_exists($key, &$arr)
\r
444 if (!defined('ADODB_FORCE_NULLS')) {
\r
445 // the following is the old behaviour where null or empty fields are ignored
\r
446 return (!empty($arr[$key])) || (isset($arr[$key]) && strlen($arr[$key])>0);
\r
449 if (isset($arr[$key])) return true;
\r
450 ## null check below
\r
451 if (ADODB_PHPVER >= 0x4010) return array_key_exists($key,$arr);
\r
456 * There is a special case of this function for the oci8 driver.
\r
457 * The proper way to handle an insert w/ a blob in oracle requires
\r
458 * a returning clause with bind variables and a descriptor blob.
\r
462 function _adodb_getinsertsql(&$zthis,&$rs,$arrFields,$magicq=false)
\r
468 $arrFields = _array_change_key_case($arrFields);
\r
469 $fieldInsertedCount = 0;
\r
471 if (is_string($rs)) {
\r
472 //ok we have a table name
\r
473 //try and get the column info ourself.
\r
476 //we need an object for the recordSet
\r
477 //because we have to call MetaType.
\r
478 //php can't do a $rsclass::MetaType()
\r
479 $rsclass = $zthis->rsPrefix.$zthis->databaseType;
\r
480 $recordSet =& new $rsclass(-1,$zthis->fetchMode);
\r
481 $recordSet->connection = &$zthis;
\r
483 $columns = $zthis->MetaColumns( $tableName );
\r
484 } else if (is_subclass_of($rs, 'adorecordset')) {
\r
485 for ($i=0, $max=$rs->FieldCount(); $i < $max; $i++)
\r
486 $columns[] = $rs->FetchField($i);
\r
490 printf(ADODB_BAD_RS,'GetInsertSQL');
\r
494 // Loop through all of the fields in the recordset
\r
495 foreach( $columns as $field ) {
\r
496 $upperfname = strtoupper($field->name);
\r
497 if (adodb_key_exists($upperfname,$arrFields)) {
\r
499 // Set the counter for the number of fields that will be inserted.
\r
500 $fieldInsertedCount++;
\r
502 // Get the name of the fields to insert
\r
503 $fields .= $field->name . ", ";
\r
505 $type = $recordSet->MetaType($field->type);
\r
507 if ((defined('ADODB_FORCE_NULLS') && is_null($arrFields[$upperfname])) ||
\r
508 $arrFields[$upperfname] === 'null') {
\r
509 $values .= "null, ";
\r
511 //we do this so each driver can customize the sql for
\r
512 //DB specific column types.
\r
513 //Oracle needs BLOB types to be handled with a returning clause
\r
514 //postgres has special needs as well
\r
515 $values .= _adodb_column_sql($zthis, 'I', $type, $upperfname,
\r
516 $arrFields, $magicq);
\r
522 // If there were any inserted fields then build the rest of the insert query.
\r
523 if ($fieldInsertedCount <= 0) return false;
\r
525 // Get the table name from the existing query.
\r
527 preg_match("/FROM\s+".ADODB_TABLE_REGEX."/is", $rs->sql, $tableName);
\r
528 $tableName = $tableName[1];
\r
531 // Strip off the comma and space on the end of both the fields
\r
532 // and their values.
\r
533 $fields = substr($fields, 0, -2);
\r
534 $values = substr($values, 0, -2);
\r
536 // Append the fields and their values to the insert query.
\r
537 return 'INSERT INTO '.$tableName.' ( '.$fields.' ) VALUES ( '.$values.' )';
\r
542 * This private method is used to help construct
\r
543 * the update/sql which is generated by GetInsertSQL and GetUpdateSQL.
\r
544 * It handles the string construction of 1 column -> sql string based on
\r
545 * the column type. We want to do 'safe' handling of BLOBs
\r
547 * @param string the type of sql we are trying to create
\r
549 * @param string column data type from the db::MetaType() method
\r
550 * @param string the column name
\r
551 * @param array the column value
\r
556 function _adodb_column_sql_oci8(&$zthis,$action, $type, $fname, $arrFields, $magicq)
\r
560 // Based on the datatype of the field
\r
561 // Format the value properly for the database
\r
564 //in order to handle Blobs correctly, we need
\r
565 //to do some magic for Oracle
\r
567 //we need to create a new descriptor to handle
\r
569 if (!empty($zthis->hasReturningInto)) {
\r
570 if ($action == 'I') {
\r
571 $sql = 'empty_blob(), ';
\r
573 $sql = $fname. '=empty_blob(), ';
\r
575 //add the variable to the returning clause array
\r
576 //so the user can build this later in
\r
577 //case they want to add more to it
\r
578 $zthis->_returningArray[$fname] = ':xx'.$fname.'xx';
\r
580 //this is to maintain compatibility
\r
581 //with older adodb versions.
\r
582 $sql = _adodb_column_sql($zthis, $action, $type, $fname, $arrFields, $magicq,false);
\r
587 //we need to do some more magic here for long variables
\r
588 //to handle these correctly in oracle.
\r
590 //create a safe bind var name
\r
591 //to avoid conflicts w/ dupes.
\r
592 if (!empty($zthis->hasReturningInto)) {
\r
593 if ($action == 'I') {
\r
594 $sql = ':xx'.$fname.'xx, ';
\r
596 $sql = $fname.'=:xx'.$fname.'xx, ';
\r
598 //add the variable to the returning clause array
\r
599 //so the user can build this later in
\r
600 //case they want to add more to it
\r
601 $zthis->_returningArray[$fname] = ':xx'.$fname.'xx';
\r
603 //this is to maintain compatibility
\r
604 //with older adodb versions.
\r
605 $sql = _adodb_column_sql($zthis, $action, $type, $fname, $arrFields, $magicq,false);
\r
610 $sql = _adodb_column_sql($zthis, $action, $type, $fname, $arrFields, $magicq,false);
\r
617 function _adodb_column_sql(&$zthis, $action, $type, $fname, $arrFields, $magicq, $recurse=true)
\r
621 switch($zthis->dataProvider) {
\r
623 if ($type == 'L') $type = 'C';
\r
626 return _adodb_column_sql_oci8($zthis, $action, $type, $fname, $arrFields, $magicq);
\r
637 if ($action == 'I') {
\r
638 $sql = $zthis->qstr($arrFields[$fname],$magicq) . ", ";
\r
640 $sql .= $fname . "=" . $zthis->qstr($arrFields[$fname],$magicq) . ", ";
\r
645 if ($action == 'I') {
\r
646 $sql = $zthis->DBDate($arrFields[$fname]) . ", ";
\r
648 $sql .= $fname . "=" . $zthis->DBDate($arrFields[$fname]) . ", ";
\r
653 if ($action == 'I') {
\r
654 $sql = $zthis->DBTimeStamp($arrFields[$fname]) . ", ";
\r
656 $sql .= $fname . "=" . $zthis->DBTimeStamp($arrFields[$fname]) . ", ";
\r
661 $val = $arrFields[$fname];
\r
662 if (empty($val)) $val = '0';
\r
665 if ($action == 'I') {
\r
666 $sql .= $val . ", ";
\r
668 $sql .= $fname . "=" . $val . ", ";
\r