Working With JDBC Drivers

Working With JDBC Drivers

This section describes how to set up your installation to use a driver other than the default driver.

Open Source JDBC Drivers

For open source JDBC drivers, buildomatic is set up to use a single default driver. If you want to use a driver other than the default driver, you can modify the buildomatic property files that determine the default JDBC driver.

The buildomatic JDBC driver property files are set up to point to a specific driver jar. This allows for multiple driver jar files in the same buildomatic/conf_source/db/<dbType>/jdbc folder. During the installation procedure only the default driver jar is copied to your application server.

If you want to use a newer JDBC driver version or a different JDBC driver, you can modify the buildomatic properties seen in your default_master.properties file.

PostgreSQL Example

The buildomatic/conf_source/db/postgresql/jdbc folder contains these driver files: 

postgresql-9.2-1002.jdbc3.jar

postgresql-9.2-1002.jdbc4.jar

If, for instance, you want to change the default driver used by PostgreSQL from type jdbc4 to jdbc3, edit your default_master.properties file: 

<js-install>/buildomatic/default_master.properties

Uncomment and change: 

# maven.jdbc.version=9.2-1002.jdbc4

To:

maven.jdbc.version=9.2-1002.jdbc3

When you next run a buildomatic command, such as deploy-webapp-pro, the jdbc3 driver will be copied to your application server.

MySQL Example

The buildomatic/conf_source/db/mysql/jdbc folder contains this driver file:

mariadb-java-client-1.1.2.jar

If, for instance, you want to use a JDBC driver built and distributed by the MySQL project, such as mysql-connector-java-5.1.30-bin.jar, you first need to download the driver from the MySQL Connector/J download location:

https://dev.mysql.com/downloads/connector/j/

Next, change your buildomatic configuration properties to point to this new driver.

Edit your default_master.properties file: 

<js-install>/buildomatic/default_master.properties

Uncomment and change: 

# jdbcDriverClass=com.mysql.jdbc.Driver

# maven.jdbc.groupId=mysql

# maven.jdbc.artifactId=mysql-connector-java

# maven.jdbc.version=5.1.30-bin

To: 

jdbcDriverClass=com.mysql.jdbc.Driver

maven.jdbc.groupId=mysql

maven.jdbc.artifactId=mysql-connector-java

maven.jdbc.version=5.1.30-bin

Commercial JDBC Drivers

As of version 5.6.1, JasperReports Server includes the TIBCO JDBC drivers for the following commercial databases. You can connect to these databases using the TIBCO JDBC driver without additional steps. The driver name is in the <js-install>\buildomatic\conf_source\db\<your_database>\jdbc directory in the following form:

Oracle — TIoracle-X.X.jar
SQL Server — TIsqlserver-X.X.jar
DB2 — TIdb2-X.X.jar

These drivers require a valid JasperReports Server license. The driver is for use by JasperReports Server only, and the driver jar must be located under the jasperserver-pro directory, for example, <tomcat_home>/tomcat/jasperserverpro/web-inf/lib.

If you're using the default settings for the driver, you don't need to edit default_master.properties.

You can also choose to use the driver supplied by the database vendor as described below. To do this, you must first obtain and install the driver you want, then modify your default_master.properties to use your driver.

Download an Optional JDBC Driver Jar

To use the driver supplied by the database vendor, you can optionally download and install it.

Download Driver Jar from Vendor Website:

You can download a commercial JDBC driver from the vendor's website. Here are some sites where you can download packages for supported databases: 

     http://www.microsoft.com/en-us/download/details.aspx?id=11774 (SQL Server)
     http://www.oracle.com/technetwork/indexes/downloads (Oracle)
     http://www-01.ibm.com/software/data/db2/linux-unix-windows/downloads.html (DB2)

Once you have downloaded your driver, copy it to the correct location and configure your files as described in the sections below.

Collect Driver Jar from Existing Application:

You may already have a JDBC driver in an application running on your network. If so, you can simply copy that driver jar to the JasperReports Server install location.

Oracle Example

1. Copy your Oracle driver to the following directory:

<js-install>/buildomatic/conf_source/db/oracle/native.jdbc

2. Change to the <js_install>/buildomatic directory and open default_master.properties in a text editor.
3. Go to the Additional Settings section in this file.
4. Go to the first setup item, Setup Standard Oracle JDBC Driver.
5. Follow the instructions to uncomment the required properties and enable your driver. The following example shows how to set up default_master.properties to point to a driver named ojdbc6-11.2.0.3.jar using SID:
# 1) Setup Standard Oracle JDBC Driver
#
# Uncomment and modify the value to native
jdbcDriverMaker=native
#
# Uncomment and modify the value in order to change the default
# 1a) Driver will be found here: <path>/buildomatic/conf_source/db/oracle/native.jdbc
#
maven.jdbc.groupId=oracle
maven.jdbc.artifactId=ojdbc6
maven.jdbc.version=11.2.0.3
 

If you're using an Oracle service name instead of an SID, uncomment the line serviceName= and add your service name.

6. Save the default_master.properties file.

SQL Server Example

1. Copy your SQL Server driver to the following directory:

<js-install>/buildomatic/conf_source/db/sqlserver/native.jdbc

