lib/adodb/adodb-lib.inc.php

   1 <?php
   2
   3 // security - hide paths
   4 if (!defined('ADODB_DIR')) die();
   5
   6 global $ADODB_INCLUDED_LIB;
   7 $ADODB_INCLUDED_LIB = 1;
   8
   9 /*
  10  @version V4.98 13 Feb 2008 (c) 2000-2008 John Lim (jlim\@natsoft.com.my). All rights reserved.
  11   Released under both BSD license and Lesser GPL library license.
  12   Whenever there is any discrepancy between the two licenses,
  13   the BSD license will take precedence. See License.txt.
  14   Set tabs to 4 for best viewing.
  15
  16   Less commonly used functions are placed here to reduce size of adodb.inc.php.
  17 */
  18
  19 function adodb_strip_order_by($sql)
  20 {
  21         $rez = preg_match('/(\sORDER\s+BY\s[^)]*)/is',$sql,$arr);
  22         if ($arr)
  23                 if (strpos($arr[0],'(') !== false) {
  24                         $at = strpos($sql,$arr[0]);
  25                         $cntin = 0;
  26                         for ($i=$at, $max=strlen($sql); $i < $max; $i++) {
  27                                 $ch = $sql[$i];
  28                                 if ($ch == '(') {
  29                                         $cntin += 1;
  30                                 } elseif($ch == ')') {
  31                                         $cntin -= 1;
  32                                         if ($cntin < 0) {
  33                                                 break;
  34                                         }
  35                                 }
  36                         }
  37                         $sql = substr($sql,0,$at).substr($sql,$i);
  38                 } else
  39                         $sql = str_replace($arr[0], '', $sql);
  40         return $sql;
  41  }
  42
  43 if (false) {
  44         $sql = 'select * from (select a from b order by a(b),b(c) desc)';
  45         $sql = '(select * from abc order by 1)';
  46         die(adodb_strip_order_by($sql));
  47 }
  48
  49 function adodb_probetypes(&$array,&$types,$probe=8)
  50 {
  51 // probe and guess the type
  52         $types = array();
  53         if ($probe > sizeof($array)) $max = sizeof($array);
  54         else $max = $probe;
  55
  56
  57         for ($j=0;$j < $max; $j++) {
  58                 $row =& $array[$j];
  59                 if (!$row) break;
  60                 $i = -1;
  61                 foreach($row as $v) {
  62                         $i += 1;
  63
  64                         if (isset($types[$i]) && $types[$i]=='C') continue;
  65
  66                         //print " ($i ".$types[$i]. "$v) ";
  67                         $v = trim($v);
  68
  69                         if (!preg_match('/^[+-]{0,1}[0-9\.]+$/',$v)) {
  70                                 $types[$i] = 'C'; // once C, always C
  71
  72                                 continue;
  73                         }
  74                         if ($j == 0) {
  75                         // If empty string, we presume is character
  76                         // test for integer for 1st row only
  77                         // after that it is up to testing other rows to prove
  78                         // that it is not an integer
  79                                 if (strlen($v) == 0) $types[$i] = 'C';
  80                                 if (strpos($v,'.') !== false) $types[$i] = 'N';
  81                                 else  $types[$i] = 'I';
  82                                 continue;
  83                         }
  84
  85                         if (strpos($v,'.') !== false) $types[$i] = 'N';
  86
  87                 }
  88         }
  89
  90 }
  91
  92 function  adodb_transpose(&$arr, &$newarr, &$hdr, &$fobjs)
  93 {
  94         $oldX = sizeof(reset($arr));
  95         $oldY = sizeof($arr);
  96
  97         if ($hdr) {
  98                 $startx = 1;
  99                 $hdr = array('Fields');
 100                 for ($y = 0; $y < $oldY; $y++) {
 101                         $hdr[] = $arr[$y][0];
 102                 }
 103         } else
 104                 $startx = 0;
 105
 106         for ($x = $startx; $x < $oldX; $x++) {
 107                 if ($fobjs) {
 108                         $o = $fobjs[$x];
 109                         $newarr[] = array($o->name);
 110                 } else
 111                         $newarr[] = array();
 112
 113                 for ($y = 0; $y < $oldY; $y++) {
 114                         $newarr[$x-$startx][] = $arr[$y][$x];
 115                 }
 116         }
 117 }
 118
 119 // Force key to upper.
 120 // See also http://www.php.net/manual/en/function.array-change-key-case.php
 121 function _array_change_key_case($an_array)
 122 {
 123         if (is_array($an_array)) {
 124                 $new_array = array();
 125                 foreach($an_array as $key=>$value)
 126                         $new_array[strtoupper($key)] = $value;
 127
 128                 return $new_array;
 129    }
 130
 131         return $an_array;
 132 }
 133
 134 function _adodb_replace(&$zthis, $table, $fieldArray, $keyCol, $autoQuote, $has_autoinc)
 135 {
 136                 if (count($fieldArray) == 0) return 0;
 137                 $first = true;
 138                 $uSet = '';
 139
 140                 if (!is_array($keyCol)) {
 141                         $keyCol = array($keyCol);
 142                 }
 143                 foreach($fieldArray as $k => $v) {
 144                         if ($v === null) {
 145                                 $v = 'NULL';
 146                                 $fieldArray[$k] = $v;
 147                         } else if ($autoQuote && !is_numeric($v) /*and strncmp($v,"'",1) !== 0 -- sql injection risk*/ and strcasecmp($v,$zthis->null2null)!=0) {
 148                                 $v = $zthis->qstr($v);
 149                                 $fieldArray[$k] = $v;
 150                         }
 151                         if (in_array($k,$keyCol)) continue; // skip UPDATE if is key
 152
 153                         if ($first) {
 154                                 $first = false;
 155                                 $uSet = "$k=$v";
 156                         } else
 157                                 $uSet .= ",$k=$v";
 158                 }
 159
 160                 $where = false;
 161                 foreach ($keyCol as $v) {
 162                         if (isset($fieldArray[$v])) {
 163                                 if ($where) $where .= ' and '.$v.'='.$fieldArray[$v];
 164                                 else $where = $v.'='.$fieldArray[$v];
 165                         }
 166                 }
 167
 168                 if ($uSet && $where) {
 169                         $update = "UPDATE $table SET $uSet WHERE $where";
 170
 171                         $rs = $zthis->Execute($update);
 172
 173
 174                         if ($rs) {
 175                                 if ($zthis->poorAffectedRows) {
 176                                 /*
 177                                  The Select count(*) wipes out any errors that the update would have returned.
 178                                 http://phplens.com/lens/lensforum/msgs.php?id=5696
 179                                 */
 180                                         if ($zthis->ErrorNo()<>0) return 0;
 181
 182                                 # affected_rows == 0 if update field values identical to old values
 183                                 # for mysql - which is silly.
 184
 185                                         $cnt = $zthis->GetOne("select count(*) from $table where $where");
 186                                         if ($cnt > 0) return 1; // record already exists
 187                                 } else {
 188                                         if (($zthis->Affected_Rows()>0)) return 1;
 189                                 }
 190                         } else
 191                                 return 0;
 192                 }
 193
 194         //      print "<p>Error=".$this->ErrorNo().'<p>';
 195                 $first = true;
 196                 foreach($fieldArray as $k => $v) {
 197                         if ($has_autoinc && in_array($k,$keyCol)) continue; // skip autoinc col
 198
 199                         if ($first) {
 200                                 $first = false;
 201                                 $iCols = "$k";
 202                                 $iVals = "$v";
 203                         } else {
 204                                 $iCols .= ",$k";
 205                                 $iVals .= ",$v";
 206                         }
 207                 }
 208                 $insert = "INSERT INTO $table ($iCols) VALUES ($iVals)";
 209                 $rs = $zthis->Execute($insert);
 210                 return ($rs) ? 2 : 0;
 211 }
 212
 213 // Requires $ADODB_FETCH_MODE = ADODB_FETCH_NUM
 214 function _adodb_getmenu(&$zthis, $name,$defstr='',$blank1stItem=true,$multiple=false,
 215                         $size=0, $selectAttr='',$compareFields0=true)
 216 {
 217         $hasvalue = false;
 218
 219         if ($multiple or is_array($defstr)) {
 220                 if ($size==0) $size=5;
 221                 $attr = ' multiple size="'.$size.'"';
 222                 if (!strpos($name,'[]')) $name .= '[]';
 223         } else if ($size) $attr = ' size="'.$size.'"';
 224         else $attr ='';
 225
 226         $s = '<select name="'.$name.'"'.$attr.' '.$selectAttr.'>';
 227         if ($blank1stItem)
 228                 if (is_string($blank1stItem))  {
 229                         $barr = explode(':',$blank1stItem);
 230                         if (sizeof($barr) == 1) $barr[] = '';
 231                         $s .= "\n<option value=\"".$barr[0]."\">".$barr[1]."</option>";
 232                 } else $s .= "\n<option></option>";
 233
 234         if ($zthis->FieldCount() > 1) $hasvalue=true;
 235         else $compareFields0 = true;
 236
 237         $value = '';
 238     $optgroup = null;
 239     $firstgroup = true;
 240     $fieldsize = $zthis->FieldCount();
 241         while(!$zthis->EOF) {
 242                 $zval = rtrim(reset($zthis->fields));
 243
 244                 if ($blank1stItem && $zval=="") {
 245                         $zthis->MoveNext();
 246                         continue;
 247                 }
 248
 249         if ($fieldsize > 1) {
 250                         if (isset($zthis->fields[1]))
 251                                 $zval2 = rtrim($zthis->fields[1]);
 252                         else
 253                                 $zval2 = rtrim(next($zthis->fields));
 254                 }
 255                 $selected = ($compareFields0) ? $zval : $zval2;
 256
 257         $group = '';
 258                 if ($fieldsize > 2) {
 259             $group = rtrim($zthis->fields[2]);
 260         }
 261 /*
 262         if ($optgroup != $group) {
 263             $optgroup = $group;
 264             if ($firstgroup) {
 265                 $firstgroup = false;
 266                 $s .="\n<optgroup label='". htmlspecialchars($group) ."'>";
 267             } else {
 268                 $s .="\n</optgroup>";
 269                 $s .="\n<optgroup label='". htmlspecialchars($group) ."'>";
 270             }
 271                 }
 272 */
 273                 if ($hasvalue)
 274                         $value = " value='".htmlspecialchars($zval2)."'";
 275
 276                 if (is_array($defstr))  {
 277
 278                         if (in_array($selected,$defstr))
 279                                 $s .= "\n<option selected='selected'$value>".htmlspecialchars($zval).'</option>';
 280                         else
 281                                 $s .= "\n<option".$value.'>'.htmlspecialchars($zval).'</option>';
 282                 }
 283                 else {
 284                         if (strcasecmp($selected,$defstr)==0)
 285                                 $s .= "\n<option selected='selected'$value>".htmlspecialchars($zval).'</option>';
 286                         else
 287                                 $s .= "\n<option".$value.'>'.htmlspecialchars($zval).'</option>';
 288                 }
 289                 $zthis->MoveNext();
 290         } // while
 291
 292     // closing last optgroup
 293     if($optgroup != null) {
 294         $s .= "\n</optgroup>";
 295         }
 296         return $s ."\n</select>\n";
 297 }
 298
 299 // Requires $ADODB_FETCH_MODE = ADODB_FETCH_NUM
 300 function _adodb_getmenu_gp(&$zthis, $name,$defstr='',$blank1stItem=true,$multiple=false,
 301                         $size=0, $selectAttr='',$compareFields0=true)
 302 {
 303         $hasvalue = false;
 304
 305         if ($multiple or is_array($defstr)) {
 306                 if ($size==0) $size=5;
 307                 $attr = ' multiple size="'.$size.'"';
 308                 if (!strpos($name,'[]')) $name .= '[]';
 309         } else if ($size) $attr = ' size="'.$size.'"';
 310         else $attr ='';
 311
 312         $s = '<select name="'.$name.'"'.$attr.' '.$selectAttr.'>';
 313         if ($blank1stItem)
 314                 if (is_string($blank1stItem))  {
 315                         $barr = explode(':',$blank1stItem);
 316                         if (sizeof($barr) == 1) $barr[] = '';
 317                         $s .= "\n<option value=\"".$barr[0]."\">".$barr[1]."</option>";
 318                 } else $s .= "\n<option></option>";
 319
 320         if ($zthis->FieldCount() > 1) $hasvalue=true;
 321         else $compareFields0 = true;
 322
 323         $value = '';
 324     $optgroup = null;
 325     $firstgroup = true;
 326     $fieldsize = sizeof($zthis->fields);
 327         while(!$zthis->EOF) {
 328                 $zval = rtrim(reset($zthis->fields));
 329
 330                 if ($blank1stItem && $zval=="") {
 331                         $zthis->MoveNext();
 332                         continue;
 333                 }
 334
 335         if ($fieldsize > 1) {
 336                         if (isset($zthis->fields[1]))
 337                                 $zval2 = rtrim($zthis->fields[1]);
 338                         else
 339                                 $zval2 = rtrim(next($zthis->fields));
 340                 }
 341                 $selected = ($compareFields0) ? $zval : $zval2;
 342
 343         $group = '';
 344                 if (isset($zthis->fields[2])) {
 345             $group = rtrim($zthis->fields[2]);
 346         }
 347
 348         if ($optgroup != $group) {
 349             $optgroup = $group;
 350             if ($firstgroup) {
 351                 $firstgroup = false;
 352                 $s .="\n<optgroup label='". htmlspecialchars($group) ."'>";
 353             } else {
 354                 $s .="\n</optgroup>";
 355                 $s .="\n<optgroup label='". htmlspecialchars($group) ."'>";
 356             }
 357                 }
 358
 359                 if ($hasvalue)
 360                         $value = " value='".htmlspecialchars($zval2)."'";
 361
 362                 if (is_array($defstr))  {
 363
 364                         if (in_array($selected,$defstr))
 365                                 $s .= "\n<option selected='selected'$value>".htmlspecialchars($zval).'</option>';
 366                         else
 367                                 $s .= "\n<option".$value.'>'.htmlspecialchars($zval).'</option>';
 368                 }
 369                 else {
 370                         if (strcasecmp($selected,$defstr)==0)
 371                                 $s .= "\n<option selected='selected'$value>".htmlspecialchars($zval).'</option>';
 372                         else
 373                                 $s .= "\n<option".$value.'>'.htmlspecialchars($zval).'</option>';
 374                 }
 375                 $zthis->MoveNext();
 376         } // while
 377
 378     // closing last optgroup
 379     if($optgroup != null) {
 380         $s .= "\n</optgroup>";
 381         }
 382         return $s ."\n</select>\n";
 383 }
 384
 385
 386 /*
 387         Count the number of records this sql statement will return by using
 388         query rewriting heuristics...
 389
 390         Does not work with UNIONs, except with postgresql and oracle.
 391
 392         Usage:
 393
 394         $conn->Connect(...);
 395         $cnt = _adodb_getcount($conn, $sql);
 396
 397 */
 398 function _adodb_getcount(&$zthis, $sql,$inputarr=false,$secs2cache=0)
 399 {
 400         $qryRecs = 0;
 401
 402          if (!empty($zthis->_nestedSQL) || preg_match("/^\s*SELECT\s+DISTINCT/is", $sql) ||
 403                 preg_match('/\s+GROUP\s+BY\s+/is',$sql) ||
 404                 preg_match('/\s+UNION\s+/is',$sql)) {
 405
 406                 $rewritesql = adodb_strip_order_by($sql);
 407
 408                 // ok, has SELECT DISTINCT or GROUP BY so see if we can use a table alias
 409                 // but this is only supported by oracle and postgresql...
 410                 if ($zthis->dataProvider == 'oci8') {
 411                         // Allow Oracle hints to be used for query optimization, Chris Wrye
 412                         if (preg_match('#/\\*+.*?\\*\\/#', $sql, $hint)) {
 413                                 $rewritesql = "SELECT ".$hint[0]." COUNT(*) FROM (".$rewritesql.")";
 414                         } else
 415                                 $rewritesql = "SELECT COUNT(*) FROM (".$rewritesql.")";
 416
 417                 } else if (strncmp($zthis->databaseType,'postgres',8) == 0 || strncmp($zthis->databaseType,'mysql',5) == 0)  {
 418                         $rewritesql = "SELECT COUNT(*) FROM ($rewritesql) _ADODB_ALIAS_";
 419                 } else {
 420                         $rewritesql = "SELECT COUNT(*) FROM ($rewritesql) ";
 421                 }
 422         } else {
 423                 // now replace SELECT ... FROM with SELECT COUNT(*) FROM
 424                 $rewritesql = preg_replace(
 425                                         '/^\s*SELECT\s.*\s+FROM\s/Uis','SELECT COUNT(*) FROM ',$sql);
 426                 // fix by alexander zhukov, alex#unipack.ru, because count(*) and 'order by' fails
 427                 // with mssql, access and postgresql. Also a good speedup optimization - skips sorting!
 428                 // also see http://phplens.com/lens/lensforum/msgs.php?id=12752
 429                 $rewritesql = adodb_strip_order_by($rewritesql);
 430         }
 431
 432         if (isset($rewritesql) && $rewritesql != $sql) {
 433                 if (preg_match('/\sLIMIT\s+[0-9]+/i',$sql,$limitarr)) $rewritesql .= $limitarr[0];
 434
 435                 if ($secs2cache) {
 436                         // we only use half the time of secs2cache because the count can quickly
 437                         // become inaccurate if new records are added
 438                         $qryRecs = $zthis->CacheGetOne($secs2cache/2,$rewritesql,$inputarr);
 439
 440                 } else {
 441                         $qryRecs = $zthis->GetOne($rewritesql,$inputarr);
 442                 }
 443                 if ($qryRecs !== false) return $qryRecs;
 444         }
 445         //--------------------------------------------
 446         // query rewrite failed - so try slower way...
 447
 448
 449         // strip off unneeded ORDER BY if no UNION
 450         if (preg_match('/\s*UNION\s*/is', $sql)) $rewritesql = $sql;
 451         else $rewritesql = $rewritesql = adodb_strip_order_by($sql);
 452
 453         if (preg_match('/\sLIMIT\s+[0-9]+/i',$sql,$limitarr)) $rewritesql .= $limitarr[0];
 454
 455         $rstest = &$zthis->Execute($rewritesql,$inputarr);
 456         if (!$rstest) $rstest = $zthis->Execute($sql,$inputarr);
 457
 458         if ($rstest) {
 459                         $qryRecs = $rstest->RecordCount();
 460                 if ($qryRecs == -1) {
 461                 global $ADODB_EXTENSION;
 462                 // some databases will return -1 on MoveLast() - change to MoveNext()
 463                         if ($ADODB_EXTENSION) {
 464                                 while(!$rstest->EOF) {
 465                                         adodb_movenext($rstest);
 466                                 }
 467                         } else {
 468                                 while(!$rstest->EOF) {
 469                                         $rstest->MoveNext();
 470                                 }
 471                         }
 472                         $qryRecs = $rstest->_currentRow;
 473                 }
 474                 $rstest->Close();
 475                 if ($qryRecs == -1) return 0;
 476         }
 477         return $qryRecs;
 478 }
 479
 480 /*
 481         Code originally from "Cornel G" <conyg@fx.ro>
 482
 483         This code might not work with SQL that has UNION in it
 484
 485         Also if you are using CachePageExecute(), there is a strong possibility that
 486         data will get out of synch. use CachePageExecute() only with tables that
 487         rarely change.
 488 */
 489 function &_adodb_pageexecute_all_rows(&$zthis, $sql, $nrows, $page,
 490                                                 $inputarr=false, $secs2cache=0)
 491 {
 492         $atfirstpage = false;
 493         $atlastpage = false;
 494         $lastpageno=1;
 495
 496         // If an invalid nrows is supplied,
 497         // we assume a default value of 10 rows per page
 498         if (!isset($nrows) || $nrows <= 0) $nrows = 10;
 499
 500         $qryRecs = false; //count records for no offset
 501
 502         $qryRecs = _adodb_getcount($zthis,$sql,$inputarr,$secs2cache);
 503         $lastpageno = (int) ceil($qryRecs / $nrows);
 504         $zthis->_maxRecordCount = $qryRecs;
 505
 506
 507
 508         // ***** Here we check whether $page is the last page or
 509         // whether we are trying to retrieve
 510         // a page number greater than the last page number.
 511         if ($page >= $lastpageno) {
 512                 $page = $lastpageno;
 513                 $atlastpage = true;
 514         }
 515
 516         // If page number <= 1, then we are at the first page
 517         if (empty($page) || $page <= 1) {
 518                 $page = 1;
 519                 $atfirstpage = true;
 520         }
 521
 522         // We get the data we want
 523         $offset = $nrows * ($page-1);
 524         if ($secs2cache > 0)
 525                 $rsreturn = &$zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $offset, $inputarr);
 526         else
 527                 $rsreturn = &$zthis->SelectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
 528
 529
 530         // Before returning the RecordSet, we set the pagination properties we need
 531         if ($rsreturn) {
 532                 $rsreturn->_maxRecordCount = $qryRecs;
 533                 $rsreturn->rowsPerPage = $nrows;
 534                 $rsreturn->AbsolutePage($page);
 535                 $rsreturn->AtFirstPage($atfirstpage);
 536                 $rsreturn->AtLastPage($atlastpage);
 537                 $rsreturn->LastPageNo($lastpageno);
 538         }
 539         return $rsreturn;
 540 }
 541
 542 // Iván Oliva version
 543 function &_adodb_pageexecute_no_last_page(&$zthis, $sql, $nrows, $page, $inputarr=false, $secs2cache=0)
 544 {
 545
 546         $atfirstpage = false;
 547         $atlastpage = false;
 548
 549         if (!isset($page) || $page <= 1) {      // If page number <= 1, then we are at the first page
 550                 $page = 1;
 551                 $atfirstpage = true;
 552         }
 553         if ($nrows <= 0) $nrows = 10;   // If an invalid nrows is supplied, we assume a default value of 10 rows per page
 554
 555         // ***** Here we check whether $page is the last page or whether we are trying to retrieve a page number greater than
 556         // the last page number.
 557         $pagecounter = $page + 1;
 558         $pagecounteroffset = ($pagecounter * $nrows) - $nrows;
 559         if ($secs2cache>0) $rstest = &$zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $pagecounteroffset, $inputarr);
 560         else $rstest = &$zthis->SelectLimit($sql, $nrows, $pagecounteroffset, $inputarr, $secs2cache);
 561         if ($rstest) {
 562                 while ($rstest && $rstest->EOF && $pagecounter>0) {
 563                         $atlastpage = true;
 564                         $pagecounter--;
 565                         $pagecounteroffset = $nrows * ($pagecounter - 1);
 566                         $rstest->Close();
 567                         if ($secs2cache>0) $rstest = &$zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $pagecounteroffset, $inputarr);
 568                         else $rstest = &$zthis->SelectLimit($sql, $nrows, $pagecounteroffset, $inputarr, $secs2cache);
 569                 }
 570                 if ($rstest) $rstest->Close();
 571         }
 572         if ($atlastpage) {      // If we are at the last page or beyond it, we are going to retrieve it
 573                 $page = $pagecounter;
 574                 if ($page == 1) $atfirstpage = true;    // We have to do this again in case the last page is the same as the first
 575                         //... page, that is, the recordset has only 1 page.
 576         }
 577
 578         // We get the data we want
 579         $offset = $nrows * ($page-1);
 580         if ($secs2cache > 0) $rsreturn = &$zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $offset, $inputarr);
 581         else $rsreturn = &$zthis->SelectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
 582
 583         // Before returning the RecordSet, we set the pagination properties we need
 584         if ($rsreturn) {
 585                 $rsreturn->rowsPerPage = $nrows;
 586                 $rsreturn->AbsolutePage($page);
 587                 $rsreturn->AtFirstPage($atfirstpage);
 588                 $rsreturn->AtLastPage($atlastpage);
 589         }
 590         return $rsreturn;
 591 }
 592
 593 function _adodb_getupdatesql(&$zthis,&$rs, $arrFields,$forceUpdate=false,$magicq=false,$force=2)
 594 {
 595         global $ADODB_QUOTE_FIELDNAMES;
 596
 597                 if (!$rs) {
 598                         printf(ADODB_BAD_RS,'GetUpdateSQL');
 599                         return false;
 600                 }
 601
 602                 $fieldUpdatedCount = 0;
 603                 $arrFields = _array_change_key_case($arrFields);
 604
 605                 $hasnumeric = isset($rs->fields[0]);
 606                 $setFields = '';
 607
 608                 // Loop through all of the fields in the recordset
 609                 for ($i=0, $max=$rs->FieldCount(); $i < $max; $i++) {
 610                         // Get the field from the recordset
 611                         $field = $rs->FetchField($i);
 612
 613                         // If the recordset field is one
 614                         // of the fields passed in then process.
 615                         $upperfname = strtoupper($field->name);
 616                         if (adodb_key_exists($upperfname,$arrFields,$force)) {
 617
 618                                 // If the existing field value in the recordset
 619                                 // is different from the value passed in then
 620                                 // go ahead and append the field name and new value to
 621                                 // the update query.
 622
 623                                 if ($hasnumeric) $val = $rs->fields[$i];
 624                                 else if (isset($rs->fields[$upperfname])) $val = $rs->fields[$upperfname];
 625                                 else if (isset($rs->fields[$field->name])) $val =  $rs->fields[$field->name];
 626                                 else if (isset($rs->fields[strtolower($upperfname)])) $val =  $rs->fields[strtolower($upperfname)];
 627                                 else $val = '';
 628
 629
 630                                 if ($forceUpdate || strcmp($val, $arrFields[$upperfname])) {
 631                                         // Set the counter for the number of fields that will be updated.
 632                                         $fieldUpdatedCount++;
 633
 634                                         // Based on the datatype of the field
 635                                         // Format the value properly for the database
 636                                         $type = $rs->MetaType($field->type);
 637
 638
 639                                         if ($type == 'null') {
 640                                                 $type = 'C';
 641                                         }
 642
 643                                         if ((strpos($upperfname,' ') !== false) || ($ADODB_QUOTE_FIELDNAMES))
 644                                                 $fnameq = $zthis->nameQuote.$upperfname.$zthis->nameQuote;
 645                                         else
 646                                                 $fnameq = $upperfname;
 647
 648
 649                 // is_null requires php 4.0.4
 650                 //********************************************************//
 651                 if (is_null($arrFields[$upperfname])
 652                                         || (empty($arrFields[$upperfname]) && strlen($arrFields[$upperfname]) == 0)
 653                     || $arrFields[$upperfname] === $zthis->null2null
 654                     )
 655                 {
 656                     switch ($force) {
 657
 658                         //case 0:
 659                         //    //Ignore empty values. This is allready handled in "adodb_key_exists" function.
 660                         //break;
 661
 662                         case 1:
 663                             //Set null
 664                             $setFields .= $field->name . " = null, ";
 665                         break;
 666
 667                         case 2:
 668                             //Set empty
 669                             $arrFields[$upperfname] = "";
 670                             $setFields .= _adodb_column_sql($zthis, 'U', $type, $upperfname, $fnameq,$arrFields, $magicq);
 671                         break;
 672                                                 default:
 673                         case 3:
 674                             //Set the value that was given in array, so you can give both null and empty values
 675                             if (is_null($arrFields[$upperfname]) || $arrFields[$upperfname] === $zthis->null2null) {
 676                                 $setFields .= $field->name . " = null, ";
 677                             } else {
 678                                 $setFields .= _adodb_column_sql($zthis, 'U', $type, $upperfname, $fnameq,$arrFields, $magicq);
 679                             }
 680                         break;
 681                     }
 682                 //********************************************************//
 683                 } else {
 684                                                 //we do this so each driver can customize the sql for
 685                                                 //DB specific column types.
 686                                                 //Oracle needs BLOB types to be handled with a returning clause
 687                                                 //postgres has special needs as well
 688                                                 $setFields .= _adodb_column_sql($zthis, 'U', $type, $upperfname, $fnameq,
 689                                                                                                                   $arrFields, $magicq);
 690                                         }
 691                                 }
 692                         }
 693                 }
 694
 695                 // If there were any modified fields then build the rest of the update query.
 696                 if ($fieldUpdatedCount > 0 || $forceUpdate) {
 697                                         // Get the table name from the existing query.
 698                         if (!empty($rs->tableName)) $tableName = $rs->tableName;
 699                         else {
 700                                 preg_match("/FROM\s+".ADODB_TABLE_REGEX."/is", $rs->sql, $tableName);
 701                                 $tableName = $tableName[1];
 702                         }
 703                         // Get the full where clause excluding the word "WHERE" from
 704                         // the existing query.
 705                         preg_match('/\sWHERE\s(.*)/is', $rs->sql, $whereClause);
 706
 707                         $discard = false;
 708                         // not a good hack, improvements?
 709                         if ($whereClause) {
 710                         #var_dump($whereClause);
 711                                 if (preg_match('/\s(ORDER\s.*)/is', $whereClause[1], $discard));
 712                                 else if (preg_match('/\s(LIMIT\s.*)/is', $whereClause[1], $discard));
 713                                 else if (preg_match('/\s(FOR UPDATE.*)/is', $whereClause[1], $discard));
 714                                 else preg_match('/\s.*(\) WHERE .*)/is', $whereClause[1], $discard); # see http://sourceforge.net/tracker/index.php?func=detail&aid=1379638&group_id=42718&atid=433976
 715                         } else
 716                                 $whereClause = array(false,false);
 717
 718                         if ($discard)
 719                                 $whereClause[1] = substr($whereClause[1], 0, strlen($whereClause[1]) - strlen($discard[1]));
 720
 721                         $sql = 'UPDATE '.$tableName.' SET '.substr($setFields, 0, -2);
 722                         if (strlen($whereClause[1]) > 0)
 723                                 $sql .= ' WHERE '.$whereClause[1];
 724
 725                         return $sql;
 726
 727                 } else {
 728                         return false;
 729         }
 730 }
 731
 732 function adodb_key_exists($key, &$arr,$force=2)
 733 {
 734         if ($force<=0) {
 735                 // the following is the old behaviour where null or empty fields are ignored
 736                 return (!empty($arr[$key])) || (isset($arr[$key]) && strlen($arr[$key])>0);
 737         }
 738
 739         if (isset($arr[$key])) return true;
 740         ## null check below
 741         if (ADODB_PHPVER >= 0x4010) return array_key_exists($key,$arr);
 742         return false;
 743 }
 744
 745 /**
 746  * There is a special case of this function for the oci8 driver.
 747  * The proper way to handle an insert w/ a blob in oracle requires
 748  * a returning clause with bind variables and a descriptor blob.
 749  *
 750  *
 751  */
 752 function _adodb_getinsertsql(&$zthis,&$rs,$arrFields,$magicq=false,$force=2)
 753 {
 754 static $cacheRS = false;
 755 static $cacheSig = 0;
 756 static $cacheCols;
 757         global $ADODB_QUOTE_FIELDNAMES;
 758
 759         $tableName = '';
 760         $values = '';
 761         $fields = '';
 762         $recordSet = null;
 763         $arrFields = _array_change_key_case($arrFields);
 764         $fieldInsertedCount = 0;
 765
 766         if (is_string($rs)) {
 767                 //ok we have a table name
 768                 //try and get the column info ourself.
 769                 $tableName = $rs;
 770
 771                 //we need an object for the recordSet
 772                 //because we have to call MetaType.
 773                 //php can't do a $rsclass::MetaType()
 774                 $rsclass = $zthis->rsPrefix.$zthis->databaseType;
 775                 $recordSet = new $rsclass(-1,$zthis->fetchMode);
 776                 $recordSet->connection = &$zthis;
 777
 778                 if (is_string($cacheRS) && $cacheRS == $rs) {
 779                         $columns =& $cacheCols;
 780                 } else {
 781                         $columns = $zthis->MetaColumns( $tableName );
 782                         $cacheRS = $tableName;
 783                         $cacheCols = $columns;
 784                 }
 785         } else if (is_subclass_of($rs, 'adorecordset')) {
 786                 if (isset($rs->insertSig) && is_integer($cacheRS) && $cacheRS == $rs->insertSig) {
 787                         $columns =& $cacheCols;
 788                 } else {
 789                         for ($i=0, $max=$rs->FieldCount(); $i < $max; $i++)
 790                                 $columns[] = $rs->FetchField($i);
 791                         $cacheRS = $cacheSig;
 792                         $cacheCols = $columns;
 793                         $rs->insertSig = $cacheSig++;
 794                 }
 795                 $recordSet =& $rs;
 796
 797         } else {
 798                 printf(ADODB_BAD_RS,'GetInsertSQL');
 799                 return false;
 800         }
 801
 802         // Loop through all of the fields in the recordset
 803         foreach( $columns as $field ) {
 804                 $upperfname = strtoupper($field->name);
 805                 if (adodb_key_exists($upperfname,$arrFields,$force)) {
 806                         $bad = false;
 807                         if ((strpos($upperfname,' ') !== false) || ($ADODB_QUOTE_FIELDNAMES))
 808                                 $fnameq = $zthis->nameQuote.$upperfname.$zthis->nameQuote;
 809                         else
 810                                 $fnameq = $upperfname;
 811
 812                         $type = $recordSet->MetaType($field->type);
 813
 814             /********************************************************/
 815             if (is_null($arrFields[$upperfname])
 816                 || (empty($arrFields[$upperfname]) && strlen($arrFields[$upperfname]) == 0)
 817                 || $arrFields[$upperfname] === $zthis->null2null
 818                                 )
 819                {
 820                     switch ($force) {
 821
 822                         case 0: // we must always set null if missing
 823                                                         $bad = true;
 824                                                         break;
 825
 826                         case 1:
 827                             $values  .= "null, ";
 828                         break;
 829
 830                         case 2:
 831                             //Set empty
 832                             $arrFields[$upperfname] = "";
 833                             $values .= _adodb_column_sql($zthis, 'I', $type, $upperfname, $fnameq,$arrFields, $magicq);
 834                         break;
 835
 836                                                 default:
 837                         case 3:
 838                             //Set the value that was given in array, so you can give both null and empty values
 839                                                         if (is_null($arrFields[$upperfname]) || $arrFields[$upperfname] === $zthis->null2null) {
 840                                                                 $values  .= "null, ";
 841                                                         } else {
 842                                         $values .= _adodb_column_sql($zthis, 'I', $type, $upperfname, $fnameq, $arrFields, $magicq);
 843                                         }
 844                                 break;
 845                         } // switch
 846
 847             /*********************************************************/
 848                         } else {
 849                                 //we do this so each driver can customize the sql for
 850                                 //DB specific column types.
 851                                 //Oracle needs BLOB types to be handled with a returning clause
 852                                 //postgres has special needs as well
 853                                 $values .= _adodb_column_sql($zthis, 'I', $type, $upperfname, $fnameq,
 854                                                                                            $arrFields, $magicq);
 855                         }
 856
 857                         if ($bad) continue;
 858                         // Set the counter for the number of fields that will be inserted.
 859                         $fieldInsertedCount++;
 860
 861
 862                         // Get the name of the fields to insert
 863                         $fields .= $fnameq . ", ";
 864                 }
 865         }
 866
 867
 868         // If there were any inserted fields then build the rest of the insert query.
 869         if ($fieldInsertedCount <= 0)  return false;
 870
 871         // Get the table name from the existing query.
 872         if (!$tableName) {
 873                 if (!empty($rs->tableName)) $tableName = $rs->tableName;
 874                 else if (preg_match("/FROM\s+".ADODB_TABLE_REGEX."/is", $rs->sql, $tableName))
 875                         $tableName = $tableName[1];
 876                 else
 877                         return false;
 878         }
 879
 880         // Strip off the comma and space on the end of both the fields
 881         // and their values.
 882         $fields = substr($fields, 0, -2);
 883         $values = substr($values, 0, -2);
 884
 885         // Append the fields and their values to the insert query.
 886         return 'INSERT INTO '.$tableName.' ( '.$fields.' ) VALUES ( '.$values.' )';
 887 }
 888
 889
 890 /**
 891  * This private method is used to help construct
 892  * the update/sql which is generated by GetInsertSQL and GetUpdateSQL.
 893  * It handles the string construction of 1 column -> sql string based on
 894  * the column type.  We want to do 'safe' handling of BLOBs
 895  *
 896  * @param string the type of sql we are trying to create
 897  *                'I' or 'U'.
 898  * @param string column data type from the db::MetaType() method
 899  * @param string the column name
 900  * @param array the column value
 901  *
 902  * @return string
 903  *
 904  */
 905 function _adodb_column_sql_oci8(&$zthis,$action, $type, $fname, $fnameq, $arrFields, $magicq)
 906 {
 907     $sql = '';
 908
 909     // Based on the datatype of the field
 910     // Format the value properly for the database
 911     switch($type) {
 912     case 'B':
 913         //in order to handle Blobs correctly, we need
 914         //to do some magic for Oracle
 915
 916         //we need to create a new descriptor to handle
 917         //this properly
 918         if (!empty($zthis->hasReturningInto)) {
 919             if ($action == 'I') {
 920                 $sql = 'empty_blob(), ';
 921             } else {
 922                 $sql = $fnameq. '=empty_blob(), ';
 923             }
 924             //add the variable to the returning clause array
 925             //so the user can build this later in
 926             //case they want to add more to it
 927             $zthis->_returningArray[$fname] = ':xx'.$fname.'xx';
 928         } else if (empty($arrFields[$fname])){
 929             if ($action == 'I') {
 930                 $sql = 'empty_blob(), ';
 931             } else {
 932                 $sql = $fnameq. '=empty_blob(), ';
 933             }
 934         } else {
 935             //this is to maintain compatibility
 936             //with older adodb versions.
 937             $sql = _adodb_column_sql($zthis, $action, $type, $fname, $fnameq, $arrFields, $magicq,false);
 938         }
 939         break;
 940
 941     case "X":
 942         //we need to do some more magic here for long variables
 943         //to handle these correctly in oracle.
 944
 945         //create a safe bind var name
 946         //to avoid conflicts w/ dupes.
 947        if (!empty($zthis->hasReturningInto)) {
 948             if ($action == 'I') {
 949                 $sql = ':xx'.$fname.'xx, ';
 950             } else {
 951                 $sql = $fnameq.'=:xx'.$fname.'xx, ';
 952             }
 953             //add the variable to the returning clause array
 954             //so the user can build this later in
 955             //case they want to add more to it
 956             $zthis->_returningArray[$fname] = ':xx'.$fname.'xx';
 957         } else {
 958             //this is to maintain compatibility
 959             //with older adodb versions.
 960             $sql = _adodb_column_sql($zthis, $action, $type, $fname, $fnameq, $arrFields, $magicq,false);
 961         }
 962         break;
 963
 964     default:
 965         $sql = _adodb_column_sql($zthis, $action, $type, $fname, $fnameq,  $arrFields, $magicq,false);
 966         break;
 967     }
 968
 969     return $sql;
 970 }
 971
 972 function _adodb_column_sql(&$zthis, $action, $type, $fname, $fnameq, $arrFields, $magicq, $recurse=true)
 973 {
 974
 975         if ($recurse) {
 976                 switch($zthis->dataProvider)  {
 977                 case 'postgres':
 978                         if ($type == 'L') $type = 'C';
 979                         break;
 980                 case 'oci8':
 981                         return _adodb_column_sql_oci8($zthis, $action, $type, $fname, $fnameq, $arrFields, $magicq);
 982
 983                 }
 984         }
 985
 986         switch($type) {
 987                 case "C":
 988                 case "X":
 989                 case 'B':
 990                         $val = $zthis->qstr($arrFields[$fname],$magicq);
 991                         break;
 992
 993                 case "D":
 994                         $val = $zthis->DBDate($arrFields[$fname]);
 995                         break;
 996
 997                 case "T":
 998                         $val = $zthis->DBTimeStamp($arrFields[$fname]);
 999             break;
1000
1001         case "F": //Floating point number       // Moodle added
1002                 case "N":
1003                     $val = $arrFields[$fname];
1004                         if (!is_numeric($val)) $val = str_replace(',', '.', (float)$val);
1005                         break;
1006
1007         case "L": //Integer field suitable for storing booleans (0 or 1)     // Moodle added
1008                 case "I":
1009                 case "R":
1010                     $val = $arrFields[$fname];
1011                         if (!is_numeric($val)) $val = (integer) $val;
1012                     break;
1013
1014                 default:
1015                         $val = str_replace(array("'"," ","("),"",$arrFields[$fname]); // basic sql injection defence
1016                         if (empty($val)) $val = '0';
1017                         break;
1018         }
1019
1020         if ($action == 'I') return $val . ", ";
1021
1022
1023         return $fnameq . "=" . $val  . ", ";
1024
1025 }
1026
1027
1028
1029 function _adodb_debug_execute(&$zthis, $sql, $inputarr)
1030 {
1031         $ss = '';
1032         if ($inputarr) {
1033                 foreach($inputarr as $kk=>$vv) {
1034                         if (is_string($vv) && strlen($vv)>64) $vv = substr($vv,0,64).'...';
1035                         if (is_null($vv)) $ss .= "($kk=>null) ";
1036                         else $ss .= "($kk=>'$vv') ";
1037                 }
1038                 $ss = "[ $ss ]";
1039         }
1040         $sqlTxt = is_array($sql) ? $sql[0] : $sql;
1041         /*str_replace(', ','##1#__^LF',is_array($sql) ? $sql[0] : $sql);
1042         $sqlTxt = str_replace(',',', ',$sqlTxt);
1043         $sqlTxt = str_replace('##1#__^LF', ', ' ,$sqlTxt);
1044         */
1045         // check if running from browser or command-line
1046         $inBrowser = isset($_SERVER['HTTP_USER_AGENT']);
1047
1048         $dbt = $zthis->databaseType;
1049         if (isset($zthis->dsnType)) $dbt .= '-'.$zthis->dsnType;
1050         if ($inBrowser) {
1051                 if ($ss) {
1052                         $ss = '<code>'.htmlspecialchars($ss).'</code>';
1053                 }
1054                 if ($zthis->debug === -1)
1055                         ADOConnection::outp( "<br />\n($dbt): ".htmlspecialchars($sqlTxt)." &nbsp; $ss\n<br />\n",false);
1056                 else
1057                         ADOConnection::outp( "<hr />\n($dbt): ".htmlspecialchars($sqlTxt)." &nbsp; $ss\n<hr />\n",false);
1058         } else {
1059                 ADOConnection::outp("-----\n($dbt): ".$sqlTxt."\n-----\n",false);
1060         }
1061
1062         $qID = $zthis->_query($sql,$inputarr);
1063
1064         /*
1065                 Alexios Fakios notes that ErrorMsg() must be called before ErrorNo() for mssql
1066                 because ErrorNo() calls Execute('SELECT @ERROR'), causing recursion
1067         */
1068         if ($zthis->databaseType == 'mssql') {
1069         // ErrorNo is a slow function call in mssql, and not reliable in PHP 4.0.6
1070                 if($emsg = $zthis->ErrorMsg()) {
1071                         if ($err = $zthis->ErrorNo()) ADOConnection::outp($err.': '.$emsg);
1072                 }
1073         } else if (!$qID) {
1074                 ADOConnection::outp($zthis->ErrorNo() .': '. $zthis->ErrorMsg());
1075         }
1076
1077         if ($zthis->debug === 99) _adodb_backtrace(true,9999,2);
1078         return $qID;
1079 }
1080
1081 # pretty print the debug_backtrace function
1082 function _adodb_backtrace($printOrArr=true,$levels=9999,$skippy=0)
1083 {
1084         if (!function_exists('debug_backtrace')) return '';
1085
1086         $html =  (isset($_SERVER['HTTP_USER_AGENT']));
1087 // moodle change start - see readme_moodle.txt
1088         $fmt =  ($html) ? "</font><font color=\"#808080\" size=\"-1\"> %% line %4d, file: <a href=\"file:/%s\">%s</a></font>" : "%% line %4d, file: %s";
1089 // moodle change end
1090
1091         $MAXSTRLEN = 128;
1092
1093 // moodle change start - see readme_moodle.txt
1094         $s = ($html) ? '<pre align="left">' : '';
1095 // moodle change end
1096
1097         if (is_array($printOrArr)) $traceArr = $printOrArr;
1098         else $traceArr = debug_backtrace();
1099         array_shift($traceArr);
1100         array_shift($traceArr);
1101         $tabs = sizeof($traceArr)-2;
1102
1103         foreach ($traceArr as $arr) {
1104                 if ($skippy) {$skippy -= 1; continue;}
1105                 $levels -= 1;
1106                 if ($levels < 0) break;
1107
1108                 $args = array();
1109                 for ($i=0; $i < $tabs; $i++) $s .=  ($html) ? ' &nbsp; ' : "\t";
1110                 $tabs -= 1;
1111                 if ($html) $s .= '<font face="Courier New,Courier">';
1112                 if (isset($arr['class'])) $s .= $arr['class'].'.';
1113                 if (isset($arr['args']))
1114                  foreach($arr['args'] as $v) {
1115                         if (is_null($v)) $args[] = 'null';
1116                         else if (is_array($v)) $args[] = 'Array['.sizeof($v).']';
1117                         else if (is_object($v)) $args[] = 'Object:'.get_class($v);
1118                         else if (is_bool($v)) $args[] = $v ? 'true' : 'false';
1119                         else {
1120                                 $v = (string) @$v;
1121                                 $str = htmlspecialchars(substr($v,0,$MAXSTRLEN));
1122                                 if (strlen($v) > $MAXSTRLEN) $str .= '...';
1123                                 $args[] = $str;
1124                         }
1125                 }
1126                 $s .= $arr['function'].'('.implode(', ',$args).')';
1127
1128
1129                 $s .= @sprintf($fmt, $arr['line'],$arr['file'],basename($arr['file']));
1130
1131                 $s .= "\n";
1132         }
1133         if ($html) $s .= '</pre>';
1134         if ($printOrArr) print $s;
1135
1136         return $s;
1137 }
1138 /*
1139 function _adodb_find_from($sql)
1140 {
1141
1142         $sql = str_replace(array("\n","\r"), ' ', $sql);
1143         $charCount = strlen($sql);
1144
1145         $inString = false;
1146         $quote = '';
1147         $parentheseCount = 0;
1148         $prevChars = '';
1149         $nextChars = '';
1150
1151
1152         for($i = 0; $i < $charCount; $i++) {
1153
1154         $char = substr($sql,$i,1);
1155             $prevChars = substr($sql,0,$i);
1156         $nextChars = substr($sql,$i+1);
1157
1158                 if((($char == "'" || $char == '"' || $char == '`') && substr($prevChars,-1,1) != '\\') && $inString === false) {
1159                         $quote = $char;
1160                         $inString = true;
1161                 }
1162
1163                 elseif((($char == "'" || $char == '"' || $char == '`') && substr($prevChars,-1,1) != '\\') && $inString === true && $quote == $char) {
1164                         $quote = "";
1165                         $inString = false;
1166                 }
1167
1168                 elseif($char == "(" && $inString === false)
1169                         $parentheseCount++;
1170
1171                 elseif($char == ")" && $inString === false && $parentheseCount > 0)
1172                         $parentheseCount--;
1173
1174                 elseif($parentheseCount <= 0 && $inString === false && $char == " " && strtoupper(substr($prevChars,-5,5)) == " FROM")
1175                         return $i;
1176
1177         }
1178 }
1179 */
1180
1181 ?>