| <?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> |