Issues

ZF-5761: How to prevent select sequece.next_val from dual ?

Description

Hi,

Still on optimizations with Oracle, we have seen as many "select sequece.next_val from dual" as "insert into xxx". Isn't there a workarround so that we could use the 'returning' SQL keyword?

Here is an extract of the dedicated insert we had in our homemade framework:


protected function _insert() {

    if (!$this->dbIsParsed(get_class($this) . '::' . __FUNCTION__ . $this->_setPk)) {

        $cols = $this->_getColsToInsert();
        $colsInsert = $cols;
        $sqlCplSeq = "";
        $sqlCplRet = "";

        if ($this->_sqlSequence != '' && !$this->_setPk) {
            //--- La pk sera retournée via la séquence
            unset($colsInsert[$this->_sqlPkIndex]);
            $sqlCplSeq = "{$this->_sqlSequence}.nextval, ";
            $sqlCplRet = "returning {$this->_sqlPk} into :{$this->_sqlPk}";
        }

        $colsInsert = array_map(array($this, '_formatInsertKey'), $colsInsert);

        $sql = "insert into {$this->_sqlTable}
                cols (" . implode(',', $cols) . ")
                values ($sqlCplSeq" . implode(',', $colsInsert) . ")
                $sqlCplRet";

       $bl = $this->dbParse($sql);

        if (!$bl) {
            throw new DboException("Objet non inséré (parse HS)");
        }

        foreach ($cols as $col) {
            switch ($this->_cols[$col]) {
                 case self::SYSDATE :
                     continue 2;
                     break;
            }
            $this->dbBind($col, $this->_getColLength($col));
        }
    }

    $this->dbMultiSet($this->_infos, false);

    $bl = $this->dbExecute(OCI_DEFAULT);
    if (!$bl) {
        throw new DboException("Objet non inséré");
    }
    $this->offsetSet($this->_sqlPk, $this->dbGetCol($this->_sqlPk));
    $this->_setPk = false;
}

Thx again, Wilfried Loche

Comments

Bulk change of all issues last updated before 1st January 2010 as "Won't Fix".

Feel free to re-open and provide a patch if you want to fix this issue.