It's been a busy couple of months. I'm currently building a couple of websites in the Amazon cloud, using APEX of course!

I have to admit that I'm a massive fan of the Amazon cloud, the service is amazing and the cost of running your environments in it is extremely cost effective, especially storage. And since the release of 11gR2 in the cloud, which has it's root device booting off the Elastic Block Store (EBS), it's much much much simpler to use!

Unfortunately this means that the jqGrid integration won't be provided for download until they're completed as I'd like to make it available on the company website. In the meantime, I'd suggest heading over to Morten Braten's blog as he has an alternative integration solution for jqGrid and APEX.

Here's a sneak preview of the company website currently under development...



Most of the content is stored in backend tables and I've converted the layout into a number of different page/region/report templates and AJAX pagination is supported. This means that I can create a number of forms/reports to manage the content without having to access the database or edit the application. I've cheated a little by purchasing a template from template monster but they're great value for money and it's pretty straight forward to merge them into APEX. The only time consuming part is coming up with/changing the content. Modifying the images and flash banner is pretty straight forward if you have a little Photoshop and flash experience.

If you weren't aware, it's quite easy to pass parameters to the flash file (<param name="movie" value="flash/af_menu.swf?button=&APP_PAGE_ID." />) it's the same as the URL syntax. This can then enable your banner buttons to behave like APEX tabs using a little actionscript. Creating a custom RSS feed using PLSQL is simple too, have a look at this post from Tyler Muth for more details...

Hopefully it will be a good showcase that an APEX application or website can look any way you want it to!

I'll be offering an APEX website creation service once the company website goes live. I can even host it for you in the Amazon cloud as an added bonus.

As for the jqGrid integration when it finally arrives, the good news is that I'm supporting inline adding and colModel options which can be defined in the column "comments" section. This is extremely useful for enabling validations and masks using the available jQuery plugins, just to name a few.

As you can tell by the title, i've been a little side tracked over the past couple of weeks from the jqGrid integration (i've also been working on declarative menus based on APEX lists and trees).

A bit of background first... I have to admit that coming from Ext back to jQuery feels like stepping back in time. I'm not trying to cause offence it's just that Ext is backed by a large team of commercial developers and has a strong/tight community. You can argue the same for jQuery but it's behind Ext in functionality and support.

Recently I needed a searchable combo and I was wanting one that was AJAX enabled, allowed filtering and was styleable. The best one I could find for the task at hand was Sexy Combo, and it was nice that it shared a similar feel to Ext. My biggest issue is that I had to search for quite a while just to find a combo, which included reading reviews which resulted in me spending quite a bit of time just choosing this widget. This just drives me crazy!! I can see jQuery UI has got alot of widgets in the pipeline, I just wish it was more feature rich right now!

Now Sexy Combo is pretty light on functionality compared to Ext, it took me quite a while to work out how reload the contents from an AJAX call which returns a JSON object. For simplicity I decided the simplest way was to remove the combo from the DOM and recreate it as there was no function to load the combo with a JSON object, only one to create it.

To make the combo's declaratively cascadable I followed Patrcik Wolf's APEXLIB implementation and made a few tweaks. The rest of the post is dedicated to providing you the code to do this. The thing to note is that this code/approach might be useful in the case of defining combo plugins in APEX 4.0 which APEX may not be able to make dynamically cascadable.

Disclaimer: It's late on Sunday night and this is a long post and a bit of a mess but hopefully it's clear enough to follow the basic steps of what you need to do. The PLSQL might need a little debugging but there's enough here to get the whole approach working.

Pre-Requisities:

• PLJSON
• Sexy Combo jQuery Plugin
• Tyler Muth's jApex plugin

Ok whats required is 1 application level process which contains the following (the supporting PLSQL is further down in the post):



Set the following class for your select lists "ajq-sexy-combo" and add the following JS to your page header/template or or place in an external javascript file.

Note: the code below uses Tyler Muth's jApex plugin.

