4 version V4.22 15 Apr 2004 (c) 2000-2004 John Lim. All rights reserved.
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.
10 Latest version is available at http://php.weblogs.com/
12 Code contributed by George Fourlanos <fou@infomap.gr>
14 13 Nov 2000 jlim - removed all ora_* references.
19 Allows you to use a date format other than the Oracle Lite default. When a literal
20 character string appears where a date value is expected, the Oracle Lite database
21 tests the string to see if it matches the formats of Oracle, SQL-92, or the value
22 specified for this parameter in the POLITE.INI file. Setting this parameter also
23 defines the default format used in the TO_CHAR or TO_DATE functions when no
24 other format string is supplied.
26 For Oracle the default is dd-mon-yy or dd-mon-yyyy, and for SQL-92 the default is
27 yy-mm-dd or yyyy-mm-dd.
29 Using 'RR' in the format forces two-digit years less than or equal to 49 to be
30 interpreted as years in the 21st century (2000-2049), and years over 50 as years in
31 the 20th century (1950-1999). Setting the RR format as the default for all two-digit
32 year entries allows you to become year-2000 compliant. For example:
33 NLS_DATE_FORMAT='RR-MM-DD'
35 You can also modify the date format using the ALTER SESSION command.
38 class ADODB_oci8 extends ADOConnection
40 var $databaseType = 'oci8';
41 var $dataProvider = 'oci8';
42 var $replaceQuote = "''"; // string to use to replace quotes
43 var $concat_operator = '||';
44 var $sysDate = "TRUNC(SYSDATE)";
45 var $sysTimeStamp = 'SYSDATE';
46 var $metaDatabasesSQL = "SELECT USERNAME FROM ALL_USERS WHERE USERNAME NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN') ORDER BY 1";
48 var $_commit = OCI_COMMIT_ON_SUCCESS;
49 var $_initdate = true; // init date to YYYY-MM-DD
50 var $metaTablesSQL = "select table_name,table_type from cat where table_type in ('TABLE','VIEW')";
51 var $metaColumnsSQL = "select cname,coltype,width, SCALE, PRECISION, NULLS, DEFAULTVAL from col where tname='%s' order by colno"; //changed by smondino@users.sourceforge. net
52 var $_bindInputArray = true;
54 var $_genIDSQL = "SELECT (%s.nextval) FROM DUAL";
55 var $_genSeqSQL = "CREATE SEQUENCE %s START WITH %s";
56 var $_dropSeqSQL = "DROP SEQUENCE %s";
57 var $hasAffectedRows = true;
58 var $random = "abs(mod(DBMS_RANDOM.RANDOM,10000001)/10000000)";
59 var $noNullStrings = false;
60 var $connectSID = false;
62 var $_hasOCIFetchStatement = false;
63 var $_getarray = false; // currently not working
64 var $leftOuter = ''; // oracle wierdness, $col = $value (+) for LEFT OUTER, $col (+)= $value for RIGHT OUTER
65 var $session_sharing_force_blob = false; // alter session on updateblob if set to true
66 var $firstrows = true; // enable first rows optimization on SelectLimit()
67 var $selectOffsetAlg1 = 100; // when to use 1st algorithm of selectlimit.
68 var $NLS_DATE_FORMAT = 'YYYY-MM-DD'; // To include time, use 'RRRR-MM-DD HH24:MI:SS'
69 var $useDBDateFormatForTextInput = false;
70 var $datetime = false; // MetaType('DATE') returns 'D' (datetime==false) or 'T' (datetime == true)
72 // var $ansiOuter = true; // if oracle9
76 $this->_hasOCIFetchStatement = ADODB_PHPVER >= 0x4200;
79 /* Function &MetaColumns($table) added by smondino@users.sourceforge.net*/
80 function &MetaColumns($table)
82 global $ADODB_FETCH_MODE;
84 $save = $ADODB_FETCH_MODE;
85 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
86 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
88 $rs = $this->Execute(sprintf($this->metaColumnsSQL, strtoupper($table)));
90 if (isset($savem)) $this->SetFetchMode($savem);
91 $ADODB_FETCH_MODE = $save;
92 if (!$rs) return false;
94 while (!$rs->EOF) { //print_r($rs->fields);
95 $fld = new ADOFieldObject();
96 $fld->name = $rs->fields[0];
97 $fld->type = $rs->fields[1];
98 $fld->max_length = $rs->fields[2];
99 $fld->scale = $rs->fields[3];
100 if ($rs->fields[1] == 'NUMBER' && $rs->fields[3] == 0) {
102 $fld->max_length = $rs->fields[4];
104 $fld->not_null = (strncmp($rs->fields[5], 'NOT', 3) === 0);
105 $fld->binary = (strpos($fld->type, 'BLOB') !== false);
106 $fld->default_value = $rs->fields[6];
108 if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) $retarr[] = $fld;
109 else $retarr[strtoupper($fld->name)] = $fld;
118 $rs =& $this->Execute("select TO_CHAR($this->sysTimeStamp,'YYYY-MM-DD HH24:MI:SS') from dual");
119 if ($rs && !$rs->EOF) return $this->UnixTimeStamp(reset($rs->fields));
126 Multiple modes of connection are supported:
129 $conn->Connect(false,'scott','tiger');
132 $conn->Connect(false,'scott','tiger',$tnsname);
133 $conn->Connect($tnsname,'scott','tiger');
135 c. Server + service name
136 $conn->Connect($serveraddress,'scott,'tiger',$service_name);
139 $conn->connectSID = true;
140 $conn->Connect($serveraddress,'scott,'tiger',$SID);
147 (ADDRESS = (PROTOCOL = TCP)(HOST = kermit)(PORT = 1523))
150 (SERVICE_NAME = natsoft.domain)
154 There are 3 connection modes, 0 = non-persistent, 1 = persistent, 2 = force new connection
157 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename, $mode = 0)
159 if (!function_exists('OCIPLogon')) return false;
161 $this->_errorMsg = false;
162 $this->_errorCode = false;
164 if ($argHostname) { // added by Jorma Tuomainen <jorma.tuomainen@ppoy.fi>
165 if (empty($argDatabasename)) $argDatabasename = $argHostname;
167 if (strpos($argHostname, ":")) {
168 $argHostinfo = explode(":", $argHostname);
169 $argHostname = $argHostinfo[0];
170 $argHostport = $argHostinfo[1];
172 $argHostport = "1521";
175 if ($this->connectSID) {
176 $argDatabasename = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" . $argHostname
177 . ")(PORT=$argHostport))(CONNECT_DATA=(SID=$argDatabasename)))";
179 $argDatabasename = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" . $argHostname
180 . ")(PORT=$argHostport))(CONNECT_DATA=(SERVICE_NAME=$argDatabasename)))";
184 //if ($argHostname) print "<p>Connect: 1st argument should be left blank for $this->databaseType</p>";
186 $this->_connectionID = OCIPLogon($argUsername, $argPassword, $argDatabasename);
187 if ($this->_connectionID && $this->autoRollback) OCIrollback($this->_connectionID);
188 } elseif ($mode == 2) {
189 $this->_connectionID = OCINLogon($argUsername, $argPassword, $argDatabasename);
191 $this->_connectionID = OCILogon($argUsername, $argPassword, $argDatabasename);
193 if ($this->_connectionID === false) return false;
194 if ($this->_initdate) {
195 $this->Execute("ALTER SESSION SET NLS_DATE_FORMAT='" . $this->NLS_DATE_FORMAT . "'");
199 // Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production
200 // $vers = OCIServerVersion($this->_connectionID);
201 // if (strpos($vers,'8i') !== false) $this->ansiOuter = true;
205 function ServerInfo()
207 $arr['compat'] = $this->GetOne('select value from sys.database_compatible_level');
208 $arr['description'] = @OCIServerVersion($this->_connectionID);
209 $arr['version'] = ADOConnection::_findvers($arr['description']);
213 // returns true or false
214 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
216 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename, 1);
219 // returns true or false
220 function _nconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
222 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename, 2);
225 function _affectedrows()
227 if (is_resource($this->_stmt)) return @OCIRowCount($this->_stmt);
231 function IfNull($field, $ifNull)
233 return " NVL($field, $ifNull) "; // if Oracle
236 // format and return date string in database date format
239 if (empty($d) && $d !== 0) return 'null';
241 if (is_string($d)) $d = ADORecordSet::UnixDate($d);
242 return "TO_DATE(" . adodb_date($this->fmtDate, $d) . ",'" . $this->NLS_DATE_FORMAT . "')";
245 // format and return date string in database timestamp format
246 function DBTimeStamp($ts)
248 if (empty($ts) && $ts !== 0) return 'null';
249 if (is_string($ts)) $ts = ADORecordSet::UnixTimeStamp($ts);
250 return 'TO_DATE(' . adodb_date($this->fmtTimeStamp, $ts) . ",'RRRR-MM-DD, HH:MI:SS AM')";
253 function RowLock($tables, $where)
255 if ($this->autoCommit) $this->BeginTrans();
256 return $this->GetOne("select 1 as ignore from $tables where $where for update");
259 function &MetaTables($ttype = false, $showSchema = false, $mask = false)
262 $save = $this->metaTablesSQL;
263 $mask = $this->qstr(strtoupper($mask));
264 $this->metaTablesSQL .= " AND table_name like $mask";
266 $ret =& ADOConnection::MetaTables($ttype, $showSchema);
269 $this->metaTablesSQL = $save;
274 function BeginTrans()
276 if ($this->transOff) return true;
277 $this->transCnt += 1;
278 $this->autoCommit = false;
279 $this->_commit = OCI_DEFAULT;
283 function CommitTrans($ok = true)
285 if ($this->transOff) return true;
286 if (!$ok) return $this->RollbackTrans();
288 if ($this->transCnt) $this->transCnt -= 1;
289 $ret = OCIcommit($this->_connectionID);
290 $this->_commit = OCI_COMMIT_ON_SUCCESS;
291 $this->autoCommit = true;
295 function RollbackTrans()
297 if ($this->transOff) return true;
298 if ($this->transCnt) $this->transCnt -= 1;
299 $ret = OCIrollback($this->_connectionID);
300 $this->_commit = OCI_COMMIT_ON_SUCCESS;
301 $this->autoCommit = true;
305 function SelectDB($dbName)
312 if ($this->_errorMsg !== false) return $this->_errorMsg;
314 if (is_resource($this->_stmt)) $arr = @OCIerror($this->_stmt);
316 $arr = @OCIerror($this->_connectionID);
317 if ($arr === false) $arr = @OCIError();
318 if ($arr === false) return '';
320 $this->_errorMsg = $arr['message'];
321 $this->_errorCode = $arr['code'];
322 return $this->_errorMsg;
327 if ($this->_errorCode !== false) return $this->_errorCode;
329 if (is_resource($this->_stmt)) $arr = @OCIError($this->_stmt);
331 $arr = @OCIError($this->_connectionID);
332 if ($arr == false) $arr = @OCIError();
333 if ($arr == false) return '';
336 $this->_errorMsg = $arr['message'];
337 $this->_errorCode = $arr['code'];
342 // Format date column in sql string given an input format that understands Y M D
343 function SQLDate($fmt, $col = false)
345 if (!$col) $col = $this->sysTimeStamp;
346 $s = 'TO_CHAR(' . $col . ",'";
349 for ($i = 0; $i < $len; $i++) {
395 // handle escape characters...
398 $ch = substr($fmt, $i, 1);
400 if (strpos('-/.:;, ', $ch) !== false) $s .= $ch;
401 else $s .= '"' . $ch . '"';
409 This algorithm makes use of
412 The FIRST_ROWS hint explicitly chooses the approach to optimize response time,
413 that is, minimum resource usage to return the first row. Results will be returned
414 as soon as they are identified.
416 b. Uses rownum tricks to obtain only the required rows from a given offset.
417 As this uses complicated sql statements, we only use this if the $offset >= 100.
418 This idea by Tomas V V Cox.
420 This implementation does not appear to work with oracle 8.0.5 or earlier. Comment
421 out this function then, and the slower SelectLimit() in the base class will be used.
423 function &SelectLimit($sql, $nrows = -1, $offset = -1, $inputarr = false, $secs2cache = 0)
425 // seems that oracle only supports 1 hint comment in 8i
426 if ($this->firstrows) {
427 if (strpos($sql, '/*+') !== false)
428 $sql = str_replace('/*+ ', '/*+FIRST_ROWS ', $sql);
430 $sql = preg_replace('/^[ \t\n]*select/i', 'SELECT /*+FIRST_ROWS*/', $sql);
433 if ($offset < $this->selectOffsetAlg1) {
435 if ($offset > 0) $nrows += $offset;
436 //$inputarr['adodb_rownum'] = $nrows;
437 if ($this->databaseType == 'oci8po') {
438 $sql = "select * from ($sql) where rownum <= ?";
440 $sql = "select * from ($sql) where rownum <= :adodb_offset";
442 $inputarr['adodb_offset'] = $nrows;
445 // note that $nrows = 0 still has to work ==> no rows returned
447 $rs =& ADOConnection::SelectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
451 // Algorithm by Tomas V V Cox, from PEAR DB oci8.php
453 // Let Oracle return the name of the columns
454 $q_fields = "SELECT * FROM ($sql) WHERE NULL = NULL";
455 if (!$stmt = OCIParse($this->_connectionID, $q_fields)) {
459 if (is_array($inputarr)) {
460 foreach ($inputarr as $k => $v) {
462 if (sizeof($v) == 2) // suggested by g.giunta@libero.
463 OCIBindByName($stmt, ":$k", $inputarr[$k][0], $v[1]);
465 OCIBindByName($stmt, ":$k", $inputarr[$k][0], $v[1], $v[2]);
468 if ($v === ' ') $len = 1;
469 if (isset($bindarr)) { // is prepared sql, so no need to ocibindbyname again
471 } else { // dynamic sql, so rebind every time
472 OCIBindByName($stmt, ":$k", $inputarr[$k], $len);
478 if (!OCIExecute($stmt, OCI_DEFAULT)) {
479 OCIFreeStatement($stmt);
483 $ncols = OCINumCols($stmt);
484 for ($i = 1; $i <= $ncols; $i++) {
485 $cols[] = '"' . OCIColumnName($stmt, $i) . '"';
489 OCIFreeStatement($stmt);
490 $fields = implode(',', $cols);
492 $offset += 1; // in Oracle rownum starts at 1
494 if ($this->databaseType == 'oci8po') {
495 $sql = "SELECT $fields FROM" .
496 "(SELECT rownum as adodb_rownum, $fields FROM" .
497 " ($sql) WHERE rownum <= ?" .
498 ") WHERE adodb_rownum >= ?";
500 $sql = "SELECT $fields FROM" .
501 "(SELECT rownum as adodb_rownum, $fields FROM" .
502 " ($sql) WHERE rownum <= :adodb_nrows" .
503 ") WHERE adodb_rownum >= :adodb_offset";
505 $inputarr['adodb_nrows'] = $nrows;
506 $inputarr['adodb_offset'] = $offset;
508 if ($secs2cache > 0) $rs =& $this->CacheExecute($secs2cache, $sql, $inputarr);
509 else $rs =& $this->Execute($sql, $inputarr);
517 * Store BLOBs and CLOBs
519 * Example: to store $var in a blob
521 * $conn->Execute('insert into TABLE (id,ablob) values(12,empty_blob())');
522 * $conn->UpdateBlob('TABLE', 'ablob', $varHoldingBlob, 'ID=12', 'BLOB');
524 * $blobtype supports 'BLOB' and 'CLOB', but you need to change to 'empty_clob()'.
526 * to get length of LOB:
527 * select DBMS_LOB.GETLENGTH(ablob) from TABLE
529 * If you are using CURSOR_SHARING = force, it appears this will case a segfault
530 * under oracle 8.1.7.0. Run:
531 * $db->Execute('ALTER SESSION SET CURSOR_SHARING=EXACT');
532 * before UpdateBlob() then...
535 function UpdateBlob($table, $column, $val, $where, $blobtype = 'BLOB')
538 //if (strlen($val) < 4000) return $this->Execute("UPDATE $table SET $column=:blob WHERE $where",array('blob'=>$val)) != false;
540 switch (strtoupper($blobtype)) {
542 ADOConnection::outp("<b>UpdateBlob</b>: Unknown blobtype=$blobtype");
552 if ($this->databaseType == 'oci8po')
553 $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO ?";
555 $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO :blob";
557 $desc = OCINewDescriptor($this->_connectionID, OCI_D_LOB);
558 $arr['blob'] = array($desc, -1, $type);
559 if ($this->session_sharing_force_blob) $this->Execute('ALTER SESSION SET CURSOR_SHARING=EXACT');
560 $commit = $this->autoCommit;
561 if ($commit) $this->BeginTrans();
562 $rs = ADODB_oci8::Execute($sql, $arr);
563 if ($rez = !empty($rs)) $desc->save($val);
565 if ($commit) $this->CommitTrans();
566 if ($this->session_sharing_force_blob) $this->Execute('ALTER SESSION SET CURSOR_SHARING=FORCE');
568 if ($rez) $rs->Close();
573 * Usage: store file pointed to by $var in a blob
575 function UpdateBlobFile($table, $column, $val, $where, $blobtype = 'BLOB')
577 switch (strtoupper($blobtype)) {
579 ADOConnection::outp("<b>UpdateBlob</b>: Unknown blobtype=$blobtype");
589 if ($this->databaseType == 'oci8po')
590 $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO ?";
592 $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO :blob";
594 $desc = OCINewDescriptor($this->_connectionID, OCI_D_LOB);
595 $arr['blob'] = array($desc, -1, $type);
598 $rs = ADODB_oci8::Execute($sql, $arr);
599 if ($rez = !empty($rs)) $desc->savefile($val);
601 $this->CommitTrans();
603 if ($rez) $rs->Close();
610 $stmt = $this->Prepare('insert into emp (empno, ename) values (:empno, :ename)');
612 function Prepare($sql, $cursor = false)
616 $stmt = OCIParse($this->_connectionID, $sql);
618 if (!$stmt) return false;
622 if (@OCIStatementType($stmt) == 'BEGIN') {
623 return array($sql, $stmt, 0, $BINDNUM, ($cursor) ? OCINewCursor($this->_connectionID) : false);
626 return array($sql, $stmt, 0, $BINDNUM);
630 Call an oracle stored procedure and return a cursor variable.
631 Convert the cursor variable into a recordset.
632 Concept by Robert Tuttle robert@ud.com
635 Note: we return a cursor variable in :RS2
636 $rs = $db->ExecuteCursor("BEGIN adodb.open_tab(:RS2); END;",'RS2');
638 $rs = $db->ExecuteCursor(
639 "BEGIN :RS2 = adodb.getdata(:VAR1); END;",
641 array('VAR1' => 'Mr Bean'));
644 function &ExecuteCursor($sql, $cursorName = 'rs', $params = false)
646 $stmt = ADODB_oci8::Prepare($sql, true); # true to allocate OCINewCursor
648 if (is_array($stmt) && sizeof($stmt) >= 5) {
649 $this->Parameter($stmt, $ignoreCur, $cursorName, false, -1, OCI_B_CURSOR);
651 foreach ($params as $k => $v) {
652 $this->Parameter($stmt, $params[$k], $k);
656 return $this->Execute($stmt);
660 Bind a variable -- very, very fast for executing repeated statements in oracle.
662 for ($i = 0; $i < $max; $i++) {
663 $p1 = ?; $p2 = ?; $p3 = ?;
664 $this->Execute("insert into table (col0, col1, col2) values (:0, :1, :2)",
669 $stmt = $DB->Prepare("insert into table (col0, col1, col2) values (:0, :1, :2)");
670 $DB->Bind($stmt, $p1);
671 $DB->Bind($stmt, $p2);
672 $DB->Bind($stmt, $p3);
673 for ($i = 0; $i < $max; $i++) {
674 $p1 = ?; $p2 = ?; $p3 = ?;
679 ** Test table has 3 cols, and 1 index. Test to insert 1000 records
680 Time 0.6081s (1644.60 inserts/sec) with direct OCIParse/OCIExecute
681 Time 0.6341s (1577.16 inserts/sec) with ADOdb Prepare/Bind/Execute
682 Time 1.5533s ( 643.77 inserts/sec) with pure SQL using Execute
684 Now if PHP only had batch/bulk updating like Java or PL/SQL...
686 Note that the order of parameters differs from OCIBindByName,
687 because we default the names to :0, :1, :2
689 function Bind(&$stmt, &$var, $size = 4000, $type = false, $name = false)
691 if (!is_array($stmt)) return false;
693 if (($type == OCI_B_CURSOR) && sizeof($stmt) >= 5) {
694 return OCIBindByName($stmt[1], ":" . $name, $stmt[4], $size, $type);
697 if ($name == false) {
698 if ($type !== false) $rez = OCIBindByName($stmt[1], ":" . $name, $var, $size, $type);
699 else $rez = OCIBindByName($stmt[1], ":" . $stmt[2], $var, $size); // +1 byte for null terminator
701 } elseif ($type == OCI_B_BLOB) {
702 //we have to create a new Descriptor here
703 $_blob = OCINewDescriptor($this->_connectionID, OCI_D_LOB);
704 $rez = OCIBindByName($stmt[1], ":" . $name, $_blob, -1, OCI_B_BLOB);
707 if ($type !== false) $rez = OCIBindByName($stmt[1], ":" . $name, $var, $size, $type);
708 else $rez = OCIBindByName($stmt[1], ":" . $name, $var, $size); // +1 byte for null terminator
714 function Param($name)
721 $stmt = $db->Prepare('select * from table where id =:myid and group=:group');
722 $db->Parameter($stmt,$id,'myid');
723 $db->Parameter($stmt,$group,'group');
726 @param $stmt Statement returned by Prepare() or PrepareSP().
727 @param $var PHP variable to bind to
728 @param $name Name of stored procedure variable name to bind to.
729 @param [$isOutput] Indicates direction of parameter 0/false=IN 1=OUT 2= IN/OUT. This is ignored in oci8.
730 @param [$maxLen] Holds an maximum length of the variable.
731 @param [$type] The data type of $var. Legal values depend on driver.
733 See OCIBindByName documentation at php.net.
735 function Parameter(&$stmt, &$var, $name, $isOutput = false, $maxLen = 4000, $type = false)
738 $prefix = ($isOutput) ? 'Out' : 'In';
739 $ztype = (empty($type)) ? 'false' : $type;
740 ADOConnection::outp("{$prefix}Parameter(\$stmt, \$php_var='$var', \$name='$name', \$maxLen=$maxLen, \$type=$ztype);");
742 return $this->Bind($stmt, $var, $maxLen, $type, $name);
746 returns query ID if successful, otherwise false
747 this version supports:
749 1. $db->execute('select * from table');
751 2. $db->prepare('insert into table (a,b,c) values (:0,:1,:2)');
752 $db->execute($prepared_statement, array(1,2,3));
754 3. $db->execute('insert into table (a,b,c) values (:a,:b,:c)',array('a'=>1,'b'=>2,'c'=>3));
756 4. $db->prepare('insert into table (a,b,c) values (:0,:1,:2)');
757 $db->$bind($stmt,1); $db->bind($stmt,2); $db->bind($stmt,3);
760 function _query($sql, $inputarr)
763 if (is_array($sql)) { // is prepared sql
766 // we try to bind to permanent array, so that OCIBindByName is persistent
767 // and carried out once only - note that max array element size is 4000 chars
768 if (is_array($inputarr)) {
770 if (isset($this->_bind[$bindpos])) {
771 // all tied up already
772 $bindarr = &$this->_bind[$bindpos];
774 // one statement to bind them all
776 foreach ($inputarr as $k => $v) {
778 OCIBindByName($stmt, ":$k", $bindarr[$k], 4000);
780 $this->_bind[$bindpos] = &$bindarr;
784 $stmt = OCIParse($this->_connectionID, $sql);
787 $this->_stmt = $stmt;
788 if (!$stmt) return false;
790 if (defined('ADODB_PREFETCH_ROWS')) @OCISetPrefetch($stmt, ADODB_PREFETCH_ROWS);
792 if (is_array($inputarr)) {
793 foreach ($inputarr as $k => $v) {
795 if (sizeof($v) == 2) // suggested by g.giunta@libero.
796 OCIBindByName($stmt, ":$k", $inputarr[$k][0], $v[1]);
798 OCIBindByName($stmt, ":$k", $inputarr[$k][0], $v[1], $v[2]);
800 if ($this->debug == 99) echo "name=:$k", ' var=' . $inputarr[$k][0], ' len=' . $v[1], ' type=' . $v[2], '<br>';
803 if ($v === ' ') $len = 1;
804 if (isset($bindarr)) { // is prepared sql, so no need to ocibindbyname again
806 } else { // dynamic sql, so rebind every time
807 OCIBindByName($stmt, ":$k", $inputarr[$k], $len);
813 $this->_errorMsg = false;
814 $this->_errorCode = false;
815 if (OCIExecute($stmt, $this->_commit)) {
817 switch (@OCIStatementType($stmt)) {
822 if (is_array($sql) && !empty($sql[4])) {
824 if (is_resource($cursor)) {
825 $ok = OCIExecute($cursor);
830 if (is_resource($stmt)) {
831 OCIFreeStatement($stmt);
838 // ociclose -- no because it could be used in a LOB?
845 // returns true or false
848 if (!$this->autoCommit) OCIRollback($this->_connectionID);
849 OCILogoff($this->_connectionID);
850 $this->_stmt = false;
851 $this->_connectionID = false;
854 function MetaPrimaryKeys($table, $owner = false, $internalKey = false)
856 if ($internalKey) return array('ROWID');
858 // tested with oracle 8.1.7
859 $table = strtoupper($table);
861 $owner_clause = "AND ((a.OWNER = b.OWNER) AND (a.OWNER = UPPER('$owner')))";
868 SELECT /*+ RULE */ distinct b.column_name
869 FROM {$ptab}CONSTRAINTS a
870 , {$ptab}CONS_COLUMNS b
871 WHERE ( UPPER(b.table_name) = ('$table'))
872 AND (UPPER(a.table_name) = ('$table') and a.constraint_type = 'P')
874 AND (a.constraint_name = b.constraint_name)";
876 $rs = $this->Execute($sql);
877 if ($rs && !$rs->EOF) {
878 $arr =& $rs->GetArray();
880 foreach ($arr as $v) {
887 // http://gis.mit.edu/classes/11.521/sqlnotes/referential_integrity.html
888 function MetaForeignKeys($table, $owner = false)
890 global $ADODB_FETCH_MODE;
892 $save = $ADODB_FETCH_MODE;
893 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
894 $table = $this->qstr(strtoupper($table));
896 $owner = $this->user;
901 $owner = ' and owner=' . $this->qstr(strtoupper($owner));
904 "select constraint_name,r_owner,r_constraint_name
905 from {$tabp}constraints
906 where constraint_type = 'R' and table_name = $table $owner";
908 $constraints =& $this->GetArray($sql);
910 foreach ($constraints as $constr) {
911 $cons = $this->qstr($constr[0]);
912 $rowner = $this->qstr($constr[1]);
913 $rcons = $this->qstr($constr[2]);
914 $cols = $this->GetArray("select column_name from {$tabp}cons_columns where constraint_name=$cons $owner order by position");
915 $tabcol = $this->GetArray("select table_name,column_name from {$tabp}cons_columns where owner=$rowner and constraint_name=$rcons order by position");
917 if ($cols && $tabcol)
918 for ($i = 0, $max = sizeof($cols); $i < $max; $i++) {
919 $arr[$tabcol[$i][0]] = $cols[$i][0] . '=' . $tabcol[$i][1];
922 $ADODB_FETCH_MODE = $save;
939 * An example is $db->qstr("Don't bother",magic_quotes_runtime());
941 * @param s the string to quote
942 * @param [magic_quotes] if $s is GET/POST var, set to get_magic_quotes_gpc().
943 * This undoes the stupidity of magic quotes for GPC.
945 * @return quoted string to be sent back to database
947 function qstr($s, $magic_quotes = false)
949 $nofixquotes = false;
951 if (is_array($s)) adodb_backtrace();
952 if ($this->noNullStrings && strlen($s) == 0) $s = ' ';
953 if (!$magic_quotes) {
954 if ($this->replaceQuote[0] == '\\') {
955 $s = str_replace('\\', '\\\\', $s);
957 return "'" . str_replace("'", $this->replaceQuote, $s) . "'";
960 // undo magic quotes for "
961 $s = str_replace('\\"', '"', $s);
963 if ($this->replaceQuote == "\\'") // ' already quoted, no need to change anything
965 else { // change \' to '' for sybase/mssql
966 $s = str_replace('\\\\', '\\', $s);
967 return "'" . str_replace("\\'", $this->replaceQuote, $s) . "'";
973 /*--------------------------------------------------------------------------------------
974 Class Name: Recordset
975 --------------------------------------------------------------------------------------*/
977 class ADORecordset_oci8 extends ADORecordSet
980 var $databaseType = 'oci8';
986 function ADORecordset_oci8($queryID, $mode = false)
988 if ($mode === false) {
989 global $ADODB_FETCH_MODE;
990 $mode = $ADODB_FETCH_MODE;
994 case ADODB_FETCH_NUM:
995 $this->fetchMode = OCI_NUM + OCI_RETURN_NULLS + OCI_RETURN_LOBS;
997 case ADODB_FETCH_ASSOC:
998 $this->fetchMode = OCI_ASSOC + OCI_RETURN_NULLS + OCI_RETURN_LOBS;
1000 case ADODB_FETCH_DEFAULT:
1001 case ADODB_FETCH_BOTH:
1002 $this->fetchMode = OCI_NUM + OCI_ASSOC + OCI_RETURN_NULLS + OCI_RETURN_LOBS;
1006 $this->_queryID = $queryID;
1012 if ($this->_inited) return;
1014 $this->_inited = true;
1015 if ($this->_queryID) {
1017 $this->_currentRow = 0;
1019 $this->EOF = !$this->_fetch();
1022 // based on idea by Gaetano Giunta to detect unusual oracle errors
1023 // see http://phplens.com/lens/lensforum/msgs.php?id=6771
1024 $err = OCIError($this->_queryID);
1025 if ($err && $this->connection->debug) ADOConnection::outp($err);
1028 if (!is_array($this->fields)) {
1029 $this->_numOfRows = 0;
1030 $this->fields = array();
1033 $this->fields = array();
1034 $this->_numOfRows = 0;
1035 $this->_numOfFields = 0;
1042 $this->_numOfRows = -1;
1043 $this->_numOfFields = OCInumcols($this->_queryID);
1044 if ($this->_numOfFields > 0) {
1045 $this->_fieldobjs = array();
1046 $max = $this->_numOfFields;
1047 for ($i = 0; $i < $max; $i++) $this->_fieldobjs[] = $this->_FetchField($i);
1051 /* Returns: an object containing field information.
1052 Get column information in the Recordset object. fetchField() can be used in order to obtain information about
1053 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
1054 fetchField() is retrieved. */
1056 function &_FetchField($fieldOffset = -1)
1058 $fld = new ADOFieldObject;
1060 $fld->name = OCIcolumnname($this->_queryID, $fieldOffset);
1061 $fld->type = OCIcolumntype($this->_queryID, $fieldOffset);
1062 $fld->max_length = OCIcolumnsize($this->_queryID, $fieldOffset);
1063 if ($fld->type == 'NUMBER') {
1064 $p = OCIColumnPrecision($this->_queryID, $fieldOffset);
1065 $sc = OCIColumnScale($this->_queryID, $fieldOffset);
1066 if ($p != 0 && $sc == 0) $fld->type = 'INT';
1067 //echo " $this->name ($p.$sc) ";
1072 /* For some reason, OCIcolumnname fails when called after _initrs() so we cache it */
1073 function &FetchField($fieldOffset = -1)
1075 return $this->_fieldobjs[$fieldOffset];
1078 // 10% speedup to move MoveNext to child class
1081 //global $ADODB_EXTENSION;if ($ADODB_EXTENSION) return @adodb_movenext($this);
1083 if ($this->EOF) return false;
1085 $this->_currentRow++;
1086 if (@OCIfetchinto($this->_queryID, $this->fields, $this->fetchMode))
1093 /* Optimize SelectLimit() by using OCIFetch() instead of OCIFetchInto() */
1094 function &GetArrayLimit($nrows, $offset = -1)
1097 $arr =& $this->GetArray($nrows);
1100 for ($i = 1; $i < $offset; $i++)
1101 if (!@OCIFetch($this->_queryID)) return array();
1103 if (!@OCIfetchinto($this->_queryID, $this->fields, $this->fetchMode)) return array();
1106 while (!$this->EOF && $nrows != $cnt) {
1107 $results[$cnt++] = $this->fields;
1114 /* Use associative array to get fields array */
1115 function Fields($colname)
1118 $this->bind = array();
1119 for ($i = 0; $i < $this->_numOfFields; $i++) {
1120 $o = $this->FetchField($i);
1121 $this->bind[strtoupper($o->name)] = $i;
1125 return $this->fields[$this->bind[strtoupper($colname)]];
1128 function _seek($row)
1135 return @OCIfetchinto($this->_queryID, $this->fields, $this->fetchMode);
1138 /* close() only needs to be called if you are worried about using too much memory while your script
1139 is running. All associated result memory for the specified result identifier will automatically be freed. */
1143 if ($this->connection->_stmt === $this->_queryID) $this->connection->_stmt = false;
1144 OCIFreeStatement($this->_queryID);
1145 $this->_queryID = false;
1149 function MetaType($t, $len = -1)
1151 if (is_object($t)) {
1153 $t = $fieldobj->type;
1154 $len = $fieldobj->max_length;
1156 switch (strtoupper($t)) {
1165 if (isset($this) && $len <= $this->blobSize) return 'C';
1169 case 'LONG VARCHAR':
1174 case 'LONG VARBINARY':
1179 return ($this->connection->datetime) ? 'T' : 'D';