Database Accessor Guide

A guide to database access with mod_db

Intro

mod_db supplies a number of standard SQL Database Accessors, this document discusses their configuration and use.

Configuration

The configuration document specifies the JDBC driver class and database username and password information. By default all database accessors look for their configuration in ffcpl:/etc/ConfigRDBMS.xml, generally an application will supply a single configuration document and all of the DB accessor requests will use this. The configuration document has the following form.

<config>
  <rdbms>
    <jdbcDriver>com.mysql.jdbc.Driver</jdbcDriver>
    <jdbcConnection>jdbc:mysql://host:port/db?user=foo&password=bar</jdbcConnection>
  </rdbms>
</config>

You must ensure that a suitable JDBC driver class is accessible from your application module (either by putting the JDBC library in the /lib directory of your module) or importing a module that can supply the driver.

In certain cases it is useful to specify an alternative config - to do this each accessor can be given a <configuration> argument providing the URI or literal configuration document to use with that specific request.

Standard Queries

mod_db standard accessors include sqlQuery, sqlBooleanQuery, sqlUpdate and sqlBatch

sqlQuery

An SQL query can be made as follows...

<instr>
  <type>sqlQuery</type>
  <operand>
    <sql>SELECT * FROM sometable;</sql>
  </operand>
  <target>this:response</target>
</instr>

The sqlQuery accessor returns results in XML form. Note since the accessor automatically converts the JDBC Result Set to XML form it is important that your database table's column names are suitable for use as XML element names. The form of the XML document is as follows...

<results>
  <row>
    <yourcolumnname>value</yourcolumnname> ...
  </row>
</results>

sqlBooleanQuery

A Boolean SQL query returns a canonical true or false document. The result is true if the JDBC result set contains any rows otherwise false.

<instr>
  <type>sqlBooleanQuery</type>
  <operand>
    <sql>SELECT id FROM sometable WHERE name='Mambo';</sql>
  </operand>
  <target>this:response</target>
</instr>

Returns a boolean canonical document...

sqlUpdate

Table updates (SQL INSERT, UPDATE) can be performed with the sqlUpdate accessor. This returns an XML document containing the number of updated rows.

<instr>
  <type>sqlUpdate</type>
  <operand>
    <sql>INSERT INTO sometable values (null, 'Balti', 'cat');</sql>
  </operand>
  <target>this:response</target>
</instr>

sqlBatch

Multiple Queries and updates can be performed with the sqlBatch accessor. This returns an XML document containing the statement number and the number of effected rows. Note this accessor requires a <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>

Adding your own methods

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.

Typical Usage Patterns

Working directly with SQL from the XML domain is very powerful. Some typical patterns include...

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>

1060^® NetKernel^TM Documentation
(C) 2003-2004 1060 Research Limited

Send Feedback