Database Connections

Scriptrunner requires a JDBC 3.0 datasource, preferably with connection pooling.

How to define a connection in XML?

Add a connection element to a task, e.g.

<task type="SQL" name="Sample">
  <connections>
    <connection dataSourceClass="org.postgresql.ds.PGPoolingDataSource" 
                dataSourceName="mt_app" 
                serverName="localhost" databaseName="postgres" 
                user="mt_user" password="empty" />
  </connections>
  SELECT * FROM ...
</task>
The dataSourceClass attribute points to a class implementing javax.sql.ConnectionPoolDataSource or, in the worst case, simply javax.sql.DataSource.

The other attributes shown above are standard in JDBC 2.0. Any of the standard JDBC properties can be set as attributes. These are: dataSourceName, databaseName, description, networkProtocol, password, portNumber, roleName, serverName, and user. Ideally, the dataSourceName would be unique in the full XML document. However this is not enforced.

For some operations, the specific type of DBMS is needed. Usually this is guessed from the dataSourceClass or Connection object class. However, it can be set using the dataSourceType attribute, taking value ORACLE or POSTGRES.

There are two special dataSourceNames: srAudit and Default. Where the ScriptRunner audit trail is being saved to a database, the srAudit defines the connection to use. This connection must be defined in the root task element.

Which connection will be used?

SQL tasks can set the attribute dataSourceName. A connection of that name will be searched for in the current task and ancestor tasks. N.B. finding none or more than one will cause an exception.

If dataSourceName is not set, connections defined in the current task are looked at. If there is a single connection that is not the ScriptRunner audit trail connection, dataSourceName="srAudit", that connection is used. Otherwise the ancestor tasks are searched for a single connection that is not srAudit. If both these searches fail, a connection with the special dataSourceName of Default is searched for in the current task and ancestor tasks.

Consider the following sample:

<task type="Serial" name="Sample.Container">
  <connections>
    <connection dataSourceClass="org.postgresql.ds.PGPoolingDataSource" 
                dataSourceName="mt_app" 
                serverName="localhost" databaseName="postgres" 
                user="mt_user" password="empty" />
    <connection dataSourceClass="oracle.jdbc.pool.OracleDataSource" 
                dataSourceName="srAudit" 
                serverName="localhost" databaseName="ORCL" 
                user="srAudit" password="srAudit" portNumber="1521">
      <properties>
        <property name="driverType">thin</property>
      </properties>
    </connection>
  </connections>
  <task type="SQL" name="Named sample" dataSourceName="mt_app">
    BEGIN ...
  </task>
  <task type="SQL" name="Ancestor sample">
    BEGIN ...
  </task>
  <task type="SQL" name="Child sample">
    <connections>
      <connection dataSourceClass="org.postgresql.ds.PGPoolingDataSource" 
                  serverName="localhost" databaseName="postgres" 
                  user="another_user" password="empty" />
    </connections>
    BEGIN ...
  </task>
</task>
The first SQL task explicitly names the conenction it wants to use; the second picks up the single non-srAudit ancestor; and the third defines a single child connection.

How to access a connection in Java?

See ConnectionFactory method getConnection.
For SQLTask, the arguments used are the value of the dataSourceName attribute and the defining XML element. The returned Connection will have been rolled-back, had auto-commit set to false and transaction isolation set to TRANSACTION_SERIALIZABLE (i.e. dirty reads, non-repeatable reads and phantom reads are prevented) and warnings cleared.