Wednesday 20 January 2010

CakePHP: Calling Oracle stored procedures and functions

Our testing with Cake is almost complete. We have been able to read and write data correctly in our non UTF-8 Oracle database, managed to communicate with SAP, played enough with AJAX, so our users will not complain when dealing with tree like data and even managed to get authenticated by our MS-Windows 2003 active directory servers. So the last question left was how does one call an Oracle PL/SQL stored procedure or function from CakePHP ?

A little bit of googling and a little bit of digging into the CakePHP code revealed the following: The most common approach to calling stored procedures and functions is to create a member function in your model class and set up the call from there. In the simple case of calling a stored procedure with IN parameters only, the Model's query() method can be used to perform the actual call via the CALL PROC_NAME( ... ) SQL statement. The usual approach is to create a model method like this :

class MyModel extends AppModel {
    var $name = "MyModel";

    ...
    function callStoredProc( $param1, $param2)
    {
        $this->query("CALL my_stored_proc( $param1, $param2");
    }
    ...
}

If however you need to get data in and out of Oracle then you have to get your hands dirty and set up the call using low level oci_* functions. A simple example will clarify everything.

Let us suppose that you library users require that you display the average number of pages of the books stored in your library. A simple PL/SQL function to return this would probably be something like this: /p>

CREATE OR REPLACE FUNCTION average_book_pages RETURN NUMBER
IS
   page_avg NUMBER;   
BEGIN
   SELECT avg( num_pages) 
      INTO page_avg
      FROM books;
   RETURN page_avg;   
END AVERAGE_BOOK_PAGES;

The next thing to do would be to create a getAvergaeBookPages() function in our Book model class :

<?php
class Book extends AppModel {
    var $name = 'Book';
    var $belongsTo = ...
    var $validate = array( ...

    function getAverageBookPages()
    {
        // every model has a datasource
        $dataSource = $this->getDataSource();
        // and every datasource has a  database connection handle
        $connection = $dataSource->connection;
        if (!$connection)
            return -1;

        // from now you need this Oracle specific approach 
        $statement = oci_parse( $connection,
                "begin :ret := AVERAGE_BOOK_PAGES(); end;");
        oci_bind_by_name($statement, ':ret', $r, 100);
        oci_execute($statement);
        return $r;
    }
}
?>

The last parameter of oci_bind_by_name is the number of bytes to allocate for returning the string representation of the bind variable. Just make sure that you allocate enough memory for that. My test data yield an average of 762,666666666666666666666666666666666667 pages per book (Thank you Mr. Minasi) and so oci_execute kept giving me ORA-06502: PL/SQL: numeric or value error: character string buffer too small until I raised the value to 100.

So that does it. Now calling this from you controller code is as easy as :

<?php
class BooksController extends AppController {
    var $name = 'Books';

    ...

    function index()
    {
        $this->Book->recursive = 0;
        $this->set('books', $this->paginate());
        $this->set('averagePages', $this->Book->getAverageBookPages());
    }

    ...
}

Thursday 7 January 2010

CakePHP: The dependent listboxes problem

Note: This example is outdated. I have a newer blog post that addresses the same issue using more up to date methods and functionality.

When creating dynamic web-sites, sooner or later you are going to face the problem of providing dependent list boxes. Imagine the case where one wishes to select a city from a list grouped by region, or a user from a group, an invoice from an order etc. Grouping things into categories is very common in real life and as far as I am concerned, it is almost always a must for your application to be able to utilize such groupings.
In CakePHP -- as far as I know -- there are two ways you can help your users pick up a value from a list of grouped items. One is to create a select box whose items are organized in selection groups sorted in some logical way. the other way in to use dependent AJAX triggered combo or list boxes where selection on the first will filter the items displayed on the second.
In this posting I will provide code that handles both cases.

The sample data

As an exercise for learning Cake, I developed a small application that manages the IT department books. Each Book belongs to a BookCategory and each BookCategory belongs to a BookCategoryGroup. Reversely, each BookCategoryGroup has many BookCategory and each BookCategory has many Book. The corresponding tables have foreign keys adhering to the CakePHP conventions, so I am not going to waste any more time explaining the data structure.
The goal here is to help our users, when adding or editing book records, to find the right category for each book given the organization of book categories in book category groups. Like I said in the introduction there are two ways we can accomplish this

One combo box organized in selection groups

The way is very easy to implement and may become particularly handy whenever the total number of list items is relatively small. Cake's Form::imput method will create option groups if the array containing the options for a select box is organized into sub arrays so if we add the following function in our BooksController ....
    private function prepareCategoriesCombo()
    {
        // gain access to the BookCategoryGroups model class
        $this->loadModel('BookCategoryGroup');
        // prepare a list of all book category groups
        $this->BookCategoryGroup->recursive = 0;
        $bookCategoryGroups = $this->BookCategoryGroup->find(
                                'all',
                                array(
                                    'conditions' => array(),
                                    'order' => array('BookCategoryGroup.name')
                                )
                            );

        // create an empty array to hold the combo box options
        $bookCategories = array();
        foreach( $bookCategoryGroups as $bookCategoryGroup) {
            $groupId = $bookCategoryGroup['BookCategoryGroup']['id'];
            $groupName = $bookCategoryGroup['BookCategoryGroup']['name'];
            // create a sub array for each group category
            $bookCategories[] = $groupName;
            // fill the array with the categories corresponding to the group
            $bookCategories[$groupName] = $this->Book->BookCategory->find(
                                'list',
                                array (
                                    'conditions' => array(
                                        'BookCategory.book_category_group_id' => $groupId
                                        ),
                                    'order' => array(
                                        'BookCategory.name'
                                        )
                                )
                                    );
        }
        return $bookCategories;
    }
Supposing that you have baked your original controller and view code with the cake script, your add() or edit() controller actions need to have the following in order to use the option grouped combo:
   ...
   $bookCategories = $this->prepareCategoriesCombo();
   ...
while the view template will require no change at all (i.e. a simple echo $form->input('book_category_id'); will suffice). As I said earlier on, this method is simple enough and unless you intent to let your users pick a US zip code organized by state, this may be the preferred solution for many cases. If however you have lots of data and an untamable desire for ajax, read on; fear not however CakePHP's approach to AJAX makes this look also like a piece of cake.

The AJAX way: Two combos with one auto filtering the other

The basic idea behind AJAX is the following: You start by defining an area in you web page identifiable via the the HTML id attribute. Then when the user clicks on a button or changes the value of some control (edit, list or combo), you make an asynchronous call to the web server -- that is without having to reload the page -- and the server returns HTML code that is ready to be placed inside that area. The actual way you implement this depends on the libraries and the AJAX framework you use. Cake does that using the prototype and the Scriptaculus frameworks.
So to get things started, download prototype and scriptaculus and place the following files in your APP/webroot/js folder:
builder.js   dragdrop.js  prototype.js      slider.js  unittest.js
controls.js  effects.js   scriptaculous.js  sound.js
Having done that, modify you application layout in order to include them. Open APP/view/layouts/default.ctp and change the HTML head part so it looks like this :
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
    <title>
      <?php echo $title_for_layout; ?>
    </title>
    <?php echo $html->css('it-library'); ?>
    <meta name="Generator" content="Quanta Plus" />
    <meta name="Author" content="Thanassis Bakalidis" />
    <?php if (isset($javascript)) : ?>
      <?php echo $javascript->link('prototype.js'); ?>
      <?php echo $javascript->link('scriptaculous.js'); ?>;
    <?php endif; ?>
    <?php echo $scripts_for_layout; ?>
  </head>
The next thing to do is to modify our controller in order to provide Javascript and AJAX support: Our books controller now looks like the following:
class BooksController extends AppController {

    var $name = 'Books';
    var $helpers = array('Html', 'Form', 'Javascript', 'Ajax');
    var $components = array('RequestHandler');

    ...
}
Next go to your view template -- may that be the add or edit.ctp -- and change the initial echo $form->input('Book.book_category_id'); line in order to look like the following:
        // here are the two list boxes displaying groups and categories
        // aim is to create an auto-filter effect with AJAX
        echo $form->label( 'BookCategory.book_category_group_id',
                            'Category Group');
        echo $form->select('BookCategory.book_category_group_id',
                            $bookCategoryGroups,
                            $bookCategoryGroupId,
                            array(
                                'id' => 'bookCategoryGroups'
                            ),
                            FALSE);
        echo $form->input('Book.book_category_id', array('id' => 'bookCategories' ));

