Getting Started with JDBC

JDBC provides generic non-spatial access to a wide number of database formats. All that is required is a driver and a connection string.

The driver JDBC version is recommended to be 4.0 or 4.1.

The primary source for drivers is the database vendor. However, commercial and open-source drivers are available from a number of locations. A list of third-party commercial JDBC drivers can be found at http://www.databasedrivers.com/jdbc/.

A single JDBC driver works for both 32-bit FME and 64-bit FME.

Installing the JDBC Driver

Place the JDBC driver .jar file in one of the locations that are added to the Java CLASSPATH when the Java Virtual Machine is loaded. These locations are dependent on platform, as follows:

Mac

/Users/<username>/Library/Application Support/FME/Plugins/Java

/Library/FME/<FME_version>/plugins

Linux

~/.fme/Plugins/Java

<FMEServerSystemShare> (see FME Server Web UI Resources)

/opt/<FME_version>/Plugins

Windows

<User_Documents>/FME/Plugins/Java

<FMEServerSystemShare>/Resources/engine/Plugins/Java (see FME Server Web UI Resources)

<FME_install_directory>/plugins

Downloads

Database

Driver

Download Location

Format Documentation

Microsdoft Sharepoint

setup.jar from RSSBus

See http://www.databasedrivers.com/jdbc/

Microsoft SharePoint List Reader/Writer
Netezza

v7.2.0.0 / nzjdbc.jar

See http://www.databasedrivers.com/jdbc/

IBM Netezza Spatial and Non-Spatial Reader/Writer
Oracle

ojdbc7.jar

See https://www.oracle.com/technetwork/database/application-development/jdbc/downloads/index.html

Oracle Non-Spatial Reader/Writer

Oracle Spatial Object Reader/Writer

PostgreSQL

postgresql-9.3-1102.jdbc41.jar

See http://www.databasedrivers.com/jdbc/

PostgreSQL Reader/Writer

SAP HANA Spatial

SAP HANA Non-spatial

ngdbc.jar

https://support.sap.com/swdc

SAP HANA and Cloud Platform HANA Service Spatial Reader/Writer

SAP HANA and Cloud Platform HANA Service Non-Spatial Reader/Writer

Teradata Spatial

Teradata Non-spatial

terajdbc4.jar

tdgssconfig.jar

http://downloads.teradata.com/download/connectivity/jdbc-driver Teradata Spatial Reader/Writer

Teradata Non-Spatial Reader/Writer

Notes:

  • To avoid connection errors, we recommend that you close FME Workbench before adding new .jar files.
  • Some JDBC drivers are bundled with additional libraries. Depending on how these libraries were packaged, they may override the libraries that came with FME. If the libraries are incompatible, they may cause instabilities within FME.

Getting Connected

JDBC connection strings are usually documented for each database driver. For example:

Using the DB2 JDBC driver, the connection string looks something like:

jdbc:db2://bt-db-db2_97:50000/TESTDB

Using the driver available from Oracle, the connection string looks something like:

jdbc:oracle:thin:@//<host>:<port>/<service name>

Using the PostgreSQL JDBC driver available from PostgreSQL, looks something like:

jdbc:postgresql://<host>/<database name>

For accessing SAP HANA, the connection string looks something like:

jdbc:sap://54.221.120.100:30015

For accessing SQL Server from Linux, the connection string looks something like:

jdbc:sqlserver://<hostname>;databaseName=<databasename>

In FME Workbench, you can set up a Database Connection entry that uses the required connection string. Select Tools > FME Options > Database Connections.

Once this connection has been successfully tested, it can be saved and used in any readers, writers, or transformers that want to connect using that particular connection string. For example:

The Java Driver Class is provided to support drivers that do not register themselves (pre-4.0 drivers or incompletely packaged version 4 drivers) and to ensure that the correct driver class is loaded to handle the connection string.