EXTJS & Oracle & PLSQL with the GridPanel & ComboBox includes pagenation,AJAX, sorting, searching.

I have been working with new EXTJS framework for a while now and decided to create a post showing you how to create a new grid & combo box which showcases some of the following features.

1. Grid running with the following buzzwords pagenation, AJAX, sortable and jsonStore.
2. Combo box with the following buzzwords pagenation, AJAX, typeahead, autocomplete and JsonStore.
3. Link both the search combo box’s with the Grid and execute a new search based on the search criteria enter via combo box.

Before I start I would like to show you some screen shots of the end result, and all the images used in this post.

The backend database is Oracle version 10.2.0.1.0, but I have not used any specific feature that would preclude this from working with Oracle 8+, though it is a while since I have gone anywhere near an Oracle 8 DB. The server side language is Oracles own PL/SQL (procedureal Lanaguage), I will demonstrate how to write the server side PL/SQL packages behind above screen shots.

Some assumptions before you read on.

1. You have Apache already configured and a DAD (database access descriptor) set up pointing back to your DB.
2. You have Oracle Application server any recent version will suffice, as long as you have mod_plsql configured. My Oracle application server version is 10.1.2.
3. You are somewhat familar with the EXTJS framework, you have at least heard of it and have google’d and view the demonstrations on their site. If not you should.

What if you dont have Oracle Application server or if you want to use Oracle XE?

Their is nothing in my PLSQL code that will stop you running in Oracle XE, however Oracle XE is not shipped with mod_plsql, an open source alternative to mod_plsql is mod_owa written by an Oracle employee. A colleague of mine Joe Lennon has a nice post on installing Apache and mod_owa you can find it here.

Lets get started, I will take the following approach, if you are not interested in the Oracle side of this post, jump straight down to section 3 on EXTJS components where all the nice visual magic takes place.

1. create the back end tables
2. create the server side plsql procedures to handle the AJAX requests and pagenation.
3. create the EXTJS components

1. Create the back end tables

I am using four back end tables over all, apologies about the names, I created this solution using pre-existing tables from our production product CoreHR, none the less you should be able to follow easily. You can get a download of the tables here

1. cp_all_employees – this table contains all the information about an employee, e.g. employee_id, surname and forename, used in figure 2.0.
2. cp_portal_attendance – this table contains all the information you see in the grid in all of the figures.
3. ct_pay_code – simply stores the name of the time code descriptions, used in the attendance type combo seen in any of the figures above.
4. cp_cost_centre – again very simple, contains the cost centre name and code.

I dont want to spend time on creating of the tables, I again assume you can create tables.

2. Server side PLSQL procedures to handle the AJAX request & pagenation

These PLSQL procedures are based on the above tables, you can easily adapt for your own tables. Lets start with an easy one, the AJAX procedure to return the Json data required for the employee combo box, this procedure will handle pagenation. to handle pagenation generally the server side procedure will need to know where to start and where to finish and also how many records in total. AJAX pagenation is better for the browser because you are only returning a defined (limit) per request. In my examples in screen shots above I only bring back 10 records at a time. (more detail on pagenation from ASK TOM can be found here ).  The full procedure outlined below can be downloaded with the following link ajax_get_employee

procedure ajax_get_employee (query in varchar2, p_start in varchar2, p_limit in varchar2) is

the parameters in the procedure signature are very important

• query – this will allow your server side procedure to accept the values being typed in the combo box. e.g if you start typing “du” all employees with du in their forename or surname will be returned, as long as your select statment caters for it.

• p_start & p_limit – these parameters will be used for the pagenation, if p_start=0&p_limit=10 this will tell your PLSQL procedure to start at 0 and return at most 10 records, if p_start=11&p_limit=10 this will tell your plsql procedure to start at record 11 and return 10 records.

No point in just having these parameters in your procedure signature, you also need to utilise them in your select statement.

cursor c1_desc is
select * from (
select a.*, rownum rnum from (
select cp.surname||' '||cp.forename complete_name,
cp.employee_no employee_id,
cp.cost_centre cost_centre
from cp_all_employees cp
where ( (lower(cp.surname) like lower(query) ||'%') or (lower(cp.forename) like lower(query) ||'%'))
order by cp.surname||' '||cp.forename) a
where rownum < = (p_limit + p_start))
where rnum > p_start;

The above select statement is pagenation aware, meaning it can handle pagenation, notice how p_limit and p_start are utilised. basically if you want your select to handle pagenation wrap your main select in the following;

cursor c1_count is
select count(*) total_count
from cp_all_employees cp
where ( (lower(cp.surname) like lower(query) ||'%') or (lower(cp.forename) like lower(query) ||'%'))
order by cp.surname;

