Home |  White Papers |  Message Board |  Search |  Products |  Purchase |  News | 

     Using XML Data Services in distributed applications

 

By Rick Strahl

http://www.west-wind.com/

 

Last Update: 10/23/2000

 

See Also:

XML Messaging in Distributed Applications (previous parts of this series)

wwSOAP classes (includes samples discussed here)

 

Amazon Honor System Click Here to Pay Learn More

XML is driving the distributed application revolution on the Web today and more and more generic XML services are becoming available. In previous articles, Rick has discussed custom XML messaging solutions. In this article, he describes generic XML data services that can simplify and open up existing applications without writing extensive custom XML code.

 

XML messaging can take a number of different forms in the application development environment.

Accessing data in XML format is getting easier with generic server-side XML services that provide data to client applications.

In the last couple of issues, I described XML messaging from a custom development point of view: a client application would access a specific URL on the Web server, post an XML request to the server and retrieve a custom-formatted XML document response. Both the client and the server pass XML between these application tiers, parse the XML, and use it internally to complete whatever application tasks need to be performed.

 

This scenario is by far the most common for XML use today, where XML generation and usage is handled largely by custom code on both ends of the application. While this works great and provides the maximum amount of control, it also requires repetitive code to be written. Think about the following scenario: A user needs some data in XML format, so she submits an XML request to a server application. The server application retrieves a filter from the provided XML information and runs a query against the backend, retrieving a SQL cursor. The cursor is then encoded into XML and returned back to the client over HTTP. Every time a different kind of query is needed, this process is repeated with more custom code.

 

The next logical step in getting around this repetitiveness is to take advantage of XML Services that can provide data and other XML content generically. You can either build this functionality yourself or take advantage of services that the operating system or an application server provides. In this article, I want to look at several examples that accomplish this:

 

 

In the next issue, I'll look at SOAP (Simple Object Access Protocol) Web Services to generically run code on Web Servers. In this issue, I'll focus on XML data access.

SQL Server 2000 XML Support

One obvious way to take advantage of XML generically is to use a SQL service to provide data in XML format directly. The idea is to provide query information like a SELECT statement and have the XML service provide the result data in XML format. In this scenario, a single URL is used to service different results based on the query information passed.

 

SQL Server 2000 introduces the concept of direct XML access, both internally in the SQL engine as well as externally over an HTTP connection. The HTTP connection is implemented through an ISAPI extension that parses queries passed via: an HTTP querystring; prepared XSL-based queries; or updategrams, which can update changed data on the server automatically. For more detailed info on the various query formats in SQL Server 2000 see the Query XML From SQL Server 2000 article in this issue.

 

SQL Server 2000 enables this functionality with a set of new keywords that can be added to SQL statements to generate XML directly from a query. For example, you can type the following into the query analyzer:

 

select * from authors for XML AUTO,ELEMENTS

 

This returns an XML result, but possibly not quite in the format you expected: The result is a cursor with 255-byte records containing the XML for the SQL result. To make use of the XML in a client application, you'd have to put the XML back together into a string like this:

 

lnSQL = SQLCONNECT("Pubs")

SQLExec(lnSQL,"select * from authors for XML AUTO,ELEMENTS")

 

lcXML = ""

SCAN

  lcXML = lcXML + EVALUATE(FIELD(1))

ENDSCAN

 

lcXML = [<?xml version="1.0"?><authorlist>] +;

        lcXML + [</authorlist>]

 

Note that the new “FOR XML” clause creates only an XML fragment, containing all of the rows at the top level and the field values below each row element (<authors> in this example):

 

<authors>

  <au_id>172-32-1176</au_id>

  <au_lname>White Jr.</au_lname>

  <au_fname>Johnson</au_fname>

  <phone>408 496-7223</phone>

  <address>10932 Bigge Rd.</address>

  <city>Menlo Park</city>

  <state>CA</state>

  <zip>94025</zip>

  <contract>1</contract>

  <pk>22</pk>

</authors>

<authors>

  <au_id>213-46-8915</au_id>

  <au_lname>Green</au_lname>

  <au_fname>Marjorie</au_fname>

  <phone>415 986-7020</phone>

  <address>309 63rd St. #411</address>

  <city>Oakland</city>

  <state>CA</state>

  <zip>94618</zip>

  <contract>1</contract>

  <pk>7</pk>

  </authors>

<authors>

…

 

Note that this is not a valid XML document, so at the very least, a document root node has to be added (<authorlist></authorlist> in the example above). The XML header processing instruction should also be added if this is to be a final XML document. The last line of code in the example above adds these elements to the retrieved XML.

 

