| [ Index ] |
PHP Cross Reference of moodle-2.8 |
[Summary view] [Print] [Text view]
1 <?php 2 /* 3 4 version V5.19 23-Apr-2014 (c) 2000-2014 John Lim. All rights reserved. 5 6 Released under both BSD license and Lesser GPL library license. 7 Whenever there is any discrepancy between the two licenses, 8 the BSD license will take precedence. 9 10 Latest version is available at http://adodb.sourceforge.net 11 12 Code contributed by George Fourlanos <[email protected]> 13 14 13 Nov 2000 jlim - removed all ora_* references. 15 */ 16 17 // security - hide paths 18 if (!defined('ADODB_DIR')) die(); 19 20 /* 21 NLS_Date_Format 22 Allows you to use a date format other than the Oracle Lite default. When a literal 23 character string appears where a date value is expected, the Oracle Lite database 24 tests the string to see if it matches the formats of Oracle, SQL-92, or the value 25 specified for this parameter in the POLITE.INI file. Setting this parameter also 26 defines the default format used in the TO_CHAR or TO_DATE functions when no 27 other format string is supplied. 28 29 For Oracle the default is dd-mon-yy or dd-mon-yyyy, and for SQL-92 the default is 30 yy-mm-dd or yyyy-mm-dd. 31 32 Using 'RR' in the format forces two-digit years less than or equal to 49 to be 33 interpreted as years in the 21st century (2000-2049), and years over 50 as years in 34 the 20th century (1950-1999). Setting the RR format as the default for all two-digit 35 year entries allows you to become year-2000 compliant. For example: 36 NLS_DATE_FORMAT='RR-MM-DD' 37 38 You can also modify the date format using the ALTER SESSION command. 39 */ 40 41 # define the LOB descriptor type for the given type 42 # returns false if no LOB descriptor 43 function oci_lob_desc($type) { 44 switch ($type) { 45 case OCI_B_BFILE: $result = OCI_D_FILE; break; 46 case OCI_B_CFILEE: $result = OCI_D_FILE; break; 47 case OCI_B_CLOB: $result = OCI_D_LOB; break; 48 case OCI_B_BLOB: $result = OCI_D_LOB; break; 49 case OCI_B_ROWID: $result = OCI_D_ROWID; break; 50 default: $result = false; break; 51 } 52 return $result; 53 } 54 55 class ADODB_oci8 extends ADOConnection { 56 var $databaseType = 'oci8'; 57 var $dataProvider = 'oci8'; 58 var $replaceQuote = "''"; // string to use to replace quotes 59 var $concat_operator='||'; 60 var $sysDate = "TRUNC(SYSDATE)"; 61 var $sysTimeStamp = 'SYSDATE'; // requires oracle 9 or later, otherwise use SYSDATE 62 var $metaDatabasesSQL = "SELECT USERNAME FROM ALL_USERS WHERE USERNAME NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN') ORDER BY 1"; 63 var $_stmt; 64 var $_commit = OCI_COMMIT_ON_SUCCESS; 65 var $_initdate = true; // init date to YYYY-MM-DD 66 var $metaTablesSQL = "select table_name,table_type from cat where table_type in ('TABLE','VIEW') and table_name not like 'BIN\$%'"; // bin$ tables are recycle bin tables 67 var $metaColumnsSQL = "select cname,coltype,width, SCALE, PRECISION, NULLS, DEFAULTVAL from col where tname='%s' order by colno"; //changed by [email protected]. net 68 var $metaColumnsSQL2 = "select column_name,data_type,data_length, data_scale, data_precision, 69 case when nullable = 'Y' then 'NULL' 70 else 'NOT NULL' end as nulls, 71 data_default from all_tab_cols 72 where owner='%s' and table_name='%s' order by column_id"; // when there is a schema 73 var $_bindInputArray = true; 74 var $hasGenID = true; 75 var $_genIDSQL = "SELECT (%s.nextval) FROM DUAL"; 76 var $_genSeqSQL = " 77 DECLARE 78 PRAGMA AUTONOMOUS_TRANSACTION; 79 BEGIN 80 execute immediate 'CREATE SEQUENCE %s START WITH %s'; 81 END; 82 "; 83 84 var $_dropSeqSQL = "DROP SEQUENCE %s"; 85 var $hasAffectedRows = true; 86 var $random = "abs(mod(DBMS_RANDOM.RANDOM,10000001)/10000000)"; 87 var $noNullStrings = false; 88 var $connectSID = false; 89 var $_bind = false; 90 var $_nestedSQL = true; 91 var $_hasOciFetchStatement = false; 92 var $_getarray = false; // currently not working 93 var $leftOuter = ''; // oracle wierdness, $col = $value (+) for LEFT OUTER, $col (+)= $value for RIGHT OUTER 94 var $session_sharing_force_blob = false; // alter session on updateblob if set to true 95 var $firstrows = true; // enable first rows optimization on SelectLimit() 96 var $selectOffsetAlg1 = 1000; // when to use 1st algorithm of selectlimit. 97 var $NLS_DATE_FORMAT = 'YYYY-MM-DD'; // To include time, use 'RRRR-MM-DD HH24:MI:SS' 98 var $dateformat = 'YYYY-MM-DD'; // DBDate format 99 var $useDBDateFormatForTextInput=false; 100 var $datetime = false; // MetaType('DATE') returns 'D' (datetime==false) or 'T' (datetime == true) 101 var $_refLOBs = array(); 102 103 // var $ansiOuter = true; // if oracle9 104 105 function ADODB_oci8() 106 { 107 $this->_hasOciFetchStatement = ADODB_PHPVER >= 0x4200; 108 if (defined('ADODB_EXTENSION')) $this->rsPrefix .= 'ext_'; 109 } 110 111 /* function MetaColumns($table, $normalize=true) added by [email protected]*/ 112 function MetaColumns($table, $normalize=true) 113 { 114 global $ADODB_FETCH_MODE; 115 116 $schema = ''; 117 $this->_findschema($table, $schema); 118 119 $false = false; 120 $save = $ADODB_FETCH_MODE; 121 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 122 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false); 123 124 if ($schema) 125 $rs = $this->Execute(sprintf($this->metaColumnsSQL2, strtoupper($schema), strtoupper($table))); 126 else 127 $rs = $this->Execute(sprintf($this->metaColumnsSQL,strtoupper($table))); 128 129 if (isset($savem)) $this->SetFetchMode($savem); 130 $ADODB_FETCH_MODE = $save; 131 if (!$rs) { 132 return $false; 133 } 134 $retarr = array(); 135 while (!$rs->EOF) { 136 $fld = new ADOFieldObject(); 137 $fld->name = $rs->fields[0]; 138 $fld->type = $rs->fields[1]; 139 $fld->max_length = $rs->fields[2]; 140 $fld->scale = $rs->fields[3]; 141 if ($rs->fields[1] == 'NUMBER') { 142 if ($rs->fields[3] == 0) $fld->type = 'INT'; 143 $fld->max_length = $rs->fields[4]; 144 } 145 $fld->not_null = (strncmp($rs->fields[5], 'NOT',3) === 0); 146 $fld->binary = (strpos($fld->type,'BLOB') !== false); 147 $fld->default_value = $rs->fields[6]; 148 149 if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) $retarr[] = $fld; 150 else $retarr[strtoupper($fld->name)] = $fld; 151 $rs->MoveNext(); 152 } 153 $rs->Close(); 154 if (empty($retarr)) 155 return $false; 156 else 157 return $retarr; 158 } 159 160 function Time() 161 { 162 $rs = $this->Execute("select TO_CHAR($this->sysTimeStamp,'YYYY-MM-DD HH24:MI:SS') from dual"); 163 if ($rs && !$rs->EOF) return $this->UnixTimeStamp(reset($rs->fields)); 164 165 return false; 166 } 167 168 /** 169 * Multiple modes of connection are supported: 170 * 171 * a. Local Database 172 * $conn->Connect(false,'scott','tiger'); 173 * 174 * b. From tnsnames.ora 175 * $conn->Connect($tnsname,'scott','tiger'); 176 * $conn->Connect(false,'scott','tiger',$tnsname); 177 * 178 * c. Server + service name 179 * $conn->Connect($serveraddress,'scott,'tiger',$service_name); 180 * 181 * d. Server + SID 182 * $conn->connectSID = true; 183 * $conn->Connect($serveraddress,'scott,'tiger',$SID); 184 * 185 * @param string|false $argHostname DB server hostname or TNS name 186 * @param string $argUsername 187 * @param string $argPassword 188 * @param string $argDatabasename Service name, SID (defaults to null) 189 * @param int $mode Connection mode, defaults to 0 190 * (0 = non-persistent, 1 = persistent, 2 = force new connection) 191 * 192 * @return bool 193 */ 194 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename=null, $mode=0) 195 { 196 if (!function_exists('oci_pconnect')) return null; 197 #adodb_backtrace(); 198 199 $this->_errorMsg = false; 200 $this->_errorCode = false; 201 202 if($argHostname) { // added by Jorma Tuomainen <[email protected]> 203 if (empty($argDatabasename)) $argDatabasename = $argHostname; 204 else { 205 if(strpos($argHostname,":")) { 206 $argHostinfo=explode(":",$argHostname); 207 $argHostname=$argHostinfo[0]; 208 $argHostport=$argHostinfo[1]; 209 } else { 210 $argHostport = empty($this->port)? "1521" : $this->port; 211 } 212 213 if (strncasecmp($argDatabasename,'SID=',4) == 0) { 214 $argDatabasename = substr($argDatabasename,4); 215 $this->connectSID = true; 216 } 217 218 if ($this->connectSID) { 219 $argDatabasename="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=".$argHostname 220 .")(PORT=$argHostport))(CONNECT_DATA=(SID=$argDatabasename)))"; 221 } else 222 $argDatabasename="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=".$argHostname 223 .")(PORT=$argHostport))(CONNECT_DATA=(SERVICE_NAME=$argDatabasename)))"; 224 } 225 } 226 227 //if ($argHostname) print "<p>Connect: 1st argument should be left blank for $this->databaseType</p>"; 228 if ($mode==1) { 229 $this->_connectionID = ($this->charSet) 230 ? oci_pconnect($argUsername,$argPassword, $argDatabasename,$this->charSet) 231 : oci_pconnect($argUsername,$argPassword, $argDatabasename); 232 if ($this->_connectionID && $this->autoRollback) oci_rollback($this->_connectionID); 233 } else if ($mode==2) { 234 $this->_connectionID = ($this->charSet) 235 ? oci_new_connect($argUsername,$argPassword, $argDatabasename,$this->charSet) 236 : oci_new_connect($argUsername,$argPassword, $argDatabasename); 237 } else { 238 $this->_connectionID = ($this->charSet) 239 ? oci_connect($argUsername,$argPassword, $argDatabasename,$this->charSet) 240 : oci_connect($argUsername,$argPassword, $argDatabasename); 241 } 242 if (!$this->_connectionID) { 243 return false; 244 } 245 246 if ($this->_initdate) { 247 $this->Execute("ALTER SESSION SET NLS_DATE_FORMAT='".$this->NLS_DATE_FORMAT."'"); 248 } 249 250 // looks like: 251 // Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production 252 // $vers = oci_server_version($this->_connectionID); 253 // if (strpos($vers,'8i') !== false) $this->ansiOuter = true; 254 return true; 255 } 256 257 function ServerInfo() 258 { 259 $arr['compat'] = $this->GetOne('select value from sys.database_compatible_level'); 260 $arr['description'] = @oci_server_version($this->_connectionID); 261 $arr['version'] = ADOConnection::_findvers($arr['description']); 262 return $arr; 263 } 264 // returns true or false 265 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename) 266 { 267 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename,1); 268 } 269 270 // returns true or false 271 function _nconnect($argHostname, $argUsername, $argPassword, $argDatabasename) 272 { 273 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename,2); 274 } 275 276 function _affectedrows() 277 { 278 if (is_resource($this->_stmt)) return @oci_num_rows($this->_stmt); 279 return 0; 280 } 281 282 function IfNull( $field, $ifNull ) 283 { 284 return " NVL($field, $ifNull) "; // if Oracle 285 } 286 287 // format and return date string in database date format 288 function DBDate($d,$isfld=false) 289 { 290 if (empty($d) && $d !== 0) return 'null'; 291 292 if ($isfld) { 293 $d = _adodb_safedate($d); 294 return 'TO_DATE('.$d.",'".$this->dateformat."')"; 295 } 296 297 if (is_string($d)) $d = ADORecordSet::UnixDate($d); 298 299 if (is_object($d)) $ds = $d->format($this->fmtDate); 300 else $ds = adodb_date($this->fmtDate,$d); 301 302 return "TO_DATE(".$ds.",'".$this->dateformat."')"; 303 } 304 305 function BindDate($d) 306 { 307 $d = ADOConnection::DBDate($d); 308 if (strncmp($d,"'",1)) return $d; 309 310 return substr($d,1,strlen($d)-2); 311 } 312 313 function BindTimeStamp($ts) 314 { 315 if (empty($ts) && $ts !== 0) return 'null'; 316 if (is_string($ts)) $ts = ADORecordSet::UnixTimeStamp($ts); 317 318 if (is_object($ts)) $tss = $ts->format("'Y-m-d H:i:s'"); 319 else $tss = adodb_date("'Y-m-d H:i:s'",$ts); 320 321 return $tss; 322 } 323 324 // format and return date string in database timestamp format 325 function DBTimeStamp($ts,$isfld=false) 326 { 327 if (empty($ts) && $ts !== 0) return 'null'; 328 if ($isfld) return 'TO_DATE(substr('.$ts.",1,19),'RRRR-MM-DD, HH24:MI:SS')"; 329 if (is_string($ts)) $ts = ADORecordSet::UnixTimeStamp($ts); 330 331 if (is_object($ts)) $tss = $ts->format("'Y-m-d H:i:s'"); 332 else $tss = date("'Y-m-d H:i:s'",$ts); 333 334 return 'TO_DATE('.$tss.",'RRRR-MM-DD, HH24:MI:SS')"; 335 } 336 337 function RowLock($tables,$where,$col='1 as adodbignore') 338 { 339 if ($this->autoCommit) $this->BeginTrans(); 340 return $this->GetOne("select $col from $tables where $where for update"); 341 } 342 343 function MetaTables($ttype=false,$showSchema=false,$mask=false) 344 { 345 if ($mask) { 346 $save = $this->metaTablesSQL; 347 $mask = $this->qstr(strtoupper($mask)); 348 $this->metaTablesSQL .= " AND upper(table_name) like $mask"; 349 } 350 $ret = ADOConnection::MetaTables($ttype,$showSchema); 351 352 if ($mask) { 353 $this->metaTablesSQL = $save; 354 } 355 return $ret; 356 } 357 358 // Mark Newnham 359 function MetaIndexes ($table, $primary = FALSE, $owner=false) 360 { 361 // save old fetch mode 362 global $ADODB_FETCH_MODE; 363 364 $save = $ADODB_FETCH_MODE; 365 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 366 367 if ($this->fetchMode !== FALSE) { 368 $savem = $this->SetFetchMode(FALSE); 369 } 370 371 // get index details 372 $table = strtoupper($table); 373 374 // get Primary index 375 $primary_key = ''; 376 377 $false = false; 378 $rs = $this->Execute(sprintf("SELECT * FROM ALL_CONSTRAINTS WHERE UPPER(TABLE_NAME)='%s' AND CONSTRAINT_TYPE='P'",$table)); 379 if (!is_object($rs)) { 380 if (isset($savem)) 381 $this->SetFetchMode($savem); 382 $ADODB_FETCH_MODE = $save; 383 return $false; 384 } 385 386 if ($row = $rs->FetchRow()) 387 $primary_key = $row[1]; //constraint_name 388 389 if ($primary==TRUE && $primary_key=='') { 390 if (isset($savem)) 391 $this->SetFetchMode($savem); 392 $ADODB_FETCH_MODE = $save; 393 return $false; //There is no primary key 394 } 395 396 $rs = $this->Execute(sprintf("SELECT ALL_INDEXES.INDEX_NAME, ALL_INDEXES.UNIQUENESS, ALL_IND_COLUMNS.COLUMN_POSITION, ALL_IND_COLUMNS.COLUMN_NAME FROM ALL_INDEXES,ALL_IND_COLUMNS WHERE UPPER(ALL_INDEXES.TABLE_NAME)='%s' AND ALL_IND_COLUMNS.INDEX_NAME=ALL_INDEXES.INDEX_NAME",$table)); 397 398 399 if (!is_object($rs)) { 400 if (isset($savem)) 401 $this->SetFetchMode($savem); 402 $ADODB_FETCH_MODE = $save; 403 return $false; 404 } 405 406 $indexes = array (); 407 // parse index data into array 408 409 while ($row = $rs->FetchRow()) { 410 if ($primary && $row[0] != $primary_key) continue; 411 if (!isset($indexes[$row[0]])) { 412 $indexes[$row[0]] = array( 413 'unique' => ($row[1] == 'UNIQUE'), 414 'columns' => array() 415 ); 416 } 417 $indexes[$row[0]]['columns'][$row[2] - 1] = $row[3]; 418 } 419 420 // sort columns by order in the index 421 foreach ( array_keys ($indexes) as $index ) { 422 ksort ($indexes[$index]['columns']); 423 } 424 425 if (isset($savem)) { 426 $this->SetFetchMode($savem); 427 $ADODB_FETCH_MODE = $save; 428 } 429 return $indexes; 430 } 431 432 function BeginTrans() 433 { 434 if ($this->transOff) return true; 435 $this->transCnt += 1; 436 $this->autoCommit = false; 437 $this->_commit = OCI_DEFAULT; 438 439 if ($this->_transmode) $ok = $this->Execute("SET TRANSACTION ".$this->_transmode); 440 else $ok = true; 441 442 return $ok ? true : false; 443 } 444 445 function CommitTrans($ok=true) 446 { 447 if ($this->transOff) return true; 448 if (!$ok) return $this->RollbackTrans(); 449 450 if ($this->transCnt) $this->transCnt -= 1; 451 $ret = oci_commit($this->_connectionID); 452 $this->_commit = OCI_COMMIT_ON_SUCCESS; 453 $this->autoCommit = true; 454 return $ret; 455 } 456 457 function RollbackTrans() 458 { 459 if ($this->transOff) return true; 460 if ($this->transCnt) $this->transCnt -= 1; 461 $ret = oci_rollback($this->_connectionID); 462 $this->_commit = OCI_COMMIT_ON_SUCCESS; 463 $this->autoCommit = true; 464 return $ret; 465 } 466 467 468 function SelectDB($dbName) 469 { 470 return false; 471 } 472 473 function ErrorMsg() 474 { 475 if ($this->_errorMsg !== false) return $this->_errorMsg; 476 477 if (is_resource($this->_stmt)) $arr = @oci_error($this->_stmt); 478 if (empty($arr)) { 479 if (is_resource($this->_connectionID)) $arr = @oci_error($this->_connectionID); 480 else $arr = @oci_error(); 481 if ($arr === false) return ''; 482 } 483 $this->_errorMsg = $arr['message']; 484 $this->_errorCode = $arr['code']; 485 return $this->_errorMsg; 486 } 487 488 function ErrorNo() 489 { 490 if ($this->_errorCode !== false) return $this->_errorCode; 491 492 if (is_resource($this->_stmt)) $arr = @oci_error($this->_stmt); 493 if (empty($arr)) { 494 $arr = @oci_error($this->_connectionID); 495 if ($arr == false) $arr = @oci_error(); 496 if ($arr == false) return ''; 497 } 498 499 $this->_errorMsg = $arr['message']; 500 $this->_errorCode = $arr['code']; 501 502 return $arr['code']; 503 } 504 505 /** 506 * Format date column in sql string given an input format that understands Y M D 507 */ 508 function SQLDate($fmt, $col=false) 509 { 510 if (!$col) $col = $this->sysTimeStamp; 511 $s = 'TO_CHAR('.$col.",'"; 512 513 $len = strlen($fmt); 514 for ($i=0; $i < $len; $i++) { 515 $ch = $fmt[$i]; 516 switch($ch) { 517 case 'Y': 518 case 'y': 519 $s .= 'YYYY'; 520 break; 521 case 'Q': 522 case 'q': 523 $s .= 'Q'; 524 break; 525 526 case 'M': 527 $s .= 'Mon'; 528 break; 529 530 case 'm': 531 $s .= 'MM'; 532 break; 533 case 'D': 534 case 'd': 535 $s .= 'DD'; 536 break; 537 538 case 'H': 539 $s.= 'HH24'; 540 break; 541 542 case 'h': 543 $s .= 'HH'; 544 break; 545 546 case 'i': 547 $s .= 'MI'; 548 break; 549 550 case 's': 551 $s .= 'SS'; 552 break; 553 554 case 'a': 555 case 'A': 556 $s .= 'AM'; 557 break; 558 559 case 'w': 560 $s .= 'D'; 561 break; 562 563 case 'l': 564 $s .= 'DAY'; 565 break; 566 567 case 'W': 568 $s .= 'WW'; 569 break; 570 571 default: 572 // handle escape characters... 573 if ($ch == '\\') { 574 $i++; 575 $ch = substr($fmt,$i,1); 576 } 577 if (strpos('-/.:;, ',$ch) !== false) $s .= $ch; 578 else $s .= '"'.$ch.'"'; 579 580 } 581 } 582 return $s. "')"; 583 } 584 585 function GetRandRow($sql, $arr = false) 586 { 587 $sql = "SELECT * FROM ($sql ORDER BY dbms_random.value) WHERE rownum = 1"; 588 589 return $this->GetRow($sql,$arr); 590 } 591 592 /** 593 * This algorithm makes use of 594 * 595 * a. FIRST_ROWS hint 596 * The FIRST_ROWS hint explicitly chooses the approach to optimize response 597 * time, that is, minimum resource usage to return the first row. Results 598 * will be returned as soon as they are identified. 599 * 600 * b. Uses rownum tricks to obtain only the required rows from a given offset. 601 * As this uses complicated sql statements, we only use this if $offset >= 100. 602 * This idea by Tomas V V Cox. 603 * 604 * This implementation does not appear to work with oracle 8.0.5 or earlier. 605 * Comment out this function then, and the slower SelectLimit() in the base 606 * class will be used. 607 */ 608 function SelectLimit($sql,$nrows=-1,$offset=-1, $inputarr=false,$secs2cache=0) 609 { 610 // seems that oracle only supports 1 hint comment in 8i 611 if ($this->firstrows) { 612 if ($nrows > 500 && $nrows < 1000) $hint = "FIRST_ROWS($nrows)"; 613 else $hint = 'FIRST_ROWS'; 614 615 if (strpos($sql,'/*+') !== false) 616 $sql = str_replace('/*+ ',"/*+$hint ",$sql); 617 else 618 $sql = preg_replace('/^[ \t\n]*select/i',"SELECT /*+$hint*/",$sql); 619 $hint = "/*+ $hint */"; 620 } else 621 $hint = ''; 622 623 if ($offset == -1 || ($offset < $this->selectOffsetAlg1 && 0 < $nrows && $nrows < 1000)) { 624 if ($nrows > 0) { 625 if ($offset > 0) $nrows += $offset; 626 //$inputarr['adodb_rownum'] = $nrows; 627 if ($this->databaseType == 'oci8po') { 628 $sql = "select * from (".$sql.") where rownum <= ?"; 629 } else { 630 $sql = "select * from (".$sql.") where rownum <= :adodb_offset"; 631 } 632 $inputarr['adodb_offset'] = $nrows; 633 $nrows = -1; 634 } 635 // note that $nrows = 0 still has to work ==> no rows returned 636 637 $rs = ADOConnection::SelectLimit($sql,$nrows,$offset,$inputarr,$secs2cache); 638 return $rs; 639 640 } else { 641 // Algorithm by Tomas V V Cox, from PEAR DB oci8.php 642 643 // Let Oracle return the name of the columns 644 $q_fields = "SELECT * FROM (".$sql.") WHERE NULL = NULL"; 645 646 $false = false; 647 if (! $stmt_arr = $this->Prepare($q_fields)) { 648 return $false; 649 } 650 $stmt = $stmt_arr[1]; 651 652 if (is_array($inputarr)) { 653 foreach($inputarr as $k => $v) { 654 if (is_array($v)) { 655 if (sizeof($v) == 2) // suggested by g.giunta@libero. 656 oci_bind_by_name($stmt,":$k",$inputarr[$k][0],$v[1]); 657 else 658 oci_bind_by_name($stmt,":$k",$inputarr[$k][0],$v[1],$v[2]); 659 } else { 660 $len = -1; 661 if ($v === ' ') $len = 1; 662 if (isset($bindarr)) { // is prepared sql, so no need to oci_bind_by_name again 663 $bindarr[$k] = $v; 664 } else { // dynamic sql, so rebind every time 665 oci_bind_by_name($stmt,":$k",$inputarr[$k],$len); 666 } 667 } 668 } 669 } 670 671 if (!oci_execute($stmt, OCI_DEFAULT)) { 672 oci_free_statement($stmt); 673 return $false; 674 } 675 676 $ncols = oci_num_fields($stmt); 677 for ( $i = 1; $i <= $ncols; $i++ ) { 678 $cols[] = '"'.oci_field_name($stmt, $i).'"'; 679 } 680 $result = false; 681 682 oci_free_statement($stmt); 683 $fields = implode(',', $cols); 684 if ($nrows <= 0) $nrows = 999999999999; 685 else $nrows += $offset; 686 $offset += 1; // in Oracle rownum starts at 1 687 688 if ($this->databaseType == 'oci8po') { 689 $sql = "SELECT $hint $fields FROM". 690 "(SELECT rownum as adodb_rownum, $fields FROM". 691 " ($sql) WHERE rownum <= ?". 692 ") WHERE adodb_rownum >= ?"; 693 } else { 694 $sql = "SELECT $hint $fields FROM". 695 "(SELECT rownum as adodb_rownum, $fields FROM". 696 " ($sql) WHERE rownum <= :adodb_nrows". 697 ") WHERE adodb_rownum >= :adodb_offset"; 698 } 699 $inputarr['adodb_nrows'] = $nrows; 700 $inputarr['adodb_offset'] = $offset; 701 702 if ($secs2cache>0) $rs = $this->CacheExecute($secs2cache, $sql,$inputarr); 703 else $rs = $this->Execute($sql,$inputarr); 704 return $rs; 705 } 706 } 707 708 /** 709 * Usage: 710 * Store BLOBs and CLOBs 711 * 712 * Example: to store $var in a blob 713 * $conn->Execute('insert into TABLE (id,ablob) values(12,empty_blob())'); 714 * $conn->UpdateBlob('TABLE', 'ablob', $varHoldingBlob, 'ID=12', 'BLOB'); 715 * 716 * $blobtype supports 'BLOB' and 'CLOB', but you need to change to 'empty_clob()'. 717 * 718 * to get length of LOB: 719 * select DBMS_LOB.GETLENGTH(ablob) from TABLE 720 * 721 * If you are using CURSOR_SHARING = force, it appears this will case a segfault 722 * under oracle 8.1.7.0. Run: 723 * $db->Execute('ALTER SESSION SET CURSOR_SHARING=EXACT'); 724 * before UpdateBlob() then... 725 */ 726 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB') 727 { 728 729 //if (strlen($val) < 4000) return $this->Execute("UPDATE $table SET $column=:blob WHERE $where",array('blob'=>$val)) != false; 730 731 switch(strtoupper($blobtype)) { 732 default: ADOConnection::outp("<b>UpdateBlob</b>: Unknown blobtype=$blobtype"); return false; 733 case 'BLOB': $type = OCI_B_BLOB; break; 734 case 'CLOB': $type = OCI_B_CLOB; break; 735 } 736 737 if ($this->databaseType == 'oci8po') 738 $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO ?"; 739 else 740 $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO :blob"; 741 742 $desc = oci_new_descriptor($this->_connectionID, OCI_D_LOB); 743 $arr['blob'] = array($desc,-1,$type); 744 if ($this->session_sharing_force_blob) $this->Execute('ALTER SESSION SET CURSOR_SHARING=EXACT'); 745 $commit = $this->autoCommit; 746 if ($commit) $this->BeginTrans(); 747 $rs = $this->_Execute($sql,$arr); 748 if ($rez = !empty($rs)) $desc->save($val); 749 $desc->free(); 750 if ($commit) $this->CommitTrans(); 751 if ($this->session_sharing_force_blob) $this->Execute('ALTER SESSION SET CURSOR_SHARING=FORCE'); 752 753 if ($rez) $rs->Close(); 754 return $rez; 755 } 756 757 /** 758 * Usage: store file pointed to by $val in a blob 759 */ 760 function UpdateBlobFile($table,$column,$val,$where,$blobtype='BLOB') 761 { 762 switch(strtoupper($blobtype)) { 763 default: ADOConnection::outp( "<b>UpdateBlob</b>: Unknown blobtype=$blobtype"); return false; 764 case 'BLOB': $type = OCI_B_BLOB; break; 765 case 'CLOB': $type = OCI_B_CLOB; break; 766 } 767 768 if ($this->databaseType == 'oci8po') 769 $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO ?"; 770 else 771 $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO :blob"; 772 773 $desc = oci_new_descriptor($this->_connectionID, OCI_D_LOB); 774 $arr['blob'] = array($desc,-1,$type); 775 776 $this->BeginTrans(); 777 $rs = ADODB_oci8::Execute($sql,$arr); 778 if ($rez = !empty($rs)) $desc->savefile($val); 779 $desc->free(); 780 $this->CommitTrans(); 781 782 if ($rez) $rs->Close(); 783 return $rez; 784 } 785 786 /** 787 * Execute SQL 788 * 789 * @param sql SQL statement to execute, or possibly an array holding prepared statement ($sql[0] will hold sql text) 790 * @param [inputarr] holds the input data to bind to. Null elements will be set to null. 791 * @return RecordSet or false 792 */ 793 function Execute($sql,$inputarr=false) 794 { 795 if ($this->fnExecute) { 796 $fn = $this->fnExecute; 797 $ret = $fn($this,$sql,$inputarr); 798 if (isset($ret)) return $ret; 799 } 800 if ($inputarr !== false) { 801 if (!is_array($inputarr)) $inputarr = array($inputarr); 802 803 $element0 = reset($inputarr); 804 $array2d = $this->bulkBind && is_array($element0) && !is_object(reset($element0)); 805 806 # see http://phplens.com/lens/lensforum/msgs.php?id=18786 807 if ($array2d || !$this->_bindInputArray) { 808 809 # is_object check because oci8 descriptors can be passed in 810 if ($array2d && $this->_bindInputArray) { 811 if (is_string($sql)) 812 $stmt = $this->Prepare($sql); 813 else 814 $stmt = $sql; 815 816 foreach($inputarr as $arr) { 817 $ret = $this->_Execute($stmt,$arr); 818 if (!$ret) return $ret; 819 } 820 return $ret; 821 } else { 822 $sqlarr = explode(':',$sql); 823 $sql = ''; 824 $lastnomatch = -2; 825 #var_dump($sqlarr);echo "<hr>";var_dump($inputarr);echo"<hr>"; 826 foreach($sqlarr as $k => $str) { 827 if ($k == 0) { $sql = $str; continue; } 828 // we need $lastnomatch because of the following datetime, 829 // eg. '10:10:01', which causes code to think that there is bind param :10 and :1 830 $ok = preg_match('/^([0-9]*)/', $str, $arr); 831 832 if (!$ok) $sql .= $str; 833 else { 834 $at = $arr[1]; 835 if (isset($inputarr[$at]) || is_null($inputarr[$at])) { 836 if ((strlen($at) == strlen($str) && $k < sizeof($arr)-1)) { 837 $sql .= ':'.$str; 838 $lastnomatch = $k; 839 } else if ($lastnomatch == $k-1) { 840 $sql .= ':'.$str; 841 } else { 842 if (is_null($inputarr[$at])) $sql .= 'null'; 843 else $sql .= $this->qstr($inputarr[$at]); 844 $sql .= substr($str, strlen($at)); 845 } 846 } else { 847 $sql .= ':'.$str; 848 } 849 850 } 851 } 852 $inputarr = false; 853 } 854 } 855 $ret = $this->_Execute($sql,$inputarr); 856 857 858 } else { 859 $ret = $this->_Execute($sql,false); 860 } 861 862 return $ret; 863 } 864 865 /* 866 * Example of usage: 867 * $stmt = $this->Prepare('insert into emp (empno, ename) values (:empno, :ename)'); 868 */ 869 function Prepare($sql,$cursor=false) 870 { 871 static $BINDNUM = 0; 872 873 $stmt = oci_parse($this->_connectionID,$sql); 874 875 if (!$stmt) { 876 $this->_errorMsg = false; 877 $this->_errorCode = false; 878 $arr = @oci_error($this->_connectionID); 879 if ($arr === false) return false; 880 881 $this->_errorMsg = $arr['message']; 882 $this->_errorCode = $arr['code']; 883 return false; 884 } 885 886 $BINDNUM += 1; 887 888 $sttype = @oci_statement_type($stmt); 889 if ($sttype == 'BEGIN' || $sttype == 'DECLARE') { 890 return array($sql,$stmt,0,$BINDNUM, ($cursor) ? oci_new_cursor($this->_connectionID) : false); 891 } 892 return array($sql,$stmt,0,$BINDNUM); 893 } 894 895 /* 896 Call an oracle stored procedure and returns a cursor variable as a recordset. 897 Concept by Robert Tuttle [email protected] 898 899 Example: 900 Note: we return a cursor variable in :RS2 901 $rs = $db->ExecuteCursor("BEGIN adodb.open_tab(:RS2); END;",'RS2'); 902 903 $rs = $db->ExecuteCursor( 904 "BEGIN :RS2 = adodb.getdata(:VAR1); END;", 905 'RS2', 906 array('VAR1' => 'Mr Bean')); 907 908 */ 909 function ExecuteCursor($sql,$cursorName='rs',$params=false) 910 { 911 if (is_array($sql)) $stmt = $sql; 912 else $stmt = ADODB_oci8::Prepare($sql,true); # true to allocate oci_new_cursor 913 914 if (is_array($stmt) && sizeof($stmt) >= 5) { 915 $hasref = true; 916 $ignoreCur = false; 917 $this->Parameter($stmt, $ignoreCur, $cursorName, false, -1, OCI_B_CURSOR); 918 if ($params) { 919 foreach($params as $k => $v) { 920 $this->Parameter($stmt,$params[$k], $k); 921 } 922 } 923 } else 924 $hasref = false; 925 926 $rs = $this->Execute($stmt); 927 if ($rs) { 928 if ($rs->databaseType == 'array') oci_free_cursor($stmt[4]); 929 else if ($hasref) $rs->_refcursor = $stmt[4]; 930 } 931 return $rs; 932 } 933 934 /** 935 * Bind a variable -- very, very fast for executing repeated statements in oracle. 936 * 937 * Better than using 938 * for ($i = 0; $i < $max; $i++) { 939 * $p1 = ?; $p2 = ?; $p3 = ?; 940 * $this->Execute("insert into table (col0, col1, col2) values (:0, :1, :2)", array($p1,$p2,$p3)); 941 * } 942 * 943 * Usage: 944 * $stmt = $DB->Prepare("insert into table (col0, col1, col2) values (:0, :1, :2)"); 945 * $DB->Bind($stmt, $p1); 946 * $DB->Bind($stmt, $p2); 947 * $DB->Bind($stmt, $p3); 948 * for ($i = 0; $i < $max; $i++) { 949 * $p1 = ?; $p2 = ?; $p3 = ?; 950 * $DB->Execute($stmt); 951 * } 952 * 953 * Some timings to insert 1000 records, test table has 3 cols, and 1 index. 954 * - Time 0.6081s (1644.60 inserts/sec) with direct oci_parse/oci_execute 955 * - Time 0.6341s (1577.16 inserts/sec) with ADOdb Prepare/Bind/Execute 956 * - Time 1.5533s ( 643.77 inserts/sec) with pure SQL using Execute 957 * 958 * Now if PHP only had batch/bulk updating like Java or PL/SQL... 959 * 960 * Note that the order of parameters differs from oci_bind_by_name, 961 * because we default the names to :0, :1, :2 962 */ 963 function Bind(&$stmt,&$var,$size=4000,$type=false,$name=false,$isOutput=false) 964 { 965 966 if (!is_array($stmt)) return false; 967 968 if (($type == OCI_B_CURSOR) && sizeof($stmt) >= 5) { 969 return oci_bind_by_name($stmt[1],":".$name,$stmt[4],$size,$type); 970 } 971 972 if ($name == false) { 973 if ($type !== false) $rez = oci_bind_by_name($stmt[1],":".$stmt[2],$var,$size,$type); 974 else $rez = oci_bind_by_name($stmt[1],":".$stmt[2],$var,$size); // +1 byte for null terminator 975 $stmt[2] += 1; 976 } else if (oci_lob_desc($type)) { 977 if ($this->debug) { 978 ADOConnection::outp("<b>Bind</b>: name = $name"); 979 } 980 //we have to create a new Descriptor here 981 $numlob = count($this->_refLOBs); 982 $this->_refLOBs[$numlob]['LOB'] = oci_new_descriptor($this->_connectionID, oci_lob_desc($type)); 983 $this->_refLOBs[$numlob]['TYPE'] = $isOutput; 984 985 $tmp = $this->_refLOBs[$numlob]['LOB']; 986 $rez = oci_bind_by_name($stmt[1], ":".$name, $tmp, -1, $type); 987 if ($this->debug) { 988 ADOConnection::outp("<b>Bind</b>: descriptor has been allocated, var (".$name.") binded"); 989 } 990 991 // if type is input then write data to lob now 992 if ($isOutput == false) { 993 $var = $this->BlobEncode($var); 994 $tmp->WriteTemporary($var); 995 $this->_refLOBs[$numlob]['VAR'] = &$var; 996 if ($this->debug) { 997 ADOConnection::outp("<b>Bind</b>: LOB has been written to temp"); 998 } 999 } else { 1000 $this->_refLOBs[$numlob]['VAR'] = &$var; 1001 } 1002 $rez = $tmp; 1003 } else { 1004 if ($this->debug) 1005 ADOConnection::outp("<b>Bind</b>: name = $name"); 1006 1007 if ($type !== false) $rez = oci_bind_by_name($stmt[1],":".$name,$var,$size,$type); 1008 else $rez = oci_bind_by_name($stmt[1],":".$name,$var,$size); // +1 byte for null terminator 1009 } 1010 1011 return $rez; 1012 } 1013 1014 function Param($name,$type='C') 1015 { 1016 return ':'.$name; 1017 } 1018 1019 /** 1020 * Usage: 1021 * $stmt = $db->Prepare('select * from table where id =:myid and group=:group'); 1022 * $db->Parameter($stmt,$id,'myid'); 1023 * $db->Parameter($stmt,$group,'group'); 1024 * $db->Execute($stmt); 1025 * 1026 * @param $stmt Statement returned by Prepare() or PrepareSP(). 1027 * @param $var PHP variable to bind to 1028 * @param $name Name of stored procedure variable name to bind to. 1029 * @param [$isOutput] Indicates direction of parameter 0/false=IN 1=OUT 2= IN/OUT. This is ignored in oci8. 1030 * @param [$maxLen] Holds an maximum length of the variable. 1031 * @param [$type] The data type of $var. Legal values depend on driver. 1032 * 1033 * @link http://php.net/oci_bind_by_name 1034 */ 1035 function Parameter(&$stmt,&$var,$name,$isOutput=false,$maxLen=4000,$type=false) 1036 { 1037 if ($this->debug) { 1038 $prefix = ($isOutput) ? 'Out' : 'In'; 1039 $ztype = (empty($type)) ? 'false' : $type; 1040 ADOConnection::outp( "{$prefix}Parameter(\$stmt, \$php_var='$var', \$name='$name', \$maxLen=$maxLen, \$type=$ztype);"); 1041 } 1042 return $this->Bind($stmt,$var,$maxLen,$type,$name,$isOutput); 1043 } 1044 1045 /** 1046 * returns query ID if successful, otherwise false 1047 * this version supports: 1048 * 1049 * 1. $db->execute('select * from table'); 1050 * 1051 * 2. $db->prepare('insert into table (a,b,c) values (:0,:1,:2)'); 1052 * $db->execute($prepared_statement, array(1,2,3)); 1053 * 1054 * 3. $db->execute('insert into table (a,b,c) values (:a,:b,:c)',array('a'=>1,'b'=>2,'c'=>3)); 1055 * 1056 * 4. $db->prepare('insert into table (a,b,c) values (:0,:1,:2)'); 1057 * $db->bind($stmt,1); $db->bind($stmt,2); $db->bind($stmt,3); 1058 * $db->execute($stmt); 1059 */ 1060 function _query($sql,$inputarr=false) 1061 { 1062 if (is_array($sql)) { // is prepared sql 1063 $stmt = $sql[1]; 1064 1065 // we try to bind to permanent array, so that oci_bind_by_name is persistent 1066 // and carried out once only - note that max array element size is 4000 chars 1067 if (is_array($inputarr)) { 1068 $bindpos = $sql[3]; 1069 if (isset($this->_bind[$bindpos])) { 1070 // all tied up already 1071 $bindarr = $this->_bind[$bindpos]; 1072 } else { 1073 // one statement to bind them all 1074 $bindarr = array(); 1075 foreach($inputarr as $k => $v) { 1076 $bindarr[$k] = $v; 1077 oci_bind_by_name($stmt,":$k",$bindarr[$k],is_string($v) && strlen($v)>4000 ? -1 : 4000); 1078 } 1079 $this->_bind[$bindpos] = $bindarr; 1080 } 1081 } 1082 } else { 1083 $stmt=oci_parse($this->_connectionID,$sql); 1084 } 1085 1086 $this->_stmt = $stmt; 1087 if (!$stmt) return false; 1088 1089 if (defined('ADODB_PREFETCH_ROWS')) @oci_set_prefetch($stmt,ADODB_PREFETCH_ROWS); 1090 1091 if (is_array($inputarr)) { 1092 foreach($inputarr as $k => $v) { 1093 if (is_array($v)) { 1094 if (sizeof($v) == 2) // suggested by g.giunta@libero. 1095 oci_bind_by_name($stmt,":$k",$inputarr[$k][0],$v[1]); 1096 else 1097 oci_bind_by_name($stmt,":$k",$inputarr[$k][0],$v[1],$v[2]); 1098 1099 if ($this->debug==99) { 1100 if (is_object($v[0])) 1101 echo "name=:$k",' len='.$v[1],' type='.$v[2],'<br>'; 1102 else 1103 echo "name=:$k",' var='.$inputarr[$k][0],' len='.$v[1],' type='.$v[2],'<br>'; 1104 1105 } 1106 } else { 1107 $len = -1; 1108 if ($v === ' ') $len = 1; 1109 if (isset($bindarr)) { // is prepared sql, so no need to oci_bind_by_name again 1110 $bindarr[$k] = $v; 1111 } else { // dynamic sql, so rebind every time 1112 oci_bind_by_name($stmt,":$k",$inputarr[$k],$len); 1113 } 1114 } 1115 } 1116 } 1117 1118 $this->_errorMsg = false; 1119 $this->_errorCode = false; 1120 if (oci_execute($stmt,$this->_commit)) { 1121 1122 if (count($this -> _refLOBs) > 0) { 1123 1124 foreach ($this -> _refLOBs as $key => $value) { 1125 if ($this -> _refLOBs[$key]['TYPE'] == true) { 1126 $tmp = $this -> _refLOBs[$key]['LOB'] -> load(); 1127 if ($this -> debug) { 1128 ADOConnection::outp("<b>OUT LOB</b>: LOB has been loaded. <br>"); 1129 } 1130 //$_GLOBALS[$this -> _refLOBs[$key]['VAR']] = $tmp; 1131 $this -> _refLOBs[$key]['VAR'] = $tmp; 1132 } else { 1133 $this->_refLOBs[$key]['LOB']->save($this->_refLOBs[$key]['VAR']); 1134 $this -> _refLOBs[$key]['LOB']->free(); 1135 unset($this -> _refLOBs[$key]); 1136 if ($this->debug) { 1137 ADOConnection::outp("<b>IN LOB</b>: LOB has been saved. <br>"); 1138 } 1139 } 1140 } 1141 } 1142 1143 switch (@oci_statement_type($stmt)) { 1144 case "SELECT": 1145 return $stmt; 1146 1147 case 'DECLARE': 1148 case "BEGIN": 1149 if (is_array($sql) && !empty($sql[4])) { 1150 $cursor = $sql[4]; 1151 if (is_resource($cursor)) { 1152 $ok = oci_execute($cursor); 1153 return $cursor; 1154 } 1155 return $stmt; 1156 } else { 1157 if (is_resource($stmt)) { 1158 oci_free_statement($stmt); 1159 return true; 1160 } 1161 return $stmt; 1162 } 1163 break; 1164 default : 1165 1166 return true; 1167 } 1168 } 1169 return false; 1170 } 1171 1172 // From Oracle Whitepaper: PHP Scalability and High Availability 1173 function IsConnectionError($err) 1174 { 1175 switch($err) { 1176 case 378: /* buffer pool param incorrect */ 1177 case 602: /* core dump */ 1178 case 603: /* fatal error */ 1179 case 609: /* attach failed */ 1180 case 1012: /* not logged in */ 1181 case 1033: /* init or shutdown in progress */ 1182 case 1043: /* Oracle not available */ 1183 case 1089: /* immediate shutdown in progress */ 1184 case 1090: /* shutdown in progress */ 1185 case 1092: /* instance terminated */ 1186 case 3113: /* disconnect */ 1187 case 3114: /* not connected */ 1188 case 3122: /* closing window */ 1189 case 3135: /* lost contact */ 1190 case 12153: /* TNS: not connected */ 1191 case 27146: /* fatal or instance terminated */ 1192 case 28511: /* Lost RPC */ 1193 return true; 1194 } 1195 return false; 1196 } 1197 1198 // returns true or false 1199 function _close() 1200 { 1201 if (!$this->_connectionID) return; 1202 1203 if (!$this->autoCommit) oci_rollback($this->_connectionID); 1204 if (count($this->_refLOBs) > 0) { 1205 foreach ($this ->_refLOBs as $key => $value) { 1206 $this->_refLOBs[$key]['LOB']->free(); 1207 unset($this->_refLOBs[$key]); 1208 } 1209 } 1210 oci_close($this->_connectionID); 1211 1212 $this->_stmt = false; 1213 $this->_connectionID = false; 1214 } 1215 1216 function MetaPrimaryKeys($table, $owner=false,$internalKey=false) 1217 { 1218 if ($internalKey) return array('ROWID'); 1219 1220 // tested with oracle 8.1.7 1221 $table = strtoupper($table); 1222 if ($owner) { 1223 $owner_clause = "AND ((a.OWNER = b.OWNER) AND (a.OWNER = UPPER('$owner')))"; 1224 $ptab = 'ALL_'; 1225 } else { 1226 $owner_clause = ''; 1227 $ptab = 'USER_'; 1228 } 1229 $sql = " 1230 SELECT /*+ RULE */ distinct b.column_name 1231 FROM {$ptab}CONSTRAINTS a 1232 , {$ptab}CONS_COLUMNS b 1233 WHERE ( UPPER(b.table_name) = ('$table')) 1234 AND (UPPER(a.table_name) = ('$table') and a.constraint_type = 'P') 1235 $owner_clause 1236 AND (a.constraint_name = b.constraint_name)"; 1237 1238 $rs = $this->Execute($sql); 1239 if ($rs && !$rs->EOF) { 1240 $arr = $rs->GetArray(); 1241 $a = array(); 1242 foreach($arr as $v) { 1243 $a[] = reset($v); 1244 } 1245 return $a; 1246 } 1247 else return false; 1248 } 1249 1250 /** 1251 * returns assoc array where keys are tables, and values are foreign keys 1252 * 1253 * @param str $table 1254 * @param str $owner [optional][default=NULL] 1255 * @param bool $upper [optional][discarded] 1256 * @return mixed[] Array of foreign key information 1257 * 1258 * @link http://gis.mit.edu/classes/11.521/sqlnotes/referential_integrity.html 1259 */ 1260 function MetaForeignKeys($table, $owner=false, $upper=false) 1261 { 1262 global $ADODB_FETCH_MODE; 1263 1264 $save = $ADODB_FETCH_MODE; 1265 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 1266 $table = $this->qstr(strtoupper($table)); 1267 if (!$owner) { 1268 $owner = $this->user; 1269 $tabp = 'user_'; 1270 } else 1271 $tabp = 'all_'; 1272 1273 $owner = ' and owner='.$this->qstr(strtoupper($owner)); 1274 1275 $sql = 1276 "select constraint_name,r_owner,r_constraint_name 1277 from {$tabp}constraints 1278 where constraint_type = 'R' and table_name = $table $owner"; 1279 1280 $constraints = $this->GetArray($sql); 1281 $arr = false; 1282 foreach($constraints as $constr) { 1283 $cons = $this->qstr($constr[0]); 1284 $rowner = $this->qstr($constr[1]); 1285 $rcons = $this->qstr($constr[2]); 1286 $cols = $this->GetArray("select column_name from {$tabp}cons_columns where constraint_name=$cons $owner order by position"); 1287 $tabcol = $this->GetArray("select table_name,column_name from {$tabp}cons_columns where owner=$rowner and constraint_name=$rcons order by position"); 1288 1289 if ($cols && $tabcol) 1290 for ($i=0, $max=sizeof($cols); $i < $max; $i++) { 1291 $arr[$tabcol[$i][0]] = $cols[$i][0].'='.$tabcol[$i][1]; 1292 } 1293 } 1294 $ADODB_FETCH_MODE = $save; 1295 1296 return $arr; 1297 } 1298 1299 1300 function CharMax() 1301 { 1302 return 4000; 1303 } 1304 1305 function TextMax() 1306 { 1307 return 4000; 1308 } 1309 1310 /** 1311 * Quotes a string. 1312 * An example is $db->qstr("Don't bother",magic_quotes_runtime()); 1313 * 1314 * @param string $s the string to quote 1315 * @param bool $magic_quotes if $s is GET/POST var, set to get_magic_quotes_gpc(). 1316 * This undoes the stupidity of magic quotes for GPC. 1317 * 1318 * @return string quoted string to be sent back to database 1319 */ 1320 function qstr($s,$magic_quotes=false) 1321 { 1322 //$nofixquotes=false; 1323 1324 if ($this->noNullStrings && strlen($s)==0)$s = ' '; 1325 if (!$magic_quotes) { 1326 if ($this->replaceQuote[0] == '\\'){ 1327 $s = str_replace('\\','\\\\',$s); 1328 } 1329 return "'".str_replace("'",$this->replaceQuote,$s)."'"; 1330 } 1331 1332 // undo magic quotes for " unless sybase is on 1333 if (!ini_get('magic_quotes_sybase')) { 1334 $s = str_replace('\\"','"',$s); 1335 $s = str_replace('\\\\','\\',$s); 1336 return "'".str_replace("\\'",$this->replaceQuote,$s)."'"; 1337 } else { 1338 return "'".$s."'"; 1339 } 1340 } 1341 1342 } 1343 1344 /*-------------------------------------------------------------------------------------- 1345 Class Name: Recordset 1346 --------------------------------------------------------------------------------------*/ 1347 1348 class ADORecordset_oci8 extends ADORecordSet { 1349 1350 var $databaseType = 'oci8'; 1351 var $bind=false; 1352 var $_fieldobjs; 1353 1354 //var $_arr = false; 1355 1356 function ADORecordset_oci8($queryID,$mode=false) 1357 { 1358 if ($mode === false) { 1359 global $ADODB_FETCH_MODE; 1360 $mode = $ADODB_FETCH_MODE; 1361 } 1362 switch ($mode) 1363 { 1364 case ADODB_FETCH_ASSOC:$this->fetchMode = OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break; 1365 case ADODB_FETCH_DEFAULT: 1366 case ADODB_FETCH_BOTH:$this->fetchMode = OCI_NUM+OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break; 1367 case ADODB_FETCH_NUM: 1368 default: 1369 $this->fetchMode = OCI_NUM+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break; 1370 } 1371 1372 $this->adodbFetchMode = $mode; 1373 $this->_queryID = $queryID; 1374 } 1375 1376 1377 function Init() 1378 { 1379 if ($this->_inited) return; 1380 1381 $this->_inited = true; 1382 if ($this->_queryID) { 1383 1384 $this->_currentRow = 0; 1385 @$this->_initrs(); 1386 if ($this->_numOfFields) $this->EOF = !$this->_fetch(); 1387 else $this->EOF = true; 1388 1389 /* 1390 // based on idea by Gaetano Giunta to detect unusual oracle errors 1391 // see http://phplens.com/lens/lensforum/msgs.php?id=6771 1392 $err = oci_error($this->_queryID); 1393 if ($err && $this->connection->debug) ADOConnection::outp($err); 1394 */ 1395 1396 if (!is_array($this->fields)) { 1397 $this->_numOfRows = 0; 1398 $this->fields = array(); 1399 } 1400 } else { 1401 $this->fields = array(); 1402 $this->_numOfRows = 0; 1403 $this->_numOfFields = 0; 1404 $this->EOF = true; 1405 } 1406 } 1407 1408 function _initrs() 1409 { 1410 $this->_numOfRows = -1; 1411 $this->_numOfFields = oci_num_fields($this->_queryID); 1412 if ($this->_numOfFields>0) { 1413 $this->_fieldobjs = array(); 1414 $max = $this->_numOfFields; 1415 for ($i=0;$i<$max; $i++) $this->_fieldobjs[] = $this->_FetchField($i); 1416 } 1417 } 1418 1419 /** 1420 * Get column information in the Recordset object. 1421 * fetchField() can be used in order to obtain information about fields 1422 * in a certain query result. If the field offset isn't specified, the next 1423 * field that wasn't yet retrieved by fetchField() is retrieved 1424 * 1425 * @return object containing field information 1426 */ 1427 function _FetchField($fieldOffset = -1) 1428 { 1429 $fld = new ADOFieldObject; 1430 $fieldOffset += 1; 1431 $fld->name =oci_field_name($this->_queryID, $fieldOffset); 1432 $fld->type = oci_field_type($this->_queryID, $fieldOffset); 1433 $fld->max_length = oci_field_size($this->_queryID, $fieldOffset); 1434 1435 switch($fld->type) { 1436 case 'NUMBER': 1437 $p = oci_field_precision($this->_queryID, $fieldOffset); 1438 $sc = oci_field_scale($this->_queryID, $fieldOffset); 1439 if ($p != 0 && $sc == 0) $fld->type = 'INT'; 1440 $fld->scale = $p; 1441 break; 1442 1443 case 'CLOB': 1444 case 'NCLOB': 1445 case 'BLOB': 1446 $fld->max_length = -1; 1447 break; 1448 } 1449 return $fld; 1450 } 1451 1452 /* For some reason, oci_field_name fails when called after _initrs() so we cache it */ 1453 function FetchField($fieldOffset = -1) 1454 { 1455 return $this->_fieldobjs[$fieldOffset]; 1456 } 1457 1458 1459 /* 1460 // 10% speedup to move MoveNext to child class 1461 function _MoveNext() 1462 { 1463 //global $ADODB_EXTENSION;if ($ADODB_EXTENSION) return @adodb_movenext($this); 1464 1465 if ($this->EOF) return false; 1466 1467 $this->_currentRow++; 1468 if($this->fields = @oci_fetch_array($this->_queryID,$this->fetchMode)) 1469 return true; 1470 $this->EOF = true; 1471 1472 return false; 1473 } 1474 */ 1475 1476 1477 function MoveNext() 1478 { 1479 if ($this->fields = @oci_fetch_array($this->_queryID,$this->fetchMode)) { 1480 $this->_currentRow += 1; 1481 return true; 1482 } 1483 if (!$this->EOF) { 1484 $this->_currentRow += 1; 1485 $this->EOF = true; 1486 } 1487 return false; 1488 } 1489 1490 /* 1491 # does not work as first record is retrieved in _initrs(), so is not included in GetArray() 1492 function GetArray($nRows = -1) 1493 { 1494 global $ADODB_OCI8_GETARRAY; 1495 1496 if (true || !empty($ADODB_OCI8_GETARRAY)) { 1497 # does not support $ADODB_ANSI_PADDING_OFF 1498 1499 //OCI_RETURN_NULLS and OCI_RETURN_LOBS is set by oci_fetch_all 1500 switch($this->adodbFetchMode) { 1501 case ADODB_FETCH_NUM: 1502 1503 $ncols = @oci_fetch_all($this->_queryID, $results, 0, $nRows, oci_fetch_all_BY_ROW+OCI_NUM); 1504 $results = array_merge(array($this->fields),$results); 1505 return $results; 1506 1507 case ADODB_FETCH_ASSOC: 1508 if (ADODB_ASSOC_CASE != 2 || $this->databaseType != 'oci8') break; 1509 1510 $ncols = @oci_fetch_all($this->_queryID, $assoc, 0, $nRows, oci_fetch_all_BY_ROW); 1511 $results = array_merge(array($this->fields),$assoc); 1512 return $results; 1513 1514 default: 1515 break; 1516 } 1517 } 1518 1519 $results = ADORecordSet::GetArray($nRows); 1520 return $results; 1521 1522 } 1523 */ 1524 1525 // Optimize SelectLimit() by using oci_fetch() 1526 function GetArrayLimit($nrows,$offset=-1) 1527 { 1528 if ($offset <= 0) { 1529 $arr = $this->GetArray($nrows); 1530 return $arr; 1531 } 1532 $arr = array(); 1533 for ($i=1; $i < $offset; $i++) 1534 if (!@oci_fetch($this->_queryID)) return $arr; 1535 1536 if (!$this->fields = @oci_fetch_array($this->_queryID,$this->fetchMode)) return $arr;; 1537 $results = array(); 1538 $cnt = 0; 1539 while (!$this->EOF && $nrows != $cnt) { 1540 $results[$cnt++] = $this->fields; 1541 $this->MoveNext(); 1542 } 1543 1544 return $results; 1545 } 1546 1547 1548 // Use associative array to get fields array 1549 function Fields($colname) 1550 { 1551 if (!$this->bind) { 1552 $this->bind = array(); 1553 for ($i=0; $i < $this->_numOfFields; $i++) { 1554 $o = $this->FetchField($i); 1555 $this->bind[strtoupper($o->name)] = $i; 1556 } 1557 } 1558 1559 return $this->fields[$this->bind[strtoupper($colname)]]; 1560 } 1561 1562 1563 1564 function _seek($row) 1565 { 1566 return false; 1567 } 1568 1569 function _fetch() 1570 { 1571 return $this->fields = @oci_fetch_array($this->_queryID,$this->fetchMode); 1572 } 1573 1574 /** 1575 * close() only needs to be called if you are worried about using too much 1576 * memory while your script is running. All associated result memory for the 1577 * specified result identifier will automatically be freed. 1578 */ 1579 function _close() 1580 { 1581 if ($this->connection->_stmt === $this->_queryID) $this->connection->_stmt = false; 1582 if (!empty($this->_refcursor)) { 1583 oci_free_cursor($this->_refcursor); 1584 $this->_refcursor = false; 1585 } 1586 @oci_free_statement($this->_queryID); 1587 $this->_queryID = false; 1588 } 1589 1590 /** 1591 * not the fastest implementation - quick and dirty - jlim 1592 * for best performance, use the actual $rs->MetaType(). 1593 * 1594 * @param mixed $t 1595 * @param int $len [optional] Length of blobsize 1596 * @param bool $fieldobj [optional][discarded] 1597 * @return str The metatype of the field 1598 */ 1599 function MetaType($t, $len=-1, $fieldobj=false) 1600 { 1601 if (is_object($t)) { 1602 $fieldobj = $t; 1603 $t = $fieldobj->type; 1604 $len = $fieldobj->max_length; 1605 } 1606 1607 switch (strtoupper($t)) { 1608 case 'VARCHAR': 1609 case 'VARCHAR2': 1610 case 'CHAR': 1611 case 'VARBINARY': 1612 case 'BINARY': 1613 case 'NCHAR': 1614 case 'NVARCHAR': 1615 case 'NVARCHAR2': 1616 if ($len <= $this->blobSize) return 'C'; 1617 1618 case 'NCLOB': 1619 case 'LONG': 1620 case 'LONG VARCHAR': 1621 case 'CLOB': 1622 return 'X'; 1623 1624 case 'LONG RAW': 1625 case 'LONG VARBINARY': 1626 case 'BLOB': 1627 return 'B'; 1628 1629 case 'DATE': 1630 return ($this->connection->datetime) ? 'T' : 'D'; 1631 1632 1633 case 'TIMESTAMP': return 'T'; 1634 1635 case 'INT': 1636 case 'SMALLINT': 1637 case 'INTEGER': 1638 return 'I'; 1639 1640 default: 1641 return 'N'; 1642 } 1643 } 1644 } 1645 1646 class ADORecordSet_ext_oci8 extends ADORecordSet_oci8 { 1647 function ADORecordSet_ext_oci8($queryID,$mode=false) 1648 { 1649 if ($mode === false) { 1650 global $ADODB_FETCH_MODE; 1651 $mode = $ADODB_FETCH_MODE; 1652 } 1653 switch ($mode) 1654 { 1655 case ADODB_FETCH_ASSOC:$this->fetchMode = OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break; 1656 case ADODB_FETCH_DEFAULT: 1657 case ADODB_FETCH_BOTH:$this->fetchMode = OCI_NUM+OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break; 1658 case ADODB_FETCH_NUM: 1659 default: $this->fetchMode = OCI_NUM+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break; 1660 } 1661 $this->adodbFetchMode = $mode; 1662 $this->_queryID = $queryID; 1663 } 1664 1665 function MoveNext() 1666 { 1667 return adodb_movenext($this); 1668 } 1669 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Fri Nov 28 20:29:05 2014 | Cross-referenced by PHPXref 0.7.1 |