Connecting JasperReports Server to your Database

Update for JasperReports Server :

The content of this article is still relevant for JasperReports Server v8.x. This new version of JasperReports Server includes a way to load Database Drivers dynamically from the UI. This option is only available for the 'superuser' and is described in detail in the JasperReports Server Administration Guide v5.1.0 - Chapter 4.1.

Introduction

JasperReports Server can connect to just about any data source. It comes standard with connectivity for JDBC, JNDI, MongoDB, Hadoop-Hive and Bean (Java bean) data sources. Jaspersoft also offers an extensible environment for including other Big Data, NoSQL data sources, flat files and custom data sources.

In this guide, we will focus on creating JDBC connections to an existing RDBMS server for reporting.

For the other options and more information check the JasperReport Server User and Admin Guides located in the /Docs folder under your JasperReports Server install path.

JDBC Database Driver

Acquire the Database Driver Package

JasperReports Server redistributes a Postgres database driver. If you need to connect to another database, you will need to first acquire the appropriate JDBC driver and add it the application server's classpath.

You can download the JDBC driver from your database vendor website. Below you will find some links that may assist you in finding the JDBC driver for your reporting database. When selecting the correct driver, check that you are downloading the one that matches your version of the Database Engine and the Java (JRE or JDK) version installed. Some DBMS's have different downloads depending on the version of Java you are using. 

To find the Version of Java that Jasper is running on login as superuser in JasperReport Server execute the Diagnostic Report (/Public/Diagnostic/Diagnostic Report) and search for “java.version” , see screenshot below:

You can also check which version you have by running ‘java –version’ in a command prompt of the server.

Extract the downloaded files to an easily accessed location. For example, c:jdbc or /usr/share on the server and locate in the downloaded package the actual driver which will be a single .jar file.  You will need it on the next step.

For example if you are using MS SQL Server 2008 you will need to use “sqljdbc4.jar” for JDK 1.6/6.0 and “sqljdbc.jar” if you have JDK 1.5/5.0.

Sample driver names:

• MySQL: mysql-connector-java-5.1.19-bin.jar

  Note: As of JasperReports Server v5.5, the MariaDB opensource MySQL DB Driver, is included in the installation.

  For more info, see: MariaDB Foundation

• jTDS: jtds-1.2.2.jar
• Microsoft SQL Server: sqljdbc4.jar
• Oracle: ojdbc6.jar
• PostgreSQL: postgresql-9.0-801.jdbc4.jar
• Pervasive: jpscs.jar, pvjdbc2.jar, and pvjdbc2x.jar

Download links:

• MySQL driver: http://dev.mysql.com/downloads/connector/j/

  Note: As of JasperReports Server v5.5, the MariaDB opensource MySQL DB Driver, is included in the installation.

  For more info, see: MariaDB Foundation » MariaDB Java Client

• jTDS driver for SQL Server and Sybase: http://jtds.sourceforge.net/
• Microsoft SQL Server: http://msdn.microsoft.com/en-us/data/aa937724.aspx
• Oracle: http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html
• PostgreSQL: http://jdbc.postgresql.org/download.html
• Pervasive: http://www.pervasivedb.com/psqlv11/Pages/Default.aspx
• Terradata (JDBC Driver): http://developer.teradata.com/doc/connectivity/jdbc/reference/current/frameset.html
• More Information here

Copy the Driver to the JasperReports Server Classpath

Now you need to copy the driver (the .jar file) to the classpath of your application server to enable JasperServer to find it.

In general it's best to copy the driver to the application server's shared library location. Refer to your application server documentation for exact locations.

Sample locations:

• Tomcat5: <catalina_home>commonlib

  (for example in a JapserServer Bundle install on windows this is located at: C:/Program Files (x86)/jasperreports-server-4.0/apache-tomcat/lib )

• Tomcat6: <catalina_home>lib

  (for example: /var/lib/tomcat6/lib)

• JBoss5: <jboss_home>serveralllib

Restart JasperReports Server

After these modifications you will need to restart the application server for this settings to take effect.

