Using the FME JDBC (Java Database Connectivity) Format

Introduction

JDBC (Java Database Connectivity) format provides generic non-spatial access to a wide number of database formats through JDBC drivers - analogous to ODBC connectors. The advantage of the JDBC is that it is available on all platforms. Information on how to get started and configure the JDBC format is available in the Getting Started with JDBC documentation and the reader/writer documentation: JDBC format documentation.
 

Examples

Creating Spatial Tables via a JDBC Connection

CreateSpatialTable.fmw is a simple workspace showing how to create a geometry column in an SAP HANA database. This is simply an example of working with a spatial data column when FME's not able to do it through a dedicated writer. NOTE: FME has a dedicated SAP HANA Spatial Writer, so this approach isn’t necessary for this format, but this example is applicable to other JDBC formats where we don't support the Spatial format natively in FME.
In this example, the connection information is missing, but what is important are the SQL statements within the SQLCreator. It shows how to create the table and then add a spatial column to it. The FME_SQL_DELIMITER statement is required to define the delimiter that will be used to separate the individual SQL statements. The leading hyphen on the "DROP TABLE ..." statement instructs FME to ignore any errors which are returned if this statement fails due to the table not already existing. 

Reading Spatial and Non-Spatial Data via a JDBC Connection

ReadJDBC.fmw shows how to read non-spatial data from one of our training PostgreSQL databases. This is the same as using any of our regular database readers or writers.



To get at the PostGIS geometry, it is necessary to work with the geometry column and the various PostGIS functions such as ST_AsText() or ST_AsBinary(). The GeometryReplacer can be used to take the WKT or WKB and replace the non-spatial features with a spatial feature.

Writing Spatial Data via a JDBC Connection

The workspace WriteJDBC.fmw shows how to write to a JDBC SAP HANA database in one of two ways. When writing spatial data, it is necessary to extract it, using a GeometryExtractor, into Well Known Text or Well Known Binary, depending on how the database is expecting to store the geometry. Then it is possible to write via the JDBC writer, which allows for easier attribute handling and provides full support for inserting new rows, updating, or deleting existing rows. Some formats (see table below), writing via a format-specific writer is still not supported. So an alternative method is shown using the SQLExecutor to Insert records. This has shown to be considerably slower but is all that is available for some databases. 

Supported Formats with FME Documentation

The following formats have their own reader/writer within FME. They can still be added via a JDBC connection. Note that depending on your version of FME, these formats may or may not be available. Please see FME Deprecations if you cannot find a format in FME. 

User Tested Formats

These are user-tested formats that Safe Software Support is aware of that can be used with the JDBC reader/writer. Safe Software has not tested this list and the functionality and driver available may be out of date.