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