Issues

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

Issue Type: Improvement Created: 2009-02-12T08:22:27.000+0000 Last Updated: 2012-11-20T20:53:28.000+0000 Status: Closed Fix version(s): Reporter: Wilfried Loche (w_loche) Assignee: None Tags: - Zend_Db_Table

Related issues: - ZF-5758

Attachments:

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:

<pre class="highlight">
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

Posted by Rob Allen (rob) on 2012-11-20T20:53:28.000+0000

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.

Have you found an issue?

See the Overview section for more details.

Copyright

© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.

Contacts