Thursday, 10 February 2011

Google Visualization API (Org Chart)

Wanted to have a go at making a family tree, and came across this API. It is worth noting this particular chart doesn't allow you to specify more than one node for the parent, so not exactly perfect for a family tree. See: http://code.google.com/p/google-visualization-api-issues/issues/detail?id=162.

I believe it worthwhile to read the getting started guide for google visualizations: http://code.google.com/apis/visualization/documentation/using_overview.html and the actual guide for using the organizational chart: http://code.google.com/apis/visualization/documentation/gallery/orgchart.html.

Why use this over the charts provided in APEX? For one thing, there is no organizational chart available, to my knowledge. For another, it is not flash ;-) That can only be a good thing!

Alright, down to business. Firstly create a table to store the data:


CREATE TABLE "PERSON"
  (
    "PERSON_ID" NUMBER NOT NULL ENABLE,
    "NAME"      VARCHAR2(200 BYTE),
    "PARENT_ID" NUMBER,
    CONSTRAINT "PERSON_PK" PRIMARY KEY ("PERSON_ID") ENABLE,
    CONSTRAINT "PERSON_PERSON_FK1" FOREIGN KEY ("PARENT_ID") REFERENCES "PERSON" ("PERSON_ID") ENABLE
  );
  /

  create sequence seq_person start with 1 increment by 1;
  /

CREATE OR REPLACE TRIGGER "BI_PERSON" before
  INSERT ON "PERSON" FOR EACH row BEGIN IF inserting THEN IF :NEW."PERSON_ID" IS NULL THEN
  SELECT seq_person.nextval INTO :NEW."PERSON_ID" FROM dual;
END IF;
END IF;
END;
/
ALTER TRIGGER "BI_PERSON" ENABLE;
/

Create an on demand process, to output an Array - which will be later used to draw the chart. I called mine getFamilyData:


declare
  v_out varchar2(4000);

BEGIN
  v_out := v_out || '[';

  FOR i IN (SELECT person_id, name, parent_id FROM person) LOOP
      v_out := v_out || '[';
      v_out := v_out || '{"v":"' || i.person_id || '", "f":"' || i.name || '"},';
      v_out := v_out || '"'||i.parent_id||'",';
      v_out := v_out || '"'||i.name||'"';
      v_out := v_out || '],';
  END LOOP;

  v_out := rtrim(v_out, ',');
  v_out := v_out || ']';
  htp.p(v_out);
END;


Create a form with report on the table you just created.

Create a HTML region - I placed mine below the report, as this seemed as convenient a place as anywhere.

In this region, add the following code (or adapt to suit your needs):


<div id="my_chart_div"></div>
<!--Load the AJAX API-->
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
// <![CDATA[
    
    // Load the Visualization API and the piechart package.   
    google.load('visualization', '1', {packages: ['orgchart']});   
    // Set a callback to run when the Google Visualization API is loaded.   
    google.setOnLoadCallback(drawChart);   
    
    function drawChart() {  
        var data = new google.visualization.DataTable();         
        data.addColumn('string', 'Name');          
        data.addColumn('string', 'parent');         
        data.addColumn('string', 'tooltip');  
        $.post('f?p=&APP_ID.:0:&APP_SESSION.:APPLICATION_PROCESS=getFamilyData::::', 
            function(output){    
                data.addRows(JSON.parse(output));   
                var chart = new google.visualization.OrgChart(document.getElementById('my_chart_div'));   
                chart.draw(data);  
            });    
        }
// ]]>
</script>

Basically, you first need a div for which the chart will live.

You need to load the AJAX API, and load the orgchart package so we can draw it in the div. The way all of the charting works is to first create a DataTable object, specify the columns you want/need. Each chart has different specs - as far as column requirements go, so you need to look at what the chart you are using requires - for the org chart, three columns are required (you need not follow the names I gave mine, but they made sense to me):



  • Text - The Name which will appear on the chart node
  • Parent - A unique value which represents a nodes parent
  • Tool Tip - for when you hover over, the text that will be displayed
You can also have each value as an Object where you want the a return value and a display value. I have used this for the first column so if there are two person(s) with the same name, they will definitely be unique from their ID.

So I used AJAX to get the Array data of the person(s) in the database, which calls the on demand process created earlier. It is then necessary to parse it as JSON before passing it into the DataTables addRow function.

Finally the chart is drawn and voila, you have your family tree outputted on the screen.


Side note: Roel Hartman has already created a plugin for this chart type (amongst others). See http://www.apex-plugin.com/oracle-apex-plugins/dynamic-action-plugin/google-visualizations_29.html.

Tuesday, 8 February 2011

APEX AJAX Basics

Introduction


I'll start of by saying this post isn't about achieving any particular task via AJAX - there are plenty of examples out there already - but how to perform AJAX with Application Express. There seems to be no official documentation on the matter, so wanted to do a general overview on how to achieve AJAX techniques. Of course, it is also worth mentioning, writing your own AJAX functions is not so necessary anymore, with the addition of Dynamic Actions in APEX 4. Never the less, it is still worth knowing.

The most common technique for AJAX in Application Express is to have PL/SQL code in an on demand process, and then calling that process via Javascript at run time. Application Express provides a helper function for calling an on demand process, and that is htmldb_Get (and additionally, apex.ajax.ondemand which is effectively a wrapper object for htmldb_Get object). With the release of APEX 4, came the jQuery library included - so another option for using AJAX is to use the jQuery AJAX API.

htmldb_Get

This function accepts 7 parameters: object, flow, req, page, instance, process, queryString - the most important being the req parameter (3rd one). Detailed explanation:

