PREVNEXTINDEX

7 Using the SQL/XML JDBC Driver and JDBC API Extensions

This chapter describes the SQL/XML JDBC driver and the classes it uses to process SQL/XML queries. It also provides information about connecting to the database and using SQL/XML queries in Java applications.

The SQL/XML JDBC driver translates SQL/XML statements into database-specific statements that do not contain any XML operators. The driver uses either a DataDirect Connect for JDBC driver or the DataDirect SequeLink for JDBC driver to communicate with the database.

Because the result of a SQL/XML operator is an XML-typed column and because the current JDBC specification does not support an XML data type, the SQL/XML JDBC driver introduces a new type and associated Java class to represent and access XML-Typed columns. This Java class is com.ddtek.jdbc.jxtr.XMLType.

Driver and Data Source Classes

The driver class for the SQL/XML JDBC driver is:

com.ddtek.jdbc.jxtr.JXTRDriver

The data source class for the SQL/XML JDBC driver is:

com.ddtek.jdbc.jxtr.JXTRDataSource

SQL/XML JDBC Driver Data Source

Because a SQL/XML JDBC driver data source embeds a Connect for JDBC data source or a SequeLink for JDBC data source, constructing a SQL/XML data source (com.ddtek.jdbc.jxtr.JXTRDataSource) requires you to pass a pre-constructed data source to the SQL/XML data source constructor.

Your Connect for SQL/XML installation contains examples that show how to create and use Connect for SQL/XML data sources. These examples are installed in the examples/src/examples/datasource directory in your Connect for SQL/XML installation directory. You can use these examples as templates for creating a SQL/XML data source that meets your needs.

Connection URLs

The syntax of URLs for the SQL/XML JDBC driver is:

jdbc:datadirect:jxtr:
	db://
	hostname:port[;
	conn_properties]

where:

dbis one of the following values: db2, informix, oracle, sqlserver, sybase, or sequelink. The values db2, informix, oracle, sqlserver, and sybase indicate that the SQL/XML JDBC driver uses an underlying Connect for JDBC driver for a database connection. The value sequelink indicates that the SQL/XML JDBC driver uses the SequeLink JDBC driver for a database connection.

hostname is the TCP/IP address or TCP/IP host name of the server to which you are connecting.

portis the number of the TCP/IP port.

conn_propertiesis a semicolon-separated list of connection properties for the SQL/XML JDBC driver and the DataDirect Technologies JDBC driver you are using for your connection. See "Connection Properties" for information about connection properties supported for the SQL/XML JDBC driver. Refer to the Connect for JDBC or SequeLink documentation for information about connection properties supported by each DataDirect Technologies JDBC driver.

For example:

jdbc:datadirect:jxtr:db2://server1:50000;DatabaseName=SAMPLE;
PackageName=JDBCPKG;binaryEncoding=hex

or

	jdbc:datadirect:jxtr:sequelink://189.23.5.132:19996;
databaseName=stores7;timeStampEncoding=iso8601

Connection Properties

The SQL/XML JDBC driver embeds either one Connect for JDBC driver or the SequeLink JDBC driver. All connection properties supported by these drivers also are supported by the SQL/XML JDBC driver. Refer to the Connect for JDBC or SequeLink documentation for information about supported connection properties.

Table 7-1 lists connection properties that are specific to the SQL/XML JDBC driver, and describes each property. The properties have the form:

property=value

NOTE: All connection property names are case-sensitive. For example, binaryencoding is different than binaryEncoding.

Table 7-1. SQL/XML JDBC Connection Properties 
Property
Description
binaryEncoding
OPTIONAL
binaryEncoding={base64 | hex}. Specifies the type of binary-to-string conversion to use when retrieving binary information from the database. The default value is base64.
nullReplacementValue
OPTIONAL
Sets the value to replace NULL values that are retrieved from the database (for example, nullReplacementValue=no value available). If no value is specified, NULL values are not replaced by another value.
timestampEncoding
OPTIONAL
timestampEncoding={odbc | iso8601}. Specifies the type of timestamp-to-string conversion to be used when representing timestamp values in an XML document. The default value is odbc, which uses the standard ODBC encoding as specified in the ODBC specification. Timestamps are converted to a string with the following format mask: YYYY-MM-DD HH:MI:SS[.ffffff].
The value, iso8601, uses an ISO standard for the timestamp-to-string conversion. The format mask used is YYYY-MM-DDTHH:MI:SS[.ffffff].