This concept works well for server-side Web applications that have to serve XML dynamically, as well as applications that have to provide multiple result sets to client applications.

 

SQL Server can also return an XML schema for the data inline with the actual XML data by adding the XMLDATA keyword to the FOR XML keyword. The XML schema describes the data in the XML document and provides data type information that allows retrieval of elements with their actual data types with the nodeTypedValue property of an element. The document with a schema looks something like this: 

<?xml version="1.0"?>

<pubs>

<Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data"

                 xmlns:dt="urn:schemas-microsoft-com:datatypes">

<ElementType name="Authors" content="eltOnly" model="closed" order="many">

  <element type="au_lname" />

  <element type="au_fname" />

  <element type="phone" />

</ElementType>

  <ElementType name="au_lname" content="textOnly" dt:type="string" />

  <ElementType name="au_fname" content="textOnly" dt:type="string" />

  <ElementType name="phone" content="textOnly" dt:type="string" />

</Schema>

<Authors xmlns="x-schema:#Schema1">

  <au_lname>DeFrance</au_lname>

  <au_fname>Michelle</au_fname>

  <phone>219 547-9982</phone>

</Authors>

<Authors xmlns="x-schema:#Schema1">

  <au_lname>del Castillo</au_lname>

  <au_fname>Innes</au_fname>

  <phone>615 996-8275</phone>

  </Authors>

<Authors xmlns="x-schema:#Schema1">

  <au_lname>Dull</au_lname>

  <au_fname>Ann</au_fname>

  <phone>415 836-7128</phone>

</Authors>

</pubs>

 

Unfortunately this schema information is insufficient to create data files on the client side on the fly as it does not include field size information. This means any application that wants to consume this XML data will have to have the data structure in place if a client side cursor is used – it cannot be created dynamically. Furthermore it uses the dt namespace for data typing, which is specific to Microsoft and is not recognized by other non Microsoft XML parsers and will not allow those parsers to return actual typed data from XMLDOM elements.

Configuring Web access to a SQL Server database

More interestingly, SQL 2000 also provides a native mechanism for directly serving this same XML data via HTTP. You can configure a custom ISAPI extension to serve data from a SQL Server installation by following two steps:

 

 

Access to SQL Server XML data through HTTP is accomplished by running Configure SQL XML Support from the SQL Server Start Menu group. This Wizard sets up a virtual directory for a specific database to be accessed.

 

The configuration process consists of running the XML administration utility to configure how the Web server will provide the XML data. Figure 1 shows the first page of the configuration wizard, where the virtual directory is defined.

 

Figure 1 – The IIS XML configuration utility sets up the virtual directory to serve data for a particular database.

 

The first page contains the directory settings for the name of the virtual Web directory and its physical location on disk. Querystring-based queries don't directly use this directory, but any XML-based template query files that you use are copied into this directory. Note that the virtual directory does not have to match the name of the database, although it’s commonly done that way.

 

The most important tab on the dialog is the Security dialog shown in Figure 2, which configures the database restrictions for Web access. The options range from allowing everyone open access to validating user names through the Web.

 

Figure 2 – The security tab determines how security is passed through to SQL Server over the Web. The shown setting allows open access by using the “sa” account, which is probably not a good idea, except for a demo. ##figure2.gif##

 

The options in the security tab are:

 

 

 

The other tabs are fairly obvious: The DataSource tab lets you set the database server and database that is to be exposed via XML, and the Settings tab specifies what kinds of queries are supported by the IIS extension (URL Queries, Template Queries, XPATH queries, POST).

Returning queries via URLs

Once you've set up the XML ISAPI extension, you can now run URL-based queries against the configured database. It's very easy with a URL like this:

 

http://localhost/pubs?sql=select+*+from+authors+FOR+XML+AUTO,ELEMENTS&Root=pubs

 

The key is to pass a “sql=” key on the querystring, followed by the actual SQL statement. As in the earlier example, note that we specify FOR XML and the display directives AUTO,ELEMENTS to return all fields with element formatting. When running URL-based queries, we also have to specify a root keyname, since FOR XML creates only an XML fragment. By specifying the “Root=” key, we provide the name of the root node for the XML document, which is required to make the document valid. Figure 3 shows the result of this query in the browser.

 

Figure 3 – XML returned from a SQL command sent via querystring.

 
To consume this XML in a VFP client application, you can retrieve it with the XMLHTTP component, then load the data into the MSXML parser to walk through the document.
 

