dims 01/06/25 04:43:08 Modified: xdocs sql-transformer.xml Log: XDOC for SQLTransformer from Sven Beauprez <[EMAIL PROTECTED]> Revision Changes Path 1.2 +559 -122 xml-cocoon2/xdocs/sql-transformer.xml Index: sql-transformer.xml =================================================================== RCS file: /home/cvs/xml-cocoon2/xdocs/sql-transformer.xml,v retrieving revision 1.1 retrieving revision 1.2 diff -u -r1.1 -r1.2 --- sql-transformer.xml 2001/06/21 11:08:01 1.1 +++ sql-transformer.xml 2001/06/25 11:42:59 1.2 @@ -2,135 +2,572 @@ <!DOCTYPE document SYSTEM "./dtd/document-v10.dtd"> -<document><header><title>SQL Transformer</title> +<document> +<header> +<title>SQL Transformer</title> <authors> -<person name="Sven Beauprez" email="[EMAIL PROTECTED]"/> -<person name="Davanum Srinivas" email="[EMAIL PROTECTED]"/> -</authors></header><body> - -<s1 title="SQL Transformer"> - - <p> - With the SQLTransformer, it is possible to process a query that was delivered by a generator. - The input XML has the following structure: - </p> - -<source><![CDATA[ - <page xmlns:sql="http://apache.org/cocoon/SQL/2.0";> - - <title>Hello</title> - <content> - <para>This is my first Cocoon2 page filled with sql data!</para> - - <execute-query xmlns="http://apache.org/cocoon/SQL/2.0";> - <query name="department" show-nr-of-rows="true"> - select id,name from department_table - where name='<substitute-value sql:name="name"/>' - </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> - </content> - </page> -]]></source> - - <p> - The substitute-value is a parameter in the sitemap for the SQLTransformer, eg - </p> -<source><![CDATA[ - <map:transform type="sql"> - <map:parameter name="use-connection" value="personnel"/> - <map:parameter name="name" value="Programmers"/> - </map:transform> -]]></source> - - <p> - The result of the first query is used to process the second one. The values for id are sequentially substituted - in the second query which is repeatedly executed for each value. This is done by using the ancestor-value element. - The result will look as follows: - </p> - -<source><![CDATA[ - <page xmlns:sql="http://apache.org/cocoon/SQL/2.0";> - <title>Hello</title> - <content> - <para>This is my first Cocoon2 page filled with sql data!</para> - <rowset name="department" xmlns="http://apache.org/cocoon/SQL/2.0";> + <person name="Sven Beauprez" email="[EMAIL PROTECTED]"/> + <person name="Davanum Srinivas" email="[EMAIL PROTECTED]"/> +</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 (eg select, insert, update), it is also +possible to use stored procedures. In combination with other transformers (eg +FilterTransformer), this one can be very powerful. +</p> +</s1> + +<s1 title="Basic functionallity"> +<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 xmlns:sql="http://apache.org/cocoon/SQL/2.0";> + <execute-query xmlns="http://apache.org/cocoon/SQL/2.0";> + <query> + <!-- here comes the SQL statement or stored procedure --> + </query> + </execute-query> + </page> + ]]> + </source> + +<p> +Nothing prevents you from putting other XML aroung the page element. If you do, +it 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> +name: +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: <query name="myName"> +</li> +<li> +isstoredprocedure: +When you want to use stored procedures, you have to explicitely add this +attribute to the query element. By default, the transformer assumes that you +want to execute a SQL statement. +<br/> +usage: <query isstoredprocedure="true"> + +</li> +</ol> +<p> +Here is an example of how the input XML might look like: +</p> + <source> + <![CDATA[ + <page xmlns:sql="http://apache.org/cocoon/SQL/2.0";> + + <title>Hello</title> + <content> + <para>This is my first Cocoon2 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> + ]]> + </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:transform> + ]]> + </source> + +<p> +The "use-connection" parameter defines which connection, defined under the +datasources element in cocoon.xconf, the SQLTransformer has to use to retrieve +the data. +</p> +<p> +The 'show-nr-of-rows' instructs the transformer to count the number of rows in +the resultset explicitely and to set the result as attribute to the rowset +element. This attribute is only usefull in combination with an 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 rerutn it also (last +solution is the best one) +</p> +<p> +The output XML will look as follows: +</p> + + <source> + <![CDATA[ + <page xmlns:sql="http://apache.org/cocoon/SQL/2.0";> + <title>Hello</title> + <content> + <para>This is my first Cocoon2 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> + ]]> + </source> + +<p> +If you use this in combination with the "simple-sql2html" XSL 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 usefull... +</p> + +</s1> + +<s1 title="Advanced functionallity"> +<s2 title="Substitution"> +<p> +Sometimes you need more information before you can execute a query, eg. 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 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 = '<substitute-value + sql:name="username"/>' + </query> + </execute-query> + </page> + ]]> + </source> +<p> +The substitution is done by the SQLTransformer before it executes the query +(before it calls the method prepareStatement!). 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 'substitute-value' element for which the +attribute 'name' contains the value 'username', it will replace this element +with the value 'Stefano Mazzocchi' (without the single quotes!). +</p> +<p> +The output XML will be as follow: +</p> + <source> + <![CDATA[ + <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> + ]]> + </source> + +<p> +It is also possible to use substitution in combination with stored procedures. +</p> +</s2> + +<s2 title="Ancestors"> +<p> +This functionallity is best described by a simple example. +</p> +<p> +Take following input XML: +</p> + + <source> + <![CDATA[ + <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> + ]]> + </source> + +<p> +The first query will retrieve all id's and name's from the department_table +table. For each id that comes from the department_table, the second query, in +which the 'ancestor-value' element will be replaced by the id, will be executed. +The above example will be transformed to the following XML: +</p> + + <source> + <![CDATA[ + <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> + ]]> + </source> + +</s2> + +<s2 title="in- and out-parameters"> + +<p> +Stored procedures can return data as a parameter. To make use of this +functionallity in java, you have to register these parameters as 'out +parameters'. 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[ + <out-parameter sql:nr="1" sql:name="code" +sql:type="java.sql.Types.INTEGER"/> + ]]> + </source> + +<p> +where: +</p> +<ol> +<li> +nr: +The targeted parameter number that will return data of a certain type. +</li> +<li> +type: +The type of data that will be returned (defined in java.sql.Types or in database +specific drivers, eg oracle.jdbc.driver.OracleTypes). Once the stored procedure +returns data in the parameters, the stored procedure tries to process them. If +the returned parameter is an instance of ResultSet, 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 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('<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> + ]]> + </source> + +<p> +The SQLTransformer will create 3 elements, respectively 'code', 'nrofrows' and +'resultset' under the element 'namesearch'. Since the type +oracle.jdbc.driver.OracleTypes.CURSOR' corresponds to a ResultSet, a 'rowset' +element will be created, containing all the data of the resultset. +It is also possible to use an 'in-parameter' element, eg. <in-parameter +sql:nr="1" sql:value="1"/>. +This functionallity is only provided to be complete, because it is available in +java itself. You can also use the 'in-parameter' in combination with a SQL +statement. +Used in combination with an out-parameter, a ?-parameter can be an in-parameter +and an out-parameter 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 too 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[ + <rowset nrofrows="56" name="test" +xmlns="http://apache.org/cocoon/SQL/2.0";> <row> - <id>1</id> - <name>Programmers</name> - <rowset name="employee"> - <row> - <id>1</id> - <name>Donald Ball</name> - </row> - <row> - <id>2</id> - <name>Stefano Mazzocchi</name> - </row> - </rowset> + <!-- db record --> </row> - </rowset> - </content> - </page> -]]></source> - - <p> - It is also possible to call stored procedures, the following example shows how to use an oracle stored procedure, - that returns a code (Integer) and a resultset (ResultSet). - </p> - -<source><![CDATA[ - <page xmlns:sql="http://apache.org/cocoon/SQL/2.0";> - <execute-query> - <query isstoredprocedure="true" name="searchresult"> - begin QUICK_SEARCH.SEARCH('<substitute-value sql:name="department"/>',?,?); end; - </query> - <out-parameter sql:nr="1" sql:name="code" sql:type="java.sql.Types.INTEGER"/> - <out-parameter sql:nr="2" sql:name="resultset" sql:type="oracle.jdbc.driver.OracleTypes.CURSOR"/> - </execute-query> - </page> -]]></source> - - <p> - The generated SAX events will be as follow (assuming that the resultset contains a firstname and a department name): - </p> - -<source><![CDATA[ - - <rowset name="searchresult> - <code>0</code> - <resultset> <row> - <firstname> - Donald - </firstname> - <department> - Programmers - </department> + <!-- db record --> </row> <row> - <firstname> - Stefano - </firstname> - <department> - Programmers - </department> + <!-- db record --> </row> - </resultset> - </rowset> -]]></source> - - <p> - It is also possible to use in-parameters, eg. <in-parameter sql:nr="1" sql:value="1"/>. - Used in combination with an out-parameter, the first ?-parameter can be an in-parameter and an out-parameter at the same time. - (in-parameters can also be used with sql statements) - </p> + ... + + <row> + <!-- db record --> + </row> + </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[ + <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> + ]]> + </source> + +<p> +To make it more dynamically, put something like {reqCount} and {reqBlock} in the +values for count and blocknr 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 rowset as root element and will store +it in the session with the name "DBresult". +</p> +<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. +</p> +<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 came from the DOM tree that is stored in +the session with name DBresult will be added after the users element. This means +as soon that the transformer encounters the end-element 'users', it will start +to generate SAX-events from the DOM tree. There are three possible positions, +'before','in' and 'after': +</p> +<ol> +<li>'before' means that when the transformer encounters the 'users' element, it +will FIRST translate the DOM tree to SAX-events and THEN it will continue to +forward the other SAX-events (starting with 'users'). +</li> +<li>'in' means that the transformer will forward the startElement event for +'users' 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>'after' means that the transformer starts to generate SAX-events from the +DOM-tree just after it has forwarded the end-element 'users'. +</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> -</body></document> ---------------------------------------------------------------------- In case of troubles, e-mail: [EMAIL PROTECTED] To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
