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 {
39 var $databaseType = 'oci8';
40 var $dataProvider = 'oci8';
41 var $replaceQuote = "''"; // string to use to replace quotes
42 var $concat_operator='||';
43 var $sysDate = "TRUNC(SYSDATE)";
44 var $sysTimeStamp = 'SYSDATE';
45 var $metaDatabasesSQL = "SELECT USERNAME FROM ALL_USERS WHERE USERNAME NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN') ORDER BY 1";
47 var $_commit = OCI_COMMIT_ON_SUCCESS;
48 var $_initdate = true; // init date to YYYY-MM-DD
49 var $metaTablesSQL = "select table_name,table_type from cat where table_type in ('TABLE','VIEW')";
50 var $metaColumnsSQL = "select cname,coltype,width, SCALE, PRECISION, NULLS, DEFAULTVAL from col where tname='%s' order by colno"; //changed by smondino@users.sourceforge. net
51 var $_bindInputArray = true;
53 var $_genIDSQL = "SELECT (%s.nextval) FROM DUAL";
54 var $_genSeqSQL = "CREATE SEQUENCE %s START WITH %s";
55 var $_dropSeqSQL = "DROP SEQUENCE %s";
56 var $hasAffectedRows = true;
57 var $random = "abs(mod(DBMS_RANDOM.RANDOM,10000001)/10000000)";
58 var $noNullStrings = false;
59 var $connectSID = false;
61 var $_hasOCIFetchStatement = false;
62 var $_getarray = false; // currently not working
63 var $leftOuter = ''; // oracle wierdness, $col = $value (+) for LEFT OUTER, $col (+)= $value for RIGHT OUTER
64 var $session_sharing_force_blob = false; // alter session on updateblob if set to true
65 var $firstrows = true; // enable first rows optimization on SelectLimit()
66 var $selectOffsetAlg1 = 100; // when to use 1st algorithm of selectlimit.
67 var $NLS_DATE_FORMAT = 'YYYY-MM-DD'; // To include time, use 'RRRR-MM-DD HH24:MI:SS'
68 var $useDBDateFormatForTextInput=false;
69 var $datetime = false; // MetaType('DATE') returns 'D' (datetime==false) or 'T' (datetime == true)
71 // var $ansiOuter = true; // if oracle9
75 $this->_hasOCIFetchStatement = ADODB_PHPVER >= 0x4200;
78 /* Function &MetaColumns($table) added by smondino@users.sourceforge.net*/
79 function &MetaColumns($table)
81 global $ADODB_FETCH_MODE;
83 $save = $ADODB_FETCH_MODE;
84 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
85 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
87 $rs = $this->Execute(sprintf($this->metaColumnsSQL,strtoupper($table)));
89 if (isset($savem)) $this->SetFetchMode($savem);
90 $ADODB_FETCH_MODE = $save;
91 if (!$rs) return false;
93 while (!$rs->EOF) { //print_r($rs->fields);
94 $fld = new ADOFieldObject();
95 $fld->name = $rs->fields[0];
96 $fld->type = $rs->fields[1];
97 $fld->max_length = $rs->fields[2];
98 $fld->scale = $rs->fields[3];
99 if ($rs->fields[1] == 'NUMBER' && $rs->fields[3] == 0) {
101 $fld->max_length = $rs->fields[4];
103 $fld->not_null = (strncmp($rs->fields[5], 'NOT',3) === 0);
104 $fld->binary = (strpos($fld->type,'BLOB') !== false);
105 $fld->default_value = $rs->fields[6];
107 if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) $retarr[] = $fld;
108 else $retarr[strtoupper($fld->name)] = $fld;
117 $rs =& $this->Execute("select TO_CHAR($this->sysTimeStamp,'YYYY-MM-DD HH24:MI:SS') from dual");
118 if ($rs && !$rs->EOF) return $this->UnixTimeStamp(reset($rs->fields));
125 Multiple modes of connection are supported:
128 $conn->Connect(false,'scott','tiger');
131 $conn->Connect(false,'scott','tiger',$tnsname);
132 $conn->Connect($tnsname,'scott','tiger');
134 c. Server + service name
135 $conn->Connect($serveraddress,'scott,'tiger',$service_name);
138 $conn->connectSID = true;
139 $conn->Connect($serveraddress,'scott,'tiger',$SID);
146 (ADDRESS = (PROTOCOL = TCP)(HOST = kermit)(PORT = 1523))
149 (SERVICE_NAME = natsoft.domain)
153 There are 3 connection modes, 0 = non-persistent, 1 = persistent, 2 = force new connection
156 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename,$mode=0)
158 if (!function_exists('OCIPLogon')) return false;
160 $this->_errorMsg = false;
161 $this->_errorCode = false;
163 if($argHostname) { // added by Jorma Tuomainen <jorma.tuomainen@ppoy.fi>
164 if (empty($argDatabasename)) $argDatabasename = $argHostname;
166 if(strpos($argHostname,":")) {
167 $argHostinfo=explode(":",$argHostname);
168 $argHostname=$argHostinfo[0];
169 $argHostport=$argHostinfo[1];
174 if ($this->connectSID) {
175 $argDatabasename="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=".$argHostname
176 .")(PORT=$argHostport))(CONNECT_DATA=(SID=$argDatabasename)))";
178 $argDatabasename="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=".$argHostname
179 .")(PORT=$argHostport))(CONNECT_DATA=(SERVICE_NAME=$argDatabasename)))";
183 //if ($argHostname) print "<p>Connect: 1st argument should be left blank for $this->databaseType</p>";
185 $this->_connectionID = OCIPLogon($argUsername,$argPassword, $argDatabasename);
186 if ($this->_connectionID && $this->autoRollback) OCIrollback($this->_connectionID);
187 } elseif ($mode==2) {
188 $this->_connectionID = OCINLogon($argUsername,$argPassword, $argDatabasename);
190 $this->_connectionID = OCILogon($argUsername,$argPassword, $argDatabasename);
192 if ($this->_connectionID === false) return false;
193 if ($this->_initdate) {
194 $this->Execute("ALTER SESSION SET NLS_DATE_FORMAT='".$this->NLS_DATE_FORMAT."'");
198 // Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production
199 // $vers = OCIServerVersion($this->_connectionID);
200 // if (strpos($vers,'8i') !== false) $this->ansiOuter = true;
204 function ServerInfo()
206 $arr['compat'] = $this->GetOne('select value from sys.database_compatible_level');
207 $arr['description'] = @OCIServerVersion($this->_connectionID);
208 $arr['version'] = ADOConnection::_findvers($arr['description']);
211 // returns true or false
212 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
214 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename,1);
217 // returns true or false
218 function _nconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
220 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename,2);
223 function _affectedrows()
225 if (is_resource($this->_stmt)) return @OCIRowCount($this->_stmt);
229 function IfNull( $field, $ifNull )
231 return " NVL($field, $ifNull) "; // if Oracle
234 // format and return date string in database date format
237 if (empty($d) && $d !== 0) return 'null';
239 if (is_string($d)) $d = ADORecordSet::UnixDate($d);
240 return "TO_DATE(".adodb_date($this->fmtDate,$d).",'".$this->NLS_DATE_FORMAT."')";
243 // format and return date string in database timestamp format
244 function DBTimeStamp($ts)
246 if (empty($ts) && $ts !== 0) return 'null';
247 if (is_string($ts)) $ts = ADORecordSet::UnixTimeStamp($ts);
248 return 'TO_DATE('.adodb_date($this->fmtTimeStamp,$ts).",'RRRR-MM-DD, HH:MI:SS AM')";
251 function RowLock($tables,$where)
253 if ($this->autoCommit) $this->BeginTrans();
254 return $this->GetOne("select 1 as ignore from $tables where $where for update");
257 function &MetaTables($ttype=false,$showSchema=false,$mask=false)
260 $save = $this->metaTablesSQL;
261 $mask = $this->qstr(strtoupper($mask));
262 $this->metaTablesSQL .= " AND table_name like $mask";
264 $ret =& ADOConnection::MetaTables($ttype,$showSchema);
267 $this->metaTablesSQL = $save;
272 function BeginTrans()
274 if ($this->transOff) return true;
275 $this->transCnt += 1;
276 $this->autoCommit = false;
277 $this->_commit = OCI_DEFAULT;
281 function CommitTrans($ok=true)
283 if ($this->transOff) return true;
284 if (!$ok) return $this->RollbackTrans();
286 if ($this->transCnt) $this->transCnt -= 1;
287 $ret = OCIcommit($this->_connectionID);
288 $this->_commit = OCI_COMMIT_ON_SUCCESS;
289 $this->autoCommit = true;
293 function RollbackTrans()
295 if ($this->transOff) return true;
296 if ($this->transCnt) $this->transCnt -= 1;
297 $ret = OCIrollback($this->_connectionID);
298 $this->_commit = OCI_COMMIT_ON_SUCCESS;
299 $this->autoCommit = true;
303 function SelectDB($dbName)
310 if ($this->_errorMsg !== false) return $this->_errorMsg;
312 if (is_resource($this->_stmt)) $arr = @OCIerror($this->_stmt);
314 $arr = @OCIerror($this->_connectionID);
315 if ($arr === false) $arr = @OCIError();
316 if ($arr === false) return '';
318 $this->_errorMsg = $arr['message'];
319 $this->_errorCode = $arr['code'];
320 return $this->_errorMsg;
325 if ($this->_errorCode !== false) return $this->_errorCode;
327 if (is_resource($this->_stmt)) $arr = @OCIError($this->_stmt);
329 $arr = @OCIError($this->_connectionID);
330 if ($arr == false) $arr = @OCIError();
331 if ($arr == false) return '';
334 $this->_errorMsg = $arr['message'];
335 $this->_errorCode = $arr['code'];
340 // Format date column in sql string given an input format that understands Y M D
341 function SQLDate($fmt, $col=false)
343 if (!$col) $col = $this->sysTimeStamp;
344 $s = 'TO_CHAR('.$col.",'";
347 for ($i=0; $i < $len; $i++) {
393 // handle escape characters...
396 $ch = substr($fmt,$i,1);
398 if (strpos('-/.:;, ',$ch) !== false) $s .= $ch;
399 else $s .= '"'.$ch.'"';
407 This algorithm makes use of
410 The FIRST_ROWS hint explicitly chooses the approach to optimize response time,
411 that is, minimum resource usage to return the first row. Results will be returned
412 as soon as they are identified.
414 b. Uses rownum tricks to obtain only the required rows from a given offset.
415 As this uses complicated sql statements, we only use this if the $offset >= 100.
416 This idea by Tomas V V Cox.
418 This implementation does not appear to work with oracle 8.0.5 or earlier. Comment
419 out this function then, and the slower SelectLimit() in the base class will be used.
421 function &SelectLimit($sql,$nrows=-1,$offset=-1, $inputarr=false,$secs2cache=0)
423 // seems that oracle only supports 1 hint comment in 8i
424 if ($this->firstrows) {
425 if (strpos($sql,'/*+') !== false)
426 $sql = str_replace('/*+ ','/*+FIRST_ROWS ',$sql);
428 $sql = preg_replace('/^[ \t\n]*select/i','SELECT /*+FIRST_ROWS*/',$sql);
431 if ($offset < $this->selectOffsetAlg1) {
433 if ($offset > 0) $nrows += $offset;
434 //$inputarr['adodb_rownum'] = $nrows;
435 if ($this->databaseType == 'oci8po') {
436 $sql = "select * from ($sql) where rownum <= ?";
438 $sql = "select * from ($sql) where rownum <= :adodb_offset";
440 $inputarr['adodb_offset'] = $nrows;
443 // note that $nrows = 0 still has to work ==> no rows returned
445 $rs =& ADOConnection::SelectLimit($sql,$nrows,$offset,$inputarr,$secs2cache);
449 // Algorithm by Tomas V V Cox, from PEAR DB oci8.php
451 // Let Oracle return the name of the columns
452 $q_fields = "SELECT * FROM ($sql) WHERE NULL = NULL";
453 if (!$stmt = OCIParse($this->_connectionID, $q_fields)) {
457 if (is_array($inputarr)) {
458 foreach($inputarr as $k => $v) {
460 if (sizeof($v) == 2) // suggested by g.giunta@libero.
461 OCIBindByName($stmt,":$k",$inputarr[$k][0],$v[1]);
463 OCIBindByName($stmt,":$k",$inputarr[$k][0],$v[1],$v[2]);
466 if ($v === ' ') $len = 1;
467 if (isset($bindarr)) { // is prepared sql, so no need to ocibindbyname again
469 } else { // dynamic sql, so rebind every time
470 OCIBindByName($stmt,":$k",$inputarr[$k],$len);
476 if (!OCIExecute($stmt, OCI_DEFAULT)) {
477 OCIFreeStatement($stmt);
481 $ncols = OCINumCols($stmt);
482 for ( $i = 1; $i <= $ncols; $i++ ) {
483 $cols[] = '"'.OCIColumnName($stmt, $i).'"';
487 OCIFreeStatement($stmt);
488 $fields = implode(',', $cols);
490 $offset += 1; // in Oracle rownum starts at 1
492 if ($this->databaseType == 'oci8po') {
493 $sql = "SELECT $fields FROM".
494 "(SELECT rownum as adodb_rownum, $fields FROM".
495 " ($sql) WHERE rownum <= ?".
496 ") WHERE adodb_rownum >= ?";
498 $sql = "SELECT $fields FROM".
499 "(SELECT rownum as adodb_rownum, $fields FROM".
500 " ($sql) WHERE rownum <= :adodb_nrows".
501 ") WHERE adodb_rownum >= :adodb_offset";
503 $inputarr['adodb_nrows'] = $nrows;
504 $inputarr['adodb_offset'] = $offset;
506 if ($secs2cache>0) $rs =& $this->CacheExecute($secs2cache, $sql,$inputarr);
507 else $rs =& $this->Execute($sql,$inputarr);
515 * Store BLOBs and CLOBs
517 * Example: to store $var in a blob
519 * $conn->Execute('insert into TABLE (id,ablob) values(12,empty_blob())');
520 * $conn->UpdateBlob('TABLE', 'ablob', $varHoldingBlob, 'ID=12', 'BLOB');
522 * $blobtype supports 'BLOB' and 'CLOB', but you need to change to 'empty_clob()'.
524 * to get length of LOB:
525 * select DBMS_LOB.GETLENGTH(ablob) from TABLE
527 * If you are using CURSOR_SHARING = force, it appears this will case a segfault
528 * under oracle 8.1.7.0. Run:
529 * $db->Execute('ALTER SESSION SET CURSOR_SHARING=EXACT');
530 * before UpdateBlob() then...
533 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
536 //if (strlen($val) < 4000) return $this->Execute("UPDATE $table SET $column=:blob WHERE $where",array('blob'=>$val)) != false;
538 switch(strtoupper($blobtype)) {
539 default: ADOConnection::outp("<b>UpdateBlob</b>: Unknown blobtype=$blobtype"); return false;
540 case 'BLOB': $type = OCI_B_BLOB; break;
541 case 'CLOB': $type = OCI_B_CLOB; break;
544 if ($this->databaseType == 'oci8po')
545 $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO ?";
547 $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO :blob";
549 $desc = OCINewDescriptor($this->_connectionID, OCI_D_LOB);
550 $arr['blob'] = array($desc,-1,$type);
551 if ($this->session_sharing_force_blob) $this->Execute('ALTER SESSION SET CURSOR_SHARING=EXACT');
552 $commit = $this->autoCommit;
553 if ($commit) $this->BeginTrans();
554 $rs = ADODB_oci8::Execute($sql,$arr);
555 if ($rez = !empty($rs)) $desc->save($val);
557 if ($commit) $this->CommitTrans();
558 if ($this->session_sharing_force_blob) $this->Execute('ALTER SESSION SET CURSOR_SHARING=FORCE');
560 if ($rez) $rs->Close();
565 * Usage: store file pointed to by $var in a blob
567 function UpdateBlobFile($table,$column,$val,$where,$blobtype='BLOB')
569 switch(strtoupper($blobtype)) {
570 default: ADOConnection::outp( "<b>UpdateBlob</b>: Unknown blobtype=$blobtype"); return false;
571 case 'BLOB': $type = OCI_B_BLOB; break;
572 case 'CLOB': $type = OCI_B_CLOB; break;
575 if ($this->databaseType == 'oci8po')
576 $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO ?";
578 $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO :blob";
580 $desc = OCINewDescriptor($this->_connectionID, OCI_D_LOB);
581 $arr['blob'] = array($desc,-1,$type);
584 $rs = ADODB_oci8::Execute($sql,$arr);
585 if ($rez = !empty($rs)) $desc->savefile($val);
587 $this->CommitTrans();
589 if ($rez) $rs->Close();
596 $stmt = $this->Prepare('insert into emp (empno, ename) values (:empno, :ename)');
598 function Prepare($sql,$cursor=false)
602 $stmt = OCIParse($this->_connectionID,$sql);
604 if (!$stmt) return false;
608 if (@OCIStatementType($stmt) == 'BEGIN') {
609 return array($sql,$stmt,0,$BINDNUM, ($cursor) ? OCINewCursor($this->_connectionID) : false);
612 return array($sql,$stmt,0,$BINDNUM);
616 Call an oracle stored procedure and return a cursor variable.
617 Convert the cursor variable into a recordset.
618 Concept by Robert Tuttle robert@ud.com
621 Note: we return a cursor variable in :RS2
622 $rs = $db->ExecuteCursor("BEGIN adodb.open_tab(:RS2); END;",'RS2');
624 $rs = $db->ExecuteCursor(
625 "BEGIN :RS2 = adodb.getdata(:VAR1); END;",
627 array('VAR1' => 'Mr Bean'));
630 function &ExecuteCursor($sql,$cursorName='rs',$params=false)
632 $stmt = ADODB_oci8::Prepare($sql,true); # true to allocate OCINewCursor
634 if (is_array($stmt) && sizeof($stmt) >= 5) {
635 $this->Parameter($stmt, $ignoreCur, $cursorName, false, -1, OCI_B_CURSOR);
637 foreach($params as $k => $v) {
638 $this->Parameter($stmt,$params[$k], $k);
642 return $this->Execute($stmt);
646 Bind a variable -- very, very fast for executing repeated statements in oracle.
648 for ($i = 0; $i < $max; $i++) {
649 $p1 = ?; $p2 = ?; $p3 = ?;
650 $this->Execute("insert into table (col0, col1, col2) values (:0, :1, :2)",
655 $stmt = $DB->Prepare("insert into table (col0, col1, col2) values (:0, :1, :2)");
656 $DB->Bind($stmt, $p1);
657 $DB->Bind($stmt, $p2);
658 $DB->Bind($stmt, $p3);
659 for ($i = 0; $i < $max; $i++) {
660 $p1 = ?; $p2 = ?; $p3 = ?;
665 ** Test table has 3 cols, and 1 index. Test to insert 1000 records
666 Time 0.6081s (1644.60 inserts/sec) with direct OCIParse/OCIExecute
667 Time 0.6341s (1577.16 inserts/sec) with ADOdb Prepare/Bind/Execute
668 Time 1.5533s ( 643.77 inserts/sec) with pure SQL using Execute
670 Now if PHP only had batch/bulk updating like Java or PL/SQL...
672 Note that the order of parameters differs from OCIBindByName,
673 because we default the names to :0, :1, :2
675 function Bind(&$stmt,&$var,$size=4000,$type=false,$name=false)
677 if (!is_array($stmt)) return false;
679 if (($type == OCI_B_CURSOR) && sizeof($stmt) >= 5) {
680 return OCIBindByName($stmt[1],":".$name,$stmt[4],$size,$type);
683 if ($name == false) {
684 if ($type !== false) $rez = OCIBindByName($stmt[1],":".$name,$var,$size,$type);
685 else $rez = OCIBindByName($stmt[1],":".$stmt[2],$var,$size); // +1 byte for null terminator
687 } elseif ($type == OCI_B_BLOB){
688 //we have to create a new Descriptor here
689 $_blob = OCINewDescriptor($this->_connectionID, OCI_D_LOB);
690 $rez = OCIBindByName($stmt[1], ":".$name, $_blob, -1, OCI_B_BLOB);
693 if ($type !== false) $rez = OCIBindByName($stmt[1],":".$name,$var,$size,$type);
694 else $rez = OCIBindByName($stmt[1],":".$name,$var,$size); // +1 byte for null terminator
700 function Param($name)
707 $stmt = $db->Prepare('select * from table where id =:myid and group=:group');
708 $db->Parameter($stmt,$id,'myid');
709 $db->Parameter($stmt,$group,'group');
712 @param $stmt Statement returned by Prepare() or PrepareSP().
713 @param $var PHP variable to bind to
714 @param $name Name of stored procedure variable name to bind to.
715 @param [$isOutput] Indicates direction of parameter 0/false=IN 1=OUT 2= IN/OUT. This is ignored in oci8.
716 @param [$maxLen] Holds an maximum length of the variable.
717 @param [$type] The data type of $var. Legal values depend on driver.
719 See OCIBindByName documentation at php.net.
721 function Parameter(&$stmt,&$var,$name,$isOutput=false,$maxLen=4000,$type=false)
724 $prefix = ($isOutput) ? 'Out' : 'In';
725 $ztype = (empty($type)) ? 'false' : $type;
726 ADOConnection::outp( "{$prefix}Parameter(\$stmt, \$php_var='$var', \$name='$name', \$maxLen=$maxLen, \$type=$ztype);");
728 return $this->Bind($stmt,$var,$maxLen,$type,$name);
732 returns query ID if successful, otherwise false
733 this version supports:
735 1. $db->execute('select * from table');
737 2. $db->prepare('insert into table (a,b,c) values (:0,:1,:2)');
738 $db->execute($prepared_statement, array(1,2,3));
740 3. $db->execute('insert into table (a,b,c) values (:a,:b,:c)',array('a'=>1,'b'=>2,'c'=>3));
742 4. $db->prepare('insert into table (a,b,c) values (:0,:1,:2)');
743 $db->$bind($stmt,1); $db->bind($stmt,2); $db->bind($stmt,3);
746 function _query($sql,$inputarr)
749 if (is_array($sql)) { // is prepared sql
752 // we try to bind to permanent array, so that OCIBindByName is persistent
753 // and carried out once only - note that max array element size is 4000 chars
754 if (is_array($inputarr)) {
756 if (isset($this->_bind[$bindpos])) {
757 // all tied up already
758 $bindarr = &$this->_bind[$bindpos];
760 // one statement to bind them all
762 foreach($inputarr as $k => $v) {
764 OCIBindByName($stmt,":$k",$bindarr[$k],4000);
766 $this->_bind[$bindpos] = &$bindarr;
770 $stmt=OCIParse($this->_connectionID,$sql);
773 $this->_stmt = $stmt;
774 if (!$stmt) return false;
776 if (defined('ADODB_PREFETCH_ROWS')) @OCISetPrefetch($stmt,ADODB_PREFETCH_ROWS);
778 if (is_array($inputarr)) {
779 foreach($inputarr as $k => $v) {
781 if (sizeof($v) == 2) // suggested by g.giunta@libero.
782 OCIBindByName($stmt,":$k",$inputarr[$k][0],$v[1]);
784 OCIBindByName($stmt,":$k",$inputarr[$k][0],$v[1],$v[2]);
786 if ($this->debug==99) echo "name=:$k",' var='.$inputarr[$k][0],' len='.$v[1],' type='.$v[2],'<br>';
789 if ($v === ' ') $len = 1;
790 if (isset($bindarr)) { // is prepared sql, so no need to ocibindbyname again
792 } else { // dynamic sql, so rebind every time
793 OCIBindByName($stmt,":$k",$inputarr[$k],$len);
799 $this->_errorMsg = false;
800 $this->_errorCode = false;
801 if (OCIExecute($stmt,$this->_commit)) {
803 switch (@OCIStatementType($stmt)) {
808 if (is_array($sql) && !empty($sql[4])) {
810 if (is_resource($cursor)) {
811 $ok = OCIExecute($cursor);
816 if (is_resource($stmt)) {
817 OCIFreeStatement($stmt);
824 // ociclose -- no because it could be used in a LOB?
831 // returns true or false
834 if (!$this->autoCommit) OCIRollback($this->_connectionID);
835 OCILogoff($this->_connectionID);
836 $this->_stmt = false;
837 $this->_connectionID = false;
840 function MetaPrimaryKeys($table, $owner=false,$internalKey=false)
842 if ($internalKey) return array('ROWID');
844 // tested with oracle 8.1.7
845 $table = strtoupper($table);
847 $owner_clause = "AND ((a.OWNER = b.OWNER) AND (a.OWNER = UPPER('$owner')))";
854 SELECT /*+ RULE */ distinct b.column_name
855 FROM {$ptab}CONSTRAINTS a
856 , {$ptab}CONS_COLUMNS b
857 WHERE ( UPPER(b.table_name) = ('$table'))
858 AND (UPPER(a.table_name) = ('$table') and a.constraint_type = 'P')
860 AND (a.constraint_name = b.constraint_name)";
862 $rs = $this->Execute($sql);
863 if ($rs && !$rs->EOF) {
864 $arr =& $rs->GetArray();
866 foreach($arr as $v) {
874 // http://gis.mit.edu/classes/11.521/sqlnotes/referential_integrity.html
875 function MetaForeignKeys($table, $owner=false)
877 global $ADODB_FETCH_MODE;
879 $save = $ADODB_FETCH_MODE;
880 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
881 $table = $this->qstr(strtoupper($table));
883 $owner = $this->user;
888 $owner = ' and owner='.$this->qstr(strtoupper($owner));
891 "select constraint_name,r_owner,r_constraint_name
892 from {$tabp}constraints
893 where constraint_type = 'R' and table_name = $table $owner";
895 $constraints =& $this->GetArray($sql);
897 foreach($constraints as $constr) {
898 $cons = $this->qstr($constr[0]);
899 $rowner = $this->qstr($constr[1]);
900 $rcons = $this->qstr($constr[2]);
901 $cols = $this->GetArray("select column_name from {$tabp}cons_columns where constraint_name=$cons $owner order by position");
902 $tabcol = $this->GetArray("select table_name,column_name from {$tabp}cons_columns where owner=$rowner and constraint_name=$rcons order by position");
904 if ($cols && $tabcol)
905 for ($i=0, $max=sizeof($cols); $i < $max; $i++) {
906 $arr[$tabcol[$i][0]] = $cols[$i][0].'='.$tabcol[$i][1];
909 $ADODB_FETCH_MODE = $save;
926 * An example is $db->qstr("Don't bother",magic_quotes_runtime());
928 * @param s the string to quote
929 * @param [magic_quotes] if $s is GET/POST var, set to get_magic_quotes_gpc().
930 * This undoes the stupidity of magic quotes for GPC.
932 * @return quoted string to be sent back to database
934 function qstr($s,$magic_quotes=false)
938 if (is_array($s)) adodb_backtrace();
939 if ($this->noNullStrings && strlen($s)==0)$s = ' ';
940 if (!$magic_quotes) {
941 if ($this->replaceQuote[0] == '\\'){
942 $s = str_replace('\\','\\\\',$s);
944 return "'".str_replace("'",$this->replaceQuote,$s)."'";
947 // undo magic quotes for "
948 $s = str_replace('\\"','"',$s);
950 if ($this->replaceQuote == "\\'") // ' already quoted, no need to change anything
952 else {// change \' to '' for sybase/mssql
953 $s = str_replace('\\\\','\\',$s);
954 return "'".str_replace("\\'",$this->replaceQuote,$s)."'";
960 /*--------------------------------------------------------------------------------------
961 Class Name: Recordset
962 --------------------------------------------------------------------------------------*/
964 class ADORecordset_oci8 extends ADORecordSet {
966 var $databaseType = 'oci8';
971 function ADORecordset_oci8($queryID,$mode=false)
973 if ($mode === false) {
974 global $ADODB_FETCH_MODE;
975 $mode = $ADODB_FETCH_MODE;
980 case ADODB_FETCH_NUM: $this->fetchMode = OCI_NUM+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
981 case ADODB_FETCH_ASSOC:$this->fetchMode = OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
982 case ADODB_FETCH_DEFAULT:
983 case ADODB_FETCH_BOTH:$this->fetchMode = OCI_NUM+OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
986 $this->_queryID = $queryID;
992 if ($this->_inited) return;
994 $this->_inited = true;
995 if ($this->_queryID) {
997 $this->_currentRow = 0;
999 $this->EOF = !$this->_fetch();
1002 // based on idea by Gaetano Giunta to detect unusual oracle errors
1003 // see http://phplens.com/lens/lensforum/msgs.php?id=6771
1004 $err = OCIError($this->_queryID);
1005 if ($err && $this->connection->debug) ADOConnection::outp($err);
1008 if (!is_array($this->fields)) {
1009 $this->_numOfRows = 0;
1010 $this->fields = array();
1013 $this->fields = array();
1014 $this->_numOfRows = 0;
1015 $this->_numOfFields = 0;
1022 $this->_numOfRows = -1;
1023 $this->_numOfFields = OCInumcols($this->_queryID);
1024 if ($this->_numOfFields>0) {
1025 $this->_fieldobjs = array();
1026 $max = $this->_numOfFields;
1027 for ($i=0;$i<$max; $i++) $this->_fieldobjs[] = $this->_FetchField($i);
1031 /* Returns: an object containing field information.
1032 Get column information in the Recordset object. fetchField() can be used in order to obtain information about
1033 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
1034 fetchField() is retrieved. */
1036 function &_FetchField($fieldOffset = -1)
1038 $fld = new ADOFieldObject;
1040 $fld->name =OCIcolumnname($this->_queryID, $fieldOffset);
1041 $fld->type = OCIcolumntype($this->_queryID, $fieldOffset);
1042 $fld->max_length = OCIcolumnsize($this->_queryID, $fieldOffset);
1043 if ($fld->type == 'NUMBER') {
1044 $p = OCIColumnPrecision($this->_queryID, $fieldOffset);
1045 $sc = OCIColumnScale($this->_queryID, $fieldOffset);
1046 if ($p != 0 && $sc == 0) $fld->type = 'INT';
1047 //echo " $this->name ($p.$sc) ";
1052 /* For some reason, OCIcolumnname fails when called after _initrs() so we cache it */
1053 function &FetchField($fieldOffset = -1)
1055 return $this->_fieldobjs[$fieldOffset];
1058 // 10% speedup to move MoveNext to child class
1061 //global $ADODB_EXTENSION;if ($ADODB_EXTENSION) return @adodb_movenext($this);
1063 if ($this->EOF) return false;
1065 $this->_currentRow++;
1066 if(@OCIfetchinto($this->_queryID,$this->fields,$this->fetchMode))
1073 /* Optimize SelectLimit() by using OCIFetch() instead of OCIFetchInto() */
1074 function &GetArrayLimit($nrows,$offset=-1)
1077 $arr =& $this->GetArray($nrows);
1080 for ($i=1; $i < $offset; $i++)
1081 if (!@OCIFetch($this->_queryID)) return array();
1083 if (!@OCIfetchinto($this->_queryID,$this->fields,$this->fetchMode)) return array();
1086 while (!$this->EOF && $nrows != $cnt) {
1087 $results[$cnt++] = $this->fields;
1094 /* Use associative array to get fields array */
1095 function Fields($colname)
1098 $this->bind = array();
1099 for ($i=0; $i < $this->_numOfFields; $i++) {
1100 $o = $this->FetchField($i);
1101 $this->bind[strtoupper($o->name)] = $i;
1105 return $this->fields[$this->bind[strtoupper($colname)]];
1108 function _seek($row)
1115 return @OCIfetchinto($this->_queryID,$this->fields,$this->fetchMode);
1118 /* close() only needs to be called if you are worried about using too much memory while your script
1119 is running. All associated result memory for the specified result identifier will automatically be freed. */
1123 if ($this->connection->_stmt === $this->_queryID) $this->connection->_stmt = false;
1124 OCIFreeStatement($this->_queryID);
1125 $this->_queryID = false;
1129 function MetaType($t,$len=-1)
1131 if (is_object($t)) {
1133 $t = $fieldobj->type;
1134 $len = $fieldobj->max_length;
1136 switch (strtoupper($t)) {
1145 if (isset($this) && $len <= $this->blobSize) return 'C';
1149 case 'LONG VARCHAR':
1154 case 'LONG VARBINARY':
1159 return ($this->connection->datetime) ? 'T' : 'D';
1161 case 'TIMESTAMP': return 'T';
1168 default: return 'N';