Using Connect for SQL/XML Hints

Connect for SQL/XML supports some hints (options or optimizations) that are not supported by the JDBC API. Hints are set by adding comments to your SQL/XML query.

Table 7-2 lists Connect for SQL/XML hints that are supported in SQL/XML queries.

Table 7-2. Connect for SQL/XML Hints 
Hint
Description
binary_encoding
Specifies the type of binary-to-string conversion to use when retrieving binary information from the database. Valid values are hex and base64 (default).
key_expr
Specifies a select expression that uniquely identifies each of the rows retrieved from the database. Multi-part keys are supported by specifying more than one key_expr value pair in the hint (for example, key_expr=c.CustId; key_expr=c.Name).
Because a SQL/XML query can contain nested queries, you can specify multiple sets of keys by prefixing each of the queries with a set of key_expr hints. See "Using key_expr Hints" for more information about the key_expr hint.
null_replacement
Sets the value to replace NULL values that are retrieved from the database. If no value is specified, NULL values are not replaced by another value.
rewrite_algorithm
Specifies the way in which the SQL/XML JDBC driver translates the SQL/XML query into one or more statements that the underlying database supports. Valid values are nested_loop and sorted_outer_join.
The default is sorted_outer_join.
See "Using rewrite_algorithm Hints" for more information about rewrite algorithms.
timestamp_encoding
Specifies the type of timestamp-to-string conversion to be used when retrieving timestamp data from the database. Valid values are iso8601 and odbc.
The default is odbc.

The following example shows how to specify SQL/XML hints in a SQL/XML query.

/*{jxtr-hints timestamp_encoding=iso8601;null_replacement=not applicable; */ 
SELECT 
  XMLELEMENT(name empinfo, 
     XMLATTRIBUTES(e.EmpId as "id", e.FirstName as "first", 
     e.LastName as "last", e.StartDate as "start", e.EndDate as "end")) 
FROM Employees e

For more examples of hints, see the examples shipped with the product in the examples/src/examples/sqlxml directory in your Connect for SQL/XML installation directory.

com.ddtek.jdbc.jxtr Java Package

In a JDBC programming environment, you use the com.ddtek.jdbc.jxtr Java package included with Connect for SQL/XML to perform the following tasks:

The com.ddtek.jdbc.jxtr package contains:

See Chapter 8 "Using the jXTransformer API" for information about the jXTransformer API and the com.ddtek.jxtr Java package.

XMLType Class

For SQL/XML queries that return XML data in the result set, your Java application must use the getObject method on the XML data and cast the retrieved object to com.ddtek.jdbc.jxtr.XMLType. The XMLType class has different methods to instantiate the actual XML document or document fragment. These methods are:

All of these methods, except generateSAX and writeXML, require the complete instantiation of the XML document or document fragment on the client side. Depending on the size of the generated XML, this can cause memory usage or performance issues.

JXTRDataSource Class

Because a SQL/XML JDBC driver data source embeds either a Connect for JDBC data source or a SequeLink for JDBC data source, constructing a SQL/XML data source requires you to pass a pre-constructed data source to the SQL/XML data source constructor. You use the methods in the JXTRDataSource class to accomplish this task. You also can set any of the connection properties defined in Table 7-1 using this class.

JXTRStatementFactory Interface

The methods of this interface let you process jXTransformer statements when you are using a SQL/XML JDBC driver connection in a Java application.

For more information about the com.ddtek.jdbc.jxtr package, see the Javadoc shipped with Connect for SQL/XML.

Connecting to the Database

Once Connect for SQL/XML is installed and your application is using the SQL/XML JDBC driver, you can connect from your application to your database in either of the following ways:

You can connect through the JDBC Driver Manager with the DriverManager.getConnection method. This method uses a string containing a URL.

