In recent articles, I have introduced CometD as framework for Server to Client and Client to Client Push: Running CometD 2 examples – locally on Tomcat using Maven and NetBeans https://technology.amis.nl/blog/14709/running-cometd-2-examples-locally-on-tomcat-using-maven-and-netbeans, CometD 2 Java Client Sample – open project in NetBeans based on Maven pom file, modify sources and run Java Based Comet Client (https://technology.amis.nl/blog/14720/cometd-2-java-client-sample-open-project-in-netbeans-based-on-maven-pom-file-modify-sources-and-run-java-based-comet-client) and Push based synchronized Slideshow demo application implemented using CometD and jQuery running on Tomcat (https://technology.amis.nl/blog/14870/push-based-synchronized-slideshow-demo-application-implemented-using-cometd-and-jquery-running-on-tomcat).
CometD allows web clients (or stand alone Java clients) to subscribe to Bayeux channels maintained by the CometD Server (Servlet). These clients can publish messages to the channels and CometD will deliver the messages to all or to selected clients – in a push-fashion. This makes it possible to push messages from the Java server side of a web application to web clients and indirectly to do the same between web clients. One example I have described of applying this technology is the synchronized slideshow: a web page is opened in two or more browsers. When a slide is selected in one of the browsers, the slide is selected in all browsers (because they all subscribed to the channel that a slide selection event was published to).
This article adds another push channel to the overall picture: it describes how the Oracle Database can publish directly to the Bayeux Channels (by posting to the CometD servlet over HTTP) and thereby perform push to the subscribed web clients. Database to browser push – how does that sound?
What is happening can be described like this:
A PL/SQL procedure in the Oracle Database uses the UTL_HTTP package to post HTTP requests to the CometD servlet. These requests contain the same JSON messages that the browser sends to connect to CometD (perform handshake) and publish a slideSelection event to the Bayeux channel. When the post request is received by the CometD Servlet, it does not look any different than the requests received from the browser or the Java client.
Analyzing HTTP traffic for CometD handshake and message publication
I have used Firebug to analyze the HTTP interaction from the browser when the connection to the CometD ‘server’ is initiated and when the slide selection message is sent. Even though the client does a lot of JavaScript processing, in the end it all ends up in plain HTTP requests – and those can be made from PL/SQL as well.
The initial interaction for a client to open communications with CometD happens with a POST request:
The call is made to http://localhost:8085/slideshow/cometd – that is the URL for the CometD Servlet in the web application running on my local Tomcat server, as described in the other articles. The importance of the handshake lies in the response:
This response contains the clientId that is required to be recognized by CometD as a known client when we next try to publish messages to the Bayeux Channel.
The message published when a slide is selected in the web client looks like this:
Note how the clientId allocated by the CometD Servlet is now used to identify the message publisher. The channel this message gets published to is /slide/show and the actual payload of the message is in the data property of the JSON object. Only the slideNumber is really relevant.
Create the PL/SQL procedure(s) to publish to CometD
With this knowledge under our belt, we need to create a PL/SQL procedure -post(p_url, p_message) – that can POST a message to a given URL. This procedure can then be invoked by our main procedure set_slide that takes a slidenumber as input and uses the post procedure to send the JSON requests to the CometD Servlet to first connect (or handshake) and then publish the slide selection message.
First the fairly generic post procedure:
PROCEDURE post(
p_url IN VARCHAR2,
p_message IN VARCHAR2,
p_response OUT VARCHAR2)
IS
l_end_loop BOOLEAN := false;
l_http_req utl_http.req;
l_http_resp utl_http.resp;
l_buffer CLOB;
l_data VARCHAR2(20000);
value VARCHAR2(1024);
BEGIN
-- source: http://awads.net/wp/2005/11/30/http-post-from-inside-oracle/
-- Ask UTL_HTTP not to raise an exception for 4xx and 5xx status codes,
-- rather than just returning the text of the error page.
utl_http.set_response_error_check(false);
-- Begin the post request
l_http_req := utl_http.begin_request (p_url, 'POST');
-- Set the HTTP request headers
utl_http.set_header(l_http_req, 'User-Agent', 'Mozilla/4.0');
utl_http.set_header(l_http_req, 'content-type', 'application/json');
utl_http.set_header(l_http_req, 'content-length', LENGTH(p_message));
-- Write the data to the body of the HTTP request
utl_http.write_text(l_http_req, p_message);
-- Process the request and get the response.
l_http_resp := utl_http.get_response (l_http_req);
dbms_output.put_line ('status code: ' || l_http_resp.status_code);
dbms_output.put_line ('reason phrase: ' || l_http_resp.reason_phrase);
LOOP
EXIT
WHEN l_end_loop;
BEGIN
utl_http.read_line(l_http_resp, l_buffer, true);
IF(l_buffer IS NOT NULL AND (LENGTH(l_buffer)>0)) THEN
l_data := l_data||l_buffer;
END IF;
EXCEPTION
WHEN utl_http.end_of_body THEN
l_end_loop := true;
END;
END LOOP;
dbms_output.put_line(l_data);
p_response:= l_data;
-- Look for client-side error and report it.
IF (l_http_resp.status_code >= 400) AND (l_http_resp.status_code <= 499) THEN
dbms_output.put_line('Check the URL.');
utl_http.end_response(l_http_resp);
-- Look for server-side error and report it.
elsif (l_http_resp.status_code >= 500) AND (l_http_resp.status_code <= 599) THEN
dbms_output.put_line('Check if the Web site is up.');
utl_http.end_response(l_http_resp);
RETURN;
END IF;
utl_http.end_response (l_http_resp);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line (sqlerrm);
raise;
END;
This procedure can be used to send any POST request to the URL specified as input parameter.
The set_slide procedure is specific for the slideshow channel and the local CometD configuration. However, it can easily be adopted for other environments, channels and message types.
PROCEDURE set_slide(
p_slidenumber IN NUMBER )
IS
l_url VARCHAR2(200) :='http://localhost:8085/slideshow/cometd';
l_json VARCHAR2(20000) := '[{"version":"1.0","minimumVersion":"0.9","channel":"/meta/handshake","supportedConnectionTypes":["long-polling","callback-polling"],"advice":{"timeout":60000,"interval":0},"id":"1"}]';
l_json1 VARCHAR2(20000) ;
l_response VARCHAR2(30000);
l_clientId VARCHAR2(30000);
BEGIN
post( p_url => l_url, p_message =>l_json, p_response => l_response);
l_clientId := SUBSTR(l_response, 156 ,instr( l_response, '"', 156)-156);
l_json1 := '[{"channel":"/slide/show","data":{"uniqueId":1326523938212,"slideNumber":'||p_slidenumber||'},"id":"40","clientId":"'|| l_clientId||'"}]';
post( p_url => l_url, p_message =>l_json1, p_response => l_response);
END;
Finally, it is simple now to drive a slideshow from within the database. Executing the following piece of PL/SQL will have all subscribed Web Clients dancing to the tune of the database, as they get the slidenumbers to present pushed from the CometD Servlet as instructed by the POST requests from the PL/SQL procedure:
BEGIN set_slide( p_slidenumber => 2); dbms_lock.sleep(3); set_slide( p_slidenumber => 4); dbms_lock.sleep(3); set_slide( p_slidenumber => 3); dbms_lock.sleep(3); set_slide( p_slidenumber => 5); dbms_lock.sleep(3); set_slide( p_slidenumber => 1); dbms_lock.sleep(3); END;
The result is:
Of course you would have to see it animated to really get excited.
Some of the output produced when executing the PL/SQL is shown here – representing to slide selection events being published:
status code: 200
reason phrase: OK
[{"id":"1","minimumVersion":"1.0","supportedConnectionTypes":["callback-polling","long-polling"],"successful":true,"channel":"/meta/handshake","clientId":"5711zev5nev6fg23jth4gpg2no3","version":"1.0"}]
response:5711zev5nev6fg23jth4gpg2no3
status code: 200
reason phrase: OK
[{"id":"40","successful":true,"channel":"/slide/show"}]
status code: 200
reason phrase: OK
[{"id":"1","minimumVersion":"1.0","supportedConnectionTypes":["callback-polling","long-polling"],"successful":true,"channel":"/meta/handshake","clientId":"5azb1iyw0mj5no19d0xjtezs5hu","version":"1.0"}]
response:5azb1iyw0mj5no19d0xjtezs5hu
status code: 200
reason phrase: OK
[{"id":"40","successful":true,"channel":"/slide/show"}]
Coordinating a slideshow is not necessarily the most useful thing to do with the ability to Push from Database to Web Clients. Offering live updates based on data manipulation in the database seems a more useful application of this facility. I am sure I will demonstrate something to that effect in the near future.
Resources
Download the PL/SQL code demonstrated in this article: postToCometFromPLSQL.zip.
No Responses