For information on restarting your server see Chapter 3 of the JasperReport Server Install Guide (is located on the /docs folder of your Jasper Server install path)

If you installed JasperReport Server using the Windows installer, there is a shortcut in the start menu to restart it.

- Click Start > All Programs > JasperReports Server 5.0 > Start or Stop Services > Stop Service.

- Click Start > All Programs > JasperReports Server 5.0 > Start or Stop Services > Start Service.

If you have used the Linux installer, you need to run the scripts in your JasperReportes Server install folder:

To Stop JasperReports Server:

cd <js-install>
./ctlscript.sh stop

To Start JasperReports Server:

cd <js-install>
./ctlscript.sh start

For users on MacOSX see Chapter 3.3 in the JasperReports Server Install Guide.

Prefer JNDI datasources to JDBC datasources

One potential bottleneck is a datasource that doesn't offer a connection pool. This is why we recommend using JNDI, which allows you to configure an entire connection pool that resides within the container. In Tomcat, this involves modifying the /jasperserver-pro/META-INF/context.xml file, and editing the object's values for the maxIdle and maxActive attributes.

For more information, see: How to configure a connection pool for a JNDI data source

Creating the connection in JasperReport Server (Data Source)

This step assumes that you have copied the JDBC Driver to the Jasperserver classpath (as explained above) and restarted your Application Server (i.e. Tomcat).

Create a Data Source in JasperReports Server