function jqSexyCombo(pIsTabForm) {

   jQuery('.ajq-sexy-combo').each(function () {
      var select = this;
      // Cascading LOV check for parent
      try {
         var isParent = (pIsTabForm) ? eval("jqTabFormCasLov." + select.name.toUpperCase()) : eval("jqCasLov." + select.id);
      } catch(e) {
         var isParent = null;
      }
      // Cascading LOV check for child
      try {
         var CasLov = (pIsTabForm) ? jqTabFormCasLov : jqCasLov;
         var isChild = new Array();
         for (var idx in CasLov) {
            var lovItems = CasLov[idx];
            for (var i = 0; i < lovItems.length; i++) {
               if (lovItems[i] == select.id || lovItems[i].toLowerCase() == select.name) {
                  isChild.push(idx);
               }
            }
         }
      } catch(e) {
         var isChild = new Array();
      }
      var comboid = select.id;
      var combovalue = jQuery(select).val();
      var addSubmitEvent = (select.className.indexOf("jq-key-submit") !== -1) ? true : false;
      jQuery(select).sexyCombo({
         suffix: "",
         hiddenSuffix: "",
         triggerSelected: true,
         width: 200,
         changeCallback: (isParent) ?
         function () {
            jqCasLovFetch(this, comboid)
         } : null
      });
   });
}
jqCasLovFetch = function (obj, comboid) {
   try {
      var CasLov = jqLov;
      var lBinds = new Array();
      for (var idx in CasLov) {
         var lovItem = CasLov[idx];
         for (var i = 0; i < lovItem.length; i++) {
            if (lovItem[i] == comboid || lovItem[i].toLowerCase() == comboid.substring(0, 3)) {
               lBinds.push(idx);
            }
         }
      }
   } catch(e) {
      var lBinds = new Array();
   }
   if (lBinds.length > 0) {
      var bindItems = lBinds.join(';;');
      jQuery.jApex.ajax({
         appProcess: 'jq.lov.getLovJSON',
         x01: bindItems,
         x02: comboid,
         x03: obj.getCurrentHiddenValue(),
         success: function (data) {
            var jsonObj = JSON.parse(data);
            for (var idx in jsonObj) {
               var selectJSON = eval("jsonObj" + "." + idx + ".row");
               var selectName = jQuery('#' + idx).attr("name");
               var selectId = jQuery('#' + idx).attr("id");
               var selectContainer = jQuery('#' + idx).parent();
               var selectValue = jQuery('#' + idx).val();
               // We need to delete the DOM nodes as we will re-add them, as there's no
               // load function to load a JSON object unfortunately
               jQuery('#' + idx).remove();
               jQuery('[name=' + selectName + ']').remove();
               var newCombo = jQuery.sexyCombo.create({
                  name: selectName,
                  id: selectId,
                  suffix: "",
                  hiddenSuffix: "",
                  triggerSelected: true,
                  value: "DISPLAY_VALUE",
                  key: "RETURN_VALUE",
                  container: selectContainer,
                  initialHiddenValue: selectValue,
                  data: selectJSON,
                  width: 200
               });
               // We may ned to set the combo value if it's in the list
               for (var i = 0; i < selectJSON.length; i++) {
                  if (selectJSON[i].RETURN_VALUE == selectValue) {
                     newCombo.setComboValue(selectJSON[i].DISPLAY_VALUE);
                  }
               }
            }
         }
      });
   }
}
jQuery(document).ready(function(){
   jqSexyCombo(false);
});

and for a non Sexy combo implementation use the following

function jqCasLovInit(pIsTabForm) {
         try { var CasLov = (pIsTabForm) ? jqTabFormCasLov : jqCasLov; } catch(e) { var CasLov = {}; }
         for (var idx in CasLov) {
            jQuery('#'+idx).bind('change', function() { jqCasLovBind(jQuery('#'+idx).val(),idx); });
         }
}
// Cascading LOV fetch event for parent LOV's
jqCasLovBind = function (val, comboid) {
   try {
      var CasLov = jqLov;
      var lBinds = new Array();
      for (var idx in CasLov) {
         var lovItem = CasLov[idx];
         for (var i = 0; i < lovItem.length; i++) {
            if (lovItem[i] == comboid || lovItem[i].toLowerCase() == comboid.substring(0, 3)) {
               lBinds.push(idx);
            }
         }
      }
   } catch (e) {
      var lBinds = new Array();
   }
   if (lBinds.length > 0) {
      var bindItems = lBinds.join(';;');
      jQuery.jApex.ajax({
         appProcess: 'jq.lov.getLovJSON',
         x01: bindItems,
         x02: comboid,
         x03: val,
         success: function (data) {
            var jsonObj = JSON.parse(data);
            for (var idx in jsonObj) {
               var selectJSON = jsonObj[idx]["row"];
               jQuery('#'+idx).children().remove();
               for ( var i=0; i < selectJSON.length; i++ ) {
                 $x(idx).options[i] = new Option(jsonObj[idx]["row"][i].DISPLAY_VALUE, jsonObj[idx]["row"][i].RETURN_VALUE);
               }
            }
         }
      });
   }
}
jQuery(document).ready(function(){
   jqCasLovInit(false);
});

As for making it cascading and declarative I've used a modified version of APEXLIB which fetches the LOV results based on an delimited item list passed in (x01), the difference is that I return all the dependant LOV results in a single JSON object. What's required in the page header is a couple of JSON objects to work out whether the item is a parent LOV and needs to have a "fetch" event binded or a child

var jqLov = {
   "P4_ITEM2": ["P4_ITEM1"],
   "P4_ITEM3": ["P4_ITEM1"]
};
var jqCasLov = {
   "P4_ITEM1": ["P4_ITEM2", "P4_ITEM3"]
};