The following list provides a summary of the steps required to connect to the database using a connection URL. After this list, each step is described in more detail.

  1. Set your CLASSPATH to include the DataDirect Technologies JDBC driver you are using for the connection and the jxtr.jar file. The CLASSPATH is the search string your Java Virtual Machine (JVM) uses to locate the jar files on your computer.
  2. Register the driver.
  3. Pass the driver's connection URL.
  4. Test the connection.

1. Setting the Classpath

The jar files that must be defined in your CLASSPATH variable depend on whether you are using a Connect for JDBC driver or the SequeLink for JDBC driver. If the files are not defined on your CLASSPATH, you will receive the a class not founderror when trying to load the driver.

DataDirect Connect for JDBC Drivers

Set your system CLASSPATH to include the following entries, where driver.jar is the driver jar file (for example, sqlserver.jar) and install_diris the path to your Connect for SQL/XML installation directory:

install_dir/lib/base.jar  
install_dir/lib/util.jar  
install_dir/lib/driver.jar 
install_dir/lib/jxtr.jar

Windows Example

CLASSPATH=
.;c:\CfSQLXML\lib\base.jar;c:\CfSQLXML\lib\util.jar;
c:\CfSQLXML\lib\sqlserver.jar;c:\CfSQLXML\lib\jxtr.jar

UNIX Example

CLASSPATH=.;/home/user1/CfSQLXML/lib/base.jar;/home/user1/
CfSQLXML/lib/util.jar;/home/user1/CfSQLXML/lib/
sqlserver.jar;/home/user1/CfSQLXML/lib/jxtr.jar

DataDirect SequeLink JDBC Driver

Set your system CLASSPATH to include the following entries where install_diris the path to your Connect for SQL/XML installation directory:

	install_dir/lib/sljc.jar 
	install_dir/lib/jxtr.jar

Windows Example

CLASSPATH=.;c:\CfSQLXML\lib\sljc.jar;c:\CfSQLXML\lib\
jxtr.jar

UNIX Example

CLASSPATH=
.;/home/user1/CfSQLXML/lib/sljc.jar;/home/user1/CfSQLXML/ 
lib/jxtr.jar

2. Registering the Driver

Registering the SQL/XML JDBC driver tells the JDBC Driver Manager which driver to load. One way to register the driver is to explicitly load the driver class using the standard Class.forName() method and call DriverManager.getConnection().

The class of the SQL/XML JDBC driver is:

com.ddtek.jdbc.jxtr.JXTRDriver

For example:

Class.forName("com.ddtek.jdbc.jxtr.JXTRDriver");

3. Passing the Connection URL

After registering the SQL/XML JDBC driver, you can pass your database connection information using a connection URL. For example, to specify a connection URL for the SQL/XML JDBC driver that uses the Connect for JDBC SQL Server driver:

