Client Tools

A number of services are provided that allow SQL operations to be performed on a database. In all cases the result sets are available as XML documents.

sqlQuery	Query a database with an SQL statement
sqlUpdate	Update a database with an SQL statement
sqlBatch	Batch process multiple SQL statements

A number of services information to and from Binary Large Object Blocks (BLOB) RDBMS fields. The resources are processed as binary streams.

sqlQueryBlob	retrieve Blob objects from an RDBMS
sqlUpdateBlob	store a resource as a Blob in an RDBMS

In addition to the primary operations a number of utilities are provided for working with XML and SQL.

SQLEscapeXML	escape XML text elements for use in SQL statements
SQLEncodeElement	encode/decode XML fragments for storing/retrieving to/from an database text field

To wrap a sequence of operations within a JDBC transaction see:

sqlTransactionWrapper

wrap a sequence of database interactions within a transaction

Configuration

JDBC

At the physical level NetKernel uses Java JDBC technology to access databases. Java JDBC requires a database driver packaged in a JAR file that is specific to each database management system.

The mod_db module includes JDBC JAR files for the HSQLDB and the MySQL database management systems. If you wish to connect to another database management system you must place the appropriate JDBC JAR file into the /lib directory within your application module. Alternatively, you may add the driver JAR to the JVM classpath, you do this by editing the startup script you use to boot NetKernel.

ConfigRDBMS.xml

The mod_db module is configured with the resource ffcpl:/etc/ConfigRDBMS.xml which supplies information to configure JDBC for the particular database management system.

element	description
jdbcDriver	The fully qualified Java classname of the JDBC driver for the particular RDBMS.
jdbcConnection	The JDBC driver-specific connection string required to establish a connection with the RDBMS.

The configuration document specifies the JDBC driver class and database username and password information. If the optional <user> and <password> tags are present they will take precedence over any user and password fields on the JDBC connection URI

The mod_db module maintains a connection pool that grows to meet concurrent demands but which will not exceed poolSize connections. An exception is thrown if more connections are requested than are provided by the configuration. If you would prefer to block requests until a connection is available instead of dealing with an exception you may wrap database access with a throttle to limit concurrency.

The <defaultQueryTimeout> tag specifies a timeout value in seconds for all sql statements to execute within. If this time is exceeded by any individual query the query fails and an exception is thrown. By default there is no timeout. Different JDBC implementation support different methods of terminating long running queries. By default this accessor uses the java.sql.Statement.setQueryTimeout() method. It is also possible to specify a timer based approach which will call the java.sql.Statement.cancel() method after the timeout period. This choice can be made by specifying a <queryTimeoutMethod> tag in the configuration with either the value SET or TIMER.

Any additional child elements placed in the configuration will be added to the jdbc connection properties.

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.

Transactions

By default all accessors operate with autocommit enabled (see JDBC documentation for more details). It is possible to wrap services inside a transaction wrapper to group database requests and commit and rollback based on outcome of the service.