Creating a Vista Gadget isn't nearly as straightforward as I thought. In addition, the prospect of using a database to generate the content for it didn't seem possible at first. But, of course, technology knows no limits, and so I've put together this article to demonstrate how to do just that. The code below walks through the set-up of a basic gadget. Then, it adds the database-driven components to have it pull data and display 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 "Customers" table.
A Windows Vista Sidebar 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.
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 SidebarDemo "%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=SidebarDemo;UID=dba;PWD=sql" setup.sql
Finally, double-click the "sqlanywhere10.gadget" file to install and load the demo gadget.
Creating a Basic SideBar Gadget
The Manifest File
First, you'll need to set up your manifest XML file, which basically just describes the gadget for the SideBar and provides information for users to see before they install your gadget. The manifest file MUST be named "gadget.xml".
Here's everything you need for the
gadget.xml file. Notice that we're linking to
gadget.html from the
<host><base src="..."> tag.
<name>Your Gadget Title Here</name>
<author name="Your Name Here">
<info url="www.YourWebSiteHere.com" />
<copyright>Your Copyright Here</copyright>
<description>Your Description Here.</description>
<base type="HTML" apiVersion="1.0.0" src="gadget.html" />
<platform minPlatformVersion="1.0" />
The HTML File
<title>SQL Anywhere 10 Sidebar Demo</title>
<link type="text/css" rel="Stylesheet" href="gadget.css" />
<div class="title">Database Stats</div>
<div class="title">Customer List</div>
<img id="upButton" src="img/buttonUp_Off.png"
alt="Previous Page" class="button" onclick="pageUp()" />
<img id="downButton" src="img/buttonDown_Off.png"
alt="Next Page" class="button" onclick="pageDown()" />
The key things to notice are:
- The link to the "gadget.css" stylesheet,
<link type="text/css" rel="Stylesheet" href="gadget.css" />.
- The use of the
<g:background src="img/gadget.png"> tag to set "gadget.png" as the background.
Next, you'll want to take a look at the CSS formatting. I'll go through just the important blocks for this one:
body is where you define the width and height of your gadget. These are REQUIRED for the gadget to display properly. You should also set the margins to 0 so that you don't have any querky borders.
margin: 0 0 0 0;
content class is where you define the margins for the content of your gadget. This is only really useful if you are using a background image.
You may wonder why I have this content div, and didn't simply place the margins in the body. This is because the background is a tag, which will also be bound by the margins. Thus, the content should sit on-top of the background with specific margins.
The global variables explained:
var numpages = 0;
var currentpage = 0;
var resultsperpage = 4;
onload="loadMain()" is set in the HTML file, it will call this function when the gadget loads.
This will call both display functions. I'll go over just the database stats function, since the database data function is nearly identical. The following function will be called. Notice the
var url = "http://localhost:8888/SidebarDemoStats";
statsRequest = new ActiveXObject("Microsoft.XMLHTTP");
statsRequest.onreadystatechange = writeStats;
statsRequest.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
statsRequest.open() to true. This will keep the request object running in non-blocking asynchronous mode. You'll see that
writeStats() is specified as the function to be called when the state of the request has changed.
if(statsRequest.readyState == 4)
if(statsRequest.status == 200)
document.getElementById('stats').innerHTML = "<table id='statsTable'
class='data'>" + statsRequest.responseText + "</table>";
Once the request status is "OK", then all you need to is set the innerHTML to the value of the requested HTML.
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,
setup.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"."GetSidebarDemoData"()
RESULT (html_doc XML)
DECLARE datacursor CURSOR FOR SELECT "CompanyName" FROM "GROUPO"."Customers"
ORDER BY "CompanyName";
DECLARE html LONG VARCHAR;
DECLARE company LONG VARCHAR;
SET html = '';
CALL dbo.sa_set_http_header( 'Content-Type', 'text/html' );
IF SQLCODE <> 0 THEN LEAVE lp END IF;
FETCH NEXT datacursor INTO company;
SET html = HTML_DECODE( XMLCONCAT( html, '<tr><td>' + company + '</tr></td>' ) );
SELECT HTML_DECODE( XMLCONCAT(html) );
All the script does is create a stored procedure. In your procedure, open a
cursor to read through your
SELECT statement. While iterating through the cursor, use the
XMLCONCAT procedure to return your results as an XML (or in this case HTML) tag. Additionally, use the
HTML_DECODE procedure to clean-up any non-HTML 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 "SidebarDemoData" TYPE 'RAW' AUTHORIZATION OFF USER "DBA" AS CALL
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 copy it to C:\Users\YourUserName\AppData\Local\Microsoft\Windows Sidebar\Gadgets.
Alternatively, just throw everything into a zip file, and rename the file to *.gadget. Double-clicking on it, will automatically install and load the gadget!
Points of Interest
Although the objective was just to create a database-driven Vista gadget, it's 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 at here.