Changeset 34 for trunk/phpgwapi/inc/adodb/drivers/adodb-oci8.inc.php
- Timestamp:
- 06/29/07 15:17:46 (17 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
trunk/phpgwapi/inc/adodb/drivers/adodb-oci8.inc.php
r2 r34 2 2 /* 3 3 4 version V4. 50 6 July 2004 (c) 2000-2004John Lim. All rights reserved.4 version V4.94 23 Jan 2007 (c) 2000-2007 John Lim. All rights reserved. 5 5 6 6 Released under both BSD license and Lesser GPL library license. … … 64 64 var $_commit = OCI_COMMIT_ON_SUCCESS; 65 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') ";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 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 68 var $_bindInputArray = true; … … 76 76 var $connectSID = false; 77 77 var $_bind = false; 78 var $_nestedSQL = true; 78 79 var $_hasOCIFetchStatement = false; 79 80 var $_getarray = false; // currently not working … … 83 84 var $selectOffsetAlg1 = 100; // when to use 1st algorithm of selectlimit. 84 85 var $NLS_DATE_FORMAT = 'YYYY-MM-DD'; // To include time, use 'RRRR-MM-DD HH24:MI:SS' 86 var $dateformat = 'YYYY-MM-DD'; // for DBDate() 85 87 var $useDBDateFormatForTextInput=false; 86 88 var $datetime = false; // MetaType('DATE') returns 'D' (datetime==false) or 'T' (datetime == true) … … 100 102 global $ADODB_FETCH_MODE; 101 103 104 $false = false; 102 105 $save = $ADODB_FETCH_MODE; 103 106 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; … … 108 111 if (isset($savem)) $this->SetFetchMode($savem); 109 112 $ADODB_FETCH_MODE = $save; 110 if (!$rs) return false; 113 if (!$rs) { 114 return $false; 115 } 111 116 $retarr = array(); 112 117 while (!$rs->EOF) { //print_r($rs->fields); … … 116 121 $fld->max_length = $rs->fields[2]; 117 122 $fld->scale = $rs->fields[3]; 118 if ($rs->fields[1] == 'NUMBER' && $rs->fields[3] == 0) {119 $fld->type ='INT';123 if ($rs->fields[1] == 'NUMBER') { 124 if ($rs->fields[3] == 0) $fld->type = 'INT'; 120 125 $fld->max_length = $rs->fields[4]; 121 126 } … … 129 134 } 130 135 $rs->Close(); 131 return $retarr; 136 if (empty($retarr)) 137 return $false; 138 else 139 return $retarr; 132 140 } 133 141 … … 190 198 $argHostport=$argHostinfo[1]; 191 199 } else { 192 $argHostport ="1521";200 $argHostport = empty($this->port)? "1521" : $this->port; 193 201 } 194 202 … … 204 212 //if ($argHostname) print "<p>Connect: 1st argument should be left blank for $this->databaseType</p>"; 205 213 if ($mode==1) { 206 $this->_connectionID = OCIPLogon($argUsername,$argPassword, $argDatabasename); 214 $this->_connectionID = ($this->charSet) ? 215 OCIPLogon($argUsername,$argPassword, $argDatabasename,$this->charSet) 216 : 217 OCIPLogon($argUsername,$argPassword, $argDatabasename) 218 ; 207 219 if ($this->_connectionID && $this->autoRollback) OCIrollback($this->_connectionID); 208 220 } else if ($mode==2) { 209 $this->_connectionID = OCINLogon($argUsername,$argPassword, $argDatabasename); 221 $this->_connectionID = ($this->charSet) ? 222 OCINLogon($argUsername,$argPassword, $argDatabasename,$this->charSet) 223 : 224 OCINLogon($argUsername,$argPassword, $argDatabasename); 225 210 226 } else { 211 $this->_connectionID = OCILogon($argUsername,$argPassword, $argDatabasename); 212 } 213 if ($this->_connectionID === false) return false; 227 $this->_connectionID = ($this->charSet) ? 228 OCILogon($argUsername,$argPassword, $argDatabasename,$this->charSet) 229 : 230 OCILogon($argUsername,$argPassword, $argDatabasename); 231 } 232 if (!$this->_connectionID) return false; 214 233 if ($this->_initdate) { 215 234 $this->Execute("ALTER SESSION SET NLS_DATE_FORMAT='".$this->NLS_DATE_FORMAT."'"); … … 259 278 260 279 if (is_string($d)) $d = ADORecordSet::UnixDate($d); 261 return "TO_DATE(".adodb_date($this->fmtDate,$d).",'".$this->NLS_DATE_FORMAT."')"; 262 } 263 280 return "TO_DATE(".adodb_date($this->fmtDate,$d).",'".$this->dateformat."')"; 281 } 282 283 function BindDate($d) 284 { 285 $d = ADOConnection::DBDate($d); 286 if (strncmp($d,"'",1)) return $d; 287 288 return substr($d,1,strlen($d)-2); 289 } 290 291 function BindTimeStamp($d) 292 { 293 $d = ADOConnection::DBTimeStamp($d); 294 if (strncmp($d,"'",1)) return $d; 295 296 return substr($d,1,strlen($d)-2); 297 } 264 298 265 299 // format and return date string in database timestamp format … … 268 302 if (empty($ts) && $ts !== 0) return 'null'; 269 303 if (is_string($ts)) $ts = ADORecordSet::UnixTimeStamp($ts); 270 return 'TO_DATE('.adodb_date( $this->fmtTimeStamp,$ts).",'RRRR-MM-DD, HH:MI:SS AM')";271 } 272 273 function RowLock($tables,$where )304 return 'TO_DATE('.adodb_date("'Y-m-d H:i:s'",$ts).",'RRRR-MM-DD, HH24:MI:SS')"; 305 } 306 307 function RowLock($tables,$where,$flds='1 as ignore') 274 308 { 275 309 if ($this->autoCommit) $this->BeginTrans(); 276 return $this->GetOne("select 1 as ignorefrom $tables where $where for update");310 return $this->GetOne("select $flds from $tables where $where for update"); 277 311 } 278 312 … … 282 316 $save = $this->metaTablesSQL; 283 317 $mask = $this->qstr(strtoupper($mask)); 284 $this->metaTablesSQL .= " AND table_namelike $mask";318 $this->metaTablesSQL .= " AND upper(table_name) like $mask"; 285 319 } 286 320 $ret =& ADOConnection::MetaTables($ttype,$showSchema); … … 290 324 } 291 325 return $ret; 326 } 327 328 // Mark Newnham 329 function &MetaIndexes ($table, $primary = FALSE, $owner=false) 330 { 331 // save old fetch mode 332 global $ADODB_FETCH_MODE; 333 334 $save = $ADODB_FETCH_MODE; 335 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 336 337 if ($this->fetchMode !== FALSE) { 338 $savem = $this->SetFetchMode(FALSE); 339 } 340 341 // get index details 342 $table = strtoupper($table); 343 344 // get Primary index 345 $primary_key = ''; 346 347 $false = false; 348 $rs = $this->Execute(sprintf("SELECT * FROM ALL_CONSTRAINTS WHERE UPPER(TABLE_NAME)='%s' AND CONSTRAINT_TYPE='P'",$table)); 349 if ($row = $rs->FetchRow()) 350 $primary_key = $row[1]; //constraint_name 351 352 if ($primary==TRUE && $primary_key=='') { 353 if (isset($savem)) 354 $this->SetFetchMode($savem); 355 $ADODB_FETCH_MODE = $save; 356 return $false; //There is no primary key 357 } 358 359 $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)); 360 361 362 if (!is_object($rs)) { 363 if (isset($savem)) 364 $this->SetFetchMode($savem); 365 $ADODB_FETCH_MODE = $save; 366 return $false; 367 } 368 369 $indexes = array (); 370 // parse index data into array 371 372 while ($row = $rs->FetchRow()) { 373 if ($primary && $row[0] != $primary_key) continue; 374 if (!isset($indexes[$row[0]])) { 375 $indexes[$row[0]] = array( 376 'unique' => ($row[1] == 'UNIQUE'), 377 'columns' => array() 378 ); 379 } 380 $indexes[$row[0]]['columns'][$row[2] - 1] = $row[3]; 381 } 382 383 // sort columns by order in the index 384 foreach ( array_keys ($indexes) as $index ) { 385 ksort ($indexes[$index]['columns']); 386 } 387 388 if (isset($savem)) { 389 $this->SetFetchMode($savem); 390 $ADODB_FETCH_MODE = $save; 391 } 392 return $indexes; 292 393 } 293 394 … … 298 399 $this->autoCommit = false; 299 400 $this->_commit = OCI_DEFAULT; 401 402 if ($this->_transmode) $this->Execute("SET TRANSACTION ".$this->_transmode); 300 403 return true; 301 404 } … … 333 436 if ($this->_errorMsg !== false) return $this->_errorMsg; 334 437 335 if (is_resource($this->_stmt)) $arr = @OCI error($this->_stmt);438 if (is_resource($this->_stmt)) $arr = @OCIError($this->_stmt); 336 439 if (empty($arr)) { 337 $arr = @OCIerror($this->_connectionID);338 if ($arr === false)$arr = @OCIError();440 if (is_resource($this->_connectionID)) $arr = @OCIError($this->_connectionID); 441 else $arr = @OCIError(); 339 442 if ($arr === false) return ''; 340 443 } … … 413 516 break; 414 517 518 case 'w': 519 $s .= 'D'; 520 break; 521 522 case 'l': 523 $s .= 'DAY'; 524 break; 525 526 case 'W': 527 $s .= 'WW'; 528 break; 529 415 530 default: 416 531 // handle escape characters... … … 458 573 //$inputarr['adodb_rownum'] = $nrows; 459 574 if ($this->databaseType == 'oci8po') { 460 $sql = "select * from ( $sql) where rownum <= ?";575 $sql = "select * from (".$sql.") where rownum <= ?"; 461 576 } else { 462 $sql = "select * from ( $sql) where rownum <= :adodb_offset";577 $sql = "select * from (".$sql.") where rownum <= :adodb_offset"; 463 578 } 464 579 $inputarr['adodb_offset'] = $nrows; … … 474 589 475 590 // Let Oracle return the name of the columns 476 $q_fields = "SELECT * FROM ($sql) WHERE NULL = NULL"; 477 if (!$stmt = OCIParse($this->_connectionID, $q_fields)) { 478 return false; 479 } 591 $q_fields = "SELECT * FROM (".$sql.") WHERE NULL = NULL"; 592 593 $false = false; 594 if (! $stmt_arr = $this->Prepare($q_fields)) { 595 return $false; 596 } 597 $stmt = $stmt_arr[1]; 480 598 481 599 if (is_array($inputarr)) { … … 500 618 if (!OCIExecute($stmt, OCI_DEFAULT)) { 501 619 OCIFreeStatement($stmt); 502 return false;620 return $false; 503 621 } 504 622 … … 615 733 } 616 734 735 /** 736 * Execute SQL 737 * 738 * @param sql SQL statement to execute, or possibly an array holding prepared statement ($sql[0] will hold sql text) 739 * @param [inputarr] holds the input data to bind to. Null elements will be set to null. 740 * @return RecordSet or false 741 */ 742 function &Execute($sql,$inputarr=false) 743 { 744 if ($this->fnExecute) { 745 $fn = $this->fnExecute; 746 $ret =& $fn($this,$sql,$inputarr); 747 if (isset($ret)) return $ret; 748 } 749 if ($inputarr) { 750 #if (!is_array($inputarr)) $inputarr = array($inputarr); 751 752 $element0 = reset($inputarr); 753 754 # is_object check because oci8 descriptors can be passed in 755 if (is_array($element0) && !is_object(reset($element0))) { 756 if (is_string($sql)) 757 $stmt = $this->Prepare($sql); 758 else 759 $stmt = $sql; 760 761 foreach($inputarr as $arr) { 762 $ret =& $this->_Execute($stmt,$arr); 763 if (!$ret) return $ret; 764 } 765 } else { 766 $ret =& $this->_Execute($sql,$inputarr); 767 } 768 769 } else { 770 $ret =& $this->_Execute($sql,false); 771 } 772 773 return $ret; 774 } 617 775 618 776 /* … … 627 785 $stmt = OCIParse($this->_connectionID,$sql); 628 786 629 if (!$stmt) return false; 630 787 if (!$stmt) { 788 $this->_errorMsg = false; 789 $this->_errorCode = false; 790 $arr = @OCIError($this->_connectionID); 791 if ($arr === false) return false; 792 793 $this->_errorMsg = $arr['message']; 794 $this->_errorCode = $arr['code']; 795 return false; 796 } 797 631 798 $BINDNUM += 1; 632 799 633 if (@OCIStatementType($stmt) == 'BEGIN') { 800 $sttype = @OCIStatementType($stmt); 801 if ($sttype == 'BEGIN' || $sttype == 'DECLARE') { 634 802 return array($sql,$stmt,0,$BINDNUM, ($cursor) ? OCINewCursor($this->_connectionID) : false); 635 } 636 803 } 637 804 return array($sql,$stmt,0,$BINDNUM); 638 805 } 639 806 640 807 /* 641 Call an oracle stored procedure and return a cursor variable. 642 Convert the cursor variable into a recordset. 808 Call an oracle stored procedure and returns a cursor variable as a recordset. 643 809 Concept by Robert Tuttle robert@ud.com 644 810 … … 655 821 function &ExecuteCursor($sql,$cursorName='rs',$params=false) 656 822 { 657 $stmt = ADODB_oci8::Prepare($sql,true); # true to allocate OCINewCursor 658 823 if (is_array($sql)) $stmt = $sql; 824 else $stmt = ADODB_oci8::Prepare($sql,true); # true to allocate OCINewCursor 825 659 826 if (is_array($stmt) && sizeof($stmt) >= 5) { 827 $hasref = true; 828 $ignoreCur = false; 660 829 $this->Parameter($stmt, $ignoreCur, $cursorName, false, -1, OCI_B_CURSOR); 661 830 if ($params) { … … 664 833 } 665 834 } 666 } 667 return $this->Execute($stmt); 835 } else 836 $hasref = false; 837 838 $rs =& $this->Execute($stmt); 839 if ($rs) { 840 if ($rs->databaseType == 'array') OCIFreeCursor($stmt[4]); 841 else if ($hasref) $rs->_refcursor = $stmt[4]; 842 } 843 return $rs; 668 844 } 669 845 … … 716 892 } 717 893 //we have to create a new Descriptor here 718 $numlob = count($this ->_refLOBs);719 $this ->_refLOBs[$numlob]['LOB'] = OCINewDescriptor($this->_connectionID, oci_lob_desc($type));720 $this ->_refLOBs[$numlob]['TYPE'] = $isOutput;721 722 $tmp = &$this ->_refLOBs[$numlob]['LOB'];894 $numlob = count($this->_refLOBs); 895 $this->_refLOBs[$numlob]['LOB'] = OCINewDescriptor($this->_connectionID, oci_lob_desc($type)); 896 $this->_refLOBs[$numlob]['TYPE'] = $isOutput; 897 898 $tmp = &$this->_refLOBs[$numlob]['LOB']; 723 899 $rez = OCIBindByName($stmt[1], ":".$name, $tmp, -1, $type); 724 900 if ($this->debug) { 725 ADOConnection::outp("<b>Bind</b>: descriptor has been allocated, var binded");901 ADOConnection::outp("<b>Bind</b>: descriptor has been allocated, var (".$name.") binded"); 726 902 } 727 903 728 904 // if type is input then write data to lob now 729 905 if ($isOutput == false) { 730 $var = $this -> BlobEncode($var); 731 $tmp -> WriteTemporary($var); 906 $var = $this->BlobEncode($var); 907 $tmp->WriteTemporary($var); 908 $this->_refLOBs[$numlob]['VAR'] = &$var; 732 909 if ($this->debug) { 733 910 ADOConnection::outp("<b>Bind</b>: LOB has been written to temp"); 734 911 } 735 912 } else { 736 $this ->_refLOBs[$numlob]['VAR'] = &$var;913 $this->_refLOBs[$numlob]['VAR'] = &$var; 737 914 } 738 915 $rez = $tmp; … … 791 968 792 969 4. $db->prepare('insert into table (a,b,c) values (:0,:1,:2)'); 793 $db-> $bind($stmt,1); $db->bind($stmt,2); $db->bind($stmt,3);970 $db->bind($stmt,1); $db->bind($stmt,2); $db->bind($stmt,3); 794 971 $db->execute($stmt); 795 972 */ 796 973 function _query($sql,$inputarr) 797 974 { 798 799 975 if (is_array($sql)) { // is prepared sql 800 976 $stmt = $sql[1]; … … 812 988 foreach($inputarr as $k => $v) { 813 989 $bindarr[$k] = $v; 814 OCIBindByName($stmt,":$k",$bindarr[$k], 4000);990 OCIBindByName($stmt,":$k",$bindarr[$k],is_string($v) && strlen($v)>4000 ? -1 : 4000); 815 991 } 816 992 $this->_bind[$bindpos] = &$bindarr; … … 861 1037 //$_GLOBALS[$this -> _refLOBs[$key]['VAR']] = $tmp; 862 1038 $this -> _refLOBs[$key]['VAR'] = $tmp; 863 } 864 $this -> _refLOBs[$key]['LOB'] -> free(); 865 unset($this -> _refLOBs[$key]); 1039 } else { 1040 $this->_refLOBs[$key]['LOB']->save($this->_refLOBs[$key]['VAR']); 1041 $this -> _refLOBs[$key]['LOB']->free(); 1042 unset($this -> _refLOBs[$key]); 1043 if ($this->debug) { 1044 ADOConnection::outp("<b>IN LOB</b>: LOB has been saved. <br>"); 1045 } 1046 } 866 1047 } 867 1048 } … … 870 1051 case "SELECT": 871 1052 return $stmt; 872 1053 1054 case 'DECLARE': 873 1055 case "BEGIN": 874 1056 if (is_array($sql) && !empty($sql[4])) { … … 901 1083 902 1084 if (!$this->autoCommit) OCIRollback($this->_connectionID); 903 if (count($this ->_refLOBs) > 0) {904 foreach ($this -> 905 $this -> _refLOBs[$key] ->free();906 unset($this ->_refLOBs[$key]);1085 if (count($this->_refLOBs) > 0) { 1086 foreach ($this ->_refLOBs as $key => $value) { 1087 $this->_refLOBs[$key]['LOB']->free(); 1088 unset($this->_refLOBs[$key]); 907 1089 } 908 1090 } … … 1010 1192 function qstr($s,$magic_quotes=false) 1011 1193 { 1012 $nofixquotes=false;1194 //$nofixquotes=false; 1013 1195 1014 1196 if ($this->noNullStrings && strlen($s)==0)$s = ' '; … … 1039 1221 var $bind=false; 1040 1222 var $_fieldobjs; 1223 1041 1224 //var $_arr = false; 1042 1225 … … 1049 1232 switch ($mode) 1050 1233 { 1051 default:1052 case ADODB_FETCH_NUM: $this->fetchMode = OCI_NUM+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;1053 1234 case ADODB_FETCH_ASSOC:$this->fetchMode = OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break; 1054 1235 case ADODB_FETCH_DEFAULT: 1055 1236 case ADODB_FETCH_BOTH:$this->fetchMode = OCI_NUM+OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break; 1056 } 1057 1237 case ADODB_FETCH_NUM: 1238 default: 1239 $this->fetchMode = OCI_NUM+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break; 1240 } 1241 1242 $this->adodbFetchMode = $mode; 1058 1243 $this->_queryID = $queryID; 1059 1244 } … … 1159 1344 } 1160 1345 1346 /* 1347 # does not work as first record is retrieved in _initrs(), so is not included in GetArray() 1348 function &GetArray($nRows = -1) 1349 { 1350 global $ADODB_OCI8_GETARRAY; 1351 1352 if (true || !empty($ADODB_OCI8_GETARRAY)) { 1353 # does not support $ADODB_ANSI_PADDING_OFF 1354 1355 //OCI_RETURN_NULLS and OCI_RETURN_LOBS is set by OCIfetchstatement 1356 switch($this->adodbFetchMode) { 1357 case ADODB_FETCH_NUM: 1358 1359 $ncols = @OCIfetchstatement($this->_queryID, $results, 0, $nRows, OCI_FETCHSTATEMENT_BY_ROW+OCI_NUM); 1360 $results = array_merge(array($this->fields),$results); 1361 return $results; 1362 1363 case ADODB_FETCH_ASSOC: 1364 if (ADODB_ASSOC_CASE != 2 || $this->databaseType != 'oci8') break; 1365 1366 $ncols = @OCIfetchstatement($this->_queryID, $assoc, 0, $nRows, OCI_FETCHSTATEMENT_BY_ROW); 1367 $results =& array_merge(array($this->fields),$assoc); 1368 return $results; 1369 1370 default: 1371 break; 1372 } 1373 } 1374 1375 $results =& ADORecordSet::GetArray($nRows); 1376 return $results; 1377 1378 } */ 1161 1379 1162 1380 /* Optimize SelectLimit() by using OCIFetch() instead of OCIFetchInto() */ … … 1167 1385 return $arr; 1168 1386 } 1387 $arr = array(); 1169 1388 for ($i=1; $i < $offset; $i++) 1170 if (!@OCIFetch($this->_queryID)) return array();1171 1172 if (!@OCIfetchinto($this->_queryID,$this->fields,$this->fetchMode)) return array();1389 if (!@OCIFetch($this->_queryID)) return $arr; 1390 1391 if (!@OCIfetchinto($this->_queryID,$this->fields,$this->fetchMode)) return $arr;; 1173 1392 $results = array(); 1174 1393 $cnt = 0; … … 1214 1433 { 1215 1434 if ($this->connection->_stmt === $this->_queryID) $this->connection->_stmt = false; 1216 OCIFreeStatement($this->_queryID); 1435 if (!empty($this->_refcursor)) { 1436 OCIFreeCursor($this->_refcursor); 1437 $this->_refcursor = false; 1438 } 1439 @OCIFreeStatement($this->_queryID); 1217 1440 $this->_queryID = false; 1218 1441 … … 1273 1496 switch ($mode) 1274 1497 { 1275 default:1276 case ADODB_FETCH_NUM: $this->fetchMode = OCI_NUM+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;1277 1498 case ADODB_FETCH_ASSOC:$this->fetchMode = OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break; 1278 1499 case ADODB_FETCH_DEFAULT: 1279 1500 case ADODB_FETCH_BOTH:$this->fetchMode = OCI_NUM+OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break; 1280 } 1281 1501 case ADODB_FETCH_NUM: 1502 default: $this->fetchMode = OCI_NUM+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break; 1503 } 1504 $this->adodbFetchMode = $mode; 1282 1505 $this->_queryID = $queryID; 1283 1506 }
Note: See TracChangeset
for help on using the changeset viewer.