2. Change to the <js_install>/buildomatic directory and open default_master.properties in a text editor.
3. Go to the Additional Settings section in this file.
4. Go to the first setup item, Setup Standard SQL Server JDBC Driver.
5. Uncomment the required properties and enable your driver. The following example shows how to set up default_master.properties to point to a driver named sqljdbc-1.6.jar:
# 1) Setup Standard SQLServer JDBC Driver
#
# Uncomment and modify the value to native
jdbcDriverMaker=native
#
# Uncomment and modify the value in order to change the default
# Driver will be found here: <path>/buildomatic/conf_source/db/sqlserver/native.jdbc
#
maven.jdbc.groupId=sqlserver
maven.jdbc.artifactId=sqljdbc
maven.jdbc.version=1.6
6. Save the default_master.properties file.

DB2 Example

1. Copy your DB2 driver to the following directory:

<js-install>/buildomatic/conf_source/db/db2/native.jdbc

2. Change to the <js_install>/buildomatic directory and open default_master.properties in a text editor.
3. Go to the Additional Settings section in this file.
4. Go to the first setup item, Setup Standard DB2 JDBC Driver.
5. Uncomment the required properties and enable your driver.
# 1) Setup Standard DB2 JDBC Driver
#
# Uncomment and modify the value to native
jdbcDriverMaker=native
#
# Uncomment and modify the value in order to change the default
# Driver will be found here: <path>/buildomatic/conf_source/db/db2/native.jdbc
#
maven.jdbc.groupId=ibm
maven.jdbc.artifactId=db2jcc
maven.jdbc.version=9.7
6. Add the following additional properties, setting the correct values for your installation. For example:
db2.driverType=4
db2.fullyMaterializeLobData=true
db2.fullyMaterializeInputStreams=true
db2.progressiveStreaming=2
db2.progressiveLocators=2
dbPort=50000
js.dbName=JSPRSRVR
sugarcrm.dbName=SUGARCRM
foodmart.dbName=FOODMART
7. Save the default_master.properties file.

Working with Oracle RAC

As of JasperReports Server 6.1, you can use Oracle 12c RAC with the TIBCO JDBC Oracle driver. This driver works with Oracle 12c RAC with the following settings:

Use Oracle 12C for non-CDB/PDB connection settings as described in All Oracle versions other than Oracle 12c with CDB/PDB (including 12c non-CDB).
Use js-install.bat/sh minimal with Oracle 12c. This option does not install sample databases.

To support additional functionality with Oracle RAC, such as load balancing with multiple servers, you need to configure your application server and manually set up the correct connection URL.

Tomcat Load Balancing Example

1. Change to the <tomcat>/webapps/jasperserver-pro/META-INF directory and open context.xml in a text editor.
2. Edit the url property by adding additional connection properties for data sources you want. The following example shows how to set up connection pool to use Oracle RAC with load balancing with a primary server and three alternate servers:
jdbc:tibcosoftware:oracle//server1:1521;ServiceName=SERVICE;AlternateServers=(server2:1521,server3:1521,server4:1521);LoadBalancing=true

JBoss EAP/WildFly Load Balancing Example

1. Change to the <jboss-install>/standalone/deployments/jasperserver-pro.war/WEB-INF directory and open js-jboss7-ds.xml in a text editor.
2. Edit the connection-url tag for the data sources you want. The following example shows how to set up a connection pool to use Oracle RAC with load balancing with a primary server and three alternate servers.
<datasource jta="false" jndi-name="java:/jdbc/jasperserver" pool-name="jasperserver" 
		enabled="true" use-ccm="false">
    <connection-url>jdbc:tibcosoftware:oracle//server1:1521;ServiceName=SERVICE;AlternateServers=(server2:1521,server3:1521,server4:1521);LoadBalancing=true</connection-url>
    <driver>TIoracle-5.14.2.jar</driver>
    <security>  
        <user-name>jasperserver</user-name>
        <password>password</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 FROM DUAL</check-valid-connection-sql>
    </validation>
    <statement>
        <share-prepared-statements>false</share-prepared-statements>
    </statement>
</datasource>

Glassfish Load Balancing Example

For Glassfish, the URL must be edited from the admin console.

1. Open the Glassfish admin console. The default location is http://hostname:4848
2. Go to Resources > JDBC > JDBC Connection Pools and select the connection pool for the data sources you want.
3. Select the Additional Properties tab and set the url property. For example:
jdbc:tibcosoftware:oracle//server1:1521;ServiceName=SERVICE;AlternateServers=(server2:1521,server3:1521,server4:1521);LoadBalancing=true

Websphere Load Balancing Example

Using the Websphere console, set up the TIBCO Oracle driver as described in Defining a JNDI Name and Sample Data Sources for DB2. Then define custom properties as described in Custom Properties for TIBCO JDBC Driver for Oracle. For example, set the AlternateServers and LoadBalancing properties.

Property Name Value
serverName server1

portNumber

1521

ServiceName SERVICE
AlternateServers (server2:1521,server3:1521,server4:1521)
LoadBalancing true

WebLogic Load Balancing Example

Using the WebLogic console, set up the driver as described in Configuring a TIBCO JDBC Oracle Connection. Then set the URL property for your servers, for example:

jdbc:tibcosoftware:oracle//server1:1521;ServiceName=SERVICE;AlternateServers=(server2:1521,server3:1521,server4:1521);LoadBalancing=true

Application Server Copy-to Locations

When the deploy-webapp-pro buildomatic target is executed it copies the JDBC driver to the following default locations:

Tomcat:

<tomcat>/lib

JBoss:

<jboss>/standalone/deployments

Wildfly: <wildfly>/standalone/deployments

GlassFish:

<glassfish>/domains/domain1/lib/ext

Feedback