Previous Topic Next Topic

Setting Up JDBC Connections in a Catalog

Logi JReport supports most of the relational databases which support JDBC drivers. Via specific JDBC drivers, you can create connections which connect Logi JReport catalogs to different relational databases.

Below is a list of the sections covered in this topic:

• Setting Up the JDBC Driver
• Creating a Connection via the JDBC Driver
  • Example 1: Connecting to Amazon RDS
  • Example 2: Connecting to RedShift
  • Example 3: Connecting via WebLogic 6.1 Connection Pool

Setting Up the JDBC Driver

Before you can retrieve data from a relational database in Logi JReport, you should first set up the JDBC driver as follows:

1. Install the JDBC driver according to the instructions provided by the JDBC driver supplier and understand the URL format required by the driver.
2. Append the class path of the JDBC driver's jar files with full path into Logi JReport's environment configuration file by editing setenv.bat for Windows or setenv.sh for Unix/Linux in <designer_install_root>\bin.

   For example, if you are using the Oracle JDBC driver ojdbc7.jar, append it as follows:

   set ADDCLASSPATH=%JAVAHOME%\lib\tools.jar;c:\oracle\lib\ojdbc7.jar;

   The step for appending the class path is very important. The same changes made to Logi JReport Designer’s class path must be made to the class path for Logi JReport Server too. A missing JDBC driver in the Logi JReport start-up batch file or command line will result in a "ClassNotFoundError message" when you try to run a report. Meanwhile, if you want to use the Preview as Page/Web Report Result feature in Logi JReport Designer, you also need to append the class path for the JDBC driver to setenv.bat/setenv.sh in <designer_install_root>\server\bin.

3. Add the driver into Logi JReport Designer's driver template file jdbcdrivers.properties in <designer_install_root>\bin in the following format:

   jdbc.drivers=JDBCDriverName:JDBCDriverName:...

   where, JDBCDriverName is the JDBC driver name that can be auto-loaded when Logi JReport starts up and ":" is the delimiter between two driver names. Below is an example of the jdbcdrivers.properties file which specifies an Oracle thin driver and an Interbase thin driver:

   jdbc.drivers=oracle.jdbc.driver.OracleDriver:interbase.interclient.Driver

   Once the drivers are added in jdbcdrivers.properties, later when you set up JDBC connections in Logi JReport Designer, you do not need to provide the driver name manually. Logi JReport will search from the beginning of the classpath and find one that contains the class specified.

Creating a Connection via the JDBC Driver

To set up a JDBC connection in a Logi JReport catalog to retrieve data via JDBC driver, take the following steps:

1. In Logi JReport Designer, create a catalog or open a catalog.
2. In the Catalog Manager, right-click the node of a data source and select New JDBC Connection from the shortcut menu. Select JDBC in the Select Connection Type dialog.

   If you want to set up the connection in a new data source in the catalog, select any of the existing catalog data sources, select New Data Source on the Catalog Manager toolbar, then in the New Data source dialog, specify the name of the data source, select the JDBC connection type and select OK.

3. In the Get JDBC Connection Information dialog, provide the necessary information to connect with the database.

   

   You have four ways to set up a JDBC connection:

   • Setting up by adding an existing connection
     1. From the Connection List drop-down list select the connection. This list contains previously added connection information. The format of the connection information is JDBC URL/(JDBC Driver Name), for example:

        jdbc:odbc:jinfonet/(sun.jdbc.odbc.JdbcOdbcDriver)

     2. The JDBC URL and JDBC driver name then appear in the corresponding text boxes.
   • Setting up a new JDBC connection
     1. In the Driver text box specify the driver. If you have added the driver into jdbcdrivers.properties when setting up the JDBC driver, you can leave this empty. Logi JReport Designer can find the correct driver from the file.
     2. Specify the URL of the JDBC driver in the URL text box. The URL format is regulated by the driver itself.
     3. Input the user name and password used to connect to the database.
   • Setting up an ODBC connection
     1. Uncheck the Driver checkbox and check Use ODBC Data Source.
     2. Enter the ODBC data source name in the DSN Name text box.
     3. Input the user name and password to enable accessing the database through the ODBC data source.

     Notes:

     • The ODBC-JDBC bridge is not included in Java JDKs after version 7. For JDK 8 or later, you will get an error that "No suitable driver found for jdbc:odbc". To resolve this issue, you need to add the path information of the ODBC-JDBC bridge to the class path during installation or by editing the setenv.bat (setenv.sh on Unix/Linux) file in <designer_install_root>\bin.
     • The JDK that Logi JReport uses must match the ODBC data source that the operating system uses. For example, 32-bit ODBC data source can be connected by 32-bit JDK only.
   • Setting up a connection via the WebLogic 6.1 connection pool
     1. Check theUse Connection Poolcheckbox. The string weblogic.jinfonet.pool.Driver is then automatically displayed in the Driver text box.
     2. In the URL text box, specify the URL of the JDBC driver.
     3. Input the user name and password respectively.
4. If your database has some special requirements, you can select the More Options button to modify the options according to your requirements.
5. Select Test Connection to test whether the information you provide is available.
6. Select OK to set up the connection, and you will be prompted with a message box showing the status of connecting to the database.

   Upon finishing setting up the connection, the Add Tables/Views/Synonyms dialog is displayed, prompting you to add tables from the database to the Logi JReport catalog. If you want to add tables at a later date, select Done.

For the Oracle, MySQL, SQL Server and PostgreSQL databases, you can also connect via connection plugins.

The following presents some examples of connecting to specific relational databases:

