Composing a ResultSet XML document from the SQL data

You can use Java methods to evaluate a given query and generate an XML result set with the query’s data. This example uses a constructor method of the ResultSetXml class. For example:

new jcs.xml.resultset.ResultSetXml
 	(“select 1 as ‘a’, 2 as ‘b’, 3 ”, “none”,
 	“yes”, “external”, “antibes:4000?user=DBA&password=SQL”);

The method uses internal JDBC operations to execute the argument query, and then constructs the XML ResultSet for the query’s data.

We can invoke this constructor in a client or in the Sybase IQ:

• If you invoke the constructor on a client, specify a server parameter that identifies the IQ server to use when evaluating the query. The query is evaluated on Sybase IQ, but the XML document is assembled on the client.

• If you invoke the constructor on Sybase IQ, specify a null value or jdbc:default:connection for the server. The query is evaluated on the current server and the XML document is assembled there.

The main( ) method of the OrderResultSetClient class is invoked in a client environment. main( ) invokes the constructor of the ResultSetXml class to generate an XML ResultSet. The constructor executes the query, retrieves its metadata and data using JDBC ResultSet methods, and assembles an XML ResultSet document with the data.

import java.io.*;
import jcs.util.*;
public class OrderResultSetClient {
    public static void main (String[] args) {
       try{
 		String orderQuery = "select order_date as Date,
 				c.customer_id as CustomerId, "
 			+ "customer_name as CustomerName, "
 			+ "o.item_id as ItemId, i.item_name as ItemName, "
 			+ "quantity as Quantity, o.unit as unit "
 			+ "from customers c, orders o, items i "
 			+ "where c.customer_id=o.customer_id and
 				o.item_id=i.item_id " ;

		jcs.xml.resultset.ResultSetXml rsx 
 				= new jcs.xml.resultset.ResultSetXml(orderQuery,
 				"none", "yes", "external",
 				"antibes:4000?user=DBA&password=SQL");
          FileUtil.string2File("OrderResultSet.xml",
 				rsx.toString());

	} catch (Exception e) {
          System.out.println("Exception:");
          e.printStackTrace();
       }
    }
 }

The following SQL script invokes the constructor of the ResultSetXml class in a server environment:

create variable rsx jcs.xml.resultset.ResultSetXml;
set rsx = new jcs.xml.resultset.ResultSetXml
 	('select 1 as ''a'', 2 as ''b'', 3 ', 'none', 'yes', 'external', '');