Building a Web XML Data Service with Visual FoxPro

Have you ever wanted to build a remote data access mechanism into an application where you can talk to data that is coming over the Web rather than from a local or network data source? Wouldn’t it be cool if you simply pointed at a URL and could remotely execute some SQL code and return data to you on the client? In this article Rick shows how to build a generic Web Sql data service that can be used with any Visual FoxPro based Web backend and any Visual FoxPro client to provide remote data access to Visual FoxPro and ODBC datasources.

If you're using any kind of business object framework you are probably familiar with the concept of data access classes. These access classes provide an object oriented way to retrieve and access data from various data sources. A typical data access layer provides the functionality to execute various SQL commands against a specific type of data store in a backend agnostic way. On some level the data access level always interfaces a data connector that is used to talk to the data store: Using a Visual FoxPro SELECT command or DML language commands, using SQLPassthrough or OleDb or a CursorAdapter are some examples of ‘low level’ data connectors you use from VFP.

In this article I want to present a pair of data engine classes that provide such an interface layer to communicate with a Web XML Service. This solution consists of two components: A client component (a Proxy basically) that handles request encoding and response decoding and a server component that handles request decoding, execution and response encoding. All of the messaging occurs using XML including data and result messages. This solution requires that you run a Web Server that is capable of executing Visual FoxPro code such as ASP + COM, Web Connection, Active FoxPro Page, FoxISAPI or similar. I’ll show examples using Web Connection initially (because it’s easier to set up and run) and finish up with a section of using ASP + COM which requires a little explanation for configuration.

Web based SQL access

The first step is to build a SQL proxy and server. The goal of this process is to pass a SQL command along with optional SQL parameters and a few processing instructions to a Web application which responds to the XML request. The request contains the information required to process a SQL query and returns the result – if any – back to the client in a consistent XML format.

My goal was to create a simple class that essentially has one key method – Execute() that performs the execution of a remote SQL command. This method behaves very similar to the way SQLExecute in SQL Passthrough works in Visual FoxPro in that it returns the same kind of return values.

If you think about it the process to do this with VFP is pretty easy because VFP provides us nearly all of the tools to perform the required tasks right out of the box. In VFP we have a dynamic execution engine (Macros and Eval) to execute our commands or SQL Passthrough and we have XML conversion tools in CURSORTOXML and XMLTOCURSOR. The only additional logic that needs to be added deals with setting up the XML protocol to pass messages between the client and server for sending requests and receiving messages from the server.

The client component is nothing more than a VFP class that implements an XML based protocol and acts as proxy to the server. It takes your SQL command and option settings turns them into XML and ships the request to the server for processing. The server processes the SQL command and handles turning the result into XML to return. The client then decodes the returned XML back into a cursor, error messages or return values. Figure 1 shows how this process works.

Figure 1 – The wwHTTPSql class passes SQL statements to the Web Server which calls on the wwHTTPSqlServer class to process them and return the results back to the client.

The client is implemented in a class called wwHttpSql which communicates with the server based class called wwHttpSqlServer. The server class can be integrated into any Web development tool (Web Connection, ASP, FoxISAPI, Active FoxPro Page, ActiveVFP etc.) that supports VFP code either natively or via COM. But it must run on a Web Server for the client to communicate with it. You can also use low level methods to take out the

The XML is passed around as strings or DOM nodes so it's flexible for any kind of environment. Figure 1 shows the relationship between the wwHTTPSql and wwHTTPSQLServer classes.

Before I go into details of the workings behind the scenes let me give you a quick example what the client code looks like. The client uses the wwHTTPSql class to establish a Web Url to retrieve the data from.

Listing 1: Running a remote SQL Query with wwHTTPSql

DO wwHTTPSQL && Load Libs

oHSQL = CREATEOBJECT("wwHTTPSQL")

oHSQL.cServerUrl = "http://www.west-wind.com/wconnect/wwHttpSql.http"

oHSQL.nConnectTimeout = 10

*** Specify the result cursor name

oHSQL.cSQLCursor = "TDevelopers"

*** Plain SQL statements

lnCount = oHSQL.Execute("select * from wwDevRegistry")

IF oHSQL.lError

? oHSQL.cErrorMsg

ELSE

BROWSE

ENDIF

The key requirements here are the URL where the server component is hosted (a Web Connection Server request in this example) and the query or other SQL command that you want to execute on the server.

This is the most basic setup you can run, but there are many more options on the wwHttpSql object, which derives from the wwHttp class that provides the core HTTP options for authentication, connection configuration and more.

The client can determine how data is returned using the nResultMode property. The default (0) is to return a VFP cursor, but you can also return only the XML (2) without converting to a cursor. In this case the value in the cResponseXML property returns the XML. A nTransportMode property allows you to select how the data travels over the wire either using VFP’s native CURSORTOXML command (1), wwXML’s XML format (can be used with VFP 6) (0) or using binary data (raw VFP cursor data which is more efficient for large results) (2). In short you have a few options to retrieve your data so that you can use the most efficient approach for whatever scenario you’re dealing with.

The Execute() method excepts a range of SQL commands and wwHttpSql will figure out which commands return cursors and which return only messages or counts. It will also capture errors and every result returns an XML response. The client and server are synched to understand the XML messages so on the client all you need to do is query the properties of the object. Understand that you can query the server directly though if you have a non-VFP client. In this case you simply pass XML strings directly to the URL and parse the result XML manually. For example, in .Net you could write a connector that parses the XML into a DataSet.

The client generates XML that looks like this for sending to the server:

<sql>select * from wwDevRegistry</sql>

<sqlcursor>TDevelopers</sqlcursor>

</wwhttpsql>

When the client fires the Execute method the following steps occur:

