Skip to end of metadata
Go to start of metadata

<ac:macro ac:name="info"><ac:parameter ac:name="title">New Proposal Template</ac:parameter><ac:rich-text-body>
<p>This page has been created from a template that uses "zones." To proceed:</p>

<ol>
<li>Edit the page</li>
<li>Replace sample content within each zone-data tag with your own content</li>
<li>Remove this notice</li>
<li>Save the page</li>
<li>When you are ready for community review, move this page to the <ac:link><ri:page ri:content-title="Ready for Review" /></ac:link> section on the edit page.</li>
</ol>

<ac:macro ac:name="note"><ac:parameter ac:name="title">No placeholders allowed!</ac:parameter><ac:rich-text-body>
<p>Please do not create placeholders. Wait until you have sufficient content to replace all sample data in the proposal template before creating your proposal document.</p></ac:rich-text-body></ac:macro></ac:rich-text-body></ac:macro>

<ac:macro ac:name="unmigrated-inline-wiki-markup"><ac:plain-text-body><![CDATA[

<ac:macro ac:name="unmigrated-inline-wiki-markup"><ac:plain-text-body><![CDATA[

Zend Framework: Zend_Db_Cursor Component Proposal

Proposed Component Name Zend_Db_Cursor
Developer Notes http://framework.zend.com/wiki/display/ZFDEV/Zend_Db_Cursor
Proposers Bruno Piraja Moyle
Zend Liaison TBD
Revision 1.0 - 24 june 2009: Initial Draft. (wiki revision: 17)

Table of Contents

1. Overview

Zend_Db_Cursor goal is to allow using database ref cursors in a row set flavor. Most of Oracle stored procedures makes use of ref cursors and usually returns it as an 'in out' parameter. oci8 extension allow the creation and access of these cursors.

2. References

3. Component Requirements, Constraints, and Acceptance Criteria

These components depends that PHP specific database extension provides interfaces to create these kind of cursors, as oci8 does.

4. Dependencies on Other Framework Components

  • Zend_Exception
  • Zend_Db_Adapter
  • Zend_Db_Statement

5. Theory of Operation

The component should be used as a wrapper for cursors, by instantiating an object using the new operator:

In the 'index' view script

To test the component, you can use the following stored procedure - in Oracle(r) database:

This stored procedure receives an 'in out' ref cursor as a parameter and open it. By opening a ref cursor, it is populated and is ready to fetch.

6. Milestones / Tasks

  • Milestone 1: [Done] Component development;
  • Milestone 2: Tests;
  • Milestone 3: Documentation;
  • Milestone 4: Approval.

7. Class Index

  • Zend_Db_Cursor_CursorAbstract
  • Zend_Db_Cursor_Oracle
  • Zend_Db_Cursor_Row
  • Zend_Db_Cursor_Exception
  • Zend_Db_Statement (this class needs to be updated in order to be aware of ref cursors binding)

8. Use Cases

UC-01

9. Class Skeletons

]]></ac:plain-text-body></ac:macro>

]]></ac:plain-text-body></ac:macro>

