[Summary view]

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

   1  <?php
   2  /*
   3  @version   v5.21.0  2021-02-27
   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 https://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  Therefore, 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_once(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  
 161  	 	 $arr = $rs->GetArray();
 162  	 	 //print_r($arr);
 163  
 164  	 	 $rs->Close();
 165  	 	 $arr2 = array();
 166  
 167  	 	 if ($ttype) {
 168  	 	 	 $isview = strncmp($ttype,'V',1) === 0;
 169  	 	 }
 170  	 	 for ($i=0; $i < sizeof($arr); $i++) {
 171  
 172  	 	 	 if (!$arr[$i][2]) continue;
 173  	 	 	 if (strncmp($arr[$i][1],'SYS',3) === 0) continue;
 174  
 175  	 	 	 $type = $arr[$i][3];
 176  
 177  	 	 	 if ($showSchema) $arr[$i][2] = $arr[$i][1].'.'.$arr[$i][2];
 178  
 179  	 	 	 if ($ttype) {
 180  	 	 	 	 if ($isview) {
 181  	 	 	 	 	 if (strncmp($type,'V',1) === 0) $arr2[] = $arr[$i][2];
 182  	 	 	 	 } else if (strncmp($type,'T',1) === 0) $arr2[] = $arr[$i][2];
 183  	 	 	 } else if (strncmp($type,'S',1) !== 0) $arr2[] = $arr[$i][2];
 184  	 	 }
 185  	 	 return $arr2;
 186  	 }
 187  
 188  	function MetaIndexes ($table, $primary = FALSE, $owner=false)
 189  	 {
 190          // save old fetch mode
 191          global $ADODB_FETCH_MODE;
 192          $save = $ADODB_FETCH_MODE;
 193          $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 194          if ($this->fetchMode !== FALSE) {
 195                 $savem = $this->SetFetchMode(FALSE);
 196          }
 197  	 	 $false = false;
 198  	 	 // get index details
 199  	 	 $table = strtoupper($table);
 200  	 	 $SQL="SELECT NAME, UNIQUERULE, COLNAMES FROM SYSIBM.SYSINDEXES WHERE TBNAME='$table'";
 201          if ($primary)
 202  	 	 	 $SQL.= " AND UNIQUERULE='P'";
 203  	 	 $rs = $this->Execute($SQL);
 204          if (!is_object($rs)) {
 205  	 	 	 if (isset($savem))
 206  	 	 	 	 $this->SetFetchMode($savem);
 207  	 	 	 $ADODB_FETCH_MODE = $save;
 208              return $false;
 209          }
 210  	 	 $indexes = array ();
 211          // parse index data into array
 212          while ($row = $rs->FetchRow()) {
 213  	 	 	 $indexes[$row[0]] = array(
 214  	 	 	    'unique' => ($row[1] == 'U' || $row[1] == 'P'),
 215  	 	 	    'columns' => array()
 216  	 	 	 );
 217  	 	 	 $cols = ltrim($row[2],'+');
 218  	 	 	 $indexes[$row[0]]['columns'] = explode('+', $cols);
 219          }
 220  	 	 if (isset($savem)) {
 221              $this->SetFetchMode($savem);
 222  	 	 	 $ADODB_FETCH_MODE = $save;
 223  	 	 }
 224          return $indexes;
 225  	 }
 226  
 227  	 // Format date column in sql string given an input format that understands Y M D
 228  	function SQLDate($fmt, $col=false)
 229  	 {
 230  	 // use right() and replace() ?
 231  	 	 if (!$col) $col = $this->sysDate;
 232  	 	 $s = '';
 233  
 234  	 	 $len = strlen($fmt);
 235  	 	 for ($i=0; $i < $len; $i++) {
 236  	 	 	 if ($s) $s .= '||';
 237  	 	 	 $ch = $fmt[$i];
 238  	 	 	 switch($ch) {
 239  	 	 	 case 'Y':
 240  	 	 	 case 'y':
 241  	 	 	 	 $s .= "char(year($col))";
 242  	 	 	 	 break;
 243  	 	 	 case 'M':
 244  	 	 	 	 $s .= "substr(monthname($col),1,3)";
 245  	 	 	 	 break;
 246  	 	 	 case 'm':
 247  	 	 	 	 $s .= "right(digits(month($col)),2)";
 248  	 	 	 	 break;
 249  	 	 	 case 'D':
 250  	 	 	 case 'd':
 251  	 	 	 	 $s .= "right(digits(day($col)),2)";
 252  	 	 	 	 break;
 253  	 	 	 case 'H':
 254  	 	 	 case 'h':
 255  	 	 	 	 if ($col != $this->sysDate) $s .= "right(digits(hour($col)),2)";
 256  	 	 	 	 else $s .= "''";
 257  	 	 	 	 break;
 258  	 	 	 case 'i':
 259  	 	 	 case 'I':
 260  	 	 	 	 if ($col != $this->sysDate)
 261  	 	 	 	 	 $s .= "right(digits(minute($col)),2)";
 262  	 	 	 	 	 else $s .= "''";
 263  	 	 	 	 break;
 264  	 	 	 case 'S':
 265  	 	 	 case 's':
 266  	 	 	 	 if ($col != $this->sysDate)
 267  	 	 	 	 	 $s .= "right(digits(second($col)),2)";
 268  	 	 	 	 else $s .= "''";
 269  	 	 	 	 break;
 270  	 	 	 default:
 271  	 	 	 	 if ($ch == '\\') {
 272  	 	 	 	 	 $i++;
 273  	 	 	 	 	 $ch = substr($fmt,$i,1);
 274  	 	 	 	 }
 275  	 	 	 	 $s .= $this->qstr($ch);
 276  	 	 	 }
 277  	 	 }
 278  	 	 return $s;
 279  	 }
 280  
 281  
 282  	function SelectLimit($sql, $nrows = -1, $offset = -1, $inputArr = false, $secs2cache = 0)
 283  	 {
 284  	 	 $nrows = (integer) $nrows;
 285  	 	 if ($offset <= 0) {
 286  	 	 // could also use " OPTIMIZE FOR $nrows ROWS "
 287  	 	 	 if ($nrows >= 0) $sql .=  " FETCH FIRST $nrows ROWS ONLY ";
 288  	 	 	 $rs = $this->Execute($sql,$inputArr);
 289  	 	 } else {
 290  	 	 	 if ($offset > 0 && $nrows < 0);
 291  	 	 	 else {
 292  	 	 	 	 $nrows += $offset;
 293  	 	 	 	 $sql .=  " FETCH FIRST $nrows ROWS ONLY ";
 294  	 	 	 }
 295  	 	 	 $rs = ADOConnection::SelectLimit($sql,-1,$offset,$inputArr);
 296  	 	 }
 297  
 298  	 	 return $rs;
 299  	 }
 300  
 301  };
 302  
 303  
 304  class  ADORecordSet_odbc_db2 extends ADORecordSet_odbc {
 305  
 306  	 var $databaseType = "db2";
 307  
 308  	function MetaType($t,$len=-1,$fieldobj=false)
 309  	 {
 310  	 	 if (is_object($t)) {
 311  	 	 	 $fieldobj = $t;
 312  	 	 	 $t = $fieldobj->type;
 313  	 	 	 $len = $fieldobj->max_length;
 314  	 	 }
 315  
 316  	 	 switch (strtoupper($t)) {
 317  	 	 case 'VARCHAR':
 318  	 	 case 'CHAR':
 319  	 	 case 'CHARACTER':
 320  	 	 case 'C':
 321  	 	 	 if ($len <= $this->blobSize) return 'C';
 322  
 323  	 	 case 'LONGCHAR':
 324  	 	 case 'TEXT':
 325  	 	 case 'CLOB':
 326  	 	 case 'DBCLOB': // double-byte
 327  	 	 case 'X':
 328  	 	 	 return 'X';
 329  
 330  	 	 case 'BLOB':
 331  	 	 case 'GRAPHIC':
 332  	 	 case 'VARGRAPHIC':
 333  	 	 	 return 'B';
 334  
 335  	 	 case 'DATE':
 336  	 	 case 'D':
 337  	 	 	 return 'D';
 338  
 339  	 	 case 'TIME':
 340  	 	 case 'TIMESTAMP':
 341  	 	 case 'T':
 342  	 	 	 return 'T';
 343  
 344  	 	 //case 'BOOLEAN':
 345  	 	 //case 'BIT':
 346  	 	 //	 return 'L';
 347  
 348  	 	 //case 'COUNTER':
 349  	 	 //	 return 'R';
 350  
 351  	 	 case 'INT':
 352  	 	 case 'INTEGER':
 353  	 	 case 'BIGINT':
 354  	 	 case 'SMALLINT':
 355  	 	 case 'I':
 356  	 	 	 return 'I';
 357  
 358  	 	 default: return ADODB_DEFAULT_METATYPE;
 359  	 	 }
 360  	 }
 361  }
 362  
 363  } //define