The properties of the object are parsed into the XML described above
The XML is sent to the URL specified
The server processes the request and returns an XML result. The result is always an XML string unless a hard server error occurs. Soft errors result in XML error responses.
The client receives the XML result and parses it.
First check is for non-XML result – if not XML an error occurred and the request fails.
If the result contains an error XML block the error is parsed and the execute call fails with lError and cErrorMsg set.
If the request had a return value that value is parsed and stuck into vReturnValue
If the request had a cursor returned in XML it is packaged up into a cursor (if nResultMode = 0) specified by the cSQLCursor property.

If you think about this process for a second, you'll start to realize that with the tools that VFP provides, plus a little help from the free tools wwXML class you can very easily build this kind of functionality with relatively little code! The core code that makes this all happen in wwHTTPSQL is fairly short. Take a look at listing 2.

Listing 2: The core code of the wwHTTPSql client class

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

* wwHTTPSQL :: CreateRequestXML

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

FUNCTION CreateRequestXML()

LOCAL lcXML

loXML = THIS.oXML

lcXML = ;

"<wwhttpsql>" + CRLF + ;

loXML.AddElement("sql",THIS.cSQL,1) + ;

loXML.AddElement("sqlcursor",THIS.cSQLCursor,1) + ;

IIF(!EMPTY(THIS.cSQLConnectString),;

loXML.AddElement("connectstring",THIS.cSQLConnectString,1),[]) +;

IIF(!EMPTY(THIS.cSkipFieldsForUpdates),loXML.AddElement("skipfieldsforupdates",;

THIS.cSkipFieldsForUpdates,1) +CRLF,[]) + ;

IIF(THIS.nTransportMode # 0,;

loXML.AddElement("transportmode",THIS.nTransportMode,1),[]) +;

IIF(THIS.nSchema = 0,loXML.AddElement("noschema",1),[]) +;

IIF(!EMPTY(THIS.cSQLParameters),CHR(9) + "<sqlparameters>" + CRLF + ;

THIS.cSQLParameters + ;

CHR(9) + "</sqlparameters>" + CRLF,"")

IF THIS.lUTF8

lcXML = lcXML + loXML.AddElement("utf8","1",1)

ENDIF

lcXML = lcXML + "</wwhttpsql>"

THIS.cRequestXML = lcXML

RETURN lcXML

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

* wwHTTPSQL :: Execute

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

FUNCTION Execute(lcSQL)

LOCAL lnSize, lnBuffer, lnResult, llNoResultSet, lcXML

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

THIS.cSQL = lcSQL

THIS.lError = .F.

THIS.cErrorMsg = ""

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

llNoResultSet = .T.

ELSE

llNoResultSet = .F.

ENDIF

*** Create the XML to send to the server

lcXML = THIS.CreateRequestXML()

THIS.nHTTPPostMode = 4 && Raw XML

THIS.AddPostKey("",lcXML)

THIS.cResponseXML = THIS.HTTPGet(THIS.cServerUrl,;

THIS.cUserName,THIS.cPassword)

*** Clear the entire buffer

THIS.AddPostKey("RESET")

THIS.AddSqlParameter()

IF THIS.nError # 0

THIS.lError = .T.

RETURN -1

ENDIF

THIS.nResultSize = LEN(THIS.cResponseXML)

IF EMPTY(THIS.cResponseXML)

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

THIS.nError = -1

THIS.lError = .T.

RETURN -1

ENDIF

RETURN this.ParseResponseXml()

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

* wwHttpSql :: ParseResponseXml

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

FUNCTION ParseResponseXml()

LOCAL lcFileName, loDOM, loRetVal, cResult, ;

loError, loSchema, loXML

loXML = this.oXml

loDOM = loXML.LoadXML(THIS.cResponseXML)

THIS.oDOM = loDOM

*** Check for valid XML

IF ISNULL(loDom)

THIS.cErrorMsg = "Invalid XML returned from server" +;

loXML.cErrorMsg

THIS.nError = -1

THIS.lError = .T.

RETURN -1

ENDIF

*** Check for return value

loRetVal = loDom.documentElement.selectSingleNode("returnvalue")

IF !ISNULL(loRetval)

THIS.vReturnValue = loRetVal.childnodes(0).Text

ENDIF

*** Check for results that don't return a cursor

lcResult = Extract(THIS.cResponseXML,"<result>","</result>")

IF lcResult = "OK"

RETURN 0

ENDIF

*** Check for server errors returned to the client

loError = loDom.documentElement.selectSingleNode("error")

IF !ISNULL(loError)

THIS.cErrorMsg = loError.selectSingleNode("errormessage").text

THIS.nError = -1

THIS.lError = .T.

RETURN -1

ENDIF

*** OK we have an embedded cursor

*** Force new table instead of appending

IF USED(THIS.cSQLCursor)

SELE (THIS.cSQLCursor)

USE

ENDIF

IF "<VFPData>" $ LEFT(THIS.cResponseXML,100)

*** Use VFP 7's XMLTOCURSOR natively (faster)

XMLTOCURSOR(THIS.cResponseXML,THIS.cSQLCursor)

ELSE

*** Otherwise use wwXML

loSchema = loDom.documentElement.selectSingleNode("Schema")

IF !ISNULL(loSchema)

IF THIS.nResultMode=0

loXML.XMLToCursor(loDOM,THIS.cSQLCursor)

IF loXML.lError

THIS.cErrorMsg = "XML conversion failed: " +loXML.cErrorMsg

RETURN -1

ENDIF

ELSE

*** No cursor to return

RETURN 0

ENDIF

RETURN RECCOUNT()

The full class includes a couple more helper methods but the core is all in the code above (you can get the code from the accompanying source code). As you can see the code is pretty straight forward and just takes advantage of the MSXML parser to quickly see what kind of response is returned from the server then sets right into parsing the XML using XMLTOCURSOR().

On to the server side