lcSQL = "SELECT au_lname,au_fname,phone, contract " + ;

        " FROM authors  WHERE au_lname like 'B%' " + ;

        " FOR XML AUTO,ELEMENTS,XMLDATA"

 

oHTTP = CREATEOBJECT("Microsoft.XMLHTTP")

oHTTP.Open("GET","http://localhost/pubs?sql=" + ;

           URLEncode(lcSQL) + "&root=pubs",.F.)

oHTTP.Send()

 

*** Directly get an XMLDOM object

oXML = oHTTP.responseXML

 

*** Check for error

IF EMPTY(oXML.xml)

   WAIT window oXML.ParseError.Reason

   RETURN

ENDIF  

 

loAuthors = oXML.SelectNodes("/pubs/authors")

IF ISNULL(loAuthors)

   WAIT window "No authors found"

   RETURN

ENDIF

 

*** Loop through rows

FOR x = 0 to loAuthors.Length -1

   *** Loop through fields

   loAuthor = loAuthors.item(x)

   FOR y = 0 to loAuthor.childNodes.Length - 1

      ? loAuthor.childnodes(y).NodeName,;

        loAuthor.childnodes(y).Text

   ENDFOR

   ?

ENDFOR

 
You can also POST the data to the Web server, which gives you a little more flexibility. For example, you can use named parameters for queries. This Visual FoxPro 7.0 example demonstrates:
 

TEXT to lcXML NOSHOW

<pubs xmlns:sql="urn:schemas-microsoft-com:xml-sql">

<sql:header>

    <sql:param name="qcLastName">B%</sql:param>

</sql:header>

<sql:query>

  SELECT au_lname, au_fname

  FROM   authors

  WHERE  au_lname like @qcLastName

  FOR XML AUTO,ELEMENTS

</sql:query>

</pubs>

ENDTEXT

 

oHTTP = CREATEOBJECT("Microsoft.XMLHTTP")

oHTTP.Open("POST","http://localhost/pubs",.F.)

oHTTP.setRequestHeader("Content-Type","text/xml")

oHTTP.Send(lcXML)

 

oXML = oHTTP.responseXML

 

… same parsing and error checking as previous sample

 

 

Note the XML document that's posted to the server. The root node of the posted document determines the root node name of the result set (in this case, pubs). The result returned from this query is identical to the previous example, and can be parsed the same way. The only difference is that the data is POSTed to the server in XML format.

 

You can also consume this XML inside a browser, using code very similar to our Visual FoxPro code. Take a look at Figure 4, which shows a dynamic HTML form that lets you specify a SQL statement in an edit box, then runs the SQL statement.

 

Figure 4 – This example uses an edit box to capture a SQL statement, then dynamically builds an HTML table to update the page with data retrieved from SQL Server via XML. Only the table is rebuilt from the downloaded XML – the main page is never reloaded.

 

This page uses JavaScript code and the XMLHTTP component to POST the SQL statement to the server using the XML block described in the previous example. Here are a few snippets of code that show how this works in the client-side script page:

 

<HTML>

<head>

<title>HTTP Remote SQL access via XML (Part 1)</title>

<script>

function RunSQL(lcSQL, lcRoot) {

 

var lcServer = "<%= Request.ServerVariables("SERVER_NAME")%>";

 

if (typeof(lcRoot) != "string")

   lcRoot = "xdoc";

 

if (typeof(lcSQL) != "string")

   lcSQL = httpsqlform.inputsql.value;

 

var httpOb = new ActiveXObject("Microsoft.XMLHTTP");

 

if (lcSQL.substr(0,6).toLowerCase() == "select")

   lcXMLClause = " FOR XML AUTO,ELEMENTS";

else

   lcXMLClause = "";

   

var lcUrl = "http://"+lcServer+"/pubs"

 

httpOb.open("POST",lcUrl,false);

httpOb.setRequestHeader("Content-Type","text/xml");

 

lcXML =

'<pubs xmlns:sql="urn:schemas-microsoft-com:xml-sql" >' +

'<sql:query>' +

lcSQL + lcXMLClause +

'\r\n</sql:query>' +

'</pubs>' ;

 

///*** And POST an XML string to the server

///*** NOTE: This actually performs the operation

httpOb.send(lcXML);

 

return httpOb.responseXML;

}

 