1. Log into JasperReport Server from your browser using the jasperadmin or superuser account (e.g.: http://localhost:8080/jasperserver-pro)

2. Go to View | Repository.

3. Select a folder where you want to create your Data Source (e.g.: /Data Sources)

4. Right click on the folder name in the repository view and from the context menu select called Add Resource and then click on Data Source.

   

5. Now you are presented with a screen to enter the settings of your Data Source connection

   

6. Choose a data source type in this example choose either JDBC.

7. Provide a Name, Resource ID, and Description (optional).

8. If you selected the JDBC connection type, you will typically enter values similar to these samples, where “hostname” is the hostname of your DB server and “yourDatabase” is the name of the database you want to connect to:

   1. MySQL

         Driver:  com.mysql.jdbc.Driver

         Sample URL: jdbc:mysql://hostname:3306/yourDatabase

   2. Microsoft SQL Server

         Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver

         Sample URL: jdbc:sqlserver://hostname:1433;databaseName=yourDatabase

   3. jTDS (for Microsoft SQL Server)

         Driver: net.sourceforge.jtds.jdbc.Driver

         Sample URL: jdbc:jtds:sqlserver:// hostname:1433/yourDatabase

         Sample URL: jdbc:jtds:sqlserver:// hostname:1433/yourDatabase;intance=yourInstance

   4. Oracle (thin driver)

          Driver: oracle.jdbc.OracleDriver

          Sample URL: jdbc:oracle:thin:@mydb_hostname.com:1521:your_db_instance_name

         Sample URL: jdbc:oracle:thin:@remotehost:1521:orcl

          Standard format: jdbc:oracle:thin:@//HOSTNAME[:PORT]/SERVICE

   5. PostgreSQL

         Driver: org.postgresql.Driver

         Sample URL: jdbc:postgresql:// hostname:5432/yourDatabase

   6. Pervasive

         Driver: com.pervasive.jdbc.v2.Driver

         Sample URL: jdbc:pervasive://hostname:1583/yourDatabase

9. Finally, enter the Username and Password you will use to connect to your DBMS

You can use the TEST button to check your connection and Save your new data source.

If the test fails, save your data source anyway and check the Troubleshooting section below that explains you how to enable logging to troubleshoot conectivity issues.

Data Source Troubleshooting

When adding a data source to Jasperserver, there are several areas that could introduce errors. This article is intended to assist in troubleshooting some common issues when setting up JasperServer Data Sources.

General Issues

General Database Server Conectivity

• Check that the driver name and URL provided in the data source are correct (see Create a Data Source section above).
• Check that the .jar file for the correct driver is in the application classpath (see Copy the driver to the ClassPath section above). Also check if there are no other drivers in the classpath that use the same classes, for example if you are using MS SQL Server only ONE .jar file should be added to the classpath, if you add “sqljdbc4.jar” and “sqljdbc.jar” at the same time the connection will fail.
• Check that your database server is available and accepting TCP/IP connections from the host where JasperServer is installed.
• Check in your DBMS that the username and password you are using are correct and have access to the selected database.
• Check for firewalls or network connectivity errors.

Logging

Often the quickest way to find the issue is to increase the logging.

Find the following section in .../webapps/jasperserver-pro/WEB-INF/log4j.properties and uncomment the relevant logger:

#JDBC Connection Logging #log4j.logger.com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.JdbcDataSourceService=DEBUG, stdout, fileout #log4j.logger.com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.JndiJdbcDataSourceService=DEBUG, stdout, fileout #log4j.logger.com.jaspersoft.jasperserver.war.action.ReportDataSourceAction=DEBUG, stdout, fileout #log4j.logger.com.jaspersoft.commons.datarator.JdbcDataSet=DEBUG, stdout, fileout #log4j.logger.com.jaspersoft.jasperserver.war.common.JasperServerUtil=DEBUG, stdout, fileout #log4j.logger.com.jaspersoft.commons.semantic.dsimpl.JdbcDataSetFactory=DEBUG, stdout, fileout #log4j.logger.com.jaspersoft.commons.semantic.metaapi.impl.jdbc.BaseJdbcMetaDataFactoryImpl=DEBUG, stdout, fileout #log4j.logger.com.jaspersoft.jasperserver.war.validation.ReportDataSourceValidator=DEBUG, stdout, fileout

Database Driver

Ensure Driver is in the Classpath

JasperServer ships with drivers for several databases, including MySQL, Oracle, PostgreSQL, etc. It is important to ensure that the drivers for your database are included in the JasperServer classpath that will be either apache-tomcat/lib or apache-tomcat/webapps/jasperserver-pro/WEB-INF/lib. Remember to restart the Application server after putting the driver in the classpath.

Ensure that the Driver Class Name is correct

The following are some example driver class names for various databases:

Database Privileges

When creating Database users, you must ensure that the user has the appropriate permissions to your database as well as from the server that JasperServer is running on.

Permissions Required to the Database

The database user that is used within your data source definition should have the appropriate select permissions to query the tables within your database for the reports you wish to generate.  The easiest way to troubleshoot this is to use a tool, such as SquirrelSQL or other database query tool, to run various queries against your database.

Ensuring Permission from JasperServer

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 example, you may refer to the following page for Setting up MySQL users: http://dev.mysql.com/doc/refman/5.1/en/adding-users.html

An fairly easy way to test connectivity from the server to that database location with a particular user is to use a tool such as SquirrelSQL or another DB query tool to connect to the database from the same host as JasperServer.

JDBC Data Source Issues

Ensure that the Database URL string entered when defining your JDBC Connection is consistent with what is required for your specific database and database driver.

JNDI Data Source Issues

JNDI is handled by your application server, check the documentation of your application server for help with JNDI data sources.

MS SQL Server

Configuring Microsoft SQL Server involves many things and enumerating all possible source of issue would be too long to be described here and generally unrelated with JasperReports.

The following Video touches on many of those items:

Situation: One thing though which can happen is, in the situation when you use MSSQL setup to use windows authentication credentials and thus you don't need to pass any username or password in the JNDI connection, you may get a connection failure due to the way the Windows Service which starts JasperReports Server is setup. For example if the MS SQL Server Database is located on a different server than JasperReports server, the credentials passed for the Windows authentication will be, by default, the one from the server which runs the server and not from the server which runs the database. If this situation happens, the logs (when setup the way described above) will show that the server name is passed as the username.

Fix: To fix this you need to restart your server without using the Windows service or to change the log on properties of the service from local to the other account.

Other Resources

• Business Intelligence Tutorials
• Wiki Search URL: Keywords: JasperReports Server, Data Sources
• https://www.jaspersoft.com/bi-training-center