[Summary view] [Print] [Text view]

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