Persisting Result Sets as XML Data Files

DataDirect Connect® Series ODBC drivers (DataDirect Connect for ODBC and DataDirect Connect64 for ODBC) include a feature that enables you to retrieve result sets from a relational database (such as Oracle, Informix, DB2, or Sybase) and store the result sets as XML data files (see following figure). This can be accomplished by simply adding one or two lines of code to existing ODBC applications.

The ability to persist result sets as XML data files helps you take advantage of XML faster as you adopt advanced B2B and B2C business processes on the Web. DataDirect Technologies provides a simple way for you to use existing data and publish it on the Web. You do not have to replicate the data or manually reformat it—you just add one or two lines of code to your existing ODBC application and an XML data file is created.

XML allows you to use the power of the Web to provide up-to-date information to your colleagues within the same organization, your outside partners, and your customers. For example, instead of e-mailing a text-formatted report to sales representatives in the field, you can publish daily sales information in an XML report on a secure Web page, which is accessible to a PC, a laptop, or a handheld device.

Creating XML from data stored in a relational database offers a way for you to:

• Quickly publish information (such as up-to-date price lists or current inventory totals) to the Web
• Allow customers to interact with the data you publish on the Web

In this scenario, a wholesaler decides to provide an online catalog over the Web. The wholesaler chooses XML for the format of the catalog because XML provides semantics to the online catalog information that will allow the customer to do business with the wholesaler online.

The wholesaler's catalog information is stored natively in an Oracle relational database inside the wholesaler's Internet firewall. Through an ODBC application using a DataDirect Connect Series ODBC Oracle driver, the wholesaler retrieves the data from the relational database and publishes that information to the Web Server as an XML data file in the form of an online catalog. To do this, the ODBC application issues a SQL Select statement, for example:

Once the wholesaler has published the catalog on the Web as an XML data file, the customer can view the catalog using any XML-enabled browser. More important, customers can deal with the wholesaler more efficiently by using applications to interact with the catalog just as if they had direct access to the wholesaler's relational database.

The wholesaler develops and distributes to his customers an ODBC application that:

• Connects to a URL that specifies the wholesaler's XML catalog
• Checks the price and availability of any item in the wholesaler's catalog
• Places the order if the item is in stock

In addition, the customer could develop custom applications to interact with the wholesaler's catalog, applications that fit the exact needs of the customer.

In this scenario, both the wholesaler and consumer benefit by the wholesaler's catalog being published to the Web using XML. And, DataDirect Connect Series ODBC drivers provide a way to persist data easily from a relational database to XML.

This section provides step-by-step instructions for implementing XML persistence in an ODBC application using a DataDirect Connect for ODBC Driver.

The ODBC application must:

1. Turn on STATIC cursors. For example:

   SQLSetStmtAttr (hstmt, SQL_ATTR_CURSOR_TYPE,

   SQL_CURSOR_STATIC, SQL_IS_INTEGER)
   
   

   NOTE: A result set can be persisted as an XML data file only if the result set is generated using STATIC cursors.

 

2. Execute a SQL statement. For example:

   SQLExecDirect (hstmt, "Select * from GTABLE", SQL_NTS)



3. Persist the result set as an XML data file. For example:

   SQLSetStmtAttr (hstmt, SQL_PERSIST_AS_XML,

   "c:\temp\GTABLE.XML", SQL_NTS)
   
   

   NOTE: A statement attribute is available to support XML persistence, SQL_PERSIST_AS_XML. A client application must call SQLSetStmtAttr with this attribute as an argument. See the following table for the definition of valid arguments for SQLSetStmtAttr.

An ODBC application can persist the data at any time that the statement is in an executed or cursor-positioned state.

DataDirect Connect Series ODBC drivers include the ability to persist result sets as XML data files for the leading databases: Oracle, Informix, DB2, and Sybase. The benefit to you is that you can quickly publish information to the Web and allow customers to interact with the data you publish. Currently, DataDirect Technologies provides the only ODBC drivers on the market that provide this functionality.