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

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