Using websockets in APEX for automatic refresh

I visited the OGH APEX day this monday in Zeist. Not only gave Patrick Wolf some insight in the possible upcoming new features of 4.2, but there were also a bunch of inspiring speakers that gave me some new ideas. One of the speakers Roel Hartman gave a presentation of some HTML5 features. He used a notification-service to sent messages to the mobile devices of some of the members of the audience.

That gave me the following.

What would it take to let the database push a refresh on the screen.

Normally you have to implement a timer mechanism that uses resources to poll the database in some frequency, say 5 seconds, to keep the report up to data.
But what if we could use websockets to let the database push a refresh after an insert.

And it works! In its simplest form it’s so easy it almost made me cry.

What I did was the following:

1. Go to Beaconpush and sign up.

After registration you will get an API-key and a Secret Key

2. Create a page with an interactive report that you want to be refreshed.
In the page footer add the following:

    Beacon.connect('*****', ['mychannel']);
    Beacon.listen(function (data) {
      if (data.message=='REFRESH') {
        gReport.pull();
      }
      else {
        alert(data.message);
      }
    });

with ***** your API-key.
Here I use the default channel. Now when we post a JSON-object

{ "message": "REFRESH" }

the message-attribute of the data-object will refresh the report. This will make your page listening to its channel in the beaconpush-server.

3. Create a simple package

create or replace package body beacon
is
/*Run these ACL-Setting as SYS when you are in 11G
begin
  dbms_network_acl_admin.create_acl
    ( acl         => 'beaconConnectList.xml'
    , description => 'Beaconpush ACL'
    , principal   => 'APEX_040100'
    , is_grant    => true
    , privilege   => 'connect'
    , start_date  => null
    , end_date    => null
    );
  commit;
end;

begin
  dbms_network_acl_admin.assign_acl
    ( acl        => 'beaconConnectList.xml'
    , host       => 'api.beaconpush.com'
    , lower_port => null
   , upper_port  => null
   );
  commit;
end;
*/

procedure push
  ( p_text in varchar2
  )
is
  l_clob clob;
begin
  l_clob := APEX_WEB_SERVICE.MAKE_REST_REQUEST
  ( p_url => 'http://api.beaconpush.com/1.0.0/*****/channels/mychannel'
  , p_http_method => 'POST'
  , p_body =>'{ "message": "'||p_text||'" }'
  );

end;

end;

We are almost there. Because in its simplest form I don’t want to use the secret key I disabled it in the account-settings on beaconpush.com:

Disable secret key usage

That’s it. You can test it by executing a simple

begin
  beacon.push('REFRESH');
end ;

from your favorite IDE like SQL*Plus to test that the page has automagically refreshed.
Then put this code in some after insert trigger and you are done.
In a production system there are of course still a few things to think of
– using the secret key
– realise how many messages you will push and read the conditions of beaconpush very carefully.

Maybe you don’t want to use beaconpush. Maybe you want to write your own Comet server.

Advertisements

3 thoughts on “Using websockets in APEX for automatic refresh

  1. Pingback: Using websockets in APEX for automatic refresh with nodejs « emoracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s