2 // security - hide paths
3 if (!defined('ADODB_DIR')) die();
5 global $ADODB_INCLUDED_LIB;
6 $ADODB_INCLUDED_LIB = 1;
9 @version v5.20.7 20-Sep-2016
10 @copyright (c) 2000-2013 John Lim (jlim#natsoft.com). All rights reserved.
11 @copyright (c) 2014 Damien Regad, Mark Newnham and the ADOdb community
12 Released under both BSD license and Lesser GPL library license.
13 Whenever there is any discrepancy between the two licenses,
14 the BSD license will take precedence. See License.txt.
15 Set tabs to 4 for best viewing.
17 Less commonly used functions are placed here to reduce size of adodb.inc.php.
20 function adodb_strip_order_by($sql)
22 $rez = preg_match('/(\sORDER\s+BY\s(?:[^)](?!LIMIT))*)/is', $sql, $arr);
24 if (strpos($arr[1], '(') !== false) {
25 $at = strpos($sql, $arr[1]);
27 for ($i=$at, $max=strlen($sql); $i < $max; $i++
) {
31 } elseif($ch == ')') {
38 $sql = substr($sql,0,$at).substr($sql,$i);
40 $sql = str_replace($arr[1], '', $sql);
46 $sql = 'select * from (select a from b order by a(b),b(c) desc)';
47 $sql = '(select * from abc order by 1)';
48 die(adodb_strip_order_by($sql));
51 function adodb_probetypes(&$array,&$types,$probe=8)
53 // probe and guess the type
55 if ($probe > sizeof($array)) $max = sizeof($array);
59 for ($j=0;$j < $max; $j++
) {
66 if (isset($types[$i]) && $types[$i]=='C') continue;
68 //print " ($i ".$types[$i]. "$v) ";
71 if (!preg_match('/^[+-]{0,1}[0-9\.]+$/',$v)) {
72 $types[$i] = 'C'; // once C, always C
77 // If empty string, we presume is character
78 // test for integer for 1st row only
79 // after that it is up to testing other rows to prove
80 // that it is not an integer
81 if (strlen($v) == 0) $types[$i] = 'C';
82 if (strpos($v,'.') !== false) $types[$i] = 'N';
83 else $types[$i] = 'I';
87 if (strpos($v,'.') !== false) $types[$i] = 'N';
94 function adodb_transpose(&$arr, &$newarr, &$hdr, &$fobjs)
96 $oldX = sizeof(reset($arr));
101 $hdr = array('Fields');
102 for ($y = 0; $y < $oldY; $y++
) {
103 $hdr[] = $arr[$y][0];
108 for ($x = $startx; $x < $oldX; $x++
) {
111 $newarr[] = array($o->name
);
115 for ($y = 0; $y < $oldY; $y++
) {
116 $newarr[$x-$startx][] = $arr[$y][$x];
121 // Force key to upper.
122 // See also http://www.php.net/manual/en/function.array-change-key-case.php
123 function _array_change_key_case($an_array)
125 if (is_array($an_array)) {
126 $new_array = array();
127 foreach($an_array as $key=>$value)
128 $new_array[strtoupper($key)] = $value;
136 function _adodb_replace(&$zthis, $table, $fieldArray, $keyCol, $autoQuote, $has_autoinc)
138 if (count($fieldArray) == 0) return 0;
142 if (!is_array($keyCol)) {
143 $keyCol = array($keyCol);
145 foreach($fieldArray as $k => $v) {
148 $fieldArray[$k] = $v;
149 } else if ($autoQuote && /*!is_numeric($v) /*and strncmp($v,"'",1) !== 0 -- sql injection risk*/ strcasecmp($v,$zthis->null2null
)!=0) {
150 $v = $zthis->qstr($v);
151 $fieldArray[$k] = $v;
153 if (in_array($k,$keyCol)) continue; // skip UPDATE if is key
163 foreach ($keyCol as $v) {
164 if (isset($fieldArray[$v])) {
165 if ($where) $where .= ' and '.$v.'='.$fieldArray[$v];
166 else $where = $v.'='.$fieldArray[$v];
170 if ($uSet && $where) {
171 $update = "UPDATE $table SET $uSet WHERE $where";
173 $rs = $zthis->Execute($update);
177 if ($zthis->poorAffectedRows
) {
179 The Select count(*) wipes out any errors that the update would have returned.
180 http://phplens.com/lens/lensforum/msgs.php?id=5696
182 if ($zthis->ErrorNo()<>0) return 0;
184 # affected_rows == 0 if update field values identical to old values
185 # for mysql - which is silly.
187 $cnt = $zthis->GetOne("select count(*) from $table where $where");
188 if ($cnt > 0) return 1; // record already exists
190 if (($zthis->Affected_Rows()>0)) return 1;
196 // print "<p>Error=".$this->ErrorNo().'<p>';
198 foreach($fieldArray as $k => $v) {
199 if ($has_autoinc && in_array($k,$keyCol)) continue; // skip autoinc col
210 $insert = "INSERT INTO $table ($iCols) VALUES ($iVals)";
211 $rs = $zthis->Execute($insert);
212 return ($rs) ?
2 : 0;
215 // Requires $ADODB_FETCH_MODE = ADODB_FETCH_NUM
216 function _adodb_getmenu(&$zthis, $name,$defstr='',$blank1stItem=true,$multiple=false,
217 $size=0, $selectAttr='',$compareFields0=true)
221 if ($multiple or is_array($defstr)) {
222 if ($size==0) $size=5;
223 $attr = ' multiple size="'.$size.'"';
224 if (!strpos($name,'[]')) $name .= '[]';
225 } else if ($size) $attr = ' size="'.$size.'"';
228 $s = '<select name="'.$name.'"'.$attr.' '.$selectAttr.'>';
230 if (is_string($blank1stItem)) {
231 $barr = explode(':',$blank1stItem);
232 if (sizeof($barr) == 1) $barr[] = '';
233 $s .= "\n<option value=\"".$barr[0]."\">".$barr[1]."</option>";
234 } else $s .= "\n<option></option>";
236 if ($zthis->FieldCount() > 1) $hasvalue=true;
237 else $compareFields0 = true;
242 $fieldsize = $zthis->FieldCount();
243 while(!$zthis->EOF
) {
244 $zval = rtrim(reset($zthis->fields
));
246 if ($blank1stItem && $zval=="") {
251 if ($fieldsize > 1) {
252 if (isset($zthis->fields
[1]))
253 $zval2 = rtrim($zthis->fields
[1]);
255 $zval2 = rtrim(next($zthis->fields
));
257 $selected = ($compareFields0) ?
$zval : $zval2;
260 if ($fieldsize > 2) {
261 $group = rtrim($zthis->fields
[2]);
264 if ($optgroup != $group) {
268 $s .="\n<optgroup label='". htmlspecialchars($group) ."'>";
270 $s .="\n</optgroup>";
271 $s .="\n<optgroup label='". htmlspecialchars($group) ."'>";
276 $value = " value='".htmlspecialchars($zval2)."'";
278 if (is_array($defstr)) {
280 if (in_array($selected,$defstr))
281 $s .= "\n<option selected='selected'$value>".htmlspecialchars($zval).'</option>';
283 $s .= "\n<option".$value.'>'.htmlspecialchars($zval).'</option>';
286 if (strcasecmp($selected,$defstr)==0)
287 $s .= "\n<option selected='selected'$value>".htmlspecialchars($zval).'</option>';
289 $s .= "\n<option".$value.'>'.htmlspecialchars($zval).'</option>';
294 // closing last optgroup
295 if($optgroup != null) {
296 $s .= "\n</optgroup>";
298 return $s ."\n</select>\n";
301 // Requires $ADODB_FETCH_MODE = ADODB_FETCH_NUM
302 function _adodb_getmenu_gp(&$zthis, $name,$defstr='',$blank1stItem=true,$multiple=false,
303 $size=0, $selectAttr='',$compareFields0=true)
307 if ($multiple or is_array($defstr)) {
308 if ($size==0) $size=5;
309 $attr = ' multiple size="'.$size.'"';
310 if (!strpos($name,'[]')) $name .= '[]';
311 } else if ($size) $attr = ' size="'.$size.'"';
314 $s = '<select name="'.$name.'"'.$attr.' '.$selectAttr.'>';
316 if (is_string($blank1stItem)) {
317 $barr = explode(':',$blank1stItem);
318 if (sizeof($barr) == 1) $barr[] = '';
319 $s .= "\n<option value=\"".$barr[0]."\">".$barr[1]."</option>";
320 } else $s .= "\n<option></option>";
322 if ($zthis->FieldCount() > 1) $hasvalue=true;
323 else $compareFields0 = true;
328 $fieldsize = sizeof($zthis->fields
);
329 while(!$zthis->EOF
) {
330 $zval = rtrim(reset($zthis->fields
));
332 if ($blank1stItem && $zval=="") {
337 if ($fieldsize > 1) {
338 if (isset($zthis->fields
[1]))
339 $zval2 = rtrim($zthis->fields
[1]);
341 $zval2 = rtrim(next($zthis->fields
));
343 $selected = ($compareFields0) ?
$zval : $zval2;
346 if (isset($zthis->fields
[2])) {
347 $group = rtrim($zthis->fields
[2]);
350 if ($optgroup != $group) {
354 $s .="\n<optgroup label='". htmlspecialchars($group) ."'>";
356 $s .="\n</optgroup>";
357 $s .="\n<optgroup label='". htmlspecialchars($group) ."'>";
362 $value = " value='".htmlspecialchars($zval2)."'";
364 if (is_array($defstr)) {
366 if (in_array($selected,$defstr))
367 $s .= "\n<option selected='selected'$value>".htmlspecialchars($zval).'</option>';
369 $s .= "\n<option".$value.'>'.htmlspecialchars($zval).'</option>';
372 if (strcasecmp($selected,$defstr)==0)
373 $s .= "\n<option selected='selected'$value>".htmlspecialchars($zval).'</option>';
375 $s .= "\n<option".$value.'>'.htmlspecialchars($zval).'</option>';
380 // closing last optgroup
381 if($optgroup != null) {
382 $s .= "\n</optgroup>";
384 return $s ."\n</select>\n";
389 Count the number of records this sql statement will return by using
390 query rewriting heuristics...
392 Does not work with UNIONs, except with postgresql and oracle.
397 $cnt = _adodb_getcount($conn, $sql);
400 function _adodb_getcount(&$zthis, $sql,$inputarr=false,$secs2cache=0)
404 if (!empty($zthis->_nestedSQL
) ||
preg_match("/^\s*SELECT\s+DISTINCT/is", $sql) ||
405 preg_match('/\s+GROUP\s+BY\s+/is',$sql) ||
406 preg_match('/\s+UNION\s+/is',$sql)) {
408 $rewritesql = adodb_strip_order_by($sql);
410 // ok, has SELECT DISTINCT or GROUP BY so see if we can use a table alias
411 // but this is only supported by oracle and postgresql...
412 if ($zthis->dataProvider
== 'oci8') {
413 // Allow Oracle hints to be used for query optimization, Chris Wrye
414 if (preg_match('#/\\*+.*?\\*\\/#', $sql, $hint)) {
415 $rewritesql = "SELECT ".$hint[0]." COUNT(*) FROM (".$rewritesql.")";
417 $rewritesql = "SELECT COUNT(*) FROM (".$rewritesql.")";
419 } else if (strncmp($zthis->databaseType
,'postgres',8) == 0 ||
strncmp($zthis->databaseType
,'mysql',5) == 0) {
420 $rewritesql = "SELECT COUNT(*) FROM ($rewritesql) _ADODB_ALIAS_";
422 $rewritesql = "SELECT COUNT(*) FROM ($rewritesql)";
425 // now replace SELECT ... FROM with SELECT COUNT(*) FROM
426 if ( strpos($sql, '_ADODB_COUNT') !== FALSE ) {
427 $rewritesql = preg_replace('/^\s*?SELECT\s+_ADODB_COUNT(.*)_ADODB_COUNT\s/is','SELECT COUNT(*) ',$sql);
429 $rewritesql = preg_replace('/^\s*SELECT\s.*\s+FROM\s/Uis','SELECT COUNT(*) FROM ',$sql);
431 // fix by alexander zhukov, alex#unipack.ru, because count(*) and 'order by' fails
432 // with mssql, access and postgresql. Also a good speedup optimization - skips sorting!
433 // also see http://phplens.com/lens/lensforum/msgs.php?id=12752
434 $rewritesql = adodb_strip_order_by($rewritesql);
437 if (isset($rewritesql) && $rewritesql != $sql) {
438 if (preg_match('/\sLIMIT\s+[0-9]+/i',$sql,$limitarr)) $rewritesql .= $limitarr[0];
441 // we only use half the time of secs2cache because the count can quickly
442 // become inaccurate if new records are added
443 $qryRecs = $zthis->CacheGetOne($secs2cache/2,$rewritesql,$inputarr);
446 $qryRecs = $zthis->GetOne($rewritesql,$inputarr);
448 if ($qryRecs !== false) return $qryRecs;
450 //--------------------------------------------
451 // query rewrite failed - so try slower way...
454 // strip off unneeded ORDER BY if no UNION
455 if (preg_match('/\s*UNION\s*/is', $sql)) $rewritesql = $sql;
456 else $rewritesql = $rewritesql = adodb_strip_order_by($sql);
458 if (preg_match('/\sLIMIT\s+[0-9]+/i',$sql,$limitarr)) $rewritesql .= $limitarr[0];
461 $rstest = $zthis->CacheExecute($secs2cache,$rewritesql,$inputarr);
462 if (!$rstest) $rstest = $zthis->CacheExecute($secs2cache,$sql,$inputarr);
464 $rstest = $zthis->Execute($rewritesql,$inputarr);
465 if (!$rstest) $rstest = $zthis->Execute($sql,$inputarr);
468 $qryRecs = $rstest->RecordCount();
469 if ($qryRecs == -1) {
470 global $ADODB_EXTENSION;
471 // some databases will return -1 on MoveLast() - change to MoveNext()
472 if ($ADODB_EXTENSION) {
473 while(!$rstest->EOF
) {
474 adodb_movenext($rstest);
477 while(!$rstest->EOF
) {
481 $qryRecs = $rstest->_currentRow
;
484 if ($qryRecs == -1) return 0;
490 Code originally from "Cornel G" <conyg@fx.ro>
492 This code might not work with SQL that has UNION in it
494 Also if you are using CachePageExecute(), there is a strong possibility that
495 data will get out of synch. use CachePageExecute() only with tables that
498 function _adodb_pageexecute_all_rows(&$zthis, $sql, $nrows, $page,
499 $inputarr=false, $secs2cache=0)
501 $atfirstpage = false;
505 // If an invalid nrows is supplied,
506 // we assume a default value of 10 rows per page
507 if (!isset($nrows) ||
$nrows <= 0) $nrows = 10;
509 $qryRecs = false; //count records for no offset
511 $qryRecs = _adodb_getcount($zthis,$sql,$inputarr,$secs2cache);
512 $lastpageno = (int) ceil($qryRecs / $nrows);
513 $zthis->_maxRecordCount
= $qryRecs;
517 // ***** Here we check whether $page is the last page or
518 // whether we are trying to retrieve
519 // a page number greater than the last page number.
520 if ($page >= $lastpageno) {
525 // If page number <= 1, then we are at the first page
526 if (empty($page) ||
$page <= 1) {
531 // We get the data we want
532 $offset = $nrows * ($page-1);
534 $rsreturn = $zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $offset, $inputarr);
536 $rsreturn = $zthis->SelectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
539 // Before returning the RecordSet, we set the pagination properties we need
541 $rsreturn->_maxRecordCount
= $qryRecs;
542 $rsreturn->rowsPerPage
= $nrows;
543 $rsreturn->AbsolutePage($page);
544 $rsreturn->AtFirstPage($atfirstpage);
545 $rsreturn->AtLastPage($atlastpage);
546 $rsreturn->LastPageNo($lastpageno);
551 // Iván Oliva version
552 function _adodb_pageexecute_no_last_page(&$zthis, $sql, $nrows, $page, $inputarr=false, $secs2cache=0)
555 $atfirstpage = false;
558 if (!isset($page) ||
$page <= 1) {
559 // If page number <= 1, then we are at the first page
564 // If an invalid nrows is supplied, we assume a default value of 10 rows per page
568 $pagecounteroffset = ($page * $nrows) - $nrows;
570 // To find out if there are more pages of rows, simply increase the limit or
571 // nrows by 1 and see if that number of records was returned. If it was,
572 // then we know there is at least one more page left, otherwise we are on
573 // the last page. Therefore allow non-Count() paging with single queries
574 // rather than three queries as was done before.
575 $test_nrows = $nrows +
1;
576 if ($secs2cache > 0) {
577 $rsreturn = $zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $pagecounteroffset, $inputarr);
579 $rsreturn = $zthis->SelectLimit($sql, $test_nrows, $pagecounteroffset, $inputarr, $secs2cache);
582 // Now check to see if the number of rows returned was the higher value we asked for or not.
583 if ( $rsreturn->_numOfRows
== $test_nrows ) {
584 // Still at least 1 more row, so we are not on last page yet...
585 // Remove the last row from the RS.
586 $rsreturn->_numOfRows
= ( $rsreturn->_numOfRows
- 1 );
587 } elseif ( $rsreturn->_numOfRows
== 0 && $page > 1 ) {
588 // Likely requested a page that doesn't exist, so need to find the last
589 // page and return it. Revert to original method and loop through pages
590 // until we find some data...
591 $pagecounter = $page +
1;
592 $pagecounteroffset = ($pagecounter * $nrows) - $nrows;
596 while ($rstest && $rstest->EOF
&& $pagecounter > 0) {
599 $pagecounteroffset = $nrows * ($pagecounter - 1);
602 $rstest = $zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $pagecounteroffset, $inputarr);
605 $rstest = $zthis->SelectLimit($sql, $nrows, $pagecounteroffset, $inputarr, $secs2cache);
608 if ($rstest) $rstest->Close();
611 // If we are at the last page or beyond it, we are going to retrieve it
612 $page = $pagecounter;
614 // We have to do this again in case the last page is the same as
615 // the first page, that is, the recordset has only 1 page.
619 // We get the data we want
620 $offset = $nrows * ($page-1);
621 if ($secs2cache > 0) {
622 $rsreturn = $zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $offset, $inputarr);
625 $rsreturn = $zthis->SelectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
627 } elseif ( $rsreturn->_numOfRows
< $test_nrows ) {
628 // Rows is less than what we asked for, so must be at the last page.
632 // Before returning the RecordSet, we set the pagination properties we need
634 $rsreturn->rowsPerPage
= $nrows;
635 $rsreturn->AbsolutePage($page);
636 $rsreturn->AtFirstPage($atfirstpage);
637 $rsreturn->AtLastPage($atlastpage);
642 function _adodb_getupdatesql(&$zthis,&$rs, $arrFields,$forceUpdate=false,$magicq=false,$force=2)
644 global $ADODB_QUOTE_FIELDNAMES;
647 printf(ADODB_BAD_RS
,'GetUpdateSQL');
651 $fieldUpdatedCount = 0;
652 $arrFields = _array_change_key_case($arrFields);
654 $hasnumeric = isset($rs->fields
[0]);
657 // Loop through all of the fields in the recordset
658 for ($i=0, $max=$rs->FieldCount(); $i < $max; $i++
) {
659 // Get the field from the recordset
660 $field = $rs->FetchField($i);
662 // If the recordset field is one
663 // of the fields passed in then process.
664 $upperfname = strtoupper($field->name
);
665 if (adodb_key_exists($upperfname,$arrFields,$force)) {
667 // If the existing field value in the recordset
668 // is different from the value passed in then
669 // go ahead and append the field name and new value to
672 if ($hasnumeric) $val = $rs->fields
[$i];
673 else if (isset($rs->fields
[$upperfname])) $val = $rs->fields
[$upperfname];
674 else if (isset($rs->fields
[$field->name
])) $val = $rs->fields
[$field->name
];
675 else if (isset($rs->fields
[strtolower($upperfname)])) $val = $rs->fields
[strtolower($upperfname)];
679 if ($forceUpdate ||
strcmp($val, $arrFields[$upperfname])) {
680 // Set the counter for the number of fields that will be updated.
681 $fieldUpdatedCount++
;
683 // Based on the datatype of the field
684 // Format the value properly for the database
685 $type = $rs->MetaType($field->type
);
688 if ($type == 'null') {
692 if ((strpos($upperfname,' ') !== false) ||
($ADODB_QUOTE_FIELDNAMES)) {
693 switch ($ADODB_QUOTE_FIELDNAMES) {
695 $fnameq = $zthis->nameQuote
.strtolower($field->name
).$zthis->nameQuote
;break;
697 $fnameq = $zthis->nameQuote
.$field->name
.$zthis->nameQuote
;break;
700 $fnameq = $zthis->nameQuote
.$upperfname.$zthis->nameQuote
;break;
703 $fnameq = $upperfname;
705 //********************************************************//
706 if (is_null($arrFields[$upperfname])
707 ||
(empty($arrFields[$upperfname]) && strlen($arrFields[$upperfname]) == 0)
708 ||
$arrFields[$upperfname] === $zthis->null2null
714 // //Ignore empty values. This is allready handled in "adodb_key_exists" function.
719 $setFields .= $field->name
. " = null, ";
724 $arrFields[$upperfname] = "";
725 $setFields .= _adodb_column_sql($zthis, 'U', $type, $upperfname, $fnameq,$arrFields, $magicq);
729 //Set the value that was given in array, so you can give both null and empty values
730 if (is_null($arrFields[$upperfname]) ||
$arrFields[$upperfname] === $zthis->null2null
) {
731 $setFields .= $field->name
. " = null, ";
733 $setFields .= _adodb_column_sql($zthis, 'U', $type, $upperfname, $fnameq,$arrFields, $magicq);
737 //********************************************************//
739 //we do this so each driver can customize the sql for
740 //DB specific column types.
741 //Oracle needs BLOB types to be handled with a returning clause
742 //postgres has special needs as well
743 $setFields .= _adodb_column_sql($zthis, 'U', $type, $upperfname, $fnameq,
744 $arrFields, $magicq);
750 // If there were any modified fields then build the rest of the update query.
751 if ($fieldUpdatedCount > 0 ||
$forceUpdate) {
752 // Get the table name from the existing query.
753 if (!empty($rs->tableName
)) $tableName = $rs->tableName
;
755 preg_match("/FROM\s+".ADODB_TABLE_REGEX
."/is", $rs->sql
, $tableName);
756 $tableName = $tableName[1];
758 // Get the full where clause excluding the word "WHERE" from
759 // the existing query.
760 preg_match('/\sWHERE\s(.*)/is', $rs->sql
, $whereClause);
763 // not a good hack, improvements?
765 #var_dump($whereClause);
766 if (preg_match('/\s(ORDER\s.*)/is', $whereClause[1], $discard));
767 else if (preg_match('/\s(LIMIT\s.*)/is', $whereClause[1], $discard));
768 else if (preg_match('/\s(FOR UPDATE.*)/is', $whereClause[1], $discard));
769 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
771 $whereClause = array(false,false);
774 $whereClause[1] = substr($whereClause[1], 0, strlen($whereClause[1]) - strlen($discard[1]));
776 $sql = 'UPDATE '.$tableName.' SET '.substr($setFields, 0, -2);
777 if (strlen($whereClause[1]) > 0)
778 $sql .= ' WHERE '.$whereClause[1];
787 function adodb_key_exists($key, &$arr,$force=2)
790 // the following is the old behaviour where null or empty fields are ignored
791 return (!empty($arr[$key])) ||
(isset($arr[$key]) && strlen($arr[$key])>0);
794 if (isset($arr[$key])) return true;
796 if (ADODB_PHPVER
>= 0x4010) return array_key_exists($key,$arr);
801 * There is a special case of this function for the oci8 driver.
802 * The proper way to handle an insert w/ a blob in oracle requires
803 * a returning clause with bind variables and a descriptor blob.
807 function _adodb_getinsertsql(&$zthis,&$rs,$arrFields,$magicq=false,$force=2)
809 static $cacheRS = false;
810 static $cacheSig = 0;
812 global $ADODB_QUOTE_FIELDNAMES;
818 $arrFields = _array_change_key_case($arrFields);
819 $fieldInsertedCount = 0;
821 if (is_string($rs)) {
822 //ok we have a table name
823 //try and get the column info ourself.
826 //we need an object for the recordSet
827 //because we have to call MetaType.
828 //php can't do a $rsclass::MetaType()
829 $rsclass = $zthis->rsPrefix
.$zthis->databaseType
;
830 $recordSet = new $rsclass(-1,$zthis->fetchMode
);
831 $recordSet->connection
= $zthis;
833 if (is_string($cacheRS) && $cacheRS == $rs) {
834 $columns = $cacheCols;
836 $columns = $zthis->MetaColumns( $tableName );
837 $cacheRS = $tableName;
838 $cacheCols = $columns;
840 } else if (is_subclass_of($rs, 'adorecordset')) {
841 if (isset($rs->insertSig
) && is_integer($cacheRS) && $cacheRS == $rs->insertSig
) {
842 $columns = $cacheCols;
844 for ($i=0, $max=$rs->FieldCount(); $i < $max; $i++
)
845 $columns[] = $rs->FetchField($i);
846 $cacheRS = $cacheSig;
847 $cacheCols = $columns;
848 $rs->insertSig
= $cacheSig++
;
853 printf(ADODB_BAD_RS
,'GetInsertSQL');
857 // Loop through all of the fields in the recordset
858 foreach( $columns as $field ) {
859 $upperfname = strtoupper($field->name
);
860 if (adodb_key_exists($upperfname,$arrFields,$force)) {
862 if ((strpos($upperfname,' ') !== false) ||
($ADODB_QUOTE_FIELDNAMES)) {
863 switch ($ADODB_QUOTE_FIELDNAMES) {
865 $fnameq = $zthis->nameQuote
.strtolower($field->name
).$zthis->nameQuote
;break;
867 $fnameq = $zthis->nameQuote
.$field->name
.$zthis->nameQuote
;break;
870 $fnameq = $zthis->nameQuote
.$upperfname.$zthis->nameQuote
;break;
873 $fnameq = $upperfname;
875 $type = $recordSet->MetaType($field->type
);
877 /********************************************************/
878 if (is_null($arrFields[$upperfname])
879 ||
(empty($arrFields[$upperfname]) && strlen($arrFields[$upperfname]) == 0)
880 ||
$arrFields[$upperfname] === $zthis->null2null
885 case 0: // we must always set null if missing
895 $arrFields[$upperfname] = "";
896 $values .= _adodb_column_sql($zthis, 'I', $type, $upperfname, $fnameq,$arrFields, $magicq);
901 //Set the value that was given in array, so you can give both null and empty values
902 if (is_null($arrFields[$upperfname]) ||
$arrFields[$upperfname] === $zthis->null2null
) {
905 $values .= _adodb_column_sql($zthis, 'I', $type, $upperfname, $fnameq, $arrFields, $magicq);
910 /*********************************************************/
912 //we do this so each driver can customize the sql for
913 //DB specific column types.
914 //Oracle needs BLOB types to be handled with a returning clause
915 //postgres has special needs as well
916 $values .= _adodb_column_sql($zthis, 'I', $type, $upperfname, $fnameq,
917 $arrFields, $magicq);
921 // Set the counter for the number of fields that will be inserted.
922 $fieldInsertedCount++
;
925 // Get the name of the fields to insert
926 $fields .= $fnameq . ", ";
931 // If there were any inserted fields then build the rest of the insert query.
932 if ($fieldInsertedCount <= 0) return false;
934 // Get the table name from the existing query.
936 if (!empty($rs->tableName
)) $tableName = $rs->tableName
;
937 else if (preg_match("/FROM\s+".ADODB_TABLE_REGEX
."/is", $rs->sql
, $tableName))
938 $tableName = $tableName[1];
943 // Strip off the comma and space on the end of both the fields
945 $fields = substr($fields, 0, -2);
946 $values = substr($values, 0, -2);
948 // Append the fields and their values to the insert query.
949 return 'INSERT INTO '.$tableName.' ( '.$fields.' ) VALUES ( '.$values.' )';
954 * This private method is used to help construct
955 * the update/sql which is generated by GetInsertSQL and GetUpdateSQL.
956 * It handles the string construction of 1 column -> sql string based on
957 * the column type. We want to do 'safe' handling of BLOBs
959 * @param string the type of sql we are trying to create
961 * @param string column data type from the db::MetaType() method
962 * @param string the column name
963 * @param array the column value
968 function _adodb_column_sql_oci8(&$zthis,$action, $type, $fname, $fnameq, $arrFields, $magicq)
972 // Based on the datatype of the field
973 // Format the value properly for the database
976 //in order to handle Blobs correctly, we need
977 //to do some magic for Oracle
979 //we need to create a new descriptor to handle
981 if (!empty($zthis->hasReturningInto
)) {
982 if ($action == 'I') {
983 $sql = 'empty_blob(), ';
985 $sql = $fnameq. '=empty_blob(), ';
987 //add the variable to the returning clause array
988 //so the user can build this later in
989 //case they want to add more to it
990 $zthis->_returningArray
[$fname] = ':xx'.$fname.'xx';
991 } else if (empty($arrFields[$fname])){
992 if ($action == 'I') {
993 $sql = 'empty_blob(), ';
995 $sql = $fnameq. '=empty_blob(), ';
998 //this is to maintain compatibility
999 //with older adodb versions.
1000 $sql = _adodb_column_sql($zthis, $action, $type, $fname, $fnameq, $arrFields, $magicq,false);
1005 //we need to do some more magic here for long variables
1006 //to handle these correctly in oracle.
1008 //create a safe bind var name
1009 //to avoid conflicts w/ dupes.
1010 if (!empty($zthis->hasReturningInto
)) {
1011 if ($action == 'I') {
1012 $sql = ':xx'.$fname.'xx, ';
1014 $sql = $fnameq.'=:xx'.$fname.'xx, ';
1016 //add the variable to the returning clause array
1017 //so the user can build this later in
1018 //case they want to add more to it
1019 $zthis->_returningArray
[$fname] = ':xx'.$fname.'xx';
1021 //this is to maintain compatibility
1022 //with older adodb versions.
1023 $sql = _adodb_column_sql($zthis, $action, $type, $fname, $fnameq, $arrFields, $magicq,false);
1028 $sql = _adodb_column_sql($zthis, $action, $type, $fname, $fnameq, $arrFields, $magicq,false);
1035 function _adodb_column_sql(&$zthis, $action, $type, $fname, $fnameq, $arrFields, $magicq, $recurse=true)
1039 switch($zthis->dataProvider
) {
1041 if ($type == 'L') $type = 'C';
1044 return _adodb_column_sql_oci8($zthis, $action, $type, $fname, $fnameq, $arrFields, $magicq);
1053 $val = $zthis->qstr($arrFields[$fname],$magicq);
1057 $val = $zthis->DBDate($arrFields[$fname]);
1061 $val = $zthis->DBTimeStamp($arrFields[$fname]);
1065 $val = $arrFields[$fname];
1066 if (!is_numeric($val)) $val = str_replace(',', '.', (float)$val);
1071 $val = $arrFields[$fname];
1072 if (!is_numeric($val)) $val = (integer) $val;
1076 $val = str_replace(array("'"," ","("),"",$arrFields[$fname]); // basic sql injection defence
1077 if (empty($val)) $val = '0';
1081 if ($action == 'I') return $val . ", ";
1084 return $fnameq . "=" . $val . ", ";
1090 function _adodb_debug_execute(&$zthis, $sql, $inputarr)
1094 foreach($inputarr as $kk=>$vv) {
1095 if (is_string($vv) && strlen($vv)>64) $vv = substr($vv,0,64).'...';
1096 if (is_null($vv)) $ss .= "($kk=>null) ";
1097 else $ss .= "($kk=>'$vv') ";
1101 $sqlTxt = is_array($sql) ?
$sql[0] : $sql;
1102 /*str_replace(', ','##1#__^LF',is_array($sql) ? $sql[0] : $sql);
1103 $sqlTxt = str_replace(',',', ',$sqlTxt);
1104 $sqlTxt = str_replace('##1#__^LF', ', ' ,$sqlTxt);
1106 // check if running from browser or command-line
1107 $inBrowser = isset($_SERVER['HTTP_USER_AGENT']);
1109 $dbt = $zthis->databaseType
;
1110 if (isset($zthis->dsnType
)) $dbt .= '-'.$zthis->dsnType
;
1113 $ss = '<code>'.htmlspecialchars($ss).'</code>';
1115 if ($zthis->debug
=== -1)
1116 ADOConnection
::outp( "<br>\n($dbt): ".htmlspecialchars($sqlTxt)." $ss\n<br>\n",false);
1117 else if ($zthis->debug
!== -99)
1118 ADOConnection
::outp( "<hr>\n($dbt): ".htmlspecialchars($sqlTxt)." $ss\n<hr>\n",false);
1121 if ($zthis->debug
!== -99)
1122 ADOConnection
::outp("-----<hr>\n($dbt): ".$sqlTxt." $ss\n-----<hr>\n",false);
1125 $qID = $zthis->_query($sql,$inputarr);
1128 Alexios Fakios notes that ErrorMsg() must be called before ErrorNo() for mssql
1129 because ErrorNo() calls Execute('SELECT @ERROR'), causing recursion
1131 if ($zthis->databaseType
== 'mssql') {
1132 // ErrorNo is a slow function call in mssql, and not reliable in PHP 4.0.6
1134 if($emsg = $zthis->ErrorMsg()) {
1135 if ($err = $zthis->ErrorNo()) {
1136 if ($zthis->debug
=== -99)
1137 ADOConnection
::outp( "<hr>\n($dbt): ".htmlspecialchars($sqlTxt)." $ss\n<hr>\n",false);
1139 ADOConnection
::outp($err.': '.$emsg);
1144 if ($zthis->debug
=== -99)
1145 if ($inBrowser) ADOConnection
::outp( "<hr>\n($dbt): ".htmlspecialchars($sqlTxt)." $ss\n<hr>\n",false);
1146 else ADOConnection
::outp("-----<hr>\n($dbt): ".$sqlTxt."$ss\n-----<hr>\n",false);
1148 ADOConnection
::outp($zthis->ErrorNo() .': '. $zthis->ErrorMsg());
1151 if ($zthis->debug
=== 99) _adodb_backtrace(true,9999,2);
1155 # pretty print the debug_backtrace function
1156 function _adodb_backtrace($printOrArr=true,$levels=9999,$skippy=0,$ishtml=null)
1158 if (!function_exists('debug_backtrace')) return '';
1160 if ($ishtml === null) $html = (isset($_SERVER['HTTP_USER_AGENT']));
1161 else $html = $ishtml;
1163 $fmt = ($html) ?
"</font><font color=#808080 size=-1> %% line %4d, file: <a href=\"file:/%s\">%s</a></font>" : "%% line %4d, file: %s";
1167 $s = ($html) ?
'<pre align=left>' : '';
1169 if (is_array($printOrArr)) $traceArr = $printOrArr;
1170 else $traceArr = debug_backtrace();
1171 array_shift($traceArr);
1172 array_shift($traceArr);
1173 $tabs = sizeof($traceArr)-2;
1175 foreach ($traceArr as $arr) {
1176 if ($skippy) {$skippy -= 1; continue;}
1178 if ($levels < 0) break;
1181 for ($i=0; $i < $tabs; $i++
) $s .= ($html) ?
' ' : "\t";
1183 if ($html) $s .= '<font face="Courier New,Courier">';
1184 if (isset($arr['class'])) $s .= $arr['class'].'.';
1185 if (isset($arr['args']))
1186 foreach($arr['args'] as $v) {
1187 if (is_null($v)) $args[] = 'null';
1188 else if (is_array($v)) $args[] = 'Array['.sizeof($v).']';
1189 else if (is_object($v)) $args[] = 'Object:'.get_class($v);
1190 else if (is_bool($v)) $args[] = $v ?
'true' : 'false';
1193 $str = htmlspecialchars(str_replace(array("\r","\n"),' ',substr($v,0,$MAXSTRLEN)));
1194 if (strlen($v) > $MAXSTRLEN) $str .= '...';
1198 $s .= $arr['function'].'('.implode(', ',$args).')';
1201 $s .= @sprintf
($fmt, $arr['line'],$arr['file'],basename($arr['file']));
1205 if ($html) $s .= '</pre>';
1206 if ($printOrArr) print $s;
1211 function _adodb_find_from($sql)
1214 $sql = str_replace(array("\n","\r"), ' ', $sql);
1215 $charCount = strlen($sql);
1219 $parentheseCount = 0;
1224 for($i = 0; $i < $charCount; $i++) {
1226 $char = substr($sql,$i,1);
1227 $prevChars = substr($sql,0,$i);
1228 $nextChars = substr($sql,$i+1);
1230 if((($char == "'" || $char == '"' || $char == '`') && substr($prevChars,-1,1) != '\\') && $inString === false) {
1235 elseif((($char == "'" || $char == '"' || $char == '`') && substr($prevChars,-1,1) != '\\') && $inString === true && $quote == $char) {
1240 elseif($char == "(" && $inString === false)
1243 elseif($char == ")" && $inString === false && $parentheseCount > 0)
1246 elseif($parentheseCount <= 0 && $inString === false && $char == " " && strtoupper(substr($prevChars,-5,5)) == " FROM")