Example 1: Connecting to Amazon RDS

Assume that:

• You have already installed the JDBC driver (com.mysql.jdbc.Driver), and have appended the archive files of the driver to the ADDCLASSPATH variable in the file setenv.bat in <designer_install_root>\bin.
• The RDS database server has the following information:

  Host name: jrdbtest.c4fb8hiicidz.us-west-2.rds.amazonaws.com
  Port number: 3306
  Database name: sampledb1110
  Database user & password: dbadmin, test1234

Take the following steps to set up a connection which connects Logi JReport Designer to a database via Amazon RDS:

1. Open a catalog in Logi JReport Designer.
2. In the Catalog Manager, right-click the node of an existing data source and select New JDBC Connection from the shortcut menu. Select JDBC in the Select Connection Type dialog.
3. In the Get JDBC Connection Information dialog, enter the JDBC driver class name com.mysql.jdbc.Driver in the Driver text box.
4. In the URL text box, specify the URL in the format jdbc:mysql://<hostname>:<port>/<database>. In this example, enter jdbc:mysql://jrdbtest.c4fb8hiicidz.us-west-2.rds.amazonaws.com:3306/sampledb1110. The URL is dynamically generated when you apply an instance.
5. Input the user name dbadmin and password test1234 respectively.

   

6. Select OK to set up the connection.

Example 2: Connecting to RedShift

Assume that:

• You have already installed the PostgreSQL JDBC driver (org.postgresql.Driver), and have appended the archive files of the driver to the ADDCLASSPATH variable in the file setenv.bat in <designer_install_root>\bin.
• The RedShift database server has the following information:

  Host name: jinfonet-rsdw-demo.cfcn5ogc14yr.us-east-1.redshift.amazonaws.com
  Port number: 5439
  Database name: sampledb
  Database user & password: dbadmin, test1234

Take the following steps to set up a connection which connects Logi JReport Designer to a database via RedShift:

1. Open a catalog in Logi JReport Designer.
2. In the Catalog Manager, right-click the node of an existing data source and select New JDBC Connection from the shortcut menu. Select JDBC in the Select Connection Type dialog.
3. In the Get JDBC Connection Information dialog, enter the JDBC driver class name org.postgresql.Driver in the Driver text box.
4. In the URL text box, specify the URL in the format jdbc:postgresql://<hostname>:<port>/<database>. In this example, enter jdbc:postgresql://jinfonet-rsdw-demo.cfcn5ogc14yr.us-east-1.redshift.amazonaws.com:5439/sampledb. The URL is dynamically generated when you apply an instance.
5. Input the user name dbadmin and password test1234 respectively.

   

6. Select OK to set up the connection.

Example 3: Connecting via WebLogic 6.1 Connection Pool

1. Set up the WebLogic Connection Pool.
   1. Assume that the WebLogic Server has been started, access the console through a web browser (http://host:7001/console). Then, go to the left panel, and expand the JDBC node.
   2. Select the Connection Pools node. All the defined connection pools will be displayed in the right panel.
   3. Select the Configure a New JDBC Connection Pool link.
   4. In the Configuration tab, define the connection pool as follows. Then, select the Create button.

      Name: jinfonet
      URL: jdbc:odbc:jinfonet
      Driver Classname: sun.jdbc.odbc.JdbcOdbcDriver

2. Check the Connection Pool.

   In the Monitoring tab, select the link Monitor all Active Pools to check if the pool is active. You will see the connection pool that you just created appear in the Monitoring tab.

3. Select the Targets tab, and add examplesServer to the Chosen column (in order to assign the connection pool to the server). Then, select Apply to save your changes.
4. Set up the data source.
   1. Select the Data Sources node in the JDBC node. In the right panel, select the Configure a New JDBC Data Source link.
   2. In the Configuration tab, enter the values in the following fields as follows:

      Name: jinfonetDS
      JNDI Name: jinfonetDS
      Pool Name: jinfonet

   3. Select Create. The new data source will then be added in the Data Sources node in the left panel.
5. Select the Targets tab and add examplesServer in the Available column to the Chosen column. Then, select Apply to save the changes.
6. Add weblogic.jar to setenv.bat.

   To connect via the WebLogic Connection Pool in Logi JReport Designer, you will first need to add weblogic.jar in <weblogic_install_root>\wlserver6.1\lib to the ADDCLASSPATH variable of setenv.bat. In this example, supposing that you have installed Logi JReport Designer in C:\JReport\Designer, and WebLogic in C:\bea, you will have to modify setenv.bat in C:\JReport\Designer\bin to add C:\bea\wlserver6.1\lib\weblogic.jar to the ADDCLASSPATH variable as follows:

   set ADDCLASSPATH=%JAVAHOME%\lib\tools.jar;C:\bea\wlserver6.1\lib\weblogic.jar;

7. Set up the connection in Logi JReport Designer.
   1. Open a catalog.
   2. In the Catalog Manager, right-click the node of an existing data source and select New JDBC Connection from the shortcut menu. Select JDBC in the Select Connection Type dialog.
   3. In the Get JDBC Connection Information dialog, check Use Connection Pool. The string weblogic.jinfonet.pool.Driver automatically displays in the Driver text box.
   4. Fill in the URL jdbc:weblogic:jinfonet:@<hostname>:7001:jinfonetDS, where <hostname> is the host name or IP address of the WebLogic Server, 7001 is the port on which the WebLogic Server listens, and jinfonetDS is the JNDI Name of the data source. Then, select OK.

Previous Topic Next Topic