Beginning with ActiveX Data Objects (ADO) 2.0, you could persist a recordset (i.e., save it to physical media) on a client computer by using the new Save method of ADO recordsets. This Save method causes the recordset to be fully retrieved from the server computer and then writes the data to a local file in Advanced Data Table Gram (ADTG) format. With ADO 2.1, you now have the option of persisting recordset data in Extensible Markup Language (XML) format on the client computer. XML is the new standard for cross-platform data interchange, and, with the advent of XML-persisted recordsets, you gain new flexibility in writing client/server and cross-platform data applications. This article will detail how to persist and reload XML data into ADO recordsets and will offer observations on how you might use the technology in your application development.

Making an XML Recordset

First things first: You need to ensure that you have the latest copy of Microsoft Data Access Components (MDAC) installed on your machine. MDAC can be obtained at the Microsoft Universal Data Access Web site at www.microsoft.com/data, and Version 2.5 was the currently supported release as of the writing of this article. Next, go to the Midrange Computing Web site at www.midrangecomputing.com/mc and download the Visual Basic (VB) project associated with this article. There is more code to this article than we can print in the magazine, so make sure to download the VB project to get all of the code. The figures referenced in the article are the most relevant code in the VB Project.

Once you have the code, open the project in VB and subsequently open Form1. Double-click the form and change the arguments to the Con1.Open method of the Con1 connection object to reflect the name of the ODBC data source configured to connect to your AS/400. The form has three buttons labeled Make Table, Read Table, and Open XML. Each button is tied to subroutines that are executed with the press of that button.

Double-click the Make Table button to view the code associated with that button. This code connects to your AS/400, creates a table in the target library (the target library is the value of form field Text1), and inserts records into the created table. This table will act as the source for your XML test.

Double-click the Read Table button to view the code associated with its click event. (The code is shown in Figure 1.) This code will create an SQL Select statement to read the data from your AS/400 table, open an ADO recordset object, and then write retrieved

records to a persistent XML file. To save the recordset, the program uses the Save method of the ADO recordset object Rs. The Save method takes two arguments: the name of a file to write the data to and the format to use when writing the data. My program uses the following line:

Rs.Save “XMLTEST.XML”, adPersistXML

This code is instructing ADO to save all of the records in the recordset into a file called XMLTEST.XML and to save the records in the format adPersistXML. After the line of code is completed, you should have a file on your hard disk that contains the information shown in Figure 2. The program then closes the recordset and exits the subroutine.

XML Exposed!

Now that the program has created a persistent recordset, let me take a moment to look at what a persistent recordset is. A persistent recordset in XML is written to the XML Data format as specified by a submission to the World Wide Web Consortium. (The specification for this format can be viewed at the W3C XML-Data Web site at www.w3.org/TR/1998/NOTE-XML-data.) The format specifies that an XML Data document contains a declaration of namespaces, a schema section, and a data section. The namespaces let you define tags that will be used in the XML document. The schema section lets you define the characteristics of the data and objects that are contained in the data section. Finally, the data section contains all of the data for the exposed recordset.

Looking at Figure 2, you can see that the area of the figure shown with the orange background is the schema portion of the XML recordset, the white background indicates the namespace portion of the recordset, and the green background indicates the data portion of the document. Because XML is designed to be a language that is easy to read and interpret, you should have no problems interpreting the recordset information defined in Figure 2.

Loading a Saved XML Recordset

Loading an XML document back into an ADO recordset is simplicity itself. Double-clicking the Open XML button of Form1 reveals the code shown in Figure 3. The first line of code, indicated by Rs.Open, calls the Open method of the recordset Rs. The Open method takes two arguments: the name of the file to open and an active connection to use to open the file. In this case, providing the string “Provider=MSPersist;” informs the recordset that it is to open a physical file by using the MSPersist OLE DB provider. MSPersist will parse the XML file and instantiate Rs as a recordset containing all of the rows that the program previously saved. The argument adOpenKeyset tells the provider to open a keyset-type cursor, and the argument adLockBatchOptimistic makes the recordset editable. This is useful if you want to change the records via the available ADO methods or if the recordset is to be used with a complex-bound data control such as the data grid control.

The program next shows Form2 and sets the MyRs recordset, declared as a public ADO recordset in Form2, to be a clone of the Rs recordset. The program then sets the DataSource property of dGrid, a database grid object on Form2, to MyRs, causing the contents of the recordset to be drawn on the grid. You are now free to edit the contents of the XML recordset via the grid control, and pressing the Save Results button on Form2 will cause any modifications of the recordset to be written back to the XMLTEST.XML file.

In the Future

Microsoft has recently released ADO 2.5. It adds even more features to persistent recordsets. Primarily, you are able to specify an Istream object as the destination of the saved recordset. An Istream object could be a physical file name, or it could be the

response object available in Internet Information Server (IIS). By saving to the response object, you will be able to stream XML information to client browsers. This holds a lot of promise for Web development under IIS.

How can you use this technique in your shop? You have seen how little code it takes to make an XML document of recordset data, so think of the possibilities for mobile-client applications. You could, for instance, create an SQL Select statement to retrieve data to a client PC and then save the data as a persistent store. Later, when the client program would need the data, you wouldn’t need to communicate with the server for record retrieval. I have used this technique for commonly used data such as lists of salespeople or products. I have a facility that checks whether or not updates have been made to these lists on the AS/400, and, if the lists have been changed, I re-download the data store to persistent XML. This data staging strategy reduces network bandwidth and makes the application appear to be much faster to the users.

You could also use persistent stores to allow the user to download data, make changes to the data offline, reconnect the recordset, and then use the UpdateBatch method to marshal the changes back to the data source. XML is also emerging as the new data interchange standard for cross-platform data transfers. Using persistent stores gives you a cheap and easy way to make XML exports of the results of SQL statements. I’m sure that, if you play with the technology, you can find a lot of uses for it!

Private Sub Command2_Click()
‘make the sql statement
Cmd1.CommandText = “select * from “ & _

Text1.Text & “.XMLTEST”
‘set the cachesize
Rs.CacheSize = 100
‘open the recordset
Rs.Open Cmd1
‘delete the file, if it exists
On Error Resume Next
Kill “XMLTEST.XML”
‘save the recordset to xml format
Rs.Save “XMLTEST.XML”, adPersistXML
‘close the recordset
Rs.Close
End Sub

Today

Figure 1: This code executes an SQL statement and saves the resulting recordset in a persistent XML file.

xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>


rs:fixedlength='true'/>



Figure 2: An XML Data document contains a declaration of namespaces, a schema section, and a data section.

Private Sub Command3_Click()
'Open the xml file as a recordset
Rs.Open "XMLTEST.XML", "Provider=MSPersist;", _
adOpenKeyset, adLockBatchOptimistic
'show form 2
Form2.Show
'set form2's recordset = a clone of rs recordset
Set Form2.MyRs = Rs.Clone
'set the grids datasource = to recordset myrs
Set Form2.dGrid.DataSource = Form2.MyRs
'close the rs recordset
Rs.Close
End Sub

Figure 3: This code reads the saved XML document back into a recordset object and binds the recordset to an editable grid.

Also Read

Open Source Starter Guide for IBM i Developers: Language Basics

Open Source Starter Guide for IBM i Developers: Ruby and IBM i, An Introduction

Open Source Starter Guide for IBM i Developers: i Object!