function ShowResult() {

 

oXML = RunSQL();

 

var lcHTML = "";

 

/// drill down into the row detail

loRows = oXML.documentElement.childNodes

 

/// Create the table header

lcHTML = lcHTML + "<table border='0'>"                         

 

/// Loop throw all of the rows                     

for (x=0; x < loRows.length  ; x++) {

    loRow = loRows.item(x);

   

    /// deal with header on the first pass only

    if (x==0) {

        lcHTML = lcHTML + "<tr>\r\n"

       

        /// Loop through all of the columns

        for (y=0; y< loRow.childNodes.length ; y++)

        lcHTML = lcHTML + "<th style='color:white;background:black'>" +

                 loRow.childNodes(y).nodeName + "</th>\r\n";

        lcHTML = lcHTML + "</tr>\r\n"

    }

   

    lcHTML = lcHTML + "<tr valign='top'>"

    /// Loop through all of the columns

    for (y=0; y< loRow.childNodes.length ; y++) {

        lcHTML = lcHTML + " <td>" + loRow.childNodes(y).text + "</td>\r\n";

   }

    lcHTML = lcHTML + "</tr>\r\n"

}

 

lcHTML = lcHTML +  "</table>";

 

queryResult.innerHTML = lcHTML;

}

</script>

</head>

 

Retrieving data from the server

The actual HTML page contains an empty <span id="queryResult"></span> tag that is filled with HTML built by the ShowResult() function. ShowResult calls the RunSQL function, to which you can simply pass a SQL statement and Root node name. It goes out via XMLHTTP, runs the query on the server, and returns an XMLDOM object. ShowResult then parses the XML generically by walking through the rows and columns, creating an HTML table from the result XML.

 

You can enhance the RunSQL() function a little to include error handling and provide error information (in a global variable, for example) to make your client code a little easier to work with. With this simple interface in place, it is fairly painless to quickly utilize data provided by the server.

 

Note that RUNSQL() actually checks to see if the statement is a SELECT statement. Only SELECT statements actually return a result, so the FOR XML clause can be dropped on anything but a SELECT statement. You can check out the update functionality by typing an INSERT or UPDATE command into the edit box.

 

Using this type of remote interface to SQL Server, you can easily build applications that access SQL from the client side, although you typically have to manually parse the XML into an HTML structure to display the data (like fields or an HTML table).

 

You can also run INSERT, DELETE and UPDATE statements using either URL-based or POST queries, so the client application running in the browser can easily modify data on the server.

 

Retrieving error information consists of looking at the returned XML and checking for error messages, which look like this:

 

<?xml version="1.0" encoding="utf-8" ?>

<pubs>

  <?MSSQLError HResult="0x80040e37" Source="Microsoft OLE DB Provider for SQL Server" Description="Invalid object name 'authodrs'."?>

</pubs>

 

Unfortunately, the error format is not very consistent, so checking for errors generically is not easy. It would have been nice if a standard error structure had been chosen, so a simple check for a node would tell whether an error occurred. As it stands now, you have to check for specific error messages in the XML content.

SQL 2000 and Schemas

What about non-SQL Server data

The SQL 2000 XML services are very nice, but what if you want to retrieve data from other data sources? Some other database servers like Oracle also support XML results over HTTP, but the formats are incompatible. And, what if you want to pull XML from a data source like Visual FoxPro, which doesn't support XML directly?

 

A couple of years back I started building a generic HTTP-based data service to pull data in a variety of formats and return the results to a client application. About a year ago this service was converted to use XML and the features provided in wwXML (discussed in great detail in the Summer 2000 issue of CoDe) to return the data from any data source that Visual FoxPro can access. This means that native Visual FoxPro data or any ODBC data source, including SQL Server, Oracle or Informix, etc., can be accessed using the same data service interface.

 

What's interesting is just how little code is needed to write a generic service like this. The server side component was written for West Wind Web Connection, but with slight modification can be plugged into any VFP-based server-side solution, such as an ASP component. It simply picks up an XML input request, runs the query or other SQL statement, and returns an XML result set back to the client application.

 

Figure 5 shows an example of the wwHTTPData sample application that lets you query data in a variety of formats from a Visual FoxPro client application (in this case, an ActiveDocument application running on the Web).

 

Figure 5 – Building client and server-side components that retrieve data from a Web server dynamically is not difficult. The wwHTTPData class, for example, requires a handful of property settings and a call to the Execute method to retrieve data much like SQL passthrough, but over the Web.

 

The various fields on the form correspond to client-side properties set on the wwHTTPData object used to run a remote query:

 

o=CREATE("wwHTTPData")

 

o.cServername = "www.west-wind.com"

o.cHTTPLink = "/wconnect/wc.dll?http~httpdata"

o.lSecure = THISFORM.chkHTTPS.Value

 

*** 0 - VFP Cursor  1 - XML and Cursor  2 - XML

o.nResultMode = 0

 

*** Result Cursor name

o.cSQLCursor = "Tcustomers"

 

lnSecs = SECONDS()  && Keep track of response

 

*** Run the request!

o.Execute(THISFORM.edtSQL.Value)

 

IF o.lError

   MessageBox(o.cErrorMsg,48,THISFORM.Caption)

   RETURN

ENDIF

 

SELE TCustomers

BROWSE

 

What's nice in this environment is that the request is sent to the server via an XML request containing the SQL statement and other operational parameters for the SQL command. The client code simply persists the wwHTTPData properties into XML and the server picks them up when the data is posted to the Web server.

 

The format looks something like this:

 

<?xml version="1.0"?>

<wwhttpdata>

      <csql>select * from tt_cust</csql>

      <nresultmode>0</nresultmode>

      <csqlcursor>TCustomer</csqlcursor>

</wwhttpdata>

 

Note that the wwHTTPClient can create this XML from property settings, but a non-VFP client can simply create the XML above as a string and POST it to the appropriate URL on the server for processing. You can also include a cSQLConnectString to specify a remote data source like SQL server.

Creating a generic HTTP data server

On the server, the SQL statement is retrieved and a few security checks are done (checking for excluded SQL commands and a parse hook that allows the server to exclude code containing certain text, for example). The SQL statement is then executed. Any resulting cursor is returned back to the client in the requested format over HTTP. wwHTTPData actually supports several formats: VFP cursor, which is packaged in a proprietary format that the wwHTTPData client understands and can turn back into a cursor; XML; or ADO-compatible XML. A VFP client application can request either, with the raw cursor being more compact. If the app is pure VFP and there's no need to share data openly or access data from another source, VFP cursors are a good choice. If non-VFP clients want the data, they can request a result in XML format. Note that the client application can determine how the data should be returned. This is nice, because a VFP application could ask for the data to be returned in native VFP code, while a browser application could ask for the same data in XML format. All that needs to change is the nResultMode property, either on the object or in the submitted XML request.

 

So, what's involved in doing this on the server? Not all that much, really. Here's the full code for the S_Execute method of the wwHTTPData object. Note that loProcess is a Web Connection process context object that holding references to the Request and Response objects. To build this into an ASP component, you'd adjust the Request and Response method calls to use the full ASP syntax of Request.Form("cSQLStatement").item(), for example.

 

************************************************************

* Server side Execute

*********************************

LPARAMETER loProcess

LOCAL lcResultAlias, loEval, lcFileText, lnMaxLength, ;

      lcSQL, llUseZip, loHTML, lcUserName, lcCursorName

LOCAL lcSQLParameters

 

REQUEST = loProcess.oRequest

Response = loProcess.oResponse

 

THIS.lError = .F.

THIS.cErrorMsg = ""

 

lcXML = REQUEST.FormXML()

IF lcXML = "<?xml"

   *** Use Request.Form() from XML

   REQUEST.lUseXMLFormVars = .T.

ENDIF

 

lcFullSQL = REQUEST.FORM("cSQL")

lcFullSQL = STRTRAN(lcFullSQL,CHR(13)," ")

lcFullSQL = STRTRAN(lcFullSQL,CHR(10),"")

 

lcSQL = LOWER(LEFT(lcFullSQL,10))

llUseZip = !EMPTY(REQUEST.FORM("lUseZip"))

lnMaxLength = VAL(REQUEST.FORM("nMaxBufferSize"))

lnResultMode = VAL(REQUEST.FORM("nResultMode"))

THIS.nResultMode = lnResultMode

lcSQLConnectString = TRIM(Request.Form("cSQLConnectString"))

lcSQLParameters = Request.Form("cSQLParameters")

 

llXML = .F.

IF lnResultMode > 0

   llXML = .T.

   loXML = CREATE("wwXML")

ENDIF  

 

IF EMPTY(lcSQL)

   Response.WRITE(THIS.S_ReturnError("No SQL statement"))

   RETURN

ENDIF

 

*** Check for illegal commands

lnAt = AT(" ",lcSQL)

lcCommand = LEFT(lcSQL,lnAt - 1)

IF ATC(lcCommand+",",THIS.cAllowedCommands+",") = 0

   Response.WRITE(THIS.S_ReturnError(lcCommand + ;

                  " is not allowed or invalid."))

   RETURN

ENDIF

 

IF lcSQL # "select" AND lcSQL # "insert" AND lcSQL # "update" AND ;

   lcSQL # "delete" AND lcSQL # "create" AND lcSQL # "execute"

   Response.WRITE(THIS.S_ReturnError("Only SQL allowed."))

   RETURN

ENDIF

 

SYS(2335,0) && Disallow any UI access in COM

 

IF !EMPTY(lcSQLConnectString)

      loSQL = CREATE("wwSQL")

      loSQL.cSQLCursor = "THTTPQuery"

      IF !loSQL.Connect(lcSQLConnectString)

         if !llXML

         Response.Write(THIS.S_ReturnError(loSQL.cErrorMsg))

       ENDIF

         RETURN

      ENDIF

 

      lnResultCursors = loSQL.Execute(lcFullSQL)

      IF loSQL.lError

         Response.Write(THIS.S_ReturnError(loSQL.cErrorMsg))

       SYS(2335,1) && Disallow any UI access in COM

         RETURN

    ENDIF

ELSE

    IF lcSQL = "select"

      lcFullSQL=lcFullSQL+" INTO CURSOR THTTPQuery NOFILTER"

    ENDIF

   

    lnResultCursors = 1

      loEval = CREATE("wwEval")

      loEval.ExecuteCommand(lcFullSQL)

      IF loEval.lError

         Response.WRITE(THIS.S_ReturnError("SQL Error:" + ;

                        CHR(13) + lcFullSQL))

         SYS(2335,1)

         RETURN

      ENDIF

ENDIF

 

SYS(2335,1)

 

IF !INLIST(lcSQL,"select","create","execute")

   *** If no cursor is returned nothing is returned

   Response.WRITE(THIS.S_ReturnError("OK"))

   RETURN

ENDIF

 

SELE THTTPQuery

 

lcFileText = ""

IF lnResultMode > 0 && XML

   lcCursorName = REQUEST.FORM("cSQLCursor")

   llXMLHTTP = (REQUEST.FORM("lXMLHTTP") = "True")

   IF EMPTY(lcCursorName)

      lcCursorName = "sqlcursor"

   ENDIF

 

   loXML = CREATE("wwXML")

   loXML.cDocRootName = "wwhttpdata"

 

   *** Dump the file to XML

   DO CASE

      CASE lnResultMode =1 OR lnResultMode = 2

         lcFileText = loXML.CursorToXML(lcCursorName)

      CASE lnResultMode = 3

         lcFileText = loXML.CursorToADOXML(lcCursorName)

   ENDCASE

 

   IF llXMLHTTP  && Must UTF8 Encode

      loXML = CREATE("wwXML")

      lcFileText = loXML.EncodeXML(lcFileText) && UTF8

   ENDIF

  

   Response.ContentTypeHeader("text/xml")

ELSE

   *** Return encoded DBF file

   lcFileName = ADDBS(SYS(2023))+"wwd"+SYS(2015)+".dbf"

 

   *** Now select the result into another cursor

   SELECT * FROM THTTPQuery INTO DBF (lcFileName)

   USE

   USE IN THTTPQuery

 

   loIP = CREATE("wwIPStuff")

   lcFileText=loIP.EncodeDBF(lcFileName,.T.)

 

   *** Get rid of Temporary files

   ERASE (FORCEEXT(lcFileName,"*"))

   Response.ContentTypeHeader("application/octet-stream")

ENDIF

 

Response.WRITE(lcFileText)

 

This is not a lot of code (slightly truncated to reduce size) to provide a fully functional remote SQL service that can be plugged into any VFP-based server-side application. You can get the whole source to this code from http://www.west-wind.com/wwIPStuff.asp which includes the wwHTTPData class.

 

Figure 6 – This XML Guest Book application brings up a static HTML page that uses script to pull data from the Web server dynamically via XML. Only the actual data is retrieved – the base HTML form is never refreshed, giving you 'flicker free' data updates.

 

For an example that uses this wwHTTPData engine with Visual FoxPro data on the backend from an Internet Explorer browser application, go to:

 

http://www.west-wind.com/wconnect/guestxml.asp

 

This page uses script code on the client side to retrieve data from the guest database and display and edit records from our online guest book. Like the SQL Server 2000 dynamic example, this example uses XMLHTTP in a generic RunSQL() function to retrieve and update individual records without entirely reloading the interface. This yields a flicker-free browser application. You can View Source on the page to see in detail how the code works with methods like FillListBox(), LoadGuest(), and SaveGuest().