Once you have your pagenated records and your total selects created, you next need to think about how to return this to the client in a JSON formatted way,  it is important the names of the columns you send back to EXTJS. I will reference in the next section back to these columns (i.e. fullName, id, costCentre)

your JSON needs to return in the following format.

{ totalCount: 52,records:[
{fullName: "Butler Paula", id: "0029", costCentre: "Core Cork Head Office"},
{fullName: "Cahill Richard", id: "0015", costCentre: "Core Cork Head Office"},
{fullName: "Campbell Rob", id: "789456", costCentre: "Core Kilkenny Office"},
{fullName: "Carter James", id: "017", costCentre: "Core Cork Head Office"},
{fullName: "Chandler Karima", id: "0020", costCentre: "Core Cork Head Office"}
]}

This includes a totalCount and the Records, to achieve this in PLSQL: (just note I have wrapped my SQL statements in curosr, c1_count for the total count and c1_desc for the selection of employees, hence you see for loops below) .

 for x in total_count (l_person) loop
    v_total_count := x.a_total_count;
   end loop;
htp.init;
owa_util.mime_header('application/json', false);
owa_util.http_header_close;
htp.p(' { totalCount: '||v_total_count||',records:['
);
if upper(nvl(dir,'ASC')) = 'DESC' then
for x in c1_desc (l_person) loop
      htp.p('{first_name: "'||initcap(x.forename)||'",
              last_name: "'||initcap(x.surname)||'",
              employee_id: "'||x.employee_id||'",
              time_code_description: "'||initcap(x.time_code_description)||'",
              cost_centre_description: "'||initcap(x.cost_centre_description)||'",
              attendance_date: "'||x.attendance_date||'",
              attendance_hours: "'||x.attendance_hours||'",
              attendance_date_raw: "'||x.raw_date ||'",
              primaryID: "'||x.attendance_id ||'"
      }');
   if c1_desc%ROWCOUNT < p_limit then
      htp.p(',');
   end if;
end loop;

Be careful when returning JSON, you should escape any invalid characters, I simply show you above how I escape my JSON (using a function called json_characters_escaped), I run it through a function which returns escaped JSON, I will leave you figure out your own JSON escape function.

Ensure your procedure returns the JSON in the correct form, otherwise your snookered, I use firebug's NET feature to see how my PLSQL returns JSON; see following screen shot.

If you want you can use the following this link to validate your JSON

Hopefully, that explains the PLSQL procedure, the procedures used for the other combo boxes in the screen shots above can be found using the following links ajax_get_cost_centre

ajax_get_attendance_type

3. Create the EXTJS components

This section is where all the visual elements are declared, usually with PLSQL and Oracle I declare my JS and CSS files as procedures within the main package, meaning they get executed each time a request is made, and yes there are not cached, the main extjs javascript library files are stored in virtual directories are cached in the same way the images are cached. You can download all my CSS and JS files create_css_and_js

3.1 Creating the Grid Component and populating

Firstly you need to create a JSONStore, and before you create this store you need to define columns, the columns I define here are the columns your JSON procedure the server side script returns.

     var recordFields = [
         {name: "first_name", mapping: "first_name"  },
         {name: "last_name", mapping: "last_name"  },
         {name: "employee_id", mapping: "employee_id"  },
         {name: "time_code_description", mapping: "time_code_description"  },
         {name: "cost_centre_description", mapping: "cost_centre_description"  },
         {name: "attendance_date", mapping: "attendance_date"  },
         {name: "attendance_hours", mapping: "attendance_hours"  },
         {name: "attendance_date_raw", mapping: "attendance_date_raw"},
         {name: "primaryID", mapping: "primaryID"}
         ];

Ok, above declares a variable that defines the columns, next lets link this object to a JsonStore.

       var remoteJsonStore;
        remoteJsonStore = new Ext.data.JsonStore( { fields : recordFields,
            url :"cp_attendance_enquiry.ajax_get_data",
            totalProperty : "totalCount",
            root : "records",
            id : "ourRemoteStore",
            autoload   : false,
            remoteSort : true ,
            baseParams : {p_employee_no: "", p_attendance_type: "", p_cost_centre:""},
            paramNames: { start: "p_start", limit: "p_limit"} //override the default parameters passed back to server, dont want start and limit passed back needs to be p_start....
        } )

Above declares a JsonStore object, first parameter passed in are the columns defined previously, a very important parameter next is the URL, this is the remote procedure that will be called on the server, next parameter is the totalProperty, this is the name of the Json parameter returned from your server side procedure, look back over the post and you will see the totalProperty being defined as totalCount in the Json returned from the server, also note in the procedures if you downloaded them return back this property. The root parameter is very important, this is the parameter that defines where all your data is going to come from, again look back over the post and you will see the Json return an object called “records”, the parameter ID is pretty obvious, this will allow us to get a handle / pointer to the json store if needs be during its lifetime in the DOM. The parameter autoload indicates to automatically load the store on creation, I set this to false as I want to load it manually, which if you down load the entire CSS and JS file above you will see the store manually loaded e.g.

   Ext.StoreMgr.get("ourRemoteStore").load ( {
       params: {
           p_start : 0,
           p_limit: 10
       }
      } );

