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 23484 2010-12-10 03:57:59Z mjh_ca $
 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: 
841: