Versions Compared

Old Version 1

changes.mady.by.user Fiona Sargeant

Saved on

compared with

New Version Current

changes.mady.by.user Anthony George

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Is there a connection between database collectors/datasource/database exporters.

Datasources and Database Collectors
Basic Settings -> Driver Class
Datasource Instances -> URL -> jdbc:database://localhost/phixflow_instance_name

Driver Class Properties

...

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

Overview

PhixFlow requires database drivers in order to communicate with a database. These are usually JDBC drivers that you download as  one or more Jar files

Driver names have the form com.mysql.jdbc.Driver

When you install PhixFlow, a set of driver jar files are installed in the PhixFlow (system somewhere) and the Driver Classes are automatically configured with the install. You cannot update or change these Driver Classes, listed in the repository. In particular, Oracle, Microsoft SQL Server, MariaDB are important not to change because PhixFlow can be installed in these database. So these are maintained by Us.

You can create your own user maintained/custom

They should be flagged as sys or not. Syst maintained ones should show up with details saying you cannot change it.

...

You may need to support the same database with different versions may need different versions of drivers.

You may also have a brand new database drivers.

Admins should know

- driver class name
- which single jar (usually) or set of jars are needed - depends on Database person
- The database manufacturers doc will tell them.
- There may be problems with compatibility - check DB doc or download

Class path: our normal class pth is all of the lib and class dir files - these are the set of jar files that tomcat has and on top that everything in lib and on top of that everything in class. Their custom files are a layer on top.

They have to get the jar files onto the tomcat server - no specific location
NOT in tomcat (lib or class)
Additional files stored separately - to avoid them being ovewritten best if stored outside tomcat or phixflow. No system directory.

You can connect PhixFlow to (any) databases to access its data in analysis models. However, you must load and configure the database drivers. To make this easy, PhixFlow has Driver Classes for the following databases:

...

?? Can we give any advice for finding the relevant one. The IBM page for DB2 had a load of different downloads with different libraries and I had no idea which would be required.??

Users could not do their own before.
This is more easy to maintain.

...

### Driver Class File

...

Look for example for excel exporters and for the where we said they were berfore.

Connector Jar File Location

# security issues

Jar file is compiled code that we cannot control that will run inside the app with no checks on what it can see.
That sets of security bells.
This could connect to a jar file tht could do anyting.
We really only want admins to do this.
We don't want any phixFlow.

An admin on the server is the only one who can install the jar file.

File collectors let users upload to a file collector - system config file upload directory.

We need to have a specific place for driver class files.
Do driver class files need a jira need something to restrict where it will look for the Jar files.

Driver Classes have their own privilege??? - should be restricted to admins and import export.... but may not be at the moment

For application building, once you have a custom driver, you need the same driver on all instances - so you need to export/import or you will lose the refences.

# How To

1. For the database you want to connect to download the appropriate driver. JDBC or ODBC
2. Upload to PhixFlow?
3. Set the Driver Class properties
4. Set the Driver Class File

The PhixFlow application communicates with:

  • the database on which it is installed.
  • an external database from which it needs to load data.
    This is configured using a Datasource in an analysis model.

To communicate with a database, PhixFlow needs:

  • The appropriate database driver jar files in its Driver Class File Directory.
    This location is set directly in the PhixFlow database, and is displayed in System Configuration.
  • A Driver Class for the database. One driver class can have multiple Driver Class Files.

There are three databases on which PhixFlow can be installed:

  • MariaDB
  • Microsoft (MS) SQL Server
  • Oracle.

For each of these, PhixFlow is preconfigured with:

  • database driver jar files in tomcat/webapps/phixflow/WEB-INF/lib 
  • driver classes that use the database driver jar files.


Panel
borderColor#7da054
titleColorwhite
titleBGColor#7da054
borderStylesolid
titleSections on this page

Table of Contents
indent12px
stylenone



PhixFlow automatically uses the appropriate driver class to communicate with its own database. You can use these driver classes in a datasource. However, you cannot update their properties or see their driver class files.

Tip

You can use the MariaDB driver class to connect to a MySQL database.

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. 

You may need to configure additional database drivers to support:

  • other types of database
  • different versions a database.

How to Configure a Driver Class

  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. Save the files to the Driver Class File Directory. You need permission to add system files to do this.
    • If it has been set in the PhixFlow database, it will be listed in the System Configuration.
    • If it has not been set:
      Insert excerpt
      _set_driver_dir
      _set_driver_dir
      nopaneltrue
  4. In the PhixFlow application, for each type of database, create one Driver Class; see Adding a Driver Class, below.
  5. In the driver class properties → Driver Class File section, add all the JDBC driver files that the database requires; see Driver Classes and Driver Class File Properties, below.
  6. For each driver class file, specify the full path to the file, which is stored in the Driver Class File Directory, or a sub-directory of it.

Adding or Changing a Driver Class 
Anchor
_add
_add

To create a driver class, in the full repository scroll down to the

Insert excerpt
_driver_class_repo
_driver_class_repo
nopaneltrue
 section. 

To add a new driver, right-click Driver Classes and select 

Insert excerpt
_add
_add
nopaneltrue
. PhixFlow opens a property tab where you can enter the details for the new driver.

To open an existing driver class, in the repository click on its name.

Tip

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

Driver Classes and Driver Class File Properties 
Anchor
properties
properties

The driver classes configured in the repository populate the drop-down list in

Insert excerpt
_datasource
_datasource
nopaneltrue
 properties Basic Settings → Driver Class. 

Insert excerpt
_standard_settings
_standard_settings
nopaneltrue

Basic Settings

FieldDescription

Name

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

Enter the driver class 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.

Insert excerpt
_check_box_untick
_check_box_untick
nopaneltrue
 indicates this driver class is custom defined for your PhixFlow instance. You can change it's configuration.

Insert excerpt
_check_box_ticked
_check_box_ticked
nopaneltrue
 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.

Driver Class File

This section has a toolbar with standard buttons. 

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

  1. Click
    Insert excerpt
    _add
    _add
    nopaneltrue
     to open a the properties for a new Driver Class File Location.
    (Driver Class 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 

    Insert excerpt
    _finish
    _finish
    nopaneltrue
     to save and close the properties and return to the driver class properties.

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

Insert excerpt
_delete
_delete
nopaneltrue
.

Using Drivers Across Multiple Instances

If you run multiple instances of PhixFlow, you must ensure that any custom driver classes you create:

  • are exported and then imported to other instances
  • have access to the database driver jar file from the other instances.

This ensures that all datasource objects that use the database driver continue to work on the other instances. 

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 driver classes file. 
  2. tomcat/webapps/phixflow/WEB-INF/lib
  3. tomcat/lib.
Note

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

Deprecated Driver Classes

The repository includes the following, deprecated driver classes, for backwards compatibility. 

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

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 driver class in which you can configure all the database drives needed.

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