| <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> |
| <html> |
| <head> |
| <META http-equiv="Content-Type" content="text/html; charset=UTF-8"> |
| <title>SQL Transformer</title> |
| <link href="http://purl.org/DC/elements/1.0/" rel="schema.DC"> |
| <meta content="Sven Beauprez" name="DC.Creator"> |
| <meta content="Davanum Srinivas" name="DC.Creator"> |
| </head> |
| <body> |
| |
| <h1>Introduction</h1> |
| |
| <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> |
| |
| |
| <h1>Basic functionality</h1> |
| |
| <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> |
| |
| <pre class="code"> |
| |
| <page> |
| <execute-query xmlns="http://apache.org/cocoon/SQL/2.0"> |
| <query> |
| <!-- here comes the SQL statement or stored procedure --> |
| </query> |
| </execute-query> |
| </page> |
| </pre> |
| |
| <p> |
| Nothing prevents you from putting other XML around the |
| <span class="codefrag">execute-query</span> 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: <span class="codefrag"><query name="myName"></span> |
| |
| </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: <span class="codefrag"><query isstoredprocedure="true"></span> |
| |
| </li> |
| |
| </ol> |
| |
| <p>Here is an example of how the input XML might look like:</p> |
| |
| <pre class="code"> |
| |
| <page> |
| <title>Hello</title> |
| <content> |
| <para>This is my first Cocoon page filled with sql data!</para> |
| <execute-query xmlns="http://apache.org/cocoon/SQL/2.0"> |
| <query name="department"> |
| select id,name from department_table |
| </query> |
| </execute-query> |
| </content> |
| </page> |
| </pre> |
| |
| <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> |
| |
| <pre class="code"> |
| |
| <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> |
| </pre> |
| |
| <p> |
| The <span class="codefrag">use-connection</span> parameter defines which connection, |
| defined under the datasources element in <span class="codefrag">cocoon.xconf</span>, the |
| SQLTransformer has to use to retrieve the data. |
| </p> |
| |
| <p> |
| The <span class="codefrag">show-nr-of-rows</span> 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 <span class="codefrag">clob-encoding</span> parameter defines what encoding should be |
| used in getting content from CLOB columns. |
| </p> |
| |
| <p>The output XML will look as follows:</p> |
| |
| <pre class="code"> |
| |
| <page> |
| <title>Hello</title> |
| <content> |
| <para>This is my first Cocoon page filled with sql data!</para> |
| <rowset nrofrows="2" name="department" |
| xmlns="http://apache.org/cocoon/SQL/2.0"> |
| <row> |
| <id>1</id> |
| <name>Programmers</name> |
| </row> |
| <row> |
| <id>2</id> |
| <name>Loungers</name> |
| </row> |
| </rowset> |
| </content> |
| </page> |
| </pre> |
| |
| <p> |
| If you use this in combination with the <span class="codefrag">simple-sql2html.xsl</span> |
| stylesheet, |
| </p> |
| |
| <pre class="code"> |
| |
| <map:transform src="stylesheets/simple-sql2html.xsl"/> |
| </pre> |
| |
| <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> |
| |
| |
| <h1>Advanced functionality</h1> |
| |
| <h2>Substitution</h2> |
| <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> |
| <pre class="code"> |
| |
| <page xmlns:sql="http://apache.org/cocoon/SQL/2.0"> |
| <execute-query xmlns="http://apache.org/cocoon/SQL/2.0"> |
| <query> |
| select id,name from employee_table where name = |
| '<sql:substitute-value sql:name="username"/>' |
| </query> |
| </execute-query> |
| </page> |
| </pre> |
| <p> |
| The substitution is done by the SQLTransformer before it executes the |
| query (before it calls the method <span class="codefrag">prepareStatement</span>!). For |
| this, the transformer has to be given the necessary values via the |
| sitemap (as parameter): |
| </p> |
| <pre class="code"> |
| |
| <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> |
| </pre> |
| <p> |
| Whenever the transformer encounters a <span class="codefrag">substitute-value</span> |
| element for which the attribute <span class="codefrag">name</span> contains the value |
| <span class="codefrag">username</span>, it will replace this element with the value |
| <span class="codefrag">Stefano Mazzocchi</span>. |
| </p> |
| <p>The output XML will be as follow:</p> |
| <pre class="code"> |
| |
| <page xmlns:sql="http://apache.org/cocoon/SQL/2.0"> |
| <rowset nrofrows="1" xmlns="http://apache.org/cocoon/SQL/2.0"> |
| <row> |
| <id>2</id> |
| <name>Stefano Mazzocchi</name> |
| </row> |
| </rowset> |
| </page> |
| </pre> |
| <p> |
| It is also possible to use substitution in combination with stored |
| procedures. |
| </p> |
| |
| <h2>Ancestors</h2> |
| <p>This functionality is best described by a simple example.</p> |
| <p>Take following input XML:</p> |
| <pre class="code"> |
| |
| <page xmlns:sql="http://apache.org/cocoon/SQL/2.0"> |
| <execute-query xmlns="http://apache.org/cocoon/SQL/2.0"> |
| <query name="department"> |
| select id,name from department_table |
| </query> |
| <execute-query> |
| <query name="employee"> |
| select id,name from employee_table where department_id = |
| <ancestor-value sql:name="id" sql:level="1"/> |
| </query> |
| </execute-query> |
| </execute-query> |
| </page> |
| </pre> |
| <p> |
| The first query will retrieve all <span class="codefrag">id</span>'s and |
| <span class="codefrag">name</span>'s from the <span class="codefrag">department_table</span> table. For |
| each <span class="codefrag">id</span> that comes from the |
| <span class="codefrag">department_table</span>, the second query, in which the |
| <span class="codefrag">ancestor-value</span> element will be replaced by the |
| <span class="codefrag">id</span>, will be executed. The above example will be |
| transformed to the following XML: |
| </p> |
| <pre class="code"> |
| |
| <page xmlns:sql="http://apache.org/cocoon/SQL/2.0"> |
| <rowset nrofrows="2" name="department" |
| xmlns="http://apache.org/cocoon/SQL/2.0"> |
| <row> |
| <id>1</id> |
| <name>Programmers</name> |
| <rowset nrofrows="2" name="employee"> |
| <row> |
| <id>1</id> |
| <name>Donald Ball</name> |
| </row> |
| <row> |
| <id>2</id> |
| <name>Stefano Mazzocchi</name> |
| </row> |
| </rowset> |
| </row> |
| <row> |
| <id>2</id> |
| <name>Loungers</name> |
| <rowset nrofrows="1" name="employee"> |
| <row> |
| <id>3</id> |
| <name>Pierpaolo Fumagalli</name> |
| </row> |
| </rowset> |
| </row> |
| </rowset> |
| </page> |
| </pre> |
| |
| <h2>in- and out-parameters</h2> |
| <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> |
| <pre class="code"> |
| |
| <out-parameter sql:nr="1" sql:name="code" |
| sql:type="java.sql.Types.INTEGER"/> |
| </pre> |
| <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 |
| <span class="codefrag">java.sql.Types</span> or in database specific drivers, e.g. |
| <span class="codefrag">oracle.jdbc.driver.OracleTypes</span>). Once the stored |
| procedure returns data in the parameters, the stored procedure tries |
| to process them. If the returned parameter is an instance of |
| <span class="codefrag">ResultSet</span>, 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> |
| <pre class="code"> |
| |
| <page xmlns:sql="http://apache.org/cocoon/SQL/2.0"> |
| <execute-query xmlns="http://apache.org/cocoon/SQL/2.0"> |
| <query isstoredprocedure="true" name="namesearch"> |
| begin QUICK_SEARCH.FIND_NAME('<sql:substitute-value |
| sql:name="username"/>',?,?,?); end; |
| </query> |
| <out-parameter sql:nr="1" sql:name="code" |
| sql:type="java.sql.Types.INTEGER"/> |
| <out-parameter sql:nr="2" sql:name="nrofrows" |
| sql:type="java.sql.Types.INTEGER"/> |
| <out-parameter sql:nr="3" sql:name="resultset" |
| sql:type="oracle.jdbc.driver.OracleTypes.CURSOR"/> |
| </execute-query> |
| </page> |
| </pre> |
| <p> |
| The SQLTransformer will create 3 elements, respectively |
| <span class="codefrag">code</span>, <span class="codefrag">nrofrows</span> and <span class="codefrag">resultset</span> |
| under the element <span class="codefrag">namesearch</span>. Since the type |
| <span class="codefrag">oracle.jdbc.driver.OracleTypes.CURSOR</span> corresponds to a |
| <span class="codefrag">ResultSet</span>, a <span class="codefrag">rowset</span> 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. |
| <span class="codefrag"><in-parameter sql:nr="1" sql:value="1"/></span>. 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> |
| |
| |
| <h1>Combined with other transformers</h1> |
| |
| <h2>Filtertransformer</h2> |
| <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> |
| <pre class="code"> |
| |
| <rowset nrofrows="56" name="test" |
| xmlns="http://apache.org/cocoon/SQL/2.0"> |
| <row> |
| <!-- db record --> |
| </row> |
| <row> |
| <!-- db record --> |
| </row> |
| |
| ... |
| |
| <row> |
| <!-- db record --> |
| </row> |
| </rowset> |
| </pre> |
| <p> |
| By adding following lines to the sitemap, just under the |
| SQLTransformer, you restrict the results to 10 elements in the first |
| block: |
| </p> |
| <pre class="code"> |
| |
| <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> |
| </pre> |
| <p>output XML:</p> |
| <pre class="code"> |
| |
| <rowset nrofrows="56" name="test" |
| xmlns="http://apache.org/cocoon/SQL/2.0"> |
| <block id="1"> |
| <row> |
| <!-- db record --> |
| </row> |
| |
| <!-- total of 10 rows --> |
| |
| <row> |
| <!-- db record --> |
| </row> |
| </block> |
| <block id="2"/> |
| <block id="3"/> |
| <block id="4"/> |
| <block id="5"/> |
| <block id="6"/> |
| </rowset> |
| </pre> |
| <p> |
| To make it more dynamically, put something like |
| <span class="codefrag">{reqCount}</span> and <span class="codefrag">{reqBlock}</span> 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> |
| |
| <h2>WriteDOMSessionTransformer</h2> |
| <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> |
| <pre class="code"> |
| |
| <map:transform type="writeDOMsession"> |
| <map:parameter name="dom-name" value="DBresult"/> |
| <map:parameter name="dom-root-element" value="rowset"/> |
| </map:transform> |
| </pre> |
| <p> |
| The transformer will build a DOM tree with <span class="codefrag">rowset</span> as root |
| element and will store it in the session with the name |
| <span class="codefrag">DBresult</span>. |
| </p> |
| <div class="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. |
| </div> |
| <p> |
| The WriteDOMSessionTransformer is a standalone component, you don't |
| need to use it in combination with the SQLTransformer. |
| </p> |
| |
| <h2>ReadDOMSessionTransformer</h2> |
| <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> |
| <pre class="code"> |
| |
| <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> |
| </pre> |
| <p> |
| In this example the SAX events, that come from the DOM tree stored in |
| the session with name <span class="codefrag">DBresult</span>, will be added after the |
| <span class="codefrag">users</span> element. This means as soon that the transformer |
| encounters the end element <span class="codefrag">users</span>, it will start to |
| generate SAX events from the DOM tree. There are three possible |
| positions, <span class="codefrag">before</span>, <span class="codefrag">in</span> and |
| <span class="codefrag">after</span>: |
| </p> |
| <ol> |
| |
| <li> |
| |
| <strong><span class="codefrag">before</span></strong> means that when the transformer |
| encounters the <span class="codefrag">users</span> element, it will FIRST translate |
| the DOM tree to SAX events and THEN it will continue to forward the |
| other SAX events (starting with <span class="codefrag">users</span>). |
| </li> |
| |
| <li> |
| |
| <strong><span class="codefrag">in</span></strong> means that the transformer will |
| forward the start element event for <span class="codefrag">users</span> 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><span class="codefrag">after</span></strong> means that the transformer |
| starts to generate SAX events from the DOM tree just after it has |
| forwarded the end element <span class="codefrag">users</span>. |
| </li> |
| |
| </ol> |
| <p> |
| The ReadDOMSessionTransformer is a standalone component, you don't |
| need to use it in combination with the WriteDOMSessionTransformer. |
| </p> |
| |
| <p>That's it,</p> |
| |
| <p>Sven Beauprez</p> |
| |
| |
| </body> |
| </html> |