jqLov lists all tems the list is dependent on whilst jqCasLov lists all items which depend upon it. The above meta data objects are built by calling the following PLSQL procedure called "After Header" - jq_form.p_form_caslovs_json; the underlying PLSQL can be found at the bottom of the post. It's best defining this on page zero.


The result that we expect back from our application process when an onchange/fetch event fires is a JSON object which contains the results of our dependent combos which is in the form of:

{
   "P4_ITEM2": {
      "row": [{
         "DISPLAY_VALUE": "Label 1",
         "RETURN_VALUE": "130"
      },
      {
         "DISPLAY_VALUE": "Label 2",
         "RETURN_VALUE": "131"
      },
      {
         "DISPLAY_VALUE": "Label 3",
         "RETURN_VALUE": "132"
      }]
   },
   "P4_ITEM3": {
      "row": [{
         "DISPLAY_VALUE": "Label 1",
         "RETURN_VALUE": "10"
      },
      {
         "DISPLAY_VALUE": "Label 2",
         "RETURN_VALUE": "11"
      },
      {
         "DISPLAY_VALUE": "Label 3",
         "RETURN_VALUE": "12"
      }]
   }
}

The above JSON object is generated by the On Demand Application Process which we defined earlier, the supporting PLSQL can be downloaded here: caslov.zip

If you have any major issues post them via a comment and I'll look to respond ASAP. Remember to install the pre-requistes first before installing the caslov.sql file.

There's a couple of frustrating things with jqGrid v3.6 and of course all the previous versions. Why is there no inline add functionality, it's only available through a dialog popup? and where are the examples for deleting rows with row editing enabled? I can't get checkboxes to work in tandem with row editing. I'd be happy if someone could shed some light on this for me....

Back to the purpose of the post.... unfortunately it seems that the powers that be with the product have not provided a way to customize the POST parameters for the delete operation, it's just frustrating to say the least (or maybe I've missed something). Anyway I loath going in and amending any of the javascript and I'm yet to find a nice easy way of overriding functionality (though I'm sure its possible, I just don't have the time/patience at the moment). So how can I get APEX to handle the additional parameters named "id" and "oper"??

There's an easy trick you can do with APEX, well modplsql, to cater for fixed POST parameters which you have no control over... simply create a PLSQL wrapper procedure which names the parameters e.g.

create or replace PROCEDURE JQGRID_DEL
( p_flow_id       IN VARCHAR2
, p_flow_step_id  IN VARCHAR2
, p_instance      IN VARCHAR2
, p_request       IN VARCHAR2
, p_widget_mod    IN VARCHAR2
, p_widget_name   IN VARCHAR2
, id              IN VARCHAR2
, oper            IN VARCHAR2
) AS

BEGIN
  wwv_flow.show
  ( p_flow_id       => p_flow_id
  , p_flow_step_id  => p_flow_step_id
  , p_instance      => p_instance
  , p_request       => p_request
  , p_widget_mod    => p_widget_mod
  , p_widget_name   => p_widget_name
  , x01             => '{"oper": "'||oper||'", "id":"'||id||'"}'
  );
END JQGRID_DEL;
/
CREATE OR REPLACE PUBLIC SYNONYM JQGRID_DEL FOR &&USERNAME..JQGRID_DEL
/
GRANT EXECUTE ON JQGRID_DEL to APEX_PUBLIC_USER
/

Now this only works for POST parameters which are fixed, i.e. we can't do this for add or editing records since they're variable based on the column names. So this is an interim workaround until I find a clean solution for overriding the Delete post parameters.

Note: I've used this wrapper technique for years now, and I'm also using it to override APEX functionality without interfering with the product installation. If you're interested in a little more detail you should keep an eye out on the e-DBA blog which I contribute to during my daytime job.

I've been busy for the past couple of weeks and I've made some great progress. I'm now able to support item types like select lists, check boxes etc. (using column LOV definitions), footer summaries based on columns with "Sum" checked under the apex report column definition, validations, custom formatting (using JSON embedded in the column comments section), and saving of data to a collection. I've run into a few issues along the way though and I'll discuss them in a bit of detail...



First was the fact that when editing rows and clicking on the next row, jqGrid performs a server side post of the row data you've just been editing. The problem with this is that we may not want to update the actual data in the backend tables at this point, and it's not exactly ideal to lock the rows whilst we wait for a commit, to be honest I don't think it's even possible in APEX to support this given it's a stateless environment... anyway so the point is I needed a way to keep track of the data changes server side, and to make life even more difficult I needed to ensure that if a grid reload/pagination is performed that we're querying this set of updated data and not the original.

