/** * DB2 catalog lookup for describe table * * @param string $tableName * @param string $schemaName OPTIONAL * @return array */ public function describeTable($tableName, $schemaName = null) { $sql = "SELECT DISTINCT c.tabschema, c.tabname, c.colname, c.colno,\n c.typename, c.default, c.nulls, c.length, c.scale,\n c.identity, tc.type AS tabconsttype, k.colseq\n FROM syscat.columns c\n LEFT JOIN (syscat.keycoluse k JOIN syscat.tabconst tc\n ON (k.tabschema = tc.tabschema\n AND k.tabname = tc.tabname\n AND tc.type = 'P'))\n ON (c.tabschema = k.tabschema\n AND c.tabname = k.tabname\n AND c.colname = k.colname)\n WHERE " . $this->_adapter->quoteInto('UPPER(c.tabname) = UPPER(?)', $tableName); if ($schemaName) { $sql .= $this->_adapter->quoteInto(' AND UPPER(c.tabschema) = UPPER(?)', $schemaName); } $sql .= " ORDER BY c.colno"; $desc = array(); $stmt = $this->_adapter->query($sql); /** * To avoid case issues, fetch using FETCH_NUM */ $result = $stmt->fetchAll(JO_Db::FETCH_NUM); /** * The ordering of columns is defined by the query so we can map * to variables to improve readability */ $tabschema = 0; $tabname = 1; $colname = 2; $colno = 3; $typename = 4; $default = 5; $nulls = 6; $length = 7; $scale = 8; $identityCol = 9; $tabconstype = 10; $colseq = 11; foreach ($result as $key => $row) { list($primary, $primaryPosition, $identity) = array(false, null, false); if ($row[$tabconstype] == 'P') { $primary = true; $primaryPosition = $row[$colseq]; } /** * In IBM DB2, an column can be IDENTITY * even if it is not part of the PRIMARY KEY. */ if ($row[$identityCol] == 'Y') { $identity = true; } $desc[$this->_adapter->foldCase($row[$colname])] = array('SCHEMA_NAME' => $this->_adapter->foldCase($row[$tabschema]), 'TABLE_NAME' => $this->_adapter->foldCase($row[$tabname]), 'COLUMN_NAME' => $this->_adapter->foldCase($row[$colname]), 'COLUMN_POSITION' => $row[$colno] + 1, 'DATA_TYPE' => $row[$typename], 'DEFAULT' => $row[$default], 'NULLABLE' => (bool) ($row[$nulls] == 'Y'), 'LENGTH' => $row[$length], 'SCALE' => $row[$scale], 'PRECISION' => $row[$typename] == 'DECIMAL' ? $row[$length] : 0, 'UNSIGNED' => false, 'PRIMARY' => $primary, 'PRIMARY_POSITION' => $primaryPosition, 'IDENTITY' => $identity); } return $desc; }
/** * Helper method to retrieve primary key column * and column location * * @param int $tabid * @return array */ protected function _getPrimaryInfo($tabid) { $sql = "SELECT i.part1, i.part2, i.part3, i.part4, i.part5, i.part6,\n i.part7, i.part8, i.part9, i.part10, i.part11, i.part12,\n i.part13, i.part14, i.part15, i.part16\n FROM sysindexes i\n JOIN sysconstraints c ON c.idxname = i.idxname\n WHERE i.tabid = " . $tabid . " AND c.constrtype = 'P'"; $stmt = $this->_adapter->query($sql); $results = $stmt->fetchAll(); $cols = array(); // this should return only 1 row // unless there is no primary key, // in which case, the empty array is returned if ($results) { $row = $results[0]; } else { return $cols; } $position = 0; foreach ($row as $key => $colno) { $position++; if ($colno == 0) { return $cols; } else { $cols[$colno] = $position; } } }
/** * Support method for fetching rows. * * @param JO_Db_Table_Select $select query options. * @return array An array containing the row results in FETCH_ASSOC mode. */ protected function _fetch(JO_Db_Table_Select $select) { $stmt = $this->_db->query($select); $data = $stmt->fetchAll(JO_Db::FETCH_ASSOC); return $data; }
/** * Special handling for PDO query(). * All bind parameter names must begin with ':' * * @param string|JO_Db_Select $sql The SQL statement with placeholders. * @param array $bind An array of data to bind to the placeholders. * @return JO_Db_Statement_Pdo * @throws JO_Db_Adapter_Exception To re-throw PDOException. */ public function query($sql, $bind = array()) { if (empty($bind) && $sql instanceof JO_Db_Select) { $bind = $sql->getBind(); } if (is_array($bind)) { foreach ($bind as $name => $value) { if (!is_int($name) && !preg_match('/^:/', $name)) { $newName = ":{$name}"; unset($bind[$name]); $bind[$newName] = $value; } } } try { return parent::query($sql, $bind); } catch (PDOException $e) { /** * @see JO_Db_Statement_Exception */ require_once 'JO/Db/Statement/Exception.php'; throw new JO_Db_Statement_Exception($e->getMessage(), $e->getCode(), $e); } }
/** * Executes the current select object and returns the result * * @param integer $fetchMode OPTIONAL * @param mixed $bind An array of data to bind to the placeholders. * @return PDO_Statement|JO_Db_Statement */ public function query($fetchMode = null, $bind = array()) { if (!empty($bind)) { $this->bind($bind); } $stmt = $this->_adapter->query($this); if ($fetchMode == null) { $fetchMode = $this->_adapter->getFetchMode(); } $stmt->setFetchMode($fetchMode); return $stmt; }