If you've examined the client code you can probably figure out how the server side code works. Operation is very similar but in reverse. As I mentioned previously the server component is not tied to a specific Web Development platform other than it must be able to call into VFP code either natively or via COM. Listing 3 uses Web Connection. I’ll describe how to run with ASP/COM at the end of the article.

Listing 3: Setting up the wwHTTPSqlServer server component w/ Web Connection

FUNCTION wwHTTPSQLData()

*** Create Data Object and call Server Side Execute method

SET PROCEDURE TO wwHTTPSQLServer ADDITIVE

loData = CREATE("wwHTTPSQLServer")

loData.cAllowedCommands = "select,execute,insert,method,"

loData.cConnectString = "" && Read data from SQL

*** Pass the XML and execute the command

loData.S_Execute(Request.FormXML())

*** Create the output

loHeader = CREATEOBJECT("wwHTTPHeader")

loHeader.SetProtocol()

loHeader.SetContentType("text/xml")

loHeader.AddForceReload()

loHeader.AddHeader("Content-length",TRANSFORM(LEN(loData.cResponseXML)))

Response.Write( loHeader.GetOutput() )

Response.Write( loData.cResponseXML )

ENDFUNC

* wcDemoProcess :: wwHTTPSQLData

The server side calling code is very simple – it simply calls the S_Execute() method with the incoming XML string (or DOM reference). S_Execute then parses the incoming XML properties into its own object members and then runs the query. S_Execute() is a high level method – if you want more control you can call lower level methods. For example, the following code checks the SQL command after it's been parsed for the occurrence of wws_ which disallows access to the West Wind Store files. This code replaces the call to S_Execute() above:

IF loData.ParseXML()

*** Custom Check - disallow access to Web Store Files

IF ATC("WWS_", loData.cFullSQL) > 0

loData.S_ReturnError("Access to table denied")

ELSE

IF loData.ExecuteSQL()

loData.CreateXML()

ENDIF

Notice that both approaches will return a full XML response regardless of whether the request succeeded or failed. Even if a failure does occur the result is still an XML document. In the latter example, since I have an 'external' error that I myself want to fire, I can call the S_ReturnError method with an error message that I want to return to the client. This generates a consistent XML format for errors to be returned.

As you can see by the lower level methods, in order process requests the server has to perform the following tasks:

Parse the incoming XML request. It does so by storing the XML values to its own properties.
Run the requested SQL command.
Encode the result into XML. Depending on the type of result it encodes the cursor depending on the nTransportMode property, or if an error occurs returns an error XML document.

These task map nicely to the three core methods in the wwHttpSqlServer class shown in Listing 4.

Listing 4: The core methods of the wwHttpSqlServer object

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

* wwHTTPSQLServer :: ParseXML

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

FUNCTION ParseXML(lcXML)

local loXML, lcFullSQL, lcSQL, ;

lcCursorName, lnAt, lcCommand

THIS.lError = .F.

THIS.cErrorMsg = ""

loXML = THIS.oXML

IF VARTYPE(lcXML) = "O"

THIS.oDOM = lcXML

THIS.oDOM.Async = .F.

this.cRequestXml = this.oDom.Xml

ELSE

IF EMPTY(lcXML)

lcXML = REQUEST.FormXML()

ENDIF

THIS.cRequestXML = lcXML

THIS.ODOM = loXML.LoadXML(lcXML)

IF ISNULL(THIS.oDom)

THIS.S_ReturnError("Invalid XML input provided.")

RETURN .F.

enDIF

ENDIF

lcFullSQL = THIS.GetXMLValue("sql")

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

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

lcSQL = LOWER(LEFT(lcFullSQL,10))

lcCursorName = THIS.GetXMLValue("sqlcursor")

IF EMPTY(lcCursorName)

lcCursorName = "THTTPSQL"

ENDIF

THIS.nTransportmode = VAL(THIS.GetXMLValue("transportmode"))

IF THIS.GetXMLValue("noschema") = "1"

THIS.nSchema = 0

ENDIF

IF THIS.GetXMLValue("utf8") = "1"

THIS.lUtf8 = .T.

ENDIF

IF EMPTY(lcSQL)

THIS.S_ReturnError("No SQL statement to process.")

RETURN .F.

ENDIF

*** Check for illegal commands

lnAt = AT(" ",lcSQL)

lcCommand = LOWER(LEFT(lcSQL,lnAt - 1))

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

THIS.S_ReturnError(lcCommand + " is not allowed or invalid.")

RETURN .F.

ENDIF

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

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

lcSQL # "execute" AND lcSQL # "method"

THIS.S_ReturnError("Only SQL commands are allowed.")

RETURN .F.

ENDIF

THIS.cCommand = lcCommand

THIS.cCursorName = lcCursorName

THIS.cFullSQL = lcFullSQL

IF THIS.cConnectString # "NOACCESS"

*** Only allow access if the connection string is not set in

*** the server code already!

IF EMPTY(THIS.cConnectString)

THIS.cConnectString = THIS.GetXMLValue("connectstring")

ENDIF

RETURN .T.

ENDFUNC

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

* wwHTTPSQLServer :: ExecuteSQL

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

FUNCTION ExecuteSQL()

LOCAL llError, lcReturnVar, loSqlParameters, ;

loType, lcType, lvValue, lcMacro,

lcCursorName, lcFullSQL, lcMethodCall, loEval, ;

lcError, lnResultCursors, loSQL, lcCommand

lcReturnVar = ""

loSQLParameters = THIS.GetXMLValue("sqlparameters",2)

*** Check for named parameters

IF !ISNULL(loSQLParameters)

*** Create the variables and assign the value to it

FOR EACH oParm IN loSQLParameters.ChildNodes

loType = oParm.Attributes.GetNamedItem("type")

IF !ISNULL(loType)

