|
1 <?php |
|
2 /** |
|
3 * Zend Framework |
|
4 * |
|
5 * LICENSE |
|
6 * |
|
7 * This source file is subject to the new BSD license that is bundled |
|
8 * with this package in the file LICENSE.txt. |
|
9 * It is also available through the world-wide-web at this URL: |
|
10 * http://framework.zend.com/license/new-bsd |
|
11 * If you did not receive a copy of the license and are unable to |
|
12 * obtain it through the world-wide-web, please send an email |
|
13 * to license@zend.com so we can send you a copy immediately. |
|
14 * |
|
15 * @category Zend |
|
16 * @package Zend_Db |
|
17 * @subpackage Adapter |
|
18 * @copyright Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com) |
|
19 * @license http://framework.zend.com/license/new-bsd New BSD License |
|
20 * @version $Id: Db2.php 23199 2010-10-21 14:27:06Z ralph $ |
|
21 * |
|
22 */ |
|
23 |
|
24 /** |
|
25 * @see Zend_Db |
|
26 */ |
|
27 require_once 'Zend/Db.php'; |
|
28 |
|
29 /** |
|
30 * @see Zend_Db_Adapter_Abstract |
|
31 */ |
|
32 require_once 'Zend/Db/Adapter/Abstract.php'; |
|
33 |
|
34 /** |
|
35 * @see Zend_Db_Statement_Db2 |
|
36 */ |
|
37 require_once 'Zend/Db/Statement/Db2.php'; |
|
38 |
|
39 |
|
40 /** |
|
41 * @package Zend_Db |
|
42 * @copyright Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com) |
|
43 * @license http://framework.zend.com/license/new-bsd New BSD License |
|
44 */ |
|
45 |
|
46 class Zend_Db_Adapter_Db2 extends Zend_Db_Adapter_Abstract |
|
47 { |
|
48 /** |
|
49 * User-provided configuration. |
|
50 * |
|
51 * Basic keys are: |
|
52 * |
|
53 * username => (string) Connect to the database as this username. |
|
54 * password => (string) Password associated with the username. |
|
55 * host => (string) What host to connect to (default 127.0.0.1) |
|
56 * dbname => (string) The name of the database to user |
|
57 * protocol => (string) Protocol to use, defaults to "TCPIP" |
|
58 * port => (integer) Port number to use for TCP/IP if protocol is "TCPIP" |
|
59 * persistent => (boolean) Set TRUE to use a persistent connection (db2_pconnect) |
|
60 * os => (string) This should be set to 'i5' if the db is on an os400/i5 |
|
61 * schema => (string) The default schema the connection should use |
|
62 * |
|
63 * @var array |
|
64 */ |
|
65 protected $_config = array( |
|
66 'dbname' => null, |
|
67 'username' => null, |
|
68 'password' => null, |
|
69 'host' => 'localhost', |
|
70 'port' => '50000', |
|
71 'protocol' => 'TCPIP', |
|
72 'persistent' => false, |
|
73 'os' => null, |
|
74 'schema' => null |
|
75 ); |
|
76 |
|
77 /** |
|
78 * Execution mode |
|
79 * |
|
80 * @var int execution flag (DB2_AUTOCOMMIT_ON or DB2_AUTOCOMMIT_OFF) |
|
81 */ |
|
82 protected $_execute_mode = DB2_AUTOCOMMIT_ON; |
|
83 |
|
84 /** |
|
85 * Default class name for a DB statement. |
|
86 * |
|
87 * @var string |
|
88 */ |
|
89 protected $_defaultStmtClass = 'Zend_Db_Statement_Db2'; |
|
90 protected $_isI5 = false; |
|
91 |
|
92 /** |
|
93 * Keys are UPPERCASE SQL datatypes or the constants |
|
94 * Zend_Db::INT_TYPE, Zend_Db::BIGINT_TYPE, or Zend_Db::FLOAT_TYPE. |
|
95 * |
|
96 * Values are: |
|
97 * 0 = 32-bit integer |
|
98 * 1 = 64-bit integer |
|
99 * 2 = float or decimal |
|
100 * |
|
101 * @var array Associative array of datatypes to values 0, 1, or 2. |
|
102 */ |
|
103 protected $_numericDataTypes = array( |
|
104 Zend_Db::INT_TYPE => Zend_Db::INT_TYPE, |
|
105 Zend_Db::BIGINT_TYPE => Zend_Db::BIGINT_TYPE, |
|
106 Zend_Db::FLOAT_TYPE => Zend_Db::FLOAT_TYPE, |
|
107 'INTEGER' => Zend_Db::INT_TYPE, |
|
108 'SMALLINT' => Zend_Db::INT_TYPE, |
|
109 'BIGINT' => Zend_Db::BIGINT_TYPE, |
|
110 'DECIMAL' => Zend_Db::FLOAT_TYPE, |
|
111 'NUMERIC' => Zend_Db::FLOAT_TYPE |
|
112 ); |
|
113 |
|
114 /** |
|
115 * Creates a connection resource. |
|
116 * |
|
117 * @return void |
|
118 */ |
|
119 protected function _connect() |
|
120 { |
|
121 if (is_resource($this->_connection)) { |
|
122 // connection already exists |
|
123 return; |
|
124 } |
|
125 |
|
126 if (!extension_loaded('ibm_db2')) { |
|
127 /** |
|
128 * @see Zend_Db_Adapter_Db2_Exception |
|
129 */ |
|
130 require_once 'Zend/Db/Adapter/Db2/Exception.php'; |
|
131 throw new Zend_Db_Adapter_Db2_Exception('The IBM DB2 extension is required for this adapter but the extension is not loaded'); |
|
132 } |
|
133 |
|
134 $this->_determineI5(); |
|
135 if ($this->_config['persistent']) { |
|
136 // use persistent connection |
|
137 $conn_func_name = 'db2_pconnect'; |
|
138 } else { |
|
139 // use "normal" connection |
|
140 $conn_func_name = 'db2_connect'; |
|
141 } |
|
142 |
|
143 if (!isset($this->_config['driver_options']['autocommit'])) { |
|
144 // set execution mode |
|
145 $this->_config['driver_options']['autocommit'] = &$this->_execute_mode; |
|
146 } |
|
147 |
|
148 if (isset($this->_config['options'][Zend_Db::CASE_FOLDING])) { |
|
149 $caseAttrMap = array( |
|
150 Zend_Db::CASE_NATURAL => DB2_CASE_NATURAL, |
|
151 Zend_Db::CASE_UPPER => DB2_CASE_UPPER, |
|
152 Zend_Db::CASE_LOWER => DB2_CASE_LOWER |
|
153 ); |
|
154 $this->_config['driver_options']['DB2_ATTR_CASE'] = $caseAttrMap[$this->_config['options'][Zend_Db::CASE_FOLDING]]; |
|
155 } |
|
156 |
|
157 if ($this->_isI5 && isset($this->_config['driver_options']['i5_naming'])) { |
|
158 if ($this->_config['driver_options']['i5_naming']) { |
|
159 $this->_config['driver_options']['i5_naming'] = DB2_I5_NAMING_ON; |
|
160 } else { |
|
161 $this->_config['driver_options']['i5_naming'] = DB2_I5_NAMING_OFF; |
|
162 } |
|
163 } |
|
164 |
|
165 if ($this->_config['host'] !== 'localhost' && !$this->_isI5) { |
|
166 // if the host isn't localhost, use extended connection params |
|
167 $dbname = 'DRIVER={IBM DB2 ODBC DRIVER}' . |
|
168 ';DATABASE=' . $this->_config['dbname'] . |
|
169 ';HOSTNAME=' . $this->_config['host'] . |
|
170 ';PORT=' . $this->_config['port'] . |
|
171 ';PROTOCOL=' . $this->_config['protocol'] . |
|
172 ';UID=' . $this->_config['username'] . |
|
173 ';PWD=' . $this->_config['password'] .';'; |
|
174 $this->_connection = $conn_func_name( |
|
175 $dbname, |
|
176 null, |
|
177 null, |
|
178 $this->_config['driver_options'] |
|
179 ); |
|
180 } else { |
|
181 // host is localhost, so use standard connection params |
|
182 $this->_connection = $conn_func_name( |
|
183 $this->_config['dbname'], |
|
184 $this->_config['username'], |
|
185 $this->_config['password'], |
|
186 $this->_config['driver_options'] |
|
187 ); |
|
188 } |
|
189 |
|
190 // check the connection |
|
191 if (!$this->_connection) { |
|
192 /** |
|
193 * @see Zend_Db_Adapter_Db2_Exception |
|
194 */ |
|
195 require_once 'Zend/Db/Adapter/Db2/Exception.php'; |
|
196 throw new Zend_Db_Adapter_Db2_Exception(db2_conn_errormsg(), db2_conn_error()); |
|
197 } |
|
198 } |
|
199 |
|
200 /** |
|
201 * Test if a connection is active |
|
202 * |
|
203 * @return boolean |
|
204 */ |
|
205 public function isConnected() |
|
206 { |
|
207 return ((bool) (is_resource($this->_connection) |
|
208 && get_resource_type($this->_connection) == 'DB2 Connection')); |
|
209 } |
|
210 |
|
211 /** |
|
212 * Force the connection to close. |
|
213 * |
|
214 * @return void |
|
215 */ |
|
216 public function closeConnection() |
|
217 { |
|
218 if ($this->isConnected()) { |
|
219 db2_close($this->_connection); |
|
220 } |
|
221 $this->_connection = null; |
|
222 } |
|
223 |
|
224 /** |
|
225 * Returns an SQL statement for preparation. |
|
226 * |
|
227 * @param string $sql The SQL statement with placeholders. |
|
228 * @return Zend_Db_Statement_Db2 |
|
229 */ |
|
230 public function prepare($sql) |
|
231 { |
|
232 $this->_connect(); |
|
233 $stmtClass = $this->_defaultStmtClass; |
|
234 if (!class_exists($stmtClass)) { |
|
235 require_once 'Zend/Loader.php'; |
|
236 Zend_Loader::loadClass($stmtClass); |
|
237 } |
|
238 $stmt = new $stmtClass($this, $sql); |
|
239 $stmt->setFetchMode($this->_fetchMode); |
|
240 return $stmt; |
|
241 } |
|
242 |
|
243 /** |
|
244 * Gets the execution mode |
|
245 * |
|
246 * @return int the execution mode (DB2_AUTOCOMMIT_ON or DB2_AUTOCOMMIT_OFF) |
|
247 */ |
|
248 public function _getExecuteMode() |
|
249 { |
|
250 return $this->_execute_mode; |
|
251 } |
|
252 |
|
253 /** |
|
254 * @param integer $mode |
|
255 * @return void |
|
256 */ |
|
257 public function _setExecuteMode($mode) |
|
258 { |
|
259 switch ($mode) { |
|
260 case DB2_AUTOCOMMIT_OFF: |
|
261 case DB2_AUTOCOMMIT_ON: |
|
262 $this->_execute_mode = $mode; |
|
263 db2_autocommit($this->_connection, $mode); |
|
264 break; |
|
265 default: |
|
266 /** |
|
267 * @see Zend_Db_Adapter_Db2_Exception |
|
268 */ |
|
269 require_once 'Zend/Db/Adapter/Db2/Exception.php'; |
|
270 throw new Zend_Db_Adapter_Db2_Exception("execution mode not supported"); |
|
271 break; |
|
272 } |
|
273 } |
|
274 |
|
275 /** |
|
276 * Quote a raw string. |
|
277 * |
|
278 * @param string $value Raw string |
|
279 * @return string Quoted string |
|
280 */ |
|
281 protected function _quote($value) |
|
282 { |
|
283 if (is_int($value) || is_float($value)) { |
|
284 return $value; |
|
285 } |
|
286 /** |
|
287 * Use db2_escape_string() if it is present in the IBM DB2 extension. |
|
288 * But some supported versions of PHP do not include this function, |
|
289 * so fall back to default quoting in the parent class. |
|
290 */ |
|
291 if (function_exists('db2_escape_string')) { |
|
292 return "'" . db2_escape_string($value) . "'"; |
|
293 } |
|
294 return parent::_quote($value); |
|
295 } |
|
296 |
|
297 /** |
|
298 * @return string |
|
299 */ |
|
300 public function getQuoteIdentifierSymbol() |
|
301 { |
|
302 $this->_connect(); |
|
303 $info = db2_server_info($this->_connection); |
|
304 if ($info) { |
|
305 $identQuote = $info->IDENTIFIER_QUOTE_CHAR; |
|
306 } else { |
|
307 // db2_server_info() does not return result on some i5 OS version |
|
308 if ($this->_isI5) { |
|
309 $identQuote ="'"; |
|
310 } |
|
311 } |
|
312 return $identQuote; |
|
313 } |
|
314 |
|
315 /** |
|
316 * Returns a list of the tables in the database. |
|
317 * @param string $schema OPTIONAL |
|
318 * @return array |
|
319 */ |
|
320 public function listTables($schema = null) |
|
321 { |
|
322 $this->_connect(); |
|
323 |
|
324 if ($schema === null && $this->_config['schema'] != null) { |
|
325 $schema = $this->_config['schema']; |
|
326 } |
|
327 |
|
328 $tables = array(); |
|
329 |
|
330 if (!$this->_isI5) { |
|
331 if ($schema) { |
|
332 $stmt = db2_tables($this->_connection, null, $schema); |
|
333 } else { |
|
334 $stmt = db2_tables($this->_connection); |
|
335 } |
|
336 while ($row = db2_fetch_assoc($stmt)) { |
|
337 $tables[] = $row['TABLE_NAME']; |
|
338 } |
|
339 } else { |
|
340 $tables = $this->_i5listTables($schema); |
|
341 } |
|
342 |
|
343 return $tables; |
|
344 } |
|
345 |
|
346 |
|
347 /** |
|
348 * Returns the column descriptions for a table. |
|
349 * |
|
350 * The return value is an associative array keyed by the column name, |
|
351 * as returned by the RDBMS. |
|
352 * |
|
353 * The value of each array element is an associative array |
|
354 * with the following keys: |
|
355 * |
|
356 * SCHEMA_NAME => string; name of database or schema |
|
357 * TABLE_NAME => string; |
|
358 * COLUMN_NAME => string; column name |
|
359 * COLUMN_POSITION => number; ordinal position of column in table |
|
360 * DATA_TYPE => string; SQL datatype name of column |
|
361 * DEFAULT => string; default expression of column, null if none |
|
362 * NULLABLE => boolean; true if column can have nulls |
|
363 * LENGTH => number; length of CHAR/VARCHAR |
|
364 * SCALE => number; scale of NUMERIC/DECIMAL |
|
365 * PRECISION => number; precision of NUMERIC/DECIMAL |
|
366 * UNSIGNED => boolean; unsigned property of an integer type |
|
367 * DB2 not supports UNSIGNED integer. |
|
368 * PRIMARY => boolean; true if column is part of the primary key |
|
369 * PRIMARY_POSITION => integer; position of column in primary key |
|
370 * IDENTITY => integer; true if column is auto-generated with unique values |
|
371 * |
|
372 * @param string $tableName |
|
373 * @param string $schemaName OPTIONAL |
|
374 * @return array |
|
375 */ |
|
376 public function describeTable($tableName, $schemaName = null) |
|
377 { |
|
378 // Ensure the connection is made so that _isI5 is set |
|
379 $this->_connect(); |
|
380 |
|
381 if ($schemaName === null && $this->_config['schema'] != null) { |
|
382 $schemaName = $this->_config['schema']; |
|
383 } |
|
384 |
|
385 if (!$this->_isI5) { |
|
386 |
|
387 $sql = "SELECT DISTINCT c.tabschema, c.tabname, c.colname, c.colno, |
|
388 c.typename, c.default, c.nulls, c.length, c.scale, |
|
389 c.identity, tc.type AS tabconsttype, k.colseq |
|
390 FROM syscat.columns c |
|
391 LEFT JOIN (syscat.keycoluse k JOIN syscat.tabconst tc |
|
392 ON (k.tabschema = tc.tabschema |
|
393 AND k.tabname = tc.tabname |
|
394 AND tc.type = 'P')) |
|
395 ON (c.tabschema = k.tabschema |
|
396 AND c.tabname = k.tabname |
|
397 AND c.colname = k.colname) |
|
398 WHERE " |
|
399 . $this->quoteInto('UPPER(c.tabname) = UPPER(?)', $tableName); |
|
400 |
|
401 if ($schemaName) { |
|
402 $sql .= $this->quoteInto(' AND UPPER(c.tabschema) = UPPER(?)', $schemaName); |
|
403 } |
|
404 |
|
405 $sql .= " ORDER BY c.colno"; |
|
406 |
|
407 } else { |
|
408 |
|
409 // DB2 On I5 specific query |
|
410 $sql = "SELECT DISTINCT C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME, C.ORDINAL_POSITION, |
|
411 C.DATA_TYPE, C.COLUMN_DEFAULT, C.NULLS ,C.LENGTH, C.SCALE, LEFT(C.IDENTITY,1), |
|
412 LEFT(tc.TYPE, 1) AS tabconsttype, k.COLSEQ |
|
413 FROM QSYS2.SYSCOLUMNS C |
|
414 LEFT JOIN (QSYS2.syskeycst k JOIN QSYS2.SYSCST tc |
|
415 ON (k.TABLE_SCHEMA = tc.TABLE_SCHEMA |
|
416 AND k.TABLE_NAME = tc.TABLE_NAME |
|
417 AND LEFT(tc.type,1) = 'P')) |
|
418 ON (C.TABLE_SCHEMA = k.TABLE_SCHEMA |
|
419 AND C.TABLE_NAME = k.TABLE_NAME |
|
420 AND C.COLUMN_NAME = k.COLUMN_NAME) |
|
421 WHERE " |
|
422 . $this->quoteInto('UPPER(C.TABLE_NAME) = UPPER(?)', $tableName); |
|
423 |
|
424 if ($schemaName) { |
|
425 $sql .= $this->quoteInto(' AND UPPER(C.TABLE_SCHEMA) = UPPER(?)', $schemaName); |
|
426 } |
|
427 |
|
428 $sql .= " ORDER BY C.ORDINAL_POSITION FOR FETCH ONLY"; |
|
429 } |
|
430 |
|
431 $desc = array(); |
|
432 $stmt = $this->query($sql); |
|
433 |
|
434 /** |
|
435 * To avoid case issues, fetch using FETCH_NUM |
|
436 */ |
|
437 $result = $stmt->fetchAll(Zend_Db::FETCH_NUM); |
|
438 |
|
439 /** |
|
440 * The ordering of columns is defined by the query so we can map |
|
441 * to variables to improve readability |
|
442 */ |
|
443 $tabschema = 0; |
|
444 $tabname = 1; |
|
445 $colname = 2; |
|
446 $colno = 3; |
|
447 $typename = 4; |
|
448 $default = 5; |
|
449 $nulls = 6; |
|
450 $length = 7; |
|
451 $scale = 8; |
|
452 $identityCol = 9; |
|
453 $tabconstType = 10; |
|
454 $colseq = 11; |
|
455 |
|
456 foreach ($result as $key => $row) { |
|
457 list ($primary, $primaryPosition, $identity) = array(false, null, false); |
|
458 if ($row[$tabconstType] == 'P') { |
|
459 $primary = true; |
|
460 $primaryPosition = $row[$colseq]; |
|
461 } |
|
462 /** |
|
463 * In IBM DB2, an column can be IDENTITY |
|
464 * even if it is not part of the PRIMARY KEY. |
|
465 */ |
|
466 if ($row[$identityCol] == 'Y') { |
|
467 $identity = true; |
|
468 } |
|
469 |
|
470 // only colname needs to be case adjusted |
|
471 $desc[$this->foldCase($row[$colname])] = array( |
|
472 'SCHEMA_NAME' => $this->foldCase($row[$tabschema]), |
|
473 'TABLE_NAME' => $this->foldCase($row[$tabname]), |
|
474 'COLUMN_NAME' => $this->foldCase($row[$colname]), |
|
475 'COLUMN_POSITION' => (!$this->_isI5) ? $row[$colno]+1 : $row[$colno], |
|
476 'DATA_TYPE' => $row[$typename], |
|
477 'DEFAULT' => $row[$default], |
|
478 'NULLABLE' => (bool) ($row[$nulls] == 'Y'), |
|
479 'LENGTH' => $row[$length], |
|
480 'SCALE' => $row[$scale], |
|
481 'PRECISION' => ($row[$typename] == 'DECIMAL' ? $row[$length] : 0), |
|
482 'UNSIGNED' => false, |
|
483 'PRIMARY' => $primary, |
|
484 'PRIMARY_POSITION' => $primaryPosition, |
|
485 'IDENTITY' => $identity |
|
486 ); |
|
487 } |
|
488 |
|
489 return $desc; |
|
490 } |
|
491 |
|
492 /** |
|
493 * Return the most recent value from the specified sequence in the database. |
|
494 * This is supported only on RDBMS brands that support sequences |
|
495 * (e.g. Oracle, PostgreSQL, DB2). Other RDBMS brands return null. |
|
496 * |
|
497 * @param string $sequenceName |
|
498 * @return string |
|
499 */ |
|
500 public function lastSequenceId($sequenceName) |
|
501 { |
|
502 $this->_connect(); |
|
503 |
|
504 if (!$this->_isI5) { |
|
505 $quotedSequenceName = $this->quoteIdentifier($sequenceName, true); |
|
506 $sql = 'SELECT PREVVAL FOR ' . $quotedSequenceName . ' AS VAL FROM SYSIBM.SYSDUMMY1'; |
|
507 } else { |
|
508 $quotedSequenceName = $sequenceName; |
|
509 $sql = 'SELECT PREVVAL FOR ' . $this->quoteIdentifier($sequenceName, true) . ' AS VAL FROM QSYS2.QSQPTABL'; |
|
510 } |
|
511 |
|
512 $value = $this->fetchOne($sql); |
|
513 return (string) $value; |
|
514 } |
|
515 |
|
516 /** |
|
517 * Generate a new value from the specified sequence in the database, and return it. |
|
518 * This is supported only on RDBMS brands that support sequences |
|
519 * (e.g. Oracle, PostgreSQL, DB2). Other RDBMS brands return null. |
|
520 * |
|
521 * @param string $sequenceName |
|
522 * @return string |
|
523 */ |
|
524 public function nextSequenceId($sequenceName) |
|
525 { |
|
526 $this->_connect(); |
|
527 $sql = 'SELECT NEXTVAL FOR '.$this->quoteIdentifier($sequenceName, true).' AS VAL FROM SYSIBM.SYSDUMMY1'; |
|
528 $value = $this->fetchOne($sql); |
|
529 return (string) $value; |
|
530 } |
|
531 |
|
532 /** |
|
533 * Gets the last ID generated automatically by an IDENTITY/AUTOINCREMENT column. |
|
534 * |
|
535 * As a convention, on RDBMS brands that support sequences |
|
536 * (e.g. Oracle, PostgreSQL, DB2), this method forms the name of a sequence |
|
537 * from the arguments and returns the last id generated by that sequence. |
|
538 * On RDBMS brands that support IDENTITY/AUTOINCREMENT columns, this method |
|
539 * returns the last value generated for such a column, and the table name |
|
540 * argument is disregarded. |
|
541 * |
|
542 * The IDENTITY_VAL_LOCAL() function gives the last generated identity value |
|
543 * in the current process, even if it was for a GENERATED column. |
|
544 * |
|
545 * @param string $tableName OPTIONAL |
|
546 * @param string $primaryKey OPTIONAL |
|
547 * @param string $idType OPTIONAL used for i5 platform to define sequence/idenity unique value |
|
548 * @return string |
|
549 */ |
|
550 |
|
551 public function lastInsertId($tableName = null, $primaryKey = null, $idType = null) |
|
552 { |
|
553 $this->_connect(); |
|
554 |
|
555 if ($this->_isI5) { |
|
556 return (string) $this->_i5LastInsertId($tableName, $idType); |
|
557 } |
|
558 |
|
559 if ($tableName !== null) { |
|
560 $sequenceName = $tableName; |
|
561 if ($primaryKey) { |
|
562 $sequenceName .= "_$primaryKey"; |
|
563 } |
|
564 $sequenceName .= '_seq'; |
|
565 return $this->lastSequenceId($sequenceName); |
|
566 } |
|
567 |
|
568 $sql = 'SELECT IDENTITY_VAL_LOCAL() AS VAL FROM SYSIBM.SYSDUMMY1'; |
|
569 $value = $this->fetchOne($sql); |
|
570 return (string) $value; |
|
571 } |
|
572 |
|
573 /** |
|
574 * Begin a transaction. |
|
575 * |
|
576 * @return void |
|
577 */ |
|
578 protected function _beginTransaction() |
|
579 { |
|
580 $this->_setExecuteMode(DB2_AUTOCOMMIT_OFF); |
|
581 } |
|
582 |
|
583 /** |
|
584 * Commit a transaction. |
|
585 * |
|
586 * @return void |
|
587 */ |
|
588 protected function _commit() |
|
589 { |
|
590 if (!db2_commit($this->_connection)) { |
|
591 /** |
|
592 * @see Zend_Db_Adapter_Db2_Exception |
|
593 */ |
|
594 require_once 'Zend/Db/Adapter/Db2/Exception.php'; |
|
595 throw new Zend_Db_Adapter_Db2_Exception( |
|
596 db2_conn_errormsg($this->_connection), |
|
597 db2_conn_error($this->_connection)); |
|
598 } |
|
599 |
|
600 $this->_setExecuteMode(DB2_AUTOCOMMIT_ON); |
|
601 } |
|
602 |
|
603 /** |
|
604 * Rollback a transaction. |
|
605 * |
|
606 * @return void |
|
607 */ |
|
608 protected function _rollBack() |
|
609 { |
|
610 if (!db2_rollback($this->_connection)) { |
|
611 /** |
|
612 * @see Zend_Db_Adapter_Db2_Exception |
|
613 */ |
|
614 require_once 'Zend/Db/Adapter/Db2/Exception.php'; |
|
615 throw new Zend_Db_Adapter_Db2_Exception( |
|
616 db2_conn_errormsg($this->_connection), |
|
617 db2_conn_error($this->_connection)); |
|
618 } |
|
619 $this->_setExecuteMode(DB2_AUTOCOMMIT_ON); |
|
620 } |
|
621 |
|
622 /** |
|
623 * Set the fetch mode. |
|
624 * |
|
625 * @param integer $mode |
|
626 * @return void |
|
627 * @throws Zend_Db_Adapter_Db2_Exception |
|
628 */ |
|
629 public function setFetchMode($mode) |
|
630 { |
|
631 switch ($mode) { |
|
632 case Zend_Db::FETCH_NUM: // seq array |
|
633 case Zend_Db::FETCH_ASSOC: // assoc array |
|
634 case Zend_Db::FETCH_BOTH: // seq+assoc array |
|
635 case Zend_Db::FETCH_OBJ: // object |
|
636 $this->_fetchMode = $mode; |
|
637 break; |
|
638 case Zend_Db::FETCH_BOUND: // bound to PHP variable |
|
639 /** |
|
640 * @see Zend_Db_Adapter_Db2_Exception |
|
641 */ |
|
642 require_once 'Zend/Db/Adapter/Db2/Exception.php'; |
|
643 throw new Zend_Db_Adapter_Db2_Exception('FETCH_BOUND is not supported yet'); |
|
644 break; |
|
645 default: |
|
646 /** |
|
647 * @see Zend_Db_Adapter_Db2_Exception |
|
648 */ |
|
649 require_once 'Zend/Db/Adapter/Db2/Exception.php'; |
|
650 throw new Zend_Db_Adapter_Db2_Exception("Invalid fetch mode '$mode' specified"); |
|
651 break; |
|
652 } |
|
653 } |
|
654 |
|
655 /** |
|
656 * Adds an adapter-specific LIMIT clause to the SELECT statement. |
|
657 * |
|
658 * @param string $sql |
|
659 * @param integer $count |
|
660 * @param integer $offset OPTIONAL |
|
661 * @return string |
|
662 */ |
|
663 public function limit($sql, $count, $offset = 0) |
|
664 { |
|
665 $count = intval($count); |
|
666 if ($count <= 0) { |
|
667 /** |
|
668 * @see Zend_Db_Adapter_Db2_Exception |
|
669 */ |
|
670 require_once 'Zend/Db/Adapter/Db2/Exception.php'; |
|
671 throw new Zend_Db_Adapter_Db2_Exception("LIMIT argument count=$count is not valid"); |
|
672 } |
|
673 |
|
674 $offset = intval($offset); |
|
675 if ($offset < 0) { |
|
676 /** |
|
677 * @see Zend_Db_Adapter_Db2_Exception |
|
678 */ |
|
679 require_once 'Zend/Db/Adapter/Db2/Exception.php'; |
|
680 throw new Zend_Db_Adapter_Db2_Exception("LIMIT argument offset=$offset is not valid"); |
|
681 } |
|
682 |
|
683 if ($offset == 0) { |
|
684 $limit_sql = $sql . " FETCH FIRST $count ROWS ONLY"; |
|
685 return $limit_sql; |
|
686 } |
|
687 |
|
688 /** |
|
689 * DB2 does not implement the LIMIT clause as some RDBMS do. |
|
690 * We have to simulate it with subqueries and ROWNUM. |
|
691 * Unfortunately because we use the column wildcard "*", |
|
692 * this puts an extra column into the query result set. |
|
693 */ |
|
694 $limit_sql = "SELECT z2.* |
|
695 FROM ( |
|
696 SELECT ROW_NUMBER() OVER() AS \"ZEND_DB_ROWNUM\", z1.* |
|
697 FROM ( |
|
698 " . $sql . " |
|
699 ) z1 |
|
700 ) z2 |
|
701 WHERE z2.zend_db_rownum BETWEEN " . ($offset+1) . " AND " . ($offset+$count); |
|
702 return $limit_sql; |
|
703 } |
|
704 |
|
705 /** |
|
706 * Check if the adapter supports real SQL parameters. |
|
707 * |
|
708 * @param string $type 'positional' or 'named' |
|
709 * @return bool |
|
710 */ |
|
711 public function supportsParameters($type) |
|
712 { |
|
713 if ($type == 'positional') { |
|
714 return true; |
|
715 } |
|
716 |
|
717 // if its 'named' or anything else |
|
718 return false; |
|
719 } |
|
720 |
|
721 /** |
|
722 * Retrieve server version in PHP style |
|
723 * |
|
724 * @return string |
|
725 */ |
|
726 public function getServerVersion() |
|
727 { |
|
728 $this->_connect(); |
|
729 $server_info = db2_server_info($this->_connection); |
|
730 if ($server_info !== false) { |
|
731 $version = $server_info->DBMS_VER; |
|
732 if ($this->_isI5) { |
|
733 $version = (int) substr($version, 0, 2) . '.' . (int) substr($version, 2, 2) . '.' . (int) substr($version, 4); |
|
734 } |
|
735 return $version; |
|
736 } else { |
|
737 return null; |
|
738 } |
|
739 } |
|
740 |
|
741 /** |
|
742 * Return whether or not this is running on i5 |
|
743 * |
|
744 * @return bool |
|
745 */ |
|
746 public function isI5() |
|
747 { |
|
748 if ($this->_isI5 === null) { |
|
749 $this->_determineI5(); |
|
750 } |
|
751 |
|
752 return (bool) $this->_isI5; |
|
753 } |
|
754 |
|
755 /** |
|
756 * Check the connection parameters according to verify |
|
757 * type of used OS |
|
758 * |
|
759 * @return void |
|
760 */ |
|
761 protected function _determineI5() |
|
762 { |
|
763 // first us the compiled flag. |
|
764 $this->_isI5 = (php_uname('s') == 'OS400') ? true : false; |
|
765 |
|
766 // if this is set, then us it |
|
767 if (isset($this->_config['os'])){ |
|
768 if (strtolower($this->_config['os']) === 'i5') { |
|
769 $this->_isI5 = true; |
|
770 } else { |
|
771 // any other value passed in, its null |
|
772 $this->_isI5 = false; |
|
773 } |
|
774 } |
|
775 |
|
776 } |
|
777 |
|
778 /** |
|
779 * Db2 On I5 specific method |
|
780 * |
|
781 * Returns a list of the tables in the database . |
|
782 * Used only for DB2/400. |
|
783 * |
|
784 * @return array |
|
785 */ |
|
786 protected function _i5listTables($schema = null) |
|
787 { |
|
788 //list of i5 libraries. |
|
789 $tables = array(); |
|
790 if ($schema) { |
|
791 $tablesStatement = db2_tables($this->_connection, null, $schema); |
|
792 while ($rowTables = db2_fetch_assoc($tablesStatement) ) { |
|
793 if ($rowTables['TABLE_NAME'] !== null) { |
|
794 $tables[] = $rowTables['TABLE_NAME']; |
|
795 } |
|
796 } |
|
797 } else { |
|
798 $schemaStatement = db2_tables($this->_connection); |
|
799 while ($schema = db2_fetch_assoc($schemaStatement)) { |
|
800 if ($schema['TABLE_SCHEM'] !== null) { |
|
801 // list of the tables which belongs to the selected library |
|
802 $tablesStatement = db2_tables($this->_connection, NULL, $schema['TABLE_SCHEM']); |
|
803 if (is_resource($tablesStatement)) { |
|
804 while ($rowTables = db2_fetch_assoc($tablesStatement) ) { |
|
805 if ($rowTables['TABLE_NAME'] !== null) { |
|
806 $tables[] = $rowTables['TABLE_NAME']; |
|
807 } |
|
808 } |
|
809 } |
|
810 } |
|
811 } |
|
812 } |
|
813 |
|
814 return $tables; |
|
815 } |
|
816 |
|
817 protected function _i5LastInsertId($objectName = null, $idType = null) |
|
818 { |
|
819 |
|
820 if ($objectName === null) { |
|
821 $sql = 'SELECT IDENTITY_VAL_LOCAL() AS VAL FROM QSYS2.QSQPTABL'; |
|
822 $value = $this->fetchOne($sql); |
|
823 return $value; |
|
824 } |
|
825 |
|
826 if (strtoupper($idType) === 'S'){ |
|
827 //check i5_lib option |
|
828 $sequenceName = $objectName; |
|
829 return $this->lastSequenceId($sequenceName); |
|
830 } |
|
831 |
|
832 //returns last identity value for the specified table |
|
833 //if (strtoupper($idType) === 'I') { |
|
834 $tableName = $objectName; |
|
835 return $this->fetchOne('SELECT IDENTITY_VAL_LOCAL() from ' . $this->quoteIdentifier($tableName)); |
|
836 } |
|
837 |
|
838 } |
|
839 |
|
840 |