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 {
17 var $databaseType = "oracle";
18 var $replaceQuote = "''"; // string to use to replace quotes
19 var $concat_operator='||';
21 var $_initdate = true; // init date to YYYY-MM-DD
22 var $metaTablesSQL = 'select table_name from cat';
23 var $metaColumnsSQL = "select cname,coltype,width from col where tname='%s' order by colno";
24 var $sysDate = "TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')";
25 var $sysTimeStamp = 'SYSDATE';
26 var $connectSID = true;
28 function ADODB_oracle()
32 // format and return date string in database date format
35 if (is_string($d)) $d = ADORecordSet::UnixDate($d);
36 return 'TO_DATE('.adodb_date($this->fmtDate,$d).",'YYYY-MM-DD')";
39 // format and return date string in database timestamp format
40 function DBTimeStamp($ts)
42 if (is_string($ts)) $d = ADORecordSet::UnixTimeStamp($ts);
43 return 'TO_DATE('.adodb_date($this->fmtTimeStamp,$ts).",'RRRR-MM-DD, HH:MI:SS AM')";
48 $this->autoCommit = false;
49 ora_commitoff($this->_connectionID);
53 function CommitTrans($ok=true)
55 if (!$ok) return $this->RollbackTrans();
56 $ret = ora_commit($this->_connectionID);
57 ora_commiton($this->_connectionID);
61 function RollbackTrans()
63 $ret = ora_rollback($this->_connectionID);
64 ora_commiton($this->_connectionID);
68 /* there seems to be a bug in the oracle extension -- always returns ORA-00000 - no error */
71 $this->_errorMsg = @ora_error($this->_curs);
72 if (!$this->_errorMsg) $this->_errorMsg = @ora_error($this->_connectionID);
73 return $this->_errorMsg;
78 $err = @ora_errorcode($this->_curs);
79 if (!$err) return @ora_errorcode($this->_connectionID);
82 // returns true or false
83 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename, $mode=0)
85 // G. Giunta 2003/08/13 - This looks danegrously suspicious: why should we want to set
86 // the oracle home to the host name of remote DB?
87 // if ($argHostname) putenv("ORACLE_HOME=$argHostname");
89 if($argHostname) { // code copied from version submitted for oci8 by Jorma Tuomainen <jorma.tuomainen@ppoy.fi>
90 if (empty($argDatabasename)) $argDatabasename = $argHostname;
92 if(strpos($argHostname,":")) {
93 $argHostinfo=explode(":",$argHostname);
94 $argHostname=$argHostinfo[0];
95 $argHostport=$argHostinfo[1];
100 if ($this->connectSID) {
101 $argDatabasename="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=".$argHostname
102 .")(PORT=$argHostport))(CONNECT_DATA=(SID=$argDatabasename)))";
104 $argDatabasename="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=".$argHostname
105 .")(PORT=$argHostport))(CONNECT_DATA=(SERVICE_NAME=$argDatabasename)))";
110 if ($argDatabasename) $argUsername .= "@$argDatabasename";
112 //if ($argHostname) print "<p>Connect: 1st argument should be left blank for $this->databaseType</p>";
114 $this->_connectionID = ora_plogon($argUsername,$argPassword);
116 $this->_connectionID = ora_logon($argUsername,$argPassword);
117 if ($this->_connectionID === false) return false;
118 if ($this->autoCommit) ora_commiton($this->_connectionID);
119 if ($this->_initdate) {
120 $rs = $this->_query("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD'");
121 if ($rs) ora_close($rs);
127 // returns true or false
128 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
130 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename, 1);
133 // returns query ID if successful, otherwise false
134 function _query($sql,$inputarr=false)
136 $curs = ora_open($this->_connectionID);
138 if ($curs === false) return false;
139 $this->_curs = $curs;
140 if (!ora_parse($curs,$sql)) return false;
141 if (ora_exec($curs)) return $curs;
147 // returns true or false
150 return @ora_logoff($this->_connectionID);
155 /*--------------------------------------------------------------------------------------
156 Class Name: Recordset
157 --------------------------------------------------------------------------------------*/
159 class ADORecordset_oracle extends ADORecordSet {
161 var $databaseType = "oracle";
164 function ADORecordset_oracle($queryID,$mode=false)
167 if ($mode === false) {
168 global $ADODB_FETCH_MODE;
169 $mode = $ADODB_FETCH_MODE;
171 $this->fetchMode = $mode;
173 $this->_queryID = $queryID;
175 $this->_inited = true;
176 $this->fields = array();
178 $this->_currentRow = 0;
179 $this->EOF = !$this->_fetch();
182 $this->_numOfRows = 0;
183 $this->_numOfFields = 0;
187 return $this->_queryID;
190 /* Returns: an object containing field information.
191 Get column information in the Recordset object. fetchField() can be used in order to obtain information about
192 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
193 fetchField() is retrieved. */
195 function FetchField($fieldOffset = -1)
197 $fld = new ADOFieldObject;
198 $fld->name = ora_columnname($this->_queryID, $fieldOffset);
199 $fld->type = ora_columntype($this->_queryID, $fieldOffset);
200 $fld->max_length = ora_columnsize($this->_queryID, $fieldOffset);
204 /* Use associative array to get fields array */
205 function Fields($colname)
208 $this->bind = array();
209 for ($i=0; $i < $this->_numOfFields; $i++) {
210 $o = $this->FetchField($i);
211 $this->bind[strtoupper($o->name)] = $i;
215 return $this->fields[$this->bind[strtoupper($colname)]];
220 $this->_numOfRows = -1;
221 $this->_numOfFields = @ora_numcols($this->_queryID);
229 function _fetch($ignore_fields=false) {
230 if ($this->fetchMode & ADODB_FETCH_ASSOC)
231 return @ora_fetch_into($this->_queryID,$this->fields,ORA_FETCHINTO_NULLS|ORA_FETCHINTO_ASSOC);
233 return @ora_fetch_into($this->_queryID,$this->fields,ORA_FETCHINTO_NULLS);
236 /* close() only needs to be called if you are worried about using too much memory while your script
237 is running. All associated result memory for the specified result identifier will automatically be freed. */
241 return @ora_close($this->_queryID);
244 function MetaType($t,$len=-1)
248 $t = $fieldobj->type;
249 $len = $fieldobj->max_length;
252 switch (strtoupper($t)) {
258 if ($len <= $this->blobSize) return 'C';
264 case 'LONG VARBINARY':
268 case 'DATE': return 'D';
270 //case 'T': return 'T';
272 case 'BIT': return 'L';
275 case 'INTEGER': return 'I';