Datasources
Datasources
A datasource is a database against which you want to run reports. Use the Configure | Datasources menu to manage datasources.
- Use the Add button to create a new datasource
- Use the Edit and Delete buttons at the top to edit or delete multiple datasources and the buttons on the side to edit or delete individual datasources
- Use the Search field to search for a particular datasource. You can also search by individual columns using the appropriate search fields below the column headings.
- You can sort the datasources list by clicking on the column headings
Creating a new datasource
From the Datasources page, use the Add button and then specify the settings for the datasource. ART can run reports against any datasource for which a JDBC driver is available.
| Field | Description |
|---|---|
| ID | An auto-generated ID used to identify the datasource |
| Name | A name to identify the datasource |
| Description | A description for the datasource |
| Active | Whether the datasource is available for use |
| Database Type | The database software that the database runs on |
| JNDI | Whether the datasource is a JNDI datasource |
| Database Protocol | An indication of the kind of syntax the database uses. This is especially important to set if using a JNDI datasource. |
| JDBC Driver | The JDBC driver name |
| URL | The JDBC URL of the target database. If you are using a JNDI datasource, set this to the JNDI name of your datasource e.g. jdbc/MyDatasource, or the full JNDI url e.g. java:comp/env/jdbc/MyDatasource |
| Username | The database user on the target database. It is recommended that this user should have the least rights on the database and tables you plan to use in your reports, mostly only SELECT rights on the relevant tables. |
| Password | The password for the database user |
| Test SQL | A short SQL query that can be used to determine if a connection is OK e.g. select 1 |
| Connection Pool Timeout (Mins) | How long in minutes an idle connection should be maintained in the connection pool before being closed |
| Options | Options for the datasource. The options are specified in JSON format. |
Note:
- Connections do not always close gracefully - for example if a network outage occurs a broken connection might stay in the pool and would throw an error when used. The Test SQL query is used every Timeout minutes to validate the connection. If it does not run successfully, the connection is closed and removed from the pool. If using a driver that supports the JDBC 4.0 specification, you can leave the Test SQL field blank for a more efficient connection test.
Options
A number of options can be specified in the Options field. These are defined in JSON format with the following possibilities.
| Property | Data Type | Description |
|---|---|---|
| limitClause | String | The syntax of a "LIMIT" clause in an SQL statement e.g. "limit {0}". This would be used when the datasource is used in a view report. The "{0}" would be replaced with the limit value at run time. This option doesn't need to be specified if the Database Protocol field is set. |
| limit | Integer | The default limit to use when the datasource is used in a view report |
| hikariCp | Object | Allows for specifying some HikariCP configuration options e.g. { "hikariCp" : { "readOnly" : true} }. If you would like to specify options to be used for all datasources, you can specify these options in the WEB-INF\classes\hikaricp.properties file. |
| queryTimeoutSeconds | Integer | For JDBC datasources, enables setting of a query timeout for reports that use this datasource. If the timeout expires before the query completes, the query execution will stop and an exception will be thrown. The value if defined should be >= 0, with 0 meaning no timeout. |
Some JDBC Drivers and URLs
Database: Oracle
Driver Name: oracle.jdbc.OracleDriver
JDBC URL: jdbc:oracle:thin:@<server>:<port>:<sid> (default port is 1521)
Driver Available from: http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html
Database: MySQL
Driver Name: com.mysql.cj.jdbc.Driver
JDBC URL: jdbc:mysql://<server>[:port]/<database>[?<property>=<value>[&...] (default port is 3306)
Driver Available from: http://dev.mysql.com/downloads/connector/j/
Database: MariaDB
Driver Name: org.mariadb.jdbc.Driver
JDBC URL: jdbc:mariadb://<server>[:port]/<database>[?<property>=<value>[&...] (default port is 3306)
Driver Available from: https://mariadb.com/kb/en/mariadb/about-mariadb-connector-j/
Database: PostgreSQL
Driver Name: org.postgresql.Driver
JDBC URL: jdbc:postgresql://<server>[:port]/<database>[?<property>=<value>[&...] (default port is 5432)
Driver Available from: http://jdbc.postgresql.org/
Database: SQL Server (Microsoft driver)
Driver Name: com.microsoft.sqlserver.jdbc.SQLServerDriver
JDBC URL: jdbc:sqlserver://<server>[:port];databaseName=<database>[;instanceName=<instance>][;<property>=<value>[;...] (default port is 1433)
Driver Available from: http://msdn.microsoft.com/en-us/data/aa937724.aspx
Database: HSQLDB (Standalone mode)
Driver Name: org.hsqldb.jdbc.JDBCDriver (HSQLDB 2.x) or org.hsqldb.jdbcDriver (HSQLDB 1.x)
JDBC URL: jdbc:hsqldb:file:<file_path>[;shutdown=true;hsqldb.write_delay=false;create=false][;<property>=<value>[;...]
Driver Available from: http://hsqldb.org/
Database: HSQLDB (Server mode)
Driver Name: org.hsqldb.jdbc.JDBCDriver (HSQLDB 2.x) or org.hsqldb.jdbcDriver (HSQLDB 1.x)
JDBC URL: jdbc:hsqldb:hsql://<server>[:port]/<database_alias>[;<property>=<value>[;...] (default port is 9001)
Driver Available from: http://hsqldb.org/
Database: Db2 for iSeries (Toolbox or JTOpen driver)
Driver Name: com.ibm.as400.access.AS400JDBCDriver
JDBC URL: jdbc:as400://<server>;prompt=false;translate binary=true[;<property>=<value>[;...]
Driver Available from: http://jt400.sourceforge.net/
