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