lcType = loType.Text

ELSE

lcType = "C"

ENDIF

loReturn =oParm.Attributes.GetNamedItem("return")

IF !ISNULL(loReturn)

lcReturnVar = oParm.NodeName

ENDIF

DO CASE

CASE lcType = "C"

lvValue = oParm.text &&REPLACE VALUE WITH oParm.TEXT

CASE lcType = "N"

lvValue = VAL(oParm.Text)

CASE lcType = "D"

lvValue = CTOD(oParm.Text)

CASE lcType = "T"

lvValue = CTOT(oParm.Text)

CASE lcType = "L"

lvValue = INLIST(LOWER(oParm.Text),"1","true","on")

ENDCASE

lcMacro = oParm.NodeName + "= lvValue"

&lcMacro && Create the variable as a PRIVATE

ENDFOR

*** Once created they can be used as named parameter via ODBC ?Parm

*** or as plain variables in straight Fox Queries

ENDIF

lcCommand = THIS.cCommand

lcCursorName = THIS.cCursorName

lcFullSQL = THIS.cFullSql

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

DO CASE

*** Access ODBC connection

CASE !ISNULL(THIS.oSQL) OR (THIS.cConnectString # "NOACCESS" AND ;

!EMPTY(THIS.cConnectString) )

*** If we don't have a connection object

*** we have to create and tear down one

IF ISNULL(THIS.oSQL)

loSQL = CREATE("wwSQL")

loSQL.cSQLCursor = THIS.cCursorName

IF !loSQL.CONNECT(THIS.cConnectString)

THIS.S_ReturnError(loSQL.cErrorMsg)

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

RETURN .F.

ENDIF

ELSE

*** Otherwise use passed in connection

*** which can be reused

loSQL = THIS.oSQL

loSQL.cSQLCursor = lcCursorName

ENDIF

loSQL.cSkipFieldsForUpdates = THIS.cSkipFieldsForUpdates

THIS.nResultCursors = loSQL.Execute(lcFullSQL)

loSQL.cSkipFieldsForUpdates = ""

IF loSQL.lError

THIS.S_ReturnError(loSQL.cErrorMsg)

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

RETURN .F.

ENDIF

OTHERWISE && Fox Data

IF lcCommand = "select"

lcFullSQL = lcFullSQL + " INTO CURSOR " + lcCursorName + " NOFILTER"

ENDIF

*** Try to map stored procedures to Fox methods of this

*** class with the same name

IF lcCommand = "execute"

poTHIS = THIS

lcFullSQL = "poTHIS." + ParseSQLSPToFoxFunction(lcFullSQL)

endif

THIS.nResultCursors = 1

llError = .f.

TRY

&lcFullSql

CATCH

llError = .t.

ENDTRY

IF llError

THIS.S_ReturnError("SQL statement caused an error." + CHR(13) + lcFullSQL)

SYS(2335,1)

RETURN .F.

ENDIF

ENDCASE

SYS(2335,1)

*** Add the return value if used

IF !EMPTY(lcReturnVar)

THIS.cReturnValueXML = "<returnvalue>" + CRLF + ;

THIS.oXML.AddElement(lcReturnVar,&lcReturnVar,1) +;

"</returnvalue>" +CRLF

ENDIF

RETURN .T.

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

* wwHTTPSQLServer :: CreateXML

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

FUNCTION CreateXML()

LOCAL lcFileText, lcFileName, loHTTP, lcDBF

IF !INLIST(THIS.cCommand,"select","create",;

"execute","method")

*** If no cursor nothing needs to be returned

THIS.S_ReturnOK()

RETURN .t.

ENDIF

lcFileText = ""

IF USED(THIS.cCursorName)

*** Now create the cursor etc.

SELECT(THIS.cCursorName)

LogString(this.cCursorName + TRANSFORM(RECCOUNT()) )

DO CASE

*... other cases skipped for brevity

CASE THIS.nTransportMode = 1

*** VFP7 CursorToXML

lcFileText = ""

CURSORTOXML(ALIAS(),"lcFileText",1,;

IIF(THIS.lUTF8,48,32),;

0,IIF(THIS.nSchema=1,"1","0"))

OTHERWISE

THIS.S_RETURNError("Invalid Transportmode: " +

TRANSFORM(THIS.nTransportmode))

RETURN .F.

ENDCASE

ELSE

*** Force an empty cursor

lcFileText = THIS.oXML.cXMLHeader + ;

"<wwhttpsql>" + CRLF + ;

"</wwhttpsql>" + CRLF

ENDIF

IF !EMPTY(THIS.cReturnValueXML)

lcFileText = STRTRAN(lcFileText,"</wwhttpsql>",

THIS.cReturnValueXML + "</wwhttpsql>")

ENDIF

IF USED(THIS.cCursorName)

USE IN (THIS.cCursorName)

ENDIF

THIS.cResponseXML = lcFileText

RETURN .T.

ParseXml() takes the incoming XML and parses it into the object properties. The ExecuteSql then executes the SQL statement. The method must also deal with named parameters, which it handles by creating PRIVATE variables of the named parameter name and assigning the value to it so that the query that runs later can see those variables. The query itself is run using a Macro inside of a TRY/CATCH block to make sure that any execution errors are trapped.

The server also sets SYS(2335,0) before running the query to avoid user interface errors which can occur when files cannot be found (File open dialogs). Sys(2335) prevents user interface access in COM servers and causes an error to be thrown rather than bringing up a File Open Dialog box for example. Since this is a server application a dialog of this sort would be a big problem hanging the server. Note that this only works in COM so if you’re running a non-COM based VFP server on the Web backend file errors may hang the server.

The ExecuteSql code also deals with stored procedure calls and can even map stored procedure calls against Fox data into method calls on the server object. You can subclass the wwHttpSqlServer object and add methods that match SQL Server stored procedures.

