Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Pseudo web service using xmlHTTP

By Leon Platt,

In my first article XML in 20 minutes, I disscused the basics of XML, identified key properties of the XML DOM and demonstrated the ability to query a XML DOM using XPATH.  I ended the article by creating a server-side Active X control which would query the titles table in the pubs sample database and return a list of books in XML format.  My Second article Client Side XML - ASP on Steroids , demonstrated the ability to use the XML DOM on the client-side browser. Using java script, I was able to bind the XML DOM to various form controls on an ASP page. I  then demonstrated the use of the DOM to browse through the list of books providing the ability to prune off book nodes from the DOM and save them as a new XML document. Perhaps the most interesting feature of the code was the ability to query the pubs database for a list of its books.  The query was initiated by the use on the client, the results were retrieved and displayed, all without ever reloading the ASP page.  And its possible with an object called xmlHTTP that is part of the Microsoft Parser version 3.0

Article 3 - "Pseudo web service using xmlHTTP" - Will delve into the mysterious xmlHTTP object and the ASP code that did the magic in article 2.  I've got to tell you that I really love using this object.  Basically what I like about it is that through xmlHTTP you can expose some or all methods of any ActiveX class to the client-side user.  What this means is you are giving the user the capability of calling your Backend Active X controls.  And the best part of it all is that the entire communication process takes place behind the scenes!  Using this object, I will enhance the previous code to extend the users ability to query for titles from the pub database. When the query is submitted, I will use xmlHTTP to send a command in xml format to the ASP listener page which will interpret the command and call a method of my VB activeX control. The VB control will then return a XML string with the query results to the ASP listener page which will then package it up in XML format and forward it back to the client. All of this will occur without reloading our initial ASP page. The user will never know what's going on under the hood.

This Article will enhance the BookViewer Application presented in Articles 1 and 2!  To maximize your leaning experience please download the code and try it out!

System Requirements:
Microsoft SQL Server with sample pubs database loaded
Microsoft I.E. 5.0+
Microsoft Parser version 3.0 (msxml3.dll)
Microsoft IIS - copy all ASP pages, style sheets, as well as the mybooks.xml file into a virtual directory
Place the files: bookview.asp, xml_receive.asp, mybooks.xml, default.css, and book.css in a virtual directory on your web server.
Register the WebClass.xmlcontrol on your web server (webclass.dll)
Set your sql servers name in the xml_receive.asp page
Set your sql server username and password in the webClass.xmlcontrol class

Lets Take a look at how the xmlHTTP object functions

CLIENT - SIDE:  Here is some code taken from the original bookViewer.asp file that uses the xmlHTTP object to retreive the query results of all books in the Pubs database:
var xmlhttp = new ActiveXObject("Msxml2.XMLHTTP"); //build the command to get my saved books
var xmlcmd='<?xml version="1.0"?><ENVELOPE><XMLCMD c1="gettitlesxml" p1="" p2="" p3=""></XMLCMD></ENVELOPE>'; 
xmlhttp.Open("POST","xml_receive.asp", false); //use http to send a command to xml_receive.asp 
xmlhttp.Send(xmlcmd); //send the xml command  

Line one: sets a reference to the xmlHTTP object

Line two: builds the XML that will be sent using the object.  Note: you don't have to send xml, you could send plain text if you want to.  The reason I use XML is that it makes it very easy to parse the command and the parameters 1 through 3. 

Line three: sets up the connection for the object.  the first parameter is type of http request.  This is most commonly either "GET" or "POST".  The second parameter is the url that the request will go to.  The third parameter is whether or not the request is asynchronous.  You may also see the object used with two additional parameters, one for username and one for password.  In my example above: I am using a HTTP POST request to a page called xml_receive.asp (which I call my listener page) and the request is synchronous (the entire request will be made before anything else occurs.  The code will not continue to execute).

Line four: just initiates the request as it has been setup in the previous line.  Note: the parameter I am sending is the xml command that was built in line two.  Note, since we are operating synchronously the code will now sit here and wait until the response is received.

A little background on the LISTENER PAGE (xml_receive.asp)

