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!