Examples
sqlQuery
The
sqlQuery
service will send an SQL query to a database and return
an XML document with the results.
<instr>
<type>sqlQuery</type>
<operand>
<sql>SELECT * FROM sometable;</sql>
</operand>
<target>this:response</target>
</instr>
The result of the query is returned in an XML document that uses column names as XML element names.
You must take care to specify column names that can fill this role.
<results>
<row>
<column-name>value</column-name> ...
</row>
</results>
sqlUpdate
Inserting or updating information in a relational table can be
performed with the
sqlUpdate
service.
<instr>
<type>sqlUpdate</type>
<operand>
<sql>INSERT INTO sometable values (null, 'Balti', 'cat');</sql>
</operand>
<target>this:response</target>
</instr>
This returns an XML document containing the number of rows inserted or updated.
The returned XML document looks like
<updated-rows>123</updated-rows>
sqlBatch
Multiple SQL statements can be performed in one request using the
sqlBatch
service.
This service uses an XML <batch> document containing one or more <sql> elements
<instr>
<type>sqlBatch</type>
<operand>
<batch>
<sql>DELETE * FROM sometable WHERE type='bird';</sql>
<sql>UPDATE sometable SET name='Newname' WHERE name='Oldname';</sql>
</batch>
</operand>
<target>this:response</target>
</instr>
This service returns an XML document containing the statement by position and the success/failure of the operation.
<batch-status>
<batch>1</batch>
<batch>1</batch>
</batch-status>
sqlBooleanQuery
A Boolean SQL query returns a
canonical boolean document
The result is true if the JDBC result set contains any rows otherwise it is false.
<instr>
<type>sqlBooleanQuery</type>
<operand>
<sql>SELECT id FROM sometable WHERE name='Mambo';</sql>
</operand>
<target>this:response</target>
</instr>
STM Template Filling
In this pattern STM is used to fill an SQL template document with parameters substituted from a supplied parameter document. This is a very efficient pattern with low cost of generation, very useful for sqlQuery/sqlBooleanQuery and simple INSERTs or UPDATEs.
<fragment>
<instr>
<type>stm</type>
<operand>
<sql />
</operand>
<operator>
<stm:group xmlns:stm="http://1060.org/stm">
<stm:set xpath="/sql"> UPDATE entries SET title='
<stm:param xpath="/nvp/title/text()" />', entry='
<stm:param xpath="/nvp/entry/text()" />', summary='
<stm:param xpath="/nvp/summary/text()" />', modified=NOW() WHERE publicid='
<stm:param xpath="/nvp/publicid/text()" />';
</stm:set>
</stm:group>
</operator>
<param>var:encoded</param>
<target>var:sql</target>
</instr>
<instr>
<type>sqlUpdate</type>
<operand>var:sql</operand>
<target>this:response</target>
</instr>
</fragment>
XSLT Transform to SQL
This pattern transforms an XML document to a SQL query or batch document. This is useful for multiple updates or complex queries.
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="xml" />
<xsl:param name="param" />
<xsl:template match="/nvp">
<sql> INSERT INTO entries VALUES ( null,
<xsl:value-of select="$param/new/blogid/text()" />, '
<xsl:value-of select="title" />',
<xsl:value-of select="$param/new/userid/text()" />, NOW(), NOW(),
<xsl:choose>
<xsl:when test="contenttype=1"> '<div>
<xsl:value-of select="entry" /><div>',
</xsl:when>
<xsl:otherwise> '
<xsl:value-of select="entry" />'
</xsl:otherwise>
</xsl:choose> '
<xsl:value-of select="$param/new/guid/text()" />' );
</sql>
</xsl:template>
</xsl:stylesheet>
Other
If you need to interact directly with the JDBC Result Set you can easily subclass org.ten60.rdbms.accessor.RDBMSAccessorImpl and add your own methods. Follow the examples of the standard methods as a starting point.
Preface
Cron
RDBMS
