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

   1 <?php
   2 /*
   3
   4   version V5.18 3 Sep 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('oci_pconnect')) 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                                 oci_pconnect($argUsername,$argPassword, $argDatabasename,$this->charSet)
 239                                 :
 240                                 oci_pconnect($argUsername,$argPassword, $argDatabasename)
 241                                 ;
 242                         if ($this->_connectionID && $this->autoRollback)  oci_rollback($this->_connectionID);
 243                 } else if ($mode==2) {
 244                         $this->_connectionID = ($this->charSet) ?
 245                                 oci_new_connect($argUsername,$argPassword, $argDatabasename,$this->charSet)
 246                                 :
 247                                 oci_new_connect($argUsername,$argPassword, $argDatabasename);
 248
 249                 } else {
 250                         $this->_connectionID = ($this->charSet) ?
 251                                 oci_connect($argUsername,$argPassword, $argDatabasename,$this->charSet)
 252                                 :
 253                                 oci_connect($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 = oci_server_version($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'] = @oci_server_version($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 @oci_num_rows($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 (!is_object($rs)) {
 390                         if (isset($savem))
 391                                 $this->SetFetchMode($savem);
 392                         $ADODB_FETCH_MODE = $save;
 393             return $false;
 394         }
 395
 396                 if ($row = $rs->FetchRow())
 397                    $primary_key = $row[1]; //constraint_name
 398
 399                 if ($primary==TRUE && $primary_key=='') {
 400                          if (isset($savem))
 401                 $this->SetFetchMode($savem);
 402                         $ADODB_FETCH_MODE = $save;
 403                         return $false; //There is no primary key
 404                 }
 405
 406         $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));
 407
 408
 409         if (!is_object($rs)) {
 410                         if (isset($savem))
 411                                 $this->SetFetchMode($savem);
 412                         $ADODB_FETCH_MODE = $save;
 413             return $false;
 414         }
 415
 416                 $indexes = array ();
 417         // parse index data into array
 418
 419         while ($row = $rs->FetchRow()) {
 420                         if ($primary && $row[0] != $primary_key) continue;
 421             if (!isset($indexes[$row[0]])) {
 422                                 $indexes[$row[0]] = array(
 423                                    'unique' => ($row[1] == 'UNIQUE'),
 424                                    'columns' => array()
 425                                 );
 426             }
 427             $indexes[$row[0]]['columns'][$row[2] - 1] = $row[3];
 428         }
 429
 430         // sort columns by order in the index
 431         foreach ( array_keys ($indexes) as $index ) {
 432             ksort ($indexes[$index]['columns']);
 433         }
 434
 435                 if (isset($savem)) {
 436             $this->SetFetchMode($savem);
 437                         $ADODB_FETCH_MODE = $save;
 438                 }
 439         return $indexes;
 440         }
 441
 442         function BeginTrans()
 443         {
 444                 if ($this->transOff) return true;
 445                 $this->transCnt += 1;
 446                 $this->autoCommit = false;
 447                 $this->_commit = OCI_DEFAULT;
 448
 449                 if ($this->_transmode) $ok = $this->Execute("SET TRANSACTION ".$this->_transmode);
 450                 else $ok = true;
 451
 452                 return $ok ? true : false;
 453         }
 454
 455         function CommitTrans($ok=true)
 456         {
 457                 if ($this->transOff) return true;
 458                 if (!$ok) return $this->RollbackTrans();
 459
 460                 if ($this->transCnt) $this->transCnt -= 1;
 461                 $ret = oci_commit($this->_connectionID);
 462                 $this->_commit = OCI_COMMIT_ON_SUCCESS;
 463                 $this->autoCommit = true;
 464                 return $ret;
 465         }
 466
 467         function RollbackTrans()
 468         {
 469                 if ($this->transOff) return true;
 470                 if ($this->transCnt) $this->transCnt -= 1;
 471                 $ret = oci_rollback($this->_connectionID);
 472                 $this->_commit = OCI_COMMIT_ON_SUCCESS;
 473                 $this->autoCommit = true;
 474                 return $ret;
 475         }
 476
 477
 478         function SelectDB($dbName)
 479         {
 480                 return false;
 481         }
 482
 483         function ErrorMsg()
 484         {
 485                 if ($this->_errorMsg !== false) return $this->_errorMsg;
 486
 487                 if (is_resource($this->_stmt)) $arr = @oci_error($this->_stmt);
 488                 if (empty($arr)) {
 489                         if (is_resource($this->_connectionID)) $arr = @oci_error($this->_connectionID);
 490                         else $arr = @oci_error();
 491                         if ($arr === false) return '';
 492                 }
 493                 $this->_errorMsg = $arr['message'];
 494                 $this->_errorCode = $arr['code'];
 495                 return $this->_errorMsg;
 496         }
 497
 498         function ErrorNo()
 499         {
 500                 if ($this->_errorCode !== false) return $this->_errorCode;
 501
 502                 if (is_resource($this->_stmt)) $arr = @oci_error($this->_stmt);
 503                 if (empty($arr)) {
 504                         $arr = @oci_error($this->_connectionID);
 505                         if ($arr == false) $arr = @oci_error();
 506                         if ($arr == false) return '';
 507                 }
 508
 509                 $this->_errorMsg = $arr['message'];
 510                 $this->_errorCode = $arr['code'];
 511
 512                 return $arr['code'];
 513         }
 514
 515         // Format date column in sql string given an input format that understands Y M D
 516         function SQLDate($fmt, $col=false)
 517         {
 518                 if (!$col) $col = $this->sysTimeStamp;
 519                 $s = 'TO_CHAR('.$col.",'";
 520
 521                 $len = strlen($fmt);
 522                 for ($i=0; $i < $len; $i++) {
 523                         $ch = $fmt[$i];
 524                         switch($ch) {
 525                         case 'Y':
 526                         case 'y':
 527                                 $s .= 'YYYY';
 528                                 break;
 529                         case 'Q':
 530                         case 'q':
 531                                 $s .= 'Q';
 532                                 break;
 533
 534                         case 'M':
 535                                 $s .= 'Mon';
 536                                 break;
 537
 538                         case 'm':
 539                                 $s .= 'MM';
 540                                 break;
 541                         case 'D':
 542                         case 'd':
 543                                 $s .= 'DD';
 544                                 break;
 545
 546                         case 'H':
 547                                 $s.= 'HH24';
 548                                 break;
 549
 550                         case 'h':
 551                                 $s .= 'HH';
 552                                 break;
 553
 554                         case 'i':
 555                                 $s .= 'MI';
 556                                 break;
 557
 558                         case 's':
 559                                 $s .= 'SS';
 560                                 break;
 561
 562                         case 'a':
 563                         case 'A':
 564                                 $s .= 'AM';
 565                                 break;
 566
 567                         case 'w':
 568                                 $s .= 'D';
 569                                 break;
 570
 571                         case 'l':
 572                                 $s .= 'DAY';
 573                                 break;
 574
 575                          case 'W':
 576                                 $s .= 'WW';
 577                                 break;
 578
 579                         default:
 580                         // handle escape characters...
 581                                 if ($ch == '\\') {
 582                                         $i++;
 583                                         $ch = substr($fmt,$i,1);
 584                                 }
 585                                 if (strpos('-/.:;, ',$ch) !== false) $s .= $ch;
 586                                 else $s .= '"'.$ch.'"';
 587
 588                         }
 589                 }
 590                 return $s. "')";
 591         }
 592
 593         function GetRandRow($sql, $arr = false)
 594         {
 595                 $sql = "SELECT * FROM ($sql ORDER BY dbms_random.value) WHERE rownum = 1";
 596
 597                 return $this->GetRow($sql,$arr);
 598         }
 599
 600         /*
 601         This algorithm makes use of
 602
 603         a. FIRST_ROWS hint
 604         The FIRST_ROWS hint explicitly chooses the approach to optimize response time,
 605         that is, minimum resource usage to return the first row. Results will be returned
 606         as soon as they are identified.
 607
 608         b. Uses rownum tricks to obtain only the required rows from a given offset.
 609          As this uses complicated sql statements, we only use this if the $offset >= 100.
 610          This idea by Tomas V V Cox.
 611
 612          This implementation does not appear to work with oracle 8.0.5 or earlier. Comment
 613          out this function then, and the slower SelectLimit() in the base class will be used.
 614         */
 615         function SelectLimit($sql,$nrows=-1,$offset=-1, $inputarr=false,$secs2cache=0)
 616         {
 617                 // seems that oracle only supports 1 hint comment in 8i
 618                 if ($this->firstrows) {
 619                         if ($nrows > 500 && $nrows < 1000) $hint = "FIRST_ROWS($nrows)";
 620                         else $hint = 'FIRST_ROWS';
 621
 622                         if (strpos($sql,'/*+') !== false)
 623                                 $sql = str_replace('/*+ ',"/*+$hint ",$sql);
 624                         else
 625                                 $sql = preg_replace('/^[ \t\n]*select/i',"SELECT /*+$hint*/",$sql);
 626                 }
 627
 628                 if ($offset == -1 || ($offset < $this->selectOffsetAlg1 && 0 < $nrows && $nrows < 1000)) {
 629                         if ($nrows > 0) {
 630                                 if ($offset > 0) $nrows += $offset;
 631                                 //$inputarr['adodb_rownum'] = $nrows;
 632                                 if ($this->databaseType == 'oci8po') {
 633                                         $sql = "select * from (".$sql.") where rownum <= ?";
 634                                 } else {
 635                                         $sql = "select * from (".$sql.") where rownum <= :adodb_offset";
 636                                 }
 637                                 $inputarr['adodb_offset'] = $nrows;
 638                                 $nrows = -1;
 639                         }
 640                         // note that $nrows = 0 still has to work ==> no rows returned
 641
 642                         $rs = ADOConnection::SelectLimit($sql,$nrows,$offset,$inputarr,$secs2cache);
 643                         return $rs;
 644
 645                 } else {
 646                          // Algorithm by Tomas V V Cox, from PEAR DB oci8.php
 647
 648                          // Let Oracle return the name of the columns
 649                         $q_fields = "SELECT * FROM (".$sql.") WHERE NULL = NULL";
 650
 651                         $false = false;
 652                         if (! $stmt_arr = $this->Prepare($q_fields)) {
 653                                 return $false;
 654                         }
 655                         $stmt = $stmt_arr[1];
 656
 657                         if (is_array($inputarr)) {
 658                                 foreach($inputarr as $k => $v) {
 659                                         if (is_array($v)) {
 660                                                 if (sizeof($v) == 2) // suggested by g.giunta@libero.
 661                                                         oci_bind_by_name($stmt,":$k",$inputarr[$k][0],$v[1]);
 662                                                 else
 663                                                         oci_bind_by_name($stmt,":$k",$inputarr[$k][0],$v[1],$v[2]);
 664                                         } else {
 665                                                 $len = -1;
 666                                                 if ($v === ' ') $len = 1;
 667                                                 if (isset($bindarr)) {  // is prepared sql, so no need to oci_bind_by_name again
 668                                                         $bindarr[$k] = $v;
 669                                                 } else {                                // dynamic sql, so rebind every time
 670                                                         oci_bind_by_name($stmt,":$k",$inputarr[$k],$len);
 671
 672                                                 }
 673                                         }
 674                                 }
 675                         }
 676
 677                          if (!oci_execute($stmt, OCI_DEFAULT)) {
 678                                  oci_free_statement($stmt);
 679                                  return $false;
 680                          }
 681
 682                          $ncols = oci_num_fields($stmt);
 683                          for ( $i = 1; $i <= $ncols; $i++ ) {
 684                                  $cols[] = '"'.oci_field_name($stmt, $i).'"';
 685                          }
 686                          $result = false;
 687
 688                          oci_free_statement($stmt);
 689                          $fields = implode(',', $cols);
 690                          if ($nrows <= 0) $nrows = 999999999999;
 691                          else $nrows += $offset;
 692                          $offset += 1; // in Oracle rownum starts at 1
 693
 694                         if ($this->databaseType == 'oci8po') {
 695                                          $sql = "SELECT /*+ FIRST_ROWS */ $fields FROM".
 696                                           "(SELECT rownum as adodb_rownum, $fields FROM".
 697                                           " ($sql) WHERE rownum <= ?".
 698                                           ") WHERE adodb_rownum >= ?";
 699                                 } else {
 700                                          $sql = "SELECT /*+ FIRST_ROWS */ $fields FROM".
 701                                           "(SELECT rownum as adodb_rownum, $fields FROM".
 702                                           " ($sql) WHERE rownum <= :adodb_nrows".
 703                                           ") WHERE adodb_rownum >= :adodb_offset";
 704                                 }
 705                                 $inputarr['adodb_nrows'] = $nrows;
 706                                 $inputarr['adodb_offset'] = $offset;
 707
 708                         if ($secs2cache>0) $rs = $this->CacheExecute($secs2cache, $sql,$inputarr);
 709                         else $rs = $this->Execute($sql,$inputarr);
 710                         return $rs;
 711                 }
 712
 713         }
 714
 715         /**
 716         * Usage:
 717         * Store BLOBs and CLOBs
 718         *
 719         * Example: to store $var in a blob
 720         *
 721         *       $conn->Execute('insert into TABLE (id,ablob) values(12,empty_blob())');
 722         *       $conn->UpdateBlob('TABLE', 'ablob', $varHoldingBlob, 'ID=12', 'BLOB');
 723         *
 724         *       $blobtype supports 'BLOB' and 'CLOB', but you need to change to 'empty_clob()'.
 725         *
 726         *  to get length of LOB:
 727         *       select DBMS_LOB.GETLENGTH(ablob) from TABLE
 728         *
 729         * If you are using CURSOR_SHARING = force, it appears this will case a segfault
 730         * under oracle 8.1.7.0. Run:
 731         *        $db->Execute('ALTER SESSION SET CURSOR_SHARING=EXACT');
 732         * before UpdateBlob() then...
 733         */
 734
 735         function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
 736         {
 737
 738                 //if (strlen($val) < 4000) return $this->Execute("UPDATE $table SET $column=:blob WHERE $where",array('blob'=>$val)) != false;
 739
 740                 switch(strtoupper($blobtype)) {
 741                 default: ADOConnection::outp("<b>UpdateBlob</b>: Unknown blobtype=$blobtype"); return false;
 742                 case 'BLOB': $type = OCI_B_BLOB; break;
 743                 case 'CLOB': $type = OCI_B_CLOB; break;
 744                 }
 745
 746                 if ($this->databaseType == 'oci8po')
 747                         $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO ?";
 748                 else
 749                         $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO :blob";
 750
 751                 $desc = oci_new_descriptor($this->_connectionID, OCI_D_LOB);
 752                 $arr['blob'] = array($desc,-1,$type);
 753                 if ($this->session_sharing_force_blob) $this->Execute('ALTER SESSION SET CURSOR_SHARING=EXACT');
 754                 $commit = $this->autoCommit;
 755                 if ($commit) $this->BeginTrans();
 756                 $rs = $this->_Execute($sql,$arr);
 757                 if ($rez = !empty($rs)) $desc->save($val);
 758                 $desc->free();
 759                 if ($commit) $this->CommitTrans();
 760                 if ($this->session_sharing_force_blob) $this->Execute('ALTER SESSION SET CURSOR_SHARING=FORCE');
 761
 762                 if ($rez) $rs->Close();
 763                 return $rez;
 764         }
 765
 766         /**
 767         * Usage:  store file pointed to by $val in a blob
 768         */
 769         function UpdateBlobFile($table,$column,$val,$where,$blobtype='BLOB')
 770         {
 771                 switch(strtoupper($blobtype)) {
 772                 default: ADOConnection::outp( "<b>UpdateBlob</b>: Unknown blobtype=$blobtype"); return false;
 773                 case 'BLOB': $type = OCI_B_BLOB; break;
 774                 case 'CLOB': $type = OCI_B_CLOB; break;
 775                 }
 776
 777                 if ($this->databaseType == 'oci8po')
 778                         $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO ?";
 779                 else
 780                         $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO :blob";
 781
 782                 $desc = oci_new_descriptor($this->_connectionID, OCI_D_LOB);
 783                 $arr['blob'] = array($desc,-1,$type);
 784
 785                 $this->BeginTrans();
 786                 $rs = ADODB_oci8::Execute($sql,$arr);
 787                 if ($rez = !empty($rs)) $desc->savefile($val);
 788                 $desc->free();
 789                 $this->CommitTrans();
 790
 791                 if ($rez) $rs->Close();
 792                 return $rez;
 793         }
 794
 795                 /**
 796          * Execute SQL
 797          *
 798          * @param sql           SQL statement to execute, or possibly an array holding prepared statement ($sql[0] will hold sql text)
 799          * @param [inputarr]    holds the input data to bind to. Null elements will be set to null.
 800          * @return              RecordSet or false
 801          */
 802         function Execute($sql,$inputarr=false)
 803         {
 804                 if ($this->fnExecute) {
 805                         $fn = $this->fnExecute;
 806                         $ret = $fn($this,$sql,$inputarr);
 807                         if (isset($ret)) return $ret;
 808                 }
 809                 if ($inputarr) {
 810                         #if (!is_array($inputarr)) $inputarr = array($inputarr);
 811
 812                         $element0 = reset($inputarr);
 813                         $array2d =  $this->bulkBind && is_array($element0) && !is_object(reset($element0));
 814
 815                         # see http://phplens.com/lens/lensforum/msgs.php?id=18786
 816                         if ($array2d || !$this->_bindInputArray) {
 817
 818                         # is_object check because oci8 descriptors can be passed in
 819                         if ($array2d && $this->_bindInputArray) {
 820                                 if (is_string($sql))
 821                                         $stmt = $this->Prepare($sql);
 822                                 else
 823                                         $stmt = $sql;
 824
 825                                 foreach($inputarr as $arr) {
 826                                         $ret = $this->_Execute($stmt,$arr);
 827                                         if (!$ret) return $ret;
 828                                 }
 829                                 return $ret;
 830                         } else {
 831                                 $sqlarr = explode(':',$sql);
 832                                 $sql = '';
 833                                 $lastnomatch = -2;
 834                                 #var_dump($sqlarr);echo "<hr>";var_dump($inputarr);echo"<hr>";
 835                                 foreach($sqlarr as $k => $str) {
 836                                                 if ($k == 0) { $sql = $str; continue; }
 837                                                 // we need $lastnomatch because of the following datetime,
 838                                                 // eg. '10:10:01', which causes code to think that there is bind param :10 and :1
 839                                                 $ok = preg_match('/^([0-9]*)/', $str, $arr);
 840
 841                                                 if (!$ok) $sql .= $str;
 842                                                 else {
 843                                                         $at = $arr[1];
 844                                                         if (isset($inputarr[$at]) || is_null($inputarr[$at])) {
 845                                                                 if ((strlen($at) == strlen($str) && $k < sizeof($arr)-1)) {
 846                                                                         $sql .= ':'.$str;
 847                                                                         $lastnomatch = $k;
 848                                                                 } else if ($lastnomatch == $k-1) {
 849                                                                         $sql .= ':'.$str;
 850                                                                 } else {
 851                                                                         if (is_null($inputarr[$at])) $sql .= 'null';
 852                                                                         else $sql .= $this->qstr($inputarr[$at]);
 853                                                                         $sql .= substr($str, strlen($at));
 854                                                                 }
 855                                                         } else {
 856                                                                 $sql .= ':'.$str;
 857                                                         }
 858
 859                                                 }
 860                                         }
 861                                         $inputarr = false;
 862                                 }
 863                         }
 864                         $ret = $this->_Execute($sql,$inputarr);
 865
 866
 867                 } else {
 868                         $ret = $this->_Execute($sql,false);
 869                 }
 870
 871                 return $ret;
 872         }
 873
 874         /*
 875                 Example of usage:
 876
 877                 $stmt = $this->Prepare('insert into emp (empno, ename) values (:empno, :ename)');
 878         */
 879         function Prepare($sql,$cursor=false)
 880         {
 881         static $BINDNUM = 0;
 882
 883                 $stmt = oci_parse($this->_connectionID,$sql);
 884
 885                 if (!$stmt) {
 886                         $this->_errorMsg = false;
 887                         $this->_errorCode = false;
 888                         $arr = @oci_error($this->_connectionID);
 889                         if ($arr === false) return false;
 890
 891                         $this->_errorMsg = $arr['message'];
 892                         $this->_errorCode = $arr['code'];
 893                         return false;
 894                 }
 895
 896                 $BINDNUM += 1;
 897
 898                 $sttype = @oci_statement_type($stmt);
 899                 if ($sttype == 'BEGIN' || $sttype == 'DECLARE') {
 900                         return array($sql,$stmt,0,$BINDNUM, ($cursor) ? oci_new_cursor($this->_connectionID) : false);
 901                 }
 902                 return array($sql,$stmt,0,$BINDNUM);
 903         }
 904
 905         /*
 906                 Call an oracle stored procedure and returns a cursor variable as a recordset.
 907                 Concept by Robert Tuttle robert@ud.com
 908
 909                 Example:
 910                         Note: we return a cursor variable in :RS2
 911                         $rs = $db->ExecuteCursor("BEGIN adodb.open_tab(:RS2); END;",'RS2');
 912
 913                         $rs = $db->ExecuteCursor(
 914                                 "BEGIN :RS2 = adodb.getdata(:VAR1); END;",
 915                                 'RS2',
 916                                 array('VAR1' => 'Mr Bean'));
 917
 918         */
 919         function ExecuteCursor($sql,$cursorName='rs',$params=false)
 920         {
 921                 if (is_array($sql)) $stmt = $sql;
 922                 else $stmt = ADODB_oci8::Prepare($sql,true); # true to allocate oci_new_cursor
 923
 924                 if (is_array($stmt) && sizeof($stmt) >= 5) {
 925                         $hasref = true;
 926                         $ignoreCur = false;
 927                         $this->Parameter($stmt, $ignoreCur, $cursorName, false, -1, OCI_B_CURSOR);
 928                         if ($params) {
 929                                 foreach($params as $k => $v) {
 930                                         $this->Parameter($stmt,$params[$k], $k);
 931                                 }
 932                         }
 933                 } else
 934                         $hasref = false;
 935
 936                 $rs = $this->Execute($stmt);
 937                 if ($rs) {
 938                         if ($rs->databaseType == 'array') oci_free_cursor($stmt[4]);
 939                         else if ($hasref) $rs->_refcursor = $stmt[4];
 940                 }
 941                 return $rs;
 942         }
 943
 944         /*
 945                 Bind a variable -- very, very fast for executing repeated statements in oracle.
 946                 Better than using
 947                         for ($i = 0; $i < $max; $i++) {
 948                                 $p1 = ?; $p2 = ?; $p3 = ?;
 949                                 $this->Execute("insert into table (col0, col1, col2) values (:0, :1, :2)",
 950                                         array($p1,$p2,$p3));
 951                         }
 952
 953                 Usage:
 954                         $stmt = $DB->Prepare("insert into table (col0, col1, col2) values (:0, :1, :2)");
 955                         $DB->Bind($stmt, $p1);
 956                         $DB->Bind($stmt, $p2);
 957                         $DB->Bind($stmt, $p3);
 958                         for ($i = 0; $i < $max; $i++) {
 959                                 $p1 = ?; $p2 = ?; $p3 = ?;
 960                                 $DB->Execute($stmt);
 961                         }
 962
 963                 Some timings:
 964                         ** Test table has 3 cols, and 1 index. Test to insert 1000 records
 965                         Time 0.6081s (1644.60 inserts/sec) with direct oci_parse/oci_execute
 966                         Time 0.6341s (1577.16 inserts/sec) with ADOdb Prepare/Bind/Execute
 967                         Time 1.5533s ( 643.77 inserts/sec) with pure SQL using Execute
 968
 969                 Now if PHP only had batch/bulk updating like Java or PL/SQL...
 970
 971                 Note that the order of parameters differs from oci_bind_by_name,
 972                 because we default the names to :0, :1, :2
 973         */
 974         function Bind(&$stmt,&$var,$size=4000,$type=false,$name=false,$isOutput=false)
 975         {
 976
 977                 if (!is_array($stmt)) return false;
 978
 979         if (($type == OCI_B_CURSOR) && sizeof($stmt) >= 5) {
 980             return oci_bind_by_name($stmt[1],":".$name,$stmt[4],$size,$type);
 981         }
 982
 983                 if ($name == false) {
 984                         if ($type !== false) $rez = oci_bind_by_name($stmt[1],":".$stmt[2],$var,$size,$type);
 985                         else $rez = oci_bind_by_name($stmt[1],":".$stmt[2],$var,$size); // +1 byte for null terminator
 986                         $stmt[2] += 1;
 987                 } else if (oci_lob_desc($type)) {
 988                         if ($this->debug) {
 989                                 ADOConnection::outp("<b>Bind</b>: name = $name");
 990                         }
 991             //we have to create a new Descriptor here
 992                         $numlob = count($this->_refLOBs);
 993                 $this->_refLOBs[$numlob]['LOB'] = oci_new_descriptor($this->_connectionID, oci_lob_desc($type));
 994                         $this->_refLOBs[$numlob]['TYPE'] = $isOutput;
 995
 996                         $tmp = $this->_refLOBs[$numlob]['LOB'];
 997                 $rez = oci_bind_by_name($stmt[1], ":".$name, $tmp, -1, $type);
 998                         if ($this->debug) {
 999                                 ADOConnection::outp("<b>Bind</b>: descriptor has been allocated, var (".$name.") binded");
1000                         }
1001
1002                         // if type is input then write data to lob now
1003                         if ($isOutput == false) {
1004                                 $var = $this->BlobEncode($var);
1005                                 $tmp->WriteTemporary($var);
1006                                 $this->_refLOBs[$numlob]['VAR'] = &$var;
1007                                 if ($this->debug) {
1008                                         ADOConnection::outp("<b>Bind</b>: LOB has been written to temp");
1009                                 }
1010                         } else {
1011                                 $this->_refLOBs[$numlob]['VAR'] = &$var;
1012                         }
1013                         $rez = $tmp;
1014                 } else {
1015                         if ($this->debug)
1016                                 ADOConnection::outp("<b>Bind</b>: name = $name");
1017
1018                         if ($type !== false) $rez = oci_bind_by_name($stmt[1],":".$name,$var,$size,$type);
1019                         else $rez = oci_bind_by_name($stmt[1],":".$name,$var,$size); // +1 byte for null terminator
1020                 }
1021
1022                 return $rez;
1023         }
1024
1025         function Param($name,$type='C')
1026         {
1027                 return ':'.$name;
1028         }
1029
1030         /*
1031         Usage:
1032                 $stmt = $db->Prepare('select * from table where id =:myid and group=:group');
1033                 $db->Parameter($stmt,$id,'myid');
1034                 $db->Parameter($stmt,$group,'group');
1035                 $db->Execute($stmt);
1036
1037                 @param $stmt Statement returned by Prepare() or PrepareSP().
1038                 @param $var PHP variable to bind to
1039                 @param $name Name of stored procedure variable name to bind to.
1040                 @param [$isOutput] Indicates direction of parameter 0/false=IN  1=OUT  2= IN/OUT. This is ignored in oci8.
1041                 @param [$maxLen] Holds an maximum length of the variable.
1042                 @param [$type] The data type of $var. Legal values depend on driver.
1043
1044                 See oci_bind_by_name documentation at php.net.
1045         */
1046         function Parameter(&$stmt,&$var,$name,$isOutput=false,$maxLen=4000,$type=false)
1047         {
1048                         if  ($this->debug) {
1049                                 $prefix = ($isOutput) ? 'Out' : 'In';
1050                                 $ztype = (empty($type)) ? 'false' : $type;
1051                                 ADOConnection::outp( "{$prefix}Parameter(\$stmt, \$php_var='$var', \$name='$name', \$maxLen=$maxLen, \$type=$ztype);");
1052                         }
1053                         return $this->Bind($stmt,$var,$maxLen,$type,$name,$isOutput);
1054         }
1055
1056         /*
1057         returns query ID if successful, otherwise false
1058         this version supports:
1059
1060            1. $db->execute('select * from table');
1061
1062            2. $db->prepare('insert into table (a,b,c) values (:0,:1,:2)');
1063                   $db->execute($prepared_statement, array(1,2,3));
1064
1065            3. $db->execute('insert into table (a,b,c) values (:a,:b,:c)',array('a'=>1,'b'=>2,'c'=>3));
1066
1067            4. $db->prepare('insert into table (a,b,c) values (:0,:1,:2)');
1068                   $db->bind($stmt,1); $db->bind($stmt,2); $db->bind($stmt,3);
1069                   $db->execute($stmt);
1070         */
1071         function _query($sql,$inputarr=false)
1072         {
1073                 if (is_array($sql)) { // is prepared sql
1074                         $stmt = $sql[1];
1075
1076                         // we try to bind to permanent array, so that oci_bind_by_name is persistent
1077                         // and carried out once only - note that max array element size is 4000 chars
1078                         if (is_array($inputarr)) {
1079                                 $bindpos = $sql[3];
1080                                 if (isset($this->_bind[$bindpos])) {
1081                                 // all tied up already
1082                                         $bindarr = $this->_bind[$bindpos];
1083                                 } else {
1084                                 // one statement to bind them all
1085                                         $bindarr = array();
1086                                         foreach($inputarr as $k => $v) {
1087                                                 $bindarr[$k] = $v;
1088                                                 oci_bind_by_name($stmt,":$k",$bindarr[$k],is_string($v) && strlen($v)>4000 ? -1 : 4000);
1089                                         }
1090                                         $this->_bind[$bindpos] = $bindarr;
1091                                 }
1092                         }
1093                 } else {
1094                         $stmt=oci_parse($this->_connectionID,$sql);
1095                 }
1096
1097                 $this->_stmt = $stmt;
1098                 if (!$stmt) return false;
1099
1100                 if (defined('ADODB_PREFETCH_ROWS')) @oci_set_prefetch($stmt,ADODB_PREFETCH_ROWS);
1101
1102                 if (is_array($inputarr)) {
1103                         foreach($inputarr as $k => $v) {
1104                                 if (is_array($v)) {
1105                                         if (sizeof($v) == 2) // suggested by g.giunta@libero.
1106                                                 oci_bind_by_name($stmt,":$k",$inputarr[$k][0],$v[1]);
1107                                         else
1108                                                 oci_bind_by_name($stmt,":$k",$inputarr[$k][0],$v[1],$v[2]);
1109
1110                                         if ($this->debug==99) {
1111                                                 if (is_object($v[0]))
1112                                                         echo "name=:$k",' len='.$v[1],' type='.$v[2],'<br>';
1113                                                 else
1114                                                         echo "name=:$k",' var='.$inputarr[$k][0],' len='.$v[1],' type='.$v[2],'<br>';
1115
1116                                         }
1117                                 } else {
1118                                         $len = -1;
1119                                         if ($v === ' ') $len = 1;
1120                                         if (isset($bindarr)) {  // is prepared sql, so no need to oci_bind_by_name again
1121                                                 $bindarr[$k] = $v;
1122                                         } else {                                // dynamic sql, so rebind every time
1123                                                 oci_bind_by_name($stmt,":$k",$inputarr[$k],$len);
1124                                         }
1125                                 }
1126                         }
1127                 }
1128
1129         $this->_errorMsg = false;
1130                 $this->_errorCode = false;
1131                 if (oci_execute($stmt,$this->_commit)) {
1132
1133                         if (count($this -> _refLOBs) > 0) {
1134
1135                                 foreach ($this -> _refLOBs as $key => $value) {
1136                                         if ($this -> _refLOBs[$key]['TYPE'] == true) {
1137                                                 $tmp = $this -> _refLOBs[$key]['LOB'] -> load();
1138                                                 if ($this -> debug) {
1139                                                         ADOConnection::outp("<b>OUT LOB</b>: LOB has been loaded. <br>");
1140                                                 }
1141                                                 //$_GLOBALS[$this -> _refLOBs[$key]['VAR']] = $tmp;
1142                                                 $this -> _refLOBs[$key]['VAR'] = $tmp;
1143                                         } else {
1144                         $this->_refLOBs[$key]['LOB']->save($this->_refLOBs[$key]['VAR']);
1145                                                 $this -> _refLOBs[$key]['LOB']->free();
1146                                                 unset($this -> _refLOBs[$key]);
1147                         if ($this->debug) {
1148                                                         ADOConnection::outp("<b>IN LOB</b>: LOB has been saved. <br>");
1149                                                 }
1150                     }
1151                                 }
1152                         }
1153
1154             switch (@oci_statement_type($stmt)) {
1155                 case "SELECT":
1156                                         return $stmt;
1157
1158                                 case 'DECLARE':
1159                 case "BEGIN":
1160                     if (is_array($sql) && !empty($sql[4])) {
1161                                                 $cursor = $sql[4];
1162                                                 if (is_resource($cursor)) {
1163                                                         $ok = oci_execute($cursor);
1164                                 return $cursor;
1165                                                 }
1166                                                 return $stmt;
1167                     } else {
1168                                                 if (is_resource($stmt)) {
1169                                                         oci_free_statement($stmt);
1170                                                         return true;
1171                                                 }
1172                         return $stmt;
1173                     }
1174                     break;
1175                 default :
1176
1177                     return true;
1178             }
1179                 }
1180                 return false;
1181         }
1182
1183         // From Oracle Whitepaper: PHP Scalability and High Availability
1184         function IsConnectionError($err)
1185         {
1186                 switch($err) {
1187                         case 378: /* buffer pool param incorrect */
1188                         case 602: /* core dump */
1189                         case 603: /* fatal error */
1190                         case 609: /* attach failed */
1191                         case 1012: /* not logged in */
1192                         case 1033: /* init or shutdown in progress */
1193                         case 1043: /* Oracle not available */
1194                         case 1089: /* immediate shutdown in progress */
1195                         case 1090: /* shutdown in progress */
1196                         case 1092: /* instance terminated */
1197                         case 3113: /* disconnect */
1198                         case 3114: /* not connected */
1199                         case 3122: /* closing window */
1200                         case 3135: /* lost contact */
1201                         case 12153: /* TNS: not connected */
1202                         case 27146: /* fatal or instance terminated */
1203                         case 28511: /* Lost RPC */
1204                         return true;
1205                 }
1206                 return false;
1207         }
1208
1209         // returns true or false
1210         function _close()
1211         {
1212                 if (!$this->_connectionID) return;
1213
1214                 if (!$this->autoCommit) oci_rollback($this->_connectionID);
1215                 if (count($this->_refLOBs) > 0) {
1216                         foreach ($this ->_refLOBs as $key => $value) {
1217                                 $this->_refLOBs[$key]['LOB']->free();
1218                                 unset($this->_refLOBs[$key]);
1219                         }
1220                 }
1221                 oci_close($this->_connectionID);
1222
1223                 $this->_stmt = false;
1224                 $this->_connectionID = false;
1225         }
1226
1227         function MetaPrimaryKeys($table, $owner=false,$internalKey=false)
1228         {
1229                 if ($internalKey) return array('ROWID');
1230
1231         // tested with oracle 8.1.7
1232                 $table = strtoupper($table);
1233                 if ($owner) {
1234                         $owner_clause = "AND ((a.OWNER = b.OWNER) AND (a.OWNER = UPPER('$owner')))";
1235                         $ptab = 'ALL_';
1236                 } else {
1237                         $owner_clause = '';
1238                         $ptab = 'USER_';
1239                 }
1240                 $sql = "
1241 SELECT /*+ RULE */ distinct b.column_name
1242    FROM {$ptab}CONSTRAINTS a
1243           , {$ptab}CONS_COLUMNS b
1244   WHERE ( UPPER(b.table_name) = ('$table'))
1245         AND (UPPER(a.table_name) = ('$table') and a.constraint_type = 'P')
1246         $owner_clause
1247         AND (a.constraint_name = b.constraint_name)";
1248
1249                 $rs = $this->Execute($sql);
1250                 if ($rs && !$rs->EOF) {
1251                         $arr = $rs->GetArray();
1252                         $a = array();
1253                         foreach($arr as $v) {
1254                                 $a[] = reset($v);
1255                         }
1256                         return $a;
1257                 }
1258                 else return false;
1259         }
1260
1261         // http://gis.mit.edu/classes/11.521/sqlnotes/referential_integrity.html
1262         function MetaForeignKeys($table, $owner=false)
1263         {
1264         global $ADODB_FETCH_MODE;
1265
1266                 $save = $ADODB_FETCH_MODE;
1267                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
1268                 $table = $this->qstr(strtoupper($table));
1269                 if (!$owner) {
1270                         $owner = $this->user;
1271                         $tabp = 'user_';
1272                 } else
1273                         $tabp = 'all_';
1274
1275                 $owner = ' and owner='.$this->qstr(strtoupper($owner));
1276
1277                 $sql =
1278 "select constraint_name,r_owner,r_constraint_name
1279         from {$tabp}constraints
1280         where constraint_type = 'R' and table_name = $table $owner";
1281
1282                 $constraints = $this->GetArray($sql);
1283                 $arr = false;
1284                 foreach($constraints as $constr) {
1285                         $cons = $this->qstr($constr[0]);
1286                         $rowner = $this->qstr($constr[1]);
1287                         $rcons = $this->qstr($constr[2]);
1288                         $cols = $this->GetArray("select column_name from {$tabp}cons_columns where constraint_name=$cons $owner order by position");
1289                         $tabcol = $this->GetArray("select table_name,column_name from {$tabp}cons_columns where owner=$rowner and constraint_name=$rcons order by position");
1290
1291                         if ($cols && $tabcol)
1292                                 for ($i=0, $max=sizeof($cols); $i < $max; $i++) {
1293                                         $arr[$tabcol[$i][0]] = $cols[$i][0].'='.$tabcol[$i][1];
1294                                 }
1295                 }
1296                 $ADODB_FETCH_MODE = $save;
1297
1298                 return $arr;
1299         }
1300
1301
1302         function CharMax()
1303         {
1304                 return 4000;
1305         }
1306
1307         function TextMax()
1308         {
1309                 return 4000;
1310         }
1311
1312         /**
1313          * Quotes a string.
1314          * An example is  $db->qstr("Don't bother",magic_quotes_runtime());
1315          *
1316          * @param s                     the string to quote
1317          * @param [magic_quotes]        if $s is GET/POST var, set to get_magic_quotes_gpc().
1318          *                              This undoes the stupidity of magic quotes for GPC.
1319          *
1320          * @return  quoted string to be sent back to database
1321          */
1322         function qstr($s,$magic_quotes=false)
1323         {
1324                 //$nofixquotes=false;
1325
1326                 if ($this->noNullStrings && strlen($s)==0)$s = ' ';
1327                 if (!$magic_quotes) {
1328                         if ($this->replaceQuote[0] == '\\'){
1329                                 $s = str_replace('\\','\\\\',$s);
1330                         }
1331                         return  "'".str_replace("'",$this->replaceQuote,$s)."'";
1332                 }
1333
1334                 // undo magic quotes for " unless sybase is on
1335                 if (!ini_get('magic_quotes_sybase')) {
1336                         $s = str_replace('\\"','"',$s);
1337                         $s = str_replace('\\\\','\\',$s);
1338                         return "'".str_replace("\\'",$this->replaceQuote,$s)."'";
1339                 } else {
1340                         return "'".$s."'";
1341                 }
1342         }
1343
1344 }
1345
1346 /*--------------------------------------------------------------------------------------
1347                  Class Name: Recordset
1348 --------------------------------------------------------------------------------------*/
1349
1350 class ADORecordset_oci8 extends ADORecordSet {
1351
1352         var $databaseType = 'oci8';
1353         var $bind=false;
1354         var $_fieldobjs;
1355
1356         //var $_arr = false;
1357
1358         function ADORecordset_oci8($queryID,$mode=false)
1359         {
1360                 if ($mode === false) {
1361                         global $ADODB_FETCH_MODE;
1362                         $mode = $ADODB_FETCH_MODE;
1363                 }
1364                 switch ($mode)
1365                 {
1366                 case ADODB_FETCH_ASSOC:$this->fetchMode = OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
1367                 case ADODB_FETCH_DEFAULT:
1368                 case ADODB_FETCH_BOTH:$this->fetchMode = OCI_NUM+OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
1369                 case ADODB_FETCH_NUM:
1370                 default:
1371                 $this->fetchMode = OCI_NUM+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
1372                 }
1373
1374                 $this->adodbFetchMode = $mode;
1375                 $this->_queryID = $queryID;
1376         }
1377
1378
1379         function Init()
1380         {
1381                 if ($this->_inited) return;
1382
1383                 $this->_inited = true;
1384                 if ($this->_queryID) {
1385
1386                         $this->_currentRow = 0;
1387                         @$this->_initrs();
1388                         if ($this->_numOfFields) $this->EOF = !$this->_fetch();
1389                         else $this->EOF = true;
1390
1391                         /*
1392                         // based on idea by Gaetano Giunta to detect unusual oracle errors
1393                         // see http://phplens.com/lens/lensforum/msgs.php?id=6771
1394                         $err = oci_error($this->_queryID);
1395                         if ($err && $this->connection->debug) ADOConnection::outp($err);
1396                         */
1397
1398                         if (!is_array($this->fields)) {
1399                                 $this->_numOfRows = 0;
1400                                 $this->fields = array();
1401                         }
1402                 } else {
1403                         $this->fields = array();
1404                         $this->_numOfRows = 0;
1405                         $this->_numOfFields = 0;
1406                         $this->EOF = true;
1407                 }
1408         }
1409
1410         function _initrs()
1411         {
1412                 $this->_numOfRows = -1;
1413                 $this->_numOfFields = oci_num_fields($this->_queryID);
1414                 if ($this->_numOfFields>0) {
1415                         $this->_fieldobjs = array();
1416                         $max = $this->_numOfFields;
1417                         for ($i=0;$i<$max; $i++) $this->_fieldobjs[] = $this->_FetchField($i);
1418                 }
1419         }
1420
1421           /*            Returns: an object containing field information.
1422                           Get column information in the Recordset object. fetchField() can be used in order to obtain information about
1423                           fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
1424                           fetchField() is retrieved.            */
1425
1426         function _FetchField($fieldOffset = -1)
1427         {
1428                 $fld = new ADOFieldObject;
1429                 $fieldOffset += 1;
1430                 $fld->name =oci_field_name($this->_queryID, $fieldOffset);
1431                 $fld->type = oci_field_type($this->_queryID, $fieldOffset);
1432                 $fld->max_length = oci_field_size($this->_queryID, $fieldOffset);
1433                 switch($fld->type) {
1434                 case 'NUMBER':
1435                         $p = oci_field_precision($this->_queryID, $fieldOffset);
1436                         $sc = oci_field_scale($this->_queryID, $fieldOffset);
1437                         if ($p != 0 && $sc == 0) $fld->type = 'INT';
1438                         $fld->scale = $p;
1439                         break;
1440
1441                 case 'CLOB':
1442                 case 'NCLOB':
1443                 case 'BLOB':
1444                         $fld->max_length = -1;
1445                         break;
1446                 }
1447                 return $fld;
1448         }
1449
1450         /* For some reason, oci_field_name fails when called after _initrs() so we cache it */
1451         function FetchField($fieldOffset = -1)
1452         {
1453                 return $this->_fieldobjs[$fieldOffset];
1454         }
1455
1456
1457         /*
1458         // 10% speedup to move MoveNext to child class
1459         function _MoveNext()
1460         {
1461         //global $ADODB_EXTENSION;if ($ADODB_EXTENSION) return @adodb_movenext($this);
1462
1463                 if ($this->EOF) return false;
1464
1465                 $this->_currentRow++;
1466                 if($this->fields = @oci_fetch_array($this->_queryID,$this->fetchMode))
1467                         return true;
1468                 $this->EOF = true;
1469
1470                 return false;
1471         }       */
1472
1473
1474         function MoveNext()
1475         {
1476                 if ($this->fields = @oci_fetch_array($this->_queryID,$this->fetchMode)) {
1477                         $this->_currentRow += 1;
1478                         return true;
1479                 }
1480                 if (!$this->EOF) {
1481                         $this->_currentRow += 1;
1482                         $this->EOF = true;
1483                 }
1484                 return false;
1485         }
1486
1487         /*
1488         # does not work as first record is retrieved in _initrs(), so is not included in GetArray()
1489         function GetArray($nRows = -1)
1490         {
1491         global $ADODB_OCI8_GETARRAY;
1492
1493                 if (true ||  !empty($ADODB_OCI8_GETARRAY)) {
1494                         # does not support $ADODB_ANSI_PADDING_OFF
1495
1496                         //OCI_RETURN_NULLS and OCI_RETURN_LOBS is set by oci_fetch_all
1497                         switch($this->adodbFetchMode) {
1498                         case ADODB_FETCH_NUM:
1499
1500                                 $ncols = @oci_fetch_all($this->_queryID, $results, 0, $nRows, oci_fetch_all_BY_ROW+OCI_NUM);
1501                                 $results = array_merge(array($this->fields),$results);
1502                                 return $results;
1503
1504                         case ADODB_FETCH_ASSOC:
1505                                 if (ADODB_ASSOC_CASE != 2 || $this->databaseType != 'oci8') break;
1506
1507                                 $ncols = @oci_fetch_all($this->_queryID, $assoc, 0, $nRows, oci_fetch_all_BY_ROW);
1508                                 $results = array_merge(array($this->fields),$assoc);
1509                                 return $results;
1510
1511                         default:
1512                                 break;
1513                         }
1514                 }
1515
1516                 $results = ADORecordSet::GetArray($nRows);
1517                 return $results;
1518
1519         } */
1520
1521         /* Optimize SelectLimit() by using oci_fetch() */
1522         function GetArrayLimit($nrows,$offset=-1)
1523         {
1524                 if ($offset <= 0) {
1525                         $arr = $this->GetArray($nrows);
1526                         return $arr;
1527                 }
1528                 $arr = array();
1529                 for ($i=1; $i < $offset; $i++)
1530                         if (!@oci_fetch($this->_queryID)) return $arr;
1531
1532                 if (!$this->fields = @oci_fetch_array($this->_queryID,$this->fetchMode)) return $arr;;
1533                 $results = array();
1534                 $cnt = 0;
1535                 while (!$this->EOF && $nrows != $cnt) {
1536                         $results[$cnt++] = $this->fields;
1537                         $this->MoveNext();
1538                 }
1539
1540                 return $results;
1541         }
1542
1543
1544         /* Use associative array to get fields array */
1545         function Fields($colname)
1546         {
1547                 if (!$this->bind) {
1548                         $this->bind = array();
1549                         for ($i=0; $i < $this->_numOfFields; $i++) {
1550                                 $o = $this->FetchField($i);
1551                                 $this->bind[strtoupper($o->name)] = $i;
1552                         }
1553                 }
1554
1555                  return $this->fields[$this->bind[strtoupper($colname)]];
1556         }
1557
1558
1559
1560         function _seek($row)
1561         {
1562                 return false;
1563         }
1564
1565         function _fetch()
1566         {
1567                 return $this->fields = @oci_fetch_array($this->_queryID,$this->fetchMode);
1568         }
1569
1570         /*              close() only needs to be called if you are worried about using too much memory while your script
1571                         is running. All associated result memory for the specified result identifier will automatically be freed.               */
1572
1573         function _close()
1574         {
1575                 if ($this->connection->_stmt === $this->_queryID) $this->connection->_stmt = false;
1576                 if (!empty($this->_refcursor)) {
1577                         oci_free_cursor($this->_refcursor);
1578                         $this->_refcursor = false;
1579                 }
1580                 @oci_free_statement($this->_queryID);
1581                 $this->_queryID = false;
1582
1583         }
1584
1585         function MetaType($t,$len=-1)
1586         {
1587                 if (is_object($t)) {
1588                         $fieldobj = $t;
1589                         $t = $fieldobj->type;
1590                         $len = $fieldobj->max_length;
1591                 }
1592                 switch (strtoupper($t)) {
1593                 case 'VARCHAR':
1594                 case 'VARCHAR2':
1595                 case 'CHAR':
1596                 case 'VARBINARY':
1597                 case 'BINARY':
1598                 case 'NCHAR':
1599                 case 'NVARCHAR':
1600                 case 'NVARCHAR2':
1601                                  if ($len <= $this->blobSize) return 'C';
1602
1603                 case 'NCLOB':
1604                 case 'LONG':
1605                 case 'LONG VARCHAR':
1606                 case 'CLOB':
1607                 return 'X';
1608
1609                 case 'LONG RAW':
1610                 case 'LONG VARBINARY':
1611                 case 'BLOB':
1612                         return 'B';
1613
1614                 case 'DATE':
1615                         return  ($this->connection->datetime) ? 'T' : 'D';
1616
1617
1618                 case 'TIMESTAMP': return 'T';
1619
1620                 case 'INT':
1621                 case 'SMALLINT':
1622                 case 'INTEGER':
1623                         return 'I';
1624
1625                 default: return 'N';
1626                 }
1627         }
1628 }
1629
1630 class ADORecordSet_ext_oci8 extends ADORecordSet_oci8 {
1631         function ADORecordSet_ext_oci8($queryID,$mode=false)
1632         {
1633                 if ($mode === false) {
1634                         global $ADODB_FETCH_MODE;
1635                         $mode = $ADODB_FETCH_MODE;
1636                 }
1637                 switch ($mode)
1638                 {
1639                 case ADODB_FETCH_ASSOC:$this->fetchMode = OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
1640                 case ADODB_FETCH_DEFAULT:
1641                 case ADODB_FETCH_BOTH:$this->fetchMode = OCI_NUM+OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
1642                 case ADODB_FETCH_NUM:
1643                 default: $this->fetchMode = OCI_NUM+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
1644                 }
1645                 $this->adodbFetchMode = $mode;
1646                 $this->_queryID = $queryID;
1647         }
1648
1649         function MoveNext()
1650         {
1651                 return adodb_movenext($this);
1652         }
1653 }
1654 ?>