object – This is the variable that the ouput value of the process will be returned into. It is not important to set this, because the return value of the get function for the object returns this value. i.e. You could either pass a variable into the first parameter, or assign it to the return of the function call.

flow – This is the flowId of the page, which can be accessed via: $v(‘pFlowId’). It is not important to set this, as the object will set the value if it is not set
req – This is probably the most important parameter. This will determine what on demand process will be executed. As you know (or may not) to run an on demand application process, you can do so by placing in the request section of the URL, the text: APPLICATION_PROCESS=processName where processName is the name you gave your application process.
page – Not important as an on demand process isn’t tied to any particular page, however, if it is an authenticated page id, the user must be authenticated, else it has to be a public page.
instance – the htmldb_Get object will again fetch this from the page, so it is not necessary to be set
proc – used for calling a procedure
queryString – to add extra data to the end of the URL

Most of the attributes explanations were just paraphrased from Carls sample application, so please see that for a more detailed explanation – http://htmldb.oracle.com/pls/otn/f?p=11933:63

Most of the parameters are irrelevant, as default values are populated in the htmldb_Get object. So all you really need to call when running an on demand process is:


var get = new htmldb_Get(null, null, 'APPLICATION_PROCESS=processName');
//add some parameters to use in your on demand process
//session
get.add('SESSION_ITEM','SESSION_VALUE');
//or temporary
get.addParam('x01', 'x01_value');
var gReturn = get.get();

Any output generated (with htp.p or htp.prn) in the on demand process will be returned from the get() function of the htmldb_Get object. In the above snippet, returned into the variable gReturn.

Since it is an on demand process, which is PL/SQL, you can use regular session state variables just like in any other PL/SQL process. However, more than likely, you want to pass some value that does not yet have some submitted value. So, you can use one of the available functions of the htmldb_Get object:
  • addParam – Where the 1st parameter is a temporary item that you want to set the value of, and the 2nd parameter is the value of said item. There are 10 temporary items that you are able to set, and they are x01 to x10. Then in your PL/SQL process, you can refer to this item with: apex_application.g_x01 (assuming you set the parameter x01).
  • add – Where the 1st parameter is the session item that you want to set the value of, and the 2nd parameter is the value of said item. I personally think it better to create a temporary application item for use in your on demand process as it will make your code that much more readable. But whatever pleases you :-). Then you just refer to this item as per usual. i.e. bind syntax or with the v/nv functions.
There is also another object you can use, which is a wrapper for htmldb_Get – apex.ajax.ondemand – where you just need to pass in the name of the on demand process. i.e:

var aReq = new apex.ajax.ondemand('processName', function(){ //todo: success. 
 });
aReq._get();



Where the 2nd parameter is the return function, which you can program your function to behave based on the XmlHttpRequest objects’ ready state – which is referred to with the variable p. i.e. if (p.readyState == 4) { var response = p.responseText; }


jQuery

Since APEX 4 comes bundled with jQuery now, another possibility is to make use of the jQuery AJAX API that is available to you. See: http://api.jquery.com/category/ajax/

I tend to just use the jQuery post function, as it effectively wraps up the ajax function setting the properties used for a POST request.

As previously mentioned, when you specify ‘APPLICATION_PROCESS=processName’ in the request portion of the URL, that will result in that on demand process being executed. With that in mind, there are two URLs that you can post to.



  • The application URL with the request portion set accordingly
  • Post to wwv_flow.show and set the data for: p_request, p_instance (the session), p_flow_id (application number), p_flow_step_id (page number) and any session items you want to set the value of, with a combination of p_arg_names (session item) and p_arg_values (item value)
Passing parameters is a little more complicated using jQuery, as there are no add functions to specify a session item to set the value of. If using the standard URL for your request, you can just set session items as per normal, using the standard URL syntax. If however you want to set session items using the wwv_flow.show URL, you need to add a parameter of p_arg_names, which represents the session item, and p_arg_values, which represents the session value. For multiple items, you would make sure they are added in the correct order so that argument names and values are not mixed up. You can also add the temporary items (x01-x10) as per normal, i.e just pass them as an additional parameter.

Examples

Pass the standard URL to run the on demand process getTest, setting a session state item TEST_ITEM, alerting of the returned output:

$.post("f?p=106:2:3500881246167611:APPLICATION_PROCESS=getTest:::TEST_ITEM:test_input", function(data){alert(data);});


Pass the wwv_flow.show URL to run the on demand process getTest, setting a session state item TEST_ITEM, alerting of the returned output

$.post('wwv_flow.show', 
  {"p_request" : "APPLICATION_PROCESS=getTest", 
  "p_flow_id" :  $v('pFlowId'), 
  "p_flow_step_id" : $v('pFlowStepId'), 
  "p_instance": $v('pInstance'), 
  "p_arg_names" : "TEST_ITEM", 
  "p_arg_values" : "test_input"}, 
  function success(data) { 
   alert(data); 
  });


Pass the wwv_flow.show URL to run the on demand process getTest, setting a session state item TEST_ITEM, and setting the temporary item x01, alerting of the returned output:

$.post('wwv_flow.show', 
  {"p_request" : "APPLICATION_PROCESS=insertDum", 
  "p_flow_id" :  $v('pFlowId'), 
  "p_flow_step_id" : $v('pFlowStepId'), 
  "p_instance": $v('pInstance'), 
  "p_arg_names" : "TEST_ITEM", 
  "p_arg_values" : "test_input", 
  "x01" : "x01_value"}, 
  function success(data) { 
   alert(data); 
  });

Useful Links