[Summary view]

Differences Between: [Versions 310 and 311] [Versions 310 and 400] [Versions 310 and 401] [Versions 310 and 402] [Versions 310 and 403]

   1  <?php
   2  /*
   3  @version   v5.20.16  12-Jan-2020
   4  @copyright (c) 2000-2013 John Lim (jlim#natsoft.com). All rights reserved.
   5  @copyright (c) 2014      Damien Regad, Mark Newnham and the ADOdb community
   6    Released under both BSD license and Lesser GPL library license.
   7    Whenever there is any discrepancy between the two licenses,
   8    the BSD license will take precedence.
   9  Set tabs to 4 for best viewing.
  10  
  11    Latest version is available at http://adodb.org/
  12  
  13    DB2 data driver. Requires ODBC.
  14  
  15  From phpdb list:
  16  
  17  Hi Andrew,
  18  
  19  thanks a lot for your help. Today we discovered what
  20  our real problem was:
  21  
  22  After "playing" a little bit with the php-scripts that try
  23  to connect to the IBM DB2, we set the optional parameter
  24  Cursortype when calling odbc_pconnect(....).
  25  
  26  And the exciting thing: When we set the cursor type
  27  to SQL_CUR_USE_ODBC Cursor Type, then
  28  the whole query speed up from 1 till 10 seconds
  29  to 0.2 till 0.3 seconds for 100 records. Amazing!!!
  30  
  31  Therfore, PHP is just almost fast as calling the DB2
  32  from Servlets using JDBC (don't take too much care
  33  about the speed at whole: the database was on a
  34  completely other location, so the whole connection
  35  was made over a slow network connection).
  36  
  37  I hope this helps when other encounter the same
  38  problem when trying to connect to DB2 from
  39  PHP.
  40  
  41  Kind regards,
  42  Christian Szardenings
  43  
  44  2 Oct 2001
  45  Mark Newnham has discovered that the SQL_CUR_USE_ODBC is not supported by
  46  IBM's DB2 ODBC driver, so this must be a 3rd party ODBC driver.
  47  
  48  From the IBM CLI Reference:
  49  
  50  SQL_ATTR_ODBC_CURSORS (DB2 CLI v5)
  51  This connection attribute is defined by ODBC, but is not supported by DB2
  52  CLI. Any attempt to set or get this attribute will result in an SQLSTATE of
  53  HYC00 (Driver not capable).
  54  
  55  A 32-bit option specifying how the Driver Manager uses the ODBC cursor
  56  library.
  57  
  58  So I guess this means the message [above] was related to using a 3rd party
  59  odbc driver.
  60  
  61  Setting SQL_CUR_USE_ODBC
  62  ========================
  63  To set SQL_CUR_USE_ODBC for drivers that require it, do this:
  64  
  65  $db = NewADOConnection('odbc_db2');
  66  $db->curMode = SQL_CUR_USE_ODBC;
  67  $db->Connect($dsn, $userid, $pwd);
  68  
  69  
  70  
  71  USING CLI INTERFACE
  72  ===================
  73  
  74  I have had reports that the $host and $database params have to be reversed in
  75  Connect() when using the CLI interface. From Halmai Csongor csongor.halmai#nexum.hu:
  76  
  77  > The symptom is that if I change the database engine from postgres or any other to DB2 then the following
  78  > connection command becomes wrong despite being described this version to be correct in the docs.
  79  >
  80  > $connection_object->Connect( $DATABASE_HOST, $DATABASE_AUTH_USER_NAME, $DATABASE_AUTH_PASSWORD, $DATABASE_NAME )
  81  >
  82  > In case of DB2 I had to swap the first and last arguments in order to connect properly.
  83  
  84  
  85  System Error 5
  86  ==============
  87  IF you get a System Error 5 when trying to Connect/Load, it could be a permission problem. Give the user connecting
  88  to DB2 full rights to the DB2 SQLLIB directory, and place the user in the DBUSERS group.
  89  */
  90  
  91  // security - hide paths
  92  if (!defined('ADODB_DIR')) die();
  93  
  94  if (!defined('_ADODB_ODBC_LAYER')) {
  95  	 include(ADODB_DIR."/drivers/adodb-odbc.inc.php");
  96  }
  97  if (!defined('ADODB_ODBC_DB2')){
  98  define('ADODB_ODBC_DB2',1);
  99  
 100  class ADODB_ODBC_DB2 extends ADODB_odbc {
 101  	 var $databaseType = "db2";
 102  	 var $concat_operator = '||';
 103  	 var $sysTime = 'CURRENT TIME';
 104  	 var $sysDate = 'CURRENT DATE';
 105  	 var $sysTimeStamp = 'CURRENT TIMESTAMP';
 106  	 // The complete string representation of a timestamp has the form
 107  	 // yyyy-mm-dd-hh.mm.ss.nnnnnn.
 108  	 var $fmtTimeStamp = "'Y-m-d-H.i.s'";
 109  	 var $ansiOuter = true;
 110  	 var $identitySQL = 'values IDENTITY_VAL_LOCAL()';
 111  	 var $_bindInputArray = true;
 112  	  var $hasInsertID = true;
 113  	 var $rsPrefix = 'ADORecordset_odbc_';
 114  
 115  	function __construct()
 116  	 {
 117  	 	 if (strncmp(PHP_OS,'WIN',3) === 0) $this->curmode = SQL_CUR_USE_ODBC;
 118  	 	 parent::__construct();
 119  	 }
 120  
 121  	function IfNull( $field, $ifNull )
 122  	 {
 123  	 	 return " COALESCE($field, $ifNull) "; // if DB2 UDB
 124  	 }
 125  
 126  	function ServerInfo()
 127  	 {
 128  	 	 //odbc_setoption($this->_connectionID,1,101 /*SQL_ATTR_ACCESS_MODE*/, 1 /*SQL_MODE_READ_ONLY*/);
 129  	 	 $vers = $this->GetOne('select versionnumber from sysibm.sysversions');
 130  	 	 //odbc_setoption($this->_connectionID,1,101, 0 /*SQL_MODE_READ_WRITE*/);
 131  	 	 return array('description'=>'DB2 ODBC driver', 'version'=>$vers);
 132  	 }
 133  
 134  	function _insertid()
 135  	 {
 136  	 	 return $this->GetOne($this->identitySQL);
 137  	 }
 138  
 139  	function RowLock($tables,$where,$col='1 as adodbignore')
 140  	 {
 141  	 	 if ($this->_autocommit) $this->BeginTrans();
 142  	 	 return $this->GetOne("select $col from $tables where $where for update");
 143  	 }
 144  
 145  	function MetaTables($ttype=false,$showSchema=false, $qtable="%", $qschema="%")
 146  	 {
 147  	 global $ADODB_FETCH_MODE;
 148  
 149  	 	 $savem = $ADODB_FETCH_MODE;
 150  	 	 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 151  	 	 $qid = odbc_tables($this->_connectionID, "", $qschema, $qtable, "");
 152  
 153  	 	 $rs = new ADORecordSet_odbc($qid);
 154  
 155  	 	 $ADODB_FETCH_MODE = $savem;
 156  	 	 if (!$rs) {
 157  	 	 	 $false = false;
 158  	 	 	 return $false;
 159  	 	 }
 160  	 	 $rs->_has_stupid_odbc_fetch_api_change = $this->_has_stupid_odbc_fetch_api_change;
 161  
 162  	 	 $arr = $rs->GetArray();
 163  	 	 //print_r($arr);
 164  
 165  	 	 $rs->Close();
 166  	 	 $arr2 = array();
 167  
 168  	 	 if ($ttype) {
 169  	 	 	 $isview = strncmp($ttype,'V',1) === 0;
 170  	 	 }
 171  	 	 for ($i=0; $i < sizeof($arr); $i++) {
 172  
 173  	 	 	 if (!$arr[$i][2]) continue;
 174  	 	 	 if (strncmp($arr[$i][1],'SYS',3) === 0) continue;
 175  
 176  	 	 	 $type = $arr[$i][3];
 177  
 178  	 	 	 if ($showSchema) $arr[$i][2] = $arr[$i][1].'.'.$arr[$i][2];
 179  
 180  	 	 	 if ($ttype) {
 181  	 	 	 	 if ($isview) {
 182  	 	 	 	 	 if (strncmp($type,'V',1) === 0) $arr2[] = $arr[$i][2];
 183  	 	 	 	 } else if (strncmp($type,'T',1) === 0) $arr2[] = $arr[$i][2];
 184  	 	 	 } else if (strncmp($type,'S',1) !== 0) $arr2[] = $arr[$i][2];
 185  	 	 }
 186  	 	 return $arr2;
 187  	 }
 188  
 189  	function MetaIndexes ($table, $primary = FALSE, $owner=false)
 190  	 {
 191          // save old fetch mode
 192          global $ADODB_FETCH_MODE;
 193          $save = $ADODB_FETCH_MODE;
 194          $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 195          if ($this->fetchMode !== FALSE) {
 196                 $savem = $this->SetFetchMode(FALSE);
 197          }
 198  	 	 $false = false;
 199  	 	 // get index details
 200  	 	 $table = strtoupper($table);
 201  	 	 $SQL="SELECT NAME, UNIQUERULE, COLNAMES FROM SYSIBM.SYSINDEXES WHERE TBNAME='$table'";
 202          if ($primary)
 203  	 	 	 $SQL.= " AND UNIQUERULE='P'";
 204  	 	 $rs = $this->Execute($SQL);
 205          if (!is_object($rs)) {
 206  	 	 	 if (isset($savem))
 207  	 	 	 	 $this->SetFetchMode($savem);
 208  	 	 	 $ADODB_FETCH_MODE = $save;
 209              return $false;
 210          }
 211  	 	 $indexes = array ();
 212          // parse index data into array
 213          while ($row = $rs->FetchRow()) {
 214  	 	 	 $indexes[$row[0]] = array(
 215  	 	 	    'unique' => ($row[1] == 'U' || $row[1] == 'P'),
 216  	 	 	    'columns' => array()
 217  	 	 	 );
 218  	 	 	 $cols = ltrim($row[2],'+');
 219  	 	 	 $indexes[$row[0]]['columns'] = explode('+', $cols);
 220          }
 221  	 	 if (isset($savem)) {
 222              $this->SetFetchMode($savem);
 223  	 	 	 $ADODB_FETCH_MODE = $save;
 224  	 	 }
 225          return $indexes;
 226  	 }
 227  
 228  	 // Format date column in sql string given an input format that understands Y M D
 229  	function SQLDate($fmt, $col=false)
 230  	 {
 231  	 // use right() and replace() ?
 232  	 	 if (!$col) $col = $this->sysDate;
 233  	 	 $s = '';
 234  
 235  	 	 $len = strlen($fmt);
 236  	 	 for ($i=0; $i < $len; $i++) {
 237  	 	 	 if ($s) $s .= '||';
 238  	 	 	 $ch = $fmt[$i];
 239  	 	 	 switch($ch) {
 240  	 	 	 case 'Y':
 241  	 	 	 case 'y':
 242  	 	 	 	 $s .= "char(year($col))";
 243  	 	 	 	 break;
 244  	 	 	 case 'M':
 245  	 	 	 	 $s .= "substr(monthname($col),1,3)";
 246  	 	 	 	 break;
 247  	 	 	 case 'm':
 248  	 	 	 	 $s .= "right(digits(month($col)),2)";
 249  	 	 	 	 break;
 250  	 	 	 case 'D':
 251  	 	 	 case 'd':
 252  	 	 	 	 $s .= "right(digits(day($col)),2)";
 253  	 	 	 	 break;
 254  	 	 	 case 'H':
 255  	 	 	 case 'h':
 256  	 	 	 	 if ($col != $this->sysDate) $s .= "right(digits(hour($col)),2)";
 257  	 	 	 	 else $s .= "''";
 258  	 	 	 	 break;
 259  	 	 	 case 'i':
 260  	 	 	 case 'I':
 261  	 	 	 	 if ($col != $this->sysDate)
 262  	 	 	 	 	 $s .= "right(digits(minute($col)),2)";
 263  	 	 	 	 	 else $s .= "''";
 264  	 	 	 	 break;
 265  	 	 	 case 'S':
 266  	 	 	 case 's':
 267  	 	 	 	 if ($col != $this->sysDate)
 268  	 	 	 	 	 $s .= "right(digits(second($col)),2)";
 269  	 	 	 	 else $s .= "''";
 270  	 	 	 	 break;
 271  	 	 	 default:
 272  	 	 	 	 if ($ch == '\\') {
 273  	 	 	 	 	 $i++;
 274  	 	 	 	 	 $ch = substr($fmt,$i,1);
 275  	 	 	 	 }
 276  	 	 	 	 $s .= $this->qstr($ch);
 277  	 	 	 }
 278  	 	 }
 279  	 	 return $s;
 280  	 }
 281  
 282  
 283  	function SelectLimit($sql, $nrows = -1, $offset = -1, $inputArr = false, $secs2cache = 0)
 284  	 {
 285  	 	 $nrows = (integer) $nrows;
 286  	 	 if ($offset <= 0) {
 287  	 	 // could also use " OPTIMIZE FOR $nrows ROWS "
 288  	 	 	 if ($nrows >= 0) $sql .=  " FETCH FIRST $nrows ROWS ONLY ";
 289  	 	 	 $rs = $this->Execute($sql,$inputArr);
 290  	 	 } else {
 291  	 	 	 if ($offset > 0 && $nrows < 0);
 292  	 	 	 else {
 293  	 	 	 	 $nrows += $offset;
 294  	 	 	 	 $sql .=  " FETCH FIRST $nrows ROWS ONLY ";
 295  	 	 	 }
 296  	 	 	 $rs = ADOConnection::SelectLimit($sql,-1,$offset,$inputArr);
 297  	 	 }
 298  
 299  	 	 return $rs;
 300  	 }
 301  
 302  };
 303  
 304  
 305  class  ADORecordSet_odbc_db2 extends ADORecordSet_odbc {
 306  
 307  	 var $databaseType = "db2";
 308  
 309  	function __construct($id,$mode=false)
 310  	 {
 311  	 	 parent::__construct($id,$mode);
 312  	 }
 313  
 314  	function MetaType($t,$len=-1,$fieldobj=false)
 315  	 {
 316  	 	 if (is_object($t)) {
 317  	 	 	 $fieldobj = $t;
 318  	 	 	 $t = $fieldobj->type;
 319  	 	 	 $len = $fieldobj->max_length;
 320  	 	 }
 321  
 322  	 	 switch (strtoupper($t)) {
 323  	 	 case 'VARCHAR':
 324  	 	 case 'CHAR':
 325  	 	 case 'CHARACTER':
 326  	 	 case 'C':
 327  	 	 	 if ($len <= $this->blobSize) return 'C';
 328  
 329  	 	 case 'LONGCHAR':
 330  	 	 case 'TEXT':
 331  	 	 case 'CLOB':
 332  	 	 case 'DBCLOB': // double-byte
 333  	 	 case 'X':
 334  	 	 	 return 'X';
 335  
 336  	 	 case 'BLOB':
 337  	 	 case 'GRAPHIC':
 338  	 	 case 'VARGRAPHIC':
 339  	 	 	 return 'B';
 340  
 341  	 	 case 'DATE':
 342  	 	 case 'D':
 343  	 	 	 return 'D';
 344  
 345  	 	 case 'TIME':
 346  	 	 case 'TIMESTAMP':
 347  	 	 case 'T':
 348  	 	 	 return 'T';
 349  
 350  	 	 //case 'BOOLEAN':
 351  	 	 //case 'BIT':
 352  	 	 //	 return 'L';
 353  
 354  	 	 //case 'COUNTER':
 355  	 	 //	 return 'R';
 356  
 357  	 	 case 'INT':
 358  	 	 case 'INTEGER':
 359  	 	 case 'BIGINT':
 360  	 	 case 'SMALLINT':
 361  	 	 case 'I':
 362  	 	 	 return 'I';
 363  
 364  	 	 default: return 'N';
 365  	 	 }
 366  	 }
 367  }
 368  
 369  } //define