I decided that using a collection was the ideal way to store these changes also given the fact that we can easily generate checksums when processing, to both identify if the row data has changed and also for concurrency checking to make sure we don't overwrite someone else's updates. Problem was though.... how would I get the jqGrid to report off the collection and not the actual query report I've written. Note: the key to this integration is that we simply set the region/report template for a standard APEX report and we automatically get an jqGrid, I want to ensure there is minimal/nothing else you need to do, otherwise what's the point? We use APEX because we want to be productive, same goes for integration, we don't want to have to write the integration every single time we need a grid.

The solution to report off the collection was to automatically create an application process which builds the collection based on my query source and also create a secondary report region (conditional display set to "Never") which copies the original and makes a few tweaks to the query source which reports off the auto created collection. These are both created by using calls from the wwv_flow_api and I gained inspiration from APEXGEN to get an idea of what I needed to do. I've got an application level process which creates this report region and process, it runs on every page "Before Header" and queries the data dictionary to determine if they need to be created.



The trick for getting the report to read off the collection for grid load/reload/pagination/sorting/filtering etc. is handled by my wrapper application process "jquery.widget" which hands of the AJAX calls to a package procedure which is a wrapper around the APEX PPR routine. Within this wrapper I query the current page and check for this secondary report region, if it exists I simply change the URL and swap the REGION_ID with the region id of my collection report along with calculating any additional information for query rowcount, filtering, summing etc. You may think it's a complicated PLSQL routine but it's only 350 lines of code (including comments), APEX still performs the majority of the work as I'm simply re-using the PPR operation, and updating session state to cater for the other requirements. The beauty is that I can reference application items in my report template so that's how I'm able to get all this information into a single JSON object.



I'm aiming to have this packaged for release and documented by end of March/April (The reason for the time lag is that I'm doing this in my own time which I have less and less of these days with a demanding GF and two kids). However if you've stumble across this blog in the interim and would be keen on using this integration before then I'd be happy to provide you the integration package (with a few rough edges) if your happy to conduct some testing and provide some feedback to me.

I've started to implement the editing capability of jqGrid in APEX. After looking at the documentation briefly, I struggled to understand how I could change the POST parameters when rows were saved, as by default I was getting this:

And what I actually needed, was them in the form of x01, x02 etc. So after a bit of searching, looking at the actual code and then back at the wiki, everything was pointing at a non-existent function called "serializeRowData". I then realized that when we create our jqGrid we can pass this function as one of the config parameters just like we do for "onSelectRow".

For simplicity and the fact that APEX onDemnad processes only cater for 10 variables I decided to pass the data back in an actual JSON object, well a string representation of it to be precise, as I'll post process it server side with PLJSON. All I needed to do was convert my postdata object into a string and convert it back to a JSON object and I got a little help from json2.js for that. Here's the solution:

jQuery("#tableid").jqGrid({
 ....
 serializeRowData: function(postdata){
    return { x01: JSON.stringify(postdata) };
 }
 ....
});

Which results in the following:

The next step was to add a new member function to the JSON type in PLJSON to extract the column names to use in the query, i.e. as they are the keys. There's no existing functionality that provides this, but it was simple as the following:

  member function get_keys return json_list as
    keys json_list;
    indx pls_integer;
  begin
    keys := json_list();
    indx := json_data.first;
    loop
      exit when indx is null;
      keys.add_elem(json_data(indx).member_name);
      indx := json_data.next(indx);
    end loop;
    return keys;
  end;

It's the start of a new decade and I've got a long list of New Years resolutions, one is to start a personal blog ( I normally write for the e-DBA blog ) and another is to do some jQuery moonlighting. I'm an Ext JS man, but with APEX 4.0 due out sometime soon and jQuery being a big part of it I thought I'd cover a my bases by gaining a little knowledge.


So my first task is to integrate jqGrid using a template based approach, luckily I'm able apply most the knowledge I've learned from integrating an Ext grid into APEX which has made it fairly straight forward. I guess you might be wondering why I'm going to integrate the jqGrid and not look to create a plugin?

Well the reason purely is that I want to use the APEX IDE to customize it. If you think about the work that went into the "Report Wizard" there's no way I want to look at duplicating it, so I'd rather reuse it, as essentially editing the report definition provides us everything we need e.g. authorizations, conditions, sort order, column names, column formatting, column ordering, page size, max rows, links etc. in order to customize our jqGrid. But what I might look to create is a plugin to manage some extra metadata for it as there's a long list of config parameters for the widget.


Anyway the first step has been to download the source and familiarize myself with the API, and there's a lot of similarities with Ext. So far I've been able to get the jqgrid rendering using a JSON reader, as my report template outputs a JSON object. I'm currently supporting sorting, pagination, filtering and reloading. Which is not much better than your standard PPR report, but it's early days. I've got my eye on subgrids, scrolling rows, and of course an editable version. So watch this space as I'll be offering the integration for download very soon!