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