"TYPO3 4.0.1: typo3_src-4.0.1/typo3/sysext/adodb/adodb/drivers/adodb-odbc_db2.inc.php Source File", "datetime" => "Sat Dec 2 19:22:26 2006", "date" => "2 Dec 2006", "doxygenversion" => "1.4.6", "projectname" => "TYPO3 4.0.1", "projectnumber" => "4.0.1" ); get_header($doxygen_vars); ?>

adodb-odbc_db2.inc.php

00001 <?php
00002 /* 
00003 V4.90 8 June 2006  (c) 2000-2006 John Lim (jlim#natsoft.com.my). All rights reserved.
00004   Released under both BSD license and Lesser GPL library license. 
00005   Whenever there is any discrepancy between the two licenses, 
00006   the BSD license will take precedence. 
00007 Set tabs to 4 for best viewing.
00008   
00009   Latest version is available at http://adodb.sourceforge.net
00010   
00011   DB2 data driver. Requires ODBC.
00012  
00013 From phpdb list:
00014 
00015 Hi Andrew,
00016 
00017 thanks a lot for your help. Today we discovered what
00018 our real problem was:
00019 
00020 After "playing" a little bit with the php-scripts that try
00021 to connect to the IBM DB2, we set the optional parameter
00022 Cursortype when calling odbc_pconnect(....).
00023 
00024 And the exciting thing: When we set the cursor type
00025 to SQL_CUR_USE_ODBC Cursor Type, then
00026 the whole query speed up from 1 till 10 seconds
00027 to 0.2 till 0.3 seconds for 100 records. Amazing!!!
00028 
00029 Therfore, PHP is just almost fast as calling the DB2
00030 from Servlets using JDBC (don't take too much care
00031 about the speed at whole: the database was on a
00032 completely other location, so the whole connection
00033 was made over a slow network connection).
00034 
00035 I hope this helps when other encounter the same
00036 problem when trying to connect to DB2 from
00037 PHP.
00038 
00039 Kind regards,
00040 Christian Szardenings
00041 
00042 2 Oct 2001
00043 Mark Newnham has discovered that the SQL_CUR_USE_ODBC is not supported by 
00044 IBM's DB2 ODBC driver, so this must be a 3rd party ODBC driver.
00045 
00046 From the IBM CLI Reference:
00047 
00048 SQL_ATTR_ODBC_CURSORS (DB2 CLI v5) 
00049 This connection attribute is defined by ODBC, but is not supported by DB2
00050 CLI. Any attempt to set or get this attribute will result in an SQLSTATE of
00051 HYC00 (Driver not capable). 
00052 
00053 A 32-bit option specifying how the Driver Manager uses the ODBC cursor
00054 library. 
00055 
00056 So I guess this means the message [above] was related to using a 3rd party
00057 odbc driver.
00058 
00059 Setting SQL_CUR_USE_ODBC
00060 ========================
00061 To set SQL_CUR_USE_ODBC for drivers that require it, do this:
00062 
00063 $db = NewADOConnection('odbc_db2');
00064 $db->curMode = SQL_CUR_USE_ODBC;
00065 $db->Connect($dsn, $userid, $pwd);
00066 
00067 
00068 
00069 USING CLI INTERFACE
00070 ===================
00071 
00072 I have had reports that the $host and $database params have to be reversed in 
00073 Connect() when using the CLI interface. From Halmai Csongor csongor.halmai#nexum.hu:
00074 
00075 > The symptom is that if I change the database engine from postgres or any other to DB2 then the following
00076 > connection command becomes wrong despite being described this version to be correct in the docs. 
00077 >
00078 > $connection_object->Connect( $DATABASE_HOST, $DATABASE_AUTH_USER_NAME, $DATABASE_AUTH_PASSWORD, $DATABASE_NAME )
00079 >
00080 > In case of DB2 I had to swap the first and last arguments in order to connect properly. 
00081 
00082 
00083 System Error 5
00084 ==============
00085 IF you get a System Error 5 when trying to Connect/Load, it could be a permission problem. Give the user connecting
00086 to DB2 full rights to the DB2 SQLLIB directory, and place the user in the DBUSERS group.
00087 */
00088 
00089 // security - hide paths
00090 if (!defined('ADODB_DIR')) die();
00091 
00092 if (!defined('_ADODB_ODBC_LAYER')) {
00093         include(ADODB_DIR."/drivers/adodb-odbc.inc.php");
00094 }
00095 if (!defined('ADODB_ODBC_DB2')){
00096 define('ADODB_ODBC_DB2',1);
00097 
00098 class ADODB_ODBC_DB2 extends ADODB_odbc {
00099         var $databaseType = "db2";      
00100         var $concat_operator = '||';
00101         var $sysTime = 'CURRENT TIME';
00102         var $sysDate = 'CURRENT DATE';
00103         var $sysTimeStamp = 'CURRENT TIMESTAMP';
00104         // The complete string representation of a timestamp has the form 
00105         // yyyy-mm-dd-hh.mm.ss.nnnnnn.
00106         var $fmtTimeStamp = "'Y-m-d-H.i.s'";
00107         var $ansiOuter = true;
00108         var $identitySQL = 'values IDENTITY_VAL_LOCAL()';
00109         var $_bindInputArray = true;
00110          var $hasInsertID = true;
00111         var $rsPrefix = 'ADORecordset_odbc_';
00112         
00113         function ADODB_DB2()
00114         {
00115                 if (strncmp(PHP_OS,'WIN',3) === 0) $this->curmode = SQL_CUR_USE_ODBC;
00116                 $this->ADODB_odbc();
00117         }
00118         
00119         function IfNull( $field, $ifNull ) 
00120         {
00121                 return " COALESCE($field, $ifNull) "; // if DB2 UDB
00122         }
00123         
00124         function ServerInfo()
00125         {
00126                 //odbc_setoption($this->_connectionID,1,101 /*SQL_ATTR_ACCESS_MODE*/, 1 /*SQL_MODE_READ_ONLY*/);
00127                 $vers = $this->GetOne('select versionnumber from sysibm.sysversions');
00128                 //odbc_setoption($this->_connectionID,1,101, 0 /*SQL_MODE_READ_WRITE*/);
00129                 return array('description'=>'DB2 ODBC driver', 'version'=>$vers);
00130         }
00131         
00132         function _insertid()
00133         {
00134                 return $this->GetOne($this->identitySQL);
00135         }
00136         
00137         function RowLock($tables,$where,$flds='1 as ignore')
00138         {
00139                 if ($this->_autocommit) $this->BeginTrans();
00140                 return $this->GetOne("select $flds from $tables where $where for update");
00141         }
00142         
00143         function &MetaTables($ttype=false,$showSchema=false, $qtable="%", $qschema="%")
00144         {
00145         global $ADODB_FETCH_MODE;
00146         
00147                 $savem = $ADODB_FETCH_MODE;
00148                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00149                 $qid = odbc_tables($this->_connectionID, "", $qschema, $qtable, "");
00150                 
00151                 $rs = new ADORecordSet_odbc($qid);
00152                 
00153                 $ADODB_FETCH_MODE = $savem;
00154                 if (!$rs) {
00155                         $false = false;
00156                         return $false;
00157                 }
00158                 $rs->_has_stupid_odbc_fetch_api_change = $this->_has_stupid_odbc_fetch_api_change;
00159                 
00160                 $arr =& $rs->GetArray();
00161                 //print_r($arr);
00162                 
00163                 $rs->Close();
00164                 $arr2 = array();
00165                 
00166                 if ($ttype) {
00167                         $isview = strncmp($ttype,'V',1) === 0;
00168                 }
00169                 for ($i=0; $i < sizeof($arr); $i++) {
00170                 
00171                         if (!$arr[$i][2]) continue;
00172                         if (strncmp($arr[$i][1],'SYS',3) === 0) continue;
00173                         
00174                         $type = $arr[$i][3];
00175                         
00176                         if ($showSchema) $arr[$i][2] = $arr[$i][1].'.'.$arr[$i][2];
00177                         
00178                         if ($ttype) { 
00179                                 if ($isview) {
00180                                         if (strncmp($type,'V',1) === 0) $arr2[] = $arr[$i][2];
00181                                 } else if (strncmp($type,'T',1) === 0) $arr2[] = $arr[$i][2];
00182                         } else if (strncmp($type,'S',1) !== 0) $arr2[] = $arr[$i][2];
00183                 }
00184                 return $arr2;
00185         }
00186 
00187         function &MetaIndexes ($table, $primary = FALSE, $owner=false)
00188         {
00189         // save old fetch mode
00190         global $ADODB_FETCH_MODE;
00191         $save = $ADODB_FETCH_MODE;
00192         $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00193         if ($this->fetchMode !== FALSE) {
00194                $savem = $this->SetFetchMode(FALSE);
00195         }
00196                 $false = false;
00197                 // get index details
00198                 $table = strtoupper($table);
00199                 $SQL="SELECT NAME, UNIQUERULE, COLNAMES FROM SYSIBM.SYSINDEXES WHERE TBNAME='$table'";
00200         if ($primary) 
00201                         $SQL.= " AND UNIQUERULE='P'";
00202                 $rs = $this->Execute($SQL);
00203         if (!is_object($rs)) {
00204                         if (isset($savem)) 
00205                                 $this->SetFetchMode($savem);
00206                         $ADODB_FETCH_MODE = $save;
00207             return $false;
00208         }
00209                 $indexes = array ();
00210         // parse index data into array
00211         while ($row = $rs->FetchRow()) {
00212                         $indexes[$row[0]] = array(
00213                            'unique' => ($row[1] == 'U' || $row[1] == 'P'),
00214                            'columns' => array()
00215                         );
00216                         $cols = ltrim($row[2],'+');
00217                         $indexes[$row[0]]['columns'] = explode('+', $cols);
00218         }
00219                 if (isset($savem)) { 
00220             $this->SetFetchMode($savem);
00221                         $ADODB_FETCH_MODE = $save;
00222                 }
00223         return $indexes;
00224         }
00225         
00226         // Format date column in sql string given an input format that understands Y M D
00227         function SQLDate($fmt, $col=false)
00228         {       
00229         // use right() and replace() ?
00230                 if (!$col) $col = $this->sysDate;
00231                 $s = '';
00232                 
00233                 $len = strlen($fmt);
00234                 for ($i=0; $i < $len; $i++) {
00235                         if ($s) $s .= '||';
00236                         $ch = $fmt[$i];
00237                         switch($ch) {
00238                         case 'Y':
00239                         case 'y':
00240                                 $s .= "char(year($col))";
00241                                 break;
00242                         case 'M':
00243                                 $s .= "substr(monthname($col),1,3)";
00244                                 break;
00245                         case 'm':
00246                                 $s .= "right(digits(month($col)),2)";
00247                                 break;
00248                         case 'D':
00249                         case 'd':
00250                                 $s .= "right(digits(day($col)),2)";
00251                                 break;
00252                         case 'H':
00253                         case 'h':
00254                                 if ($col != $this->sysDate) $s .= "right(digits(hour($col)),2)";        
00255                                 else $s .= "''";
00256                                 break;
00257                         case 'i':
00258                         case 'I':
00259                                 if ($col != $this->sysDate)
00260                                         $s .= "right(digits(minute($col)),2)";
00261                                         else $s .= "''";
00262                                 break;
00263                         case 'S':
00264                         case 's':
00265                                 if ($col != $this->sysDate)
00266                                         $s .= "right(digits(second($col)),2)";
00267                                 else $s .= "''";
00268                                 break;
00269                         default:
00270                                 if ($ch == '\\') {
00271                                         $i++;
00272                                         $ch = substr($fmt,$i,1);
00273                                 }
00274                                 $s .= $this->qstr($ch);
00275                         }
00276                 }
00277                 return $s;
00278         } 
00279  
00280         
00281         function &SelectLimit($sql,$nrows=-1,$offset=-1,$inputArr=false)
00282         {
00283                 $nrows = (integer) $nrows;
00284                 if ($offset <= 0) {
00285                 // could also use " OPTIMIZE FOR $nrows ROWS "
00286                         if ($nrows >= 0) $sql .=  " FETCH FIRST $nrows ROWS ONLY ";
00287                         $rs =& $this->Execute($sql,$inputArr);
00288                 } else {
00289                         if ($offset > 0 && $nrows < 0);
00290                         else {
00291                                 $nrows += $offset;
00292                                 $sql .=  " FETCH FIRST $nrows ROWS ONLY ";
00293                         }
00294                         $rs =& ADOConnection::SelectLimit($sql,-1,$offset,$inputArr);
00295                 }
00296                 
00297                 return $rs;
00298         }
00299         
00300 };
00301  
00302 
00303 class  ADORecordSet_odbc_db2 extends ADORecordSet_odbc {        
00304         
00305         var $databaseType = "db2";              
00306         
00307         function ADORecordSet_db2($id,$mode=false)
00308         {
00309                 $this->ADORecordSet_odbc($id,$mode);
00310         }
00311 
00312         function MetaType($t,$len=-1,$fieldobj=false)
00313         {
00314                 if (is_object($t)) {
00315                         $fieldobj = $t;
00316                         $t = $fieldobj->type;
00317                         $len = $fieldobj->max_length;
00318                 }
00319                 
00320                 switch (strtoupper($t)) {
00321                 case 'VARCHAR':
00322                 case 'CHAR':
00323                 case 'CHARACTER':
00324                 case 'C':
00325                         if ($len <= $this->blobSize) return 'C';
00326                 
00327                 case 'LONGCHAR':
00328                 case 'TEXT':
00329                 case 'CLOB':
00330                 case 'DBCLOB': // double-byte
00331                 case 'X':
00332                         return 'X';
00333                 
00334                 case 'BLOB':
00335                 case 'GRAPHIC':
00336                 case 'VARGRAPHIC':
00337                         return 'B';
00338                         
00339                 case 'DATE':
00340                 case 'D':
00341                         return 'D';
00342                 
00343                 case 'TIME':
00344                 case 'TIMESTAMP':
00345                 case 'T':
00346                         return 'T';
00347                 
00348                 //case 'BOOLEAN': 
00349                 //case 'BIT':
00350                 //      return 'L';
00351                         
00352                 //case 'COUNTER':
00353                 //      return 'R';
00354                         
00355                 case 'INT':
00356                 case 'INTEGER':
00357                 case 'BIGINT':
00358                 case 'SMALLINT':
00359                 case 'I':
00360                         return 'I';
00361                         
00362                 default: return 'N';
00363                 }
00364         }
00365 }
00366 
00367 } //define
00368 ?>