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