next is remoteSort, this means sorting is sent back to the server to be handle, doing this will mean your server side procedure will have to handle some extra parameters, like “dir” and “sort”, these are parameters EXTJS will automatically send your server side procedure “dir” will either be ASC or DESC for ascending / descending. In the above example the remote procedure cp_attendance_enquiry.ajax_get_data will need to be abe to handle both “dir” and “sort”. setting the baseParams, means these parameters will always be sent to the server side procedure, i.e. in the post to the server the URL will contain at least,

p_employee_no=&p_attendance_type=&p_cost_centre=

in fact it will also contain the limit and start params so at a minimum the following params are sent to the server side procedure cp_attendance_enquiry.ajax_get_data, why are the p_start & p_limit parameters sent? I will explain this when dealing with the pagenation toolbar further down the post. the reason I am mentioning them here is because for Oracle I need to override the default values of strart and limit being sent to the server, if you dont include this param, EXTJS will send “start” and “limit” to the server side procedure, both of these are reserved words so you need to over write and ensure they are sent as p_start and p_limit. OK thats all for the JsonStore,

p_start=0&p_limit=10&p_employee_no=&p_attendance_type=&p_cost_centre=&sort=last_name&dir=DESC

next up is defining the columns to be displayed in the Grid. Note I have 10 columns returned in the JsonStore and decide to display 5 on screen via your grid. Lets define the parameters for the grid.

var columnModel;
        columnModel = new Ext.grid.ColumnModel( [
            {
                header : "Unique Identifier",
                dataIndex : "primaryID",
                sortable : false,
                width : 50,
                resizable : false,
                hidden : true
            },
            {
                header : "Staff Number",
                dataIndex : "employee_id",
                sortable : false,
                width : 50,
                resizable : false,
                hidden : true
            },
            {
                header : "Last Name",
                dataIndex : "last_name",
                sortable : true,
                hideable : false,
                width : 120
            },
            {
                header : "First Name",
                dataIndex : "first_name",
                sortable : true,
                hideable : false,
                width : 120
            }   ,
           {
                header : "Attendance Type",
                dataIndex : "time_code_description",
                sortable : true,
                hideable : false,
                width: 175
            }   ,
            {
                header : "Cost Centre",
                dataIndex : "cost_centre_description",
                id: "ccd",   //ID is used in the Grid Panel to set the autoExpandColumn
                sortable : true,
                hideable : false,
                width: 170
            }   ,
            {
                header : "Attendance Date",
                dataIndex : "attendance_date",
                sortable : true,
                hideable : false,
                width: 125
            }   ,
            {
                header : "Attendance Hours",
                dataIndex : "attendance_hours",
                sortable : true,
                hideable : false,
                width: 100
            }
         ]);

OK, lets explain the following params

• header – simply the name displayed on column header
• dataindex – how column is mapped back to the jsonstore
• sortable – can you sort or not via this column
• hideable – can you turn of the visibility of this column
• width – what the default width this column should be set to

Do note, if you set a column to be sortable, then the columns dataIndex value will be sent to the server side procedure as the descrition in the “sort” variable. E.g. if you select to sort by Attendance Date on screen, then EXTJS will send an extra variable to your server side script, in this case the dataIndex value of attendance date is “attendance_date” so the post to your server will include the value of “sort=attendance_date”. So ensure your server script can handle this extra parameter, EXTJS does this very nice because all you have to handle is a vairable called sort, meaning if you add extra columns to display on screen and you wish to subsequently sort, you need not change your function prototype (header signature) all you need to handle the the value contained within the sort column i.e. what ever the name of the dataIndex of the new column. Next up is the pagenation toolbar,

        var pagingToolbar;
        pagingToolbar = { xtype : "paging",
            store : remoteJsonStore,
            pageSize : 10,
            displayInfo : true };

Whats important here? I define the xtype to be paging, to understand xtypes go here. Finally, the last step to create the actual grid componenet,  I combine all items column model, jsonstore and paging toolbar with a GridPanel component.

   // create the Grid
    var grid = new Ext.grid.GridPanel({
        height: 300,
        width: 900,
        colModel: columnModel,
        store: remoteJsonStore,
        loadMask: true,
        title: "Attendance Record Detail",
        columnLines: true,
        stripeRows: true,
        bbar: pagingToolbar,
        fbar: [button_new_ts, button_ex_type, button_type_delete],
        buttonAlign: "center",
        //autoExpandColumn: "ccd", //must be the ID and not the dataIndex value, this param allows the cost centre parameter to take up any extra free space.
        viewConfig: {
         forceFit: true,
         emptyText: "No data available to display...."
        }
    });

