1<?php 2/** 3 * IBM DB2 Native Client driver. 4 * 5 * Originally DB2 drivers were dependent on an ODBC driver, and some installations 6 * may still use that. To use an ODBC driver connection, use the odbc_db2 7 * ADOdb driver. For Linux, you need the 'ibm_db2' PECL extension for PHP, 8 * For Windows, you need to locate an appropriate version of the php_ibm_db2.dll, 9 * as well as the IBM data server client software. 10 * This is basically a full rewrite of the original driver, for information 11 * about all the changes, see the update information on the ADOdb website 12 * for version 5.21.0. 13 * 14 * @link http://pecl.php.net/package/ibm_db2 PECL Extension For DB2 15 * 16 * This file is part of ADOdb, a Database Abstraction Layer library for PHP. 17 * 18 * @package ADOdb 19 * @link https://adodb.org Project's web site and documentation 20 * @link https://github.com/ADOdb/ADOdb Source code and issue tracker 21 * 22 * The ADOdb Library is dual-licensed, released under both the BSD 3-Clause 23 * and the GNU Lesser General Public Licence (LGPL) v2.1 or, at your option, 24 * any later version. This means you can use it in proprietary products. 25 * See the LICENSE.md file distributed with this source code for details. 26 * @license BSD-3-Clause 27 * @license LGPL-2.1-or-later 28 * 29 * @copyright 2000-2013 John Lim 30 * @copyright 2014 Damien Regad, Mark Newnham and the ADOdb community 31 * @author Mark Newnham 32 */ 33 34// security - hide paths 35if (!defined('ADODB_DIR')) die(); 36 37define("_ADODB_DB2_LAYER", 2 ); 38 39 40class ADODB_db2 extends ADOConnection { 41 var $databaseType = "db2"; 42 var $fmtDate = "'Y-m-d'"; 43 var $concat_operator = '||'; 44 45 var $sysTime = 'CURRENT TIME'; 46 var $sysDate = 'CURRENT DATE'; 47 var $sysTimeStamp = 'CURRENT TIMESTAMP'; 48 49 var $fmtTimeStamp = "'Y-m-d H:i:s'"; 50 var $replaceQuote = "''"; // string to use to replace quotes 51 var $dataProvider = "db2"; 52 var $hasAffectedRows = true; 53 54 var $binmode = DB2_BINARY; 55 56 /* 57 * setting this to true will make array elements in FETCH_ASSOC 58 * mode case-sensitive breaking backward-compat 59 */ 60 var $useFetchArray = false; 61 var $_bindInputArray = true; 62 var $_genIDSQL = "VALUES NEXTVAL FOR %s"; 63 var $_genSeqSQL = " 64 CREATE SEQUENCE %s START WITH %s 65 NO MAXVALUE NO CYCLE INCREMENT BY 1 NO CACHE 66 "; 67 var $_dropSeqSQL = "DROP SEQUENCE %s"; 68 var $_autocommit = true; 69 var $_lastAffectedRows = 0; 70 var $hasInsertID = true; 71 var $hasGenID = true; 72 73 /* 74 * Character used to wrap column and table names for escaping special 75 * characters in column and table names as well as forcing upper and 76 * lower case 77 */ 78 public $nameQuote = '"'; 79 80 /* 81 * Executed after successful connection 82 */ 83 public $connectStmt = ''; 84 85 /* 86 * Holds the current database name 87 */ 88 private $databaseName = ''; 89 90 /* 91 * Holds information about the stored procedure request 92 * currently being built 93 */ 94 private $storedProcedureParameters = false; 95 96 97 function __construct() {} 98 99 protected function _insertID($table = '', $column = '') 100 { 101 return ADOConnection::GetOne('VALUES IDENTITY_VAL_LOCAL()'); 102 } 103 104 public function _connect($argDSN, $argUsername, $argPassword, $argDatabasename) 105 { 106 return $this->doDB2Connect($argDSN, $argUsername, $argPassword, $argDatabasename); 107 } 108 109 public function _pconnect($argDSN, $argUsername, $argPassword, $argDatabasename) 110 { 111 return $this->doDB2Connect($argDSN, $argUsername, $argPassword, $argDatabasename,true); 112 } 113 114 private function doDB2Connect($argDSN, $argUsername, $argPassword, $argDatabasename, $persistent=false) 115 { 116 global $php_errormsg; 117 118 if (!function_exists('db2_connect')) { 119 ADOConnection::outp("DB2 extension not installed."); 120 return null; 121 } 122 123 $connectionParameters = $this->unpackParameters($argDSN, 124 $argUsername, 125 $argPassword, 126 $argDatabasename); 127 128 if ($connectionParameters == null) 129 { 130 /* 131 * Error thrown 132 */ 133 return null; 134 } 135 136 $argDSN = $connectionParameters['dsn']; 137 $argUsername = $connectionParameters['uid']; 138 $argPassword = $connectionParameters['pwd']; 139 $argDatabasename = $connectionParameters['database']; 140 $useCataloguedConnection = $connectionParameters['catalogue']; 141 142 if ($this->debug){ 143 if ($useCataloguedConnection){ 144 $connectMessage = "Catalogued connection using parameters: "; 145 $connectMessage .= "DB=$argDatabasename / "; 146 $connectMessage .= "UID=$argUsername / "; 147 $connectMessage .= "PWD=$argPassword"; 148 } 149 else 150 { 151 $connectMessage = "Uncatalogued connection using DSN: $argDSN"; 152 } 153 ADOConnection::outp($connectMessage); 154 } 155 /* 156 * This needs to be set before the connect(). 157 */ 158 ini_set('ibm_db2.binmode', $this->binmode); 159 160 if ($persistent) 161 $db2Function = 'db2_pconnect'; 162 else 163 $db2Function = 'db2_connect'; 164 165 /* 166 * We need to flatten out the connectionParameters 167 */ 168 169 $db2Options = array(); 170 if ($this->connectionParameters) 171 { 172 foreach($this->connectionParameters as $p) 173 foreach($p as $k=>$v) 174 $db2Options[$k] = $v; 175 } 176 177 if ($useCataloguedConnection) 178 $this->_connectionID = $db2Function($argDatabasename, 179 $argUsername, 180 $argPassword, 181 $db2Options); 182 else 183 $this->_connectionID = $db2Function($argDSN, 184 null, 185 null, 186 $db2Options); 187 188 $php_errormsg = ''; 189 190 $this->_errorMsg = @db2_conn_errormsg(); 191 192 if ($this->_connectionID && $this->connectStmt) 193 $this->execute($this->connectStmt); 194 195 return $this->_connectionID != false; 196 197 } 198 199 /** 200 * Validates and preprocesses the passed parameters for consistency 201 * 202 * @param string $argDSN Either DSN or database 203 * @param string $argUsername User name or null 204 * @param string $argPassword Password or null 205 * @param string $argDatabasename Either DSN or database 206 * 207 * @return mixed array if correct, null if not 208 */ 209 private function unpackParameters($argDSN, $argUsername, $argPassword, $argDatabasename) 210 { 211 212 global $php_errormsg; 213 214 $connectionParameters = array('dsn'=>'', 215 'uid'=>'', 216 'pwd'=>'', 217 'database'=>'', 218 'catalogue'=>true 219 ); 220 221 /* 222 * Uou can either connect to a catalogued connection 223 * with a database name e.g. 'SAMPLE' 224 * or an uncatalogued connection with a DSN like connection 225 * DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password; 226 */ 227 228 if (!$argDSN && !$argDatabasename) 229 { 230 $errorMessage = 'Supply either catalogued or uncatalogued connection parameters'; 231 $this->_errorMsg = $errorMessage; 232 if ($this->debug) 233 ADOConnection::outp($errorMessage); 234 return null; 235 } 236 237 $useCataloguedConnection = true; 238 $schemaName = ''; 239 240 if ($argDSN && $argDatabasename) 241 { 242 /* 243 * If a catalogued connection if provided, 244 * as well as user and password 245 * that will take priority 246 */ 247 if ($argUsername && $argPassword && !$this->isDsn($argDatabasename)) 248 { 249 if ($this->debug){ 250 $errorMessage = 'Warning: Because you provided user,'; 251 $errorMessage.= 'password and database, DSN connection '; 252 $errorMessage.= 'parameters were discarded'; 253 ADOConnection::outp($errorMessage); 254 255 } 256 $argDSN = ''; 257 } 258 else if ($this->isDsn($argDSN) && $this->isDsn($argDatabasename)) 259 { 260 $errorMessage = 'Supply uncatalogued connection parameters '; 261 $errorMessage.= 'in either the database or DSN arguments, '; 262 $errorMessage.= 'but not both'; 263 $php_errormsg = $errorMessage; 264 if ($this->debug) 265 ADOConnection::outp($errorMessage); 266 return null; 267 } 268 } 269 270 if (!$this->isDsn($argDSN) && $this->isDsn($argDatabasename)) 271 { 272 /* 273 * Switch them around for next test 274 */ 275 $temp = $argDSN; 276 $argDsn = $argDatabasename; 277 $argDatabasenME = $temp; 278 } 279 280 if ($this->isDsn($argDSN)) 281 { 282 283 if (!preg_match('/uid=/i',$argDSN) 284 || !preg_match('/pwd=/i',$argDSN)) 285 { 286 $errorMessage = 'For uncatalogued connections, provide '; 287 $errorMessage.= 'both UID and PWD in the connection string'; 288 $php_errormsg = $errorMessage; 289 if ($this->debug) 290 ADOConnection::outp($errorMessage); 291 return null; 292 } 293 294 if (preg_match('/database=/i',$argDSN)) 295 { 296 if ($argDatabasename) 297 { 298 $argDatabasename = ''; 299 if ($this->debug) 300 { 301 $errorMessage = 'Warning: Because you provided '; 302 $errorMessage.= 'database information in the DSN '; 303 $errorMessage.= 'parameters, the supplied database '; 304 $errorMessage.= 'name was discarded'; 305 ADOConnection::outp($errorMessage); 306 } 307 } 308 $useCataloguedConnection = false; 309 310 } 311 elseif ($argDatabasename) 312 { 313 $this->databaseName = $argDatabasename; 314 $argDSN .= ';database=' . $argDatabasename; 315 $argDatabasename = ''; 316 $useCataloguedConnection = false; 317 318 } 319 else 320 { 321 $errorMessage = 'Uncatalogued connection parameters '; 322 $errorMessage.= 'must contain a database= argument'; 323 $php_errormsg = $errorMessage; 324 if ($this->debug) 325 ADOConnection::outp($errorMessage); 326 return null; 327 } 328 } 329 330 if ($argDSN && !$argDatabasename && $useCataloguedConnection) 331 { 332 $argDatabasename = $argDSN; 333 $argDSN = ''; 334 } 335 336 337 if ($useCataloguedConnection 338 && (!$argDatabasename 339 || !$argUsername 340 || !$argPassword)) 341 { 342 343 $errorMessage = 'For catalogued connections, provide '; 344 $errorMessage.= 'database, username and password'; 345 $this->_errorMsg = $errorMessage; 346 if ($this->debug) 347 ADOConnection::outp($errorMessage); 348 return null; 349 350 } 351 352 if ($argDatabasename) 353 $this->databaseName = $argDatabasename; 354 elseif (!$this->databaseName) 355 $this->databaseName = $this->getDatabasenameFromDsn($argDSN); 356 357 358 $connectionParameters = array('dsn'=>$argDSN, 359 'uid'=>$argUsername, 360 'pwd'=>$argPassword, 361 'database'=>$argDatabasename, 362 'catalogue'=>$useCataloguedConnection 363 ); 364 365 return $connectionParameters; 366 367 } 368 369 /** 370 * Does the provided string look like a DSN 371 * 372 * @param string $dsnString 373 * 374 * @return bool 375 */ 376 private function isDsn($dsnString){ 377 $dsnArray = preg_split('/[;=]+/',$dsnString); 378 if (count($dsnArray) > 2) 379 return true; 380 return false; 381 } 382 383 384 /** 385 * Gets the database name from the DSN 386 * 387 * @param string $dsnString 388 * 389 * @return string 390 */ 391 private function getDatabasenameFromDsn($dsnString){ 392 393 $dsnArray = preg_split('/[;=]+/',$dsnString); 394 $dbIndex = array_search('database',$dsnArray); 395 396 return $dsnArray[$dbIndex + 1]; 397 } 398 399 400 /** 401 * format and return date string in database timestamp format 402 * 403 * @param mixed $ts either a string or a unixtime 404 * @param bool $isField discarded 405 * 406 * @return string 407 */ 408 function dbTimeStamp($ts,$isField=false) 409 { 410 if (empty($ts) && $ts !== 0) return 'null'; 411 if (is_string($ts)) $ts = ADORecordSet::unixTimeStamp($ts); 412 return 'TO_DATE('.adodb_date($this->fmtTimeStamp,$ts).",'YYYY-MM-DD HH24:MI:SS')"; 413 } 414 415 /** 416 * Format date column in sql string given an input format that understands Y M D 417 * 418 * @param string $fmt 419 * @param bool $col 420 * 421 * @return string 422 */ 423 function sqlDate($fmt, $col=false) 424 { 425 if (!$col) $col = $this->sysDate; 426 427 /* use TO_CHAR() if $fmt is TO_CHAR() allowed fmt */ 428 if ($fmt== 'Y-m-d H:i:s') 429 return 'TO_CHAR('.$col.", 'YYYY-MM-DD HH24:MI:SS')"; 430 431 $s = ''; 432 433 $len = strlen($fmt); 434 for ($i=0; $i < $len; $i++) { 435 if ($s) $s .= $this->concat_operator; 436 $ch = $fmt[$i]; 437 switch($ch) { 438 case 'Y': 439 case 'y': 440 if ($len==1) return "year($col)"; 441 $s .= "char(year($col))"; 442 break; 443 case 'M': 444 if ($len==1) return "monthname($col)"; 445 $s .= "substr(monthname($col),1,3)"; 446 break; 447 case 'm': 448 if ($len==1) return "month($col)"; 449 $s .= "right(digits(month($col)),2)"; 450 break; 451 case 'D': 452 case 'd': 453 if ($len==1) return "day($col)"; 454 $s .= "right(digits(day($col)),2)"; 455 break; 456 case 'H': 457 case 'h': 458 if ($len==1) return "hour($col)"; 459 if ($col != $this->sysDate) $s .= "right(digits(hour($col)),2)"; 460 else $s .= "''"; 461 break; 462 case 'i': 463 case 'I': 464 if ($len==1) return "minute($col)"; 465 if ($col != $this->sysDate) 466 $s .= "right(digits(minute($col)),2)"; 467 else $s .= "''"; 468 break; 469 case 'S': 470 case 's': 471 if ($len==1) return "second($col)"; 472 if ($col != $this->sysDate) 473 $s .= "right(digits(second($col)),2)"; 474 else $s .= "''"; 475 break; 476 default: 477 if ($ch == '\\') { 478 $i++; 479 $ch = substr($fmt,$i,1); 480 } 481 $s .= $this->qstr($ch); 482 } 483 } 484 return $s; 485 } 486 487 488 function serverInfo() 489 { 490 $sql = "SELECT service_level, fixpack_num 491 FROM TABLE(sysproc.env_get_inst_info()) 492 AS INSTANCEINFO"; 493 $row = $this->GetRow($sql); 494 495 496 if ($row) { 497 $info['version'] = $row[0].':'.$row[1]; 498 $info['fixpack'] = $row[1]; 499 $info['description'] = ''; 500 } else { 501 return ADOConnection::serverInfo(); 502 } 503 504 return $info; 505 } 506 507 function createSequence($seqname='adodbseq',$start=1) 508 { 509 if (empty($this->_genSeqSQL)) 510 return false; 511 512 $ok = $this->execute(sprintf($this->_genSeqSQL,$seqname,$start)); 513 if (!$ok) 514 return false; 515 return true; 516 } 517 518 function dropSequence($seqname='adodbseq') 519 { 520 if (empty($this->_dropSeqSQL)) return false; 521 return $this->execute(sprintf($this->_dropSeqSQL,$seqname)); 522 } 523 524 function selectLimit($sql,$nrows=-1,$offset=-1,$inputArr=false,$secs2cache=0) 525 { 526 $nrows = (integer) $nrows; 527 528 if ($offset <= 0) 529 { 530 if ($nrows >= 0) 531 $sql .= " FETCH FIRST $nrows ROWS ONLY "; 532 533 $rs = $this->execute($sql,$inputArr); 534 535 } 536 else 537 { 538 if ($offset > 0 && $nrows < 0); 539 540 else 541 { 542 $nrows += $offset; 543 $sql .= " FETCH FIRST $nrows ROWS ONLY "; 544 } 545 546 /* 547 * DB2 has no native support for mid table offset 548 */ 549 $rs = ADOConnection::selectLimit($sql,$nrows,$offset,$inputArr); 550 551 } 552 553 return $rs; 554 } 555 556 557 function errorMsg() 558 { 559 if ($this->_errorMsg !== false) 560 return $this->_errorMsg; 561 562 if (empty($this->_connectionID)) 563 return @db2_conn_errormsg(); 564 565 return @db2_conn_errormsg($this->_connectionID); 566 } 567 568 function errorNo() 569 { 570 571 if ($this->_errorCode !== false) 572 return $this->_errorCode; 573 574 575 if (empty($this->_connectionID)) 576 $e = @db2_conn_error(); 577 578 else 579 $e = @db2_conn_error($this->_connectionID); 580 581 return $e; 582 } 583 584 585 586 function beginTrans() 587 { 588 if (!$this->hasTransactions) 589 return false; 590 if ($this->transOff) 591 return true; 592 593 $this->transCnt += 1; 594 595 $this->_autocommit = false; 596 597 return db2_autocommit($this->_connectionID,false); 598 } 599 600 function CommitTrans($ok=true) 601 { 602 if ($this->transOff) 603 return true; 604 605 if (!$ok) 606 return $this->RollbackTrans(); 607 608 if ($this->transCnt) 609 $this->transCnt -= 1; 610 611 $this->_autocommit = true; 612 $ret = @db2_commit($this->_connectionID); 613 @db2_autocommit($this->_connectionID,true); 614 return $ret; 615 } 616 617 function RollbackTrans() 618 { 619 if ($this->transOff) return true; 620 if ($this->transCnt) $this->transCnt -= 1; 621 $this->_autocommit = true; 622 $ret = @db2_rollback($this->_connectionID); 623 @db2_autocommit($this->_connectionID,true); 624 return $ret; 625 } 626 627 /** 628 * Return a list of Primary Keys for a specified table 629 * 630 * We don't use db2_statistics as the function does not seem to play 631 * well with mixed case table names 632 * 633 * @param string $table 634 * @param bool $primary (optional) only return primary keys 635 * @param bool $owner (optional) not used in this driver 636 * 637 * @return string[] Array of indexes 638 */ 639 public function metaPrimaryKeys($table,$owner=false) 640 { 641 642 $primaryKeys = array(); 643 644 global $ADODB_FETCH_MODE; 645 646 $schema = ''; 647 $this->_findschema($table,$schema); 648 649 $table = $this->getTableCasedValue($table); 650 651 $savem = $ADODB_FETCH_MODE; 652 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 653 $this->setFetchMode(ADODB_FETCH_NUM); 654 655 656 $sql = "SELECT * 657 FROM syscat.indexes 658 WHERE tabname='$table'"; 659 660 $rows = $this->getAll($sql); 661 662 $this->setFetchMode($savem); 663 $ADODB_FETCH_MODE = $savem; 664 665 if (empty($rows)) 666 return false; 667 668 foreach ($rows as $r) 669 { 670 if ($r[7] != 'P') 671 continue; 672 673 $cols = explode('+',$r[6]); 674 foreach ($cols as $colIndex=>$col) 675 { 676 if ($colIndex == 0) 677 continue; 678 $columnName = $this->getMetaCasedValue($col); 679 $primaryKeys[] = $columnName; 680 } 681 break; 682 } 683 return $primaryKeys; 684 } 685 686 /** 687 * returns assoc array where keys are tables, and values are foreign keys 688 * 689 * @param string $table 690 * @param string $owner [optional][discarded] 691 * @param bool $upper [optional][discarded] 692 * @param bool $associative[optional][discarded] 693 * 694 * @return mixed[] Array of foreign key information 695 */ 696 public function metaForeignKeys($table, $owner = FALSE, $upper = FALSE, $asociative = FALSE ) 697 { 698 699 global $ADODB_FETCH_MODE; 700 701 $schema = ''; 702 $this->_findschema($table,$schema); 703 704 $savem = $ADODB_FETCH_MODE; 705 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 706 707 $this->setFetchMode(ADODB_FETCH_NUM); 708 709 $sql = "SELECT SUBSTR(tabname,1,20) table_name, 710 SUBSTR(constname,1,20) fk_name, 711 SUBSTR(REFTABNAME,1,12) parent_table, 712 SUBSTR(refkeyname,1,20) pk_orig_table, 713 fk_colnames 714 FROM syscat.references 715 WHERE tabname = '$table'"; 716 717 $results = $this->getAll($sql); 718 719 $ADODB_FETCH_MODE = $savem; 720 $this->setFetchMode($savem); 721 722 if (empty($results)) 723 return false; 724 725 $foreignKeys = array(); 726 727 foreach ($results as $r) 728 { 729 $parentTable = trim($this->getMetaCasedValue($r[2])); 730 $keyName = trim($this->getMetaCasedValue($r[1])); 731 $foreignKeys[$parentTable] = $keyName; 732 } 733 734 return $foreignKeys; 735 } 736 737 /** 738 * Returns a list of tables 739 * 740 * @param string $ttype (optional) 741 * @param string $schema (optional) 742 * @param string $mask (optional) 743 * 744 * @return array 745 */ 746 public function metaTables($ttype=false,$schema=false,$mask=false) 747 { 748 749 global $ADODB_FETCH_MODE; 750 751 $savem = $ADODB_FETCH_MODE; 752 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 753 754 /* 755 * Values for TABLE_TYPE 756 * --------------------------- 757 * ALIAS, HIERARCHY TABLE, INOPERATIVE VIEW, NICKNAME, 758 * MATERIALIZED QUERY TABLE, SYSTEM TABLE, TABLE, 759 * TYPED TABLE, TYPED VIEW, and VIEW 760 * 761 * If $ttype passed as '', match 'TABLE' and 'VIEW' 762 * If $ttype passed as 'T' it is assumed to be 'TABLE' 763 * if $ttype passed as 'V' it is assumed to be 'VIEW' 764 */ 765 $ttype = strtoupper($ttype); 766 if ($ttype) { 767 /* 768 * @todo We could do valid type checking or array type 769 */ 770 if ($ttype == 'V') 771 $ttype = 'VIEW'; 772 if ($ttype == 'T') 773 $ttype = 'TABLE'; 774 } 775 776 if (!$schema) 777 $schema = '%'; 778 779 if (!$mask) 780 $mask = '%'; 781 782 $qid = @db2_tables($this->_connectionID,NULL,$schema,$mask,$ttype); 783 784 $rs = new ADORecordSet_db2($qid); 785 786 $ADODB_FETCH_MODE = $savem; 787 788 if (!$rs) 789 return false; 790 791 $arr = $rs->getArray(); 792 793 $rs->Close(); 794 795 $tableList = array(); 796 797 /* 798 * Array items 799 * --------------------------------- 800 * 0 TABLE_CAT The catalog that contains the table. 801 * The value is NULL if this table does not have catalogs. 802 * 1 TABLE_SCHEM Name of the schema that contains the table. 803 * 2 TABLE_NAME Name of the table. 804 * 3 TABLE_TYPE Table type identifier for the table. 805 * 4 REMARKS Description of the table. 806 */ 807 808 for ($i=0; $i < sizeof($arr); $i++) 809 { 810 811 $tableRow = $arr[$i]; 812 $tableName = $tableRow[2]; 813 $tableType = $tableRow[3]; 814 815 if (!$tableName) 816 continue; 817 818 if ($ttype == '' && (strcmp($tableType,'TABLE') <> 0 && strcmp($tableType,'VIEW') <> 0)) 819 continue; 820 821 /* 822 * Set metacasing if required 823 */ 824 $tableName = $this->getMetaCasedValue($tableName); 825 826 /* 827 * If we requested a schema, we prepend the schema 828 name to the table name 829 */ 830 if (strcmp($schema,'%') <> 0) 831 $tableName = $schema . '.' . $tableName; 832 833 $tableList[] = $tableName; 834 835 } 836 return $tableList; 837 } 838 839 /** 840 * Return a list of indexes for a specified table 841 * 842 * We don't use db2_statistics as the function does not seem to play 843 * well with mixed case table names 844 * 845 * @param string $table 846 * @param bool $primary (optional) only return primary keys 847 * @param bool $owner (optional) not used in this driver 848 * 849 * @return string[] Array of indexes 850 */ 851 public function metaIndexes($table, $primary = false, $owner = false) { 852 853 global $ADODB_FETCH_MODE; 854 855 /* Array( 856 * [name_of_index] => Array( 857 * [unique] => true or false 858 * [columns] => Array( 859 * [0] => firstcol 860 * [1] => nextcol 861 * [2] => etc........ 862 * ) 863 * ) 864 * ) 865 */ 866 $indices = array(); 867 $primaryKeyName = ''; 868 869 $table = $this->getTableCasedValue($table); 870 871 872 $savem = $ADODB_FETCH_MODE; 873 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 874 $this->setFetchMode(ADODB_FETCH_NUM); 875 876 $sql = "SELECT * 877 FROM syscat.indexes 878 WHERE tabname='$table'"; 879 880 $rows = $this->getAll($sql); 881 882 $this->setFetchMode($savem); 883 $ADODB_FETCH_MODE = $savem; 884 885 if (empty($rows)) 886 return false; 887 888 foreach ($rows as $r) 889 { 890 891 $primaryIndex = $r[7] == 'P'?1:0; 892 if (!$primary) 893 /* 894 * Primary key not requested, ignore that one 895 */ 896 if ($r[7] == 'P') 897 continue; 898 899 $indexName = $this->getMetaCasedValue($r[1]); 900 if (!isset($indices[$indexName])) 901 { 902 $unique = ($r[7] == 'U')?1:0; 903 $indices[$indexName] = array('unique'=>$unique, 904 'primary'=>$primaryIndex, 905 'columns'=>array() 906 ); 907 } 908 $cols = explode('+',$r[6]); 909 foreach ($cols as $colIndex=>$col) 910 { 911 if ($colIndex == 0) 912 continue; 913 $columnName = $this->getMetaCasedValue($col); 914 $indices[$indexName]['columns'][] = $columnName; 915 } 916 917 } 918 919 return $indices; 920 921 } 922 923 /** 924 * List procedures or functions in an array. 925 * 926 * We interrogate syscat.routines instead of calling the PHP 927 * function procedures because ADOdb requires the type of procedure 928 * this is not available in the php function 929 * 930 * @param string $procedureNamePattern (optional) 931 * @param string $catalog (optional) 932 * @param string $schemaPattern (optional) 933 934 * @return array of procedures on current database. 935 * 936 */ 937 public function metaProcedures($procedureNamePattern = null, $catalog = null, $schemaPattern = null) { 938 939 940 global $ADODB_FETCH_MODE; 941 942 $metaProcedures = array(); 943 $procedureSQL = ''; 944 $catalogSQL = ''; 945 $schemaSQL = ''; 946 947 $savem = $ADODB_FETCH_MODE; 948 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 949 950 if ($procedureNamePattern) 951 $procedureSQL = "AND ROUTINENAME LIKE " . strtoupper($this->qstr($procedureNamePattern)); 952 953 if ($catalog) 954 $catalogSQL = "AND OWNER=" . strtoupper($this->qstr($catalog)); 955 956 if ($schemaPattern) 957 $schemaSQL = "AND ROUTINESCHEMA LIKE {$this->qstr($schemaPattern)}"; 958 959 960 $fields = " 961 ROUTINENAME, 962 CASE ROUTINETYPE 963 WHEN 'P' THEN 'PROCEDURE' 964 WHEN 'F' THEN 'FUNCTION' 965 ELSE 'METHOD' 966 END AS ROUTINETYPE_NAME, 967 ROUTINESCHEMA, 968 REMARKS"; 969 970 $SQL = "SELECT $fields 971 FROM syscat.routines 972 WHERE OWNER IS NOT NULL 973 $procedureSQL 974 $catalogSQL 975 $schemaSQL 976 ORDER BY ROUTINENAME 977 "; 978 979 $result = $this->execute($SQL); 980 981 $ADODB_FETCH_MODE = $savem; 982 983 if (!$result) 984 return false; 985 986 while ($r = $result->fetchRow()){ 987 $procedureName = $this->getMetaCasedValue($r[0]); 988 $schemaName = $this->getMetaCasedValue($r[2]); 989 $metaProcedures[$procedureName] = array('type'=> $r[1], 990 'catalog' => '', 991 'schema' => $schemaName, 992 'remarks' => $r[3] 993 ); 994 } 995 996 return $metaProcedures; 997 998 } 999 1000 /** 1001 * Lists databases. Because instances are independent, we only know about 1002 * the current database name 1003 * 1004 * @return string[] 1005 */ 1006 public function metaDatabases(){ 1007 1008 $dbName = $this->getMetaCasedValue($this->databaseName); 1009 1010 return (array)$dbName; 1011 1012 } 1013 1014 1015 1016 1017/* 1018See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/db2/htm/db2datetime_data_type_changes.asp 1019/ SQL data type codes / 1020#define SQL_UNKNOWN_TYPE 0 1021#define SQL_CHAR 1 1022#define SQL_NUMERIC 2 1023#define SQL_DECIMAL 3 1024#define SQL_INTEGER 4 1025#define SQL_SMALLINT 5 1026#define SQL_FLOAT 6 1027#define SQL_REAL 7 1028#define SQL_DOUBLE 8 1029#if (DB2VER >= 0x0300) 1030#define SQL_DATETIME 9 1031#endif 1032#define SQL_VARCHAR 12 1033 1034 1035/ One-parameter shortcuts for date/time data types / 1036#if (DB2VER >= 0x0300) 1037#define SQL_TYPE_DATE 91 1038#define SQL_TYPE_TIME 92 1039#define SQL_TYPE_TIMESTAMP 93 1040 1041#define SQL_UNICODE (-95) 1042#define SQL_UNICODE_VARCHAR (-96) 1043#define SQL_UNICODE_LONGVARCHAR (-97) 1044*/ 1045 function DB2Types($t) 1046 { 1047 switch ((integer)$t) { 1048 case 1: 1049 case 12: 1050 case 0: 1051 case -95: 1052 case -96: 1053 return 'C'; 1054 case -97: 1055 case -1: //text 1056 return 'X'; 1057 case -4: //image 1058 return 'B'; 1059 1060 case 9: 1061 case 91: 1062 return 'D'; 1063 1064 case 10: 1065 case 11: 1066 case 92: 1067 case 93: 1068 return 'T'; 1069 1070 case 4: 1071 case 5: 1072 case -6: 1073 return 'I'; 1074 1075 case -11: // uniqidentifier 1076 return 'R'; 1077 case -7: //bit 1078 return 'L'; 1079 1080 default: 1081 return 'N'; 1082 } 1083 } 1084 1085 public function metaColumns($table, $normalize=true) 1086 { 1087 global $ADODB_FETCH_MODE; 1088 1089 $savem = $ADODB_FETCH_MODE; 1090 1091 $schema = '%'; 1092 $this->_findschema($table,$schema); 1093 $table = $this->getTableCasedValue($table); 1094 $colname = "%"; 1095 $qid = db2_columns($this->_connectionID, null, $schema, $table, $colname); 1096 if (empty($qid)) 1097 { 1098 if ($this->debug) 1099 { 1100 $errorMessage = @db2_conn_errormsg($this->_connectionID); 1101 ADOConnection::outp($errorMessage); 1102 } 1103 return false; 1104 } 1105 1106 $rs = new ADORecordSet_db2($qid); 1107 1108 if (!$rs) 1109 return false; 1110 1111 $rs->_fetch(); 1112 1113 $retarr = array(); 1114 1115 /* 1116 $rs->fields indices 1117 0 TABLE_QUALIFIER 1118 1 TABLE_SCHEM 1119 2 TABLE_NAME 1120 3 COLUMN_NAME 1121 4 DATA_TYPE 1122 5 TYPE_NAME 1123 6 PRECISION 1124 7 LENGTH 1125 8 SCALE 1126 9 RADIX 1127 10 NULLABLE 1128 11 REMARKS 1129 12 Column Default 1130 13 SQL Data Type 1131 14 SQL DateTime SubType 1132 15 Max length in Octets 1133 16 Ordinal Position 1134 17 Is NULLABLE 1135 */ 1136 while (!$rs->EOF) 1137 { 1138 if ($rs->fields[2] == $table) 1139 { 1140 1141 $fld = new ADOFieldObject(); 1142 $fld->name = $rs->fields[3]; 1143 $fld->type = $this->DB2Types($rs->fields[4]); 1144 1145 // ref: http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaraccgen/html/msdn_odk.asp 1146 // access uses precision to store length for char/varchar 1147 1148 if ($fld->type == 'C' or $fld->type == 'X') { 1149 if ($rs->fields[4] <= -95) // UNICODE 1150 $fld->max_length = $rs->fields[7]/2; 1151 else 1152 $fld->max_length = $rs->fields[7]; 1153 } else 1154 $fld->max_length = $rs->fields[7]; 1155 1156 $fld->not_null = !empty($rs->fields[10]); 1157 $fld->scale = $rs->fields[8]; 1158 $fld->primary_key = false; 1159 1160 //$columnName = $this->getMetaCasedValue($fld->name); 1161 $columnName = strtoupper($fld->name); 1162 $retarr[$columnName] = $fld; 1163 1164 } 1165 else if (sizeof($retarr)>0) 1166 break; 1167 1168 $rs->MoveNext(); 1169 1170 } 1171 1172 $rs->Close(); 1173 if (empty($retarr)) 1174 $retarr = false; 1175 1176 /* 1177 * Now we find out if the column is part of a primary key 1178 */ 1179 1180 $qid = @db2_primary_keys($this->_connectionID, "", $schema, $table); 1181 if (empty($qid)) 1182 return false; 1183 1184 $rs = new ADORecordSet_db2($qid); 1185 1186 if (!$rs) 1187 { 1188 $ADODB_FETCH_MODE = $savem; 1189 return $retarr; 1190 } 1191 $rs->_fetch(); 1192 1193 /* 1194 $rs->fields indices 1195 0 TABLE_CAT 1196 1 TABLE_SCHEM 1197 2 TABLE_NAME 1198 3 COLUMN_NAME 1199 4 KEY_SEQ 1200 5 PK_NAME 1201 */ 1202 while (!$rs->EOF) { 1203 if (strtoupper(trim($rs->fields[2])) == $table 1204 && (!$schema || strtoupper($rs->fields[1]) == $schema)) 1205 { 1206 $retarr[strtoupper($rs->fields[3])]->primary_key = true; 1207 } 1208 else if (sizeof($retarr)>0) 1209 break; 1210 1211 $rs->MoveNext(); 1212 } 1213 $rs->Close(); 1214 1215 $ADODB_FETCH_MODE = $savem; 1216 1217 if (empty($retarr)) 1218 return false; 1219 1220 /* 1221 * If the fetch mode is numeric, return as numeric array 1222 */ 1223 if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) 1224 $retarr = array_values($retarr); 1225 1226 return $retarr; 1227 } 1228 1229 /** 1230 * In this version if prepareSp, we just check to make sure 1231 * that the name of the stored procedure is correct 1232 * If true, we returns an array 1233 * else false 1234 * 1235 * @param string $procedureName 1236 * @param mixed $parameters (not used in db2 connections) 1237 * @return mixed[] 1238 */ 1239 function prepareSp($procedureName,$parameters=false) { 1240 1241 global $ADODB_FETCH_MODE; 1242 1243 $this->storedProcedureParameters = array('name'=>'', 1244 'resource'=>false, 1245 'in'=>array(), 1246 'out'=>array(), 1247 'index'=>array(), 1248 'parameters'=>array(), 1249 'keyvalue' => array()); 1250 1251 //$procedureName = strtoupper($procedureName); 1252 //$procedureName = $this->getTableCasedValue($procedureName); 1253 1254 $savem = $ADODB_FETCH_MODE; 1255 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 1256 1257 $qid = db2_procedures($this->_connectionID, NULL , '%' , $procedureName ); 1258 1259 $ADODB_FETCH_MODE = $savem; 1260 1261 if (!$qid) 1262 { 1263 if ($this->debug) 1264 ADOConnection::outp(sprintf('No Procedure of name %s available',$procedureName)); 1265 return false; 1266 } 1267 1268 1269 1270 $this->storedProcedureParameters['name'] = $procedureName; 1271 /* 1272 * Now we know we have a valid procedure name, lets see if it requires 1273 * parameters 1274 */ 1275 $savem = $ADODB_FETCH_MODE; 1276 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 1277 1278 $qid = db2_procedure_columns($this->_connectionID, NULL , '%' , $procedureName , NULL ); 1279 1280 $ADODB_FETCH_MODE = $savem; 1281 1282 if (!$qid) 1283 { 1284 if ($this->debug) 1285 ADOConnection::outp(sprintf('No columns of name %s available',$procedureName)); 1286 return false; 1287 } 1288 $rs = new ADORecordSet_db2($qid); 1289 if (!$rs) 1290 return false; 1291 1292 $preparedStatement = 'CALL %s(%s)'; 1293 $parameterMarkers = array(); 1294 while (!$rs->EOF) 1295 { 1296 $parameterName = $rs->fields[3]; 1297 if ($parameterName == '') 1298 { 1299 $rs->moveNext(); 1300 continue; 1301 } 1302 $parameterType = $rs->fields[4]; 1303 $ordinalPosition = $rs->fields[17]; 1304 switch($parameterType) 1305 { 1306 case DB2_PARAM_IN: 1307 case DB2_PARAM_INOUT: 1308 $this->storedProcedureParameters['in'][$parameterName] = ''; 1309 break; 1310 case DB2_PARAM_INOUT: 1311 case DB2_PARAM_OUT: 1312 $this->storedProcedureParameters['out'][$parameterName] = ''; 1313 break; 1314 } 1315 $this->storedProcedureParameters['index'][$parameterName] = $ordinalPosition; 1316 $this->storedProcedureParameters['parameters'][$ordinalPosition] = $rs->fields; 1317 $rs->moveNext(); 1318 1319 } 1320 $parameterCount = count($this->storedProcedureParameters['index']); 1321 $parameterMarkers = array_fill(0,$parameterCount,'?'); 1322 1323 /* 1324 * We now know how many parameters to bind to the stored procedure 1325 */ 1326 $parameterList = implode(',',$parameterMarkers); 1327 1328 $sql = sprintf($preparedStatement,$procedureName,$parameterList); 1329 1330 $spResource = @db2_prepare($this->_connectionID,$sql); 1331 1332 if (!$spResource) 1333 { 1334 $errorMessage = @db2_conn_errormsg($this->_connectionID); 1335 $this->_errorMsg = $errorMessage; 1336 1337 if ($this->debug) 1338 ADOConnection::outp($errorMessage); 1339 1340 return false; 1341 } 1342 1343 $this->storedProcedureParameters['resource'] = $spResource; 1344 1345 if ($this->debug) 1346 { 1347 1348 ADOConnection::outp('The following parameters will be used in the SP call'); 1349 ADOConnection::outp(print_r($this->storedProcedureParameters)); 1350 } 1351 /* 1352 * We now have a stored parameter resource 1353 * to bind to. The spResource and sql that is returned are 1354 * not usable, its for dummy compatibility. Everything 1355 * will be handled by the storedProcedureParameters 1356 * array 1357 */ 1358 return array($sql,$spResource); 1359 1360 } 1361 1362 private function storedProcedureParameter(&$stmt, 1363 &$var, 1364 $name, 1365 $isOutput=false, 1366 $maxLen=4000, 1367 $type=false) 1368 { 1369 1370 1371 $name = strtoupper($name); 1372 1373 /* 1374 * Must exist in the list of parameter names for the type 1375 */ 1376 if ($isOutput 1377 && !isset( $this->storedProcedureParameters['out'][$name])) 1378 { 1379 $errorMessage = sprintf('%s is not a valid OUT parameter name',$name); 1380 1381 $this->_errorMsg = $errorMessage; 1382 if ($this->debug) 1383 ADOConnection::outp($errorMessage); 1384 return false; 1385 } 1386 1387 if (!$isOutput 1388 && !isset( $this->storedProcedureParameters['in'][$name])) 1389 { 1390 $errorMessage = sprintf('%s is not a valid IN parameter name',$name); 1391 1392 $this->_errorMsg = $errorMessage; 1393 if ($this->debug) 1394 ADOConnection::outp($errorMessage); 1395 return false; 1396 } 1397 1398 /* 1399 * We will use these values to bind to when we execute 1400 * the query 1401 */ 1402 $this->storedProcedureParameters['keyvalue'][$name] = &$var; 1403 1404 return true; 1405 1406 } 1407 1408 /** 1409 * Executes a prepared stored procedure. 1410 * 1411 * The function uses the previously accumulated information and 1412 * resources in the $storedProcedureParameters array 1413 * 1414 * @return mixed The statement id if successful, or false 1415 */ 1416 private function executeStoredProcedure() 1417 { 1418 1419 /* 1420 * Get the previously built resource 1421 */ 1422 $stmtid = $this->storedProcedureParameters['resource']; 1423 1424 /* 1425 * Bind our variables to the DB2 procedure 1426 */ 1427 foreach ($this->storedProcedureParameters['keyvalue'] as $spName=>$spValue){ 1428 1429 /* 1430 * Get the ordinal position, required for binding 1431 */ 1432 $ordinalPosition = $this->storedProcedureParameters['index'][$spName]; 1433 1434 /* 1435 * Get the db2 column dictionary for the parameter 1436 */ 1437 $columnDictionary = $this->storedProcedureParameters['parameters'][$ordinalPosition]; 1438 $parameterType = $columnDictionary[4]; 1439 $dataType = $columnDictionary[5]; 1440 $precision = $columnDictionary[10]; 1441 $scale = $columnDictionary[9]; 1442 1443 $ok = @db2_bind_param ($this->storedProcedureParameters['resource'], 1444 $ordinalPosition , 1445 $spName, 1446 $parameterType, 1447 $dataType, 1448 $precision, 1449 $scale 1450 ); 1451 1452 if (!$ok) 1453 { 1454 $this->_errorMsg = @db2_stmt_errormsg(); 1455 $this->_errorCode = @db2_stmt_error(); 1456 1457 if ($this->debug) 1458 ADOConnection::outp($this->_errorMsg); 1459 return false; 1460 } 1461 1462 if ($this->debug) 1463 ADOConnection::outp("Correctly Bound parameter $spName to procedure"); 1464 1465 /* 1466 * Build a variable in the current environment that matches 1467 * the parameter name 1468 */ 1469 ${$spName} = $spValue; 1470 1471 } 1472 1473 /* 1474 * All bound, execute 1475 */ 1476 1477 if (!@db2_execute($stmtid)) 1478 { 1479 $this->_errorMsg = @db2_stmt_errormsg(); 1480 $this->_errorCode = @db2_stmt_error(); 1481 1482 if ($this->debug) 1483 ADOConnection::outp($this->_errorMsg); 1484 return false; 1485 } 1486 1487 /* 1488 * We now take the changed parameters back into the 1489 * stored procedures array where we can query them later 1490 * Remember that $spValue was passed in by reference, so we 1491 * can access the value in the variable that was originally 1492 * passed to inParameter or outParameter 1493 */ 1494 foreach ($this->storedProcedureParameters['keyvalue'] as $spName=>$spValue) 1495 { 1496 /* 1497 * We make it available to the environment 1498 */ 1499 $spValue = ${$spName}; 1500 $this->storedProcedureParameters['keyvalue'][$spName] = $spValue; 1501 } 1502 1503 return $stmtid; 1504 } 1505 1506 /** 1507 * 1508 * Accepts an input or output parameter to bind to either a stored 1509 * or prepared statements. For DB2, this should not be called as an 1510 * API. always wrap with inParameter and outParameter 1511 * 1512 * @param mixed[] $stmt Statement returned by Prepare() or PrepareSP(). 1513 * @param mixed $var PHP variable to bind to. Can set to null (for isNull support). 1514 * @param string $name Name of stored procedure variable name to bind to. 1515 * @param int $isOutput optional) Indicates direction of parameter 1516 * 0/false=IN 1=OUT 2= IN/OUT 1517 * This is ignored for Stored Procedures 1518 * @param int $maxLen (optional)Holds an maximum length of the variable. 1519 * This is ignored for Stored Procedures 1520 * @param int $type (optional) The data type of $var. 1521 * This is ignored for Stored Procedures 1522 * 1523 * @return bool Success of the operation 1524 */ 1525 public function parameter(&$stmt, &$var, $name, $isOutput=false, $maxLen=4000, $type=false) 1526 { 1527 1528 /* 1529 * If the $stmt is the name of a stored procedure we are 1530 * setting up, we will process it one way, otherwise 1531 * we assume we are setting up a prepared statement 1532 */ 1533 if (is_array($stmt)) 1534 { 1535 if ($this->debug) 1536 ADOConnection::outp("Adding parameter to stored procedure"); 1537 if ($stmt[1] == $this->storedProcedureParameters['resource']) 1538 return $this->storedProcedureParameter($stmt[1], 1539 $var, 1540 $name, 1541 $isOutput, 1542 $maxLen, 1543 $type); 1544 1545 } 1546 1547 /* 1548 * We are going to add a parameter to a prepared statement 1549 */ 1550 if ($this->debug) 1551 ADOConnection::outp("Adding parameter to prepared statement"); 1552 } 1553 1554 1555 /** 1556 * Prepares a prepared SQL statement, not used for stored procedures 1557 * 1558 * @param string $sql 1559 * 1560 * @return mixed 1561 */ 1562 function prepare($sql) 1563 { 1564 1565 if (! $this->_bindInputArray) return $sql; // no binding 1566 1567 $stmt = @db2_prepare($this->_connectionID,$sql); 1568 if (!$stmt) { 1569 // we don't know whether db2 driver is parsing prepared stmts, so just return sql 1570 return $sql; 1571 } 1572 return array($sql,$stmt,false); 1573 } 1574 1575 /** 1576 * Executes a query 1577 * 1578 * @param mixed $sql 1579 * @param mixed $inputarr An optional array of parameters 1580 * 1581 * @return mixed either the queryID or false 1582 */ 1583 function _query(&$sql,$inputarr=false) 1584 { 1585 1586 GLOBAL $php_errormsg; 1587 1588 if (isset($php_errormsg)) 1589 $php_errormsg = ''; 1590 $this->_error = ''; 1591 1592 $db2Options = array(); 1593 /* 1594 * Use DB2 Internal case handling for best speed 1595 */ 1596 switch(ADODB_ASSOC_CASE) 1597 { 1598 case ADODB_ASSOC_CASE_UPPER: 1599 $db2Options = array('db2_attr_case'=>DB2_CASE_UPPER); 1600 $setOption = @db2_set_option($this->_connectionID,$db2Options,1); 1601 break; 1602 1603 case ADODB_ASSOC_CASE_LOWER: 1604 $db2Options = array('db2_attr_case'=>DB2_CASE_LOWER); 1605 $setOption = @db2_set_option($this->_connectionID,$db2Options,1); 1606 break; 1607 1608 default: 1609 $db2Options = array('db2_attr_case'=>DB2_CASE_NATURAL); 1610 $setOption = @db2_set_option($this->_connectionID,$db2Options,1); 1611 } 1612 1613 if ($inputarr) 1614 { 1615 if (is_array($sql)) 1616 { 1617 $stmtid = $sql[1]; 1618 } 1619 else 1620 { 1621 $stmtid = @db2_prepare($this->_connectionID,$sql); 1622 1623 if ($stmtid == false) 1624 { 1625 $this->_errorMsg = isset($php_errormsg) ? $php_errormsg : ''; 1626 return false; 1627 } 1628 } 1629 1630 if (! @db2_execute($stmtid,$inputarr)) 1631 { 1632 $this->_errorMsg = @db2_stmt_errormsg(); 1633 $this->_errorCode = @db2_stmt_error(); 1634 if ($this->debug) 1635 ADOConnection::outp($this->_errorMsg); 1636 return false; 1637 } 1638 1639 } 1640 else if (is_array($sql)) 1641 { 1642 1643 /* 1644 * Either a prepared statement or a stored procedure 1645 */ 1646 1647 if (is_array($this->storedProcedureParameters) 1648 && is_resource($this->storedProcedureParameters['resource'] 1649 )) 1650 /* 1651 * This is all handled in the separate method for 1652 * readability 1653 */ 1654 return $this->executeStoredProcedure(); 1655 1656 /* 1657 * First, we prepare the statement 1658 */ 1659 $stmtid = @db2_prepare($this->_connectionID,$sql[0]); 1660 if (!$stmtid){ 1661 $this->_errorMsg = @db2_stmt_errormsg(); 1662 $this->_errorCode = @db2_stmt_error(); 1663 if ($this->debug) 1664 ADOConnection::outp("Prepare failed: " . $this->_errorMsg); 1665 1666 return false; 1667 } 1668 /* 1669 * We next bind some input parameters 1670 */ 1671 $ordinal = 1; 1672 foreach ($sql[1] as $psVar=>$psVal){ 1673 ${$psVar} = $psVal; 1674 $ok = @db2_bind_param($stmtid, $ordinal, $psVar, DB2_PARAM_IN); 1675 if (!$ok) 1676 { 1677 $this->_errorMsg = @db2_stmt_errormsg(); 1678 $this->_errorCode = @db2_stmt_error(); 1679 if ($this->debug) 1680 ADOConnection::outp("Bind failed: " . $this->_errorMsg); 1681 return false; 1682 } 1683 } 1684 1685 if (!@db2_execute($stmtid)) 1686 { 1687 $this->_errorMsg = @db2_stmt_errormsg(); 1688 $this->_errorCode = @db2_stmt_error(); 1689 if ($this->debug) 1690 ADOConnection::outp($this->_errorMsg); 1691 return false; 1692 } 1693 1694 return $stmtid; 1695 } 1696 else 1697 { 1698 1699 $stmtid = @db2_exec($this->_connectionID,$sql); 1700 } 1701 $this->_lastAffectedRows = 0; 1702 if ($stmtid) 1703 { 1704 if (@db2_num_fields($stmtid) == 0) 1705 { 1706 $this->_lastAffectedRows = db2_num_rows($stmtid); 1707 $stmtid = true; 1708 } 1709 else 1710 { 1711 $this->_lastAffectedRows = 0; 1712 } 1713 1714 $this->_errorMsg = ''; 1715 $this->_errorCode = 0; 1716 1717 } 1718 else 1719 { 1720 1721 $this->_errorMsg = @db2_stmt_errormsg(); 1722 $this->_errorCode = @db2_stmt_error(); 1723 1724 } 1725 return $stmtid; 1726 } 1727 1728 /* 1729 Insert a null into the blob field of the table first. 1730 Then use UpdateBlob to store the blob. 1731 1732 Usage: 1733 1734 $conn->execute('INSERT INTO blobtable (id, blobcol) VALUES (1, null)'); 1735 $conn->UpdateBlob('blobtable','blobcol',$blob,'id=1'); 1736 */ 1737 function updateBlob($table,$column,$val,$where,$blobtype='BLOB') 1738 { 1739 return $this->execute("UPDATE $table SET $column=? WHERE $where",array($val)) != false; 1740 } 1741 1742 // returns true or false 1743 function _close() 1744 { 1745 $ret = @db2_close($this->_connectionID); 1746 $this->_connectionID = false; 1747 return $ret; 1748 } 1749 1750 function _affectedrows() 1751 { 1752 return $this->_lastAffectedRows; 1753 } 1754 1755 /** 1756 * Gets a meta cased parameter 1757 * 1758 * Receives an input variable to be processed per the metaCasing 1759 * rule, and returns the same value, processed 1760 * 1761 * @param string $value 1762 * 1763 * @return string 1764 */ 1765 final public function getMetaCasedValue($value) 1766 { 1767 global $ADODB_ASSOC_CASE; 1768 1769 switch($ADODB_ASSOC_CASE) 1770 { 1771 case ADODB_ASSOC_CASE_LOWER: 1772 $value = strtolower($value); 1773 break; 1774 case ADODB_ASSOC_CASE_UPPER: 1775 $value = strtoupper($value); 1776 break; 1777 } 1778 return $value; 1779 } 1780 1781 1782 const TABLECASE_LOWER = 0; 1783 const TABLECASE_UPPER = 1; 1784 const TABLECASE_DEFAULT = 2; 1785 1786 /** 1787 * Controls the casing of the table provided to the meta functions 1788 */ 1789 private $tableCase = 2; 1790 1791 /** 1792 * Sets the table case parameter 1793 * 1794 * @param int $caseOption 1795 * @return null 1796 */ 1797 final public function setTableCasing($caseOption) 1798 { 1799 $this->tableCase = $caseOption; 1800 } 1801 1802 /** 1803 * Gets the table casing parameter 1804 * 1805 * @return int $caseOption 1806 */ 1807 final public function getTableCasing() 1808 { 1809 return $this->tableCase; 1810 } 1811 1812 /** 1813 * Gets a table cased parameter 1814 * 1815 * Receives an input variable to be processed per the tableCasing 1816 * rule, and returns the same value, processed 1817 * 1818 * @param string $value 1819 * 1820 * @return string 1821 */ 1822 final public function getTableCasedValue($value) 1823 { 1824 switch($this->tableCase) 1825 { 1826 case self::TABLECASE_LOWER: 1827 $value = strtolower($value); 1828 break; 1829 case self::TABLECASE_UPPER: 1830 $value = strtoupper($value); 1831 break; 1832 } 1833 return $value; 1834 } 1835 1836} 1837 1838/*-------------------------------------------------------------------------------------- 1839 Class Name: Recordset 1840--------------------------------------------------------------------------------------*/ 1841 1842class ADORecordSet_db2 extends ADORecordSet { 1843 1844 var $bind = false; 1845 var $databaseType = "db2"; 1846 var $dataProvider = "db2"; 1847 var $useFetchArray; 1848 1849 function __construct($id,$mode=false) 1850 { 1851 if ($mode === false) { 1852 global $ADODB_FETCH_MODE; 1853 $mode = $ADODB_FETCH_MODE; 1854 } 1855 $this->fetchMode = $mode; 1856 1857 $this->_queryID = $id; 1858 } 1859 1860 1861 // returns the field object 1862 function fetchField($offset = 0) 1863 { 1864 $o = new ADOFieldObject(); 1865 $o->name = @db2_field_name($this->_queryID,$offset); 1866 $o->type = @db2_field_type($this->_queryID,$offset); 1867 $o->max_length = @db2_field_width($this->_queryID,$offset); 1868 1869 /* 1870 if (ADODB_ASSOC_CASE == 0) 1871 $o->name = strtolower($o->name); 1872 else if (ADODB_ASSOC_CASE == 1) 1873 $o->name = strtoupper($o->name); 1874 */ 1875 return $o; 1876 } 1877 1878 /* Use associative array to get fields array */ 1879 function fields($colname) 1880 { 1881 1882 if ($this->fetchMode & ADODB_FETCH_ASSOC) { 1883 return $this->fields[$colname]; 1884 } 1885 1886 if (!$this->bind) { 1887 $this->bind = array(); 1888 for ($i=0; $i < $this->_numOfFields; $i++) { 1889 $o = $this->FetchField($i); 1890 $this->bind[strtoupper($o->name)] = $i; 1891 } 1892 } 1893 1894 return $this->fields[$this->bind[strtoupper($colname)]]; 1895 } 1896 1897 1898 function _initrs() 1899 { 1900 global $ADODB_COUNTRECS; 1901 $this->_numOfRows = ($ADODB_COUNTRECS) ? @db2_num_rows($this->_queryID) : -1; 1902 1903 $this->_numOfFields = @db2_num_fields($this->_queryID); 1904 1905 // some silly drivers such as db2 as/400 and intersystems cache return _numOfRows = 0 1906 1907 if ($this->_numOfRows == 0) 1908 $this->_numOfRows = -1; 1909 } 1910 1911 function _seek($row) 1912 { 1913 return false; 1914 } 1915 1916 function getArrayLimit($nrows,$offset=0) 1917 { 1918 if ($offset <= 0) { 1919 $rs = $this->GetArray($nrows); 1920 return $rs; 1921 } 1922 1923 $this->Move($offset); 1924 1925 1926 $results = array(); 1927 $cnt = 0; 1928 while (!$this->EOF && $nrows != $cnt) { 1929 $results[$cnt++] = $this->fields; 1930 $this->MoveNext(); 1931 } 1932 1933 return $results; 1934 } 1935 1936 function moveNext() 1937 { 1938 if ($this->EOF || $this->_numOfRows == 0) 1939 return false; 1940 1941 $this->_currentRow++; 1942 1943 $this->processCoreFetch(); 1944 return $this->processMoveRecord(); 1945 1946 } 1947 1948 private function processCoreFetch() 1949 { 1950 switch ($this->fetchMode){ 1951 case ADODB_FETCH_ASSOC: 1952 1953 /* 1954 * Associative array 1955 */ 1956 $this->fields = @db2_fetch_assoc($this->_queryID); 1957 break; 1958 1959 case ADODB_FETCH_BOTH: 1960 /* 1961 * Fetch both numeric and Associative array 1962 */ 1963 $this->fields = @db2_fetch_both($this->_queryID); 1964 break; 1965 default: 1966 /* 1967 * Numeric array 1968 */ 1969 $this->fields = @db2_fetch_array($this->_queryID); 1970 break; 1971 } 1972 } 1973 1974 private function processMoveRecord() 1975 { 1976 if (!$this->fields){ 1977 $this->EOF = true; 1978 return false; 1979 } 1980 1981 return true; 1982 } 1983 1984 function _fetch() 1985 { 1986 $this->processCoreFetch(); 1987 if ($this->fields) 1988 return true; 1989 1990 $this->fields = false; 1991 return false; 1992 } 1993 1994 function _close() 1995 { 1996 $ok = @db2_free_result($this->_queryID); 1997 if (!$ok) 1998 { 1999 $this->_errorMsg = @db2_stmt_errormsg($this->_queryId); 2000 $this->_errorCode = @db2_stmt_error(); 2001 2002 if ($this->debug) 2003 ADOConnection::outp($this->_errorMsg); 2004 return false; 2005 } 2006 2007 } 2008 2009} 2010