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