Beispiel #1
0
 /**
  * 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;
 }
Beispiel #2
0
 /**
  * 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;
         }
     }
 }
Beispiel #3
0
 /**
  * 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;
 }
Beispiel #4
0
 /**
  * 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);
     }
 }
Beispiel #5
0
 /**
  * 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;
 }