[Sommaire] [Imprimer]

   1  <?php
   2  /* 
   3  V4.65 22 July 2005  (c) 2000-2005 John Lim (jlim@natsoft.com.my). 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('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  */
  84  
  85  // security - hide paths
  86  if (!defined('ADODB_DIR')) die();
  87  
  88  if (!defined('_ADODB_ODBC_LAYER')) {
  89      include(ADODB_DIR."/drivers/adodb-odbc.inc.php");
  90  }
  91  if (!defined('ADODB_DB2')){
  92  define('ADODB_DB2',1);
  93  
  94  class ADODB_DB2 extends ADODB_odbc {
  95      var $databaseType = "db2";    
  96      var $concat_operator = '||';
  97      var $sysDate = 'CURRENT_DATE';
  98      var $sysTimeStamp = 'CURRENT TIMESTAMP';
  99      // The complete string representation of a timestamp has the form 
 100      // yyyy-mm-dd-hh.mm.ss.nnnnnn.
 101      var $fmtTimeStamp = "'Y-m-d-H.i.s'";
 102      var $ansiOuter = true;
 103      var $identitySQL = 'values IDENTITY_VAL_LOCAL()';
 104      var $_bindInputArray = true;
 105       var $hasInsertID = true;
 106      
 107      function ADODB_DB2()
 108      {
 109          if (strncmp(PHP_OS,'WIN',3) === 0) $this->curmode = SQL_CUR_USE_ODBC;
 110          $this->ADODB_odbc();
 111      }
 112      
 113  	function IfNull( $field, $ifNull ) 
 114      {
 115          return " COALESCE($field, $ifNull) "; // if DB2 UDB
 116      }
 117      
 118  	function ServerInfo()
 119      {
 120          //odbc_setoption($this->_connectionID,1,101 /*SQL_ATTR_ACCESS_MODE*/, 1 /*SQL_MODE_READ_ONLY*/);
 121          $vers = $this->GetOne('select versionnumber from sysibm.sysversions');
 122          //odbc_setoption($this->_connectionID,1,101, 0 /*SQL_MODE_READ_WRITE*/);
 123          return array('description'=>'DB2 ODBC driver', 'version'=>$vers);
 124      }
 125      
 126  	function _insertid()
 127      {
 128          return $this->GetOne($this->identitySQL);
 129      }
 130      
 131  	function RowLock($tables,$where,$flds='1 as ignore')
 132      {
 133          if ($this->_autocommit) $this->BeginTrans();
 134          return $this->GetOne("select $flds from $tables where $where for update");
 135      }
 136      
 137      function &MetaTables($ttype=false,$showSchema=false, $qtable="%", $qschema="%")
 138      {
 139      global $ADODB_FETCH_MODE;
 140      
 141          $savem = $ADODB_FETCH_MODE;
 142          $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 143          $qid = odbc_tables($this->_connectionID, "", $qschema, $qtable, "");
 144          
 145          $rs = new ADORecordSet_odbc($qid);
 146          
 147          $ADODB_FETCH_MODE = $savem;
 148          if (!$rs) {
 149              $false = false;
 150              return $false;
 151          }
 152          $rs->_has_stupid_odbc_fetch_api_change = $this->_has_stupid_odbc_fetch_api_change;
 153          
 154          $arr =& $rs->GetArray();
 155          //print_r($arr);
 156          
 157          $rs->Close();
 158          $arr2 = array();
 159          
 160          if ($ttype) {
 161              $isview = strncmp($ttype,'V',1) === 0;
 162          }
 163          for ($i=0; $i < sizeof($arr); $i++) {
 164          
 165              if (!$arr[$i][2]) continue;
 166              if (strncmp($arr[$i][1],'SYS',3) === 0) continue;
 167              
 168              $type = $arr[$i][3];
 169              
 170              if ($showSchema) $arr[$i][2] = $arr[$i][1].'.'.$arr[$i][2];
 171              
 172              if ($ttype) { 
 173                  if ($isview) {
 174                      if (strncmp($type,'V',1) === 0) $arr2[] = $arr[$i][2];
 175                  } else if (strncmp($type,'T',1) === 0) $arr2[] = $arr[$i][2];
 176              } else if (strncmp($type,'S',1) !== 0) $arr2[] = $arr[$i][2];
 177          }
 178          return $arr2;
 179      }
 180  
 181      function &MetaIndexes ($table, $primary = FALSE, $owner=false)
 182      {
 183          // save old fetch mode
 184          global $ADODB_FETCH_MODE;
 185          $save = $ADODB_FETCH_MODE;
 186          $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 187          if ($this->fetchMode !== FALSE) {
 188                 $savem = $this->SetFetchMode(FALSE);
 189          }
 190          $false = false;
 191          // get index details
 192          $table = strtoupper($table);
 193          $SQL="SELECT NAME, UNIQUERULE, COLNAMES FROM SYSIBM.SYSINDEXES WHERE TBNAME='$table'";
 194          if ($primary) 
 195              $SQL.= " AND UNIQUERULE='P'";
 196          $rs = $this->Execute($SQL);
 197          if (!is_object($rs)) {
 198              if (isset($savem)) 
 199                  $this->SetFetchMode($savem);
 200              $ADODB_FETCH_MODE = $save;
 201              return $false;
 202          }
 203          $indexes = array ();
 204          // parse index data into array
 205          while ($row = $rs->FetchRow()) {
 206              $indexes[$row[0]] = array(
 207                 'unique' => ($row[1] == 'U' || $row[1] == 'P'),
 208                 'columns' => array()
 209              );
 210              $cols = ltrim($row[2],'+');
 211              $indexes[$row[0]]['columns'] = explode('+', $cols);
 212          }
 213          if (isset($savem)) { 
 214              $this->SetFetchMode($savem);
 215              $ADODB_FETCH_MODE = $save;
 216          }
 217          return $indexes;
 218      }
 219      
 220      // Format date column in sql string given an input format that understands Y M D
 221  	function SQLDate($fmt, $col=false)
 222      {    
 223      // use right() and replace() ?
 224          if (!$col) $col = $this->sysDate;
 225          $s = '';
 226          
 227          $len = strlen($fmt);
 228          for ($i=0; $i < $len; $i++) {
 229              if ($s) $s .= '||';
 230              $ch = $fmt[$i];
 231              switch($ch) {
 232              case 'Y':
 233              case 'y':
 234                  $s .= "char(year($col))";
 235                  break;
 236              case 'M':
 237                  $s .= "substr(monthname($col),1,3)";
 238                  break;
 239              case 'm':
 240                  $s .= "right(digits(month($col)),2)";
 241                  break;
 242              case 'D':
 243              case 'd':
 244                  $s .= "right(digits(day($col)),2)";
 245                  break;
 246              case 'H':
 247              case 'h':
 248                  if ($col != $this->sysDate) $s .= "right(digits(hour($col)),2)";    
 249                  else $s .= "''";
 250                  break;
 251              case 'i':
 252              case 'I':
 253                  if ($col != $this->sysDate)
 254                      $s .= "right(digits(minute($col)),2)";
 255                      else $s .= "''";
 256                  break;
 257              case 'S':
 258              case 's':
 259                  if ($col != $this->sysDate)
 260                      $s .= "right(digits(second($col)),2)";
 261                  else $s .= "''";
 262                  break;
 263              default:
 264                  if ($ch == '\\') {
 265                      $i++;
 266                      $ch = substr($fmt,$i,1);
 267                  }
 268                  $s .= $this->qstr($ch);
 269              }
 270          }
 271          return $s;
 272      } 
 273   
 274      
 275      function &SelectLimit($sql,$nrows=-1,$offset=-1,$inputArr=false)
 276      {
 277          if ($offset <= 0) {
 278          // could also use " OPTIMIZE FOR $nrows ROWS "
 279              if ($nrows >= 0) $sql .=  " FETCH FIRST $nrows ROWS ONLY ";
 280              $rs =& $this->Execute($sql,$inputArr);
 281          } else {
 282              if ($offset > 0 && $nrows < 0);
 283              else {
 284                  $nrows += $offset;
 285                  $sql .=  " FETCH FIRST $nrows ROWS ONLY ";
 286              }
 287              $rs =& ADOConnection::SelectLimit($sql,-1,$offset,$inputArr);
 288          }
 289          
 290          return $rs;
 291      }
 292      
 293  };
 294   
 295  
 296  class  ADORecordSet_db2 extends ADORecordSet_odbc {    
 297      
 298      var $databaseType = "db2";        
 299      
 300  	function ADORecordSet_db2($id,$mode=false)
 301      {
 302          $this->ADORecordSet_odbc($id,$mode);
 303      }
 304  
 305  	function MetaType($t,$len=-1,$fieldobj=false)
 306      {
 307          if (is_object($t)) {
 308              $fieldobj = $t;
 309              $t = $fieldobj->type;
 310              $len = $fieldobj->max_length;
 311          }
 312          
 313          switch (strtoupper($t)) {
 314          case 'VARCHAR':
 315          case 'CHAR':
 316          case 'CHARACTER':
 317          case 'C':
 318              if ($len <= $this->blobSize) return 'C';
 319          
 320          case 'LONGCHAR':
 321          case 'TEXT':
 322          case 'CLOB':
 323          case 'DBCLOB': // double-byte
 324          case 'X':
 325              return 'X';
 326          
 327          case 'BLOB':
 328          case 'GRAPHIC':
 329          case 'VARGRAPHIC':
 330              return 'B';
 331              
 332          case 'DATE':
 333          case 'D':
 334              return 'D';
 335          
 336          case 'TIME':
 337          case 'TIMESTAMP':
 338          case 'T':
 339              return 'T';
 340          
 341          //case 'BOOLEAN': 
 342          //case 'BIT':
 343          //    return 'L';
 344              
 345          //case 'COUNTER':
 346          //    return 'R';
 347              
 348          case 'INT':
 349          case 'INTEGER':
 350          case 'BIGINT':
 351          case 'SMALLINT':
 352          case 'I':
 353              return 'I';
 354              
 355          default: return 'N';
 356          }
 357      }
 358  }
 359  
 360  } //define
 361  ?>