|
|
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.
- Name : sql
- Class: org.apache.cocoon.transformation.SQLTransformer
- Cacheable: no.
|
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:
 |  |  |
 |
<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>
|  |
 |  |  |
Nothing prevents you from putting other XML around 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.
The query element has the following optional attributes:
-
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.
usage: <query name="myName">
-
isstoredprocedure:
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.
usage: <query isstoredprocedure="true">
Here is an example of how the input XML might look like:
 |  |  |
 |
<page xmlns:sql="http://apache.org/cocoon/SQL/2.0">
<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>
|  |
 |  |  |
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:
 |  |  |
 |
<map:transform type="sql">
<map:parameter name="use-connection" value="personnel"/>
<map:parameter name="show-nr-of-rows" value="true"/>
</map:transform>
|  |
 |  |  |
The "use-connection" parameter defines which connection, defined under the
datasources element in cocoon.xconf, the SQLTransformer has to use to retrieve
the data.
The 'show-nr-of-rows' 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 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 return it also (last
solution is the best one)
The output XML will look as follows:
 |  |  |
 |
<page xmlns:sql="http://apache.org/cocoon/SQL/2.0">
<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>
|  |
 |  |  |
If you use this in combination with the "simple-sql2html" XSL stylesheet,
 |  |  |
 |
<map:transform src="stylesheets/simple-sql2html.xsl"/>
|  |
 |  |  |
you will get a more visually attractive page.
See below for a more in depth example with stored procedures.
By now you should be able to use the SQLTransformer, but there are some more
options you might find useful...
|
Stored procedures can return data as a parameter. To make use of this
functionality 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:
 |  |  |
 |
<out-parameter sql:nr="1" sql:name="code"
sql:type="java.sql.Types.INTEGER"/>
|  |
 |  |  |
where:
-
nr:
The targeted parameter number that will return data of a certain type.
-
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.
This is an example of how to call an oracle stored procedure and process it with
the SQLTransformer:
 |  |  |
 |
<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>
|  |
 |  |  |
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 functionality 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.
|
|
 |  |  |
 | Combined with other transformers |  |
 |  |  |
|
|