- I call this my listener page because basically all it does is sit on the web server and wait for xmlHTTP requests.  When a request is made to this page it does three things.  First - it loads the request into a XML DOM and parses out the command and parameters that were sent.  Second - based on the command it may call a method of the back-end Active X control and the back-end Active X control will usually return an XML string.  Third - it loads the return data into a XML DOM and then sends it back to the client using the response object.  The three steps will be demonstrated bellow:

 LISTENER PAGE (Step One - Parse the command):  here is some code taken from the original xml_receive.asp that processes the request above:  This code should be self explanatory.  Basically all it does is parse the xml that was sent to find the command, parameter1, parameter2, parameter3 and any additional XML that may have been sent as an additional parameter. 

    var doc = Server.CreateObject("Msxml2.DOMDocument");
    doc.load(Request); //load the xmlHTTP request into a XML DOM for parsing	
    var c1=doc.childNodes.item(1).childNodes.item(0).attributes.item(0).text; //command
    var p1=doc.childNodes.item(1).childNodes.item(0).attributes.item(1).text; //parameter 1
    var p2=doc.childNodes.item(1).childNodes.item(0).attributes.item(2).text; //parameter 2
    var p3=doc.childNodes.item(1).childNodes.item(0).attributes.item(3).text; //parameter 3
    if (doc.childNodes.item(1).childNodes.length==2) //check for passed xml
    var passedXML=''+doc.childNodes.item(1).childNodes.item(1).xml;
    var xmlreturn=''; //initialize return string
    var doc=null; //dont need the XML DOM anymore now that command and parametes are parsed

 LISTENER PAGE (Step Two - call the correct method of the back-end ActiveX control):   here is some code taken from the original xml_receive.asp that processes the command "gettitlesxml":  Note the command is processed by setting up a reference to a back-end ActiveX control and calling the method GetTitlesXML() which queries the Pubs database and returns the results in an XML format. 

//************* Function gettitlesxml ***********************
if (c1=="gettitlesxml") 
   resultsXML=true; //results object will load a xml string
   var objWC= Server.CreateObject("WebClass.xmlControl"); //instantiate vb control
   objWC.strServer=sqlServer; //which sql server
   xmlreturn=objWC.GetTitlesXML(); //vb function to get titles and return as xml string 
   var objWC=null; //destroy the vb control    

LISTENER PAGE (Step Three - return the results):  here is some code taken from the original xml_receive.asp that sends the response back to the client:  This involves creating a results DOM, loading the results into the DOM, checking to make sure the XML loaded correctly, and finally using the ASP Response object to send the DOM to the client.  Note the actual sending of the XML DOM is accomplished using the save method of the XML DOM.  Using the parameter Response causes the xml to be saved out to the Response stream. 

var result =  Server.CreateObject("Msxml2.DOMDocument"); //create the results XML DOM
if (resultsXML) //load a xml string
   result.loadXML(xmlreturn); //call loadXML to load a string into the DOM
   if (c1=="savemybooks"); //save the xml if necessary
if ( result.parseError.errorCode!=0 ) //error parsing document
   //return for errors
   xmlreturn='<?xml version="1.0"?><RESULTS error="true">FALSE</RESULTS>';
   result.loadXML(xmlreturn); //load the xml error string into the resutls object
//use ASP Response object to sent the response back to the client
Response.ContentType = "text/xml"; //we are sending xml back;//save the dom to the HTTPResponse object            
var result = null;

Back to the Client (bookViewer.asp)

Meanwhile back at the client our code is paitently waiting for the xmlHttp response.  And when it occurs it is loaded into a XML DOM where its data can then be bound to ASP form controls. 

objSallbooks.load(xmlhttp.responseXML); //put the response into a dom
var xmlhttp = null;
//check for errors in the response
if (objSallbooks.childNodes.item(1).childNodes.item(0).nodeValue=="FALSE") Snumberofbooks=0;
else Snumberofbooks=(objSallbooks.childNodes.item(1).childNodes.length); //set number of books
if (Snumberofbooks>0) 			display_book(0,1); //display the first book 			    

Line one: loads the xmlHttp response into the XML DOM objSallBooks using the load method since we had set the content type of the response to be XML.  Note, the xmlHTTP object also has a responseText method to retreive the response as plain text.

Line two: at this point we have our answer in a XML DOM so we don't need the xmlHTTP object anymore.

Line three:  If there was an error then the listener page would send back the following string: <?xml version="1.0"?><RESULTS error="true">FALSE</RESULTS>'

Line four: otherwise we have the titles in the DOM and we can find out how many there are using the length method of the roots childnode.

Line five: if we have books in the DOM then bind the data to the forms controls for the first one in the list.

Now lets add some additional funcionality to the gettitlesxml command

Changes to the BookViewer.ASP code:   With a few simple changes to our previous code, the gettitlesxml command can be modified to allow the user to query for books based on type,price or year to date sales.  The code bellow shows the changes necessary:  Note, the three query fields also had to be changed from readonly textboxes to regular textboxes.

var xmlhttp = new ActiveXObject("Msxml2.XMLHTTP");
var where= ""; 
if(document.frmFields.Stype.value.length>0) where=where + " type= '"+document.frmFields.Stype.value+"' AND "; 
if(document.frmFields.Sprice.value.length>0) where=where + " price<= "+document.frmFields.Sprice.value+" AND "; 
if(document.frmFields.Ssales.value.length>0) where=where + " ytd_sales>= "+document.frmFields.Ssales.value; 
if(where.length>0) where = " WHERE " + where;
var xmlcmd='<?xml version="1.0"?><ENVELOPE><XMLCMD c1="gettitlesxml" p1="'+where+'" p2="" p3=""></XMLCMD></ENVELOPE>'; xmlhttp.Open("POST", "xml_receive.asp", false);  //use http to senda command to xml_receive.asp     
xmlhttp.Send(xmlcmd);//send thexml command   

