DOWNLOAD Oracle Database Sample code

TAGS database, google, All

Creating Oracle Database-backed iGoogle Gadgets

By Luca Mearelli

Learn how to build gadgets for iGoogle and OpenSocial containers that read data from an Oracle database.

Published April 2009

Gadgets within personalized home pages are an interesting channel to distribute information and extend the reach of Web applications. As we work more and more on "extended enterprise" or enterprise 2.0 applications, enterprise developers are looking for new ways to reach our users, and to integrate within the flow of information that the user is otherwise receiving.

In this article you'll learn how to use an Oracle database (with its native support for HTTP and XML) as a backend for a Google gadget. We'll do this while building a few simple gadgets that pull data from the database, and using Oracle XML DB to host associated static files. No prior knowledge of the Google gadgets specification is needed and all of the examples have been tested on Oracle Database XE (and will probably run on Oracle Database 11g as well).

What is a "Gadget"?

A gadget is a small software program living inside a gadget container that can be a personalized start page but also the profile page on a social network. Often many gadgets live side by side on the same page. I'll use iGoogle (the Google personalized home page) as the gadget container but most of this article applies to all the containers implementing the OpenSocial specification.

On this page you'll find a list of all the containers implementing the OpenSocial API. There is also an open source reference implementation: Shinding (which could be installed inside a firewall to provide intranet-wide start pages).

Note: at the time of this writing, the OpenSocial API has not been released on all the Google properties where gadgets can be hosted; some still use the so-called legacy API. Nonetheless all of the principles and most of the code shown here will apply to the legacy API with a few changed to the code (usually just changing the gadgets.* namespace to the old _IG_* namespace).

Structure of a Gadget

A gadget at its core is just an XML file which contains both a specification of the gadget requirements, its interface, and the Javascript code implementing its behavior. Let's look at a basic hello world gadget that we may install on our home page.

<?xml version="1.0" encoding="UTF-8" ?>
<Module>
  <ModulePrefs title="Hello World!">
    <Require feature="opensocial-0.8" />
  </ModulePrefs>
  <Content type="html">
    
      Hello, world! ( from Oracle XDB :-) )
    
  </Content>
</Module>

Development and Deployment

To develop and test a gadget you just need a text editor to write the xml file and a public place to host it. We'll later show how to host even the static files on Oracle but for now you can just put it anywhere it fits (e.g. on Google Pages).

To test the gadget you can signup with iGoogle and enable your home page to use the iGoogle sandbox. After enabling the sandbox you can add a few useful gadgets developer tools which will help you to test your gadgets. Just go here and click on the Add developer tools link.

To add the hello world gadget to your page just save the XML in a file on a Web server and enter its URL into the developer gadget box (and click Add). While developing it's useful to disable caching for the gadget as otherwise you would not be able to immediately see the changes you make to the XML file.

XML Descriptor File

Let's see in more details the Hello World example. All of the gadget is enclosed in the Module tag and using the ModulePrefs tag you can specify the title which will appear at the top of the gadget box. Inside the ModulePrefs you can describe which features will be needed for the gadget to run.

In the hello world example we are requiring just the core OpenSocial API. There are various different features that we can require e.g. the ability to set preferences at runtime or the ability to dynamically resize the gadget.

<Preload href="http://host/a/remote/resource" /> 

All of the different options and tags that can be used in the gadget descriptor can be found in the gadgets reference guide (see the links at the bottom).

Javascript APIs

Apart from the static HTML and XML all of the gadget development will be done using Javascript. OpenSocial implements a rich Javascript API enabling our gadgets to do things like interact with external services or download content, manage user preferences, and build complex interfaces.

Let's look at two basic APIs that will be useful in almost any gadget that we'll build. The first one is the MiniMessage feature that enables our gadget to show a small message to the user. We can use these to show an alert to the user for an error to update the interface to show a status message.