Labels:
None
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. Sep 18, 2009

    <p>By design, a cursor is a in memory result set. As a result set, it contains many records - or rows - and each row contains columns with values.</p>

    <p>I think that is a good idea to define a class to abstract these rows - Zend_Db_Cursor_Row - as well, or make cursors behave as Zend_Db_Table_Rowset - as they share common behavior.</p>

    <p>Any opinions?</p>

  2. Sep 18, 2009

    <p>Added the class skeleton. This code is not tested yet.</p>

  3. Sep 21, 2009

    <blockquote><p>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).</p>

    <p>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.</p></blockquote>

    <p>I am in 100% agreement that this is the proper approach to take. My first pass was really just to flesh out the ideas, and get some workable code to test. I would like to also modify Zend_Db_Statment_Oracle to be "cursor aware" so that in addition to your use case (Theory of Operation) above, you could do the following:</p>

    <ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
    // Creates a statement and cursor objects
    $statement = new Zend_Db_Statement_Oracle($conn, 'begin get_clients(:cursor); end;');
    $statement->bindParam('cursor', $refCursor, OCI_B_CURSOR); // or new Zend_DB::REF_CURSOR

    // Executes both statement and cursor
    $statement->execute();

    $results = $refCursor->fetchAll();
    ]]></ac:plain-text-body></ac:macro>

    <p>As you said, by design a cursor is an in memory result set. It's that point which makes me feel that you shouldn't be calling execute() on a cursor, that should be done on the Statement. If we make the Statement "cursor aware" it becomes much more fluid for the program to do the above, while still allowing the use case you have proposed for those that prefer modeling the PHP/OCI8 coding style.</p>

    <p>What I'm currently working on looks similar to what you have proposed, though slightly different. Let me see if I can make this look right...</p>

    <ac:macro ac:name="noformat"><ac:plain-text-body><![CDATA[
    Zend/
    Db/
    Cursor/
    Oracle/
    Exception.php
    Exception.php
    Interface.php
    Oracle.php
    Cursor.php
    ]]></ac:plain-text-body></ac:macro>

    <p>Where Zend_Db_Cursor is an abstract class that implements Zend_Db_Cursor_Interface and Zend_Db_Oracle_Cursor extends Zend_Db_Cursor. I have skeleton code to complete all the above classes, but haven't gotten around to testing them yet. I'm hoping to have time today (after meetings) to start working on that.</p>

    1. Sep 22, 2009

      <p>I agree with you that executing a cursor looks odd. My initial intention was to not create dependencies between it and the Zend_Db_* components, but after some tests, it looks like that the dependency with Zend_Db_Statement will be inevitable.</p>

      <p>My files structure is like this:</p>
      <ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
      Zend/
      Db/
      Cursor/
      Abstract.php
      Oracle.php
      Row.php
      ]]></ac:plain-text-body></ac:macro>

      <p>Some considerations:</p>
      <ul>
      <li>Maybe we can go without the interface;</li>
      <li>The Zend_Db_Cursor_Row will abstract a cursor row, as the Table Row Gateway pattern;</li>
      <li>Instead of using Zend_Db_Cursor as abstract, I defined Zend_Db_Cursor_Abstract.</li>
      </ul>

      <p>I found a annoying problem: The oci extension only knows how to work with cursors created with the oci_new_cursor function. The component will have to integrate with Zend_Db_Statement to turn on the cursor 'awareness'.</p>

      <p>Looking at class code, there is a $_cursor property that holds a oci 'flavor' cursor. Maybe its necessary to create some methods to access it (getRawCursor() and setRawCursor($cursor)).</p>

      <p>I am updating the theory of operation.</p>

  4. Dec 01, 2009

    <p>Re-assuming the proposal after a long break.</p>

  5. Jan 11, 2010

    <p>Just finished a new component draft, updating within a week.</p>

  6. Jan 29, 2010

    <p>I tried the alpha component and it seems that Statement is not well created, i get this error : <br />
    Warning: oci_bind_by_name(): supplied argument is not a valid oci8 statement resource</p>

  7. Jan 29, 2010

    <p>Just updated this proposal. Uploaded the working class skeletons - see the Theory of Operation section.</p>

    <p>The alpha version allow to bind a ref cursor in an Oracle statement and to fetch data from it as a rowset.</p>

    <p>Next improvement is to allow object flavor access to the Zend_Db_Cursor_Row objects.</p>

    <p>If you can, test it and give some feedback.</p>

    <p>Thanks <ac:emoticon ac:name="smile" /></p>

  8. Jan 29, 2010

    <p>Zend_Db_Row can be accessed as an array ($row<ac:link><ri:page ri:content-title="'column'" /></ac:link>) and as an object ($row->column)</p>

  9. Feb 11, 2010

    <p>Just pushed the package draft in github, the link is <a href="http://github.com/brunitto/zend-db-cursor.git">http://github.com/brunitto/zend-db-cursor.git</a></p>

  10. May 13, 2010

    <p>Hello, good work. But in my opinion I believe there should be simpler, as previously stated by Brian, because the cursor in Oracle is a statement and this should be so treated for compatibility as other classes of stock are. If you create an abstract class for cursor depending on the interpretation can be confusing because its definition is sometimes a little differently in each bank, as being fit for a bank but not as a cursor.</p>

    <p>How do I put this link: <a class="external-link" href="http://framework.zend.com/issues/browse/ZFINC-13">http://framework.zend.com/issues/browse/ZFINC-13</a> would leave here the implementation I made that solves this problem as yet have no knowledge of how to create a proposal I have just the patch same link above.</p>

    <p>Following example of using already testing in Oracle 9i Release 2 (9.2):</p>

    <p>SampleModel.php</p>

    <ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
    /**

    • 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;
      }
      ]]></ac:plain-text-body></ac:macro>

    <p>SampleController.php</p>

    <ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
    $stmtVinculo = $objJuntaComercialModel->getVinculoByCPF($cpf);
    $this->view->arrObjVinculo = $stmtVinculo->fetchAll();
    ]]></ac:plain-text-body></ac:macro>

  11. May 13, 2010

    <p>Patch:</p>

    <ac:macro ac:name="code"><ac:plain-text-body><![CDATA[

        1. Eclipse Workspace Patch 1.0
          #P Zend Framework
          Index: library/Zend/Db/Statement/Oracle.php
          ===================================================================
        • library/Zend/Db/Statement/Oracle.php (revision 22149)
          +++ library/Zend/Db/Statement/Oracle.php (working copy)
          @@ -115,6 +115,19 @@
          $type = SQLT_CHR;
          }

    + if($type == Zend_Db::PARAM_STMT){
    + $type = OCI_B_CURSOR;
    + $variable = @oci_new_cursor($this->_adapter->getConnection());
    + if (empty($variable))

    Unknown macro: {+ /**+ * @see Zend_Db_Adapter_Oracle_Exception+ */+ require_once 'Zend/Db/Statement/Oracle/Exception.php';+ $error = array("code" => "", "message" => "Error to create oracle cursor");+ throw new Zend_Db_Statement_Oracle_Exception(oci_error($variable));+ }

    + }
    +
    // default value
    if ($length === NULL)

    Unknown macro: { $length = -1;@@ -276,6 +289,23 @@ $this->_values = array_fill(0, count($this->_keys), null); }

    + foreach($this->_bindParam as &$value){
    + if(is_resource($value)
    + && get_resource_type($value) == "oci8 statement"){
    + $retvalCursor = @oci_execute($value, $this->_adapter->_getExecuteMode());
    + if ($retvalCursor === false)

    Unknown macro: {+ /**+ * @see Zend_Db_Adapter_Oracle_Exception+ */+ require_once 'Zend/Db/Statement/Oracle/Exception.php';+ throw new Zend_Db_Statement_Oracle_Exception(oci_error($value));+ }

    + $stmt = $value;
    + $value = clone $this;
    + $value->_stmt = $stmt;
    + }
    + }
    +
    return $retval;
    }

    ]]></ac:plain-text-body></ac:macro>

  12. Feb 05, 2011

    <p>Archiving this proposal, feel free to recover it when you want to work on it again. For more details see <a href="http://framework.zend.com/wiki/display/ZFDEV/Archiving+of+abandoned+proposals+(Feb+5+2011)">this email</a>.</p>