Working With Data Sources

When adding a data source to JasperReports Server, several things can cause errors. Start by looking at the following general connectivity issues:

Check that your database server is available and accepting TCP/IP connections from the host where JasperReports Server is installed.
Check in your RDBMS that the username and password you're using are correct and have access to the selected database.
Check for firewalls or network connectivity errors.

Many databases, including MySQL, also require the user grants to include the specific host from which connections are allowed. Otherwise, when testing the JDBC connection, a connection may not be allowed even though the username and password are correct. For more information, refer to the MySQL documentation for setting up users.

An easy way to test connectivity from the server to the database with a particular user is to use a tool such as SQuirreL or another DB query tool to connect to the database from the host of your JasperReports Server instance.

Logging JDBC Operations

You can enable additional logging to help you find the cause of the error. Set any or all of the following loggers in the server settings interface or in the .../WEB-INF/log4j.properties file:

log4j.logger.com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.JdbcDataSourceService
log4j.logger.com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.
JndiJdbcDataSourceService
log4j.logger.com.jaspersoft.jasperserver.war.action.ReportDataSourceAction
log4j.logger.com.jaspersoft.commons.datarator.JdbcDataSet
log4j.logger.com.jaspersoft.jasperserver.war.common.JasperServerUtil
log4j.logger.com.jaspersoft.commons.semantic.dsimpl.JdbcDataSetFactory
log4j.logger.com.jaspersoft.commons.semantic.metaapi.impl.jdbc.BaseJdbcMetaDataFactoryImpl
log4j.logger.com.jaspersoft.jasperserver.war.validation.ReportDataSourceValidator

JDBC Drivers

JasperReports Server ships with drivers for several databases, as listed in the dialog for creating data sources. If the JDBC driver for your database is not included, the system administrator can easily upload the driver and use it immediately in a data source.

Database Permissions

When creating database users, you must ensure that they have the appropriate privileges to access data, as well as permission to connect from the server that JasperReports Server is running on.

The database user that you specify in your data source definition needs the privileges to run SELECT queries on the tables used in your reports. The server blocks any DROP, INSERT, UPDATE, and DELETE SQL commands through its SQL injection protection. In some cases, additional permissions may be required to execute stored procedures, depending on your configuration and needs.
If you accept the defaults during installation of JasperReports Server on Linux from an RPM using apt-get, rpm, or yum, the bundled PostgreSQL allows only the user who owns PostgreSQL to connect. Enter the following commands to connect:
su - postgres
psql -U postgres
Many databases, including MySQL, also require that the user permissions name the specific host from which connections are allowed. Otherwise, when testing the JDBC connection, a connection may not be allowed even though the user name and password are correct. For example, see the MySQL documentation for setting up users.

A fairly easy way to test permissions and connectivity is to use a tool such as SquirrelSQL or another DB query tool to connect to the database from the same host as JasperReports Server and to run typical queries against your database.

Unique JDBC Data Source Fields

When you choose a JDBC driver, the data source creation wizard displays the fields that are required for your database. In some cases, you may need to enter information that is unique to this type of JDBC data source. The following tables explains the field for the most common databases.

Autonomous REST

Field

Description

Authentication Method

The authentication method for accessing the data source. Changing the authentication method will require you to configure the URL. The authentication method can be one of the following:

none - Does not use an authentication method for accessing the data source.
basic - Uses a user ID and password for authentication Requires user and password properties in the URL.
HttpHeader - Uses a security token for authentication. Requires user and SecurityToken properties in the URL.
UrlParameter - Uses a security token and URL parameter for authentication. The URL requires user, SecurityToken, and AuthParam properties.
OAuth2 - Uses OAuth 2.0 for authentication. There are multiple ways to use OAuth 2.0. TIBCO Jaspersoft recommends using the official driver documentation for more information.
Custom - Uses the custom token-based authentication defined in the config file. TIBCO Jaspersoft recommends using the official driver documentation for more information.

By default, the authentication method is set to none.

Path to Config File The full path to the configuration file for the data source.

Cassandra

Field

Description

Key Space Name of the key space that acts as the data store for the Cassandra data source.

Google BigQuery

Field

Description

Project ID The unique identifier for your Google Cloud project.
Dataset ID The unque BigQuery dataset name.
Google Service Account Email Address The email address associated with the BigQuery service account.
Private Key Path

The full path to the .json key file used to authenticate the service account email address.

The .p12 files are not supported.

Impala

Field

Description

Schema Name Name of the Impala Schema object.

Oracle

Field

Description

Service Name of the Oracle database service.

Salesforce

Field

Description

Security Token A case-sensitive alphanumeric key used in combination with a password to access the Salesforce data source.
Maximum Number of Web Service Calls Maximum number of times JasperReports Server will call the Salesforce web service in a 24-hour period. Set to 0 for unlimited calls.

