non-releases/trunk_before_flattening/src/documentation/src/content/xdocs/c21-block-databases-sql-transformer/content_en.xdoc - cocoon - Git at Google

 <?xml version="1.0" encoding="UTF-8"?>
 <!--
   Copyright 1999-2004 The Apache Software Foundation

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
 -->
 <!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V1.0//EN" "document-v10.dtd">

 <document>
   <header>
     <title>SQL Transformer</title>
     <version>$Id$</version>
     <authors>
       <person name="Sven Beauprez" email="Sven.Beauprez@the-ecorp.com"/>
       <person name="Davanum Srinivas" email="dims@yahoo.com"/>
     </authors>
   </header>
   <body>
     <s1 title="Introduction">
       <p>
         The purpose of the SQLTransformer is to query a database and translate
         the result to XML. To retrieve the information from the database, you
         are not restricted to use simple SQL statements (e.g. select, insert,
         update), it is also possible to use stored procedures. In combination
         with other transformers (e.g. FilterTransformer), this one can be very
         powerful.
       </p>
       <ul>
         <li>Name: sql</li>
         <li>Class: org.apache.cocoon.transformation.SQLTransformer</li>
         <li>Cacheable: no</li>
       </ul>
     </s1>
     <s1 title="Basic functionality">
       <p>
         To be able to query a database, we need XML that describes exactly what
         we want to do. The general structure of this input XML is as follows:
       </p>
       <source>
       <![CDATA[
   <page>
     <sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
       <sql:query>
       <!-- here comes the SQL statement or stored procedure -->
       </sql:query>
     </sql:execute-query>
   </page>
    ]]></source>
       <p>
         Nothing prevents you from putting other XML around the
         <code>execute-query</code> element. Any element not in the SQL namespace
         will stay untouched. The format of the SQL statement or the stored
         procedure is exactly the same as if you would call it directly from java
         with a prepared statement or a callable statement.
       </p>
       <p>The query element has the following optional attributes:</p>
       <ol>
         <li>
           <strong>name</strong>:
           Naming a query implicates naming the corresponding rowset (see below).
           When you have a sequence of queries you want to execute, it can be
           handy give them a name. To process the retrieved data of a certain
           query, you can use another transformer to check the name of the rowset
           and to execute the necessary business logic on it.
           <br/>
           usage: <code>&lt;sql:query name="myName"&gt;</code>
         </li>
         <li>
           <strong>isstoredprocedure</strong>:
           When you want to use stored procedures, you have to explicitly add
           this attribute to the query element. By default, the transformer
           assumes that you want to execute a SQL statement.
           <br/>
           usage: <code>&lt;sql:query isstoredprocedure="true"&gt;</code>
         </li>
       </ol>
       <p>Here is an example of how the input XML might look like:</p>
       <source>
       <![CDATA[
   <page>
    <title>Hello</title>
    <content>
     <para>This is my first Cocoon page filled with sql data!</para>
     <sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
      <sql:query name="department">
        select id,name from department_table
      </sql:query>
     </sql:execute-query>
    </content>
   </page>
    ]]></source>
       <p>
         You can use the file generator to retrieve the XML from the filesystem.
         To invoke the SQLTransformer you have to add following to the sitemap:
       </p>
       <source>
       <![CDATA[
   <map:transform type="sql">
     <map:parameter name="use-connection" value="personnel"/>
     <map:parameter name="show-nr-of-rows" value="true"/>
     <map:parameter name="clob-encoding" value="UTF-8"/>
   </map:transform>
    ]]></source>
       <p>
         The <code>use-connection</code> parameter defines which connection,
         defined under the datasources element in <code>cocoon.xconf</code>, the
         SQLTransformer has to use to retrieve the data.
       </p>
       <p>
         The <code>show-nr-of-rows</code> instructs the transformer to count the
         number of rows in the resultset explicitly and to set the result as
         attribute to the rowset element. This attribute is only useful in
         combination with a sql statement, not with stored procedures. If a
         stored procedure returns a resultset and you want to know how many rows
         it contains, you have to count the number of rows in another transformer
         or your stored procedure has to return it also (last solution is the
         best one).
       </p>
       <p>
         The <code>clob-encoding</code> parameter defines what encoding should be
         used in getting content from CLOB columns.
       </p>
       <p>The output XML will look as follows:</p>
       <source>
       <![CDATA[
   <page>
    <title>Hello</title>
    <content>
     <para>This is my first Cocoon page filled with sql data!</para>
     <sql:rowset nrofrows="2" name="department"
                 xmlns:sql="http://apache.org/cocoon/SQL/2.0">
       <sql:row>
         <sql:id>1</sql:id>
         <sql:name>Programmers</sql:name>
       </sql:row>
       <sql:row>
         <sql:id>2</sql:id>
         <sql:name>Loungers</sql:name>
       </sql:row>
     </sql:rowset>
    </content>
   </page>
    ]]></source>
       <p>
         If you use this in combination with the <code>simple-sql2html.xsl</code>
         stylesheet,
       </p>
       <source>
       <![CDATA[
   <map:transform src="stylesheets/simple-sql2html.xsl"/>
    ]]></source>
       <p>you will get a more visually attractive page.</p>
       <p>See below for a more in depth example with stored procedures.</p>
       <p>
         By now you should be able to use the SQLTransformer, but there are some
         more options you might find useful...
       </p>
     </s1>
     <s1 title="Advanced functionality">
       <s2 title="Substitution">
         <p>
           Sometimes you need more information before you can execute a query,
           e.g. the name of the user that is currently logged on your site. This
           information is only available at runtime and hence can only be
           substituted in the query when available.
         </p>
         <p>
           To pass this information to the SQL statement, the input XML has to
           look like this:
         </p>
         <source>
         <![CDATA[
   <page>
     <sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
       <sql:query>
         select id,name from employee_table where name =
             '<sql:substitute-value name="username"/>'
       </sql:query>
     </sql:execute-query>
   </page>
      ]]></source>
         <p>
           The substitution is done by the SQLTransformer before it executes the
           query (before it calls the method <code>prepareStatement</code>!). For
           this, the transformer has to be given the necessary values via the
           sitemap (as parameter):
         </p>
         <source>
         <![CDATA[
   <map:transform type="sql">
     <map:parameter name="use-connection" value="personnel"/>
     <map:parameter name="show-nr-of-rows" value="true"/>
     <map:parameter name="username" value="Stefano Mazzocchi"/>
   </map:transform>
      ]]></source>
         <p>
           Whenever the transformer encounters a <code>substitute-value</code>
           element for which the attribute <code>name</code> contains the value
           <code>username</code>, it will replace this element with the value
           <code>Stefano Mazzocchi</code>.
         </p>
         <p>The output XML will be as follow:</p>
         <source>
         <![CDATA[
   <page>
     <sql:rowset nrofrows="1" xmlns:sql="http://apache.org/cocoon/SQL/2.0">
       <sql:row>
         <sql:id>2</sql:id>
         <sql:name>Stefano Mazzocchi</sql:name>
       </sql:row>
     </sql:rowset>
   </page>
      ]]></source>
         <p>
           It is also possible to use substitution in combination with stored
           procedures.
         </p>
       </s2>
       <s2 title="Ancestors">
         <p>This functionality is best described by a simple example.</p>
         <p>Take following input XML:</p>
         <source>
         <![CDATA[
   <page>
     <sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
       <sql:query name="department">
         select id, name from department_table
       </sql:query>
       <sql:execute-query>
         <sql:query name="employee">
           select id, name from employee_table where department_id =
               <sql:ancestor-value name="id" level="1"/>
         </sql:query>
       </sql:execute-query>
     </sql:execute-query>
   </page>
      ]]></source>
         <p>
           The first query will retrieve all <code>id</code>'s and
           <code>name</code>'s from the <code>department_table</code> table. For
           each <code>id</code> that comes from the
           <code>department_table</code>, the second query, in which the
           <code>ancestor-value</code> element will be replaced by the
           <code>id</code>, will be executed. The above example will be
           transformed to the following XML:
         </p>
         <source>
         <![CDATA[
   <page>
     <sql:rowset nrofrows="2" name="department"
                 xmlns:sql="http://apache.org/cocoon/SQL/2.0">
       <sql:row>
         <sql:id>1</sql:id>
         <sql:name>Programmers</sql:name>
         <sql:rowset nrofrows="2" name="employee">
           <sql:row>
             <sql:id>1</sql:id>
             <sql:name>Donald Ball</sql:name>
           </sql:row>
           <sql:row>
             <sql:id>2</sql:id>
             <sql:name>Stefano Mazzocchi</sql:name>
           </sql:row>
         </sql:rowset>
       </sql:row>
       <sql:row>
         <sql:id>2</sql:id>
         <sql:name>Loungers</sql:name>
         <sql:rowset nrofrows="1" name="employee">
           <sql:row>
             <sql:id>3</sql:id>
             <sql:name>Pierpaolo Fumagalli</sql:name>
           </sql:row>
         </sql:rowset>
       </sql:row>
     </sql:rowset>
   </page>
      ]]></source>
       </s2>
       <s2 title="in- and out-parameters">
         <p>
           Stored procedures can return data as a parameter. To make use of this
           functionality in java, you have to register these parameters as
           <em>out parameters</em>. Since this information is application
           specific, the SQLTransformer uses reflection to retrieve the data in
           the right format. For this, an extra element is needed in the input
           XML:
         </p>
         <source>
         <![CDATA[
   <sql:out-parameter nr="1"
                      name="code"
                      type="java.sql.Types.INTEGER"/>
      ]]></source>
         <p>where:</p>
         <ol>
           <li>
             <strong>nr</strong>:
             The targeted parameter number that will return data of a certain
             type.
           </li>
           <li>
             <strong>type</strong>:
             The type of data that will be returned (defined in
             <code>java.sql.Types</code> or in database specific drivers, e.g.
             <code>oracle.jdbc.driver.OracleTypes</code>). Once the stored
             procedure returns data in the parameters, the stored procedure tries
             to process them. If the returned parameter is an instance of
             <code>ResultSet</code>, it will be translated to XML as we saw
             before. In all the other situations the SQLTransformer will convert
             the parameter to a string.
           </li>
         </ol>
         <p>
           This is an example of how to call an oracle stored procedure and
           process it with the SQLTransformer:
         </p>
         <source>
         <![CDATA[
   <page>
     <sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
       <sql:query isstoredprocedure="true" name="namesearch">
         begin QUICK_SEARCH.FIND_NAME('<sql:substitute-value
             name="username"/>',?,?,?); end;
       </sql:query>
       <sql:out-parameter nr="1" name="code"
                          type="java.sql.Types.INTEGER"/>
       <sql:out-parameter nr="2" name="nrofrows"
                          type="java.sql.Types.INTEGER"/>
       <sql:out-parameter nr="3" name="resultset"
                          type="oracle.jdbc.driver.OracleTypes.CURSOR"/>
     </sql:execute-query>
   </page>
      ]]></source>
         <p>
           The SQLTransformer will create 3 elements, respectively
           <code>code</code>, <code>nrofrows</code> and <code>resultset</code>
           under the element <code>namesearch</code>. Since the type
           <code>oracle.jdbc.driver.OracleTypes.CURSOR</code> corresponds to a
           <code>ResultSet</code>, a <code>rowset</code> element will be created,
           containing all the data of the resultset. It is also possible to use
           an <em>in-parameter</em> element, e.g.
           <code>&lt;sql:in-parameter nr="1" value="1"/&gt;</code>. This
           functionality is only provided to be complete, because it is available
           in Java itself. You can also use the <em>in-parameter</em> in
           combination with a SQL statement. Used in combination with an
           <em>out-parameter</em>, a <em>?-parameter</em> can be an
           <em>in-parameter</em> and an <em>out-parameter</em> at the same time.
         </p>
       </s2>
     </s1>
     <s1 title="Combined with other transformers">
       <s2 title="FilterTransformer">
         <p>
           When you query a database and it returns too many rows to process at
           once, you might want to take a block of elements, process this block
           and ignore the rest for now. You can best compare it to a search on
           Google: they only return 10 results in one time, for more results you
           have to click on another block (page). It wouldn't be wise to process
           more than 10 elements in the pipeline if you only need to display 10
           elements.
         </p>
         <p>
           Assume that a query returns 56 row elements (by using the
           SQLTransformer) and that you only want to display the first 10
           elements:
         </p>
         <p>Output XML from the SQLTransformer:</p>
         <source>
         <![CDATA[
   <sql:rowset nrofrows="56" name="test"
               xmlns:sql="http://apache.org/cocoon/SQL/2.0">
     <sql:row>
       <!-- db record -->
     </sql:row>
     <sql:row>
       <!-- db record -->
     </sql:row>

     ...

     <sql:row>
       <!-- db record -->
     </sql:row>
   </sql:rowset>
      ]]></source>
         <p>
           By adding following lines to the sitemap, just under the
           SQLTransformer, you restrict the results to 10 elements in the first
           block:
         </p>
         <source>
         <![CDATA[
   <map:transform type="filter">
     <map:parameter name="element-name" value="row"/>
     <map:parameter name="count" value="10"/>
     <map:parameter name="blocknr" value="1"/>
   </map:transform>
      ]]></source>
         <p>output XML:</p>
         <source>
         <![CDATA[
   <sql:rowset nrofrows="56" name="test"
               xmlns:sql="http://apache.org/cocoon/SQL/2.0">
     <block id="1">
       <sql:row>
         <!-- db record -->
       </sql:row>

       <!-- total of 10 rows -->

       <sql:row>
         <!-- db record -->
       </sql:row>
     </block>
     <block id="2"/>
     <block id="3"/>
     <block id="4"/>
     <block id="5"/>
     <block id="6"/>
   </sql:rowset>
      ]]></source>
         <p>
           To make it more dynamically, put something like
           <code>{reqCount}</code> and <code>{reqBlock}</code> in the values for
           <em>count</em> and <em>blocknr</em> respectively. These can be
           parameters from the request and they can be passed to the sitemap with
           an action.
         </p>
         <p>
           The FilterTransformer is a standalone component; you don't need to use
           it in combination with the SQLTransformer.
         </p>
       </s2>
       <s2 title="WriteDOMSessionTransformer">
         <p>
           If you only use the FilterTransformer in combination with the
           SQLTransformer, you have to query the database each time the user
           wants to see another part of the result. You can better store the
           result in the session after the first request and retrieve the result
           from the session for the subsequent requests. This can be done by
           using a selector, which checks if the data is available in the session
           or not.
         </p>
         <p>
           WriteDOMSessionTransformer can build a DOM starting from a given
           element (which will be the root of the DOM tree) and store it in the
           session. If you want to store the result of a query, you have to add
           following to the sitemap:
         </p>
         <source>
         <![CDATA[
   <map:transform type="writeDOMsession">
     <map:parameter name="dom-name" value="DBresult"/>
     <map:parameter name="dom-root-element" value="rowset"/>
   </map:transform>
      ]]></source>
         <p>
           The transformer will build a DOM tree with <code>rowset</code> as root
           element and will store it in the session with the name
           <code>DBresult</code>.
         </p>
         <note>
           Most of the times, it is not smart to keep the output XML of the
           SQLTransformer in the session. Check if it is better to do the
           necessary transformations first, so that you get a smaller DOM, and
           then put the result in the session. You probably will be able to use
           the FilterTransformer on the transformed XML also.
         </note>
         <p>
           The WriteDOMSessionTransformer is a standalone component, you don't
           need to use it in combination with the SQLTransformer.
         </p>
       </s2>
       <s2 title="ReadDOMSessionTransformer">
         <p>
           Simply transforms a DOM to SAX events, which can be used further on in
           the pipeline. Once you stored the result of a query in the session
           with the WriteDOMSessionTransformer, you can read it again with the
           ReadDOMSessionTransformer:
         </p>
         <source>
         <![CDATA[
   <map:transform type="readDOMsession">
     <map:parameter name="dom-name" value="DBresult"/>
     <map:parameter name="trigger-element" value="users"/>
     <map:parameter name="position" value="after"/>
   </map:transform>
      ]]></source>
         <p>
           In this example the SAX events, that come from the DOM tree stored in
           the session with name <code>DBresult</code>, will be added after the
           <code>users</code> element. This means as soon that the transformer
           encounters the end element <code>users</code>, it will start to
           generate SAX events from the DOM tree. There are three possible
           positions, <code>before</code>, <code>in</code> and
           <code>after</code>:
         </p>
         <ol>
           <li>
             <strong><code>before</code></strong> means that when the transformer
             encounters the <code>users</code> element, it will FIRST translate
             the DOM tree to SAX events and THEN it will continue to forward the
             other SAX events (starting with <code>users</code>).
           </li>
           <li>
             <strong><code>in</code></strong> means that the transformer will
             forward the start element event for <code>users</code> and that it
             IMMEDIATELY starts to generate SAX events from the DOM tree. After
             that, it will continue to forward the child elements of users and
             then all the other elements.
           </li>
           <li>
             <strong><code>after</code></strong> means that the transformer
             starts to generate SAX events from the DOM tree just after it has
             forwarded the end element <code>users</code>.
           </li>
         </ol>
         <p>
           The ReadDOMSessionTransformer is a standalone component, you don't
           need to use it in combination with the WriteDOMSessionTransformer.
         </p>
       </s2>
       <p>That's it,</p>
       <p>Sven Beauprez</p>
     </s1>
   </body>
 </document>
	<?xml version="1.0" encoding="UTF-8"?>
	<!--
	Copyright 1999-2004 The Apache Software Foundation

	Licensed under the Apache License, Version 2.0 (the "License");
	you may not use this file except in compliance with the License.
	You may obtain a copy of the License at

	http://www.apache.org/licenses/LICENSE-2.0

	Unless required by applicable law or agreed to in writing, software
	distributed under the License is distributed on an "AS IS" BASIS,
	WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
	See the License for the specific language governing permissions and
	limitations under the License.
	-->
	<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V1.0//EN" "document-v10.dtd">

	<document>
	<header>
	<title>SQL Transformer</title>
	<version>$Id$</version>
	<authors>
	<person name="Sven Beauprez" email="Sven.Beauprez@the-ecorp.com"/>
	<person name="Davanum Srinivas" email="dims@yahoo.com"/>
	</authors>
	</header>
	<body>
	<s1 title="Introduction">
	<p>
	The purpose of the SQLTransformer is to query a database and translate
	the result to XML. To retrieve the information from the database, you
	are not restricted to use simple SQL statements (e.g. select, insert,
	update), it is also possible to use stored procedures. In combination
	with other transformers (e.g. FilterTransformer), this one can be very
	powerful.
	</p>
	<ul>
	<li>Name: sql</li>
	<li>Class: org.apache.cocoon.transformation.SQLTransformer</li>
	<li>Cacheable: no</li>
	</ul>
	</s1>
	<s1 title="Basic functionality">
	<p>
	To be able to query a database, we need XML that describes exactly what
	we want to do. The general structure of this input XML is as follows:
	</p>
	<source>
	<![CDATA[
	<page>
	<sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
	<sql:query>
	<!-- here comes the SQL statement or stored procedure -->
	</sql:query>
	</sql:execute-query>
	</page>
	]]></source>
	<p>
	Nothing prevents you from putting other XML around the
	<code>execute-query</code> element. Any element not in the SQL namespace
	will stay untouched. The format of the SQL statement or the stored
	procedure is exactly the same as if you would call it directly from java
	with a prepared statement or a callable statement.
	</p>
	<p>The query element has the following optional attributes:</p>
	<ol>
	<li>
	<strong>name</strong>:
	Naming a query implicates naming the corresponding rowset (see below).
	When you have a sequence of queries you want to execute, it can be
	handy give them a name. To process the retrieved data of a certain
	query, you can use another transformer to check the name of the rowset
	and to execute the necessary business logic on it.
	<br/>
	usage: <code><sql:query name="myName"></code>
	</li>
	<li>
	<strong>isstoredprocedure</strong>:
	When you want to use stored procedures, you have to explicitly add
	this attribute to the query element. By default, the transformer
	assumes that you want to execute a SQL statement.
	<br/>
	usage: <code><sql:query isstoredprocedure="true"></code>
	</li>
	</ol>
	<p>Here is an example of how the input XML might look like:</p>
	<source>
	<![CDATA[
	<page>
	<title>Hello</title>
	<content>
	<para>This is my first Cocoon page filled with sql data!</para>
	<sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
	<sql:query name="department">
	select id,name from department_table
	</sql:query>
	</sql:execute-query>
	</content>
	</page>
	]]></source>
	<p>
	You can use the file generator to retrieve the XML from the filesystem.
	To invoke the SQLTransformer you have to add following to the sitemap:
	</p>
	<source>
	<![CDATA[
	<map:transform type="sql">
	<map:parameter name="use-connection" value="personnel"/>
	<map:parameter name="show-nr-of-rows" value="true"/>
	<map:parameter name="clob-encoding" value="UTF-8"/>
	</map:transform>
	]]></source>
	<p>
	The <code>use-connection</code> parameter defines which connection,
	defined under the datasources element in <code>cocoon.xconf</code>, the
	SQLTransformer has to use to retrieve the data.
	</p>
	<p>
	The <code>show-nr-of-rows</code> instructs the transformer to count the
	number of rows in the resultset explicitly and to set the result as
	attribute to the rowset element. This attribute is only useful in
	combination with a sql statement, not with stored procedures. If a
	stored procedure returns a resultset and you want to know how many rows
	it contains, you have to count the number of rows in another transformer
	or your stored procedure has to return it also (last solution is the
	best one).
	</p>
	<p>
	The <code>clob-encoding</code> parameter defines what encoding should be
	used in getting content from CLOB columns.
	</p>
	<p>The output XML will look as follows:</p>
	<source>
	<![CDATA[
	<page>
	<title>Hello</title>
	<content>
	<para>This is my first Cocoon page filled with sql data!</para>
	<sql:rowset nrofrows="2" name="department"
	xmlns:sql="http://apache.org/cocoon/SQL/2.0">
	<sql:row>
	<sql:id>1</sql:id>
	<sql:name>Programmers</sql:name>
	</sql:row>
	<sql:row>
	<sql:id>2</sql:id>
	<sql:name>Loungers</sql:name>
	</sql:row>
	</sql:rowset>
	</content>
	</page>
	]]></source>
	<p>
	If you use this in combination with the <code>simple-sql2html.xsl</code>
	stylesheet,
	</p>
	<source>
	<![CDATA[
	<map:transform src="stylesheets/simple-sql2html.xsl"/>
	]]></source>
	<p>you will get a more visually attractive page.</p>
	<p>See below for a more in depth example with stored procedures.</p>
	<p>
	By now you should be able to use the SQLTransformer, but there are some
	more options you might find useful...
	</p>
	</s1>
	<s1 title="Advanced functionality">
	<s2 title="Substitution">
	<p>
	Sometimes you need more information before you can execute a query,
	e.g. the name of the user that is currently logged on your site. This
	information is only available at runtime and hence can only be
	substituted in the query when available.
	</p>
	<p>
	To pass this information to the SQL statement, the input XML has to
	look like this:
	</p>
	<source>
	<![CDATA[
	<page>
	<sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
	<sql:query>
	select id,name from employee_table where name =
	'<sql:substitute-value name="username"/>'
	</sql:query>
	</sql:execute-query>
	</page>
	]]></source>
	<p>
	The substitution is done by the SQLTransformer before it executes the
	query (before it calls the method <code>prepareStatement</code>!). For
	this, the transformer has to be given the necessary values via the
	sitemap (as parameter):
	</p>
	<source>
	<![CDATA[
	<map:transform type="sql">
	<map:parameter name="use-connection" value="personnel"/>
	<map:parameter name="show-nr-of-rows" value="true"/>
	<map:parameter name="username" value="Stefano Mazzocchi"/>
	</map:transform>
	]]></source>
	<p>
	Whenever the transformer encounters a <code>substitute-value</code>
	element for which the attribute <code>name</code> contains the value
	<code>username</code>, it will replace this element with the value
	<code>Stefano Mazzocchi</code>.
	</p>
	<p>The output XML will be as follow:</p>
	<source>
	<![CDATA[
	<page>
	<sql:rowset nrofrows="1" xmlns:sql="http://apache.org/cocoon/SQL/2.0">
	<sql:row>
	<sql:id>2</sql:id>
	<sql:name>Stefano Mazzocchi</sql:name>
	</sql:row>
	</sql:rowset>
	</page>
	]]></source>
	<p>
	It is also possible to use substitution in combination with stored
	procedures.
	</p>
	</s2>
	<s2 title="Ancestors">
	<p>This functionality is best described by a simple example.</p>
	<p>Take following input XML:</p>
	<source>
	<![CDATA[
	<page>
	<sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
	<sql:query name="department">
	select id, name from department_table
	</sql:query>
	<sql:execute-query>
	<sql:query name="employee">
	select id, name from employee_table where department_id =
	<sql:ancestor-value name="id" level="1"/>
	</sql:query>
	</sql:execute-query>
	</sql:execute-query>
	</page>
	]]></source>
	<p>
	The first query will retrieve all <code>id</code>'s and
	<code>name</code>'s from the <code>department_table</code> table. For
	each <code>id</code> that comes from the
	<code>department_table</code>, the second query, in which the
	<code>ancestor-value</code> element will be replaced by the
	<code>id</code>, will be executed. The above example will be
	transformed to the following XML:
	</p>
	<source>
	<![CDATA[
	<page>
	<sql:rowset nrofrows="2" name="department"
	xmlns:sql="http://apache.org/cocoon/SQL/2.0">
	<sql:row>
	<sql:id>1</sql:id>
	<sql:name>Programmers</sql:name>
	<sql:rowset nrofrows="2" name="employee">
	<sql:row>
	<sql:id>1</sql:id>
	<sql:name>Donald Ball</sql:name>
	</sql:row>
	<sql:row>
	<sql:id>2</sql:id>
	<sql:name>Stefano Mazzocchi</sql:name>
	</sql:row>
	</sql:rowset>
	</sql:row>
	<sql:row>
	<sql:id>2</sql:id>
	<sql:name>Loungers</sql:name>
	<sql:rowset nrofrows="1" name="employee">
	<sql:row>
	<sql:id>3</sql:id>
	<sql:name>Pierpaolo Fumagalli</sql:name>
	</sql:row>
	</sql:rowset>
	</sql:row>
	</sql:rowset>
	</page>
	]]></source>
	</s2>
	<s2 title="in- and out-parameters">
	<p>
	Stored procedures can return data as a parameter. To make use of this
	functionality in java, you have to register these parameters as
	<em>out parameters</em>. Since this information is application
	specific, the SQLTransformer uses reflection to retrieve the data in
	the right format. For this, an extra element is needed in the input
	XML:
	</p>
	<source>
	<![CDATA[
	<sql:out-parameter nr="1"
	name="code"
	type="java.sql.Types.INTEGER"/>
	]]></source>
	<p>where:</p>
	<ol>
	<li>
	<strong>nr</strong>:
	The targeted parameter number that will return data of a certain
	type.
	</li>
	<li>
	<strong>type</strong>:
	The type of data that will be returned (defined in
	<code>java.sql.Types</code> or in database specific drivers, e.g.
	<code>oracle.jdbc.driver.OracleTypes</code>). Once the stored
	procedure returns data in the parameters, the stored procedure tries
	to process them. If the returned parameter is an instance of
	<code>ResultSet</code>, it will be translated to XML as we saw
	before. In all the other situations the SQLTransformer will convert
	the parameter to a string.
	</li>
	</ol>
	<p>
	This is an example of how to call an oracle stored procedure and
	process it with the SQLTransformer:
	</p>
	<source>
	<![CDATA[
	<page>
	<sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
	<sql:query isstoredprocedure="true" name="namesearch">
	begin QUICK_SEARCH.FIND_NAME('<sql:substitute-value
	name="username"/>',?,?,?); end;
	</sql:query>
	<sql:out-parameter nr="1" name="code"
	type="java.sql.Types.INTEGER"/>
	<sql:out-parameter nr="2" name="nrofrows"
	type="java.sql.Types.INTEGER"/>
	<sql:out-parameter nr="3" name="resultset"
	type="oracle.jdbc.driver.OracleTypes.CURSOR"/>
	</sql:execute-query>
	</page>
	]]></source>
	<p>
	The SQLTransformer will create 3 elements, respectively
	<code>code</code>, <code>nrofrows</code> and <code>resultset</code>
	under the element <code>namesearch</code>. Since the type
	<code>oracle.jdbc.driver.OracleTypes.CURSOR</code> corresponds to a
	<code>ResultSet</code>, a <code>rowset</code> element will be created,
	containing all the data of the resultset. It is also possible to use
	an <em>in-parameter</em> element, e.g.
	<code><sql:in-parameter nr="1" value="1"/></code>. This
	functionality is only provided to be complete, because it is available
	in Java itself. You can also use the <em>in-parameter</em> in
	combination with a SQL statement. Used in combination with an
	<em>out-parameter</em>, a <em>?-parameter</em> can be an
	<em>in-parameter</em> and an <em>out-parameter</em> at the same time.
	</p>
	</s2>
	</s1>
	<s1 title="Combined with other transformers">
	<s2 title="FilterTransformer">
	<p>
	When you query a database and it returns too many rows to process at
	once, you might want to take a block of elements, process this block
	and ignore the rest for now. You can best compare it to a search on
	Google: they only return 10 results in one time, for more results you
	have to click on another block (page). It wouldn't be wise to process
	more than 10 elements in the pipeline if you only need to display 10
	elements.
	</p>
	<p>
	Assume that a query returns 56 row elements (by using the
	SQLTransformer) and that you only want to display the first 10
	elements:
	</p>
	<p>Output XML from the SQLTransformer:</p>
	<source>
	<![CDATA[
	<sql:rowset nrofrows="56" name="test"
	xmlns:sql="http://apache.org/cocoon/SQL/2.0">
	<sql:row>
	<!-- db record -->
	</sql:row>
	<sql:row>
	<!-- db record -->
	</sql:row>

	...

	<sql:row>
	<!-- db record -->
	</sql:row>
	</sql:rowset>
	]]></source>
	<p>
	By adding following lines to the sitemap, just under the
	SQLTransformer, you restrict the results to 10 elements in the first
	block:
	</p>
	<source>
	<![CDATA[
	<map:transform type="filter">
	<map:parameter name="element-name" value="row"/>
	<map:parameter name="count" value="10"/>
	<map:parameter name="blocknr" value="1"/>
	</map:transform>
	]]></source>
	<p>output XML:</p>
	<source>
	<![CDATA[
	<sql:rowset nrofrows="56" name="test"
	xmlns:sql="http://apache.org/cocoon/SQL/2.0">
	<block id="1">
	<sql:row>
	<!-- db record -->
	</sql:row>

	<!-- total of 10 rows -->

	<sql:row>
	<!-- db record -->
	</sql:row>
	</block>
	<block id="2"/>
	<block id="3"/>
	<block id="4"/>
	<block id="5"/>
	<block id="6"/>
	</sql:rowset>
	]]></source>
	<p>
	To make it more dynamically, put something like
	<code>{reqCount}</code> and <code>{reqBlock}</code> in the values for
	<em>count</em> and <em>blocknr</em> respectively. These can be
	parameters from the request and they can be passed to the sitemap with
	an action.
	</p>
	<p>
	The FilterTransformer is a standalone component; you don't need to use
	it in combination with the SQLTransformer.
	</p>
	</s2>
	<s2 title="WriteDOMSessionTransformer">
	<p>
	If you only use the FilterTransformer in combination with the
	SQLTransformer, you have to query the database each time the user
	wants to see another part of the result. You can better store the
	result in the session after the first request and retrieve the result
	from the session for the subsequent requests. This can be done by
	using a selector, which checks if the data is available in the session
	or not.
	</p>
	<p>
	WriteDOMSessionTransformer can build a DOM starting from a given
	element (which will be the root of the DOM tree) and store it in the
	session. If you want to store the result of a query, you have to add
	following to the sitemap:
	</p>
	<source>
	<![CDATA[
	<map:transform type="writeDOMsession">
	<map:parameter name="dom-name" value="DBresult"/>
	<map:parameter name="dom-root-element" value="rowset"/>
	</map:transform>
	]]></source>
	<p>
	The transformer will build a DOM tree with <code>rowset</code> as root
	element and will store it in the session with the name
	<code>DBresult</code>.
	</p>
	<note>
	Most of the times, it is not smart to keep the output XML of the
	SQLTransformer in the session. Check if it is better to do the
	necessary transformations first, so that you get a smaller DOM, and
	then put the result in the session. You probably will be able to use
	the FilterTransformer on the transformed XML also.
	</note>
	<p>
	The WriteDOMSessionTransformer is a standalone component, you don't
	need to use it in combination with the SQLTransformer.
	</p>
	</s2>
	<s2 title="ReadDOMSessionTransformer">
	<p>
	Simply transforms a DOM to SAX events, which can be used further on in
	the pipeline. Once you stored the result of a query in the session
	with the WriteDOMSessionTransformer, you can read it again with the
	ReadDOMSessionTransformer:
	</p>
	<source>
	<![CDATA[
	<map:transform type="readDOMsession">
	<map:parameter name="dom-name" value="DBresult"/>
	<map:parameter name="trigger-element" value="users"/>
	<map:parameter name="position" value="after"/>
	</map:transform>
	]]></source>
	<p>
	In this example the SAX events, that come from the DOM tree stored in
	the session with name <code>DBresult</code>, will be added after the
	<code>users</code> element. This means as soon that the transformer
	encounters the end element <code>users</code>, it will start to
	generate SAX events from the DOM tree. There are three possible
	positions, <code>before</code>, <code>in</code> and
	<code>after</code>:
	</p>
	<ol>
	<li>
	<strong><code>before</code></strong> means that when the transformer
	encounters the <code>users</code> element, it will FIRST translate
	the DOM tree to SAX events and THEN it will continue to forward the
	other SAX events (starting with <code>users</code>).
	</li>
	<li>
	<strong><code>in</code></strong> means that the transformer will
	forward the start element event for <code>users</code> and that it
	IMMEDIATELY starts to generate SAX events from the DOM tree. After
	that, it will continue to forward the child elements of users and
	then all the other elements.
	</li>
	<li>
	<strong><code>after</code></strong> means that the transformer
	starts to generate SAX events from the DOM tree just after it has
	forwarded the end element <code>users</code>.
	</li>
	</ol>
	<p>
	The ReadDOMSessionTransformer is a standalone component, you don't
	need to use it in combination with the WriteDOMSessionTransformer.
	</p>
	</s2>
	<p>That's it,</p>
	<p>Sven Beauprez</p>
	</s1>
	</body>
	</document>