/**
* 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;
}