Web Page SVG driven by a SQL Server feed

 

 

The XML SVG syntax offers a powerful and flexible graphics front end for                      webapplications. The normal means by which data changes are presented over the web involves a page refresh with updates incorporated server-side  from a database. This article aims to demonstrate how a graphic object on a web page can be updated client-side using data from a SQL Server database without having a page refresh.

Scenario

A web application is required which displays continually changing data in a dial. The data source is a SQL Server database in which data is being continuously updated. The dial needs to reflect these changes every couple of seconds in an uninterrupted manner and without having to refresh the web page.

Server Side

In this example a changing data state is provided by a SQL stored procedure which simply outputs a random integer as XML. Here, RAW is required as no table is accessed.

CREATE sp_Data AS
SELECT CAST(RAND()*100 AS INT) AS dat FOR XML RAW
-- must have RAW as no table is accessed
GO
-- returns <row dat='xx'/>

First use Windows Explorer to create a subdirectory under the root of the appropriate web. This will be used as a target for a new virtual directory in the web.

The data from SQL is to be retrieved using the Javascript getURL() function (more of which later) and hence will need to be accessed via a URL. This is made possible by creating a virtual directory for SQL Server within the same web that the page resides using the mmc snapin Virtual Directory Management for SQL Server.

Start/Programs/Microsoft SQL Server/Configure SQL XML Support in IIS.

Select the appropriate web in the left hand pane and then right click the right hand pane and select New/Virtual Directory.

The subsequent dialog box has six tabs. On the General Tab enter a Name for this Virtual Directory e.g. WebServ. Also select the physical path to the virtual directory.


On the security tab enter the SQL Login and password for the database to be used.

On the Data Source Tab select the Server name and Database name e.g (local) and svc.

On the settings tab ensure that Allow URL queries is checked.

Click OK, confirm the login password and URL access is complete.

Client Side

Most of the work is in the web page, constructing the SVG graphic, obtaining the data from SQL and animating the needle.

The SVG graphic is defined in a separate file say svg1.svg saved in the includes subdirectory of the web and is embedded in a web page using the embed tag:

<EMBED id=svg1 style="left:0px;top:0px;" type=image/svg+xml height=200 width=200 src="/includes/svg1.svg">

The SVG file is an XML file containing the graphics definition and a javascript script block. Its content is shown below.

The header section comprises the standard XML header for SVG files whilst the SVG document itself is defined between the <svg> and </svg> tags.

<?xml version="1.0" encoding="UTF-8"?>
<svg width="200" height="200" xmlns="http://www.w3.org/2000/svg">

The dial graphic is constructed by defining a needle symbol and including it in a definition block. It is not displayed at this point.

<defs>
<symbol id="needle" viewbox="0,0,200,200" style="stroke:black;">
<polygon points="100,100,95,95,100,45,105,95"/>
</symbol>
</defs>

The background to the dial is a light grey 200 by 200 box with a 5 pixel diameter yellow circle marking the centre point.

<rect id="rec" x="0" y="0" width="200" height="200" style="fill:lightgrey">
<circle cx="100" cy="100" r="5" style="stroke:black;fill:yellow"/>
<path d="M20,100A80,0,0,1,180,100"
style="strokelightblue;stroke-width:10;fill:lightgrey;"/>

The path tag provides for constructing the light blue elliptic arc. The parameters are start position (20,100), x and y radii of the ellipse, here both 80 so it is a circle. The next parameter is the rotation of the x axis which is 0. The next parameter indicates whether the larger or smaller of the two arcs thus defined is chosen, 1 for arcs>180 and 0 for arcs <180.The next parameter defines the sweep direction, 0 if the arc is to be drawn in the -ve angle direction or 1 if the +ve direction. Finally the last two parameters define the end position of the arc.

The needle is drawn on the dial with the use tag.

<use xlink:href="#needle" style="fill:green" id="N1">

Animation of the needle is done using javascript. The functions are included in a script block defined as follows:

<script type="text/ecmascript">
<![CDATA[
.
.
.
//]]>
</script>

Code within the script block accesses elements of the SVG DOM through a global reference to the document.

var svgDoc=window.svgDocument;

Data is retieved from SQL server via a URL query with the getURL() function which takes two parameters, the URL and a callback function which is fired when the request for data returns. Note that the virtual directory must be on the same web from which the page is served. Also if the URL to the web and the virtual directory differs in that one uses an explicit IP address whereas the other uses DNS then the call for data will return a security violation eror.

var sFileName="http://svc/WebServ/?sql=exec+sp_Data";
getURL(sFileName,moveNeedle);

The data returned is an XML string containing a number here interpreted as the angle of rotation. If this was a value within a range represented on the dial then the angle could easily be mapped through a formula of the type x*(max-min)/180.

Tha data object of the callback function provides the status of the fetch as well as the data itself. If data is returned (data.success==true), the numerical value is extracted from the XML using a regular expression match otherwise an error is reported.

if(data.success){
var dat=new String();
dat = data.content;
dat=dat.match(/\d{1,2}/);
}
else{
alert("Load failed");
}

The needle is rotated by resetting the transform attribute of its placement on the dial (N1) to a rotation of the given amount.

var needle=svgDocument.getElementByID("N1");
sTransform="rotate(" + dat + ",100,100)";
needle.setAttribute("transform",sTransform);

A javascript timer is set up to fire the data retrieval on a regular basis thus providing for continuous update of the dial.

Setup

Server - Microsoft SQL Server 2000 sp3

Client - IE 6.0 (5.5 is OK) / MSXML 4.0 / ADOBE SVG Viewer