Using the FME JDBC (Java Database Connectivity) Format

Liz Sanderson
Liz Sanderson
  • Updated


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.


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. 

Database Documentation
DB2 & DB2 Cloud DB2 JDBC Non-Spatial Information
Hortonworks Hadoop Hive Apache Hive Information
SAP HANA SAP Hana Non-Spatial Information
SAP HANA Spatial SAP Hana Spatial Information
Microsoft SQL Server and Azure SQL Database Non-Spatial MSSQL Non-Spatial Information
Microsoft SQL Server and Azure SQL Database Spatial* MSSQL Spatial Information
Teradata Spatial and Non-Spatial Teradata Information


Informix Information

MS Access

MS Access Information

SAP Sybase ASE

SAP Sybase ASE


Denodo Information


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. 

Database Driver
HP Vertica Community Edition v9.0.1
Neo4j neo4j-jdbc-driver-x.x.x.jar
Netezza Vx.x.x.x / nzjdbc.jar
Oracle ojdbcx.jar
PostgreSQL postgresql-x.x-xxxx.jdbcxx.jar
Sharepoint setup.jar from RSSBus
Firebird jaybird-full-x.x.x.jar (java x)
HP Vertica vertica.jar xx.xx.xxxx
MySQL supports an encrypted connection
SAP Sybase ASE SQL Anywhere
FileMaker fmjdbc.jar
Apache Ignite ignite-core-x.xx.x.jar

Was this article helpful?



Please sign in to leave a comment.