library/adodb/drivers/adodb-db2.inc.php

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