3 V4.22 15 Apr 2004 (c) 2000-2004 John Lim (jlim@natsoft.com.my). All rights reserved.
4 Released under both BSD license and Lesser GPL library license.
5 Whenever there is any discrepancy between the two licenses,
6 the BSD license will take precedence.
8 Latest version is available at http://php.weblogs.com/
10 Oracle data driver. Requires Oracle client. Works on Windows and Unix and Oracle 7 and 8.
12 If you are using Oracle 8, use the oci8 driver which is much better and more reliable.
16 class ADODB_oracle extends ADOConnection
18 var $databaseType = "oracle";
19 var $replaceQuote = "''"; // string to use to replace quotes
20 var $concat_operator = '||';
22 var $_initdate = true; // init date to YYYY-MM-DD
23 var $metaTablesSQL = 'select table_name from cat';
24 var $metaColumnsSQL = "select cname,coltype,width from col where tname='%s' order by colno";
25 var $sysDate = "TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')";
26 var $sysTimeStamp = 'SYSDATE';
27 var $connectSID = true;
29 function ADODB_oracle()
33 // format and return date string in database date format
36 if (is_string($d)) $d = ADORecordSet::UnixDate($d);
37 return 'TO_DATE(' . adodb_date($this->fmtDate, $d) . ",'YYYY-MM-DD')";
40 // format and return date string in database timestamp format
41 function DBTimeStamp($ts)
43 if (is_string($ts)) $d = ADORecordSet::UnixTimeStamp($ts);
44 return 'TO_DATE(' . adodb_date($this->fmtTimeStamp, $ts) . ",'RRRR-MM-DD, HH:MI:SS AM')";
49 $this->autoCommit = false;
50 ora_commitoff($this->_connectionID);
54 function CommitTrans($ok = true)
56 if (!$ok) return $this->RollbackTrans();
57 $ret = ora_commit($this->_connectionID);
58 ora_commiton($this->_connectionID);
62 function RollbackTrans()
64 $ret = ora_rollback($this->_connectionID);
65 ora_commiton($this->_connectionID);
69 /* there seems to be a bug in the oracle extension -- always returns ORA-00000 - no error */
72 $this->_errorMsg = @ora_error($this->_curs);
73 if (!$this->_errorMsg) $this->_errorMsg = @ora_error($this->_connectionID);
74 return $this->_errorMsg;
79 $err = @ora_errorcode($this->_curs);
80 if (!$err) return @ora_errorcode($this->_connectionID);
83 // returns true or false
84 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename, $mode = 0)
86 // G. Giunta 2003/08/13 - This looks danegrously suspicious: why should we want to set
87 // the oracle home to the host name of remote DB?
88 // if ($argHostname) putenv("ORACLE_HOME=$argHostname");
90 if ($argHostname) { // code copied from version submitted for oci8 by Jorma Tuomainen <jorma.tuomainen@ppoy.fi>
91 if (empty($argDatabasename)) $argDatabasename = $argHostname;
93 if (strpos($argHostname, ":")) {
94 $argHostinfo = explode(":", $argHostname);
95 $argHostname = $argHostinfo[0];
96 $argHostport = $argHostinfo[1];
98 $argHostport = "1521";
101 if ($this->connectSID) {
102 $argDatabasename = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" . $argHostname
103 . ")(PORT=$argHostport))(CONNECT_DATA=(SID=$argDatabasename)))";
105 $argDatabasename = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" . $argHostname
106 . ")(PORT=$argHostport))(CONNECT_DATA=(SERVICE_NAME=$argDatabasename)))";
111 if ($argDatabasename) $argUsername .= "@$argDatabasename";
113 //if ($argHostname) print "<p>Connect: 1st argument should be left blank for $this->databaseType</p>";
115 $this->_connectionID = ora_plogon($argUsername, $argPassword);
117 $this->_connectionID = ora_logon($argUsername, $argPassword);
118 if ($this->_connectionID === false) return false;
119 if ($this->autoCommit) ora_commiton($this->_connectionID);
120 if ($this->_initdate) {
121 $rs = $this->_query("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD'");
122 if ($rs) ora_close($rs);
128 // returns true or false
129 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
131 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename, 1);
134 // returns query ID if successful, otherwise false
135 function _query($sql, $inputarr = false)
137 $curs = ora_open($this->_connectionID);
139 if ($curs === false) return false;
140 $this->_curs = $curs;
141 if (!ora_parse($curs, $sql)) return false;
142 if (ora_exec($curs)) return $curs;
148 // returns true or false
151 return @ora_logoff($this->_connectionID);
156 /*--------------------------------------------------------------------------------------
157 Class Name: Recordset
158 --------------------------------------------------------------------------------------*/
160 class ADORecordset_oracle extends ADORecordSet
163 var $databaseType = "oracle";
166 function ADORecordset_oracle($queryID, $mode = false)
169 if ($mode === false) {
170 global $ADODB_FETCH_MODE;
171 $mode = $ADODB_FETCH_MODE;
173 $this->fetchMode = $mode;
175 $this->_queryID = $queryID;
177 $this->_inited = true;
178 $this->fields = array();
180 $this->_currentRow = 0;
181 $this->EOF = !$this->_fetch();
184 $this->_numOfRows = 0;
185 $this->_numOfFields = 0;
189 return $this->_queryID;
192 /* Returns: an object containing field information.
193 Get column information in the Recordset object. fetchField() can be used in order to obtain information about
194 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
195 fetchField() is retrieved. */
197 function FetchField($fieldOffset = -1)
199 $fld = new ADOFieldObject;
200 $fld->name = ora_columnname($this->_queryID, $fieldOffset);
201 $fld->type = ora_columntype($this->_queryID, $fieldOffset);
202 $fld->max_length = ora_columnsize($this->_queryID, $fieldOffset);
206 /* Use associative array to get fields array */
207 function Fields($colname)
210 $this->bind = array();
211 for ($i = 0; $i < $this->_numOfFields; $i++) {
212 $o = $this->FetchField($i);
213 $this->bind[strtoupper($o->name)] = $i;
217 return $this->fields[$this->bind[strtoupper($colname)]];
222 $this->_numOfRows = -1;
223 $this->_numOfFields = @ora_numcols($this->_queryID);
231 function _fetch($ignore_fields = false)
233 if ($this->fetchMode & ADODB_FETCH_ASSOC)
234 return @ora_fetch_into($this->_queryID, $this->fields, ORA_FETCHINTO_NULLS | ORA_FETCHINTO_ASSOC);
236 return @ora_fetch_into($this->_queryID, $this->fields, ORA_FETCHINTO_NULLS);
239 /* close() only needs to be called if you are worried about using too much memory while your script
240 is running. All associated result memory for the specified result identifier will automatically be freed. */
244 return @ora_close($this->_queryID);
247 function MetaType($t, $len = -1)
251 $t = $fieldobj->type;
252 $len = $fieldobj->max_length;
255 switch (strtoupper($t)) {
261 if ($len <= $this->blobSize) return 'C';
267 case 'LONG VARBINARY':
274 //case 'T': return 'T';