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

SAP HANA Spatial

SAP HANA Non-spatial

ngdbc.jar

https://support.sap.com/swdc

SAP HANA Spatial Reader/Writer

SAP HANA 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 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 so that we can provide limited support of pre-4.0 drivers or incompletely packaged version 4 drivers. These require that you also specify the Java Driver class that you want to load, as well as the connection string. In most cases, this field can remain empty.