JDBC Database URLs

When you choose a JDBC driver, the data source creation wizard prompts you for the elements of the URL that are required for your database. In some cases, you may need to add certain arguments to the JDBC URL. Ensure that the database URL you entered when defining your JDBC data source is consistent with what is required for your specific database and database driver. The following table gives the default URLs and port numbers and examples of optional arguments supported by the most common databases:

Database

Default JDBC Database URL

PostgreSQL

jdbc:postgresql://<host>:5432/<db-name>

MySQL and MariaDB

jdbc:mysql://<host>:3306/<db-name>?tinyInt1isBit=false

Oracle

jdbc:tibcosoftware:oracle://<host>:1521;SID=ORCL

SQL Server

jdbc:tibcosoftware:sqlserver://<host>:1433;databaseName=<db-name>

IBM DB2

jdbc:tibcosoftware:db2://<host>:50000;databaseName=<db-name>

Cassandra jdbc:cassandra://<host>:9042;DefaultKeyspace=
Hive jdbc:tibcosoftware:hive://<host>:10001;TransactionMode=ignore
Impala jdbc:impala://<host>:21050/default
Redshift jdbc:tibcosoftware:redshift://<host>:5439;databaseName=<db-name>;TransactionMode=ignore
SparkSQL jdbc:tibcosoftware:sparksql://<host>:10000;TransactionMode=ignore
Salesforce jdbc:tibcosoftware:sforce://;SecurityToken=;TransactionMode=ignore;StmtCallLimit=1000;databaseName=[TMPDIR/]Salesforce

SQL Functions with TIBCO JDBC Drivers

TIBCO provides several JDBC drivers. The TIBCO JDBC drivers are based on the Progress DataDirect Connect drivers and support a slightly different SQL syntax than the vendors' drivers. If you see errors like the one below when running reports or creating domains that use scalar functions, you'll need to modify your queries.

com.jaspersoft.commons.semantic.metaapi.MetaDataException: Cannot execute JDBC Query.
java.sql.SQLDataException: [TIBCO][SQLServer JDBC Driver][SQLServer]Conversion failed
when converting the varchar value 'Assets' to data type int.

For example, the following query does not work with the TIBCO JDBC drivers:

SELECT account_id+account_description AS account_concat from account

The correct syntax for the TIBCO JDBC drivers is as follows:

SELECT {fn CONCAT(account_id,account_description)} AS account_concat from account

For more information, see the Progress DataDirect page on scalar functions.

Enabling the TIBCO JDBC Drivers for Impala and Cassandra Data Sources

By default, a single JDBC driver is enabled for each data source type. JasperReports Server uses the Simba JDBC drivers for Impala and Cassandra data sources by default. If you want to use the TIBCO JDBC drivers for Impala (tibcosoftware.jdbc.impala.ImpalaDriver) or Cassandra (tibcosoftware.jdbc.cassandra.CassandraDriver) data sources, you can modify the .../WEB-INF/applicationContext-webapp.xml file to make the drivers active.

To enable the TIBCO JDBC drivers for Impala and Cassandra data sources:

1. Open the file .../WEB-INF/applicationContext-webapp.xml for editing.
2. Locate the jdbcTibcoConnectionMap bean and find the following lines for the inactive Impala and Cassandra JDBC drivers:
<!--  entry key="impala">
   ...
</entry -->
...
<!--  entry key="impala">
   ...
</entry -->
3. Modify the lines as follows to enable the JDBC drivers in JasperReports Server:
<entry key="impala">
   ...
</entry>
...
<entry key="impala">
   ...
</entry>
4. Save the file and restart JasperReports Server.

Enabling the JDBC Driver for ElasticSearch Data Sources

The ElasticSearch JDBC driver is not enabled by default due to certain limitations. If you want to use the driver, you can modify the .../WEB-INF/applicationContext-webapp.xml file to make it active.

Be aware of the following issues when using an ElasticSearch data source:

It is not supported for JBoss or Wildfly app servers.
Table joins are not supported.
There may be issues with calculated fields, including more complex aggregation functions, using* as a special characters, and the use of calculated fields in crosstabs and charts.
When an ElasticSearch data source is used in a virtual data source, the virtual data source only displays the Base tables of the ElasticSearch data source, not the views, when used in a domain.

To enable the TIBCO JDBC drivers for ElasticSearch data sources:

1. Open the file .../WEB-INF/applicationContext-webapp.xml for editing.
2. Locate the jdbcTibcoConnectionMap bean and find the following lines for the inactive ElasticSearch JDBC driver:
<!--  entry key="elastic_search">
   ...
</entry -->
3. Modify the lines as follows to enable the JDBC driver in JasperReports Server:
<entry key="elastic_search">
   ...
</entry>
4. Save the file and restart JasperReports Server.

