Repository JDBC Concepts

JDBC provides a way for Java programs to access databases and other data sources. Using JDBC, ReportCaster connects to your repository. It then creates and executes SQL statements to access and write repository information. In theory, JDBC provides a level of abstraction so that most SQL statements work on most databases. However, in practice, differences occur and you should ensure you choose a database and driver that are supported by WebFOCUS.

In order for the WebFOCUS Client to connect to a repository using JDBC, the following are required:

• User ID and Password
• JDBC Driver
• JDBC Path

The credentials you provide to the database are critical, as they determine how you access the repository. Depending on the type of database, if you wish to maintain separate repositories for separate instances of WebFOCUS Client, you may need separate user IDs.

During the WebFOCUS Client installation, the credentials are set in the WebFOCUS configuration file, install.cfg. If you need to change these values, you can edit this file. The WebFOCUS Administration Console allows you to change the password.

The JDBC driver is a class name used to access the driver. This varies depending on the driver.

During the Distribution Server installation, this is determined and set.

• For Derby, Oracle, SQL Server, MySQL, and DB2, the installation automatically writes the JDBC driver class name for the standard driver.
• For other databases and drivers, you are prompted to provide the JDBC driver class name. This value is stored in the WebFOCUS configuration file, install.cfg. If you need to change this value, you can edit this file.

A JDBC driver is usually packaged as one or more JAR or ZIP files. Each target data source has its own JDBC driver, so you would use the Oracle JDBC driver to access Oracle and the SQL Server JDBC driver to access SQL Server. Some vendors may also require different drivers for different database releases.

There are two types of JDBC drivers that connect in different ways. WebFOCUS normally uses a Type 4 or Type 2 driver. A Type 4 driver is entirely Java-based. A Type 2 driver includes files compiled for a specific platform (native files).

The JDBC driver must be installed on the machine or machines that run WebFOCUS Client and ReportCaster Distribution Server. For Type 4 drivers, you can normally just copy the driver into a directory on the WebFOCUS machines. For Type 2 drivers, you may need to install separate components.

The JDBC driver is used by both the Distribution Server and the application server. For ReportCaster to find the driver, the JDBC driver must be included in their CLASSPATH variables.

• For the Distribution Server, you provide the location of the driver during the Distribution Server installation. The installation uses this information to add the location of the driver to the CLASSPATH variable used by ReportCaster scripts and utilities. This is set in the following file:

  drive:\ibi\WebFOCUS82\ReportCaster\bin\classpath.bat

  and in the registry:

  HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Information Builders\ReportCaster\WF82\Parameters\Java\Classpath

• For the application server, you set your application server CLASSPATH variable to include the driver file or files.

  For Apache Tomcat, this is set if you choose to configure Tomcat when you install WebFOCUS Client. To manually set it, use the Start menu to select Tomcat Configuration Utility, under the Information Builders Program Group. Then, select the Java tab and add a semicolon (;) plus the full path of the file to the end of the Java Classpath field.

  Note: You can also copy driver files into the WEB-INF/lib directory located inside the webfocus.war file or WebFOCUS82 directory before you deploy the web application.

You must always specify the driver file or files, not just the directory containing the driver. You enter the JDBC driver file name in the JDBC Path field.

During the Distribution Server installation, this is created and set.

• For Derby, Oracle, SQL Server, MySQL, and DB2, you are prompted for specific information needed to access your repository. This varies depending on the type of database.
• For other databases and drivers, you must provide the JDBC path.

This value is stored in the WebFOCUS configuration file, install.cfg, and in ..\utilities\setenv\utiluservars.bat. If you need to change the value, you can edit these files.

The JDBC class is a value used to access the JDBC driver. The JDBC class value varies depending on the driver.

During the WebFOCUS Client installation, the JDBC class value is determined and set based on the database selection.

• For Oracle, SQL Server, MySQL, and DB2, the installation automatically writes the JDBC CLASS for the standard driver.
• For other databases and drivers, you are prompted to provide the JDBC CLASS.

The JDBC class value is stored in the WebFOCUS configuration file, install.cfg. You can edit this file if you need to alter the JDBC driver information and provide a different JDBC class value.

The JDBC URL is a value used to access the driver and repository. This varies depending on the driver and other connection information.

During the WebFOCUS Client installation, the JDBC URL is set based on the selected database.

• For Oracle, SQL Server, MySQL, and DB2, you are prompted for specific information needed to access your repository. This varies depending on the type of database and may include the host name or port where your database resides. The installation uses this information to create the JDBC URL.
• For other databases and drivers, you must provide the JDBC URL.

The JDBC URL value is stored in the WebFOCUS configuration file, install.cfg. You can edit this file if you need to alter the JDBC driver information and provide a different JDBC URL value.