1<?php 2/* 3 V4.80 8 Mar 2006 (c) 2006 John Lim (jlim@natsoft.com.my). All rights reserved. 4 5This is a version of the ADODB driver for DB2. It uses the 'ibm_db2' PECL extension for PHP 6 (http://pecl.php.net/package/ibm_db2), which in turn requires DB2 V8.2.2. 7 8 Tested with PHP 5.1.1 and Apache 2.0.55 on Windows XP SP2. 9 10 This file was ported from "adodb-odbc.inc.php" by Larry Menard, "larry.menard@rogers.com". 11 I ripped out what I believed to be a lot of redundant or obsolete code, but there are 12 probably still some remnants of the ODBC support in this file; I'm relying on reviewers 13 of this code to point out any other things that can be removed. 14*/ 15 16// security - hide paths 17if (!defined('ADODB_DIR')) die(); 18 19 define("_ADODB_DB2_LAYER", 2 ); 20 21/*-------------------------------------------------------------------------------------- 22--------------------------------------------------------------------------------------*/ 23 24 25class ADODB_db2 extends ADOConnection { 26 var $databaseType = "db2"; 27 var $fmtDate = "'Y-m-d'"; 28 var $fmtTimeStamp = "'Y-m-d, h:i:sA'"; 29 var $replaceQuote = "''"; // string to use to replace quotes 30 var $dataProvider = "db2"; 31 var $hasAffectedRows = true; 32 33 var $binmode = DB2_BINARY; 34 35 var $useFetchArray = false; // setting this to true will make array elements in FETCH_ASSOC mode case-sensitive 36 // breaking backward-compat 37 var $_bindInputArray = false; 38 var $_genSeqSQL = "create table %s (id integer)"; 39 var $_autocommit = true; 40 var $_haserrorfunctions = true; 41 var $_lastAffectedRows = 0; 42 var $uCaseTables = true; // for meta* functions, uppercase table names 43 44 function ADODB_db2() 45 { 46 $this->_haserrorfunctions = ADODB_PHPVER >= 0x4050; 47 } 48 49 // returns true or false 50 function _connect($argDSN, $argUsername, $argPassword, $argDatabasename) 51 { 52 global $php_errormsg; 53 54 if (!function_exists('db2_connect')) { 55 ADOConnection::outp("Warning: The old ODBC based DB2 driver has been renamed 'odbc_db2'. This ADOdb driver calls PHP's native db2 extension."); 56 return null; 57 } 58 // This needs to be set before the connect(). 59 // Replaces the odbc_binmode() call that was in Execute() 60 ini_set('ibm_db2.binmode', $this->binmode); 61 62 if ($argDatabasename) { 63 $this->_connectionID = db2_connect($argDatabasename,$argUsername,$argPassword); 64 } else { 65 $this->_connectionID = db2_connect($argDSN,$argUsername,$argPassword); 66 } 67 if (isset($php_errormsg)) $php_errormsg = ''; 68 69 // For db2_connect(), there is an optional 4th arg. If present, it must be 70 // an array of valid options. So far, we don't use them. 71 72 $this->_errorMsg = isset($php_errormsg) ? $php_errormsg : ''; 73 if (isset($this->connectStmt)) $this->Execute($this->connectStmt); 74 75 return $this->_connectionID != false; 76 } 77 78 // returns true or false 79 function _pconnect($argDSN, $argUsername, $argPassword, $argDatabasename) 80 { 81 global $php_errormsg; 82 83 if (!function_exists('db2_connect')) return null; 84 85 // This needs to be set before the connect(). 86 // Replaces the odbc_binmode() call that was in Execute() 87 ini_set('ibm_db2.binmode', $this->binmode); 88 89 if (isset($php_errormsg)) $php_errormsg = ''; 90 $this->_errorMsg = isset($php_errormsg) ? $php_errormsg : ''; 91 92 if ($argDatabasename) { 93 $this->_connectionID = db2_pconnect($argDatabasename,$argUsername,$argPassword); 94 } else { 95 $this->_connectionID = db2_pconnect($argDSN,$argUsername,$argPassword); 96 } 97 if (isset($php_errormsg)) $php_errormsg = ''; 98 99 $this->_errorMsg = isset($php_errormsg) ? $php_errormsg : ''; 100 if ($this->_connectionID && $this->autoRollback) @db2_rollback($this->_connectionID); 101 if (isset($this->connectStmt)) $this->Execute($this->connectStmt); 102 103 return $this->_connectionID != false; 104 } 105 106 107 function ServerInfo() 108 { 109 110 if (!empty($this->host) && ADODB_PHPVER >= 0x4300) { 111 $dsn = strtoupper($this->host); 112 $first = true; 113 $found = false; 114 115 if (!function_exists('db2_data_source')) return false; 116 117 while(true) { 118 119 $rez = @db2_data_source($this->_connectionID, 120 $first ? SQL_FETCH_FIRST : SQL_FETCH_NEXT); 121 $first = false; 122 if (!is_array($rez)) break; 123 if (strtoupper($rez['server']) == $dsn) { 124 $found = true; 125 break; 126 } 127 } 128 if (!$found) return ADOConnection::ServerInfo(); 129 if (!isset($rez['version'])) $rez['version'] = ''; 130 return $rez; 131 } else { 132 return ADOConnection::ServerInfo(); 133 } 134 } 135 136 137 function CreateSequence($seqname='adodbseq',$start=1) 138 { 139 if (empty($this->_genSeqSQL)) return false; 140 $ok = $this->Execute(sprintf($this->_genSeqSQL,$seqname)); 141 if (!$ok) return false; 142 $start -= 1; 143 return $this->Execute("insert into $seqname values($start)"); 144 } 145 146 var $_dropSeqSQL = 'drop table %s'; 147 function DropSequence($seqname) 148 { 149 if (empty($this->_dropSeqSQL)) return false; 150 return $this->Execute(sprintf($this->_dropSeqSQL,$seqname)); 151 } 152 153 /* 154 This algorithm is not very efficient, but works even if table locking 155 is not available. 156 157 Will return false if unable to generate an ID after $MAXLOOPS attempts. 158 */ 159 function GenID($seq='adodbseq',$start=1) 160 { 161 // if you have to modify the parameter below, your database is overloaded, 162 // or you need to implement generation of id's yourself! 163 $MAXLOOPS = 100; 164 while (--$MAXLOOPS>=0) { 165 $num = $this->GetOne("select id from $seq"); 166 if ($num === false) { 167 $this->Execute(sprintf($this->_genSeqSQL ,$seq)); 168 $start -= 1; 169 $num = '0'; 170 $ok = $this->Execute("insert into $seq values($start)"); 171 if (!$ok) return false; 172 } 173 $this->Execute("update $seq set id=id+1 where id=$num"); 174 175 if ($this->affected_rows() > 0) { 176 $num += 1; 177 $this->genID = $num; 178 return $num; 179 } 180 } 181 if ($fn = $this->raiseErrorFn) { 182 $fn($this->databaseType,'GENID',-32000,"Unable to generate unique id after $MAXLOOPS attempts",$seq,$num); 183 } 184 return false; 185 } 186 187 188 function ErrorMsg() 189 { 190 if ($this->_haserrorfunctions) { 191 if ($this->_errorMsg !== false) return $this->_errorMsg; 192 if (empty($this->_connectionID)) return @db2_errormsg(); 193 return @db2_errormsg($this->_connectionID); 194 } else return ADOConnection::ErrorMsg(); 195 } 196 197 function ErrorNo() 198 { 199 200 if ($this->_haserrorfunctions) { 201 if ($this->_errorCode !== false) { 202 // bug in 4.0.6, error number can be corrupted string (should be 6 digits) 203 return (strlen($this->_errorCode)<=2) ? 0 : $this->_errorCode; 204 } 205 206 if (empty($this->_connectionID)) $e = @db2_error(); 207 else $e = @db2_error($this->_connectionID); 208 209 // bug in 4.0.6, error number can be corrupted string (should be 6 digits) 210 // so we check and patch 211 if (strlen($e)<=2) return 0; 212 return $e; 213 } else return ADOConnection::ErrorNo(); 214 } 215 216 217 218 function BeginTrans() 219 { 220 if (!$this->hasTransactions) return false; 221 if ($this->transOff) return true; 222 $this->transCnt += 1; 223 $this->_autocommit = false; 224 return db2_autocommit($this->_connectionID,false); 225 } 226 227 function CommitTrans($ok=true) 228 { 229 if ($this->transOff) return true; 230 if (!$ok) return $this->RollbackTrans(); 231 if ($this->transCnt) $this->transCnt -= 1; 232 $this->_autocommit = true; 233 $ret = db2_commit($this->_connectionID); 234 db2_autocommit($this->_connectionID,true); 235 return $ret; 236 } 237 238 function RollbackTrans() 239 { 240 if ($this->transOff) return true; 241 if ($this->transCnt) $this->transCnt -= 1; 242 $this->_autocommit = true; 243 $ret = db2_rollback($this->_connectionID); 244 db2_autocommit($this->_connectionID,true); 245 return $ret; 246 } 247 248 function MetaPrimaryKeys($table) 249 { 250 global $ADODB_FETCH_MODE; 251 252 if ($this->uCaseTables) $table = strtoupper($table); 253 $schema = ''; 254 $this->_findschema($table,$schema); 255 256 $savem = $ADODB_FETCH_MODE; 257 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 258 $qid = @db2_primarykeys($this->_connectionID,'',$schema,$table); 259 260 if (!$qid) { 261 $ADODB_FETCH_MODE = $savem; 262 return false; 263 } 264 $rs = new ADORecordSet_db2($qid); 265 $ADODB_FETCH_MODE = $savem; 266 267 if (!$rs) return false; 268 269 $arr =& $rs->GetArray(); 270 $rs->Close(); 271 $arr2 = array(); 272 for ($i=0; $i < sizeof($arr); $i++) { 273 if ($arr[$i][3]) $arr2[] = $arr[$i][3]; 274 } 275 return $arr2; 276 } 277 278 279 280 function &MetaTables($ttype=false) 281 { 282 global $ADODB_FETCH_MODE; 283 284 $savem = $ADODB_FETCH_MODE; 285 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 286 $qid = db2_tables($this->_connectionID); 287 288 $rs = new ADORecordSet_db2($qid); 289 290 $ADODB_FETCH_MODE = $savem; 291 if (!$rs) { 292 $false = false; 293 return $false; 294 } 295 296 $arr =& $rs->GetArray(); 297 298 $rs->Close(); 299 $arr2 = array(); 300 301 if ($ttype) { 302 $isview = strncmp($ttype,'V',1) === 0; 303 } 304 for ($i=0; $i < sizeof($arr); $i++) { 305 if (!$arr[$i][2]) continue; 306 $type = $arr[$i][3]; 307 if ($ttype) { 308 if ($isview) { 309 if (strncmp($type,'V',1) === 0) $arr2[] = $arr[$i][2]; 310 } else if (strncmp($type,'SYS',3) !== 0) $arr2[] = $arr[$i][2]; 311 } else if (strncmp($type,'SYS',3) !== 0) $arr2[] = $arr[$i][2]; 312 } 313 return $arr2; 314 } 315 316/* 317See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/db2/htm/db2datetime_data_type_changes.asp 318/ SQL data type codes / 319#define SQL_UNKNOWN_TYPE 0 320#define SQL_CHAR 1 321#define SQL_NUMERIC 2 322#define SQL_DECIMAL 3 323#define SQL_INTEGER 4 324#define SQL_SMALLINT 5 325#define SQL_FLOAT 6 326#define SQL_REAL 7 327#define SQL_DOUBLE 8 328#if (DB2VER >= 0x0300) 329#define SQL_DATETIME 9 330#endif 331#define SQL_VARCHAR 12 332 333 334/ One-parameter shortcuts for date/time data types / 335#if (DB2VER >= 0x0300) 336#define SQL_TYPE_DATE 91 337#define SQL_TYPE_TIME 92 338#define SQL_TYPE_TIMESTAMP 93 339 340#define SQL_UNICODE (-95) 341#define SQL_UNICODE_VARCHAR (-96) 342#define SQL_UNICODE_LONGVARCHAR (-97) 343*/ 344 function DB2Types($t) 345 { 346 switch ((integer)$t) { 347 case 1: 348 case 12: 349 case 0: 350 case -95: 351 case -96: 352 return 'C'; 353 case -97: 354 case -1: //text 355 return 'X'; 356 case -4: //image 357 return 'B'; 358 359 case 9: 360 case 91: 361 return 'D'; 362 363 case 10: 364 case 11: 365 case 92: 366 case 93: 367 return 'T'; 368 369 case 4: 370 case 5: 371 case -6: 372 return 'I'; 373 374 case -11: // uniqidentifier 375 return 'R'; 376 case -7: //bit 377 return 'L'; 378 379 default: 380 return 'N'; 381 } 382 } 383 384 function &MetaColumns($table) 385 { 386 global $ADODB_FETCH_MODE; 387 388 $false = false; 389 if ($this->uCaseTables) $table = strtoupper($table); 390 $schema = ''; 391 $this->_findschema($table,$schema); 392 393 $savem = $ADODB_FETCH_MODE; 394 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 395 396 $colname = "%"; 397 $qid = db2_columns($this->_connectionID, "", $schema, $table, $colname); 398 if (empty($qid)) return $false; 399 400 $rs =& new ADORecordSet_db2($qid); 401 $ADODB_FETCH_MODE = $savem; 402 403 if (!$rs) return $false; 404 $rs->_fetch(); 405 406 $retarr = array(); 407 408 /* 409 $rs->fields indices 410 0 TABLE_QUALIFIER 411 1 TABLE_SCHEM 412 2 TABLE_NAME 413 3 COLUMN_NAME 414 4 DATA_TYPE 415 5 TYPE_NAME 416 6 PRECISION 417 7 LENGTH 418 8 SCALE 419 9 RADIX 420 10 NULLABLE 421 11 REMARKS 422 */ 423 while (!$rs->EOF) { 424 if (strtoupper(trim($rs->fields[2])) == $table && (!$schema || strtoupper($rs->fields[1]) == $schema)) { 425 $fld = new ADOFieldObject(); 426 $fld->name = $rs->fields[3]; 427 $fld->type = $this->DB2Types($rs->fields[4]); 428 429 // ref: http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaraccgen/html/msdn_odk.asp 430 // access uses precision to store length for char/varchar 431 if ($fld->type == 'C' or $fld->type == 'X') { 432 if ($rs->fields[4] <= -95) // UNICODE 433 $fld->max_length = $rs->fields[7]/2; 434 else 435 $fld->max_length = $rs->fields[7]; 436 } else 437 $fld->max_length = $rs->fields[7]; 438 $fld->not_null = !empty($rs->fields[10]); 439 $fld->scale = $rs->fields[8]; 440 $retarr[strtoupper($fld->name)] = $fld; 441 } else if (sizeof($retarr)>0) 442 break; 443 $rs->MoveNext(); 444 } 445 $rs->Close(); //-- crashes 4.03pl1 -- why? 446 447 if (empty($retarr)) $retarr = false; 448 return $retarr; 449 } 450 451 function Prepare($sql) 452 { 453 if (! $this->_bindInputArray) return $sql; // no binding 454 $stmt = db2_prepare($this->_connectionID,$sql); 455 if (!$stmt) { 456 // we don't know whether db2 driver is parsing prepared stmts, so just return sql 457 return $sql; 458 } 459 return array($sql,$stmt,false); 460 } 461 462 /* returns queryID or false */ 463 function _query($sql,$inputarr=false) 464 { 465 GLOBAL $php_errormsg; 466 if (isset($php_errormsg)) $php_errormsg = ''; 467 $this->_error = ''; 468 469 if ($inputarr) { 470 if (is_array($sql)) { 471 $stmtid = $sql[1]; 472 } else { 473 $stmtid = db2_prepare($this->_connectionID,$sql); 474 475 if ($stmtid == false) { 476 $this->_errorMsg = isset($php_errormsg) ? $php_errormsg : ''; 477 return false; 478 } 479 } 480 481 if (! db2_execute($stmtid,$inputarr)) { 482 if ($this->_haserrorfunctions) { 483 $this->_errorMsg = db2_errormsg(); 484 $this->_errorCode = db2_error(); 485 } 486 return false; 487 } 488 489 } else if (is_array($sql)) { 490 $stmtid = $sql[1]; 491 if (!db2_execute($stmtid)) { 492 if ($this->_haserrorfunctions) { 493 $this->_errorMsg = db2_errormsg(); 494 $this->_errorCode = db2_error(); 495 } 496 return false; 497 } 498 } else 499 $stmtid = db2_exec($this->_connectionID,$sql); 500 501 $this->_lastAffectedRows = 0; 502 if ($stmtid) { 503 if (@db2_num_fields($stmtid) == 0) { 504 $this->_lastAffectedRows = db2_num_rows($stmtid); 505 $stmtid = true; 506 } else { 507 $this->_lastAffectedRows = 0; 508 } 509 510 if ($this->_haserrorfunctions) { 511 $this->_errorMsg = ''; 512 $this->_errorCode = 0; 513 } else 514 $this->_errorMsg = isset($php_errormsg) ? $php_errormsg : ''; 515 } else { 516 if ($this->_haserrorfunctions) { 517 $this->_errorMsg = db2_stmt_errormsg(); 518 $this->_errorCode = db2_stmt_error(); 519 } else 520 $this->_errorMsg = isset($php_errormsg) ? $php_errormsg : ''; 521 } 522 return $stmtid; 523 } 524 525 /* 526 Insert a null into the blob field of the table first. 527 Then use UpdateBlob to store the blob. 528 529 Usage: 530 531 $conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1, null)'); 532 $conn->UpdateBlob('blobtable','blobcol',$blob,'id=1'); 533 */ 534 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB') 535 { 536 return $this->Execute("UPDATE $table SET $column=? WHERE $where",array($val)) != false; 537 } 538 539 // returns true or false 540 function _close() 541 { 542 $ret = @db2_close($this->_connectionID); 543 $this->_connectionID = false; 544 return $ret; 545 } 546 547 function _affectedrows() 548 { 549 return $this->_lastAffectedRows; 550 } 551 552} 553 554/*-------------------------------------------------------------------------------------- 555 Class Name: Recordset 556--------------------------------------------------------------------------------------*/ 557 558class ADORecordSet_db2 extends ADORecordSet { 559 560 var $bind = false; 561 var $databaseType = "db2"; 562 var $dataProvider = "db2"; 563 var $useFetchArray; 564 565 function ADORecordSet_db2($id,$mode=false) 566 { 567 if ($mode === false) { 568 global $ADODB_FETCH_MODE; 569 $mode = $ADODB_FETCH_MODE; 570 } 571 $this->fetchMode = $mode; 572 573 $this->_queryID = $id; 574 } 575 576 577 // returns the field object 578 function &FetchField($fieldOffset = -1) 579 { 580 581 $off=$fieldOffset+1; // offsets begin at 1 582 583 $o= new ADOFieldObject(); 584 $o->name = @db2_field_name($this->_queryID,$off); 585 $o->type = @db2_field_type($this->_queryID,$off); 586 $o->max_length = db2_field_width($this->_queryID,$off); 587 if (ADODB_ASSOC_CASE == 0) $o->name = strtolower($o->name); 588 else if (ADODB_ASSOC_CASE == 1) $o->name = strtoupper($o->name); 589 return $o; 590 } 591 592 /* Use associative array to get fields array */ 593 function Fields($colname) 594 { 595 if ($this->fetchMode & ADODB_FETCH_ASSOC) return $this->fields[$colname]; 596 if (!$this->bind) { 597 $this->bind = array(); 598 for ($i=0; $i < $this->_numOfFields; $i++) { 599 $o = $this->FetchField($i); 600 $this->bind[strtoupper($o->name)] = $i; 601 } 602 } 603 604 return $this->fields[$this->bind[strtoupper($colname)]]; 605 } 606 607 608 function _initrs() 609 { 610 global $ADODB_COUNTRECS; 611 $this->_numOfRows = ($ADODB_COUNTRECS) ? @db2_num_rows($this->_queryID) : -1; 612 $this->_numOfFields = @db2_num_fields($this->_queryID); 613 // some silly drivers such as db2 as/400 and intersystems cache return _numOfRows = 0 614 if ($this->_numOfRows == 0) $this->_numOfRows = -1; 615 } 616 617 function _seek($row) 618 { 619 return false; 620 } 621 622 // speed up SelectLimit() by switching to ADODB_FETCH_NUM as ADODB_FETCH_ASSOC is emulated 623 function &GetArrayLimit($nrows,$offset=-1) 624 { 625 if ($offset <= 0) { 626 $rs =& $this->GetArray($nrows); 627 return $rs; 628 } 629 $savem = $this->fetchMode; 630 $this->fetchMode = ADODB_FETCH_NUM; 631 $this->Move($offset); 632 $this->fetchMode = $savem; 633 634 if ($this->fetchMode & ADODB_FETCH_ASSOC) { 635 $this->fields =& $this->GetRowAssoc(ADODB_ASSOC_CASE); 636 } 637 638 $results = array(); 639 $cnt = 0; 640 while (!$this->EOF && $nrows != $cnt) { 641 $results[$cnt++] = $this->fields; 642 $this->MoveNext(); 643 } 644 645 return $results; 646 } 647 648 649 function MoveNext() 650 { 651 if ($this->_numOfRows != 0 && !$this->EOF) { 652 $this->_currentRow++; 653 654 $this->fields = @db2_fetch_array($this->_queryID); 655 if ($this->fields) { 656 if ($this->fetchMode & ADODB_FETCH_ASSOC) { 657 $this->fields =& $this->GetRowAssoc(ADODB_ASSOC_CASE); 658 } 659 return true; 660 } 661 } 662 $this->fields = false; 663 $this->EOF = true; 664 return false; 665 } 666 667 function _fetch() 668 { 669 670 $this->fields = db2_fetch_array($this->_queryID); 671 if ($this->fields) { 672 if ($this->fetchMode & ADODB_FETCH_ASSOC) { 673 $this->fields =& $this->GetRowAssoc(ADODB_ASSOC_CASE); 674 } 675 return true; 676 } 677 $this->fields = false; 678 return false; 679 } 680 681 function _close() 682 { 683 return @db2_free_result($this->_queryID); 684 } 685 686} 687?>