Zend Framework Incubator

This would be a very useful, I would almost say necessary, feature for anyone using Oracle in any kind of enterprise environment. I'm hoping to inter-operate with an ERP system which has pushed all of its business logic down into PL/SQL procedures/functions/packages, most of which return a cursor.

In an attempt to not have to scrap all of the Zend Framework code already in place (dealing with the previous non-PL/SQL ERP) I'm looking to extend Zend_Db in the manner described here (Zend_Db_Cursor_Oracle).

Thanks for replying Brian.

Do you have any class definition? If you have some I would like to take a look. I will organize and revise my class and publish here.

Sorry Bruno, I have no code to share at this moment. We are at the very beginning of this project and I have just come to the realization that what needs to be done can not be done with Zend_Db in its current state.

Any code you have to share would be great, maybe we could collaborate and see about getting this included in Zend Framework.

I've got a solution currently working, and I'm working on a more elegant solution (based off your ideas in the Description), but I honestly don't know which would be the preferred method of doing things.

My currently working solution was to just patch Zend_Db_Statement_Oracle. This seemed "right" because Oracle is the only RDBMS that uses REF CURSORS in this manner (to my limited knowledge). I added a new protected variable:

/** 
 * Cursor pointer.
 */
protected $_cursor;

Then I modified _bindParam() to create and bind a cursor when one is passed in:

if ($type === OCI_B_CURSOR) {
    $variable = oci_new_cursor($this->_adapter->getConnection());
    $this->_cursor = $variable;
}

Then I modified _execute() to look for and execute on $this->_cursor, in addition to $this->_stmt if a cursor was passed to _bindParam():

if (isset($this->_cursor)) {
    @oci_execute($this->_cursor, $this->_adapter->_getExecuteMode());
}

At this point I just added a new method, fetchCursor() to see if what I had done was working, rather than modify fetch(), fetchAll(), fetchColumn() and fetchObject() to check for an active cursor pointer. This function is basically a mirror of fetch() with all occurences of $this->_stmt replaced with $this->_cursor.

At the same time, this solution doesn't feel right because Zend_Db has sought to mirror the PDOStatement API and adding the fetchCursor() function effectively breaks that.

Thanks for replying Brian.

Although your approach seens to work well and its simpler than one in my proposal. I believe that using an abstract class is more elegant and expansible than to inject cursors logic directly in statements components. By design, the ref cursors are in memory result sets and do not behaves like a statement, it behaves like a result set (in a Zend_Db_Table_Rowset flavor).

Keeping it under its package decrease the coupling and increases the cohesion of the component, allowing to allocate ne DBMS ref cursors at a glance.

Did you read the Zend_Db_Cursor proposal? If not a ask you to do so and give some reply, as you already got some experience with that.

Bruno

Very nice Bruno. I will quote your last comment and continue this discussion over at the newly added Proposal as it seems the more appropriate place to now continue it.

This patch resolve. I'm create modifications for cursor adding a stmt object for you. Sorry for my english...

How to for this patch:

/**
 * Retorna o vínculo pelo CPF caso exista.
 *
 * @param string $cpf
 * @return Zend_Db_Statement
 */
public function getVinculoByCPF($cpf)
{
        $sql = "CALL CONSULTA_VINCULO_CPF(:cpf, :rs)";
        $stmt = $this->getDefaultAdapter()->prepare($sql);
        $stmt->bindParam(':cpf', $cpf);
        $stmt->bindParam(':rs', $rs, Zend_Db::PARAM_STMT);
        $stmt->execute();
        return $rs;
	}

$stmtVinculo = $objJuntaComercialModel->getVinculoByCPF($cpf);
$this->view->arrObjVinculo = $stmtVinculo->fetchAll();

The oracle cursor is a "oci8 statement" of return with oci_execute, see http://br.php.net/manual/en/resource.php

Thanks for the reply Nunes. For better organization, let's discuss this component in the following link http://framework.zend.com/wiki/display/ZFPROP/Zend_Db_Cursor+-+Bruno+Piraja+Moyle.

My best