Resources

The Resources feature allows you to add connections to databases for use in scripts, and other places. For example:

• Workflow Validator: Use to check that a particular item exists in your contracts database.

• Post-Function: Use to update a sales database with a link to the current ticket.

ScriptRunner manages a connection pool, allowing database connections to be reused when future requests are required. A connection pool eliminates the need to close a connection after each use or specify connection details, such as passwords, in scripts. Instead of entering specific connection information, you can refer to the pool name entered when configuring the connection.

The Resources page lists all previously configured database connections.

Browse Resources

After selecting Create Resources, you can use the Search ScriptRunner Functionality search bar to search the available resources.

For example, if you’re looking for a resource that works with local databases, you could type "Local" and press Enter. Then, the list of resources is narrowed down to only those containing the word "local" in their title or description.

External Database Connection

There are two types of database connection:

• External: Used to connect to any database, such as your sales or contacts database.

• Local: Connects to the current database your Atlassian application is using.

The Resources page lists all previously configured database connections.

To set up a connection to an external database, you need to know the following information for the database:

• The JDBC URL

• Any required credential information (username and password)

• The driver class

To set up an external database connection and make the database available to scripts:

1. Navigate to ScriptRunner > Resources > Add New Item > Database Connection.

2. Provide a name for the connection in Pool Name.

3. Enter the JDBC URL of the database to which you wish to connect.
   For more information on sample JDBC URLs, see JDBC Driver Connection URL Strings.

4. Provide the Driver Class Name.
   Click Show examples to see a list of common driver classes.
   

5. Enter the username required to authenticate the database in User and the corresponding Password.

   The User and Password fields are not required if the information is provided in the URL.

6. Optionally, enter a query into the SQL field to test receiving information from the database. Use Preview to test out different queries.

   This SQL query is not saved and is only used to test the connection.

7. Again, optionally, you can set advanced connection pool properties, such as the maximum pool size. This may be useful in several cases:

   1. If you are using Data Center, by default, the database resource will take 10 connections per node in your cluster.

      If this totals too many connections, you can limit the maximum size by entering, for example: maximumPoolSize=3.

   2. If you would like to limit a database session to ten minutes, enter maxLifetime=600000 (ten minutes in milliseconds).

   3. If you see connections are growing, it could be that a prepared statement was not closed. Enter leakDetectionThreshold=2000 to report any connection that was borrowed from the pool for more than two seconds.

      Look for an exception in your logs beginning: java.lang.Exception: Apparent connection leak detected.

      If your database query is expected to take more than two seconds, then this is probably not a leak, and you should raise this value.

8. If the preview is successful, click Add.

Other Drivers

You might want to use these to create a custom field that allows users to pick from a row in a spreadsheet.

In the example shown below, we are using a CSV driver. This makes available all CSV in the directory provided in the JDBC URL. So in /tmp, we have a CSV file called devs.csv.

Local Database Connection

To set up a local database connection and make the local database (the one that your Atlassian application is using) available to scripts, follow these steps:

1. Navigate to ScriptRunner > Resources > Add New Item > Local Database Connection.

2. Provide a name for the connection in Pool Name.

   For local connections, we recommend the name local.

3. Optionally, enter a query into the SQL field to test receiving information from the database. Use Preview to test out different queries.

   This SQL query is not saved and is only used to test the connection.

   + SQL can be used to run queries harder to achieve using the API. For example, aggregate queries:

4. If the preview is successful, click Add.

   The local connection is always read-only (except in the case of H2, where this driver does not support it).

Use Database Resources in Scripts

Having set up a local connection, you can use it in a script as follows:

groovy
import com.onresolve.scriptrunner.db.DatabaseUtil 

DatabaseUtil.withSql('local') { sql -> 
	sql.rows('select * from project') 
}

DatabaseUtil.withSql takes two arguments:

1. The name of the connection as defined by you in the Pool Name parameter when adding the connection (in this example local),

2. A closure. The closure receives an initialized groovy.lang.Sql object as an argument. See executing SQL for more information on executing queries. The benefit of using a closure is that it is returned the connection to the pool after execution.

   The withSql method returns whatever the closure returns, so as another example, you could get the number of projects using:

groovy
import com.onresolve.scriptrunner.db.DatabaseUtil

def nProjects = DatabaseUtil.withSql('local') { sql ->
    sql.firstRow('select count(*) from project')[0]
}

LDAP Connection

Adding an LDAP resource allows you to query your LDAP servers in a similar way to database connections.

Use an LDAP resource to:

• Validate that a username provided in a custom field is a member of an LDAP group.

• Write a REST endpoint to list office addresses.

• In a Leavers workflow, use a post function to mark a user as having left the company.

To set up an LDAP connection, and make the connection available to scripts:

1. Navigate to ScriptRunner > Resources > Add New Item > LDAP Connection.

2. Provide a name for the connection in Pool Name.

3. Enter the Host.

4. Optionally, check Use TLS to use TLS/SSL encryption.

5. Enter the Port the LDAP connection is using.

6. Enter the base dn into the Base field.

7. Add the User dn.

8. Enter the LDAP Password.

   Contact your directory services administrator for LDAP details. If you have set up an LDAP server as an application User Directory, and it’s the same LDAP server, you can copy and paste the values.

9. Click Add.

   Clicking Preview validates that a successful connection and query can be made to the LDAP server.

Use LDAP Resources in Scripts

Having set up an LDAP connection, you can use it in a script as follows:

This example uses the LDAP connection with the Pool Name corporate.

groovy
import com.onresolve.scriptrunner.ldap.LdapUtil
import org.springframework.ldap.core.AttributesMapper

import javax.naming.directory.SearchControls

def cnList = LdapUtil.withTemplate('corporate') { template ->
    template.search("", "(sn=Smi*)", SearchControls.SUBTREE_SCOPE, { attributes ->
        attributes.get('cn').get()
    } as AttributesMapper<String>)
}

// cnList now contains the list of common names of users whose surnames begin with "Smi"...

LdapUtil.withTemplate takes two arguments:

1. The name of the connection as defined by you in the Pool Name parameter when adding the connection (in this example corporate),

2. A closure. The closure receives a org.springframework.ldap.core.LdapOperations object as an argument.

See spring ldap for more information on querying. Where the documentation refers to an LdapTemplate, this is equivalent to the above-mentioned LdapOperations.