SQL

Attributes

URI:	http://www.xmlpipe.org/xpe/util/filter/sql
Type:	filter
Namespace:	http://www.xmlpipe.org/xpe/sql
Owner:	http://www.xmlpipe.org/xpe

Description

This filter allows XPE pipelines to communicate with SQL databases by declaring transactions and specifying the SQL to execute against a database. This filter recognises elements within its namespace and leaves all other elements untouched.

Elements

Element name

Description

sql:connect

Main outer element. This element is mandatory and specifies how a database connection is established. A number of optional attributes that may be used to indicate how a database connection is to be established (j2ee, dataSource, driver. One of these must be specified.

Attributes

Attribute name	Description
j2ee	Specifies an optional j2ee JDBC connection pool to utilise for the connection
dataSource	Specifies an optional XPE connection pool data source to utilise for the connection.
driver	The driver attribute is an optional attribute that specifies a jdbc driver (eg: driver="com.mysql.jdbc.Driver") to be used in creating a connection immediately and not be sourced from either an XPE or j2ee connection pool. If present the username and password attributes are also required. This approach is not recommended unless the JDBC drivers being used support connection pooling natively. This is because establishing a JDBC connection using non-pooling JDBC drivers is time-consuming and inefficient.
username	Specifies the username to be used in establishing a connection. Only relevant when the driver attribute is also specified.
password	Specifies the password to be used in establishing a connection. Only relevant when the driver attribute is also specified.

Example

   <sql:connect dataSource="dev" xmlns:sql="http://www.softtouchit.com/xpe/sql" >
      ...
   </sql:connect>

sql:transaction>

The transaction element defines a transaction boundary. Any queries or updates within the same transaction element belong to the same transaction. All or none of the executed sql statements will be commited.

Example

   <sql:transaction xmlns:sql="http://www.softtouchit.com/xpe/sql" >
      <sql:query rowName="authenticationUpdate" >
         <sql:update>
            <sql:sql>
               update authentication set sessionid ='<xsl:value-of select="./sessionId"/>' where username='<xsl:value-of select="./username"/>'and password='<xsl:value-of select="./password"/>'
            </sql:sql>
         </sql:update>
         <sql:update>
            <sql:sql>
               update audit set sessionid ='<xsl:value-of select="./sessionId"/>' where username='<xsl:value-of select="./username"/>'and password='<xsl:value-of select="./password"/>'
            </sql:sql>
         </sql:update>
      </sql:query>
   </sql:transaction>

sql:query

The query element is used to define the sql query (usually SELECT) statements to be executed against the database.

Attributes

Attribute name	Description
name	The name attribute can be used to connect multiple sql queries together. If you set the name attribute when you know that the selection only returns one value, then XPE creates a ${name} variable, you can use in other sql queries. See further down for an example
start	The start attribute can be used to identify the first row of the query result set to return skipping the first start-1 rows. See further down for an example
limit	The limit attribute can be used to specify how many rows to limit the result set to. This attribute used in conjunction with the start attribute can be used to page through a resullt set. If omitted it defaults to 10. See further down for an example
iterator	Not sure how to use this.
rowName	The rowName attribute can be used to name the outer element of each result row.

Example

   <sql:connect dataSource="dev" xmlns:sql="http://www.softtouchit.com/xpe/sql" >
      <sql:transaction>
         <sql:query rowName="musicSelectCount" >
            <sql:sql>
               SELECT count(*) from music where composerName is not null
            </sql:sql>
         </sql:query>
      </sql:transaction>
   </sql:connect>

   <sql:connect dataSource="dev" xmlns:sql="http://www.softtouchit.com/xpe/sql" >
      <sql:transaction>
         <sql:query rowName="musicSelect" start="20" limit="25" >
            <sql:sql>
               SELECT * from music where composerName is not null
            </sql:sql>
         </sql:query>
      </sql:transaction>
   </sql:connect>

sql:update

The update element is used to define the sql update or insert statement(s) to be executed against the database.

Attributes

Attribute name	Description
on	The on attribute can be used in conjunction with the name and not attribute from the query element to selectively control which update sql (from a set) should be executed.
not	The not attribute can be used in conjunction with the name and on attribute from the query element to selectively control which update sql (from a set) should be executed.

Example

The following example illustrates the usage of these attributes:

   <sql:connect dataSource="cms" xmlns:sql="http://www.xmlpipe.org/xpe/sql" >
      <sql:transaction>
         <sql:query name="id" >
            <sql:sql>
               select id from abc where dd=123
            </sql:sql>
         </sql:query>
         <sql:update on="id" >
            <sql:sql>
               update abc set ....
            </sql:sql>
         </sql:update>
         <sql:update not="id" >
            <sql:sql>
               insert into abc ...
            </sql:sql>
         </sql:update>
      </sql:transaction>
   </sql:connect>

Getting a Database connection

This filter can get database connections in one of three ways:

From a J2EE JDBC Connection Pool.
From a XPE Connection Pool
Created on the fly.

All database transactions must be wrapped within a database connection. The establishment of a connection is by declaring a connect element.

Establishing a DB connection from a J2EE JDBC pool

This approach is suitable, if XPE is deployed inside a J2EE environment and there is a database connection pool avaialbe through Java naming service. The following example shows how to get a connection from a J2EE JDBC pool:

   <sql:connect j2ee="jdbc/cms" xmlns:sql="http://www.xmlpipe.org/xpe/sql" >
      ...
   </sql:connect>

The j2ee attribute defines the name of the connection pool.

Establishing a DB connection from a XPE connection pool

If a connection pool is declared within the applications db.xml file, then connections from this pool are available for others to access. The following illustrates how an XPE connection pool can be defined within the XPE db.xml file to create an Oracle connection pool:

   <db:dbs xmlns:db="http://www.org.xml/pipe/xpe/dataSource" >
      <db:db>
         <db:dataSource name="cms" maxActive="3000" maxWait="20" >
            <db:connection driver="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@localhost:1521:xe" username="oracle" password="oracle" />
         </db:dataSource>
      </db:db>
   </db:dbs>

Once defined, a connection from this pool can be accessed from an sql:connect element in the following way:

   <sql:connect dataSource="cms" xmlns:sql="http://www.xmlpipe.org/xpe/sql" >
      ...
   </sql:connect>

Remember that the jar file containing the jdbc drivers for your chosen database need to be placed in the XPE lib directory.

The name attribute defines the name of the XPE connection pool.

Optionally, one can also define username and password attributes to specify the username and password of the connection.

Establishing a JDBC connection on the fly

This approach is not recommended unless the JDBC drivers being used support connection pooling natively. This is because establishing a JDBC connection using non-pooling JDBC drivers is time-consuming.

   <sql:connect driver="com.mysql.jdbc.Driver" username="sql" password="test" xmlns:sql="http://www.xmlpipe.org/xpe/sql" >
      ...
   </sql:connect>

Defining a transaction boundary

The transaction element defines a transaction boundary. Any queries or updates within the same transaction element belong to the same transaction.

   <sql:connect dataSource="cms" xmlns:sql="http://www.xmlpipe.org/xpe/sql" >
      <sql:transaction>
         <sql:query rowName="dbSitemap" >
            <sql:sql>
               select * from cms_contents where uri = 'test'
            </sql:sql>
         </sql:query>
         <sql:update>
            <sql:sql>
               update cms_contents set value='abc' where uri = 'test'
            </sql:sql>
         </sql:update>
      </sql:transaction>
   </sql:connect>

Working with queries

The XPE SQL filter allows one to define many different ways of getting and setting data from a database. Any sql that retrieves data from a database must be defined in an sql:query element, while sql that updates or inserts data into a table must be defined in a sql:update element.

The previous example illustrates both these elements in action.

The query element

<sql:query name="{name}" start="{first row to return}" limit="{max rows to return}" iterator="{yes|no}" rowName="{name}" xmlns:sql="http://www.xmlpipe.org/xpe/sql/2.0" />

The query element has a number of attributes that control its behaviour and function. These are:

name
start
iterator
limit
rowName

The name attribute

The name attribute can be used to connect multiple sql queries together. If you set the name attribute when you know that the selection only returns one value, then XPE creates a ${name} variable, you can use in other sql queries.

For example:

   <sql:connect dataSource="cms" xmlns:sql="http://www.xmlpipe.org/xpe/sql" >
      <sql:transaction>
         <sql:query name="id" >
            <sql:sql>
               select id from abc where dd=123
            </sql:sql>
         </sql:query>
         <sql:query>
            <sql:sql>
               select jj from abc where id=${id}
            </sql:sql>
         </sql:query>
      </sql:transaction>
   </sql:connect>

The start attribute

The start attribute can be used to identify the first row of the query result set to return. For example the following fragment will return the rows starting with the 10th result set row - ignoring the first nine rows:

   <sql:connect dataSource="cms" xmlns:sql="http://www.xmlpipe.org/xpe/sql" >
      <sql:transaction>
         <sql:query start="10" >
            <sql:sql>
               select id from abc
            </sql:sql>
         </sql:query>
      </sql:transaction>
   </sql:connect>

The limit attribute

The limit attribute can be used to limit the number of rows returned. For example the following fragment will return just the first 50 rows (if they exist). If the sql would normally return more than 50 rows, then rows 51 onward are ignored.

   <sql:connect dataSource="cms" xmlns:sql="http://www.xmlpipe.org/xpe/sql" >
      <sql:transaction>
         <sql:query limit="50" >
            <sql:sql>
               select id from abc
            </sql:sql>
         </sql:query>
      </sql:transaction>
   </sql:connect>

The limit and start attributes are usually used together to systematically return blocks of data for a given sql query. In this way very efficient page or block based scanning of data can be achieved.

The rowName attribute can be used to name the outer element of each result row. For example the following fragment will create an XML result set with an element named user for each row returned:

   <result xmlns="" >
      <sql:connect dataSource="cms" xmlns:sql="http://www.xmlpipe.org/xpe/sql" >
         <sql:transaction>
            <sql:query rowName="user" >
               <sql:sql>
                  select id from user
               </sql:sql>
            </sql:query>
         </sql:transaction>
      </sql:connect>
   </result>

The following XML fragment is typical of the result from such a query:

The iterator attribute

The iterator attribute can be set to either 'yes' or 'no' and indicates whether a java iterator is to be created for a given query.

The update element

The update element is used instead of the query element when a database update or insert is required.

<sql:update on="{name}" not="{name}" xmlns:sql="http://www.xmlpipe.org/xpe/sql/2.0" />

The folowing example shows the basic usage of the update element:

   <sql:connect dataSource="cms" xmlns:sql="http://www.xmlpipe.org/xpe/sql" >
      <sql:transaction>
         <sql:update>
            <sql:sql>
               update cms_contents set value='abc' where uri = 'test'
            </sql:sql>
         </sql:update>
      </sql:transaction>
   </sql:connect>

This element has a number of optional attributes that control its behaviour and function. These are:

The on and not attributes

The on and notattributes can be used in conjunction with the name attribute from the query element to selectively control which update sql (from a set) should be executed. The following example illustrates the usage of these attributes:

In this example a named select is executed first. Then if the select returns a single result (that is there is a record in the db satisfying the select sql), then the update specifying on="id" is executed otherwise the update (insert) specifying not="id" is executed.