haul 2002/06/04 02:09:11
Modified: src/documentation/xdocs/userdocs/xsp esql.xml
Log:
Improved docs.
Revision Changes Path
1.5 +319 -123 xml-cocoon2/src/documentation/xdocs/userdocs/xsp/esql.xml
Index: esql.xml
===================================================================
RCS file: /home/cvs/xml-cocoon2/src/documentation/xdocs/userdocs/xsp/esql.xml,v
retrieving revision 1.4
retrieving revision 1.5
diff -u -r1.4 -r1.5
--- esql.xml 5 Feb 2002 16:59:40 -0000 1.4
+++ esql.xml 4 Jun 2002 09:09:11 -0000 1.5
@@ -7,27 +7,30 @@
<person name="Robin Green" email="[EMAIL PROTECTED]"/>
</authors></header><body>
- <s1 title="Description">
+ <s1 title="Description">
- <p>The ESQL logicsheet is an
-XSP logicsheet that performs sql queries and serializes their
-results as XML. This allows you to work with data from a wide variety of different
sources when using Apache Cocoon.
-</p>
-
- <p>It has a number of important advantages over the old (deprecated) SQL
logicsheet and SQL processor.
- For example, it allows you to mix esql with other logicsheets. It also
- supports prepared statements (which gives you automatic parameter escaping),
multiple encodings
- in a single query and even multiple resultsets on one statement (if supported
from database)!</p>
-
- <p>The name was chosen merely to emphasise the fact that this is an extended
version of the old sql logicsheet -
- esql still uses standard SQL syntax. In fact, it is just a conversion
wrapper around your JDBC database
- driver, so it supports no more and no less SQL syntax than your JDBC driver
supports.
+ <p>The ESQL logicsheet is an XSP logicsheet that performs sql queries and
+ serializes their results as XML. This allows you to work with data from a
+ wide variety of different sources when using Apache Cocoon.
+ </p>
+
+ <p>It has a number of important advantages over the old (deprecated) SQL
+ logicsheet and SQL processor. For example, it allows you to mix esql with
+ other logicsheets. It also supports prepared statements (which gives you
+ automatic parameter escaping), multiple encodings in a single query and
+ even multiple resultsets on one statement (if supported from database)!</p>
+
+ <p>The name was chosen merely to emphasise the fact that this is an
+ extended version of the old sql logicsheet - esql still uses standard SQL
+ syntax. In fact, it is just a conversion wrapper around your JDBC database
+ driver, so it supports no more and no less SQL syntax than your JDBC driver
+ supports.
</p>
- </s1>
+ </s1>
- <s1 title="Installation">
- <p>Check your <code>cocoon.xconf</code> for this line and add it if
it's not already there:</p>
- <source><![CDATA[
+ <s1 title="Installation">
+ <p>Check your <code>cocoon.xconf</code> for this line and add it if it's not
already there:</p>
+ <source><![CDATA[
<builtin-logicsheet>
<parameter name="prefix" value="esql"/>
<parameter name="uri" value="http://apache.org/cocoon/SQL/v2"/>
@@ -35,38 +38,43 @@
value="resource://org/apache/cocoon/components/language/markup/xsp/java/esql.xsl"/>
</builtin-logicsheet>
]]></source>
- </s1>
+ </s1>
- <s1 title="Configuration">
- <p>Map the</p>
- <source>http://apache.org/cocoon/SQL/v2</source>
- <p>namespace to the esql prefix. Elements in the esql taglib namespace
will be interpreted as input to
-the esql taglib and will be stripped from the output.</p>
- <p>This is typically done like this:</p>
- <source><![CDATA[
+ <s1 title="Configuration">
+ <p>Map the</p>
+ <source>http://apache.org/cocoon/SQL/v2</source>
+ <p>namespace to the esql prefix. Elements in the esql taglib namespace will be
interpreted as input to
+ the esql taglib and will be stripped from the output.</p>
+ <p>This is typically done like this:</p>
+ <source><![CDATA[
<xsp:page
language="java"
xmlns:xsp="http://apache.org/xsp";
- xmlns:esql="http://apache.org/cocoon/SQL/v2";
+ xmlns:esql="http://apache.org/cocoon/SQL/v2";
>
. . .
</xsp:page>
]]></source>
- </s1>
- <s1 title="Usage and Examples">
- <p>At the moment documentation on esql is quite thin on the ground -
however, it should be enough to get
- you started.
- In the <code>docs/samples/xsp</code> directory you will find
<code>esql.xsp</code>, which is an example
- of two esql queries, demonstrating "nested" queries and dynamic prepared
statements. However, much more
- comprehensive is the <strong>schema</strong> in <code>esql.xsd</code>
which is a formal specification,
- written in the W3C standard language XML Schema, of every single esql
element and attribute.
- It is fairly human-readable and includes comments for the purpose of each
tag.</p>
-
- <p>A fairly common example is to list a query result in a table. Notice
that esql:results and esql:no-results
- are mutual exclusive. So only one of them will be in your XML tree. This
example takes a connection from a
- datasource defined in <code>cocoon.xconf</code>:</p>
+ </s1>
- <source><![CDATA[
+ <s1 title="Usage and Examples">
+ <p>At the moment documentation on esql is quite thin on the ground -
+ however, it should be enough to get you started. In the
+ <code>docs/samples/xsp</code> directory you will find
+ <code>esql.xsp</code>, which is an example of two esql queries,
+ demonstrating "nested" queries and dynamic prepared statements. However,
+ much more comprehensive is the <strong>schema</strong> in
+ <code>esql.xsd</code> which is a formal specification, written in the W3C
+ standard language XML Schema, of every single esql element and attribute.
+ It is fairly human-readable and includes comments for the purpose of each
+ tag.</p>
+
+ <p>A fairly common example is to list a query result in a table. Notice that
+ esql:results and esql:no-results are mutual exclusive. So only one of them
+ will be in your XML tree. This example takes a connection from a datasource
+ defined in <code>cocoon.xconf</code>:</p>
+
+ <source><![CDATA[
<esql:connection>
<esql:pool>connectionName</esql:pool>
<esql:execute-query>
@@ -88,24 +96,129 @@
</esql:connection>]]>
</source>
- <p>For more complex lists, often nested queries are
- needed. Esql allows arbitrary nesting of queries. However,
- you can do table joins and then insert a header and footer
- whenever a "watched" column value changes using the
- <code><esql:group/></code> and
- <code><esql:member/></code> tags. It follows the
- nesting ideology of <code><xsp:logic>
- ... <xsp:content></></></code>You can
- nest <code><esql:group></code> and
- <code><esql:member></code> indefinately.</p>
+ <s2 title="Refering to results">
+
+ <p>A select query usually returns one ResultSet. This case is handled by
+ the <code>esql:results</code> tag and its content. However, many special
+ cases exist, e.g. an error occurs or an update query is used. Esql
+ provides different tags for these cases.
+ </p>
+
+ <p>If an empty result set is returned, the <code>esql:no-results</code>
+ block is used.</p>
+
+ <s3 title="Errors">
+ <p>In case of an error, usually signalled by an Exception during setup or
+ execution of a query, the <code>esql:error-results</code> block is
+ evaluated. If no such tag exists, the exception is rethrown and
+ processing is stopped. Withing the tag, <code>esql:get-message</code>,
+ <code>esql:get-stacktrace</code>, and <code>esql:to-string</code> allow
+ access to the error message.</p>
+ </s3>
+
+ <s3 title="Limiting the number of rows returned">
+
+ <p>Esql allows to display only a part of the result set using the
+ <code>esql:use-limit-clause</code>. If your DBMS is supported, the DBMS
+ generates only the indicated rows, otherwise a number of rows are skipped
+ and retrieval is stopped after a given number of rows. It works like a
+ fixed-size window to the result set, paging through it.</p>
+
+ <p>These parameters are set for a connection.</p>
+
+ <p>If the <code>esql:use-limit-clause</code> is empty or set to "auto",
+ esql tries to determine automatically which method to use, depending on
+ the connection URL.</p>
+
+ <p><code>esql:skip-rows</code> and <code>esql:max-rows</code> tags specify
+ how many rows should be skipped at the beginning and how many rows should
+ be retrieved at maximum.</p>
+
+ <p>In this context the <code>esql:previous-results</code> and
+ <code>esql:more-results</code> blocks hold code and content that is only
+ used if this sliding window has previous or following windows.</p>
+
+ </s3>
+
+ <source><![CDATA[
+<esql:connection>
+ <esql:pool>connectionName</esql:pool>
+ <esql:execute-query>
+ <esql:query>SELECT mycolumn1,mycolumn2 FROM table</esql:query>
+ <esql:use-limit-clause>auto</esql:use-limit-clause>
+ <esql:skip-rows><xsp:expr>skiprows</xsp:expr></esql:skip-rows>
+ <esql:max-rows>10</esql:max-rows>
+ <esql:results>
+ <table>
+ <esql:row-results>
+ <esql:previous-results>previous rows available</esql:previous-results>
+ <esql:more-results>more rows available</esql:more-results>
+ <tr>
+ <td><esql:get-string column="mycolumn1"/></td>
+ <td><esql:get-string column="mycolumn2"/></td>
+ </tr>
+ </esql:row-results>
+ </table>
+ </esql:results>
+ <esql:error-results>An error occurred</esql:error-results>
+ <esql:no-results>
+ <p>Sorry, no results!</p>
+ </esql:no-results>
+ </esql:execute-query>
+</esql:connection>]]>
+</source>
+
+ <s3 title="Updates">
+ <p>In JDBC, updates, inserts, and deletes are "update queries". For those,
+ no results are available but an update count is returned, indicating,
+ how many rows were affected.</p>
+
+ <p>Code or content that depends on this has to be placed inside the
+ <code>esql:update-results</code> tag. It is used whenever at least one
+ row was affected. The update count can be accessed through the
+ <code>esql:get-update-count</code> tag.
+ </p>
+
+ <p>If no rows where affected, the <code>esql:no-results</code> block is
+ used.</p>
+ </s3>
+
+
+ <source><![CDATA[
+<esql:connection>
+ <esql:pool>connectionName</esql:pool>
+ <esql:execute-query>
+ <esql:query>update table set price=price*1.17</esql:query>
+ <esql:error-results>An error occurred</esql:error-results>
+ <esql:update-results>
+ <esql:get-update-count/> prices adjusted.
+ </esql:update-results>
+ <esql:no-results>
+ <p>Sorry, no prices adjusted!</p>
+ </esql:no-results>
+ </esql:execute-query>
+</esql:connection>]]>
+</source>
+
+ </s2>
- <source><![CDATA[
+ <s2 title="Groups">
+ <p>For more complex lists, often nested queries are needed. Esql allows
+ arbitrary nesting of queries. However, you can do table joins and then
+ insert a header whenever a "watched" column value changes using the
+ <code><esql:group/></code> and <code><esql:member/></code>
+ tags. It follows the nesting ideology of <code><xsp:logic> ...
+ <xsp:content></></></code>You can nest
+ <code><esql:group></code> and <code><esql:member></code>
+ indefinately.</p>
+
+ <source><![CDATA[
<esql:execute-query>
<esql:query>
- select committeeName, title, firstName, middleName, lastName, suffix,
-status from committeeMember left join directoryInformation using(userid)
-left join committee on committee.id=committeeMember.committeeid order by
-committeeName asc
+ select committeeName, title, firstName, middleName, lastName, suffix, status
+ from committeeMember left join directoryInformation using(userid)
+ left join committee on committee.id=committeeMember.committeeid
+ order by committeeName asc
</esql:query>
<esql:results>
<esql:row-results>
@@ -126,97 +239,180 @@
</esql:row-results>
</esql:results>
</esql:execute-query>]]>
- </source>
+ </source>
- <s2 title="Stored Procedure Support">
- <p>In order to use stored procedures replace
- <code><esql:query/></code> with
- <code><esql:call/></code>, use either DBMS specific syntax or
- JDBC escape syntax <code>{? = foo(?)}</code>. If your jdbc driver
- requires to use the <code>executeQuery()</code> method instead of
- the <code>execute()</code> method (like e.g. INFORMIX does), set
- <code>needs-query="true"</code> attribute.</p>
-
- <p>If a result set is returned through the (only) return parameter of
- a stored procedure, e.g. <code>resultset-from-object="1"</code> as attribute
- to <code><esql:call/></code>to automatically use this result
- set. For a more general alternative see further below.</p>
-
- <p>Parameters for a stored procedure call may be of
- <code>direction="in|out|inout"</code> with the usual JDBC meaning. In
- addition a <code>type</code> needs to be supplied as well. This would
- be the same "XXX" as used in a <code>get-XXX</code> JDBC-method
- call.</p>
-
- <p><code><esql:call-results/></code> (child of
- <code><esql:execute-query/></code>) may contain code that will
- always be executed whether the query returned a result or not. For
- example most stored procedures will not return a result set but
- several out parameters.</p>
-
- <p>all <code><esql:get-xxx/></code> tags accept new attribute
- <code>from-call="yes"</code> to indicate that the value is retrieved
- from the CallableStatement rather than the current
- ResultSet. Obviously, this only works after a call to a stored
- procedure.</p>
-
- <p>Retrieve a ResultSet from any column and use it like the result of a
- nested query. Supported child elements are
- <code><esql:row-results/></code> and
- <code><esql:no-results/></code>. Otherwise it behaves
- exactly like nesting queries. Thus the <code>ancestor</code>
- attribute can be used to access e.g. the original query.</p>
+ <p>One important limitation of the grouping feature is, that <em>no access
+ to a column may appear after closing a group.</em> The value will belong
+ to the following row or cause an error if no next row exists. If this is
+ needed, consider swapping columns using XSLT. Hence the following example
+ is illegal:</p>
+ </s2>
+ <source><![CDATA[
+<esql:execute-query>
+ <esql:query>
+ select committeeName, committeeTitle, title, firstName, middleName,
+ lastName, suffix, status
+ from committeeMember left join directoryInformation using(userid)
+ left join committee on committee.id=committeeMember.committeeid
+ order by committeeName asc
+ </esql:query>
+ <esql:results>
+ <esql:row-results>
+ <esql:group group-on="committeeName">
+ <h2><esql:get-string column="committeeName"/></h2>
+ <ul>
+ <esql:member>
+ <li>
+ <esql:get-string column="title"/>
+ <esql:get-string column="firstName"/>
+ <esql:get-string column="middleName"/>
+ <esql:get-string column="lastName"/>
+ <esql:get-string column="suffix"/>
+ </li>
+ </esql:member>
+ </ul>
+ </esql:group>
+ <esql:get-string column="committeeTitle"/><!-- illegal !! -->
+ </esql:row-results>
+ </esql:results>
+</esql:execute-query>]]>
+ </source>
+
+
+ <s2 title="Stored Procedure Support">
+ <p>In order to use stored procedures replace
+ <code><esql:query/></code> with <code><esql:call/></code>, use
+ either DBMS specific syntax or JDBC escape syntax <code>{? =
+ foo(?)}</code>. If your jdbc driver requires to use the
+ <code>executeQuery()</code> method instead of the <code>execute()</code>
+ method (like e.g. INFORMIX does), set <code>needs-query="true"</code>
+ attribute.</p>
+
+ <p>If a result set is returned through the (only) return parameter of a
+ stored procedure, e.g. <code>resultset-from-object="1"</code> as attribute
+ to <code><esql:call/></code>to automatically use this result set.
+ For a more general alternative see further below.</p>
+
+ <p>Parameters for a stored procedure call may be of
+ <code>direction="in|out|inout"</code> with the usual JDBC meaning. In
+ addition a <code>type</code> needs to be supplied as well. This would be
+ the same "XXX" as used in a <code>get-XXX</code> JDBC-method call.</p>
+
+ <p><code><esql:call-results/></code> (child of
+ <code><esql:execute-query/></code>) may contain code that will
+ always be executed whether the query returned a result or not. For example
+ most stored procedures will not return a result set but several out
+ parameters.</p>
+
+ <p>all <code><esql:get-xxx/></code> tags accept new attribute
+ <code>from-call="yes"</code> to indicate that the value is retrieved from
+ the CallableStatement rather than the current ResultSet. Obviously, this
+ only works after a call to a stored procedure.</p>
+
+ <p>Retrieve a ResultSet from any column and use it like the result of a
+ nested query with the <code>esql:use-results</code> tag. It behaves
+ exactly like nesting queries. Thus the <code>ancestor</code> attribute can
+ be used to access e.g. the original query.</p>
- <p>Example:</p>
-<source><![CDATA[
+
+ <p>Example:</p>
+ <source><![CDATA[
<esql:call>{? = foo(<esql:parameter direction="in"
type="Int"><xsp:expr>1</xsp:expr></esql:parameter>)}
</esql:call>
<esql:call-results>
- <esql:results>
+ <esql:use-results>
<esql:result><xsp:expr>(ResultSet)<esql:get-object column="1"
from-call="true"/></xsp:expr></esql:result>
<esql:row-results>
<esql:get-string column="1"/>
</esql:row-results>
- </esql:results>
+ </esql:use-results>
</esql:call-results>
]]></source>
- <p>Example:</p>
-<source><![CDATA[
+
+ <p>Example:</p>
+ <source><![CDATA[
<esql:query>select name, list_of_aliases from table</esql:query>
<esql:results>
<esql:row-results>
<p>
<esql:get-string column="name"/>:
- <esql:results>
+ <esql:use-results>
<esql:result><xsp:expr><esql:get-array
column="list_of_aliases"/>.getResultSet()</xsp:expr></esql:result>
<esql:row-results>
<esql:get-string column="1"/>
</esql:row-results>
- </esql:results>
+ </esql:use-results>
</p>
</esql:row-results>
</esql:results>
]]></source>
- </s2>
-
- <p>The ultimate reference, is of course the source code, which is an XSLT
logicsheet contained in the
- file
<code>src/org/apache/cocoon/components/language/markup/xsp/java/esql.xsl</code></p>
- <p>Of course, we would be very grateful for any improvements on this
documentation
- or further examples - please send them to
- <link
href="mailto:[EMAIL PROTECTED]";>[EMAIL PROTECTED]</link>!</p>
+ </s2>
- </s1>
+ <s2 title="Multiple Results">
+ <p>If multiple results are returned from a stored procedure or a query, the
+ <code>esql:results</code> block is reused. However, it is supported to
+ have different blocks for each result. Since a result can either be a
+ ResultSet or an UpdateCount, both are counted independently. The nth
+ ResultSet will be handled by the nth <code>esql:results</code> block, or -
+ if there are fewer blocks - the last one.</p>
+
+ <p>The same holds true for <code>esql:update-results</code> and
+ <code>esql:no-results</code> blocks as well.</p>
+
+
+ <p>Example: Suppose stored procedure <code>bar</code> returns an update
+ count, another update count, a result set, an update count, and a
+ last result set.</p>
+
+ <source><![CDATA[
+<esql:call>{? = bar(<esql:parameter direction="in"
+type="Int"><xsp:expr>1</xsp:expr></esql:parameter>)}
+</esql:call>
+<esql:results>
+ <!-- this is used for the first result set -->
+</esql:results>
+<esql:results>
+ <!-- this is used for the second and
+ all following result sets -->
+</esql:results>
+<esql:update-results>
+ <!-- this is used for the first update count -->
+</esql:update-results>
+<esql:no-results>
+ <!-- this is used for the first update count -->
+</esql:no-results>
+<esql:update-results>
+ <!-- this is used for the second and
+ all following update counts -->
+</esql:update-results>
+<esql:no-results>
+ <!-- this is used for the second and
+ all following update counts -->
+</esql:no-results>
+]]></source>
+ </s2>
- <s1 title="Template Descriptions">
- <table>
- <tr>
- <th>Tag</th>
- <th>Description</th>
- </tr>
+ <p>The ultimate reference, is of course the source code, which is an XSLT
+ logicsheet contained in the file
+
<code>src/org/apache/cocoon/components/language/markup/xsp/java/esql.xsl</code></p>
+
+ <p>Of course, we would be very grateful for any improvements on this
+ documentation or further examples - please send them to <link
+
href="mailto:[EMAIL PROTECTED]";>[EMAIL PROTECTED]</link>!</p>
+
+ </s1>
+
+
+ <s1 title="Template Descriptions">
+ <table>
+ <tr>
+ <th>Tag</th>
+ <th>Description</th>
+ </tr>
<tr><td>esql:row-results//esql:get-columns</td>
<td>results in a set of elements whose names are the names of the
columns. the elements each have one text child, whose value is the value of the column
interpreted as a string. No special formatting is allowed here. If you want to mess
around with the names of the elements or the value of the text field, use the
type-specific get methods and write out the result fragment yourself. For Cocoon 2
only, this outputs structured types as well. Here sql-list or sql-set contains several
sql-list-item or sql-set-item element that again contain the actual data.</td>
</tr>
@@ -258,7 +454,7 @@
</tr>
<tr><td>esql:row-results//esql:get-array</td>
<td>returns the value of the given column as an java.sql.Array. This is
frequently used for collection
- datatypes like lists, sets, bags etc.</td>
+ datatypes like lists, sets, bags etc.</td>
</tr>
<tr><td>esql:row-results//esql:get-struct</td>
<td>returns the value of the given column as a java.sql.Struct. This is
frequently used for row types.</td>
@@ -302,12 +498,12 @@
<tr><td>esql:results/esql:get-resultset</td>
<td>returns the current resultset</td>
</tr>
- <tr><td>esql:group</td>
- <td>Allows header and footer elements around groups of consecutive records
with identical values in column named by @group-on. Facilitates a single query with
joins to be used in lieu of some nested queries.</td>
+ <tr><td>esql:group</td>
+ <td>Allows header elements around groups of consecutive records with
identical values in column named by @group-on. Facilitates a single query with joins
to be used in lieu of some nested queries.</td>
</tr>
- <tr><td>esql:member</td>
- <td>Used in conjunction with and nested inside esql:group. Formatting for
individual records goes within esql:member. Header and footer stuff goes in between
group and member.</td>
- </tr>
+ <tr><td>esql:member</td>
+ <td>Used in conjunction with and nested inside esql:group. Formatting for
individual records goes within esql:member. Header stuff goes in between group and
member.</td>
+ </tr>
<tr><td>@*|node()</td>
<td>used internally to determine which column is the given column. if a
column attribute exists and its value is a number, it is taken to be the column's
position. if the value is not a number, it is taken to be the column's name. if a
column attribute does not exist, an esql:column element is assumed to exist and to
render as a string (after all of the xsp instructions have been evaluated), which is
taken to be the column's name.</td>
</tr>
----------------------------------------------------------------------
In case of troubles, e-mail: [EMAIL PROTECTED]
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
