Updated adodb syysext to upstream version 5.0.8a.
[Packages/TYPO3.CMS.git] / typo3 / sysext / adodb / adodb / drivers / adodb-odbc_db2.inc.php
1 <?php
2 /*
3 V5.08 6 Apr 2009 (c) 2000-2009 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,$flds='1 as ignore')
138 {
139 if ($this->_autocommit) $this->BeginTrans();
140 return $this->GetOne("select $flds 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 ?>