<?xml version="1.0" encoding="UTF-8" ?>
<Module>
  <ModulePrefs title="Hello World (MiniMessage version)">
    <Require feature="opensocial-0.8" />
    <Require feature="minimessage"/>
  </ModulePrefs>
  <Content type="html">
    <![CDATA[
      Hello, world!
	<script>
		var msg = new gadgets.MiniMessage(__MODULE_ID__);
		var statusMsg = msg.createDismissibleMessage("Hello from Oracle XDB :-)");
		// Change the message's background color to red
		statusMsg.style.backgroundColor = "red";
		statusMsg.style.color = "white";
	</script>
    
  </Content>
</Module>

• disappear automatically after a few seconds, or
• present a link to be manually closed, or
• stay on the screen until they are programmatically removed.

One of the most important features available to gadgets is the ability to request information from different hosts using the HTTP protocol. We can use this to build interfaces to remote services and data, integrating various data sources directly on the user's home page.

The next example shows how to request a simple file from a remote host.

<?xml version="1.0" encoding="UTF-8" ?>
<Module>
  <ModulePrefs title="Hello World (remote version)">
    <Require feature="opensocial-0.8" />
    <Require feature="minimessage"/>
  </ModulePrefs>
  <Content type="html">
    <![CDATA[
    	Hello, world!
      <script>

		function onResponse(data) {
			var msg = new gadgets.MiniMessage(__MODULE_ID__);
			var statusMsg = msg.createDismissibleMessage(data.text);
		}
		function makeRequest() {
		  var params = {};
		  params[gadgets.io.RequestParameters.CONTENT_TYPE] = gadgets.io.ContentType.TEXT;
		    gadgets.io.makeRequest('http://my_server:8080/igoogle/hello.txt', onResponse, params);
		}

		gadgets.util.registerOnLoadHandler(makeRequest);
      </script>
    
  </Content>
</Module>	

{
  data : <parsed data, if applicable>,
  errors : <any errors that occurred>,
  text : <raw text of the response>
}

The HTTP method to use for making the remote request can be specified as well and, if the remote server supports it, requests can be signed or made to use the OAuth authorization delegation protocol. Using OAuth it's possible for a gadget to securely get authorization from a user to access his/her own data hosted on a third party service. (This subject is beyond the scope of this article but here you'll find some relevant information about using OAuth within a gadget.)

Building the Gadget

Now you'll learn how to use the database built in HTTP listener to serve both the XML static files for the gadgets and to build the dynamic responses for the gadgets querying the data.

Serving the XML

Since the introduction of Oracle XML DB, Oracle has been able to directly serve content over the HTTP protocol acting as a repository for Web clients. This gives us a file repository that has all the properties of the database (with respect to the backup, security, scalability etc.) while making easy to manage the content using common tools (e.g. text editors) as it is also possible to enable access through the FTP and WebDAV protocols.

To serve the XML we'll create a directory to hold our gadget files, to do so we need to login to the database with a user holding the xdbadmin role and then use the createFolder function of the dbms_xdb package.

The HTTP listener is enabled by default on Oracle Database XE listening on port 8080 (otherwise we'd have to use the dbms_xdb.setListenerEndPoint to enable it), but it's accessible only from the localhost, so we need to enable external access to the HTTP listener. This is just a matter of calling the setListenerLocalAccess stored with a FALSE parameter.

DECLARE
	v_return BOOLEAN;
BEGIN
	-- Create the igoogle folder 
	v_return := dbms_xdb.createFolder('/igoogle/');
	
	-- enable remote access for the HTTP listener
	dbms_xdb.setListenerLocalAccess(l_access => FALSE);
	
	COMMIT;
END;
/

-- make the changes apply immediately
ALTER SYSTEM REGISTER;

To test this setup just copy the example files over the shared igoogle folder and add them to your iGoogle page using the URL: http://<server>:<port>/igoogle/<example file name>.xml .

Querying a Data Source

It's now time to look at how to hook up a gadget to a data source serving information extracted from the database.

The database as a direct source for the information gives us the flexibility to manage it with the tools we are using for the rest of the data management tasks; for instance the permissions can be finely tuned by separating the database user in charge of the http data access from the schema holding the actual data.

We'll create an IG user specific for the iGoogle gadget and give it only the read access to the data (using the HR demo schema in the examples). All the PL/SQL code and queries invoked from the http listener in response to the gadget requests will be executed with the IG user permissions, and thus the gadget will not be able to change data in the database, just to read it. We don’t show the code here but it’s in the sample code download for this article.

Another important element of the Oracle Database since 10g Release 2 is the embedded PL/SQL gateway which permits the creation of web applications using PL/SQL. It offers a way to call stored procedures from an HTTP client and has a package that make it easy to generate and send back HTML (or other content).

The DBMS_EPG package can be used to administer the embedded PL/SQL gateway.

We'll define a Database Access Descriptor (DAD) which will pass the HTTP requests to its associated virtual path to the stored procedures of the IG schema.

-- create the DAD with an associated virtual path
BEGIN
  DBMS_EPG.create_dad (
    dad_name => 'igoogle/apps',
    path     => '/igoogle/apps/*');
END;
/

-- set the DAD database username to be used 
-- this avoids requiring the user / password 
-- via HTTP BasicAuth
BEGIN
  DBMS_EPG.set_dad_attribute (
    dad_name   => 'igoogle/apps',
    attr_name  => 'database-username',
    attr_value => 'IG');
END;
/

-- enable access to the specified schema via the DAD
BEGIN
  DBMS_EPG.authorize_dad (
    dad_name => 'igoogle/apps',
    user     => 'IG');
END;
/

To test it create the following stored returning the string Hello world!:

CREATE OR REPLACE PROCEDURE ig.hello_world IS
BEGIN
  htp.print('Hello world!');
END hello_world;
/
SHOW ERRORS

Sending Data to the Client

As you have seen, a gadget can use data provided in various formats from a remote server, with the XML and JSON data being the most useful. Within a stored procedure it's really easy to generate XML data from an arbitrary query using the DBMS_XMLGEN package, here is an example that queries the regions table inside the HR schema and returns the data to the client:

procedure regions is
	l_ctx    dbms_xmlgen.ctxHandle;
	l_xml    clob;
begin

  l_ctx := dbms_xmlgen.newContext('select * from hr.regions');
  l_xml := dbms_xmlgen.getXML(l_ctx);
  ig.prn_clob(l_xml);
  dbms_xmlgen.closecontext(l_ctx);

end regions;

create or replace procedure ig.prn_clob (p_clob in clob)
as
	offset number := 1;
	amount number := 4000;
	len number := dbms_lob.getlength (p_clob);
	lc_buffer varchar2 (4000);
begin
	while (offset < len) loop
		dbms_lob.read (p_clob, amount, offset, lc_buffer);
		htp.prn (lc_buffer);
		offset := offset + amount;
	end loop;
end prn_clob;

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <REGION_ID>1</REGION_ID>
  <REGION_NAME>Europe</REGION_NAME>
 </ROW>
 <ROW>
  <REGION_ID>2</REGION_ID>
  <REGION_NAME>Americas</REGION_NAME>
 </ROW>
 <ROW>
  <REGION_ID>3</REGION_ID>
  <REGION_NAME>Asia</REGION_NAME>
 </ROW>
 <ROW>
  <REGION_ID>4</REGION_ID>
  <REGION_NAME>Middle East and Africa</REGION_NAME>
 </ROW>
</ROWSET>

<?xml version="1.0" encoding="UTF-8" ?>
<Module>
  <ModulePrefs title="Show the regions">
    <Require feature="opensocial-0.8" />
    <Require feature="minimessage"/>
    <Require feature="dynamic-height"/>
  </ModulePrefs>
  <Content type="html">
    <![CDATA[
	  <div id='regions'></div>
	  <div id='reload' style="display:none;"><a href='javascript:void(0);' onclick="init(); return false;">Click here to reload</div>
      <script>
		var regionsList;
		var msg = new gadgets.MiniMessage(__MODULE_ID__);
		var statusMsg;

		function noDataFound(errors) {
			if (errors && errors.length) {
			    statusMsg = msg.createTimerMessage("Error getting data: "+errors.join(", "), 5);
			    statusMsg.style.backgroundColor = "red";
			    statusMsg.style.color = "white";				
			} else {
			    statusMsg = msg. createDismissibleMessage("No data found.");
			}
		}
		
		function onRegions(data) {
		    msg.dismissMessage(statusMsg);
		    if (data.data) {
		        regionsList = data.data.getElementsByTagName("ROW");

		    } else {
				noDataFound(data.errors);
		        document.getElementById('reload').style.display = "block";
		        return;
		    }

		    var html = new Array();
		    html.push('<i>Regions in the database:</i><br/>')
		    html.push('<ul>');
		    for (var i = 0; i < regionsList.length; i++) {
		        html.push('<li ');

		        var region_id = regionsList[i].getElementsByTagName("REGION_ID");
		        if (region_id && region_id.length) {
		            html.push('id="region_', region_id[0].textContent, '"');
		        }

		        html.push('>');

		        var region_name = regionsList[i].getElementsByTagName("REGION_NAME");
		        if (region_name && region_name.length) {
		            html.push(region_name[0].textContent);
		        }

		        html.push('</li>');
		    }
		    html.push('</ul>');
		    document.getElementById('regions').innerHTML = html.join('');
		    gadgets.window.adjustHeight();
		}
		function requestRegions() {
		    var params = {};
		    params[gadgets.io.RequestParameters.CONTENT_TYPE] = gadgets.io.ContentType.DOM;
		    gadgets.io.makeRequest('http://my_server:8080/igoogle/apps/hrgadget.regions', onRegions, params);
		}

		function init() {
		    statusMsg = msg.createStaticMessage("Loading the regions");
		    document.getElementById('regions').innerHTML = '';
		    document.getElementById('reload').style.display = "none";
		    requestRegions();
		}

		gadgets.util.registerOnLoadHandler(init);
      </script>
    
  </Content>
</Module>

Generating JSON Replies

One thing that shows clearly from the example is that using the default XML format, while being easy to generate from the server, requires a rather verbose code to be handled inside the gadget. But we may solve this problem easily by generating a JSON answer in the stored procedure and making a gadgets.io.ContentType.JSON request from the gadget.

Luckily the DBMS_XMLGEN package has a very simple way to modify the XML returned using an XSL sheet, while this is often used to translate an XML format into another XML format we can use it also to build the JSON message from the XML.

To do so it's sufficient to use the setXslt procedure passing to it the DBMS_XMLGEN context generated for the query and the XSL sheet as an XMLType object. The getXML function will thus return the XML transformed by the XSL sheet.

procedure regions_json is
	l_ctx    dbms_xmlgen.ctxHandle;
	l_xml    clob;
	l_xsl XMLType := XMLType(q'[
	<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	  	<xsl:output omit-xml-declaration="yes" method="text" encoding="UTF-8" media-type="text/x-json" />
	  	<xsl:template match="/">
	  	{ "regions" : [
  			<xsl:for-each select="ROWSET/ROW">
            <xsl:if test="position() > 1">,</xsl:if>
			{ "id" : "<xsl:value-of select="REGION_ID"/>",
			  "name" : "<xsl:value-of select="REGION_NAME"/>"
			}
	  		</xsl:for-each>
		  ]
		 }
	  	</xsl:template>
	</xsl:stylesheet>]'
	);

	l_output  XMLType;

	l_xml_data XMLType;
begin

  l_ctx := dbms_xmlgen.newContext('select * from hr.regions');
  dbms_xmlgen.setnullhandling(l_ctx, 1);

  dbms_xmlgen.setxslt(l_ctx, l_xsl);
  l_xml := dbms_xmlgen.getXML(l_ctx);

  OWA_UTIL.mime_header ('text/x-json', FALSE);
  htp.p('Cache-Control: no-cache');
  htp.p('Pragma: no-cache');
  OWA_UTIL.http_header_close;
  ig.prn_clob(dbms_xmlgen.convert(l_xml,1));
  dbms_xmlgen.closecontext(l_ctx);

end regions_json;

		
{ "regions" : [  			
	{ "id" : "1",
	  "name" : "Europe"
	},
	{ "id" : "2",
	  "name" : "Americas"
	},
	{ "id" : "3",
	  "name" : "Asia"
	},
	{ "id" : "4",
	  "name" : "Middle East and Africa"
	}
  ] 		 
}

<?xml version="1.0" encoding="UTF-8" ?>
<Module>
  <ModulePrefs title="Show the regions (json version)">
    <Require feature="opensocial-0.8" />
    <Require feature="minimessage"/>
  </ModulePrefs>
  <Content type="html">
    <![CDATA[
	    <div id='main'>
	      <i>Regions in the database:</i>
	      <div id='regions'</div>
		  <div id='reload' style="display:none;"><a href='javascript:void(0);' onclick="init(); return false;">Click here to reload</div>
	    </div>
      <script>
		var regionsList = {};
		var msg = new gadgets.MiniMessage(__MODULE_ID__);
		var statusMsg;

		function noDataFound(errors) {
			if (errors && errors.length) {
			    statusMsg = msg.createTimerMessage("Error getting data: "+errors.join(", "), 5);
			    statusMsg.style.backgroundColor = "red";
			    statusMsg.style.color = "white";				
			} else {
			    statusMsg = msg. createDismissibleMessage("No data found.");
			}
		}

		function onRegions(data) {
		    msg.dismissMessage(statusMsg);
		    if (data.data && data.data.regions && data.data.regions.length) {
		        regionsList = data.data.regions;
		    } else {
		        noDataFound(data.errors);
		        document.getElementById('reload').style.display = "block";
		        return;
		    }

		    var html = new Array();
		    html.push('<ul>');
		    for (var i = 0; i < regionsList.length; i++) {
		        html.push('<li id="region_', regionsList[i].id, '">', regionsList[i].name, '</li>');
		    }
		    html.push('</ul>');
		    document.getElementById('regions').innerHTML = html.join('');
		}
		function requestRegions() {
		    var params = {};
		    params[gadgets.io.RequestParameters.CONTENT_TYPE] = gadgets.io.ContentType.JSON;
		    gadgets.io.makeRequest('http://my_server:8080/igoogle/apps/hrgadget.regions_json', onRegions, params);
		}

		function init() {
		    statusMsg = msg.createStaticMessage("Loading the regions");
		    document.getElementById('regions').innerHTML = '';
		    document.getElementById('reload').style.display = "none";
		    requestRegions();
		}

		gadgets.util.registerOnLoadHandler(init);
      </script>
    
  </Content>
</Module>

Passing Parameters to the Queries

It is really simple to pass parameters to the stored procedures using the techniques described here. It's just a matter of creating a stored that accepts input parameters, each of its parameters will be a query parameter that should be passed in via HTTP. The following procedure reads the list of countries for a given region whose id is passed as the 'r' input parameter:

procedure countries_for_region_json(r in number) is
	l_ctx    dbms_xmlgen.ctxHandle;
	l_xml    clob;
	l_xsl XMLType := XMLType(q'[<?xml version="1.0" encoding="UTF-8" ?>
	<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	  	<xsl:output omit-xml-declaration="yes" method="text" encoding="UTF-8" media-type="text/x-json" />
	  	<xsl:template match="/">
		{ "countries" : [
	  		<xsl:for-each select="ROWSET/ROW">
        	<xsl:if test="position() > 1">,</xsl:if>
				{
					"id" : "<xsl:value-of select="COUNTRY_ID"/>",
					"name" : "<xsl:value-of select="COUNTRY_NAME"/>",
					"has_departments" : <xsl:choose><xsl:when test="DEP_CNT > 0">true</xsl:when><xsl:otherwise>false</xsl:otherwise></xsl:choose>
				}
	  		</xsl:for-each>
		] }
	  	</xsl:template>
	</xsl:stylesheet>]'
	);

	l_output  XMLType;

	l_xml_data XMLType;

	l_srf SYS_REFCURSOR;
begin

  open l_srf for
	select c.*, nvl(cd.dep_cnt,0) dep_cnt
	from hr.countries c
	left outer join
	( select count(*) dep_cnt, l.country_id
	  from hr.departments d
	  join hr.locations l on l.location_id = d.location_id
	  GROUP by l.country_id
	) cd on cd.country_id = c.country_id
	where c.region_id = r;

  l_ctx := dbms_xmlgen.newContext(l_srf);
  dbms_xmlgen.setnullhandling(l_ctx, 1);

  dbms_xmlgen.setxslt(l_ctx, l_xsl);
  l_xml := dbms_xmlgen.getXML(l_ctx);

  OWA_UTIL.mime_header ('text/x-json', FALSE);
  htp.p('Cache-Control: no-cache');
  htp.p('Pragma: no-cache');
  OWA_UTIL.http_header_close;
  ig.prn_clob(dbms_xmlgen.convert(l_xml,1));

  dbms_xmlgen.closecontext(l_ctx);

end countries_for_region_json;

http://<server>:<port>/igoogle/apps/hrgadget.countries_for_region_json?r=<region id>

<?xml version="1.0" encoding="UTF-8" ?>
<Module>
  <ModulePrefs title="From regions to countries">
    <Require feature="opensocial-0.8" />
    <Require feature="minimessage"/>
    <Require feature="dynamic-height"/>
  </ModulePrefs>
  <Content type="html">
    <![CDATA[
	  <div id='regions' style="display:none;"></div>
	  <div id='countries' style="display:none;"></div>
	  <div id='reload' style="display:none;"><a href='javascript:void(0);' onclick="requestRegions(); return false;">Click here to reload</div>
      <script>
		var regionsList;
		var msg = new gadgets.MiniMessage(__MODULE_ID__);
		var statusMsg;
		var current_region_id;

		function noDataFound(errors) {
			if (errors && errors.length) {
			    statusMsg = msg.createTimerMessage("Error getting data: "+errors.join(", "), 5);
			    statusMsg.style.backgroundColor = "red";
			    statusMsg.style.color = "white";				
			} else {
			    statusMsg = msg. createDismissibleMessage("No data found.");
			}
		}
		
		function show(what) {
		    if (what == 'countries') {
		        document.getElementById('regions').style.display = "none";
		        document.getElementById('reload').style.display = "none";
		        document.getElementById('countries').style.display = "block";
		    } else {
		        document.getElementById('regions').style.display = "block";
		        document.getElementById('reload').style.display = "none";
		        document.getElementById('countries').style.display = "none";

		    }
		}

		function onRegions(data) {
		    msg.dismissMessage(statusMsg);
		    
		    if (data.data && data.data.regions && data.data.regions.length) {
		        regionsList = data.data.regions;
		    } else {
		        noDataFound(data.errors);
		        document.getElementById('reload').style.display = "block";
		        return;
		    }

		    var html = new Array();
		    html.push('<i>Regions in the database:</i><br/><small>Click to on the name to load the countries in that region.</small>')
		    html.push('<ul>');
		    for (var i = 0; i < regionsList.length; i++) {
		        html.push('<li>');

		        html.push('<a href="javascript:void(0);" onclick="requestCountries(\'', regionsList[i].id ,'\'); return false;" ',
		                    ' id="region_', regionsList[i].id, '"', '>');
		        html.push(regionsList[i].name);
		        html.push('</a>');
		
		        html.push('</li>');
		    }
		    html.push('</ul>');
		    document.getElementById('regions').innerHTML = html.join('');
		    show('regions');
		    gadgets.window.adjustHeight();
		}
		function requestRegions() {
		    document.getElementById('regions').innerHTML = '';
		    statusMsg = msg.createStaticMessage("Loading the regions");
		    var params = {};
		    params[gadgets.io.RequestParameters.CONTENT_TYPE] = gadgets.io.ContentType.JSON;
		    gadgets.io.makeRequest('http://my_server:8080/igoogle/apps/hrgadget.regions_json', onRegions, params);
		}

		function onCountries(data) {
		    msg.dismissMessage(statusMsg);
		    
		if (data.data && data.data.countries && data.data.countries.length) {
		        countriesList = data.data.countries;
		    } else {
		        noDataFound(data.errors);
		        return;
		    }

		    var html = new Array();
		    html.push('<i>Countries in ', document.getElementById("region_" + current_region_id).innerHTML, ':</i>');
		    html.push('<br/><small>The bold ones indicate those where departments are located</small>');
		    html.push('<ul>');
		    for (var i = 0; i < countriesList.length; i++) {
		        html.push('<li id="region_', countriesList[i].id, '" >');

		        if (countriesList[i].has_departments) {
		            html.push('<b>');
		        }
	            html.push(countriesList[i].name);
		        if (countriesList[i].has_departments) {
		            html.push('</b>');
		        }

		        html.push('</li>');
		    }
		    html.push('</ul>');
		    html.push('<br/><a href="javascript:void(0);" onclick="show(\'regions\'); gadgets.window.adjustHeight(); return false;">← Regions</a>');
		    document.getElementById('countries').innerHTML = html.join('');
		    show('countries');
		    gadgets.window.adjustHeight();
		}

		function requestCountries(region_id) {
		    current_region_id = region_id;
		    document.getElementById('countries').innerHTML = '';
		    statusMsg = msg.createStaticMessage("Loading the countries");
		    var params = {};
		    params[gadgets.io.RequestParameters.CONTENT_TYPE] = gadgets.io.ContentType.JSON;
		    gadgets.io.makeRequest('http://my_server:8080/igoogle/apps/hrgadget.countries_for_region_json?r=' + region_id, onCountries, params);
		}

		gadgets.util.registerOnLoadHandler(requestRegions);
      </script>
    
  </Content>
</Module>

Managing User Preferences

Finally you'll learn how to combine user preferences with remote data services to pre-populate a gadget with information relevant for the user.

The gadget specification allows the definition of user preferences using the UserPref XML tag. The preferences set by the user are retained if the page reloads, so it's possible to customize a gadget for the specific user that is viewing it. It's possible to define various kinds of preferences: string, bool, enum, list (dynamically generated from user input) and hidden which is a string that is not visible or user editable.

If you need to store more complex kinds of preferences its advisable to store them as a serialized Javascript object (in the JSON format) using a hidden preferences, and this is exactly what we are doing in this example.

Here we request a list of departments from the database and ask the user to choose one of them if he/she has not yet made this choice. When the user has chosen the department we query the server for the list of employees working in that department and show the gadget user a list with their relevant information. We also store the department Javascript object inside a hidden preference (in a serialized JSON format), and if the preference is set whet the gadget page reloads we skip the department choice page.

<UserPref name="choosen_dept" datatype="hidden" />

var prefs = new gadgets.Prefs(__MODULE_ID__);
To save the preference programmatically we need to require the setprefs feature, then we are able to use the set method: 

prefs.set('choosen_dept',  gadgets.json.stringify(choosen_dept));

choosen_dept = gadgets.json.parse(gadgets.util.unescapeString(prefs.getString("choosen_dept")));
	
<?xml version="1.0" encoding="UTF-8" ?>
<Module>
  <ModulePrefs title="Employees"  height="300" >
    <Require feature="opensocial-0.8" />
    <Require feature="minimessage"/>
	<Require feature="setprefs"/>
	<Require feature="settitle"/>
    <Preload href="http://my_server:8080/igoogle/apps/hrgadget.departments_json" />
  </ModulePrefs>
  <UserPref name="choosen_dept" 
     datatype="hidden" />
  <Content type="html">
    <![CDATA[
	  <div id='departments' style="display:none;"></div>
	  <div id='employees' style="display:none;">		
		<div id='employees_list' style="width:100%;height:260px;overflow-y:scroll;"> </div>
		<hr/>
		<small id=''><a href="javascript:void(0);" onclick="loadDepartments();return false;">change department</a></small>
	  </div>
      <script>
	    // Get userprefs
	    var prefs = new gadgets.Prefs(__MODULE_ID__);
		var choosen_dept;
		var departmentsList;
		var msg = new gadgets.MiniMessage(__MODULE_ID__);
		var statusMsg;

		function noDataFound(errors) {
			if (errors && errors.length) {
			    statusMsg = msg.createTimerMessage("Error getting data: "+errors.join(", "), 5);
			    statusMsg.style.backgroundColor = "red";
			    statusMsg.style.color = "white";				
			} else {
			    statusMsg = msg. createDismissibleMessage("No data found.");
			}
		}
		function show(what) {
		    if (what == 'departments') {
		        document.getElementById('departments').style.display = "block";
		        document.getElementById('employees').style.display = "none";
		    } else if (what == 'employees') {
		        document.getElementById('departments').style.display = "none";
		        document.getElementById('employees').style.display = "block";
		    } else {
		        document.getElementById('departments').style.display = "none";
		        document.getElementById('employees').style.display = "none";			
			}
		}

		function onDepartments(data) {
		    msg.dismissMessage(statusMsg);
		    if (data.data && data.data.departments && data.data.departments.length) {
		        departmentsList = data.data.departments;
				var html = new Array();
				html.push("Choose a department: ")
				html.push("<select id=\"dept_select\">")
			    html.push('<option value=\"\"></option>');
				for (var i = 0; i < departmentsList.length; i++) {
			        html.push('<option value=\"',i,'\">');
					html.push(departmentsList[i].name);
					html.push('</option>');
			    }
			 	html.push("</select>");
				document.getElementById('departments').innerHTML = html.join('');
				document.getElementById('dept_select').onchange = chooseDepartment;
				show('departments');
		    } else {
		        noDataFound(data.errors);
		        return;
		    }
			
		}
		
		function loadDepartments() {
			statusMsg = msg.createStaticMessage("Loading data...");
			document.getElementById('departments').innerHTML = '';
		    var params = {};
		    params[gadgets.io.RequestParameters.CONTENT_TYPE] = gadgets.io.ContentType.JSON;
		    gadgets.io.makeRequest('http://my_server:8080/igoogle/apps/hrgadget.departments_json', onDepartments, params);	
				
		}
		
		function chooseDepartment() {
			var dept_idx = document.getElementById('dept_select').value;
			if (departmentsList && departmentsList[dept_idx]) {
				choosen_dept = departmentsList[dept_idx];
				prefs.set('choosen_dept', 
				          gadgets.json.stringify(choosen_dept));
			}
			loadEmployees();
		}
		
		function setChoosenDepartment() {
			try {
				choosen_dept = gadgets.json.parse(gadgets.util.unescapeString(prefs.getString("choosen_dept")));
			} catch (err) {
				choosen_dept = null;
			}
						
		}
		
		function onEmployees(data) {
		    msg.dismissMessage(statusMsg);
		    if (data.data && data.data.employees && data.data.employees.length) {
		        var employees = data.data.employees;
				var html = new Array();
				for (var i = 0; i < employees.length; i++) {
					html.push("<div class=\"emp\" id=\"emp_",employees[i].id,"\">")
							        html.push('<b>', employees[i].name, '</b>');
					html.push("<div style=\"font-size:10px;\">");
					html.push("<i>",employees[i].job_title,"</i><br/>");
					html.push("Phone: ", employees[i].phone_number, " - <a href=\"mailto:", employees[i].email,"\">send email</a><br/>");
					html.push("Managed by: ", employees[i].manager,"<br/>");
					html.push('</div>');
				 	html.push("</div>");
							    }
				document.getElementById('employees_list').innerHTML = html.join('');
				show('employees');
		    } else {
		        noDataFound(data.errors);
		        return;
		    }
			
		}
		function loadEmployees() {
			if (!choosen_dept) {
				init();
				return;
			}
			
			document.getElementById('employees_list').innerHTML = '';
			gadgets.window.setTitle("Employees of: "+choosen_dept.name);
			
			statusMsg = msg.createStaticMessage("Loading data...");
			var params = {};
		    params[gadgets.io.RequestParameters.CONTENT_TYPE] = gadgets.io.ContentType.JSON;
		    gadgets.io.makeRequest('http://my_server:8080/igoogle/apps/hrgadget.employees_json?d='+choosen_dept.id, onEmployees, params);	
			
		}
		function init() {
			setChoosenDepartment();
			
			if (choosen_dept) {
				loadEmployees();
			} else {
				loadDepartments();
			}			
		}

		gadgets.util.registerOnLoadHandler(init);
      </script>
    
  </Content>
</Module>

Conclusion

You should now have a basic understanding of what an iGoogle/OpenSocial gadget is and how it can be built to make use of an Oracle database as its data source. This can be extremely useful to extend the reach of our enterprise systems and create new ways for our users to interact with the data residing in the database.

You have learned how to fully host the gadgets on an Oracle database and how to use the database XML and HTTP features to build the data services needed to publish information in a gadget.

References

• Reference guide for the gadgets XML
• MiniMessage API reference
• Examples of the makeRequest function
• DBMS_EPG package doc
• DBMS_XMLGEN package doc

Luca Mearelli (l.mearelli@spazidigitali.com) is a specialist in Oracle and Web technologies (http://spazidigitali.com) based in Città di Castello, Italy.