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

   1 <?php
   2 /*
   3
   4   version V4.22 15 Apr 2004 (c) 2000-2004 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://php.weblogs.com/
  11
  12   Code contributed by George Fourlanos <fou@infomap.gr>
  13
  14   13 Nov 2000 jlim - removed all ora_* references.
  15 */
  16
  17 /*
  18 NLS_Date_Format
  19 Allows you to use a date format other than the Oracle Lite default. When a literal
  20 character string appears where a date value is expected, the Oracle Lite database
  21 tests the string to see if it matches the formats of Oracle, SQL-92, or the value
  22 specified for this parameter in the POLITE.INI file. Setting this parameter also
  23 defines the default format used in the TO_CHAR or TO_DATE functions when no
  24 other format string is supplied.
  25
  26 For Oracle the default is dd-mon-yy or dd-mon-yyyy, and for SQL-92 the default is
  27 yy-mm-dd or yyyy-mm-dd.
  28
  29 Using 'RR' in the format forces two-digit years less than or equal to 49 to be
  30 interpreted as years in the 21st century (2000\962049), and years over 50 as years in
  31 the 20th century (1950\961999). Setting the RR format as the default for all two-digit
  32 year entries allows you to become year-2000 compliant. For example:
  33 NLS_DATE_FORMAT='RR-MM-DD'
  34
  35 You can also modify the date format using the ALTER SESSION command.
  36 */
  37
  38 class ADODB_oci8 extends ADOConnection {
  39         var $databaseType = 'oci8';
  40         var $dataProvider = 'oci8';
  41         var $replaceQuote = "''"; // string to use to replace quotes
  42         var $concat_operator='||';
  43         var $sysDate = "TRUNC(SYSDATE)";
  44         var $sysTimeStamp = 'SYSDATE';
  45         var $metaDatabasesSQL = "SELECT USERNAME FROM ALL_USERS WHERE USERNAME NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN') ORDER BY 1";
  46         var $_stmt;
  47         var $_commit = OCI_COMMIT_ON_SUCCESS;
  48         var $_initdate = true; // init date to YYYY-MM-DD
  49         var $metaTablesSQL = "select table_name,table_type from cat where table_type in ('TABLE','VIEW')";
  50         var $metaColumnsSQL = "select cname,coltype,width, SCALE, PRECISION, NULLS, DEFAULTVAL from col where tname='%s' order by colno"; //changed by smondino@users.sourceforge. net
  51         var $_bindInputArray = true;
  52         var $hasGenID = true;
  53         var $_genIDSQL = "SELECT (%s.nextval) FROM DUAL";
  54         var $_genSeqSQL = "CREATE SEQUENCE %s START WITH %s";
  55         var $_dropSeqSQL = "DROP SEQUENCE %s";
  56         var $hasAffectedRows = true;
  57         var $random = "abs(mod(DBMS_RANDOM.RANDOM,10000001)/10000000)";
  58         var $noNullStrings = false;
  59         var $connectSID = false;
  60         var $_bind = false;
  61         var $_hasOCIFetchStatement = false;
  62         var $_getarray = false; // currently not working
  63         var $leftOuter = '';  // oracle wierdness, $col = $value (+) for LEFT OUTER, $col (+)= $value for RIGHT OUTER
  64         var $session_sharing_force_blob = false; // alter session on updateblob if set to true
  65         var $firstrows = true; // enable first rows optimization on SelectLimit()
  66         var $selectOffsetAlg1 = 100; // when to use 1st algorithm of selectlimit.
  67         var $NLS_DATE_FORMAT = 'YYYY-MM-DD';  // To include time, use 'RRRR-MM-DD HH24:MI:SS'
  68         var $useDBDateFormatForTextInput=false;
  69         var $datetime = false; // MetaType('DATE') returns 'D' (datetime==false) or 'T' (datetime == true)
  70
  71         // var $ansiOuter = true; // if oracle9
  72
  73         function ADODB_oci8()
  74         {
  75                 $this->_hasOCIFetchStatement = ADODB_PHPVER >= 0x4200;
  76         }
  77
  78         /*  Function &MetaColumns($table) added by smondino@users.sourceforge.net*/
  79         function &MetaColumns($table)
  80         {
  81         global $ADODB_FETCH_MODE;
  82
  83                 $save = $ADODB_FETCH_MODE;
  84                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
  85                 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
  86
  87                 $rs = $this->Execute(sprintf($this->metaColumnsSQL,strtoupper($table)));
  88
  89                 if (isset($savem)) $this->SetFetchMode($savem);
  90                 $ADODB_FETCH_MODE = $save;
  91                 if (!$rs) return false;
  92                 $retarr = array();
  93                 while (!$rs->EOF) { //print_r($rs->fields);
  94                         $fld = new ADOFieldObject();
  95                         $fld->name = $rs->fields[0];
  96                         $fld->type = $rs->fields[1];
  97                         $fld->max_length = $rs->fields[2];
  98                         $fld->scale = $rs->fields[3];
  99                         if ($rs->fields[1] == 'NUMBER' && $rs->fields[3] == 0) {
 100                                 $fld->type ='INT';
 101                         $fld->max_length = $rs->fields[4];
 102                 }
 103                         $fld->not_null = (strncmp($rs->fields[5], 'NOT',3) === 0);
 104                         $fld->binary = (strpos($fld->type,'BLOB') !== false);
 105                         $fld->default_value = $rs->fields[6];
 106
 107                         if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) $retarr[] = $fld;
 108                         else $retarr[strtoupper($fld->name)] = $fld;
 109                         $rs->MoveNext();
 110                 }
 111                 $rs->Close();
 112                 return $retarr;
 113         }
 114
 115         function Time()
 116         {
 117                 $rs =& $this->Execute("select TO_CHAR($this->sysTimeStamp,'YYYY-MM-DD HH24:MI:SS') from dual");
 118                 if ($rs && !$rs->EOF) return $this->UnixTimeStamp(reset($rs->fields));
 119
 120                 return false;
 121         }
 122
 123 /*
 124
 125   Multiple modes of connection are supported:
 126
 127   a. Local Database
 128     $conn->Connect(false,'scott','tiger');
 129
 130   b. From tnsnames.ora
 131     $conn->Connect(false,'scott','tiger',$tnsname);
 132     $conn->Connect($tnsname,'scott','tiger');
 133
 134   c. Server + service name
 135     $conn->Connect($serveraddress,'scott,'tiger',$service_name);
 136
 137   d. Server + SID
 138         $conn->connectSID = true;
 139         $conn->Connect($serveraddress,'scott,'tiger',$SID);
 140
 141
 142 Example TNSName:
 143 ---------------
 144 NATSOFT.DOMAIN =
 145   (DESCRIPTION =
 146         (ADDRESS_LIST =
 147           (ADDRESS = (PROTOCOL = TCP)(HOST = kermit)(PORT = 1523))
 148         )
 149         (CONNECT_DATA =
 150           (SERVICE_NAME = natsoft.domain)
 151         )
 152   )
 153
 154   There are 3 connection modes, 0 = non-persistent, 1 = persistent, 2 = force new connection
 155
 156 */
 157         function _connect($argHostname, $argUsername, $argPassword, $argDatabasename,$mode=0)
 158         {
 159                 if (!function_exists('OCIPLogon')) return false;
 160
 161
 162         $this->_errorMsg = false;
 163                 $this->_errorCode = false;
 164
 165                 if($argHostname) { // added by Jorma Tuomainen <jorma.tuomainen@ppoy.fi>
 166                         if (empty($argDatabasename)) $argDatabasename = $argHostname;
 167                         else {
 168                                 if(strpos($argHostname,":")) {
 169                                         $argHostinfo=explode(":",$argHostname);
 170                                         $argHostname=$argHostinfo[0];
 171                                         $argHostport=$argHostinfo[1];
 172                                 } else {
 173                                         $argHostport="1521";
 174                                 }
 175
 176                                 if ($this->connectSID) {
 177                                         $argDatabasename="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=".$argHostname
 178                                         .")(PORT=$argHostport))(CONNECT_DATA=(SID=$argDatabasename)))";
 179                                 } else
 180                                         $argDatabasename="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=".$argHostname
 181                                         .")(PORT=$argHostport))(CONNECT_DATA=(SERVICE_NAME=$argDatabasename)))";
 182                         }
 183                 }
 184
 185                 //if ($argHostname) print "<p>Connect: 1st argument should be left blank for $this->databaseType</p>";
 186                 if ($mode==1) {
 187                         $this->_connectionID = OCIPLogon($argUsername,$argPassword, $argDatabasename);
 188                         if ($this->_connectionID && $this->autoRollback)  OCIrollback($this->_connectionID);
 189                 } else if ($mode==2) {
 190                         $this->_connectionID = OCINLogon($argUsername,$argPassword, $argDatabasename);
 191                 } else {
 192                         $this->_connectionID = OCILogon($argUsername,$argPassword, $argDatabasename);
 193                 }
 194                 if ($this->_connectionID === false) return false;
 195                 if ($this->_initdate) {
 196                         $this->Execute("ALTER SESSION SET NLS_DATE_FORMAT='".$this->NLS_DATE_FORMAT."'");
 197                 }
 198
 199                 // looks like:
 200                 // Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production
 201                 // $vers = OCIServerVersion($this->_connectionID);
 202                 // if (strpos($vers,'8i') !== false) $this->ansiOuter = true;
 203                 return true;
 204         }
 205
 206         function ServerInfo()
 207         {
 208                 $arr['compat'] = $this->GetOne('select value from sys.database_compatible_level');
 209                 $arr['description'] = @OCIServerVersion($this->_connectionID);
 210                 $arr['version'] = ADOConnection::_findvers($arr['description']);
 211                 return $arr;
 212         }
 213                 // returns true or false
 214         function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
 215         {
 216                 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename,1);
 217         }
 218
 219
 220
 221         // returns true or false
 222         function _nconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
 223         {
 224                 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename,2);
 225         }
 226
 227         function _affectedrows()
 228         {
 229                 if (is_resource($this->_stmt)) return @OCIRowCount($this->_stmt);
 230                 return 0;
 231         }
 232
 233         function IfNull( $field, $ifNull )
 234         {
 235                 return " NVL($field, $ifNull) "; // if Oracle
 236         }
 237
 238         // format and return date string in database date format
 239         function DBDate($d)
 240         {
 241                 if (empty($d) && $d !== 0) return 'null';
 242
 243                 if (is_string($d)) $d = ADORecordSet::UnixDate($d);
 244                 return "TO_DATE(".adodb_date($this->fmtDate,$d).",'".$this->NLS_DATE_FORMAT."')";
 245         }
 246
 247
 248         // format and return date string in database timestamp format
 249         function DBTimeStamp($ts)
 250         {
 251                 if (empty($ts) && $ts !== 0) return 'null';
 252                 if (is_string($ts)) $ts = ADORecordSet::UnixTimeStamp($ts);
 253                 return 'TO_DATE('.adodb_date($this->fmtTimeStamp,$ts).",'RRRR-MM-DD, HH:MI:SS AM')";
 254         }
 255
 256         function RowLock($tables,$where)
 257         {
 258                 if ($this->autoCommit) $this->BeginTrans();
 259                 return $this->GetOne("select 1 as ignore from $tables where $where for update");
 260         }
 261
 262         function &MetaTables($ttype=false,$showSchema=false,$mask=false)
 263         {
 264                 if ($mask) {
 265                         $save = $this->metaTablesSQL;
 266                         $mask = $this->qstr(strtoupper($mask));
 267                         $this->metaTablesSQL .= " AND table_name like $mask";
 268                 }
 269                 $ret =& ADOConnection::MetaTables($ttype,$showSchema);
 270
 271                 if ($mask) {
 272                         $this->metaTablesSQL = $save;
 273                 }
 274                 return $ret;
 275         }
 276
 277         function BeginTrans()
 278         {
 279                 if ($this->transOff) return true;
 280                 $this->transCnt += 1;
 281                 $this->autoCommit = false;
 282                 $this->_commit = OCI_DEFAULT;
 283                 return true;
 284         }
 285
 286         function CommitTrans($ok=true)
 287         {
 288                 if ($this->transOff) return true;
 289                 if (!$ok) return $this->RollbackTrans();
 290
 291                 if ($this->transCnt) $this->transCnt -= 1;
 292                 $ret = OCIcommit($this->_connectionID);
 293                 $this->_commit = OCI_COMMIT_ON_SUCCESS;
 294                 $this->autoCommit = true;
 295                 return $ret;
 296         }
 297
 298         function RollbackTrans()
 299         {
 300                 if ($this->transOff) return true;
 301                 if ($this->transCnt) $this->transCnt -= 1;
 302                 $ret = OCIrollback($this->_connectionID);
 303                 $this->_commit = OCI_COMMIT_ON_SUCCESS;
 304                 $this->autoCommit = true;
 305                 return $ret;
 306         }
 307
 308
 309         function SelectDB($dbName)
 310         {
 311                 return false;
 312         }
 313
 314         function ErrorMsg()
 315         {
 316                 if ($this->_errorMsg !== false) return $this->_errorMsg;
 317
 318                 if (is_resource($this->_stmt)) $arr = @OCIerror($this->_stmt);
 319                 if (empty($arr)) {
 320                         $arr = @OCIerror($this->_connectionID);
 321                         if ($arr === false) $arr = @OCIError();
 322                         if ($arr === false) return '';
 323                 }
 324                 $this->_errorMsg = $arr['message'];
 325                 $this->_errorCode = $arr['code'];
 326                 return $this->_errorMsg;
 327         }
 328
 329         function ErrorNo()
 330         {
 331                 if ($this->_errorCode !== false) return $this->_errorCode;
 332
 333                 if (is_resource($this->_stmt)) $arr = @OCIError($this->_stmt);
 334                 if (empty($arr)) {
 335                         $arr = @OCIError($this->_connectionID);
 336                         if ($arr == false) $arr = @OCIError();
 337                         if ($arr == false) return '';
 338                 }
 339
 340                 $this->_errorMsg = $arr['message'];
 341                 $this->_errorCode = $arr['code'];
 342
 343                 return $arr['code'];
 344         }
 345
 346         // Format date column in sql string given an input format that understands Y M D
 347         function SQLDate($fmt, $col=false)
 348         {
 349                 if (!$col) $col = $this->sysTimeStamp;
 350                 $s = 'TO_CHAR('.$col.",'";
 351
 352                 $len = strlen($fmt);
 353                 for ($i=0; $i < $len; $i++) {
 354                         $ch = $fmt[$i];
 355                         switch($ch) {
 356                         case 'Y':
 357                         case 'y':
 358                                 $s .= 'YYYY';
 359                                 break;
 360                         case 'Q':
 361                         case 'q':
 362                                 $s .= 'Q';
 363                                 break;
 364
 365                         case 'M':
 366                                 $s .= 'Mon';
 367                                 break;
 368
 369                         case 'm':
 370                                 $s .= 'MM';
 371                                 break;
 372                         case 'D':
 373                         case 'd':
 374                                 $s .= 'DD';
 375                                 break;
 376
 377                         case 'H':
 378                                 $s.= 'HH24';
 379                                 break;
 380
 381                         case 'h':
 382                                 $s .= 'HH';
 383                                 break;
 384
 385                         case 'i':
 386                                 $s .= 'MI';
 387                                 break;
 388
 389                         case 's':
 390                                 $s .= 'SS';
 391                                 break;
 392
 393                         case 'a':
 394                         case 'A':
 395                                 $s .= 'AM';
 396                                 break;
 397
 398                         default:
 399                         // handle escape characters...
 400                                 if ($ch == '\\') {
 401                                         $i++;
 402                                         $ch = substr($fmt,$i,1);
 403                                 }
 404                                 if (strpos('-/.:;, ',$ch) !== false) $s .= $ch;
 405                                 else $s .= '"'.$ch.'"';
 406
 407                         }
 408                 }
 409                 return $s. "')";
 410         }
 411
 412
 413         /*
 414         This algorithm makes use of
 415
 416         a. FIRST_ROWS hint
 417         The FIRST_ROWS hint explicitly chooses the approach to optimize response time,
 418         that is, minimum resource usage to return the first row. Results will be returned
 419         as soon as they are identified.
 420
 421         b. Uses rownum tricks to obtain only the required rows from a given offset.
 422          As this uses complicated sql statements, we only use this if the $offset >= 100.
 423          This idea by Tomas V V Cox.
 424
 425          This implementation does not appear to work with oracle 8.0.5 or earlier. Comment
 426          out this function then, and the slower SelectLimit() in the base class will be used.
 427         */
 428         function &SelectLimit($sql,$nrows=-1,$offset=-1, $inputarr=false,$secs2cache=0)
 429         {
 430                 // seems that oracle only supports 1 hint comment in 8i
 431                 if ($this->firstrows) {
 432                         if (strpos($sql,'/*+') !== false)
 433                                 $sql = str_replace('/*+ ','/*+FIRST_ROWS ',$sql);
 434                         else
 435                                 $sql = preg_replace('/^[ \t\n]*select/i','SELECT /*+FIRST_ROWS*/',$sql);
 436                 }
 437
 438                 if ($offset < $this->selectOffsetAlg1) {
 439                         if ($nrows > 0) {
 440                                 if ($offset > 0) $nrows += $offset;
 441                                 //$inputarr['adodb_rownum'] = $nrows;
 442                                 if ($this->databaseType == 'oci8po') {
 443                                         $sql = "select * from ($sql) where rownum <= ?";
 444                                 } else {
 445                                         $sql = "select * from ($sql) where rownum <= :adodb_offset";
 446                                 }
 447                                 $inputarr['adodb_offset'] = $nrows;
 448                                 $nrows = -1;
 449                         }
 450                         // note that $nrows = 0 still has to work ==> no rows returned
 451
 452                         $rs =& ADOConnection::SelectLimit($sql,$nrows,$offset,$inputarr,$secs2cache);
 453                         return $rs;
 454
 455                 } else {
 456                          // Algorithm by Tomas V V Cox, from PEAR DB oci8.php
 457
 458                          // Let Oracle return the name of the columns
 459                          $q_fields = "SELECT * FROM ($sql) WHERE NULL = NULL";
 460                          if (!$stmt = OCIParse($this->_connectionID, $q_fields)) {
 461                                  return false;
 462                          }
 463
 464                          if (is_array($inputarr)) {
 465                                 foreach($inputarr as $k => $v) {
 466                                         if (is_array($v)) {
 467                                                 if (sizeof($v) == 2) // suggested by g.giunta@libero.
 468                                                         OCIBindByName($stmt,":$k",$inputarr[$k][0],$v[1]);
 469                                                 else
 470                                                         OCIBindByName($stmt,":$k",$inputarr[$k][0],$v[1],$v[2]);
 471                                         } else {
 472                                                 $len = -1;
 473                                                 if ($v === ' ') $len = 1;
 474                                                 if (isset($bindarr)) {  // is prepared sql, so no need to ocibindbyname again
 475                                                         $bindarr[$k] = $v;
 476                                                 } else {                                // dynamic sql, so rebind every time
 477                                                         OCIBindByName($stmt,":$k",$inputarr[$k],$len);
 478                                                 }
 479                                         }
 480                                 }
 481                         }
 482
 483                          if (!OCIExecute($stmt, OCI_DEFAULT)) {
 484                                  OCIFreeStatement($stmt);
 485                                  return false;
 486                          }
 487
 488                          $ncols = OCINumCols($stmt);
 489                          for ( $i = 1; $i <= $ncols; $i++ ) {
 490                                  $cols[] = '"'.OCIColumnName($stmt, $i).'"';
 491                          }
 492                          $result = false;
 493
 494                          OCIFreeStatement($stmt);
 495                          $fields = implode(',', $cols);
 496                          $nrows += $offset;
 497                          $offset += 1; // in Oracle rownum starts at 1
 498
 499                         if ($this->databaseType == 'oci8po') {
 500                                          $sql = "SELECT $fields FROM".
 501                                           "(SELECT rownum as adodb_rownum, $fields FROM".
 502                                           " ($sql) WHERE rownum <= ?".
 503                                           ") WHERE adodb_rownum >= ?";
 504                                 } else {
 505                                          $sql = "SELECT $fields FROM".
 506                                           "(SELECT rownum as adodb_rownum, $fields FROM".
 507                                           " ($sql) WHERE rownum <= :adodb_nrows".
 508                                           ") WHERE adodb_rownum >= :adodb_offset";
 509                                 }
 510                                 $inputarr['adodb_nrows'] = $nrows;
 511                                 $inputarr['adodb_offset'] = $offset;
 512
 513                         if ($secs2cache>0) $rs =& $this->CacheExecute($secs2cache, $sql,$inputarr);
 514                         else $rs =& $this->Execute($sql,$inputarr);
 515                         return $rs;
 516                 }
 517
 518         }
 519
 520         /**
 521         * Usage:
 522         * Store BLOBs and CLOBs
 523         *
 524         * Example: to store $var in a blob
 525         *
 526         *       $conn->Execute('insert into TABLE (id,ablob) values(12,empty_blob())');
 527         *       $conn->UpdateBlob('TABLE', 'ablob', $varHoldingBlob, 'ID=12', 'BLOB');
 528         *
 529         *       $blobtype supports 'BLOB' and 'CLOB', but you need to change to 'empty_clob()'.
 530         *
 531         *  to get length of LOB:
 532         *       select DBMS_LOB.GETLENGTH(ablob) from TABLE
 533         *
 534         * If you are using CURSOR_SHARING = force, it appears this will case a segfault
 535         * under oracle 8.1.7.0. Run:
 536         *        $db->Execute('ALTER SESSION SET CURSOR_SHARING=EXACT');
 537         * before UpdateBlob() then...
 538         */
 539
 540         function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
 541         {
 542
 543                 //if (strlen($val) < 4000) return $this->Execute("UPDATE $table SET $column=:blob WHERE $where",array('blob'=>$val)) != false;
 544
 545                 switch(strtoupper($blobtype)) {
 546                 default: ADOConnection::outp("<b>UpdateBlob</b>: Unknown blobtype=$blobtype"); return false;
 547                 case 'BLOB': $type = OCI_B_BLOB; break;
 548                 case 'CLOB': $type = OCI_B_CLOB; break;
 549                 }
 550
 551                 if ($this->databaseType == 'oci8po')
 552                         $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO ?";
 553                 else
 554                         $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO :blob";
 555
 556                 $desc = OCINewDescriptor($this->_connectionID, OCI_D_LOB);
 557                 $arr['blob'] = array($desc,-1,$type);
 558                 if ($this->session_sharing_force_blob) $this->Execute('ALTER SESSION SET CURSOR_SHARING=EXACT');
 559                 $commit = $this->autoCommit;
 560                 if ($commit) $this->BeginTrans();
 561                 $rs = ADODB_oci8::Execute($sql,$arr);
 562                 if ($rez = !empty($rs)) $desc->save($val);
 563                 $desc->free();
 564                 if ($commit) $this->CommitTrans();
 565                 if ($this->session_sharing_force_blob) $this->Execute('ALTER SESSION SET CURSOR_SHARING=FORCE');
 566
 567                 if ($rez) $rs->Close();
 568                 return $rez;
 569         }
 570
 571         /**
 572         * Usage:  store file pointed to by $var in a blob
 573         */
 574         function UpdateBlobFile($table,$column,$val,$where,$blobtype='BLOB')
 575         {
 576                 switch(strtoupper($blobtype)) {
 577                 default: ADOConnection::outp( "<b>UpdateBlob</b>: Unknown blobtype=$blobtype"); return false;
 578                 case 'BLOB': $type = OCI_B_BLOB; break;
 579                 case 'CLOB': $type = OCI_B_CLOB; break;
 580                 }
 581
 582                 if ($this->databaseType == 'oci8po')
 583                         $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO ?";
 584                 else
 585                         $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO :blob";
 586
 587                 $desc = OCINewDescriptor($this->_connectionID, OCI_D_LOB);
 588                 $arr['blob'] = array($desc,-1,$type);
 589
 590                 $this->BeginTrans();
 591                 $rs = ADODB_oci8::Execute($sql,$arr);
 592                 if ($rez = !empty($rs)) $desc->savefile($val);
 593                 $desc->free();
 594                 $this->CommitTrans();
 595
 596                 if ($rez) $rs->Close();
 597                 return $rez;
 598         }
 599
 600
 601         /*
 602                 Example of usage:
 603
 604                 $stmt = $this->Prepare('insert into emp (empno, ename) values (:empno, :ename)');
 605         */
 606         function Prepare($sql,$cursor=false)
 607         {
 608         static $BINDNUM = 0;
 609
 610                 $stmt = OCIParse($this->_connectionID,$sql);
 611
 612                 if (!$stmt) return false;
 613
 614                 $BINDNUM += 1;
 615
 616                 if (@OCIStatementType($stmt) == 'BEGIN') {
 617                         return array($sql,$stmt,0,$BINDNUM, ($cursor) ? OCINewCursor($this->_connectionID) : false);
 618                 }
 619
 620                 return array($sql,$stmt,0,$BINDNUM);
 621         }
 622
 623         /*
 624                 Call an oracle stored procedure and return a cursor variable.
 625                 Convert the cursor variable into a recordset.
 626                 Concept by Robert Tuttle robert@ud.com
 627
 628                 Example:
 629                         Note: we return a cursor variable in :RS2
 630                         $rs = $db->ExecuteCursor("BEGIN adodb.open_tab(:RS2); END;",'RS2');
 631
 632                         $rs = $db->ExecuteCursor(
 633                                 "BEGIN :RS2 = adodb.getdata(:VAR1); END;",
 634                                 'RS2',
 635                                 array('VAR1' => 'Mr Bean'));
 636
 637         */
 638         function &ExecuteCursor($sql,$cursorName='rs',$params=false)
 639         {
 640                 $stmt = ADODB_oci8::Prepare($sql,true); # true to allocate OCINewCursor
 641
 642                 if (is_array($stmt) && sizeof($stmt) >= 5) {
 643                         $this->Parameter($stmt, $ignoreCur, $cursorName, false, -1, OCI_B_CURSOR);
 644                         if ($params) {
 645                                 foreach($params as $k => $v) {
 646                                         $this->Parameter($stmt,$params[$k], $k);
 647                                 }
 648                         }
 649                 }
 650                 return $this->Execute($stmt);
 651         }
 652
 653         /*
 654                 Bind a variable -- very, very fast for executing repeated statements in oracle.
 655                 Better than using
 656                         for ($i = 0; $i < $max; $i++) {
 657                                 $p1 = ?; $p2 = ?; $p3 = ?;
 658                                 $this->Execute("insert into table (col0, col1, col2) values (:0, :1, :2)",
 659                                         array($p1,$p2,$p3));
 660                         }
 661
 662                 Usage:
 663                         $stmt = $DB->Prepare("insert into table (col0, col1, col2) values (:0, :1, :2)");
 664                         $DB->Bind($stmt, $p1);
 665                         $DB->Bind($stmt, $p2);
 666                         $DB->Bind($stmt, $p3);
 667                         for ($i = 0; $i < $max; $i++) {
 668                                 $p1 = ?; $p2 = ?; $p3 = ?;
 669                                 $DB->Execute($stmt);
 670                         }
 671
 672                 Some timings:
 673                         ** Test table has 3 cols, and 1 index. Test to insert 1000 records
 674                         Time 0.6081s (1644.60 inserts/sec) with direct OCIParse/OCIExecute
 675                         Time 0.6341s (1577.16 inserts/sec) with ADOdb Prepare/Bind/Execute
 676                         Time 1.5533s ( 643.77 inserts/sec) with pure SQL using Execute
 677
 678                 Now if PHP only had batch/bulk updating like Java or PL/SQL...
 679
 680                 Note that the order of parameters differs from OCIBindByName,
 681                 because we default the names to :0, :1, :2
 682         */
 683         function Bind(&$stmt,&$var,$size=4000,$type=false,$name=false)
 684         {
 685                 if (!is_array($stmt)) return false;
 686
 687         if (($type == OCI_B_CURSOR) && sizeof($stmt) >= 5) {
 688             return OCIBindByName($stmt[1],":".$name,$stmt[4],$size,$type);
 689         }
 690
 691                 if ($name == false) {
 692                         if ($type !== false) $rez = OCIBindByName($stmt[1],":".$name,$var,$size,$type);
 693                         else $rez = OCIBindByName($stmt[1],":".$stmt[2],$var,$size); // +1 byte for null terminator
 694                         $stmt[2] += 1;
 695                 } else if ($type == OCI_B_BLOB){
 696             //we have to create a new Descriptor here
 697             $_blob = OCINewDescriptor($this->_connectionID, OCI_D_LOB);
 698             $rez = OCIBindByName($stmt[1], ":".$name, &$_blob, -1, OCI_B_BLOB);
 699             $rez = $_blob;
 700                 } else {
 701                         if ($type !== false) $rez = OCIBindByName($stmt[1],":".$name,$var,$size,$type);
 702                         else $rez = OCIBindByName($stmt[1],":".$name,$var,$size); // +1 byte for null terminator
 703                 }
 704
 705                 return $rez;
 706         }
 707
 708         function Param($name)
 709         {
 710                 return ':'.$name;
 711         }
 712
 713         /*
 714         Usage:
 715                 $stmt = $db->Prepare('select * from table where id =:myid and group=:group');
 716                 $db->Parameter($stmt,$id,'myid');
 717                 $db->Parameter($stmt,$group,'group');
 718                 $db->Execute($stmt);
 719
 720                 @param $stmt Statement returned by Prepare() or PrepareSP().
 721                 @param $var PHP variable to bind to
 722                 @param $name Name of stored procedure variable name to bind to.
 723                 @param [$isOutput] Indicates direction of parameter 0/false=IN  1=OUT  2= IN/OUT. This is ignored in oci8.
 724                 @param [$maxLen] Holds an maximum length of the variable.
 725                 @param [$type] The data type of $var. Legal values depend on driver.
 726
 727                 See OCIBindByName documentation at php.net.
 728         */
 729         function Parameter(&$stmt,&$var,$name,$isOutput=false,$maxLen=4000,$type=false)
 730         {
 731                         if  ($this->debug) {
 732                                 $prefix = ($isOutput) ? 'Out' : 'In';
 733                                 $ztype = (empty($type)) ? 'false' : $type;
 734                                 ADOConnection::outp( "{$prefix}Parameter(\$stmt, \$php_var='$var', \$name='$name', \$maxLen=$maxLen, \$type=$ztype);");
 735                         }
 736                         return $this->Bind($stmt,$var,$maxLen,$type,$name);
 737         }
 738
 739         /*
 740         returns query ID if successful, otherwise false
 741         this version supports:
 742
 743            1. $db->execute('select * from table');
 744
 745            2. $db->prepare('insert into table (a,b,c) values (:0,:1,:2)');
 746                   $db->execute($prepared_statement, array(1,2,3));
 747
 748            3. $db->execute('insert into table (a,b,c) values (:a,:b,:c)',array('a'=>1,'b'=>2,'c'=>3));
 749
 750            4. $db->prepare('insert into table (a,b,c) values (:0,:1,:2)');
 751                   $db->$bind($stmt,1); $db->bind($stmt,2); $db->bind($stmt,3);
 752                   $db->execute($stmt);
 753         */
 754         function _query($sql,$inputarr)
 755         {
 756
 757                 if (is_array($sql)) { // is prepared sql
 758                         $stmt = $sql[1];
 759
 760                         // we try to bind to permanent array, so that OCIBindByName is persistent
 761                         // and carried out once only - note that max array element size is 4000 chars
 762                         if (is_array($inputarr)) {
 763                                 $bindpos = $sql[3];
 764                                 if (isset($this->_bind[$bindpos])) {
 765                                 // all tied up already
 766                                         $bindarr = &$this->_bind[$bindpos];
 767                                 } else {
 768                                 // one statement to bind them all
 769                                         $bindarr = array();
 770                                         foreach($inputarr as $k => $v) {
 771                                                 $bindarr[$k] = $v;
 772                                                 OCIBindByName($stmt,":$k",$bindarr[$k],4000);
 773                                         }
 774                                         $this->_bind[$bindpos] = &$bindarr;
 775                                 }
 776                         }
 777                 } else {
 778                         $stmt=OCIParse($this->_connectionID,$sql);
 779                 }
 780
 781                 $this->_stmt = $stmt;
 782                 if (!$stmt) return false;
 783
 784                 if (defined('ADODB_PREFETCH_ROWS')) @OCISetPrefetch($stmt,ADODB_PREFETCH_ROWS);
 785
 786                 if (is_array($inputarr)) {
 787                         foreach($inputarr as $k => $v) {
 788                                 if (is_array($v)) {
 789                                         if (sizeof($v) == 2) // suggested by g.giunta@libero.
 790                                                 OCIBindByName($stmt,":$k",$inputarr[$k][0],$v[1]);
 791                                         else
 792                                                 OCIBindByName($stmt,":$k",$inputarr[$k][0],$v[1],$v[2]);
 793
 794                                         if ($this->debug==99) echo "name=:$k",' var='.$inputarr[$k][0],' len='.$v[1],' type='.$v[2],'<br>';
 795                                 } else {
 796                                         $len = -1;
 797                                         if ($v === ' ') $len = 1;
 798                                         if (isset($bindarr)) {  // is prepared sql, so no need to ocibindbyname again
 799                                                 $bindarr[$k] = $v;
 800                                         } else {                                // dynamic sql, so rebind every time
 801                                                 OCIBindByName($stmt,":$k",$inputarr[$k],$len);
 802                                         }
 803                                 }
 804                         }
 805                 }
 806
 807         $this->_errorMsg = false;
 808                 $this->_errorCode = false;
 809                 if (OCIExecute($stmt,$this->_commit)) {
 810
 811             switch (@OCIStatementType($stmt)) {
 812                 case "SELECT":
 813                                         return $stmt;
 814
 815                 case "BEGIN":
 816                     if (is_array($sql) && !empty($sql[4])) {
 817                                                 $cursor = $sql[4];
 818                                                 if (is_resource($cursor)) {
 819                                                         $ok = OCIExecute($cursor);
 820                                 return $cursor;
 821                                                 }
 822                                                 return $stmt;
 823                     } else {
 824                                                 if (is_resource($stmt)) {
 825                                                         OCIFreeStatement($stmt);
 826                                                         return true;
 827                                                 }
 828                         return $stmt;
 829                     }
 830                     break;
 831                 default :
 832                                         // ociclose -- no because it could be used in a LOB?
 833                     return true;
 834             }
 835                 }
 836                 return false;
 837         }
 838
 839         // returns true or false
 840         function _close()
 841         {
 842                 if (!$this->autoCommit) OCIRollback($this->_connectionID);
 843                 OCILogoff($this->_connectionID);
 844                 $this->_stmt = false;
 845                 $this->_connectionID = false;
 846         }
 847
 848         function MetaPrimaryKeys($table, $owner=false,$internalKey=false)
 849         {
 850                 if ($internalKey) return array('ROWID');
 851
 852         // tested with oracle 8.1.7
 853                 $table = strtoupper($table);
 854                 if ($owner) {
 855                         $owner_clause = "AND ((a.OWNER = b.OWNER) AND (a.OWNER = UPPER('$owner')))";
 856                         $ptab = 'ALL_';
 857                 } else {
 858                         $owner_clause = '';
 859                         $ptab = 'USER_';
 860                 }
 861                 $sql = "
 862 SELECT /*+ RULE */ distinct b.column_name
 863    FROM {$ptab}CONSTRAINTS a
 864           , {$ptab}CONS_COLUMNS b
 865   WHERE ( UPPER(b.table_name) = ('$table'))
 866         AND (UPPER(a.table_name) = ('$table') and a.constraint_type = 'P')
 867         $owner_clause
 868         AND (a.constraint_name = b.constraint_name)";
 869
 870                 $rs = $this->Execute($sql);
 871                 if ($rs && !$rs->EOF) {
 872                         $arr =& $rs->GetArray();
 873                         $a = array();
 874                         foreach($arr as $v) {
 875                                 $a[] = reset($v);
 876                         }
 877                         return $a;
 878                 }
 879                 else return false;
 880         }
 881
 882         // http://gis.mit.edu/classes/11.521/sqlnotes/referential_integrity.html
 883         function MetaForeignKeys($table, $owner=false)
 884         {
 885         global $ADODB_FETCH_MODE;
 886
 887                 $save = $ADODB_FETCH_MODE;
 888                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 889                 $table = $this->qstr(strtoupper($table));
 890                 if (!$owner) {
 891                         $owner = $this->user;
 892                         $tabp = 'user_';
 893                 } else
 894                         $tabp = 'all_';
 895
 896                 $owner = ' and owner='.$this->qstr(strtoupper($owner));
 897
 898                 $sql =
 899 "select constraint_name,r_owner,r_constraint_name
 900         from {$tabp}constraints
 901         where constraint_type = 'R' and table_name = $table $owner";
 902
 903                 $constraints =& $this->GetArray($sql);
 904                 $arr = false;
 905                 foreach($constraints as $constr) {
 906                         $cons = $this->qstr($constr[0]);
 907                         $rowner = $this->qstr($constr[1]);
 908                         $rcons = $this->qstr($constr[2]);
 909                         $cols = $this->GetArray("select column_name from {$tabp}cons_columns where constraint_name=$cons $owner order by position");
 910                         $tabcol = $this->GetArray("select table_name,column_name from {$tabp}cons_columns where owner=$rowner and constraint_name=$rcons order by position");
 911
 912                         if ($cols && $tabcol)
 913                                 for ($i=0, $max=sizeof($cols); $i < $max; $i++) {
 914                                         $arr[$tabcol[$i][0]] = $cols[$i][0].'='.$tabcol[$i][1];
 915                                 }
 916                 }
 917                 $ADODB_FETCH_MODE = $save;
 918
 919                 return $arr;
 920         }
 921
 922
 923         function CharMax()
 924         {
 925                 return 4000;
 926         }
 927
 928         function TextMax()
 929         {
 930                 return 4000;
 931         }
 932
 933         /**
 934          * Quotes a string.
 935          * An example is  $db->qstr("Don't bother",magic_quotes_runtime());
 936          *
 937          * @param s                     the string to quote
 938          * @param [magic_quotes]        if $s is GET/POST var, set to get_magic_quotes_gpc().
 939          *                              This undoes the stupidity of magic quotes for GPC.
 940          *
 941          * @return  quoted string to be sent back to database
 942          */
 943         function qstr($s,$magic_quotes=false)
 944         {
 945         $nofixquotes=false;
 946
 947                 if (is_array($s)) adodb_backtrace();
 948                 if ($this->noNullStrings && strlen($s)==0)$s = ' ';
 949                 if (!$magic_quotes) {
 950                         if ($this->replaceQuote[0] == '\\'){
 951                                 $s = str_replace('\\','\\\\',$s);
 952                         }
 953                         return  "'".str_replace("'",$this->replaceQuote,$s)."'";
 954                 }
 955
 956                 // undo magic quotes for "
 957                 $s = str_replace('\\"','"',$s);
 958
 959                 if ($this->replaceQuote == "\\'")  // ' already quoted, no need to change anything
 960                         return "'$s'";
 961                 else {// change \' to '' for sybase/mssql
 962                         $s = str_replace('\\\\','\\',$s);
 963                         return "'".str_replace("\\'",$this->replaceQuote,$s)."'";
 964                 }
 965         }
 966
 967 }
 968
 969 /*--------------------------------------------------------------------------------------
 970                  Class Name: Recordset
 971 --------------------------------------------------------------------------------------*/
 972
 973 class ADORecordset_oci8 extends ADORecordSet {
 974
 975         var $databaseType = 'oci8';
 976         var $bind=false;
 977         var $_fieldobjs;
 978         //var $_arr = false;
 979
 980         function ADORecordset_oci8($queryID,$mode=false)
 981         {
 982                 if ($mode === false) {
 983                         global $ADODB_FETCH_MODE;
 984                         $mode = $ADODB_FETCH_MODE;
 985                 }
 986                 switch ($mode)
 987                 {
 988                 default:
 989                 case ADODB_FETCH_NUM: $this->fetchMode = OCI_NUM+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
 990                 case ADODB_FETCH_ASSOC:$this->fetchMode = OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
 991                 case ADODB_FETCH_DEFAULT:
 992                 case ADODB_FETCH_BOTH:$this->fetchMode = OCI_NUM+OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
 993                 }
 994
 995                 $this->_queryID = $queryID;
 996         }
 997
 998
 999         function Init()
1000         {
1001                 if ($this->_inited) return;
1002
1003                 $this->_inited = true;
1004                 if ($this->_queryID) {
1005
1006                         $this->_currentRow = 0;
1007                         @$this->_initrs();
1008                         $this->EOF = !$this->_fetch();
1009
1010                         /*
1011                         // based on idea by Gaetano Giunta to detect unusual oracle errors
1012                         // see http://phplens.com/lens/lensforum/msgs.php?id=6771
1013                         $err = OCIError($this->_queryID);
1014                         if ($err && $this->connection->debug) ADOConnection::outp($err);
1015                         */
1016
1017                         if (!is_array($this->fields)) {
1018                                 $this->_numOfRows = 0;
1019                                 $this->fields = array();
1020                         }
1021                 } else {
1022                         $this->fields = array();
1023                         $this->_numOfRows = 0;
1024                         $this->_numOfFields = 0;
1025                         $this->EOF = true;
1026                 }
1027         }
1028
1029         function _initrs()
1030         {
1031                 $this->_numOfRows = -1;
1032                 $this->_numOfFields = OCInumcols($this->_queryID);
1033                 if ($this->_numOfFields>0) {
1034                         $this->_fieldobjs = array();
1035                         $max = $this->_numOfFields;
1036                         for ($i=0;$i<$max; $i++) $this->_fieldobjs[] = $this->_FetchField($i);
1037                 }
1038         }
1039
1040           /*            Returns: an object containing field information.
1041                           Get column information in the Recordset object. fetchField() can be used in order to obtain information about
1042                           fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
1043                           fetchField() is retrieved.            */
1044
1045         function &_FetchField($fieldOffset = -1)
1046         {
1047                 $fld = new ADOFieldObject;
1048                 $fieldOffset += 1;
1049                 $fld->name =OCIcolumnname($this->_queryID, $fieldOffset);
1050                 $fld->type = OCIcolumntype($this->_queryID, $fieldOffset);
1051                 $fld->max_length = OCIcolumnsize($this->_queryID, $fieldOffset);
1052                 if ($fld->type == 'NUMBER') {
1053                         $p = OCIColumnPrecision($this->_queryID, $fieldOffset);
1054                         $sc = OCIColumnScale($this->_queryID, $fieldOffset);
1055                         if ($p != 0 && $sc == 0) $fld->type = 'INT';
1056                         //echo " $this->name ($p.$sc) ";
1057                 }
1058                 return $fld;
1059         }
1060
1061         /* For some reason, OCIcolumnname fails when called after _initrs() so we cache it */
1062         function &FetchField($fieldOffset = -1)
1063         {
1064                 return $this->_fieldobjs[$fieldOffset];
1065         }
1066
1067
1068         // 10% speedup to move MoveNext to child class
1069         function MoveNext()
1070         {
1071         //global $ADODB_EXTENSION;if ($ADODB_EXTENSION) return @adodb_movenext($this);
1072
1073                 if ($this->EOF) return false;
1074
1075                 $this->_currentRow++;
1076                 if(@OCIfetchinto($this->_queryID,$this->fields,$this->fetchMode))
1077                         return true;
1078                 $this->EOF = true;
1079
1080                 return false;
1081         }
1082
1083         /* Optimize SelectLimit() by using OCIFetch() instead of OCIFetchInto() */
1084         function &GetArrayLimit($nrows,$offset=-1)
1085         {
1086                 if ($offset <= 0) {
1087                         $arr =& $this->GetArray($nrows);
1088                         return $arr;
1089                 }
1090                 for ($i=1; $i < $offset; $i++)
1091                         if (!@OCIFetch($this->_queryID)) return array();
1092
1093                 if (!@OCIfetchinto($this->_queryID,$this->fields,$this->fetchMode)) return array();
1094                 $results = array();
1095                 $cnt = 0;
1096                 while (!$this->EOF && $nrows != $cnt) {
1097                         $results[$cnt++] = $this->fields;
1098                         $this->MoveNext();
1099                 }
1100
1101                 return $results;
1102         }
1103
1104
1105         /* Use associative array to get fields array */
1106         function Fields($colname)
1107         {
1108                 if (!$this->bind) {
1109                         $this->bind = array();
1110                         for ($i=0; $i < $this->_numOfFields; $i++) {
1111                                 $o = $this->FetchField($i);
1112                                 $this->bind[strtoupper($o->name)] = $i;
1113                         }
1114                 }
1115
1116                  return $this->fields[$this->bind[strtoupper($colname)]];
1117         }
1118
1119
1120
1121         function _seek($row)
1122         {
1123                 return false;
1124         }
1125
1126         function _fetch()
1127         {
1128                 return @OCIfetchinto($this->_queryID,$this->fields,$this->fetchMode);
1129         }
1130
1131         /*              close() only needs to be called if you are worried about using too much memory while your script
1132                         is running. All associated result memory for the specified result identifier will automatically be freed.               */
1133
1134         function _close()
1135         {
1136                 if ($this->connection->_stmt === $this->_queryID) $this->connection->_stmt = false;
1137                 OCIFreeStatement($this->_queryID);
1138                 $this->_queryID = false;
1139
1140         }
1141
1142         function MetaType($t,$len=-1)
1143         {
1144                 if (is_object($t)) {
1145                         $fieldobj = $t;
1146                         $t = $fieldobj->type;
1147                         $len = $fieldobj->max_length;
1148                 }
1149                 switch (strtoupper($t)) {
1150                 case 'VARCHAR':
1151                 case 'VARCHAR2':
1152                 case 'CHAR':
1153                 case 'VARBINARY':
1154                 case 'BINARY':
1155                 case 'NCHAR':
1156                 case 'NVARCHAR':
1157                 case 'NVARCHAR2':
1158                                  if (isset($this) && $len <= $this->blobSize) return 'C';
1159
1160                 case 'NCLOB':
1161                 case 'LONG':
1162                 case 'LONG VARCHAR':
1163                 case 'CLOB':
1164                 return 'X';
1165
1166                 case 'LONG RAW':
1167                 case 'LONG VARBINARY':
1168                 case 'BLOB':
1169                         return 'B';
1170
1171                 case 'DATE':
1172                         return  ($this->connection->datetime) ? 'T' : 'D';
1173
1174
1175                 case 'TIMESTAMP': return 'T';
1176
1177                 case 'INT':
1178                 case 'SMALLINT':
1179                 case 'INTEGER':
1180                         return 'I';
1181
1182                 default: return 'N';
1183                 }
1184         }
1185 }
1186 ?>