        // each time the bookCategoryGroups element changes we are to
        // asynchronously call the updateSelect action of the current 
        // controller and insert whatever the action produces inside the 
        // html DOM element identified by bookCategories
        $ajaxOptions = array('url' => 'updateSelect','update' => 'bookCategories');
        echo $ajax->observeField('bookCategoryGroups',$ajaxOptions);
I believe that the code is self explanatory. Now let us add the updateSelect method of the BooksController class
    function updateSelect()
    {
        $groupId = $this->data['BookCategory']['book_category_group_id'];
        if (!empty( $groupId )) {
            $options = $this->getBookCategoriesForGroup( $groupId);
            // these are the combo box options to be used in the view file
            $this->set('options',$options);
        }
    }
There is one thing to mention here: the AJAX code produced by observeField() serializes the entire field that is supposed to observe, so this will be available in the controller action as $this->data['Model']['field'].
Next we need to create the actual view code. Create a file named update_select.ctp inside your APP/views/books directory and place the following code inside (Thanks HerbCSO):
<?php
    // create  tags coming from a $options variable
    // This is to be used by AJAX in order to fill the contents of a combo
    // box
    if(!empty($options)) {
        foreach($options as $key => $value) {
             echo "<option value=\"$key\">$value</option>";
        }
    }
?>
Now we have everything in place. The only thing left to do is to initialize the two combo boxes so that they contain the correct data, i.e. all the group categories for the top combo and the categories for the selected records category group on the second, during initial page load. To achieve this I have created two additional functions in the BooksController class:
    private function getBookCategoriesGroups()
    {
        $this->loadModel('BookCategoryGroup');
        $this->BookCategoryGroup->recursive = 0;
        return $this->BookCategoryGroup->find('list',
                                                array(
                                                    'conditions' => array(),
                                                    'order' => array(
                                                        'BookCategoryGroup.name'
                                                        )
                                                )
                                            );
    }

    private function getBookCategoriesForGroup( $groupId)
    {
        return $this->Book->BookCategory->find('list',
                                array(
                                    'conditions' => array (
                                        'book_category_group_id' => $groupId
                                        ),
                                    'order' => array(
                                            'BookCategory.name'
                                        )
                                )
                            );
    }
Now my controller's edit action -- which as I mentioned earlier, was baked by cake -- looks like the following.
    function edit($id = null)
    {
        if (!$id && empty($this->data)) {
            $this->Session->setFlash(__('Invalid Book', true));
            $this->redirect(array('action'=>'index'));
        }
        if (!empty($this->data)) {
            if ($this->Book->save($this->data)) {
                $this->Session->setFlash(__('The Book has been saved', true));
                $this->redirect(array('action'=>'index'));
            } else {
                $this->Session->setFlash(__('The Book could not be saved. Please, try again.', true));
            }
        }

        if (empty($this->data)) {
            $this->data = $this->Book->read(null, $id);
        }

        // set up additional book record parameters
        $sites = $this->Book->Site->find('list');
        $bookTypes = $this->Book->BookType->find('list');
        $languages = $this->Book->Language->find('list');

        // setup the two AJAX operated combo boxes
        $bookCategoryGroups = $this->getBookCategoriesGroups();                
        $bookCategoryGroupId = $this->data['BookCategory']['book_category_group_id'];
        $bookCategories = $this->getBookCategoriesForGroup( $bookCategoryGroupId);

        $ratings = $this->Book->Rating->find('list');
        $publishers = $this->Book->Publisher->find('list');

        $this->set( compact( 'sites','bookTypes','languages',
                             'bookCategories', 'bookCategoryGroups', 'bookCategoryGroupId',
                             'ratings','publishers'));
    }
Needless to say that when adding a record, the initial $bookCategoryGroupId can be set to an initial value say 1 and then let your uses change to whatever seems appropriate.
I have tested this with CakePHP 1.2.5 on both Firefox (versions 3 and 3.5) and IE (version 8).
As a last statement, I would like to point out that I am by no means an expert on AJAX or CakePHP. I got my info from an earlier posting by DEVMOZ and the CakePHP AJAXHelper class info page. I have put this down as a working reference to a real problem, that anybody can copy -- hopefully -- easy to modify code.