Wednesday, January 12, 2011

Counting clicks

In the past week, I've been asked to help implement the "counting of clicks" in two different applications, the forthcoming Oracle Learning Library being one of these applications. All that's really desired is to track when someone clicks on a link. This is usually accomplished by responding to the click, inserting a row into a log table and then redirecting to the desired target URL.

If you're familiar with APEX_UTIL.COUNT_CLICK, this is implemented in a similar fashion. There is an underlying log table in Application Express which has a row inserted into it before redirecting to the desired URL. But the requirements of these two applications were a little bit different and required something more than APEX_UTIL.COUNT_CLICK, namely:

  1. They wanted to preserve the logged clicks for all time and they wanted to insert into their own log table. In the case of APEX_UTIL.COUNT_CLICK, it's actually stored in a set of two rotating tables which switch every 2 weeks.

  2. In the case of the Oracle Learning Library, we found that when you're on an SSL-rendered page, if you redirect to another URL, even a fully-qualified one, it will always redirect to an SSL (https) URL. Thus, we needed a solution which would not do a browser redirect.

The solution I devised was quite straightforward. It involved:

  1. A page-level on-demand process
  2. A local JavaScript function
  3. and an anchor with an Onclick event

That's it! A demonstration of this logic is in an application here.

To demonstrate, I created a simple table to store some of my most commonly used URLs. I built an application containing a report and form on top of this table, and then removed a lot of the functionality of the form to make it read-only.

Then, I created a simple log table with columns for some of the information I'd like to capture:



create table click_log(
user_name varchar2(1000),
click_ts timestamp,
ip_address varchar2(100),
user_agent varchar2(1000),
apex_session_id number,
rownum_clicked number,
info varchar2(4000) )


With this basic infrastructure in place, I now want to implement the three constructs to easily enable click logging of these URLs.

On page 2 of my application (the "form" page), I created a PL/SQL process named LOG_CLICK with execution point "On Demand - Run this process when requested by AJAX". It consisted of the code:


insert into click_log(
user_name,
click_ts,
ip_address,
user_agent,
apex_session_id,
rownum_clicked,
info )
values
( v('APP_USER'),
systimestamp,
owa_util.get_cgi_env('REMOTE_ADDR'),
owa_util.get_cgi_env('HTTP_USER_AGENT'),
v('APP_SESSION'),
v('P2_ID'),
'Clicked: ' || v('P2_NAME') );
commit;

Note how I'm able to use both session state information via the APEX v() function and the PL/SQL Web Toolkit functions owa_util to get other information about the user.



The second part of this solution is a JavaScript function which I used to invoke my on-demand function. I defined this in the page-level attributes of page 2 in the JavaScript section for Function and Global Variable Declaration:




function f_logClick(){
var req = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=LOG_CLICK',&APP_PAGE_ID.);
var gReturn = req.get();
req = null;
return gReturn;
}

I'm no AJAX wizard and I won't pretend to be, but this is the syntax (even in Application Express 4.0) to invoke an On-Demand process from JavaScript. For those familiar with On-Demand process invocation, note how I had to specify &APP_PAGE_ID. because I defined this process at the page-level and not the application-level.


So with my On-Demand process in place and my JavaScript function defined, all I needed to do is simply employ this function. And I did this by augmenting the URLs on my form page, changing them from:

<a href="http://www.amazon.com/" target="_new">http://www.amazon.com</a>

to:

<a href="http://www.amazon.com/" target="_new" onclick="f_logClick();">http://www.amazon.com</a>

You can see a demonstration of this logic here in My Favorite URLs application.






Note: View the comments below for an even simpler implementation using dynamic actions, new in Application Express 4.0

4 comments:

Dimitri Gielis said...

Hi Joel,

Nice post and this approach works both in APEX 3 as 4.0.

If I'm allowed to give my view on it, with 4.0 I would have preferred to use a Dynamic Action.

It would mean you don't need to define the onclick event on every link you want to track. Instead you could use a JQuery selector like "a" or if you don't want to track all links, you first assign a class to the link and do something like a.link (if link was the class assigned).

The nice thing with APEX 4.0 is that these Dynamic Actions give you less moving parts (no custom javascript code, no app process, no onclick code).

I would create a dynamic action like:

When:
Event: Click
Selection Type: JQuery Selector
JQuery Selector: a.link (if you defined link as the class)

True Actions:
Execute PL/SQL Code which has the logic of your Application Process.

That is it. Especially with APEX 4.0 my motto is to use as much build in features of APEX as possible and limit as much as possible custom code.


This is definitely not to criticise your approach, but to give another solution to the people reading this and who are on APEX 4.0. Hope that is ok with you.

Dimitri

Joel R. Kallman said...

Hi Dimitri,

I love it! Your suggested method, while it's specific to APEX 4.0, is much simpler. As a matter of fact, it took me all of 3 minutes to extend my sample application to use your suggested implementation via a jQuery selector (id="link") and a dynamic action.

It's at:

http://apex.oracle.com/pls/apex/f?p=46009:4

Thanks again - I welcome your comments/suggestions/improvements at any time.

Joel

Stew said...

For those of us who haven't used Dynamic Actions yet and aren't as smart as Dimitri, could someone please post the implement using that method?

Thanks,

Stew

Joel R. Kallman said...

Here you go, Stew.

http://joelkallman.blogspot.com/2011/01/counting-clicks-via-dynamic-actions.html