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