Solved

DHTML/Javascript database access - feasibility question

Posted on 2003-10-22
10
3,066 Views
Last Modified: 2010-04-06
I have been asked to develop a small intranet application for my company.  The application will consist of two main pages, one for data entry (single user) and one for data display (many users).  The data will be stored across several tables in a SQL server db.
Timescales are short so I feel I have to develop it using the technology I understand i.e. ASP.  However, one of the requirements is that there should be a very short latency between data being entered/modified and it being displayed on the data display screen.  Also, the data display screen should only update if data has been entered/modified.  These reqs would seem to rule out simply refreshing the display screen using a <meta> refresh tag.  I realise I could write everything in DHTML/Javascript but I don't have enough time to learn for this project.

The other idea I had was to use a hybrid i.e. write a small amount of DHMTL/Javascript that checks a timestamp in the db to see if data has been changed.  If it has changed then cause the page to refresh and let the ASP code do the rest.  This check would have to take place every 5 seconds or so or be event-driven.
Is this possible/sensible?  Any code fragments?
0
Comment
Question by:commonorgarden
  • 6
  • 3
10 Comments
 
LVL 15

Expert Comment

by:VincentPuglia
ID: 9610588
Hi,

  offhand I would say no, at least not the way you envison it.  dHTML/javascript are client languages -- they do not speak to the server.

Your best bet is to update the displays after updating the dbms.  that is:
1)the data entry clerk hits submit
2) the data is uploaded
3) you massage the data, do final verifications, and then update the database
4) you then rewrite the display pages (if any) with the new data (if necessary)

Vinny

0
 
LVL 26

Expert Comment

by:rdcpro
ID: 9611717
This can be done with DHTML and Javascript.  You communicate with SQL Server via the XMLHTTPRequest object.  It makes requests of the server (in XML), and gets XML responses.  The XML is then transformed into the data view on the client page.  I built my first client-server application like this back in 1999/2000, and the tools now are much more robust.

SQL Server has built-in XML integration.  Download SQL XML 3.0 (to get the latest support), and review the documentation on XML and Internet support.  It's very cool.  You create a virtual directory in your IIS box (which does not have to reside on the SQL Server Box) using the configuration tool that comes with SQL XML 3.0.  Then you can put XML "templates" in a sub-directory, that look like:

getCustomer.xml
====================================
<?xml version="1.0" encoding="UTF-8"?>
<ROOT xmlns="urn:schemas-microsoft-com:xml-sql">
      <query>
            SELECT CustomerID, ContactName
            FROM Customers
            FOR XML AUTO
      </query>
</ROOT>


Then you load that template from the client HTML page using the XMLHTTPRequest object, like:

xmlhttp = new ActiveXObject("Msxml2.XMLHTTP");
xmlhttp.open("GET", "http://myIISServer/virtualdirectory/templatevirtualname/getCustomer.xml", false);
xmlhttp.send();
divTarget.innerHTML = xmlhttp.responseXML.transformNode(xslDoc);

where divTarget is a div tag with the id "devTarget" that receives the result of the transformation.  What's cool is that ISAPI hands the template off to the SQL XML dll, which does the query on SQL Server, and returns the result as XML to the client.  

You can pass parameters into the template like:

http://myIISServer/virtualdirectory/templatevirtualname/getCustomerData.xml?CustomerID=ALFKI

<?xml version="1.0" encoding="UTF-8"?>
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
      <sql:header>
            <sql:param name="CustomerID"/>
      </sql:header>
      <sql:query>
                  SELECT 1 as Tag,
                  NULL as Parent,
                  Customers.CustomerID as [Cust!1!CustID],
                  Null as [Order!2!OrderID]
                  FROM Customers
                  WHERE Customers.CustomerID LIKE @CustomerID
                  
                  UNION ALL
                  
                  SELECT  2,
                  1,
                  Customers.CustomerID,
                  Orders.OrderID
                  FROM Customers JOIN Orders
                  ON Customers.CustomerID = Orders.CustomerID
                  WHERE Customers.CustomerID LIKE @CustomerID
                  ORDER BY [Cust!1!CustID], [Order!2!OrderID]
                  FOR XML EXPLICIT

      </sql:query>
</ROOT>


You can handle updates with UpdateGrams, which are templates that contain "before update" and "after update" conditions, which handle contention issues nicely.  Also, they can easily be grouped into transactions, so that if an update fails, the entire transaction rolls back.  I've been doing this for several years now on a variety of projects, and it's pretty easy stuff.  

If  you try this, and have questions as you go along, post in the XML area, as that's where I usually hang out.

Regards,
Mike Sharp

http://dev.rdcpro.com
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 9611805
BTW, I have a tutorial on the syntax of FOR XML EXPLICIT on my site:

http://dev.rdcpro.com/news/zones/xml/sqlxml/explicit/

And the steps you use for configuring the virtual directory are listed here:

http://dev.rdcpro.com/zones/xml/sqlxml/sqlxmlvirtualdir/

Regards,
Mike Sharp

