Changeset 34 for trunk/phpgwapi/inc/adodb/drivers/adodb-db2.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-db2.inc.php
r2 r34 1 1 <?php 2 2 /* 3 V4.51 29 July 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. 7 Set tabs to 4 for best viewing. 8 9 Latest version is available at http://adodb.sourceforge.net 10 11 DB2 data driver. Requires ODBC. 12 13 From phpdb list: 14 15 Hi Andrew, 16 17 thanks a lot for your help. Today we discovered what 18 our real problem was: 19 20 After "playing" a little bit with the php-scripts that try 21 to connect to the IBM DB2, we set the optional parameter 22 Cursortype when calling odbc_pconnect(....). 23 24 And the exciting thing: When we set the cursor type 25 to SQL_CUR_USE_ODBC Cursor Type, then 26 the whole query speed up from 1 till 10 seconds 27 to 0.2 till 0.3 seconds for 100 records. Amazing!!! 28 29 Therfore, PHP is just almost fast as calling the DB2 30 from Servlets using JDBC (don't take too much care 31 about the speed at whole: the database was on a 32 completely other location, so the whole connection 33 was made over a slow network connection). 34 35 I hope this helps when other encounter the same 36 problem when trying to connect to DB2 from 37 PHP. 38 39 Kind regards, 40 Christian Szardenings 41 42 2 Oct 2001 43 Mark Newnham has discovered that the SQL_CUR_USE_ODBC is not supported by 44 IBM's DB2 ODBC driver, so this must be a 3rd party ODBC driver. 45 46 From the IBM CLI Reference: 47 48 SQL_ATTR_ODBC_CURSORS (DB2 CLI v5) 49 This connection attribute is defined by ODBC, but is not supported by DB2 50 CLI. Any attempt to set or get this attribute will result in an SQLSTATE of 51 HYC00 (Driver not capable). 52 53 A 32-bit option specifying how the Driver Manager uses the ODBC cursor 54 library. 55 56 So I guess this means the message [above] was related to using a 3rd party 57 odbc driver. 58 59 Setting SQL_CUR_USE_ODBC 60 ======================== 61 To set SQL_CUR_USE_ODBC for drivers that require it, do this: 62 63 $db = NewADOConnection('db2'); 64 $db->curMode = SQL_CUR_USE_ODBC; 65 $db->Connect($dsn, $userid, $pwd); 66 67 68 69 USING CLI INTERFACE 70 =================== 71 72 I have had reports that the $host and $database params have to be reversed in 73 Connect() when using the CLI interface. From Halmai Csongor csongor.halmai#nexum.hu: 74 75 > The symptom is that if I change the database engine from postgres or any other to DB2 then the following 76 > connection command becomes wrong despite being described this version to be correct in the docs. 77 > 78 > $connection_object->Connect( $DATABASE_HOST, $DATABASE_AUTH_USER_NAME, $DATABASE_AUTH_PASSWORD, $DATABASE_NAME ) 79 > 80 > In case of DB2 I had to swap the first and last arguments in order to connect properly. 81 82 3 V4.94 23 Jan 2007 (c) 2006 John Lim (jlim#natsoft.com.my). All rights reserved. 4 5 This is a version of the ADODB driver for DB2. It uses the 'ibm_db2' PECL extension 6 for PHP (http://pecl.php.net/package/ibm_db2), which in turn requires DB2 V8.2.2 or 7 higher. 8 9 Originally tested with PHP 5.1.1 and Apache 2.0.55 on Windows XP SP2. 10 More recently tested with PHP 5.1.2 and Apache 2.0.55 on Windows XP SP2. 11 12 This file was ported from "adodb-odbc.inc.php" by Larry Menard, "larry.menard#rogers.com". 13 I ripped out what I believed to be a lot of redundant or obsolete code, but there are 14 probably still some remnants of the ODBC support in this file; I'm relying on reviewers 15 of this code to point out any other things that can be removed. 83 16 */ 84 17 … … 86 19 if (!defined('ADODB_DIR')) die(); 87 20 88 if (!defined('_ADODB_ODBC_LAYER')) { 89 include(ADODB_DIR."/drivers/adodb-odbc.inc.php");90 } 91 if (!defined('ADODB_DB2')){ 92 define('ADODB_DB2',1); 93 94 class ADODB_ DB2 extends ADODB_odbc{21 define("_ADODB_DB2_LAYER", 2 ); 22 23 /*-------------------------------------------------------------------------------------- 24 --------------------------------------------------------------------------------------*/ 25 26 27 class ADODB_db2 extends ADOConnection { 95 28 var $databaseType = "db2"; 29 var $fmtDate = "'Y-m-d'"; 96 30 var $concat_operator = '||'; 97 var $sysDate = 'CURRENT_DATE'; 31 32 var $sysTime = 'CURRENT TIME'; 33 var $sysDate = 'CURRENT DATE'; 98 34 var $sysTimeStamp = 'CURRENT TIMESTAMP'; 99 // The complete string representation of a timestamp has the form 100 // yyyy-mm-dd-hh.mm.ss.nnnnnn. 101 var $fmtTimeStamp = "'Y-m-d-H.i.s'"; 102 var $ansiOuter = true; 103 var $identitySQL = 'values IDENTITY_VAL_LOCAL()'; 104 var $_bindInputArray = true; 105 var $hasInsertID = true; 106 107 function ADODB_DB2() 108 { 109 if (strncmp(PHP_OS,'WIN',3) === 0) $this->curmode = SQL_CUR_USE_ODBC; 110 $this->ADODB_odbc(); 111 } 112 113 function IfNull( $field, $ifNull ) 114 { 115 return " COALESCE($field, $ifNull) "; // if DB2 UDB 116 } 117 118 function ServerInfo() 119 { 120 //odbc_setoption($this->_connectionID,1,101 /*SQL_ATTR_ACCESS_MODE*/, 1 /*SQL_MODE_READ_ONLY*/); 121 $vers = $this->GetOne('select versionnumber from sysibm.sysversions'); 122 //odbc_setoption($this->_connectionID,1,101, 0 /*SQL_MODE_READ_WRITE*/); 123 return array('description'=>'DB2 ODBC driver', 'version'=>$vers); 124 } 125 126 function _insertid() 127 { 128 return $this->GetOne($this->identitySQL); 129 } 130 131 function RowLock($tables,$where) 132 { 133 if ($this->_autocommit) $this->BeginTrans(); 134 return $this->GetOne("select 1 as ignore from $tables where $where for update"); 135 } 136 137 function &MetaTables($ttype=false,$showSchema=false, $qtable="%", $qschema="%") 138 { 139 global $ADODB_FETCH_MODE; 140 141 $savem = $ADODB_FETCH_MODE; 142 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 143 $qid = odbc_tables($this->_connectionID, "", $qschema, $qtable, ""); 144 145 $rs = new ADORecordSet_odbc($qid); 146 147 $ADODB_FETCH_MODE = $savem; 148 if (!$rs) return false; 149 150 $rs->_has_stupid_odbc_fetch_api_change = $this->_has_stupid_odbc_fetch_api_change; 151 152 $arr =& $rs->GetArray(); 153 //print_r($arr); 154 155 $rs->Close(); 156 $arr2 = array(); 157 158 if ($ttype) { 159 $isview = strncmp($ttype,'V',1) === 0; 160 } 161 for ($i=0; $i < sizeof($arr); $i++) { 162 163 if (!$arr[$i][2]) continue; 164 if (strncmp($arr[$i][1],'SYS',3) === 0) continue; 165 166 $type = $arr[$i][3]; 167 168 if ($showSchema) $arr[$i][2] = $arr[$i][1].'.'.$arr[$i][2]; 169 170 if ($ttype) { 171 if ($isview) { 172 if (strncmp($type,'V',1) === 0) $arr2[] = $arr[$i][2]; 173 } else if (strncmp($type,'T',1) === 0) $arr2[] = $arr[$i][2]; 174 } else if (strncmp($type,'S',1) !== 0) $arr2[] = $arr[$i][2]; 175 } 176 return $arr2; 177 } 178 35 36 var $fmtTimeStamp = "'Y-m-d-H:i:s'"; 37 var $replaceQuote = "''"; // string to use to replace quotes 38 var $dataProvider = "db2"; 39 var $hasAffectedRows = true; 40 41 var $binmode = DB2_BINARY; 42 43 var $useFetchArray = false; // setting this to true will make array elements in FETCH_ASSOC mode case-sensitive 44 // breaking backward-compat 45 var $_bindInputArray = false; 46 var $_genIDSQL = "VALUES NEXTVAL FOR %s"; 47 var $_genSeqSQL = "CREATE SEQUENCE %s START WITH 1 NO MAXVALUE NO CYCLE"; 48 var $_dropSeqSQL = "DROP SEQUENCE %s"; 49 var $_autocommit = true; 50 var $_haserrorfunctions = true; 51 var $_lastAffectedRows = 0; 52 var $uCaseTables = true; // for meta* functions, uppercase table names 53 var $hasInsertID = true; 54 55 function _insertid() 56 { 57 return ADOConnection::GetOne('VALUES IDENTITY_VAL_LOCAL()'); 58 } 59 60 function ADODB_db2() 61 { 62 $this->_haserrorfunctions = ADODB_PHPVER >= 0x4050; 63 } 64 65 // returns true or false 66 function _connect($argDSN, $argUsername, $argPassword, $argDatabasename) 67 { 68 global $php_errormsg; 69 70 if (!function_exists('db2_connect')) { 71 ADOConnection::outp("Warning: The old ODBC based DB2 driver has been renamed 'odbc_db2'. This ADOdb driver calls PHP's native db2 extension."); 72 return null; 73 } 74 // This needs to be set before the connect(). 75 // Replaces the odbc_binmode() call that was in Execute() 76 ini_set('ibm_db2.binmode', $this->binmode); 77 78 if ($argDatabasename) { 79 $this->_connectionID = db2_connect($argDatabasename,$argUsername,$argPassword); 80 } else { 81 $this->_connectionID = db2_connect($argDSN,$argUsername,$argPassword); 82 } 83 if (isset($php_errormsg)) $php_errormsg = ''; 84 85 // For db2_connect(), there is an optional 4th arg. If present, it must be 86 // an array of valid options. So far, we don't use them. 87 88 $this->_errorMsg = @db2_conn_errormsg(); 89 90 if (isset($this->connectStmt)) $this->Execute($this->connectStmt); 91 92 return $this->_connectionID != false; 93 } 94 95 // returns true or false 96 function _pconnect($argDSN, $argUsername, $argPassword, $argDatabasename) 97 { 98 global $php_errormsg; 99 100 if (!function_exists('db2_connect')) return null; 101 102 // This needs to be set before the connect(). 103 // Replaces the odbc_binmode() call that was in Execute() 104 ini_set('ibm_db2.binmode', $this->binmode); 105 106 if (isset($php_errormsg)) $php_errormsg = ''; 107 $this->_errorMsg = isset($php_errormsg) ? $php_errormsg : ''; 108 109 if ($argDatabasename) { 110 $this->_connectionID = db2_pconnect($argDatabasename,$argUsername,$argPassword); 111 } else { 112 $this->_connectionID = db2_pconnect($argDSN,$argUsername,$argPassword); 113 } 114 if (isset($php_errormsg)) $php_errormsg = ''; 115 116 $this->_errorMsg = @db2_conn_errormsg(); 117 if ($this->_connectionID && $this->autoRollback) @db2_rollback($this->_connectionID); 118 if (isset($this->connectStmt)) $this->Execute($this->connectStmt); 119 120 return $this->_connectionID != false; 121 } 122 123 // format and return date string in database timestamp format 124 function DBTimeStamp($ts) 125 { 126 if (empty($ts) && $ts !== 0) return 'null'; 127 if (is_string($ts)) $ts = ADORecordSet::UnixTimeStamp($ts); 128 return 'TO_DATE('.adodb_date($this->fmtTimeStamp,$ts).",'YYYY-MM-DD HH24:MI:SS')"; 129 } 179 130 180 131 // Format date column in sql string given an input format that understands Y M D … … 183 134 // use right() and replace() ? 184 135 if (!$col) $col = $this->sysDate; 136 137 /* use TO_CHAR() if $fmt is TO_CHAR() allowed fmt */ 138 if ($fmt== 'Y-m-d H:i:s') 139 return 'TO_CHAR('.$col.", 'YYYY-MM-DD HH24:MI:SS')"; 140 185 141 $s = ''; 186 142 187 143 $len = strlen($fmt); 188 144 for ($i=0; $i < $len; $i++) { 189 if ($s) $s .= '||';145 if ($s) $s .= $this->concat_operator; 190 146 $ch = $fmt[$i]; 191 147 switch($ch) { 192 148 case 'Y': 193 149 case 'y': 150 if ($len==1) return "year($col)"; 194 151 $s .= "char(year($col))"; 195 152 break; 196 153 case 'M': 154 if ($len==1) return "monthname($col)"; 197 155 $s .= "substr(monthname($col),1,3)"; 198 156 break; 199 157 case 'm': 158 if ($len==1) return "month($col)"; 200 159 $s .= "right(digits(month($col)),2)"; 201 160 break; 202 161 case 'D': 203 162 case 'd': 163 if ($len==1) return "day($col)"; 204 164 $s .= "right(digits(day($col)),2)"; 205 165 break; 206 166 case 'H': 207 167 case 'h': 168 if ($len==1) return "hour($col)"; 208 169 if ($col != $this->sysDate) $s .= "right(digits(hour($col)),2)"; 209 170 else $s .= "''"; … … 211 172 case 'i': 212 173 case 'I': 174 if ($len==1) return "minute($col)"; 213 175 if ($col != $this->sysDate) 214 176 $s .= "right(digits(minute($col)),2)"; … … 217 179 case 'S': 218 180 case 's': 181 if ($len==1) return "second($col)"; 219 182 if ($col != $this->sysDate) 220 183 $s .= "right(digits(second($col)),2)"; … … 233 196 234 197 235 function &SelectLimit($sql,$nrows=-1,$offset=-1,$inputArr=false) 236 { 237 if ($offset <= 0) { 238 // could also use " OPTIMIZE FOR $nrows ROWS " 239 if ($nrows >= 0) $sql .= " FETCH FIRST $nrows ROWS ONLY "; 240 $rs =& $this->Execute($sql,$inputArr); 198 function ServerInfo() 199 { 200 201 if (!empty($this->host) && ADODB_PHPVER >= 0x4300) { 202 $dsn = strtoupper($this->host); 203 $first = true; 204 $found = false; 205 206 if (!function_exists('db2_data_source')) return false; 207 208 while(true) { 209 210 $rez = @db2_data_source($this->_connectionID, 211 $first ? SQL_FETCH_FIRST : SQL_FETCH_NEXT); 212 $first = false; 213 if (!is_array($rez)) break; 214 if (strtoupper($rez['server']) == $dsn) { 215 $found = true; 216 break; 217 } 218 } 219 if (!$found) return ADOConnection::ServerInfo(); 220 if (!isset($rez['version'])) $rez['version'] = ''; 221 return $rez; 241 222 } else { 242 if ($offset > 0 && $nrows < 0); 243 else { 244 $nrows += $offset; 245 $sql .= " FETCH FIRST $nrows ROWS ONLY "; 246 } 247 $rs =& ADOConnection::SelectLimit($sql,-1,$offset,$inputArr); 248 } 249 250 return $rs; 251 } 252 253 }; 254 255 256 class ADORecordSet_db2 extends ADORecordSet_odbc { 257 258 var $databaseType = "db2"; 259 260 function ADORecordSet_db2($id,$mode=false) 261 { 262 $this->ADORecordSet_odbc($id,$mode); 263 } 264 265 function MetaType($t,$len=-1,$fieldobj=false) 266 { 267 if (is_object($t)) { 268 $fieldobj = $t; 269 $t = $fieldobj->type; 270 $len = $fieldobj->max_length; 271 } 272 273 switch (strtoupper($t)) { 274 case 'VARCHAR': 275 case 'CHAR': 276 case 'CHARACTER': 277 if ($len <= $this->blobSize) return 'C'; 278 279 case 'LONGCHAR': 280 case 'TEXT': 281 case 'CLOB': 282 case 'DBCLOB': // double-byte 223 return ADOConnection::ServerInfo(); 224 } 225 } 226 227 228 function CreateSequence($seqname='adodbseq',$start=1) 229 { 230 if (empty($this->_genSeqSQL)) return false; 231 $ok = $this->Execute(sprintf($this->_genSeqSQL,$seqname)); 232 if (!$ok) return false; 233 return true; 234 } 235 236 function DropSequence($seqname) 237 { 238 if (empty($this->_dropSeqSQL)) return false; 239 return $this->Execute(sprintf($this->_dropSeqSQL,$seqname)); 240 } 241 242 /* 243 This algorithm is not very efficient, but works even if table locking 244 is not available. 245 246 Will return false if unable to generate an ID after $MAXLOOPS attempts. 247 */ 248 function GenID($seq='adodbseq',$start=1) 249 { 250 // if you have to modify the parameter below, your database is overloaded, 251 // or you need to implement generation of id's yourself! 252 $num = $this->GetOne("VALUES NEXTVAL FOR $seq"); 253 return $num; 254 } 255 256 257 function ErrorMsg() 258 { 259 if ($this->_haserrorfunctions) { 260 if ($this->_errorMsg !== false) return $this->_errorMsg; 261 if (empty($this->_connectionID)) return @db2_conn_errormsg(); 262 return @db2_conn_errormsg($this->_connectionID); 263 } else return ADOConnection::ErrorMsg(); 264 } 265 266 function ErrorNo() 267 { 268 269 if ($this->_haserrorfunctions) { 270 if ($this->_errorCode !== false) { 271 // bug in 4.0.6, error number can be corrupted string (should be 6 digits) 272 return (strlen($this->_errorCode)<=2) ? 0 : $this->_errorCode; 273 } 274 275 if (empty($this->_connectionID)) $e = @db2_conn_error(); 276 else $e = @db2_conn_error($this->_connectionID); 277 278 // bug in 4.0.6, error number can be corrupted string (should be 6 digits) 279 // so we check and patch 280 if (strlen($e)<=2) return 0; 281 return $e; 282 } else return ADOConnection::ErrorNo(); 283 } 284 285 286 287 function BeginTrans() 288 { 289 if (!$this->hasTransactions) return false; 290 if ($this->transOff) return true; 291 $this->transCnt += 1; 292 $this->_autocommit = false; 293 return db2_autocommit($this->_connectionID,false); 294 } 295 296 function CommitTrans($ok=true) 297 { 298 if ($this->transOff) return true; 299 if (!$ok) return $this->RollbackTrans(); 300 if ($this->transCnt) $this->transCnt -= 1; 301 $this->_autocommit = true; 302 $ret = db2_commit($this->_connectionID); 303 db2_autocommit($this->_connectionID,true); 304 return $ret; 305 } 306 307 function RollbackTrans() 308 { 309 if ($this->transOff) return true; 310 if ($this->transCnt) $this->transCnt -= 1; 311 $this->_autocommit = true; 312 $ret = db2_rollback($this->_connectionID); 313 db2_autocommit($this->_connectionID,true); 314 return $ret; 315 } 316 317 function MetaPrimaryKeys($table) 318 { 319 global $ADODB_FETCH_MODE; 320 321 if ($this->uCaseTables) $table = strtoupper($table); 322 $schema = ''; 323 $this->_findschema($table,$schema); 324 325 $savem = $ADODB_FETCH_MODE; 326 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 327 $qid = @db2_primarykeys($this->_connectionID,'',$schema,$table); 328 329 if (!$qid) { 330 $ADODB_FETCH_MODE = $savem; 331 return false; 332 } 333 $rs = new ADORecordSet_db2($qid); 334 $ADODB_FETCH_MODE = $savem; 335 336 if (!$rs) return false; 337 338 $arr =& $rs->GetArray(); 339 $rs->Close(); 340 $arr2 = array(); 341 for ($i=0; $i < sizeof($arr); $i++) { 342 if ($arr[$i][3]) $arr2[] = $arr[$i][3]; 343 } 344 return $arr2; 345 } 346 347 function MetaForeignKeys($table, $owner = FALSE, $upper = FALSE, $asociative = FALSE ) 348 { 349 global $ADODB_FETCH_MODE; 350 351 if ($this->uCaseTables) $table = strtoupper($table); 352 $schema = ''; 353 $this->_findschema($table,$schema); 354 355 $savem = $ADODB_FETCH_MODE; 356 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 357 $qid = @db2_foreign_keys($this->_connectionID,'',$schema,$table); 358 if (!$qid) { 359 $ADODB_FETCH_MODE = $savem; 360 return false; 361 } 362 $rs = new ADORecordSet_db2($qid); 363 364 $ADODB_FETCH_MODE = $savem; 365 /* 366 $rs->fields indices 367 0 PKTABLE_CAT 368 1 PKTABLE_SCHEM 369 2 PKTABLE_NAME 370 3 PKCOLUMN_NAME 371 4 FKTABLE_CAT 372 5 FKTABLE_SCHEM 373 6 FKTABLE_NAME 374 7 FKCOLUMN_NAME 375 */ 376 if (!$rs) return false; 377 378 $foreign_keys = array(); 379 while (!$rs->EOF) { 380 if (strtoupper(trim($rs->fields[2])) == $table && (!$schema || strtoupper($rs->fields[1]) == $schema)) { 381 if (!is_array($foreign_keys[$rs->fields[5].'.'.$rs->fields[6]])) 382 $foreign_keys[$rs->fields[5].'.'.$rs->fields[6]] = array(); 383 $foreign_keys[$rs->fields[5].'.'.$rs->fields[6]][$rs->fields[7]] = $rs->fields[3]; 384 } 385 $rs->MoveNext(); 386 } 387 388 $rs->Close(); 389 return $foreign_key; 390 } 391 392 393 function &MetaTables($ttype=false,$schema=false) 394 { 395 global $ADODB_FETCH_MODE; 396 397 $savem = $ADODB_FETCH_MODE; 398 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 399 $qid = db2_tables($this->_connectionID); 400 401 $rs = new ADORecordSet_db2($qid); 402 403 $ADODB_FETCH_MODE = $savem; 404 if (!$rs) { 405 $false = false; 406 return $false; 407 } 408 409 $arr =& $rs->GetArray(); 410 411 $rs->Close(); 412 $arr2 = array(); 413 414 if ($ttype) { 415 $isview = strncmp($ttype,'V',1) === 0; 416 } 417 for ($i=0; $i < sizeof($arr); $i++) { 418 if (!$arr[$i][2]) continue; 419 $type = $arr[$i][3]; 420 $schemaval = ($schema) ? $arr[$i][1].'.' : ''; 421 if ($ttype) { 422 if ($isview) { 423 if (strncmp($type,'V',1) === 0) $arr2[] = $schemaval.$arr[$i][2]; 424 } else if (strncmp($type,'SYS',3) !== 0) $arr2[] = $schemaval.$arr[$i][2]; 425 } else if (strncmp($type,'SYS',3) !== 0) $arr2[] = $schemaval.$arr[$i][2]; 426 } 427 return $arr2; 428 } 429 430 /* 431 See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/db2/htm/db2datetime_data_type_changes.asp 432 / SQL data type codes / 433 #define SQL_UNKNOWN_TYPE 0 434 #define SQL_CHAR 1 435 #define SQL_NUMERIC 2 436 #define SQL_DECIMAL 3 437 #define SQL_INTEGER 4 438 #define SQL_SMALLINT 5 439 #define SQL_FLOAT 6 440 #define SQL_REAL 7 441 #define SQL_DOUBLE 8 442 #if (DB2VER >= 0x0300) 443 #define SQL_DATETIME 9 444 #endif 445 #define SQL_VARCHAR 12 446 447 448 / One-parameter shortcuts for date/time data types / 449 #if (DB2VER >= 0x0300) 450 #define SQL_TYPE_DATE 91 451 #define SQL_TYPE_TIME 92 452 #define SQL_TYPE_TIMESTAMP 93 453 454 #define SQL_UNICODE (-95) 455 #define SQL_UNICODE_VARCHAR (-96) 456 #define SQL_UNICODE_LONGVARCHAR (-97) 457 */ 458 function DB2Types($t) 459 { 460 switch ((integer)$t) { 461 case 1: 462 case 12: 463 case 0: 464 case -95: 465 case -96: 466 return 'C'; 467 case -97: 468 case -1: //text 283 469 return 'X'; 284 285 case 'BLOB': 286 case 'GRAPHIC': 287 case 'VARGRAPHIC': 470 case -4: //image 288 471 return 'B'; 472 473 case 9: 474 case 91: 475 return 'D'; 476 477 case 10: 478 case 11: 479 case 92: 480 case 93: 481 return 'T'; 289 482 290 case 'DATE': 291 return 'D'; 292 293 case 'TIME': 294 case 'TIMESTAMP': 295 return 'T'; 296 297 //case 'BOOLEAN': 298 //case 'BIT': 299 // return 'L'; 300 301 //case 'COUNTER': 302 // return 'R'; 303 304 case 'INT': 305 case 'INTEGER': 306 case 'BIGINT': 307 case 'SMALLINT': 483 case 4: 484 case 5: 485 case -6: 308 486 return 'I'; 309 487 310 default: return 'N'; 311 } 312 } 488 case -11: // uniqidentifier 489 return 'R'; 490 case -7: //bit 491 return 'L'; 492 493 default: 494 return 'N'; 495 } 496 } 497 498 function &MetaColumns($table) 499 { 500 global $ADODB_FETCH_MODE; 501 502 $false = false; 503 if ($this->uCaseTables) $table = strtoupper($table); 504 $schema = ''; 505 $this->_findschema($table,$schema); 506 507 $savem = $ADODB_FETCH_MODE; 508 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 509 510 $colname = "%"; 511 $qid = db2_columns($this->_connectionID, "", $schema, $table, $colname); 512 if (empty($qid)) return $false; 513 514 $rs =& new ADORecordSet_db2($qid); 515 $ADODB_FETCH_MODE = $savem; 516 517 if (!$rs) return $false; 518 $rs->_fetch(); 519 520 $retarr = array(); 521 522 /* 523 $rs->fields indices 524 0 TABLE_QUALIFIER 525 1 TABLE_SCHEM 526 2 TABLE_NAME 527 3 COLUMN_NAME 528 4 DATA_TYPE 529 5 TYPE_NAME 530 6 PRECISION 531 7 LENGTH 532 8 SCALE 533 9 RADIX 534 10 NULLABLE 535 11 REMARKS 536 */ 537 while (!$rs->EOF) { 538 if (strtoupper(trim($rs->fields[2])) == $table && (!$schema || strtoupper($rs->fields[1]) == $schema)) { 539 $fld = new ADOFieldObject(); 540 $fld->name = $rs->fields[3]; 541 $fld->type = $this->DB2Types($rs->fields[4]); 542 543 // ref: http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaraccgen/html/msdn_odk.asp 544 // access uses precision to store length for char/varchar 545 if ($fld->type == 'C' or $fld->type == 'X') { 546 if ($rs->fields[4] <= -95) // UNICODE 547 $fld->max_length = $rs->fields[7]/2; 548 else 549 $fld->max_length = $rs->fields[7]; 550 } else 551 $fld->max_length = $rs->fields[7]; 552 $fld->not_null = !empty($rs->fields[10]); 553 $fld->scale = $rs->fields[8]; 554 $fld->primary_key = false; 555 $retarr[strtoupper($fld->name)] = $fld; 556 } else if (sizeof($retarr)>0) 557 break; 558 $rs->MoveNext(); 559 } 560 $rs->Close(); 561 if (empty($retarr)) $retarr = false; 562 563 $qid = db2_primary_keys($this->_connectionID, "", $schema, $table); 564 if (empty($qid)) return $false; 565 566 $rs =& new ADORecordSet_db2($qid); 567 $ADODB_FETCH_MODE = $savem; 568 569 if (!$rs) return $retarr; 570 $rs->_fetch(); 571 572 /* 573 $rs->fields indices 574 0 TABLE_CAT 575 1 TABLE_SCHEM 576 2 TABLE_NAME 577 3 COLUMN_NAME 578 4 KEY_SEQ 579 5 PK_NAME 580 */ 581 while (!$rs->EOF) { 582 if (strtoupper(trim($rs->fields[2])) == $table && (!$schema || strtoupper($rs->fields[1]) == $schema)) { 583 $retarr[strtoupper($rs->fields[3])]->primary_key = true; 584 } else if (sizeof($retarr)>0) 585 break; 586 $rs->MoveNext(); 587 } 588 $rs->Close(); 589 590 if (empty($retarr)) $retarr = false; 591 return $retarr; 592 } 593 594 function Prepare($sql) 595 { 596 if (! $this->_bindInputArray) return $sql; // no binding 597 $stmt = db2_prepare($this->_connectionID,$sql); 598 if (!$stmt) { 599 // we don't know whether db2 driver is parsing prepared stmts, so just return sql 600 return $sql; 601 } 602 return array($sql,$stmt,false); 603 } 604 605 /* returns queryID or false */ 606 function _query($sql,$inputarr=false) 607 { 608 GLOBAL $php_errormsg; 609 if (isset($php_errormsg)) $php_errormsg = ''; 610 $this->_error = ''; 611 612 if ($inputarr) { 613 if (is_array($sql)) { 614 $stmtid = $sql[1]; 615 } else { 616 $stmtid = db2_prepare($this->_connectionID,$sql); 617 618 if ($stmtid == false) { 619 $this->_errorMsg = isset($php_errormsg) ? $php_errormsg : ''; 620 return false; 621 } 622 } 623 624 if (! db2_execute($stmtid,$inputarr)) { 625 if ($this->_haserrorfunctions) { 626 $this->_errorMsg = db2_stmt_errormsg(); 627 $this->_errorCode = db2_stmt_error(); 628 } 629 return false; 630 } 631 632 } else if (is_array($sql)) { 633 $stmtid = $sql[1]; 634 if (!db2_execute($stmtid)) { 635 if ($this->_haserrorfunctions) { 636 $this->_errorMsg = db2_stmt_errormsg(); 637 $this->_errorCode = db2_stmt_error(); 638 } 639 return false; 640 } 641 } else 642 $stmtid = @db2_exec($this->_connectionID,$sql); 643 644 $this->_lastAffectedRows = 0; 645 if ($stmtid) { 646 if (@db2_num_fields($stmtid) == 0) { 647 $this->_lastAffectedRows = db2_num_rows($stmtid); 648 $stmtid = true; 649 } else { 650 $this->_lastAffectedRows = 0; 651 } 652 653 if ($this->_haserrorfunctions) { 654 $this->_errorMsg = ''; 655 $this->_errorCode = 0; 656 } else 657 $this->_errorMsg = isset($php_errormsg) ? $php_errormsg : ''; 658 } else { 659 if ($this->_haserrorfunctions) { 660 $this->_errorMsg = db2_stmt_errormsg(); 661 $this->_errorCode = db2_stmt_error(); 662 } else 663 $this->_errorMsg = isset($php_errormsg) ? $php_errormsg : ''; 664 665 } 666 return $stmtid; 667 } 668 669 /* 670 Insert a null into the blob field of the table first. 671 Then use UpdateBlob to store the blob. 672 673 Usage: 674 675 $conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1, null)'); 676 $conn->UpdateBlob('blobtable','blobcol',$blob,'id=1'); 677 */ 678 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB') 679 { 680 return $this->Execute("UPDATE $table SET $column=? WHERE $where",array($val)) != false; 681 } 682 683 // returns true or false 684 function _close() 685 { 686 $ret = @db2_close($this->_connectionID); 687 $this->_connectionID = false; 688 return $ret; 689 } 690 691 function _affectedrows() 692 { 693 return $this->_lastAffectedRows; 694 } 695 313 696 } 314 315 } //define 697 698 /*-------------------------------------------------------------------------------------- 699 Class Name: Recordset 700 --------------------------------------------------------------------------------------*/ 701 702 class ADORecordSet_db2 extends ADORecordSet { 703 704 var $bind = false; 705 var $databaseType = "db2"; 706 var $dataProvider = "db2"; 707 var $useFetchArray; 708 709 function ADORecordSet_db2($id,$mode=false) 710 { 711 if ($mode === false) { 712 global $ADODB_FETCH_MODE; 713 $mode = $ADODB_FETCH_MODE; 714 } 715 $this->fetchMode = $mode; 716 717 $this->_queryID = $id; 718 } 719 720 721 // returns the field object 722 function &FetchField($offset = -1) 723 { 724 $o= new ADOFieldObject(); 725 $o->name = @db2_field_name($this->_queryID,$offset); 726 $o->type = @db2_field_type($this->_queryID,$offset); 727 $o->max_length = db2_field_width($this->_queryID,$offset); 728 if (ADODB_ASSOC_CASE == 0) $o->name = strtolower($o->name); 729 else if (ADODB_ASSOC_CASE == 1) $o->name = strtoupper($o->name); 730 return $o; 731 } 732 733 /* Use associative array to get fields array */ 734 function Fields($colname) 735 { 736 if ($this->fetchMode & ADODB_FETCH_ASSOC) return $this->fields[$colname]; 737 if (!$this->bind) { 738 $this->bind = array(); 739 for ($i=0; $i < $this->_numOfFields; $i++) { 740 $o = $this->FetchField($i); 741 $this->bind[strtoupper($o->name)] = $i; 742 } 743 } 744 745 return $this->fields[$this->bind[strtoupper($colname)]]; 746 } 747 748 749 function _initrs() 750 { 751 global $ADODB_COUNTRECS; 752 $this->_numOfRows = ($ADODB_COUNTRECS) ? @db2_num_rows($this->_queryID) : -1; 753 $this->_numOfFields = @db2_num_fields($this->_queryID); 754 // some silly drivers such as db2 as/400 and intersystems cache return _numOfRows = 0 755 if ($this->_numOfRows == 0) $this->_numOfRows = -1; 756 } 757 758 function _seek($row) 759 { 760 return false; 761 } 762 763 // speed up SelectLimit() by switching to ADODB_FETCH_NUM as ADODB_FETCH_ASSOC is emulated 764 function &GetArrayLimit($nrows,$offset=-1) 765 { 766 if ($offset <= 0) { 767 $rs =& $this->GetArray($nrows); 768 return $rs; 769 } 770 $savem = $this->fetchMode; 771 $this->fetchMode = ADODB_FETCH_NUM; 772 $this->Move($offset); 773 $this->fetchMode = $savem; 774 775 if ($this->fetchMode & ADODB_FETCH_ASSOC) { 776 $this->fields =& $this->GetRowAssoc(ADODB_ASSOC_CASE); 777 } 778 779 $results = array(); 780 $cnt = 0; 781 while (!$this->EOF && $nrows != $cnt) { 782 $results[$cnt++] = $this->fields; 783 $this->MoveNext(); 784 } 785 786 return $results; 787 } 788 789 790 function MoveNext() 791 { 792 if ($this->_numOfRows != 0 && !$this->EOF) { 793 $this->_currentRow++; 794 795 $this->fields = @db2_fetch_array($this->_queryID); 796 if ($this->fields) { 797 if ($this->fetchMode & ADODB_FETCH_ASSOC) { 798 $this->fields =& $this->GetRowAssoc(ADODB_ASSOC_CASE); 799 } 800 return true; 801 } 802 } 803 $this->fields = false; 804 $this->EOF = true; 805 return false; 806 } 807 808 function _fetch() 809 { 810 811 $this->fields = db2_fetch_array($this->_queryID); 812 if ($this->fields) { 813 if ($this->fetchMode & ADODB_FETCH_ASSOC) { 814 $this->fields =& $this->GetRowAssoc(ADODB_ASSOC_CASE); 815 } 816 return true; 817 } 818 $this->fields = false; 819 return false; 820 } 821 822 function _close() 823 { 824 return @db2_free_result($this->_queryID); 825 } 826 827 } 316 828 ?>
Note: See TracChangeset
for help on using the changeset viewer.
