DHTML/Javascript database access - feasibility question

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

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

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

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?

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

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

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

Thanks

David

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

David

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