http://dev.rdcpro.com
0
 

Author Comment

by:commonorgarden
ID: 9632391
Thanks for these comments.  rdcpro - your approach seems pretty cool.  How can I use it to automatically update the client page when the database is updated, though?  Should I use an invisible iframe (refreshing every few seconds) to check whether an update has occurred then cause the main part of the page to update?  Or have I missed something in your original answer?
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 9634701
Just use the same XMLHTTP Request object to communicate with the server, either with a simple query that returns the timestamp you were talking about, or that gets some other piece of iinformation that tells if the information has changed.  You'll want an asyncrhronous call, so the browser isn't locked up.  You specify a callback function in the XMLHTTP object that is called when the readyState changes on the request.  The XMLHTTP might return a simple XML document that indicates the last time the data has changed, like:

<dbChangeDate>2003 10-31 12:57.045</dbChangeDate>

var lastChanged = // when page is first loaded, this value is set

xmlhttpDate = new ActiveXObject("Msxml2.XMLHTTP");
xmlhttpDate.open("GET", "http://myIISServer/virtualdirectory/templatevirtualname/getChangedStatus.xml", true);
xmlhttpDate.onReadyStateChange = checkTimeStamp
xmlhttpDate.send();
divTarget.innerHTML = xmlhttp.responseXML.transformNode(xslDoc);

function checkTimeStamp()
{
    if (xmlhttpDate.readyState == 4)
    {
        if (xmlhttpDate.responseXML.documentElement != lastChanged)
        {
            //Data has changed!  Refresh Page
        }
    }
       
}

Watch for stack overflow if you don't refresh the page each time (I think it would be possible to do it with functions and without refreshing, but you just have to be careful).  Also, if there are many, many clients, this will generate a lot of network traffic.  I think .NET has a mechanism (in C#, you're using delegates) that would allow you to "notify" the client if an update occurs.

Regards,
Mike Sharp
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 26

Expert Comment

by:rdcpro
ID: 9634715
Well, in my code above, you obviously have to have a counter function that sleeps to call the XMLHTTP every few seconds.  This could be done with an i-frame, too, as you suggest.

Regards,
Mike Sharp
0
 

Author Comment

by:commonorgarden
ID: 9641958
How would a counter function work?  Is a neater way to do this kind of thing than with an iframe?

Thanks

David
0
 
LVL 26

Accepted Solution

by:
rdcpro earned 500 total points
ID: 9647402
Hmm...the more I think about it, the more I think trying to do an async counter in a browser might be tricky.  I'm not sure you can even start a separate thread to keep track of the time.   The iframe is probably the neatest, and certainly the simplest way.  Whatever it takes to get the job done, eh?

A while back there was a discussion about the XMLHTTP object and how it dealt with expired content.  I think there was no way to have it refresh itself (like a meta tag would do for a window or frame).  You should make sure you've set

response.expires = -1000

so that the data isn't cached.  

I'm not sure what else this page must do, but you could use an infinite loop that sleeps for, say 5000 milliseconds, then wakes up and runs the request.  I think maybe the page would still respond to events, so if you clicked something on the page, the onclick event would fire, and you could handle the click.  Then when your code is finished doing whatever it needs to with the click, it would go back to waiting for the timeout of the sleep method.  This, by the way, is a method of the Windows Scripting Host, so you have to instantiate an object and call the sleep method on it. Something like:


var lastChanged = // when page is first loaded, this value is set
var WshShell = new ActiveXObject("WScript.Shell");
goToSleep()


function getData()
{
    xmlhttp = new ActiveXObject("Msxml2.XMLHTTP");
    xmlhttp.open("GET", "http://myIISServer/virtualdirectory/templatevirtualname/getChangedStatus.xml", false);
    xmlhttp.send();
    divTarget.innerHTML = xmlhttp.responseXML.transformNode(xslDoc);
}

function goToSleep()
{
    while (false)
    {
        WScript.Sleep(5000);
        xmlhttpDate = new ActiveXObject("Msxml2.XMLHTTP");
        xmlhttpDate.open("GET", "http://myIISServer/virtualdirectory/templatevirtualname/getChangedStatus.xml", true);
        xmlhttpDate.onReadyStateChange = checkTimeStamp
        xmlhttpDate.send();
    }
}


function checkTimeStamp()
{
    if (xmlhttpDate.readyState == 4)
    {
        if (xmlhttpDate.responseXML.documentElement.text != lastChanged)
        {
            //Data has changed!  Refresh Page
            lastChanged = xmlhttpDate.responseXML.documentElement.text
            getData()
        }
    }
       
}


You might play around with that for a bit, and see if it does what you want and still handles page events.

Regards,
Mike Sharp
0
 

Author Comment

by:commonorgarden
ID: 9648582
Thanks for all your help, Mike.  You may be hearing from me again as I put this stuff to use.

David
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 9652805
Great!  I mostly hang out in the XML topic area, though, so if you have a question on this stuff, you might post there.

Regards,
Mike Sharp
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now