Package org.apache.xalan.lib.sql

Provides extension functions for connecting to a JDBC data source, executing a query, and working incrementally through a "streamable" result set.

See:
Description

Interface Summary

ConnectionPool

This interface specification is used to define how Xalan will interact with a Connection Pool.

Class Summary

Column	A JDBC Query returns a resultSet that contains rows of Data.
ColumnAttribute	Represents a column attribute on a column-header element.
ColumnData	Represents the col element text node, i.e., the column value.
ColumnHeader	The ColumnHeader is a special branch of the document that provides a mechinsim to interogate columns that are returned from the query without accessing the data or moving the row counter.
DefaultConnectionPool	The default connection pool was implemented so that all connection to JDBC data sources use connection pools.
ExtensionError	**Experimental**
PooledConnection	A special container that wraps a standard JDBC Connection that is used to identify the connection inside the Connection Pool.
QueryParameter	The Query Parameter is a container class that is used to hold the Parameter information.
Row	This class represents a row from a query result set.
RowSet	The row-set is the controlling element in the Document that determines if the JDB ResultSet is being traversed or if the ResultSet MetaData is being interogated.
SQLExtensionError	An Extension of the Extension Error Class that adds information about the SQL Exception.
StreamableNode	The StreamableNode really just provides a base implemtation for the other SQL Node based classes.
XConnection	The XConnection Object is the main (currently the only) interface into the SQL Extensions from the Transformer.
XConnectionPoolManager	The ConnectionPoolManager provides a static container that allows external programs, inside the same JVM, to supply a set of JDBC connections to the Xalan transformer.
XStatement	Represents a JDBC query statement.

Package org.apache.xalan.lib.sql Description

Provides extension functions for connecting to a JDBC data source, executing a query, and working incrementally through a "streamable" result set.

The SQL Extension operates in one of two modes: streaming or cached.

In streaming mode, a single row object is allocated. The contents of this object are updated as the row element set is traversed. Accordingly, the result set can only be traversed once moving forward. Consider using streaming mode when the result set is large and memory is an issue. The number of records returned does not affect the memory footprint.

In cached mode, the result set can be traversed forward or backward as many times as desired. The downside: memory is consumed as the result set is traversed.

Currently, the default is cached mode. To set the mode, use the enableCacheNodes() or disableCahcedNodes() method.

**Experimental** This package is experimental. Streaming mode is not fully implemented and may not return the entire result set. If you do use streaming mode, be careful not to attempt to traverse the result set in any manner other than a single traversal from start to finish.

XConnection provides three extension functions that you can use in your stylesheet.

XConnection

1. new() -- Use one of the XConnection constructors to connect to a data source, and return an XConnection object. You can use one of the constructors creates a connection pool from which stylesheets can obtain connections to a data source. To support connection pools, SQL library includes a Connection-Pool interface and a implementation: DefaultConnectionPool. You can also provide your own Connection-Pool implementation.
   
   
2. query() -- Use the XConnection object query() method to return a "streamable" result set in the form of a row-set node. Work your way through the row-set one row at a time. The same row element is used over and over again, so you can begin "transforming" the row-set before the entire result set has been returned.
   
   
3. pquery(), addParameter(), addParameterFromElement(), clearParameters() -- Use the XConnection pquery() method in conjunction with these other methods to set up and execute parameterized queries.
   
   
4. enableCacheNodes(), disableCacheNodes() -- Use these XConnection methods to cache the result set or use a single row element to stream through the result set.
   
   
5. close() -- Use the XConnection object close() method to terminate the connection.

The query() or pquery() extension function returns a Document node that contains (as needed) an array of column-header elements, a single row element that is used repeatedly, and an array of col elements. Each column-header element (one per column in the row-set) contains an attribute (ColumnAttribute) for each of the column descriptors in the ResultSetMetaData object. Each col element contains a text node with a textual representation of the value for that column in the current row.

XML Sample of the return Data

This example displays the result set from a table in a sample InstantDB database.

  <Column-header attribute=value>
    One column header entry for each column in the query.
    Each column header contains a set of attributes describing the MetaData
    for that column. For a list of possible attributes see ColumnAttribute.java.
    ColumnAttribute
  <Column-header>

  <row-set>

      <row>
          In Streaming Mode, Caching Disabled, only one row object is returned.
          The benefit of streaming mode is that the memory footprint is the same
          regardless of the size of the query. A downside to streaming mode is that
          the row elements can ONLY BE TRAVERSED ONCE. In cached mode, there
          is a new row element for each row in the result set. The row elements can
          be traversed as many times as needed.
          Currently the default mode is cached, streaming mode is experimental
          and currently does not work 

          <col>Value attribute=value </col>
            One column entry for each column in the query.
            Each column contains a set of attributes describing the MetaData
            for that column. Foa a list of possible attributes see ColumnAttribute.java.
          <col attribute=value >Value</col>
          <col attribute=value >Value</col>
      </row>

  </row-set>

Example

This example displays the result set from a table in a sample InstantDB database.

<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                version="1.0"
                xmlns:sql="org.apache.xalan.lib.sql.XConnection"
                extension-element-prefixes="sql">
  <xsl:output method="html" indent="yes"/>
  <xsl:param name="query" select="'SELECT * FROM import1'"/>

  <xsl:template match="/">
    <!-- 1. Make the connection -->
    <xsl:variable name="products"
                  select="sql:new('org.enhydra.instantdb.jdbc.idbDriver',
                                'jdbc:idb:D:\instantdb\Examples\sample.prp')"/>
    <HTML>
      <HEAD>
      </HEAD>
      <BODY>
        <TABLE border="1">
        <!--2. Execute the query -->
        <xsl:variable name="table" select='sql:query($products, $query)'/>
          <TR>
          <!-- Get column-label attribute from each column-header-->
          <xsl:for-each select="$table/row-set/column-header">
            <TH><xsl:value-of select="@column-label"/></TH>
          </xsl:for-each>
          </TR>
          <xsl:apply-templates select="$table/row-set/row"/>
          <xsl:text>&#10;</xsl:text>
        </TABLE>
      </BODY>
    </HTML>
    <!-- 3. Close the connection -->
    <xsl:value-of select="sql:close($products)"/>
  </xsl:template>

  <xsl:template match="row">
        <TR>
          <xsl:apply-templates select="col"/>
        </TR>
  </xsl:template>

  <xsl:template match="col">
    <TD>
      <!-- Here is the column data -->
      <xsl:value-of select="text()"/>
    </TD>
  </xsl:template>

</xsl:stylesheet>

Handling of SQL Errors and other exceptions in this extension

**Experimental** This Extension enhancement is extremelly experimental and is being used to test its validity.

The SQL Extension package provides a mechinism to capture errors that occur as a result of an SQL Exception.

The document that is returned provides the Exception information along with the SQL Error and SQL State. To detect if an error occured, /ext-error can be evaluated using the xsl:test function. A value of true reflects that an error ha occured. See the sample code in java/samples/extension/sql/show-error for a working example.

  

  
Sample of an Error Return in the event of an SQL Error
  <ext-error>
    <exception-info>
      <message> Message from the Exception thrown </message>
        <sql-error>
          <error-code> The SQL Error Code returned by the driver</error-code>
          <state> The Current SQL Connection State </state>
        </sql-error>
    </exception-info>
  </ext-error>