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