Once the query's been run the CreateXml() method converts the result into an XML string to be returned based on the options the client provided (such as the transport mode, whether the XML is UTF 8 encoded etc.) and sets the cResponseXml property.

Any errors that occur during the processing always call the S_ReturnError() method which also sets the cResponseXML property with XML of a failure message in a specific format. A failure message looks like this:

<?xml version="1.0"?>

<error>

<errormessage>Could not find stored procedure 'sp_ww_NewsId'. [1526:2812]</errormessage>

</error>

</wwhttpsql>

The wwHTTPSql client checks for error messages before trying to do anything else and if an error does occur it sets the lError and cErrorMsg property to return the error safely. Typical wwHttpSql client code should always check for errors by checking the lError flag before going on to use the data.

Dealing with the 255 character literal string limit in VFP

One thing to be aware of is that Visual FoxPro has a 255 literal string limitation which can be a problem for a SQL engine. In short you can’t do something like this:

UPDATE SomeTable set LDescript='<longer than 255 char string>'

So, if you use code like this to build the SQL statement:

lcSql = [UPDATE SomeTable set LDescript='] + lcLDescript + [']

you will hit this problem quickly whenever a string exceeds 255 characters. To get around this we need to use named parameters in the query, which is accomplished by using the AddSqlParameter() of wwHttpSql. Using that method you’d change your SQL code to:

oHSql = CREATEOBJECT("wwHttpSql")

lcDescript = [Some Long String]

lcSQL = oHSql.AddSqlParameter("parmDescript",lcDescript)

oHSql.ExecuteSql([UPDATE SomeTable SET LDescript=parmDescript])

This syntax causes the parameter to be marshaled to the server, which recreates the variable and value on the server side before executing the SQL statement there.

You can also call stored procedures and provide named parameters with the AddSqlParameter which takes a parameter name and value as parameters. The parameters are marshaled to the server, unpacked there and then inserted into the query using typical SQL Passthrough syntax. The following example hits a SQL Server backend and calls a stored procedure shown in Listing 6.

Listing 6: Calling a stored procedure using named parameters over the Web

oHSQL = CREATEOBJECT("wwHTTPSQL")

oHSQL.cServerUrl = "http://localhost/wconnect/wwhttpsql.http"

oHSQL.cSQLConnectString = ;

"driver={sql server};server=(local);database=wwDeveloper; "

oHSQL.cSQLCursor = "TDevelopers"

pnID = 0

pcTablename = "wwDevRegistry"

oHSQL.AddSQLParameter("pnID",pnID,,.T.) && Return this one back

oHSQL.AddSQLParameter("pcTableName",pcTableName)

oHSQL.AddSQLPArameter("pcIDTable","wwr_id")

oHSQL.AddSQLParameter("pcPKField","pk")

*** returns 0

? oHSQL.Execute("Execute sp_ww_NewId ?pcTableName,?@pnID")

*** pnID result value

? oHSQL.vResultValue

*** or explicitly retrieve a return value if there’s more than one

? oHSQL.GetSQLReturnValue("pnID")

Notice that in this example I pass a cSQLConnectString parameter which allows me to specify the connection that is used on the server. I'll talk about this in a moment when I describe the server component. If a connection string is not provided on the server the client may send it and specify which connection to use (you can override this on the server to allow using only a specific connection or not allow ODBC access at all – more on this later).

You can see a few extra parameters set here and the above query generates an XML request sent to the server that looks like this:

<?xml version="1.0"?>

<sql>Execute sp_ww_NewId ?pcTableName,?@pnID</sql>

<sqlcursor>TSQLQuery</sqlcursor>

<sqlconnectstring> driver={sql server};server=(local);database=wwDeveloper;

</sqlconnectstring>

<pctablename type="C">wwDevRegistry</pctablename>

</sqlparameters>

</wwhttpsql>

What about Security

If you’ve been reading through this article so far you may say to yourself: “This is way cool, but it’s also scary as heck! You’re opening up all your data to the Web and there’s little control over what commands you run.” And right you are!

Security comes on several levels, so let’s start with the simple stuff, which is what HTTP natively provides. Let’s start with Authentication (either Windows Auth or Basic Authentication). You can protect the URL you are accessing with Windows security or implement Basic Authentication on the request to allow only specific users access to the data engine. You can use the cUsername and cPassword properties of the wwHTTPSql object to provide the needed credentials.

You can also use Basic Authentication along the same lines. In Web Connection on the server you would simply check for a specific login like this:

*** Check for validation here

IF !THIS.Login("ANY")

RETURN

ENDIF

Where ANY is a special name for any logged in user, but you could also provide a specific list of users (Basic Auth doesn’t work with groups). This authenticates the request before the object is every created to process commands and provides the high level protection.

If you need encrypted data over the wire you can use HTTPS/SSL which is provided by the HTTP protocol. All you need is a certificate on the Web Server and you’re ready to go.

Ok, this covers the easy infrastructure options for security. In addition you can also modify the way the wwHttpSqlServer class operates. You can limit the SQL commands that are allowed with the cAllowedCommands property which accepts a comma delimited list of commands that the server will accept. By default it’s assigned like this:

cAllowedCommands = ",select,insert,update,delete,execute,method,"

but you can simply remove commands from this list. If you don’t want people updating simply leave the SELECT command in there.

You can further use code based logic to decide whether you’ll allow running commands by first parsing the incoming data with ParseXml().To do this you can use the lower level methods instead of S_Execute() as shown in Listing 7.

Listing 7– Checking the parsed SQL for filter criteria to disalllow commands

loData = CREATE("wwHTTPSQLServer")

loData.cAllowedCommands = "select,execute,insert,method,update"

loData.cConnectString = "" && Allow Odbc Access

