This is pretty much the same kind of article that I wrote for creating a database-driven Vista gadget. The principles haven't changed, but there are several distinct differences. Sorry for any repetition between this and the last article, I just don't want people to have to read both articles if they're only interested in one of them. Basically, the objective is to create a gadget that pulls data from a database and displays it.
The database I used is SQL Anywhere 10, by Sybase iAnywhere. To access the database, the gadget makes HTTP requests, and this is easy to do with SQL Anywhere because it has a built-in HTTP server that allows SQL queries to be exposed as web services. You can download a free Developer Edition of SQL Anywhere from here.
The source code and explanations below will guide you through the creation of the demo gadget that is included with this article. Basically, the gadget pulls data from the sample database included with SQL Anywhere 10, and displays live database statistics as well as a list of data from the
A Google Desktop Gadget is a user-friendly, highly graphical utility or widget that allows users to see key information about running applications. Database-driven solutions can use gadgets to display important application information stored inside the database. For example, you can create a small notification gadget to notify users of certain database changes or gather database statistics for performance monitoring. The nice thing about it being a Google Desktop Gadget is that Google Desktop is available for Windows, Linux, and Mac. I haven't really tested this gadget out on Linux or Mac, but the code here is platform independent and should work without a problem. I'm also pretty sure that SQL Anywhere is available for those platforms too.
Running the Demo Gadget
To run the demo, first unzip the files to a folder.
Start up the SQL Anywhere 10 demo database, by running the following command:
> dbeng10 -n gadget "%SQLANYSAMP10%"\demo.db -xs http(port=8888)
Then, install the scripts and webservice by running the following command from the unzipped folder location:
> dbisql -c "ENG=gadget;UID=dba;PWD=sql" demo.sql
Finally, double-click the sqlanywhere10.gg file to install and load the demo gadget.
Creating a Basic Google Gadget
The Manifest File
First, you'll need to set up your manifest file, which basically just describes the gadget for the Google Desktop and provides information for users to see before they install your gadget. The manifest file MUST have a *.gmanifest extension.
Here's everything you need for the manifest file.
<name>Your Gadget Title Here</name>
<description>Your Gadget Description Here</description>
<aboutText>Your Gadget About Text Here</aboutText>
<author>Your Name Here</author>
<authorWebsite>Your Website Here</authorWebsite>
<id>A UUID Here</id>
<copyright>Your Copyright Here</copyright>
<authorEmail>Your Email Here</authorEmail>
The main.xml File
<view height="300" width="180" onopen="loadMain();">
<div height="300" name="content" opacity="225" width="180" x="0" y="0"
<div height="50" name="stats" width="170" x="5" y="50" autoscroll="true"
<label height="32" name="label1" width="180" x="0" y="0" align="center"
bold="true" color="#FFFFFF" size="12" valign="middle">SQL Anywhere 10</label>
<label height="18" name="label2" width="170" x="5" y="32"
bold="true" color="#FFFFFF" valign="bottom">Database Statistics</label>
<div height="154" name="data" width="170" x="5" y="118"
<label height="18" name="label3" width="170" x="5" y="100"
bold="true" color="#FFFFFF" valign="bottom">Customer Table Data</label>
<label height="16" name="page" width="56" x="64" y="277"
align="center" color="#FFFFFF">0 / 0 </label>
<button height="16" name="down" width="16" x="131" y="278"
<button height="16" name="up" width="16" x="35" y="278"
<script src="main.js" />
<script src="xml.js" />
The key things to notice are:
The main.js and xml.js Files
The global variables are explained:
var statsRequest; var dataRequest;
var maxDataItemsPerPage = 10; var currPage = -1; var numPages = 0;
onopen="loadMain()" is set in the main.xml file, it will call this function when the gadget loads.
var url = "http://localhost:8888/GadgetData";
dataRequest = new XMLHttpRequest();
dataRequest.onreadystatechange = writeData;
dataRequest.open("GET", url, true);
All that is needed is to create an XMLHTTP request which will retrieve the HTML output from the specified URL. To keep the gadget responsive under all circumstances, you'll want to set the third parameter of
true. This will keep the request object running in non-blocking asynchronous mode. You'll see that
writeData() is specified as the function to be called when the state of the request has changed.
if(dataRequest.readyState == 4)
if(dataRequest.status == 200)
var y = 3;
xml = new DataXml(dataRequest.responseXml);
items = xml.getItems("row");
for(var i = 0; i < items.length; i++)
element = data.appendElement("<label>" + items[i] + "</label>");
element.color = "#FFFFFF";
element.y = y;
y += 14;
Once the request status is "OK", then all you need to do is format the data and append it to the existing
You'll notice the creation of a
DataXml object, so I'll just go over that. You'll find the code in xml.js.
this.xml = xml;
DataXml.prototype.getItems = function(key)
var xml = this.xml;
var items = ;
var nodes = xml.getElementsByTagName(key);
for (var i = 0; i < nodes.length; i++)
This is essentially just a simple XML parser. The
getItems() member function returns an array of company names.
Creating a Web Service in the Database to Supply the HTML
Once you've connected to the "demo" database in a query editor, run the SQL script, demo.sql. I'll go over what the script is doing for the database data retrieval. The script to set up the database statistics retrieval part is nearly identical.
CREATE PROCEDURE "DBA"."GetGadgetData"()
RESULT (CompanyName LONG VARCHAR)
SELECT REPLACE(HTML_ENCODE("CompanyName"), ''', '''')
ORDER BY "CompanyName";
All the script does is create a stored procedure. Use the
HTML_ENCODE procedure to substitute any invalid XML symbols.
Finally, all you need to do is turn your stored procedure into a web service. This is done with a single SQL statement:
CREATE SERVICE "GadgetData" TYPE 'XML' AUTHORIZATION OFF USER "DBA" _
AS call GetGadgetData();
Commit your changes. All you need to do now is deploy the gadget.
Deploying Your Gadget
> dbeng10.exe yourdb.db -xs http(port=8888)
-xs http(port=8888) is the part that makes the magic happen. Basically, it tells the database engine to bind to port 8888 and listen for requests. When a request is received, a response is generated based on the associated SQL statements or stored procedure.
To get your gadget installed, simply double click on the *.gmanifest file.
Alternatively, just throw everything into a zip file, and rename the file to *.gg. Double-clicking on it will automatically install and load the gadget!
Points of Interest
Although the objective was just to create a database-driven Google gadget, it has become clear (to me anyways) that the web services feature of SQL Anywhere 10 provides developers with a powerful platform for database-enablement of any kind of application. I suggest reading up more on their product here.