[Summary view] [Print] [Text view]

   1  <?php
   2  /*
   3  @version   v5.20.3  01-Jan-2016
   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.sourceforge.net
  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