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