Mod DB Guide

Index > Books > Declarative Development Guide > Reference Library > Developer Guides > Mod DB Guide

Intro

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

JDBC Configuration

You must ensure that a suitable JDBC driver class is accessible from your application module. You can either place your prefered JDBC driver jar file in the <install>/modules/mod_db/lib/directory where it will be automatically detected or alternatively, for restricted access, you could put it in the /lib directory of your module. As a final option you could add the JDBC driver to the JVM classpath, you do this by editing the startup script you use to boot NetKernel. Any of these options will make the JDBC driver available to your module.

ConfigRDBMS.xml

A 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>
    <user>optionalUsername</user>
    <password>optionalPassword</password>
  </rdbms>
</config>

If the optional <user> and <password> tags are present they will take precendence over any user and password fields on the JDBC connection URI

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>