14
Mon, Oct
6 New Articles

The XML Features of ADO

Web Languages
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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.


BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: