Hi,
I rewritten some parts of and added some new features to the SQLTransformer.
Overall there were too many changes to make a diff, so i posted the complete
source file and also a txt with some explanation.
The biggest change is that it now uses prepared and callable statements.
The biggest addition is that you can use stored procedures also.
There are 2 parts i am not sure of:
- i commented out lines 213 and 252:
213: this.contentHandler.startPrefixMapping("",my_uri);
252: this.contentHandler.endPrefixMapping("");
i don't think they are needed
- i commented out lines 911-914 because the connection is closed for sure in
the execute() method
Please test it and feel free to add it to cocoon2
Sven
SQLTransformer.java
With the SQLTransformer, it is possible to process a query that was delivered by a
generator.
The input XML has the following structure:
<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>
The substitute-value is a parameter in the sitemap for the SQLTransformer, eg
<map:transform type="sql">
<map:parameter name="use-connection" value="personnel"/>
<map:parameter name="name" value="Programmers"/>
</map:transform>
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:
<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";>
<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>
</row>
</rowset>
</content>
</page>
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).
<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>
The generated SAX events will be as follow (assuming that the resultset contains a
firstname and a department name):
<rowset name="searchresult>
<code>0</code>
<resultset>
<row>
<firstname>
Donald
</firstname>
<department>
Programmers
</department>
</row>
<row>
<firstname>
Stefano
</firstname>
<department>
Programmers
</department>
</row>
</resultset>
</rowset>
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)
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, email: [EMAIL PROTECTED]