JNDI Services on Apache Tomcat

If you have trouble with a JNDI connection, you need to look at the JNDI definition for your database on your application server. This section gives common issues with JNDI definitions on Apache Tomcat connecting to MySQL. If you use a different application server or database server, refer to its documentation.

A JNDI connection on Tomcat is defined in two different files. Make sure both have the following information:

<tomcat>/webapps/jasperserver[-pro]/META-INF/context.xml
<Resource name="jdbc/<db-name>" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="<db-user>" password="<db-user-password>"
driverClassName="org.postgresql.Driver"
validationQuery="SELECT 1" testOnBorrow="true"
url="jdbc:mysql://<host>:3306/<database>?autoReconnect=true&amp;autoReconnect
ForPools=true"/>
<tomcat>/webapps/jasperserver-pro/WEB-INF/web.xml
<resource-ref>
<description>JNDI Example</description>
<res-ref-name>jdbc/<db-name></res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

Also check the following points:

Ensure the driver for your database connection is in the <tomcat>/lib folder.
Ensure the database user has the privileges to run SELECT queries on the tables used in your reports. In some cases, additional permissions may be required to execute stored procedures, depending on your configuration and needs. For more information, see Database Permissions.
If you installed JasperReports Server from a WAR file, Tomcat may have created a separate copy of context.xml in <tomcat>/conf/Catalina/Localhost/jasperserver-pro.xml. See the corresponding section in the troubleshooting appendix of the TIBCO JasperReports Server Installation Guide.
See the Apache Tomcat documentation for JNDI data sources.
For Oracle databases, you may need to specify additional parameters in the context.xml file. For example, in order to support in Oracle, add the following line:
driverClassName="oracle.jdbc.OracleDriver"
validationQuery="SELECT 1 FROM dual"
accessToUnderlyingConnectionAllowed="true"

JNDI Services on JBoss

Follow these steps to configure JasperReports Server to use JNDI data sources with JBoss:

1. Add a new JNDI service user to <jboss>/standalone/deployments/jasperserver-pro.war/WEB-INF/js-jboss7-ds.xml. For example:
<datasource jta="false" jndi-name="java:/jdbc/sample_db" pool-name="sample_db" enabled="true" use-ccm="false">
        <connection-url>jdbc:postgresql://localhost:5432/sample_db</connection-url>
        <driver>postgresql-9.4-1210.jdbc41.jar</driver>
        <security>
            <user-name>postgres</user-name>
            <password>postgres</password>
        </security>
        <pool>
            <min-pool-size>5</min-pool-size>
            <max-pool-size>50</max-pool-size>
            <prefill>true</prefill>
	</pool>
	<validation>
            <validate-on-match>false</validate-on-match>
            <background-validation>false</background-validation>
            <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
        </validation>
        <statement>
            <share-prepared-statements>false</share-prepared-statements>
        </statement>
    </datasource>

JNDI Services on WebLogic

Follow these steps to configure JasperReports Server to use JNDI data sources with WebLogic:

1. Append the following definition to the <reference-descriptor> node of .../WEB-INF/weblogic.xml:
<resource-description>
    <res-ref-name>TestDatabase</res-ref-name>
    <jndi-name>jdbc/testDatabase</jndi-name>
</resource-description>
2. Append the following definition to .../WEB-INF/web.xml:
<resource-ref>
    <description>TestDatabase database</description>
    <res-ref-name>TestDatabase</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>
3. In the WebLogic Admin Console, create a JNDI data source, in this example its JNDI name would be TestDatabase.

Ensure the database user in your JNDI definition has the privileges to run SELECT queries on the tables used in your reports. In some cases, additional permissions may be required to execute stored procedures, depending on your configuration and needs. For more information, see Database Permissions.

4. Restart the jasperserver-pro instance using the WebLogic Admin Console.

Creating a Data Source on SQL Server Using Windows Authentication

If your database is Microsoft SQL Server and you use Windows Authentication (also called Integrated Security), use the following procedure to create a data source.

1. Download the latest JDBC driver for your version of Microsoft SQL Server. For example download Microsoft SQL Server JDBC Driver 6.4.

To find the latest version of your Microsoft SQL Server driver, see the Microsoft JDBC Driver for SQL Server Support Matrix.

For information about downloading and installing Microsoft SQL Server drivers, see the Microsoft JDBC Driver for SQL Server page.

2. Download and run the self-extracting executable: sqljdbc_6.4.0.0_enu.exe
3. Open the extracted folder sqljdbc_6.4\enu\auth\x64 and copy the file sqljdbc_auth.dll to the folder your app server automatically searches for DLLs.

For Tomcat, this is the <tomcat>\bin folder.

4. Open the extracted folder sqljdbc_6.4\enu and copy mssql-jdbc-6.4.0.jre8.jar to folder your app server automatically searches for jars.

