[!!!][TASK] Doctrine: Remove ext:dbal
[Packages/TYPO3.CMS.git] / typo3 / sysext / adodb / adodb / drivers / adodb-odbc_db2.inc.php
1 <?php
2 /*
3 @version v5.20.3 01-Jan-2016
4 @copyright (c) 2000-2013 John Lim (jlim#natsoft.com). All rights reserved.
5 @copyright (c) 2014 Damien Regad, Mark Newnham and the ADOdb community
6 Released under both BSD license and Lesser GPL library license.
7 Whenever there is any discrepancy between the two licenses,
8 the BSD license will take precedence.
9 Set tabs to 4 for best viewing.
10
11 Latest version is available at http://adodb.sourceforge.net
12
13 DB2 data driver. Requires ODBC.
14
15 From phpdb list:
16
17 Hi Andrew,
18
19 thanks a lot for your help. Today we discovered what
20 our real problem was:
21
22 After "playing" a little bit with the php-scripts that try
23 to connect to the IBM DB2, we set the optional parameter
24 Cursortype when calling odbc_pconnect(....).
25
26 And the exciting thing: When we set the cursor type
27 to SQL_CUR_USE_ODBC Cursor Type, then
28 the whole query speed up from 1 till 10 seconds
29 to 0.2 till 0.3 seconds for 100 records. Amazing!!!
30
31 Therfore, PHP is just almost fast as calling the DB2
32 from Servlets using JDBC (don't take too much care
33 about the speed at whole: the database was on a
34 completely other location, so the whole connection
35 was made over a slow network connection).
36
37 I hope this helps when other encounter the same
38 problem when trying to connect to DB2 from
39 PHP.
40
41 Kind regards,
42 Christian Szardenings
43
44 2 Oct 2001
45 Mark Newnham has discovered that the SQL_CUR_USE_ODBC is not supported by
46 IBM's DB2 ODBC driver, so this must be a 3rd party ODBC driver.
47
48 From the IBM CLI Reference:
49
50 SQL_ATTR_ODBC_CURSORS (DB2 CLI v5)
51 This connection attribute is defined by ODBC, but is not supported by DB2
52 CLI. Any attempt to set or get this attribute will result in an SQLSTATE of
53 HYC00 (Driver not capable).
54
55 A 32-bit option specifying how the Driver Manager uses the ODBC cursor
56 library.
57
58 So I guess this means the message [above] was related to using a 3rd party
59 odbc driver.
60
61 Setting SQL_CUR_USE_ODBC
62 ========================
63 To set SQL_CUR_USE_ODBC for drivers that require it, do this:
64
65 $db = NewADOConnection('odbc_db2');
66 $db->curMode = SQL_CUR_USE_ODBC;
67 $db->Connect($dsn, $userid, $pwd);
68
69
70
71 USING CLI INTERFACE
72 ===================
73
74 I have had reports that the $host and $database params have to be reversed in
75 Connect() when using the CLI interface. From Halmai Csongor csongor.halmai#nexum.hu:
76
77 > The symptom is that if I change the database engine from postgres or any other to DB2 then the following
78 > connection command becomes wrong despite being described this version to be correct in the docs.
79 >
80 > $connection_object->Connect( $DATABASE_HOST, $DATABASE_AUTH_USER_NAME, $DATABASE_AUTH_PASSWORD, $DATABASE_NAME )
81 >
82 > In case of DB2 I had to swap the first and last arguments in order to connect properly.
83
84
85 System Error 5
86 ==============
87 IF you get a System Error 5 when trying to Connect/Load, it could be a permission problem. Give the user connecting
88 to DB2 full rights to the DB2 SQLLIB directory, and place the user in the DBUSERS group.
89 */
90
91 // security - hide paths
92 if (!defined('ADODB_DIR')) die();
93
94 if (!defined('_ADODB_ODBC_LAYER')) {
95 include(ADODB_DIR."/drivers/adodb-odbc.inc.php");
96 }
97 if (!defined('ADODB_ODBC_DB2')){
98 define('ADODB_ODBC_DB2',1);
99
100 class ADODB_ODBC_DB2 extends ADODB_odbc {
101 var $databaseType = "db2";
102 var $concat_operator = '||';
103 var $sysTime = 'CURRENT TIME';
104 var $sysDate = 'CURRENT DATE';
105 var $sysTimeStamp = 'CURRENT TIMESTAMP';
106 // The complete string representation of a timestamp has the form
107 // yyyy-mm-dd-hh.mm.ss.nnnnnn.
108 var $fmtTimeStamp = "'Y-m-d-H.i.s'";
109 var $ansiOuter = true;
110 var $identitySQL = 'values IDENTITY_VAL_LOCAL()';
111 var $_bindInputArray = true;
112 var $hasInsertID = true;
113 var $rsPrefix = 'ADORecordset_odbc_';
114
115 function __construct()
116 {
117 if (strncmp(PHP_OS,'WIN',3) === 0) $this->curmode = SQL_CUR_USE_ODBC;
118 parent::__construct();
119 }
120
121 function IfNull( $field, $ifNull )
122 {
123 return " COALESCE($field, $ifNull) "; // if DB2 UDB
124 }
125
126 function ServerInfo()
127 {
128 //odbc_setoption($this->_connectionID,1,101 /*SQL_ATTR_ACCESS_MODE*/, 1 /*SQL_MODE_READ_ONLY*/);
129 $vers = $this->GetOne('select versionnumber from sysibm.sysversions');
130 //odbc_setoption($this->_connectionID,1,101, 0 /*SQL_MODE_READ_WRITE*/);
131 return array('description'=>'DB2 ODBC driver', 'version'=>$vers);
132 }
133
134 function _insertid()
135 {
136 return $this->GetOne($this->identitySQL);
137 }
138
139 function RowLock($tables,$where,$col='1 as adodbignore')
140 {
141 if ($this->_autocommit) $this->BeginTrans();
142 return $this->GetOne("select $col from $tables where $where for update");
143 }
144
145 function MetaTables($ttype=false,$showSchema=false, $qtable="%", $qschema="%")
146 {
147 global $ADODB_FETCH_MODE;
148
149 $savem = $ADODB_FETCH_MODE;
150 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
151 $qid = odbc_tables($this->_connectionID, "", $qschema, $qtable, "");
152
153 $rs = new ADORecordSet_odbc($qid);
154
155 $ADODB_FETCH_MODE = $savem;
156 if (!$rs) {
157 $false = false;
158 return $false;
159 }
160 $rs->_has_stupid_odbc_fetch_api_change = $this->_has_stupid_odbc_fetch_api_change;
161
162 $arr = $rs->GetArray();
163 //print_r($arr);
164
165 $rs->Close();
166 $arr2 = array();
167
168 if ($ttype) {
169 $isview = strncmp($ttype,'V',1) === 0;
170 }
171 for ($i=0; $i < sizeof($arr); $i++) {
172
173 if (!$arr[$i][2]) continue;
174 if (strncmp($arr[$i][1],'SYS',3) === 0) continue;
175
176 $type = $arr[$i][3];
177
178 if ($showSchema) $arr[$i][2] = $arr[$i][1].'.'.$arr[$i][2];
179
180 if ($ttype) {
181 if ($isview) {
182 if (strncmp($type,'V',1) === 0) $arr2[] = $arr[$i][2];
183 } else if (strncmp($type,'T',1) === 0) $arr2[] = $arr[$i][2];
184 } else if (strncmp($type,'S',1) !== 0) $arr2[] = $arr[$i][2];
185 }
186 return $arr2;
187 }
188
189 function MetaIndexes ($table, $primary = FALSE, $owner=false)
190 {
191 // save old fetch mode
192 global $ADODB_FETCH_MODE;
193 $save = $ADODB_FETCH_MODE;
194 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
195 if ($this->fetchMode !== FALSE) {
196 $savem = $this->SetFetchMode(FALSE);
197 }
198 $false = false;
199 // get index details
200 $table = strtoupper($table);
201 $SQL="SELECT NAME, UNIQUERULE, COLNAMES FROM SYSIBM.SYSINDEXES WHERE TBNAME='$table'";
202 if ($primary)
203 $SQL.= " AND UNIQUERULE='P'";
204 $rs = $this->Execute($SQL);
205 if (!is_object($rs)) {
206 if (isset($savem))
207 $this->SetFetchMode($savem);
208 $ADODB_FETCH_MODE = $save;
209 return $false;
210 }
211 $indexes = array ();
212 // parse index data into array
213 while ($row = $rs->FetchRow()) {
214 $indexes[$row[0]] = array(
215 'unique' => ($row[1] == 'U' || $row[1] == 'P'),
216 'columns' => array()
217 );
218 $cols = ltrim($row[2],'+');
219 $indexes[$row[0]]['columns'] = explode('+', $cols);
220 }
221 if (isset($savem)) {
222 $this->SetFetchMode($savem);
223 $ADODB_FETCH_MODE = $save;
224 }
225 return $indexes;
226 }
227
228 // Format date column in sql string given an input format that understands Y M D
229 function SQLDate($fmt, $col=false)
230 {
231 // use right() and replace() ?
232 if (!$col) $col = $this->sysDate;
233 $s = '';
234
235 $len = strlen($fmt);
236 for ($i=0; $i < $len; $i++) {
237 if ($s) $s .= '||';
238 $ch = $fmt[$i];
239 switch($ch) {
240 case 'Y':
241 case 'y':
242 $s .= "char(year($col))";
243 break;
244 case 'M':
245 $s .= "substr(monthname($col),1,3)";
246 break;
247 case 'm':
248 $s .= "right(digits(month($col)),2)";
249 break;
250 case 'D':
251 case 'd':
252 $s .= "right(digits(day($col)),2)";
253 break;
254 case 'H':
255 case 'h':
256 if ($col != $this->sysDate) $s .= "right(digits(hour($col)),2)";
257 else $s .= "''";
258 break;
259 case 'i':
260 case 'I':
261 if ($col != $this->sysDate)
262 $s .= "right(digits(minute($col)),2)";
263 else $s .= "''";
264 break;
265 case 'S':
266 case 's':
267 if ($col != $this->sysDate)
268 $s .= "right(digits(second($col)),2)";
269 else $s .= "''";
270 break;
271 default:
272 if ($ch == '\\') {
273 $i++;
274 $ch = substr($fmt,$i,1);
275 }
276 $s .= $this->qstr($ch);
277 }
278 }
279 return $s;
280 }
281
282
283 function SelectLimit($sql, $nrows = -1, $offset = -1, $inputArr = false, $secs2cache = 0)
284 {
285 $nrows = (integer) $nrows;
286 if ($offset <= 0) {
287 // could also use " OPTIMIZE FOR $nrows ROWS "
288 if ($nrows >= 0) $sql .= " FETCH FIRST $nrows ROWS ONLY ";
289 $rs = $this->Execute($sql,$inputArr);
290 } else {
291 if ($offset > 0 && $nrows < 0);
292 else {
293 $nrows += $offset;
294 $sql .= " FETCH FIRST $nrows ROWS ONLY ";
295 }
296 $rs = ADOConnection::SelectLimit($sql,-1,$offset,$inputArr);
297 }
298
299 return $rs;
300 }
301
302 };
303
304
305 class ADORecordSet_odbc_db2 extends ADORecordSet_odbc {
306
307 var $databaseType = "db2";
308
309 function __construct($id,$mode=false)
310 {
311 parent::__construct($id,$mode);
312 }
313
314 function MetaType($t,$len=-1,$fieldobj=false)
315 {
316 if (is_object($t)) {
317 $fieldobj = $t;
318 $t = $fieldobj->type;
319 $len = $fieldobj->max_length;
320 }
321
322 switch (strtoupper($t)) {
323 case 'VARCHAR':
324 case 'CHAR':
325 case 'CHARACTER':
326 case 'C':
327 if ($len <= $this->blobSize) return 'C';
328
329 case 'LONGCHAR':
330 case 'TEXT':
331 case 'CLOB':
332 case 'DBCLOB': // double-byte
333 case 'X':
334 return 'X';
335
336 case 'BLOB':
337 case 'GRAPHIC':
338 case 'VARGRAPHIC':
339 return 'B';
340
341 case 'DATE':
342 case 'D':
343 return 'D';
344
345 case 'TIME':
346 case 'TIMESTAMP':
347 case 'T':
348 return 'T';
349
350 //case 'BOOLEAN':
351 //case 'BIT':
352 // return 'L';
353
354 //case 'COUNTER':
355 // return 'R';
356
357 case 'INT':
358 case 'INTEGER':
359 case 'BIGINT':
360 case 'SMALLINT':
361 case 'I':
362 return 'I';
363
364 default: return 'N';
365 }
366 }
367 }
368
369 } //define