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