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

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