TYPO3 CMS  TYPO3_6-2
•All Classes Namespaces Files Functions Variables Pages
adodb-odbc_db2.inc.php
Go to the documentation of this file.
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
ADODB_odbc\ADODB_odbc
ADODB_odbc()
Definition: adodb-odbc.inc.php:42
$sql
$sql
Definition: server.php:82
ADORecordSet_odbc
Definition: adodb-odbc.inc.php:601
$rs
if(isset($_REQUEST['nrows'])) else $rs
Definition: server.php:92
ADORecordSet_db2
Definition: adodb-db2.inc.php:722
die
die
Definition: index.php:6
ADODB_odbc\BeginTrans
BeginTrans()
Definition: adodb-odbc.inc.php:213
ADODB_odbc\ServerInfo
ServerInfo()
Definition: adodb-odbc.inc.php:95
ADODB_odbc\MetaTables
MetaTables($ttype=false, $showSchema=false, $mask=false)
Definition: adodb-odbc.inc.php:277
ADODB_odbc
Definition: adodb-odbc.inc.php:22