IF loData.ParseXML(Request.FormXml())

*** Custom ERror Checking - disallow access to West Wind Files

IF ATC("WWS_", loData.cFullSQL) > 0

loData.S_ReturnError("Access to table denied")

ELSE

IF loData.ExecuteSQL()

loData.CreateXML()

ENDIF

You can check any of the object's properties after the ParseXML() method has been called. ParseXML() maps the incoming XML request data to properties of the object so you can easily check these and perform any custom filtering in your code. Here I’m filtering out specific set of tables based on a prefix which is a simplistic example, but you can of course add much more sophisticated logic here.

With this you get the best of both worlds: You can use Windows authentication on the server plus you can provide your own filter criteria to check prior to running a SQL command.

Implementing the wwHttpSqlServer with ASP

In the examples above I used Web Connection to demonstrate operation of the wwHttpSqlServer. As mentioned, the server can run on any VFP enabled platform. For operation under a COM environment you can simply subclass the wwHttpSqlServer class as follows:

Listing 9 – wwHttpSqlServerCom implementation for operation in ASP and ASP.Net

DO wwHttpSqlServer && force libraries to be pulled in

DEFINE CLASS wwHttpSqlServerCOM as wwHttpSqlServer OLEPUBLIC

cAppStartPath = ""

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

FUNCTION INIT

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

*** Function: Set the server's environment. IMPORTANT!

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

*** Make all required environment settings here

*** KEEP IT SIMPLE: Remember your object is created

*** on EVERY ASP page hit!

SET RESOURCE OFF && Best to compile into a CONFIG.FPW

SET EXCLUSIVE OFF

SET REPROCESS TO 2 SECONDS

SET CPDIALOG OFF

SET DELETED ON

SET EXACT OFF

SET SAFETY OFF

*** IMPORTANT: Figure out your DLL startup path

IF application.Startmode = 3 OR Application.StartMode = 5

THIS.cAppStartPath = ADDBS(JUSTPATH(Application.ServerName))

ELSE

THIS.cAppStartPath = SYS(5) + ADDBS(CURDIR())

ENDIF

*** If you access VFP data you probably will have to

*** use this path plus a relative path to get to it!

*** You can SET PATH here, or else always access data

*** with the explicit path

DO PATH WITH THIS.cAppStartpath

DO PATH WITH THIS.cAppStartPath + "wwDemo"

DO PATH WITH THIS.cAppStartPath + "wwDevRegistry"

*** Make sure to call the base constructor!

DODEFAULT()

ENDFUNC

ENDDEFINE

That’s it. This is stock ASP COM server stuff that makes sure we set up the environment and more importantly set the application’s start path so we can find the data we want to have access to. I’m assuming here VFP data files will be found in the DLL directory or wwDemo or wwDevRegistry which is where it lives on my server for demo purpose.

It’s important that you understand the security requirements – ASP will access these files in its security environment, so if an anonymous user hits the site as IUSR_<Machinename> he will likely not have rights to read or write Fox files there. You either will have to add the IUSR_ account to the permissions for the data folders or alternately force a login with the client code using cUsername and cPassword to match an NT authorization account and protecting the ASP page by removing anonymous access.

Compile the above into an MTDLL with:

BUILD MTDLL wwHttpDataService FROM wwHttpDataService RECOMPILE

and test it from the VFP command prompt:

o = CREATE("wwHttpDataService.wwHttpSqlServerCom")

to make sure the server can load. If it does you can add it to an ASP page of your choice:

Listing 10 – Server Implementation for classic ASP

'*** Get the XML input - easiest to load in DOM object

'set oXML = Server.CreateObject("MSXML2.DOMDOCUMENT")

set oXML = Server.CreateObject("MSXML2.FreeThreadedDOMDocument")

oXml.Async = false ' Make sure you read async

oXML.Load(Request)

set loData = Server.CreateObject("wwHttpDataService.wwHttpSqlServerCOM")

'loData.cConnectString = "server=(local);driver={SQL Server};database=wwDeveloper;"

loData.lUtf8 = False

loData.S_Execute(oXML)

'if loData.ParseXml(oXML)

' if loData.ExecuteSql()

' loData.CreateXml()

' end if

'end if

Response.Write(loData.cResponseXML)

'Response.Write(loData.CERRORMSG) ' debug

Notice the use of the XML Free Threaded DOM to pick up the full XML document in the POST buffer. You can simply pass the Request object to the DOM’s Load method and it picks up the entire XML document. For ASP applications you’ll want to use the FreeThreadedDomDocument as this version is thread safe and can work with many simultaneous requests at a time.

As with the VFP code shown earlier, inside the ASP page you can use either S_Execute() or the lower level methods to parse, execute and encode the Xml. In ASP operation use S_Execute if you can to minimize extra COM calls – only use the lower level methods if you need some specific functionality.

All that remains now is to change the client URL to point at this ASP page and you’re ready to go.

ASP classic is good, but that’s old hat now that ASP.Net is out, right? Well, not quite. ASP.Net actually is a bit more complicated than ASP and will actually show considerably worse performance. The problem is that with ASP.Net you have to use Reflection to call methods on the COM object or use TLBIMP to create a wrapper .Net class for the COM object. Furthermore performance of COM object calls is considerably worse with ASP.Net compared to COM as there is the transition from managed code to unmanaged COM code. For a component such as this that doesn’t require much logic and relies on a high performance interface to the COM object ASP is a much better choice.

If you must use ASP.Net you can look at the following article how to import a COM object and load it from an ASP. Net page:

http://www.west-wind.com/presentations/VfpDotNetInterop/aspcominterop.asp

From query to business object

So how do we use this functionality? What I’ve shown so far is a remote Web SQL engine, which is basically a 2 tier setup with your front end application talking to the data engine. This is useful for some things but doesn’t really fit the common multi-tier, business object based model most developers use. But this mechanism can be relatively easily be hooked up to a business object framework.

