To follow on from the previous post in which we created a read only version of the Ext property grid based on a Standard APEX report and using templates in APEX, the next step was to turn this into an updateable property grid.

I spent a little bit of time thinking about the best way to manage updates as the support with 2.2.1 is not as extensive as 3.0 ( e.g. REST & CRUD is included in 3.0 for the store, a reason for us to upgrade sooner rather than later but since this was more proof of concept I had to come up with something else). I toyed with the idea of using APEX collections but because I wanted this to be a simple implementation which would reuse an APEX report with no additional setup this was out of the question. So I decided on the following:

1. Perform all messaging via JSON, including the changes to be sent back to the server would reside in a single JSON object. We could easily transmit a JSON object by using “Ext.util.JSON.encode” to turn it into a string and assign it to x01 etc. limitation though is the 32K size of x01 going by the WWV_FLOW api
2. Use PLJSON to easily read the returned JSON object on the server side (we originally used a different JSON implementation but it wasn’t ideal for reading objects back in (it was Ok for constructing them though). I have to admit that the PLJSON code is pretty amazing, I’m keen to do more object orientated PLSQL programming in the future, as this is a really slick implementation have a look inside if you get a chance.
3. Use “EXECUTE IMMEDIATE” to dynamically build the update statement, including support for any APEX specific columns which may need to be catered for e.g. REGION_ID, APP_ID, PAGE_ID, and SESSION_ID. This is to cater for the fact that a property grid only contains 2 columns so this allows multiple properties with the same names across multiple apps, pages, sessions etc.

Here’s a raw extract from our development environment to give you some idea of how we intend to process the data server side, hopefully it will highlight how little work we need to do thanks to PLJSON:

  PROCEDURE properties_save
  ( p_widget_name         IN VARCHAR2 DEFAULT NULL
  , p_widget_mod          IN VARCHAR2 DEFAULT NULL
  , p_widget_action       IN VARCHAR2 DEFAULT NULL
  , p_widget_action_mod   IN VARCHAR2 DEFAULT NULL
  , p_widget_num_return   IN VARCHAR2 DEFAULT NULL
  , x01                   IN VARCHAR2 DEFAULT NULL
  , x02                   IN VARCHAR2 DEFAULT NULL
  , x03                   IN VARCHAR2 DEFAULT NULL
  , x04                   IN VARCHAR2 DEFAULT NULL
  , x05                   IN VARCHAR2 DEFAULT NULL
  , x06                   IN VARCHAR2 DEFAULT NULL
  , x07                   IN VARCHAR2 DEFAULT NULL
  , x08                   IN VARCHAR2 DEFAULT NULL
  , x09                   IN VARCHAR2 DEFAULT NULL
  , x10                   IN VARCHAR2 DEFAULT NULL
  ) AS

    --
    -- Local Variables
    --
    l_Cursor        INTEGER DEFAULT dbms_sql.open_cursor;
    l_Stmt          VARCHAR2(4000);
    l_colCnt        NUMBER DEFAULT 0;
    l_descTbl       dbms_sql.desc_tab;

    l_where_clause  VARCHAR2(4000);
    l_parameter     VARCHAR2(500);
    l_raw_value     ANYDATA;
    l_value         VARCHAR2(4000);

    TYPE varchar_tab IS TABLE OF VARCHAR2(4000) INDEX BY binary_integer;

    l_where_tab     varchar_tab;

    l_json_obj      JSON;
    l_json_array    JSON_LIST;

  BEGIN
    IF p_widget_action_mod IS NOT NULL THEN
      --
      -- Our holding table may contain APEX reference data, i.e. since we can only
      -- have two columns in a property grid we need a way to determine if there
      -- are any other columns within the table to make it unique within APEX.
      -- We use config level tables at the app, page, and session levels so we add
      -- the following to the update statement automatically
      --
      dbms_sql.parse(l_Cursor, 'SELECT * FROM '||DBMS_ASSERT.SIMPLE_SQL_NAME(p_widget_action_mod), dbms_sql.native);
      dbms_sql.describe_columns(l_Cursor, l_colCnt, l_descTbl);
      FOR i IN 1..l_colCnt LOOP
        IF l_descTbl(i).col_name IN ('APPLICATION_ID','APP_ID') THEN
          l_where_tab(nvl(l_where_tab.last+1,1)) := q'!v('APP_ID') = !'||l_descTbl(i).col_name;
        ELSIF l_descTbl(i).col_name IN ('PAGE_ID','PAGE','APP_PAGE_ID') THEN
          l_where_tab(nvl(l_where_tab.last+1,1)) := q'!v('APP_PAGE_ID') = !'||l_descTbl(i).col_name;
        ELSIF l_descTbl(i).col_name IN ('SESSION_ID','SESSION','APP_SESSION','APP_SESSION_ID') THEN
          l_where_tab(nvl(l_where_tab.last+1,1)) := q'!v('APP_SESSION') = !'||l_descTbl(i).col_name;
        ELSIF l_descTbl(i).col_name IN ('APP_USER','APP_USER_ID') THEN
          l_where_tab(nvl(l_where_tab.last+1,1)) := q'!v('APP_USER') = !'||l_descTbl(i).col_name;
        ELSIF l_descTbl(i).col_name IN ('REGION_ID','REGION') THEN
          l_where_tab(nvl(l_where_tab.last+1,1)) := l_descTbl(i).col_name||' = '||p_widget_mod;
        END IF;
      END LOOP;

      --
      -- Lets build our where clause for filtering the update if there are any
      -- of the above columns defined
      --
      FOR i IN 1..l_where_tab.count LOOP
        l_where_clause := l_where_clause || ' AND ' || l_where_tab(i);
      END LOOP;

      --
      -- Lets parse our JSON object which is housed in x01
      --
      l_json_obj := json_parser.parser(x01);
      l_json_array := json_ext.get_json_list(l_json_obj,'row');

      --
      -- Lets loop through the array of updated rows
      --
      FOR i IN 1..l_json_array.count LOOP
        --
        -- Lets get the parameter name from the JSON object, it's always a varchar
        --
        l_parameter := DBMS_ASSERT.ENQUOTE_LITERAL(DBMS_ASSERT.SIMPLE_SQL_NAME(json_ext.get_varchar2(l_json_obj,'row['||i||'].name')));

        --
        -- We can have mixed value types returned so we need to check the data type
        -- first before extracting with the correct function
        --
        l_raw_value := json_ext.get_anydata(l_json_obj,'row['||i||'].value');

        IF json_ext.is_json_bool(l_raw_value) THEN
          l_value := json_ext.get_json_bool(l_json_obj,'row['||i||'].value').to_char;
        ELSIF json_ext.is_number(l_raw_value) THEN
          l_value := json_ext.get_number(l_json_obj,'row['||i||'].value');
        ELSIF json_ext.is_date(l_raw_value) THEN
          l_value := json_ext.get_date(l_json_obj,'row['||i||'].value');
        ELSIF json_ext.is_varchar2(l_raw_value) THEN
          l_value := json_ext.get_varchar2(l_json_obj,'row['||i||'].value');
        END IF;

        --
        -- Note: our value column in our properties table is always a VARCHAR
        --       hence the need to wrap it in quotes
        --
        EXECUTE IMMEDIATE 'UPDATE '||DBMS_ASSERT.SIMPLE_SQL_NAME(p_widget_action_mod)|| ' SET value = '|| DBMS_ASSERT.ENQUOTE_LITERAL(l_value) ||
                          ' WHERE parameter = '||l_parameter||' '||l_where_clause;

      END LOOP;
      htp.p('{ success: true }');
      COMMIT;
    ELSE
      htp.p('{ success: false, message: "Table name not defined!"}');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      htp.p('{ success: false, message: "'||SQLERRM||'"}');
  END properties_save;

A couple of things to note from the above code:

1. Since our values are all stored in a varchar2 column we thought it would be as simple as using PLJSON’s get_charchar2 on our values. However there is no implicit type conversion so you need to know which datatype your dealing with in order to extract the value, otherwise if you use get_varchar2 on say a boolean field it will return NULL., hence why we have to get the raw value first and check the data type before calling the correct extract function
2. What allows us to make this generic for all property grids is that we expect the underling table to have the same format i.e. two columns named “parameter” and “value” .
3. We will dynamically pass the table name through our On-Demand process “Ext.widget”. The table name is extracted from the “Tabular Form Element” section from the “VALUE” column in the report column definition and is added to our report meta data object which appears in the page header, and is referenced in our properties grid javascript function.

Here’s an extract of how we produce our JSON object to POST our On-Demand process client side:

var obj = {
   "row": []
};
ds = Ext.getCmp(pRegionID).getStore();
records = ds.getModifiedRecords();
for (i = 0; i < (records.length); i++) {
   obj["row"].push(records[i].data);
}
var propGridValues = Ext.util.JSON.encode(obj);

Here’s what it looks like through firebug e.g.

Note: there was an issue with Ext 2.2.1 as the property grid store was not keeping track of the modified rows as they were updated (we simply wanted to call “getModifiedRecords()” when processing the changes), the fix was to override the “onUpdate” function within Ext.grid.PropertyStore e.g.

// private
onUpdate: function (ds, record, type) {
   if (type == Ext.data.Record.EDIT) {
      var v = record.data['value'];
      var oldValue = record.modified['value'];
      ds.modified.push(record); // Added this to save the modified records - MJN 12-12-2009
      if (this.grid.fireEvent('beforepropertychange', this.source, record.id, v, oldValue) !== false) {
         this.source[record.id] = v;
         record.commit();
         this.grid.fireEvent('propertychange', this.source, record.id, v, oldValue);
      } else {
         record.reject();
      }
   }
}

As for the notification visualization part on the form we simply use “Ext.StatusBar” and place it at the bottom of the grid using the “bbar” config option. Note: in Ext 3.0 this omitted from the core source so it will need to be “Ext.ux.StatusBar” which you will need to download from their support site.

e.g.

Unfortunately I’m not going into any more detail as it just takes too much time to blog about it in full, but if you’re interested in doing the same hopefully it gives you some insight into a possible option for saving data. As I stated earlier the code is raw we need to clean it up to handle concurrent updates, improved exception handling, improved security etc. so let me know if you have any better ideas or different design options.