Lines two through six:  are used to build a sql where clause based on the values entered into the three textboxes.  

Line seven:  has changed from the original code in that it now contains a parameter value for p1, which is the where clause that is built up in lines two through six

Changes to the xml_receive.ASP code:   consists of simply passing the parameter p1 to the GetTitlesXML function of the ActiveX control.

//************* Function gettitlesxml ***********************
if (c1=="gettitlesxml") 
   resultsXML=true; //results object will load a xml string
   var objWC= Server.CreateObject("WebClass.xmlControl");    //instantiate vb control
   objWC.strServer=sqlServer; //which sql server
   xmlreturn=objWC.GetTitlesXML(p1); //vb function to get titles and return as xml string
   var objWC=null; //destroy the vb control    


What I have just done is to take ASP to a new level of user interaction.  The user of the bookViewer application can now perform simple limited queries against the pubs database from within a single web page.  The queries are performed in the background via the xmlHTTP object and the user is left thinking he is running more of a windows type application than your typical ASP web application.  The technology I have just described is basically the concept behind Web Services.  The only diference is that Web Services are wrapped in a Layer of complexity namely the SOAP protocol.  The reason for the complexity is to make Web Services more flexible and standardized than what I have described above.  For situations where you don't want to expose yourself to whole world (only your own internal pages will be using the functionality) the methods I have described will more that suffice. 

Using the methods I have described in this article:  (Client-side application-->ASP Listener Page-->ActiveX Control)(ActiveX Control-->ASP Listener Page-->Client-side application) you can provide a simple method of communication between your client-side users and your back-end ActiveX controls.  Using the ASPListener Page allows you to expose only the methods and properties of the ActiveX controls that you want while hiding the rest.  Security could be build into the ASP Listener Page to prevent or allow access to only certain users if desired.  Using these methods, database security is maintained to the highest degree since all database access goes through the ActiveX controls and only those methods that are deamed safe and reliable need to be exposed through the ASP Listener Page.

I was amazed by the number of uses I have found for this technology.  My ASP Listener Page has grown from three or four functions to somewhere around 30 to 40.  There were so many instances where I used to call another ASP page just to process some information or to load a dropdown list box.  Now all I do is package up a XML command and send it to my ASP Listener Page and the results are back before the user even knows it.  I just mentioned populating a dropdown list box.  How many times have you wanted to populate a dropdown listbox based on a selection made in another listbox or textbox.  With pure ASP the only way to do it is to submit the form and process the rusults.  With JavaScript and the xmlHTTP object its easy as cake.  Just put some code in the first listboxes on_change() event handler to package up an xml command that will go out and get the values for the second list box based on the value chosen in the first.  In my next article "XSLT in 20 minutes" I will discuss the basics of XSL Transforms and the benifits of separating data from presentation.  Thanks for taking the time to read this article.  Please use the specific forum that has been set up for this article for any feedback or question regarding it.  And remember voting is always appreciated and is helpful for future readers.

Added Bonus - Populate one  drop down list box (<SELECT>) based on the value of another:

function dropDownOne_onchange(dropDownOneValue)
   var xmlhttp = new ActiveXObject("Msxml2.XMLHTTP"); 
   var xmlcmd; xmlcmd='<?xml version="1.0"?><ENVELOPE><XMLCMD c1="getDropDownTwo" p1="'+dropDownOneValue+'" p2="" p3 =""></XMLCMD></ENVELOPE>';
   xmlhttp.Open("POST", "xml_receive.asp", false);
   var doc = new ActiveXObject("Msxml2.DOMDocument");
   doc.load(xmlhttp.responseXML);  //load the returned xml into the doc DOM
   var xmlhttp = null; 
   var listLen= doc.childNodes.item(1).childNodes.length; //length of listbox = number of Nodes in DOM
   var mylistbox=document.frmControls.dropDownTwo //set reference to the dropDownTwo list box
   mylistbox.length = listLen; //change the number of items in dropDownTwo list box
   mylistbox.selectedIndex=0; //set the  selected item to be the first item in the list box
   for (i=0;i<listLen;i++)   //populate thedropDownTwo lsitbox fromthe DOM 
      //the structure of the returned XML should be similar tothe following
      //<ITEMS><ITEM value="value1" text="text1"/></ITMES>
Total article views: 7387 | Views in the last 30 days: 3
Related Articles

Command text was not set for the command object

Command text was not set for the command object


How do I version control SQL Server objects?

Version control SQL Server 2008 objects


Add SQL Objects to TFS/ Version Control

Trying to add SQL Objects to Source Control and versioning


Version Control -Part 1- Dealing with Code

Part 1 of Steve Jones series on version control and SQL Server. This article examines how you can wo...


Introduction to ADO - The Command Object

The third article in a four part series, this week Andy shows how to use the command object to work ...