Introduction
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.
Background
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
A gadget is nothing more than a micro-sized HTML page. The fancy appearance is made possible through transparent images (PNG or GIF), the styling through CSS, and the functionality through JavaScript. Microsoft goes into a bit of detail on how to get started here. But, I'll just recapitulate the basics of what you need.
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.
="1.0"="utf-8"
<gadget>
<name>Your Gadget Title Here</name>
<namespace>windows.sdk</namespace>
<version>1.0.0.0</version>
<author name="Your Name Here">
<info url="www.YourWebSiteHere.com" />
</author>
<copyright>Your Copyright Here</copyright>
<description>Your Description Here.</description>
<hosts>
<host name="sidebar">
<base type="HTML" apiVersion="1.0.0" src="gadget.html" />
<permissions>Full</permissions>
<platform minPlatformVersion="1.0" />
</host>
</hosts>
</gadget>
The HTML File
Next, you'll need to actually define the HTML file. The HTML formatting is achieved through the use of CSS, and the functionality implemented through the use of JavaScript. Keep in mind that Microsoft has created an XML namespace, "g", that exposes a few useful tags. Namely, the "background", "image", and "text" tags.
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>SQL Anywhere 10 Sidebar Demo</title>
<script type="text/javascript" src="gadget.js"></script>
<link type="text/css" rel="Stylesheet" href="gadget.css" />
</head>
<body onload="loadMain()">
<g:background src="img/gadget.png">
<div class="content">
<div class="title">Database Stats</div>
<div id="stats">
</div>
<div class="title">Customer List</div>
<div id="data">
</div>
<div class="nav">
<table class="navbar">
<tr>
<td>
<img id="upButton" src="img/buttonUp_Off.png"
alt="Previous Page" class="button" onclick="pageUp()" />
</td>
<td id="page">
0/0
</td>
<td>
<img id="downButton" src="img/buttonDown_Off.png"
alt="Next Page" class="button" onclick="pageDown()" />
</td>
</tr>
</table>
</div>
</div>
</g:background>
</body>
</html>
The key things to notice are:
- The link to the "gadget.css" stylesheet,
<link type="text/css" rel="Stylesheet" href="gadget.css" />. - The link to the gadget.js JavaScript file,
<script type="text/javascript" src="gadget.js"></script>. - The use of the
<g:background src="img/gadget.png"> tag to set "gadget.png" as the background. - The three calls to JavaScript functions:
onload="loadMain()", onclick="pageUp()", and onclick="pageDown()".
Next, you'll want to take a look at the CSS formatting. I'll go through just the important blocks for this one:
The 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.
body
{
margin: 0 0 0 0;
width: 120px;
height: 240px;
}
The 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.
Important Note
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.
.content
{
margin-top: 20px;
margin-left: 5px;
margin-right: 5px;
margin-bottom: 25px;
}
Finally, we'll finish off with the JavaScript file. This is the what gives the gadget functionality. I will only go over the code that is used for actually retrieving the data from the database.
The global variables explained:
var statsRequest;
var dataRequest;
var numpages = 0;
var currentpage = 0;
var resultsperpage = 4;
Because the onload="loadMain()" is set in the HTML file, it will call this function when the gadget loads.
function loadMain()
{
displayStats();
displayData();
}
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 setTimeout("displayStats()", 500); call which causes this function to be recalled automatically every 500ms. This is how to achieve the auto-refreshing for the gadget. JavaScript will keep calling this function every 500 ms. You can make this refresh time as long or short as you like.
function displayStats()
{
var url = "http://localhost:8888/SidebarDemoStats";
statsRequest = new ActiveXObject("Microsoft.XMLHTTP");
statsRequest.onreadystatechange = writeStats;
statsRequest.open("GET", url, true);
statsRequest.send(null);
setTimeout("displayStats()", 500);
}
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.
function writeStats()
{
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
The JavaScript will try to pull the data from a specfied URL as shown above. Therefore, you need to make sure that there actually is some HTML being returned when the gadget submits an HTTP request to that URL. Thankfully, SQL Anywhere 10 does all the work for you, so all you have to do is write out some SQL statements to return the data, and it will take care of the rest.
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)
BEGIN
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' );
OPEN datacursor;
lp: LOOP
IF SQLCODE <> 0 THEN LEAVE lp END IF;
FETCH NEXT datacursor INTO company;
SET html = HTML_DECODE( XMLCONCAT( html, '<tr><td>' + company + '</tr></td>' ) );
END LOOP;
CLOSE datacursor;
SELECT HTML_DECODE( XMLCONCAT(html) );
END
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
GetSidebarDemoData();
Commit your changes. All you need to do now is deploy the gadget.
Deploying Your Gadget
The first thing to do is to make sure that your database is running with an HTTP listener. You can do this by specifying the following startup options when running your SQL Anywhere 10 database. You can set the port to whatever you like, just make sure that it corresponds to the same port used in the URL you specified earlier in the JavaScript file.
> dbeng10.exe yourdb.db -xs http(port=8888)
The -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.
