Newer version with UI configuraiton

Looking for the documentation on the newest versions of SIL Engine and the Simple Issue Language for Jira 8 for Server/Data Center? Click here !

Adding a Datasource

To create a data source from the user interface, access the Datasources menu in cPrime Add-ons section, then click Add Datasource.

Fill out the necessary fields:

Name of the datasource, identifying uniquely ^{( * )} your data source
The database type
The JDBC URL, with the syntax the driver understands
Username and password for the database, if applicable

Optionally, you may control the pool parameters:

Validation query - The SQL query that will be used to validate connections from the pool.
Initial size - Number of initial connections.
Max Active - Maximum number of active connections that can be allocated at the same time.
Max Idle - The maximum number of connections that can remain idle in the pool.
Min Idle - The minimal number of connections that can remain idle in the pool.

These are DBCP parameters that are used when defining any JNDI datasource. Once you have configured your datasource, you are ready to test it. Any error is picked up and shown in this dialog so you can review the parameters, make the changes and test again. When it works, just save it, your new datasource is ready to be used.

Important (*)

The identification of the datasource is unique only among these ones, defined in the Kepler suite. As we explained, the name here takes precedence over any JNDI name that happens to be the same.

Very Important!

JDBC Driver must be already loaded (for instance placed in Jira's lib folder).

At the moment, there's no possibility to monitor the performance of the SQL pool, so you will have to rely on external tools, specific to your database / operating system.

Managing Datasources

The datasources screen lists all your datasources and allows to edit, test and remove operations to be performed on each datasource. Note that there's no warning when removing a datasource that is in use by your SIL™ programs, so take care when performing destructive operations. Changes in the datasources are picked up immediately.

Older Versions of Power Apps

By default, JIRA runs in Tomcat, so the following example applies to Tomcat only. To learn how to define data source, refer to the application server manual.

Ensure that you have the SQL driver in JIRA_HOME/lib directory. The driver should be proper for the type of the (external to JIRA) database in use.

Open the JIRA_HOME/conf/context.xml file in a text editor. Enter your data source there, for instance, between the existing tags <Context> </Context>:
Hsqldb

<Resource name="TestDB"
          auth="Container" type="javax.sql.DataSource"
          username="sa"
          password=""
          driverClassName="org.hsqldb.jdbcDriver"
          url="jdbc:hsqldb:/tmp/somedb;create=true;"
<!-- Optional, but highly recommended, add here the limits for your SQL database connection pool -->
maxTotal="20" maxIdle="5" maxWaitMillis="-1"
/>

Restart your Jira. You should be now ready to use the data source you just defined.

Check the settings in the Resource tag:

name: Appears in the general parameters as Database(JNDI name)
username: Username for connecting to the database
password: Password for connecting to the database
driverClassName: Name of the class from the driver. The driver should be a proper jar archive for the type of the database used.
In our example the type of the database used is hsqldb
url: The url used to connect to the database

Settings for other databases

PostgreSQL

<Resource name="TestDB"
          auth="Container" type="javax.sql.DataSource"
          username="sa"
          password=""
          driverClassName="org.postgresql.Driver"
          url="jdbc:postgresql://127.0.0.1:5432/somedb"
 />

MySQL

<Resource name="TestDB"
          auth="Container" type="javax.sql.DataSource"
          username="sa"
          password=""
          driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://127.0.0.1:3306/somedb"
 />

If you want to use a column alias in a select sql and use that alias instead of a column name you should add "?useOldAliasMetadataBehavior=true" as a configuration parameter in your JDBC url. This is due to a change in My SQL JDBC driver implementation starting with version 5.1.

You can find more details here.

If you use this parameter in combination with other parameters please also see this.

Oracle

<Resource name="TestDB"
          auth="Container" type="javax.sql.DataSource"
          username="sa"
          password=""
          driverClassName="oracle.jdbc.driver.OracleDriver"
          url="jdbc:oracle:thin:@127.0.0.1:1522:somedb" 
	  connectionProperties="SetBigStringTryClob=true"
 />

MS SQL Server

<Resource name="TestDB"
 	  auth="Container" type="javax.sql.DataSource"
 	  username="sa"
 	  password=""
 	  driverClassName="net.sourceforge.jtds.jdbc.Driver"
 	  url="jdbc:jtds:sqlserver://localhost:1433/somedb;instance=INSTANCE_NAME" 
 />

For MS Sql Server 2000 the instance name should be omitted from the url even if you are connecting to a named instance. Thus, the url attribute should be: url="jdbc:jtds:sqlserver://localhost:1433/somedb".

AS400

<Resource name="TestDB"
          auth="Container" type="javax.sql.DataSource"
          username="sa"
          password=""
          driverClassName="com.ibm.as400.access.AS400JDBCDriver"
          url="jdbc:as400://127.0.0.1/somedb;naming=sql;errors=full"
 />

Firebird

<Resource name="TestDB" auth="Container" type="javax.sql.DataSource" 
	  username="sysdba" 
	  password="masterkey" 
	  driverClassName="org.firebirdsql.jdbc.FBDriver" 
	  url="jdbc:firebirdsql:127.0.0.1/3050:d:/somedb.fdb" 
/>

If want to connect to Jira database, you can see its configuration in the dbconfig.xml file from Jira's home directory.

What's next

After you configure the data sources, see User Guide for details on how to use database custom fields.

Power Database Fields PRO™

Data Source Configuration