For Tomcat, this is the <tomcat>\lib folder.

5. Restart your app server.
6. Log into JasperReports Server as an administrator.
7. Select Create > Data Source from the main menu.
8. In the Type field, select JDBC Data Source. The page refreshes to show the fields necessary for a JDBC data source.
9. Enter a name and optional description for your data source.
10. From the dropdown field, select com.microsoft.sqlserver.jdbc.SQLServerDriver.
11. Enter the database hostname and database name of your SQL Server instance.
12. In the URL field, add the following string to the end of the generated URL:

;integratedSecurity=true

13. In the User Name field, enter any non-blank string you want, for example none.
14. In the Password field, enter any non-blank string you want, for example none.
15. Set the Time Zone and Save Location fields if necessary.
16. Click Test Connection and verify that the connection works.
17. Click Save to save the data source in the repository.

Upgrading Bean Data Sources

There was a change in the Spring configuration for JasperReports Server 6.0 which changes how some of the existing Spring beans are made accessible for use by other beans. This can break existing custom data sources. This change specifically affects beans which implement the interface ReportDataSourceServiceFactory.

Prior to release 6.0, if code in JasperReports Server accessed a bean of this type, it would get the actual instance of the Spring bean as configured in the Spring XML file, and it could be cast to the concrete class. In 6.0 and later, these beans were intercepted in order to implement the profile attributes feature, and instead of seeing the actual instance, other code would see a dynamic proxy instead of the actual bean.

Dynamic proxies are a Java feature which allows classes to be generated at run time that implement any interface that can be loaded on the classpath. The resulting object can be cast to any of those interfaces, but doesn't correspond to any concrete class. For more information:

http://www.javaworld.com/article/2076233/java-se/explore-the-dynamic-proxy-api.html

Since proxies can only represent interfaces, existing code that tries to cast the bean to a concrete class will break. Casting is usually done to get access to methods on a more specific class or interface. As long as the code is not casting the bean to a concrete class, it will work, so there are two ways to get around this problem:

If the code needs to access methods on an existing interface, just do a cast to that interface, or inject the property using the existing interface, so no cast is needed.
If the code needs to access methods that are not on an existing interface, simply create an interface with the methods needed, and have the target object implement that interface.

For example, let's say you have a bean with id myBean that needs to access the jdbcDataSourceServiceFactory, configured as follows:

<bean id="jdbcDataSourceServiceFactory" class="com.jaspersoft.jasperserver.
api.engine.jasperreports.service.impl.JdbcReportDataSourceServiceFactory">
...
</bean>

Where myBean has the following Spring configuration:

<bean id="myBean" class="example.MyBean">
    <property name="jdbcDSSF" ref="jdbcDataSourceServiceFactory"/>

The following code worked before but will now cause an error:

public class MyBean {
  private ReportDataSourceServiceFactory jdbcDSSF;
 
  public ReportDataSourceServiceFactory getJdbcDSSF() {
    return jdbcDSSF;
  }
 
  // before 6.0, was called by Spring with the actual bean
  // 6.0 and after, is called with a dynamic proxy
  public void setJdbcDSSF(ReportDataSourceServiceFactory jdbcDSSF) {
    this.jdbcDSSF = jdbcDSSF;
  }
 
  public void doSomething() {
    // this code used to work, but now it will break
    ((JdbcReportDataSourceServiceFactory) jdbcDSSF).createService();
    ((JdbcReportDataSourceServiceFactory) jdbcDSSF).doSomethingElse();
  }
}

Because the first call is a method which is part of the ReportDataSourceServiceFactory, the cast is unnecessary; to fix it, just leave it out:

    jdbcDSSF.createService();

In this example, JdbcReportDataSourceServiceFactory has a method called doSomethingElse(). This method is not part of any interface, but you can create an interface that includes it:

public interface MyDSSF extends ReportDataSourceServiceFactory {
   public void doSomethingElse();
}

JdbcReportDataSourceServiceFactory would need modification so that it implements this new interface:

public JdbcReportDataSourceServiceFactory implements MyDSSF {
....
}

You don't have to change the declaration in MyBean because Spring will generate a dynamic proxy implementing MyDSSF, but if you change the declaration, the code will be easier to understand because no casts will be necessary:

public class MyBean {
  private MyDSSF jdbcDSSF;
 
  public MyDSSF getJdbcDSSF() {
    return jdbcDSSF;
  }
 
  // called with a dynamic proxy which implements all needed interfaces
  public void setJdbcDSSF(MyDSSF jdbcDSSF) {
    this.jdbcDSSF = jdbcDSSF;
  }
 
  public void doSomething() {
    // no need to cast
    jdbcDSSF.createService();
    jdbcDSSF.doSomethingElse();
  }
}
Version: 
Feedback