lib/adodb/drivers/adodb-oci8.inc.php

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