important attributes, I ignore the obvious ones, like height, width etc.

colModel: this links back to the column model.

store: links to the Json Store

loadMask: this create the loading data… indicator and create a transparent div over the grid, when it is loading, which aids informing the user that the gird is loading.

columnLines: creates subtle column lines to seperate the columns

stripRows: inter changes the row colour when several rows appear.

bbar: this is the bottom toolbar which will display the pagenation toolbar.

viewConfig: I force fit the Grid to fit the entire available space, this ensures if you adjust the grid columns, the entire size of the grid will re-calculate and re-adjust proportionally and set the empty text meaning when no data is available to display in the grid.

autoExpandColumn: if extra space is available this column should take it.

I have some buttons that I also add for the fbar (footer bar) but I am going to ignore for the moment.

3.2 Creating the Combo Box Elements.

These combox elements have type ahead suggestions, perform searches as you type, run in an ajax mode and appear in a pagenation form. I am only going to define one of the combo boxes I am sure you can follow and adapt for code. I will be placing the combo box into a fieldset and then displaying the field set within a form panel

        var comboFields = [
        {name: "fullName", mapping: "fullName"},
        {name: "id", mapping: "id"},
        {name: "costCentre", mapping: "costCentre"},
        ];

       var remoteJsonStoreCombo;
        remoteJsonStoreCombo = new Ext.data.JsonStore( { fields : comboFields,
            url :"cp_attendance_enquiry.ajax_get_employee",
            root : "records",
            paramNames: {start:"p_start",limit:"p_limit"},
            totalProperty: "totalCount",
            id : "remoteJsonStoreCombo"
        } ); 



//define all the columns to be displayed in the search section.
var comboFields = [
   {
         xtype: "combo",
         fieldLabel: "Employee Name",
         forceSelection: true,
         displayField: "fullName",
         valueField: "id",
         hiddenName: "id",
         loadingText: "Querying...." ,
         minChars: 1,
         triggerAction: "all",
         store: remoteJsonStoreCombo,
         emptyText: "Start by typing employee''s name or select from drop down",
         listEmptyText: "No employees were found matching search criteria",
         selectOnFocus: true,
         pageSize: 5,
         id: "employee_combo",
         tpl: tpl
   },
   {
         xtype: "combo",
         blah, blah, blah,
    },
   {
         xtype: "combo",
        blah, blah, blah,
   }]

var fieldset1 = {
 xtype:  "fieldset",
 border: false,
 autoHeight: true,
 labelWidth: 175,
 defaults: {width: 450},
 defaultType: "textfield",
 items: comboFields
};

//display the set of fields on screen.
var myformPanel = new Ext.FormPanel (
    {
      width: 900,
      cls: "panel_padding",
      title: "Search Attendance Details",
      collapsible: true,
      titleCollapse: true,
      frame: true,
      id: "searchPanel",
      items: [fieldset1],
      fbar: [button_type_search,button_clear_filter ],
      buttonAlign: "center"
    });

start off by defining the column and mappings as done in previous section, these will be used by the jsonstore, next define the json store, again same parameters as in previous section, next up is the create of the combo box, some important columns,

• xtype: set this to be a combo type
• displayField: this is the field that will be displayed from the mapped json store, though note I further down use a template to display the composite information.
• valueField: the important value or unique identifier to indicate which value has been selected.
• loadingText: when the query goes to the DB and awaiting a response.
• minChars: number of characters that need to be entered
• triggerAction: set to all allowing the user to select the drop down box instead of having to type any characters.
• store: this is where the information will come from, in this case a Json Store pointing back to the server side procedure
• emptyText: The value in the combo box when nothing has been typed, used in my ase as a hint.
• listemptyText: when the jsonstore returns nothing, what will appear in the combo box.
• selectonFocus: when the user has selected a value from the combo box, setting this to turn will force the combo box field to maintain the cursor focus
• pagesize: for pagenation
• id: unique identifier
• tpl: template, you will need to reference back to the figure 5.0 at start of the post for the code, this tempplate allowed me combine several fields to crate a complex / composite row for each employee when employee Name, Employee ID Cost Centre etc. becuase the template contains some HTML I had difficulty with word press displaying in my code box above, so please refer back to figure 5.0 above.

Next I define the field set hopefully the attributes are pretty obvious, this field set is a nice concept, it groups all my fields together. This is useful if you might this additional fields will be added to the form. Next up is the FormPanel, again hopefully the field attributes are pretty obvious. the footer bar FBAR has some buttons, I will define these at a later stage, thats it for the moment, I will be back to go through

To Do: The Searching and the buttons.