Client code to simplify consumption of server data

If the client application is Visual FoxPro, you can actually make life a lot easier by providing a client to directly parse incoming data from XML or encoded VFP cursors into usable VFP cursors, much the same as SQL Passthrough.

 

The client code posts the parameters to the server, retrieves the data, and returns it back in the requested format. If XML was returned, the HTTP result is simply returned as is. If a cursor is returned, the result string is retrieve and decoded into a table, then loaded into a cursor which can be accessed by the calling code:

 

LPARAMETER lcSQL

LOCAL lnSize, lnBuffer, lnResult, llNoResultSet, lcbuffer

 

lcSQL=IIF(VARTYPE(lcSQL)="C",lcSQL,THIS.cSQL)

 

THIS.lError = .F.

THIS.cErrorMsg = ""

 

IF !INLIST(LOWER(lcSQL),"select","create","execute")

   llNoResultSet = .T.

ELSE

   llNoResultSet = .F.

ENDIF

 

lnResult = THIS.oHTTP.HTTPConnect(THIS.cServerName,THIS.cUsername,THIS.cPassWord,THIS.lSecure)

IF lnResult # 0

   THIS.cErrorMsg = THIS.oHTTP.cErrorMsg

   THIS.nError = lnResult

   THIS.lError = .T.

   RETURN .F.

ENDIF

 

THIS.oHTTP.AddPostKey()

THIS.oHTTP.AddPostKey("cSQL",lcSQL)

THIS.oHTTP.AddPostKey("nMaxBufferSize",TRANS(THIS.nMaxBufferSize))

THIS.oHTTP.AddPostKey("nResultMode",TRANS(THIS.nResultMode))

THIS.oHTTP.AddPostKey("cSQLCursor",THIS.cSQLCursor)

THIS.oHTTP.AddPostKey("cSQLConnectString",THIS.cSQLConnectString)

 

lcbuffer = ""

lnSize = 0

 

*** Access the Url and pull data down to client

lnResult = THIS.oHTTP.HTTPGetEx(THIS.cHTTPLink,@lcbuffer,@lnSize)

IF lnResult # 0

   THIS.cErrorMsg = THIS.oHTTP.cErrorMsg

   THIS.nError = lnResult

   THIS.lError = .T.

   RETURN .F.

ENDIF

 

THIS.nResultSize = lnSize

 

IF llNoResultSet

   IF EMPTY(lcbuffer)

      RETURN .T.

   ENDIF

ELSE

   IF EMPTY(lcbuffer) or lcBuffer = "OK"

      THIS.cErrorMsg = "No data was returned from this request..."

      THIS.nError = -1

      THIS.lError = .T.

      RETURN .F.

   ENDIF

ENDIF

 

IF lcbuffer = "Error"

   THIS.cErrorMsg = lcbuffer

   THIS.nError = -1

   THIS.lError = .T.

   RETURN .F.

ENDIF

 

IF llNoResultSet

   RETURN .T.

ENDIF

 

IF THIS.nResultMode > 0  && XML

   THIS.cXMLResult = lcBuffer

 

   IF THIS.nResultMode = 1  && XML into a cursor

      *** Force new table instead of appending

      IF USED(THIS.cSQLCursor)

         SELE (THIS.cSQLCursor)

         USE

      ENDIF

 

      loXML = CREATE("wwXML")

      loXML.XMLToCursor(THIS.cXMLResult,THIS.cSQLCursor)

      IF loXML.lError

         THIS.cErrorMsg = "Error: Unable to parse XML into cursor"

         THIS.lError = .T.

         THIS.nError = -1

         RETURN .F.

      ENDIF

   ENDIF

ELSE

   *** Retrieve the file name from the buffer

   lcFileName = FORCEEXT(ADDBS(SYS(2023))+TRIM( SUBSTR(lcbuffer,6,40) ),"dbf")

 

   IF !THIS.oHTTP.DecodeDbf( lcbuffer,lcFileName) )

      THIS.cErrorMsg = " Error Decoding the downloaded file"

      IF AT("401",lcbuffer) > 0 AND ATC("Unauthorized",lcbuffer) > 0

         THIS.cErrorMsg = "Unauthorized access. "

      ENDIF

      THIS.nError = -1

      THIS.lError = .T.

      RETURN .F.

   ENDIF

 

   USE (lcFileName) ALIAS THTTPImport IN 0

 

   SELECT * FROM THTTPImport WHERE .T. INTO CURSOR ( THIS.cSQLCursor )

 

   USE IN THTTPImport

 

   ERASE (FORCEEXT(lcFileName,"*"))

