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:
- Using
SQL Server 2000 XML capabilities
- Creating
a custom SQL XML Service in VFP
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:
- Set up
a Web virtual directory
- Run the
queries through URL-based access
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 its
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:
- Always log on as
This uses a specific SQL Server or Windows user account. If you use
this option, it's very important that you configure this user so that she
has access to only the files and file operations that are appropriate for
all users. This is the least restrictive mechanism, because all users impersonate
the specified account. If you use the sa account as in the example
above, you're saying that everyone has open access to the database. So, be
careful if you use this option.
- Use Windows Integrated Authentication
This mechanism uses the Windows file security settings for the virtual
directory to restrict and pass through user account information to the SQL
backend. This authentication mode maps to Windows security in SQL Server
and will not work with SQL Server user accounts. Note that for this to
work, each user wanting access to the database must be included in the
physical directory permissions for the virtual directory (set using the
Security tab in Explorer).
- Basic Authentication
HTTP's built-in Basic Authentication user security is used to verify
the user against a SQL Server login. The authenticated user is
impersonated and passed to the SQL Server security handler, mapping
against SQL Server security accounts.
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>
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.
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:
- Access to any data VFP can access
VFP Cursors, SQL Server, Oracle, Informix, DB2, anything at all.
- Code control
Because you have the VFP source code, you have full control over how
this mechanism works, and can change and adjust it as needed.
- Full control over security
Because the service is provided as a pluggable component, it can be
called from just about anywhere in your server application. You can create
custom requests that perform filtering and pre-processing (like checking
for invalid inputs or access to certain tables) of the incoming SQL, as
well as user verification.
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).
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 youll 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.
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/.
