Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Introducing the CursorAdapter Class : Page 7

One of the most exciting new features of Visual FoxPro 8 is the CursorAdapter class, which provides a common interface for working with data from many different sources. Chuck takes you with him on an adventure in exploring how to use CursorAdapter to change the way you relate to data in VFP 8, whether native tables, ODBC, OLE DB, or XML.


CursorAdapter with XML
Last, but not least, let's build a CursorAdapter that uses XML as its data source. This scenario is interesting, since an XML document doesn't normally act as a data source. Also, the CursorAdapter does not automatically build SQL Update, Insert or Delete statements when the data source is set to XML. Therefore, this type of CursorAdapter will require the most coding to retrieve and update data.

In this example, I will use the SQLXML feature of SQL Server 2000 to provide an XML document. Also, since SQLXML supports updating via XML, we'll take the time to write the necessary code to perform updates. This assumes that you have configured SQLXML to allow HTTP data access to the Northwind database, and that you are allowing updates to the database with UpdateGrams.

In my case, I have set up IIS to use a virtual directory called "nwind" for HTTP access. Therefore, all of my examples will contain URLs that reference

to access SQLXML via IIS.

Let's start by creating a new program called caXML.prg with the following basic class definition:

   PUBLIC oCAXML as CursorAdapter 
   SET MULTILOCKS ON && need for table buffering
   DEFINE CLASS xcXML AS CursorAdapter
     DataSourceType = "XML"
     Alias = "xmlCursor"
     UpdateCmdDataSourceType = "XML"
     InsertCmdDataSourceType = "XML"
     DeleteCmdDataSourceType = "XML"
     BufferModeOverride = 5 
     *custom properties
     oXMLDOM = NULL 
     cServer = "localhost"
     cVDir = "nwind"
Beyond the common DataSourceType and Alias property settings, this is the first time we've seen the xxxCmdDataSourceType properties. Since this is an XML-based CursorAdapter, these properties are not optional if you want it to be updatable. The custom properties oXMLHTTP and oXMLDOM become object references used throughout the class, and will be detailed below.

Retrieving XML Data
Before thinking about the updatability of the CursorAdapter, let's concentrate on retrieving a document from the SQLXML server. First, since a simple Select command will not work, we have to establish a custom SelectCmd. This is easily done in the Init method, where we will also invoke the CursorFill method, as follows:

   FUNCTION INIT() as Boolean 
     LOCAL llRetVal, lcMsg, laErr[1]
     this.SelectCmd = "this.GetXml()"
     llRetVal = THIS.CursorFill()
     IF NOT llRetVal THEN 
       lcMsg = "Cursor was not filled!" 
       IF NOT EMPTY(laErr[2]) THEN 
         lcMsg = lcMsg + CHR(13) + laErr[2]
       MESSAGEBOX(lcMsg,16,"XMLCursorAdapter Test")
     RETURN llRetVal 
This code establishes the SelectCmd as a local method instead of a SQL Select command. While this hasn't been done in the previous examples, this is perfectly legal for any CursorAdapter class, regardless of the type. However, when you use a local method as the SelectCmd, you will have to also provide custom code for your Update, Insert and Delete commands, since VFP won't be able to automatically handle something that is not a SQL Select command.

When we invoke CursorFill in the Init(), the GetXML method is called. With the data source set to XML, the GetXML method must return a valid XML document that contains only a single table. If it contains multiple tables, you will get unexpected results. The GetXML method is shown in Listing 4.

GetXML starts by getting a reference to an MSXML2.XMLHTTP COM object. This object handles all of the communication across HTTP, including sending the queries to the server and retrieving the results. As you can see, the instantiation of the oXMLHTTP object is controlled via the provided Access method, designed to prevent the constant creation and destruction of this COM server.

Next, you can see our typical Select statement, except that the LIKE clause is a little different. HTTP requires that we "escape" the percent sign with the hex value of the character, forcing us to expand it to "%25". This value will be "collapsed" to the single percent sign character before SQL Server receives the query.

After that, the code sets up the URL with the specified query and sends the URL to SQL Server via HTTP. SQL Server receives the query, processes it, and returns the result as XML because we've included the FOR XML clause on the query. The root element of the XML document is named "results" in this example. As you can see from the query string, this is configurable to your liking.

At this point, lcRetXML contains an XML stream from the SQL Server. Since the GetXML method was invoked by VFP as the SelectCmd, you can simply return the contents of this variable from the GetXML method and VFP will convert the stream into a VFP cursor. You can test this by executing the caXML program. A browse window should appear with the contents of the returned XML document. Feel free to use the debugger to step through the GetXML method so you can see the XML document in the lcRetXML variable before it is converted to a VFP cursor and discarded.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date