ENDIF

 

RETURN .T.

 

With these client and server pieces in place and talking to each other, a VFP application can retrieve data from a server seamlessly over HTTP with a few lines of code. And non-VFP solutions can simply retrieve the data in XML format and use it as needed on the client side

 

Some of this functionality obviously overlaps with what SQL Server 2000 provides, but this solution allows more flexibility:

 

Watch out for security!

This remote SQL functionality is extremely easy to use and apply. But, be very careful that you check out your security environment, especially if you build browser-based applications. If your application is locked down via SQL Server security, you have to provide passwords (which can be passed as the 4th and 5th parameters to the XMLHTTP::Open() method).

 

Code security in HTML applications

If you're using SQL 2000, I strongly suggest that you use Windows security with your SQL installation and tie the XML Web services to Integrated Windows Security. It's secure over the wire and validates users against the database after they have authenticated against the directory. Client applications using tools like XMLHTTP will have to provide that security information – make sure to ask users for this info, rather than hardcoding a specific account into your script code, since client-side scripts are visible in the browser.

 

Also keep in mind that a database application exposed in this manner over the internet is vulnerable to hackers. Because the data engine is directly accessible over the Web, anyone can hit it with a variety of hacking tools for password cracking. People could be monitoring your site and scanning for logon passwords. You might want to consider setting up special logon pages that run under HTTPS, to make sure that password integrity is maintained over the Web connection to the data.

 

Finally, make sure to encrypt your data using HTTPS/SSL if the actual data traveling over the wire is sensitive. Either of the mechanisms described above support this simply by specifying an HTTPS based URL.

Don't get too happy with remote data services

At first glance, accessing SQL Server or VFP data directly over the Internet seems like a really great way to get data into client applications, but think about application design for a moment. If your client application accesses data directly this way, you're really eliminating the middle tier from your application. No longer do you have business objects talking to your data and performing crucial business rule validation and data abstraction, but you're now back to a traditional client/server environment where the client directly talks to the database and must provide its own validations and business rules.

 

There are ways around this for Fat Client applications, which could simply use the business objects on the client side and access the data through those business and data tier objects. Your data tier might even do this transparently through a runtime switch so it can go to native data on the local network or through XML when connected over the Web.

 

However, thin client and browser applications will not have that ability, since they cannot load business objects. Instead, they have to consume the XML pulled from the data sources directly. Take another look at the Guest Book application I linked to above – you’ll find that all the data access logic, error handling, and even validation is now pushed into the browser using a woefully inadequate scripting language. Building anything but small applets this way can be very tedious and time consuming.

 

Resources

So, what's the solution? Don't throw out custom XML Services just yet in favor of generic data services. In many cases, you can provide n-Tier functionality by using business objects on the server and having those business objects return XML over the wire. This provides the abstraction of business logic that is appropriate for a proper n-Tier development solution, but also gets away from the 'generic' data service concept. Still, you can use tools like the SQL results from SQL Server 2000 with SQL Passthrough or wwXML to help you generate the XML quickly and easily, but still run all of this through your middle tier objects to apply the appropriate business rules.

 

In the next issue, I'll look at another solution that makes this last option more palatable. SOAP (Simple Object Access Protocol) promises to standardize a remote procedure call interface for much more generic execution of server code. It does this through an XML standard protocol that can be complemented by a simple server-side Web Service interface to mimick simple function calls and abstract away the details of XML, HTTP, and the distributed architecture. The client just makes a method call and the server implements a simple method with input parameters and an output return value. This layer is ideal for accessing business logic to serve data properly using middle-tier objects which can for example use the data generated from the data services discussed in this article.

 

Until then, play with the remote data access mechanisms described here and see how they can fit into your development tool arsenal.
 
 

Rick Strahl is president of West Wind Technologies in Maui, Hawaii. The company specializes in Web and distributed application development and tools, with a focus on Windows 2000 and Visual Studio. Rick is the author of West Wind Web Connection, a powerful and widely used Web application framework for Visual FoxPro, and West Wind HTML Help Builder, and is co-author of Visual WebBuilder. He's also a Microsoft Most Valuable Professional and a frequent contributor to FoxPro magazines and books. He is co-publisher and co-editor of CoDe Magazine, and his book, "Internet Applications with Visual FoxPro 6.0" is published by Hentzenwerke Publishing. For more information, please visit: http://www.west-wind.com/.

 
   Home |  White Papers |  Message Board |  Search |  Products |  Purchase |  News |