The SQL logicsheet is deprecated and no longer supported, and it is recommended that you use the ESQL logicsheet instead. This documentation is provided for those who are still using the SQL logicsheet.

The SQL 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 Cocoon.

Check your cocoon.properties for this line and add it if it's not already there:

	processor.xsp.logicsheet.sql.java = resource://org/apache/cocoon/processor/xsp/library/sql/sql.xsl

Note the line break is for formatting purposes, it should not appear in your cocoon.properties file.

If you are going to use connection pools, then make sure that you have followed the installation steps for the connection pools.

Map the

	http://www.apache.org/1999/SQL

namespace to the sql prefix. Elements in the sql taglib namespace will be interpreted as input to the sql taglib and will be stripped from the output.

This is typically done like this:

	<xsp:page language="java" xmlns:sql="http://www.apache.org/1999/SQL" xmlns:xsp="http://www.apache.org/1999/XSP/Core" > . . . </xsp:page>

The only element in the sql taglib namespace that may appear as a descendent of an element in another namespace is the execute-query element. It indicates that a sql query should be executed. The result XML will replace this element.

The execute-query element will contain child elements in the sql taglib namespace which specify parameters for the query. It is an error if it contains children (not necessarily descendents) in another namespace. No other element in the sql taglib namespace may contain children in the sql taglib namespace.

The list of valid sql taglib configuration elements is:

driver (mandatory*) -

The fully qualified class name of the JDBC driver

dburl (mandatory*) -

The JDBC URL to the data source

use-connection (mandatory**) -

The name of the connection in the JDBC connection pool configured in cocoon.properties.

query (mandatory) -

The SQL query to execute.

username* -

The username for database authentication

password* -

The password for database authentication

doc-element -

The name of the element which will wrap the entire resultset, if there are any results. If this is not set, there will be no top-level wrapper.

row-element -

The name of the element which will wrap each row in the resultset. If this is not set, there will be no row-level wrapper.

null-indicator -

If this is equal to 'y' or 'yes', null column values will be output as an element with attribute NULL="YES". Otherwise null columns will be skipped.

tag-case -

The case the result column element names should be. Options are preserve (use whatever the database engine gives us), lower, and upper. The default is preserve.

id-attribute -

The name of the attribute to set on the result's row elements to uniquely identify each row. if this is null, no unique attribute will be created.

id-attribute-column -

The name of the column in the resultset to use for the id attribute. Typically this will be a primary key column. If this is not set, the id attribute value will be the row's position in the rowset.

max-rows -

The maximum number of rows to return.

skip-rows -

The number of rows to skip before returning rows.

count-attribute -

The name of the attribute of the result doc element whose value will contain the total number of rows returned by the query. NOTE - this is not the same as the number of rows returned the taglib if the max-rows element is set.

query-attribute -

The name of the attribute of the result doc element whose value will contain the actual SQL query that was executed.

skip-rows-attribute -

The name of the attribute of the result doc element whose value will contain the actual number of rows skipped.

max-rows-attribute -

The name of the attribute of the result doc element whose value will contain the actual maximum number of rows.

update-rows-attribute -

The name of the attribute of the result doc element whose value will contain the actual number of rows updated (if this query was not a SELECT).

namespace -

The namespace prefix to use when creating result elements. FIXME - we should ask for a namespace URI and prefix, right?

column-format -

Indicates that a column needs special formatting. See the column formatting section.

* -

Indicates that this is manditory ONLY if you are NOT using the connection pool

** -

Indicates that this is manditory ONLY if you ARE using the connection pool

Note that the query element may contain elements from other XSP-enabled namespaces, like request or session. The others may not right now, though I'm certainly willing to consider changing that if desired. I'm still new at this XSP thing too.

Let's step through an example of using a connection defined to be "helpdesk" that uses oracle. In the cocoon.properties have something simular to this:

	#### ORACLE processor.xsp.pool.database.helpdesk.driver=oracle.jdbc.driver.OracleDriver processor.xsp.pool.database.helpdesk.url=jdbc:oracle:thin:@localhost:1521:dldf processor.xsp.pool.database.helpdesk.username=dnUser processor.xsp.pool.database.helpdesk.password=dbPass # The number of database connections to cache in the ConnectionPool. processor.xsp.pool.database.helpdesk.maxConnections=3

then in my xml file, I would use the connection pool name "helpdesk" as:

	<sql:execute-query> <sql:use-connection>helpdesk</sql:use-connection> <sql:skip-rows>0</sql:skip-rows> <sql:max-rows>50</sql:max-rows> <sql:null-indicator>y</sql:null-indicator> <sql:count-attribute>BOB</sql:count-attribute> <sql:doc-element>ROWSET</sql:doc-element> <sql:row-element>ROW</sql:row-element> <sql:tag-case>preserve</sql:tag-case> <sql:id-attribute>ID</sql:id-attribute> <sql:query> SELECT last_name, first_name, initials FROM EMPLOYEE </sql:query> </sql:execute-query>

Notice that for the connection pool, you do not use the driver, dburl, username, nor password tags. Lets look at an example that does not use the connection pool:

	<sql:execute-query> <sql:driver>com.informix.jdbc.IfxDriver</sql:driver> <sql:dburl>jdbc:informix-sqli://localhost:1526/database:informixserver=server</sql:dburl> <sql:username>dbUser</sql:username> <sql:password>dbPass</sql:password> <sql:skip-rows>0</sql:skip-rows> <sql:max-rows>50</sql:max-rows> <sql:tag-case>preserve</sql:tag-case> <sql:count-attribute>count</sql:count-attribute> <sql:doc-element>ROWSET</sql:doc-element> <sql:row-element>ROW</sql:row-element> <sql:null-indicator>omit</sql:null-indicator> <sql:id-attribute>ID</sql:id-attribute> <sql:query>SELECT * FROM CUSTOMER;</sql:query> </sql:execute-query>

The SQL logicsheet will try to name the column elements with the names of the columns from the queries, applying case styling as indicated by the sql:tag-case element. You must ensure that those column names are valid XML element names - e.g. no spaces or strange characters. For instance, MAX(*) is forbidden. You must alias these columns with the AS command - MAX(*) AS THEMAX.

Generally, column values are formatted naively - the taglib requests an Object from the ResultSet and invoked the toString() method. Some columns return byte or character arrays - in that case, we construct a new String using the array as a construction argument.

These defaults may be overridden by supplying one or more sql:column-format elements. Each sql:column-format element may contain these child elements:

name (mandatory) -

The name of the column (after the case translation occurs) to which the formatting should be applied

class (mandatory) -

The fully qualified class name of the formatter class. This must be a subclass of java.text.Format.

parameter -

A construction parameter for the formatter class. There may be more than one parameters.

For each sql:column-format element, a new instance of the Format subclass specified by the sql:class element is constructed using the construction parameters specified by the sql:parameter elements. The constructor must accept only Strings as parameters; if other datatypes are required for a class you wish to use, you must provide your own decorator wrapper for that class. Values from the named column are formatting using the format method. Here is an example:

	<sql:column-format> <sql:name>my_date</sql:name> <sql:class>java.text.SimpleDateFormat</sql:class> <sql:parameter>MM/dd/yyyy</sql:parameter> </sql:column-format>