Database Driver

This page is for administrators who need to connect PhixFlow to external databases.

Overview

The PhixFlow application can communicate with an external database from which it needs to load data. This is configured using a Datasource in an analysis model. 

To communicate with an external database, PhixFlow needs:

  1. The jar files for connecting to a database, stored in PhixFlow's Database Driver File Directory. This location is set directly in System Configuration → System Directories → Database Driver File Directory.
  2. A database driver configured in the repository. One database driver item can refer to multiple jar files.

Preinstalled Databases

There are three databases on which PhixFlow can be installed:

  1. MySQL
  2. MS SQL Server
  3. Oracle.

For each of these, PhixFlow is preconfigured with:

  • jar files stored in in tomcat/webapps/phixflow/WEB-INF/lib 
  • a database driver in the repository, which uses jar files.

You can use these pre-configured database drivers in a datasource. However, you cannot update their properties or see their jar files.

Additional Databases

You may need to configure additional database drivers to support:

  • other types of database
  • different versions a database.

How to Configure a Database Driver

  1. Find out which JDBC database drivers you require. This information is available in the documentation for your database. Also check the System Requirements and Compatibility page for supported versions. Communication to a database may require multiple JDBC driver files. 
  2. Download the required JDBC driver files from:
  3. Ensure you have a Database Driver File Directory setup. If it has been setup it can be seen in System Configuration → System Directories → Database Driver File Directory.
    1. If it has not been setup, create the directory in a location available to Tomcat.
    2. Open Administration → System Configuration → System Directories, and update the Database Driver File Directory with the path of the directory.
  4. In PhixFlow, for each type of database, create one database driver;
    1. In the full repository scroll down to the Database Drivers section. 
    2. To add a new driver, click . PhixFlow opens a property tab where you can enter the details for the new driver.
      1. To open an existing database driver, in the repository double-click on its name.
    3. In the database driver properties → Database Driver File section, add all the JDBC driver files that the database requires; see Properties Tab → Settings, below.
    4. For each jar file, specify the path to the file stored in the Database Driver File Directory.


You cannot change the pre-configured database drivers supplied with your PhixFlow installation. These have the System Defined property ticked.

PhixFlow also includes a number of unconfigured drivers, such as Google Big Query, which are included for backwards compactivity. See Deprecated Database Drivers below for full details. 

 Properties Tab

The database drivers configured in the repository populate the drop-down list in Datasource properties Basic Settings → Database Driver 

Parent Details

If this item is within or belongs to another, its parent name is shown here. See the Parent Details section on the Common Properties page for more details.

 This section is available for custom database drivers and shows the package to which a database driver has been added, if any.

If you intend to use an analysis model in multiple PhixFlow instances, and the model contains a datasource that uses a custom database drivers, remember to add the database driver to the same application or package as the analysis model; see Using Drivers Across Multiple Instances below.

Parent Details

If this item is within or belongs to another, its parent name is shown here. See the Parent Details section on the Common Properties page for more details.

Settings

Basic 

FieldDescription

Name

Enter a unique name for the database driver, which will appear in the PhixFlow repository.
Database Driver Name

Enter the  database driver name, which you will find in the driver documentation. For example, for MySQL, the driver name is: com.mysql.jdbc.Driver

System Defined

Read-only.

 Untick indicates this  database drivers is custom defined for your PhixFlow instance. You can change it's configuration.

 Tick indicates this driver is preconfigured when PhixFlow is installed. It is required for the system and cannot be updated.

DescriptionOptionally enter a description for the driver.

Advanced

FieldDescription
Datasource Profile

Use this option to specify the datasource properties to use. Enter:

  • default - for most drivers
  •  bigQuery - for Google BigQuery.

Database Driver File

This section has a toolbar with standard buttons

The grid lists the jar files for this database driver. Each database usually requires multiple jar files. To add a jar file to the list:

  1. Click  Create New to open a the properties for a new Database Driver File Location.
    (Database Driver File Location properties are only available from this grid. They are not listed in the repository.)
  2. In the Basic Settings section, set:

    FieldDescription

    Name

    Enter a unique name for the driver.  
    Connector Jar File Location

    The root Database Driver File Directory, must be specified in System Configuration

    Specify a path, relative to the root Database Driver File Directory:

    • either to a specific driver jar file
    • or to a sub-directory that contains multiple driver jar files.

    For example:

    • if the file is /opt/phixflow-drivers/BigQueryJDBC/driverfile.jar
    • and the Database Driver File Directory is set to /opt/phixflow-drivers/

    then enter:

    • for Linux: BigQueryJDBC/driverfile.jar
    • Windows:  BigQueryJDBC\driverfile.jar

    If you specify a sub-directory, all jar files will be loaded but further sub-directories will be ignored.

  3. Click  Apply and Close to save and close the properties and return to the database driver properties.

To remove a jar file from the grid, use the toolbar button  Delete


 Audit Tab

Audit Summary

See the Common Properties page, Audit Summary section.

Using Drivers Across Multiple Instances

If you run multiple instances of PhixFlow, you must ensure that:

  • a custom database driver used by a datasource, is moved with the datasource to another PhixFlow instance
  • AND the database driver has access to the database driver jar file from the other instance.

This means all datasource objects continue to work in other instances. 

Packages cannot depend on objects in another package. For example, if a Datasource is in an application then its Database Driver can be in the application or any linked packages. However, if a Datasource is in a package then its Database Driver must be in the same package. Datasources in one package cannot reference Database Drivers in a different package. 

The easiest way to ensure Database Drivers move with the Datasource is to add the database driver to the same application or package as the model.

For adding database drivers to the same application or package as the model; see Package.

The following system database drivers cannot be moved into a package because they are installed on all PhixFlow instances:

  • MariaDB/ MySQL
  • MS SQL Server
  • Oracle

How PhixFlow Decides Which Driver to Use

PhixFlow is configured with a class path for finding drivers. This will use the first appropriate driver it finds in:

  1. The Connector Jar File Location configured for the database drivers file. 
  2. tomcat/webapps/phixflow/WEB-INF/lib
  3. tomcat/lib.

We do not recommend using the tomcat/lib directory for driver files.

Deprecated Database Drivers

For PhixFlow pre-version 10.1, the repository includes the following deprecated database drivers, for backwards compatibility. 

  • DB2
  • Debug JDBC
  • Google Big Query
  • Hadoop Hive
  • MySQL
  • Netezza
  • Sun ODBC Bridge
  • Teradata

These are drivers are read-only and do not connect to any database drivers. If you want to connect to one of these databases, we recommend you create a new database driver in which you can configure all the database drives needed.

If you want to connect a preconfigured database driver to the appropriate JDBC database driver files, download and save them to tomcat/webapps/phixflow/WEB-INF/lib. Be aware that the deprecated database drivers will be removed in a future release.

Sections on this page

Learn More