sunlabs.brazil.sql
Class SqlTemplate

java.lang.Object
  sunlabs.brazil.template.Template
      sunlabs.brazil.sql.SqlTemplate (view source)

All Implemented Interfaces:

TemplateInterface

public class SqlTemplate
extends Template

Sample Template class for running SQL queries via jdbc and placing the results into the request properties for further processing.

Foreach session, a connection is made to one or more sql database via jdbc. Session reconnection is attempted if the server connection breaks. An SQL query is issued, with the results populating the request properties.

There are two ways to use this template (and they may be used together): static and dynamic. For static configurations, the jdbc URL's and additional properties are defined in the configuration file, and database connections are represented by "tokens". For the dynamic onfigurations, the jdbc URL's are passed as attributes to the sql tag.

Configuration options:

drivers

A list of java classes that will be used as JDBC drivers for the database connectors. At least one driver should be provided.

databases

A list of tokens that represent databases that may be accessed by this template. For each token, the properties:

token.url

Specifies a JDBC URL to use to connect to the database.

token.sqlPrefix

Contains a list of tokens that represent additional information that should be provided for a connection, in the form of one or more "token.name", "token.value" pairs.

token.user, token.passwd

The user and password associated with this connection. If "user" is specified, then the tokens in "sqlPrefix" are ignored.

For a discussion of how the tag attributes and how the results results map to properties, see below.

Field Summary

Fields inherited from class sunlabs.brazil.template.Template

debug

Constructor Summary

SqlTemplate()

Method Summary

Statement	doSQL(Connection con, String query, String type, int timeout)
boolean	init(RewriteContext hr) Gather up all the databases and their credentials.
void	tag_slash_sql(RewriteContext hr)
void	tag_sql(RewriteContext hr) Replace the SQL query with the appropriate request properties.

Methods inherited from class sunlabs.brazil.template.Template

done

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

SqlTemplate

public SqlTemplate()

Method Detail

init

public boolean init(RewriteContext hr)

Gather up all the databases and their credentials.

Specified by:

init in interface TemplateInterface

Overrides:

init in class Template

tag_sql

public void tag_sql(RewriteContext hr)

Replace the SQL query with the appropriate request properties. Look for the following parameters: (NOTE - This interface is preliminary, and subject to change).

debug

Include diagnostics in html comments

prefix

prefix to prepend to all results. Defaults to the database token

database

the database to use. This is either one of the database tokens used with the "databases" configuration parameter, or a fully qualified jdbc url (e.g. "jdbc:..." to use for this database. if the jdbc url form is used, the driver must have already been specified, and no optional parameters can be provided for the connection.

max

The max # of rows returned (default=100)

na

Value to reurn for NULL. Defaults to "n/a"

type

The type of SQL command, one of "query", "system", or "update". these values map to the JDBC calls executeQuery(), execute() and executeUpdate() respectively. Defaults to "query".

timeout

The number of seconds to wait for the query to finish. Defaults to "0": wait forever

eval

If present, do ${...} to entire query. (see getProperty).

zeroIndex

if true, row counts start at 0, not 1

index

If present, use column 1 as part of the name. Otherwise, an index name is invented.

noTable

If true, the table name is not encoded as part of the result

close

If true, the database copnnection will be closed after the query conpletes.

For all queries, the following properties (with the prefix prepended) are set:

columncount

The number of columns returned

rowcount

The number of rows returned

Foreach entry in the resultant table, its property is: ${prefix}.${table_name}.${columname}.${key}. If the index parameter is set, the key is the value of the first column returned. Otherwise the key is the row number, and the additional property ${prefix}.rows contains a list of all the row numbers returned.

doSQL

public Statement doSQL(Connection con,
                       String query,
                       String type,
                       int timeout)
                throws SQLException

Throws:

SQLException

tag_slash_sql

public void tag_slash_sql(RewriteContext hr)