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

   1 <?php
   2 /*
   3 V5.14 8 Sept 2011  (c) 2000-2011 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
 368 ?>