Connection conn = DriverManager.getConnection 
("jdbc:datadirect:jxtr:sqlserver://server1:1433;User=test;
Password=secret;binaryEncoding=hex");

See "Connection URLs" for more information about the connection URL used for the SQL/XML JDBC driver.

4. Testing the Connection

To test your connection to the database, you can use the DataDirect Query Builder for SQL/XML, a tool provided with Connect for SQL/XML for creating and modifying Connect for SQL/XML queries. You can create your own Connect for SQL/XML query for testing or use one of the example Connect for SQL/XML queries in the examples/src/examples directory in the Connect for SQL/XML installation directory. For instructions on using the DataDirect Query Builder for SQL/XML to connect to the database, see "Connecting to the Database".

Using SQL/XML Queries in Java Applications

NOTE: To compile and run Java applications that use SQL/XML queries, you must add the appropriate jar files to your classpath. For information about the jar files you need to add, refer to the DataDirect Connect for SQL/XML Installation Guide.

Typically, a Java application containing a SQL/XML query performs the following tasks:

  1. Connects to the database using the SQL/XML JDBC driver
  2. Prepares the SQL/XML query in a String object
  3. Executes the SQL/XML query
  4. Retrieves data from the result set
  5. Creates an XML document or document fragment (optional)

The following example shows a SQL/XML query coded in a Java application. For more examples of SQL/XML queries that demonstrate specific SQL/XML features and functions in a Java application, refer to the example files in the examples/src/example/sqlxml directory in the Connect for SQL/XML installation directory.

/*  
 * 
---------------------------------------------------------------------------- 
 * Copyright(c) 2003 DataDirect Technologies. All rights reserved.           
 *                                                                           
 * This product includes Xerces, developed by the Apache Software            
 * Foundation (http://www.apache.org). Copyright (C) 1999-2000 The Apache    
 * Software Foundation.  All rights reserved.                                
 *                                                                           
 * This product includes Xalan, developed by the Apache Software             
 * Foundation (http://www.apache.org). Copyright (C) 1999-2000 The Apache    
 * Software Foundation.  All rights reserved.                                
 *                                                                           
 * This product includes JDOM, developed by the JDOM Project                 
 * (http://jdom.org). Copyright (C) 2001 Brett McLaughlin & Jason Hunter.    
 * All rights reserved.                                                      
 * 
 * Description: 
 * 
 * SQL/XML Example4: 
 * Demonstrates the following hints for SQL/XML queries 
 * (a) null replacement hint. 
 * (b) timestamp encoding hint. 
 * 
 * The example can be invoked either through the Example class or by invoking 
 * the main in this class. 
 * 
 * See the accompanying readme file for more information on the  
 * provided examples. 
 * 
 * 
---------------------------------------------------------------------------- 
 */ 
package examples.sqlxml; 
 
import java.io.OutputStreamWriter; 
import java.sql.*; 
 
import com.ddtek.jdbc.jxtr.*; 
import examples.*; 
 
public class SQLXMLExample4 extends Example 
{ 
 
  /** 
   * Executes the 'SQL/XML Example4' example 
   */ 
  public void execute() throws Exception 
  { 
    // Output example description 
    System.out.println( "=================================" ); 
    System.out.println( "= SQL/XML Example4 demonstrates =" ); 
    System.out.println( "=================================" ); 
    System.out.println( "  the following hints for SQL/XML queries." ); 
    System.out.println( "  (a) null replacement hint." ); 
    System.out.println( "  (b) timestamp encoding hint." ); 
 
    // Load properties from the resource 
    loadProperties(); 
 
    // Create the SQL/XML JDBC connection 
    connectWithConnectForSQLXML(); 
 
    // Build SQL/XML query 
    StringBuffer sqlXml = new StringBuffer(); 
    sqlXml.append ( "--{jxtr-hints null_replacement=NA;key_expr=
e.EmpId;timestamp_encoding=odbc " ); 
    sqlXml.append ( lineSeparator ); 
    sqlXml.append ( "select " ); 
    sqlXml.append ( "  xmlelement (NAME \"employee\", " ); 
    sqlXml.append ( "    xmlattributes(e.EmpId as \"id\"), " ); 
    sqlXml.append ( "    xmlelement(NAME \"names\", " ); 
    sqlXml.append ( "      xmlelement(NAME \"first\", e.FirstName), " ); 
    sqlXml.append ( "      xmlelement(NAME \"last\", e.LastName)), " ); 
    sqlXml.append ( "    xmlelement(NAME \"hire-dates\", " ); 
    sqlXml.append ( "      xmlattributes(e.StartDate as \"start\",e.EndDate 
as \"end\") "); 
    sqlXml.append ( "  )) as SQLXMLCOL1 " ); 
    sqlXml.append ( "from Employees e where e.EmpId = 12 " ); 
 
    // Output result header 
    System.out.println( "---------------------------------------" ); 
    System.out.println( "Query result (via getString on XMLType)" ); 
    System.out.println( "---------------------------------------" ); 
 
    // Execute the SQL/XML query 
    resultSet = stmt.executeQuery(sqlXml.toString()); 
 
    // Retrieve data from the resultset 
    while(resultSet.next()) 
    { 
		XMLType xmlType = (XMLType)resultSet.getObject(1); 
		System.out.println(xmlType.getString()); 
    } 
    resultSet.close(); 
             
    // Close SQL/XML JDBC connection 
    disconnect(); 
  } 
 
  /** 
   * Main method. 
   */ 
  public static void main ( String[] args ) throws Exception 
  { 
    Example thisDemo = new SQLXMLExample4();     
    thisDemo.execute(); 
  } 
}

PREVNEXTINDEX