I’ll use my own wwBusiness class as an example here (note I won’t provide source code for this class, but you will get the general idea from the samples provided here. Let me talk a little about how wwBusiness works so we can get a better base to understand how to hook up the data engine to it.

wwBusiness is a light weight business object class, which provides the basic CRUD (Create, Read, Update, Delete) as well as query services functionality against multiple data sources. Internally the class has methods such as Load, Save, New and Query that basically figure out which type of data source to use and then retrieve or update the data to that data source. The key feature of wwBusiness is its use of an internal oData member to hold record based data. Methods like Load, New and Find populate this member with data – typically from a single record in a cursor using SCATTER NAME. However, the methods can be overridden to create custom types of objects that include more or less information as long as the appropriate classes (Save, Load, GetBlankRecord) are overridden to handle the non-default type of data.

wwBusiness natively supports three data access modes: Local VFP data, SQL Server data and finally Web Data coming from a compatible provider. The Web provider is wwHttpSqlData. Let’s see how hooking up this provider is accomplished.

So, rather than using wwHttpSql directly to issue SQL commands we can let the business object framework do it for us. In essence, wwBusiness wraps the functionality of wwHttpSql on the client side. The server side for wwHttpSqlServer doesn't change in this scenario – only the client is now wwBusiness rather than straight application code. Figure 2 show the flow of this configuration.

Figure 2 – Using wwHttpSql wwBusiness can access Web Data Sources by using the wwHttpSql as a proxy to a Web data source.

To make this possible the wwBusiness object class is equipped with some additional parameters and another datamode. The new parameter is cServerUrl that specifies the URL to connect to (much like a connection string for SQL Server). Listing 8 shows wwBusiness accessing data through the wwHttpSql provider.

Listing 8: Using wwBusiness with a Web data source

oDev = CREATEOBJECT("cDeveloper")

oDev.nDataMode = 4 && Web wwHttpSql

oDev.cServerUrl = "http://localhost/wconnect/wc.dll?http~HTTPSQL_wwDevRegistry"

*** Execute a raw SQL statement against the server

odev.Execute("delete wwDevregistry where pk = 220")

IF oDev.lError

? oDev.cErrorMsg

ENDIF

*** Run a query that returns a cursor

lnRecords = oDev.Query("select * from wwDevRegistry where company > 'L' ")

IF oDev.lError

? oDev.cErrorMsg

ELSE

BROWSE

ENDIF

*** Load one object

oDev.Load(8)

? oDev.oData.Company

? oDev.oData.Name

oDev.oData.Company = "West Wind Technologies"

IF !oDev.Save()

? oDev.cErrorMsg

ENDIF

*** Create a new record

? oDev.New()

loData = oDev.oData

loData.Company = "TEST COMPANY"

loData.Name = "Rick Sttrahl"

? oDev.Save()

*** Show added rec

? oDev.Query()

GO BOTT

BROWSE

What’s interesting here is that the code shown here is no different than it would be if it were talking to a Fox table or a SQL Server database. The only difference are the nDataMode and the cServerUrl properties which now point at the Web data source. Assuming the data is there, and the URL is running wwHttpDataSqlServer on the other end you can very easily take an existing application and run it with a Web data source!!! Isn’t that pretty cool?

Ok, you might need some additional code if you want to log in, provide a specific timeout for the data or use a proxy server. In this case you'd have to add:

*** Optional - configure any HTTP settings you need using wwHTTP properties

oDev.Open()

oDev.oHTTPSQL.cUsername = "rick"

oDev.oHTTPSQL.cPassword = "keepguessingbuddy"

oDev.oHTTPSQL.nConnectTimeout = 40

oDev.oHTTPSQL.nTransportMode = 0 && Use wwXML style

The Open() method doesn’t actually open anything but it does create the wwHttpSql object reference that is used for the communication with the server. So once the object exists you can configure any of the properties of that object, such as username and password, timeout, proxy settings etc.

If you want to set up a wwHttpSQL object only once rather than do this on each request you can persist the object and simply assign it to the oHttpSql property of the wwBusiness object subclass.

oDev.oHttpSql = THISFORM.oPersistedHttp

oDev.oHttpSql.nConnectTimeout = 40

This is useful if you have Proxy configuration or authentication settings to make – you don't want to reassign these each time.

The wwBusiness also supports passing the object settings down to any child objects you create using the CreateChildObject() method which passes the oHttpSql or oSql properties to child objects to maintain the same connection settings for any embedded objects so you don't have to reconfigure any objects from within the business object itself.

Hooking up to the wwBusiness object

So how does this work? How does wwBusiness use the wwHttpSql object? Take a look – here's the full Load method that retrieves a record into the oData member. The code in listing 9 shows all three data access mechanisms so you can easily compare how they each work.

Listing 9: The wwBusiness object Load() method with Web access support (4)

* wwBusiness.Load

LPARAMETER lnpk, lnLookupType

LOCAL loRecord, lcPKField, lnResult

THIS.SetError()

IF VARTYPE(lnpk) # "N"

THIS.SetError("Load failed - no key passed.")

RETURN .F.

ENDIF

*** Load(0) loads an empty record

IF lnPK = 0

RETURN THIS.Getblankrecord()

ENDIF

IF !THIS.OPEN()

RETURN .F.

ENDIF

DO CASE

CASE THIS.ndatamode = 0

lcPKField = THIS.cPKField

LOCATE FOR &lcPKField = lnpk

IF FOUND()

SCATTER NAME THIS.oData MEMO

IF THIS.lcompareupdates

SCATTER NAME THIS.oOrigData MEMO

ENDIF

THIS.nUpdateMode = 1 && Edit

ELSE

SCATTER NAME THIS.oData MEMO BLANK

IF THIS.lcompareupdates

SCATTER NAME THIS.oOrigData MEMO BLANK

ENDIF

THIS.SetError("GetRecord - Record not found.")

RETURN .F.

ENDIF

CASE THIS.ndatamode = 2 OR This.nDataMode = 4

IF this.nDataMode = 4

loSQL = this.oHttpSql

ELSE

loSql = loSql

ENDIF

lnResult = loSQL.Execute("select * from " + THIS.cFileName + " where " + ;

THIS.cPKField + "=" + TRANSFORM(lnpk))

IF lnResult # 1

IF loSql.lError

THIS.SetError(loSql.cErrorMsg)

ENDIF

RETURN .F.

ENDIF

IF RECCOUNT() > 0

SCATTER NAME THIS.oData MEMO

IF THIS.lcompareupdates

SCATTER NAME THIS.oOrigData MEMO

ENDIF

THIS.nUpdateMode = 1 && Edit

ELSE

SCATTER NAME THIS.oData MEMO BLANK

IF THIS.lcompareupdates

SCATTER NAME THIS.oOrigData MEMO BLANK

ENDIF

THIS.SetError("No match found.")

RETURN .F.

ENDIF

ENDCASE

RETURN .T.

Notice that in this code the VFP mode simply does a LOCATE and SCATTER name, while the SQL and Web versions (2 and 4) run a SELECT statement that they SCATTER on if the data is found. Notice that the code for the SQL and Web versions are nearly identical. And for good reason – SQL Server access is handled through the wwSQL class which has same basic query interface (Execute()) and return values as does the Execute() method on wwHttpSql. These two are almost interchangeable and in fact they are using the same shared codesnipped to load a record.

Let's look at another more complex example – the Save method which writes the content of the current entry back to the database in Listing 10.

Listing 10: The wwBusiness :: Save() method

LOCAL lcPKField, llRetVal, loRecord

llRetVal = .T.

THIS.SetError()

*** Optional auto Validation

IF THIS.lValidateOnSave AND ;

!THIS.VALIDATE()

RETURN .F.

ENDIF

loRecord = THIS.oData

IF !THIS.OPEN()

RETURN .F.

ENDIF

DO CASE

CASE THIS.ndatamode = 0

DO CASE

CASE THIS.nupdatemode = 2 && New

APPEND BLANK

GATHER NAME loRecord MEMO

THIS.nupdatemode = 1

CASE THIS.nupdatemode = 1 && Edit

lcPKField = THIS.cPKField

LOCATE FOR &lcPKField = loRecord.&lcPKField

IF FOUND()

GATHER NAME loRecord MEMO

ELSE

APPEND BLANK

GATHER NAME loRecord MEMO

ENDIF

ENDCASE

CASE THIS.ndatamode = 2 OR THIS.nDataMode = 4

IF THIS.nDataMode = 2

loSQL = THIS.oSQL

ELSE

loSQL = THIS.oHTTPSql

ENDIF

DO CASE

CASE THIS.nupdatemode = 2 && New

loSQL.cSQL = THIS.SQLBuildInsertStatement(loRecord)

loSQL.Execute()

IF loSQL.lError

THIS.SetError(loSQL.cErrorMsg)

RETURN .F.

ENDIF

THIS.nupdatemode = 1

CASE THIS.nupdatemode = 1 && Edit

*** Check if exists first

loSQL.Execute("select " +THIS.cPKField +" from " + THIS.cFileName +;

" where " + THIS.cPKField + "=" + TRANS(loRecord.pk))

IF loSQL.lError

THIS.SetError(loSQL.cSQL)

RETURN .F.

ENDIF

IF RECCOUNT() < 1

loSQL.Execute( THIS.SQLBuildInsertStatement(loRecord) )

ELSE

loSQL.Execute( THIS.SQLBuildUpdateStatement(loRecord) )

ENDIF

IF loSQL.lError

THIS.SetError(loSQL.cErrorMsg)

RETURN .F.

ENDIF

ENDCASE

RETURN llRetVal

Note again that the SQL and Web data source use exactly the same code path – even here in the business object/data access layer code no changes were required because the wwHttpSql client matches the existing interface of wwSql.

The key thing that happens here is that an INSERT or UPDATE statement is automatically generated from the oData member using the SqlBuildInsertStatement method. This method runs through each of the properties in the oData member and creates an INSERT or UPDATE statement from those fields with literal values embedded.

A similar approach is taken to various other methods in the business object. The end result is that the business object now can use a remote data source over the Web without requiring any code changes and it took very little code to make this happen.

Where’s the Remote?

Having a remote data engine is extremely handy. When thinking about distributed applications we often wonder exactly how to integrate data from remote sources. While there are different ways to accomplish this task using local logic and using the server only to push data down to you can be very efficient. It also provides you with all the business logic on the desktop as opposed to logic that’s tied up on the server in a Web Service. Another advantage to this approach is that you’re shipping only the data over the wire here – no SOAP envelopes and SOAP parsing to worry about. As far as VFP to VFP (or VFP -> VFP -> Sql Server) communication is concerned it’s very efficient.

But keep this concept firmly in mind: If you use only the data service from your applications without a business object layer you’re using a two-tier environment. If you add the business object you are building a distributed application with ALL of your application logic on the client with the server acting only as a data service. It’s a slightly different approach to distributed applications, but one that in many ways is more flexible and easy to work with than having logic sitting tied up on the server.

Next time you think about creating a Web Service think about how much easier it is to simply query some data from a server over the Web into your application! It’s not a solution that fits all distributed scenarios, but it’s a great solution for porting existing applications and for quick, down and dirty data requests to a server.

As always if you have any questions or comments you can contact me on our message board at:

http://www.west-wind.com/wwThreads/Default.asp?Forum=White+Papers