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