span8
span4
span8
span4
JDBC (Java Database Connectivity) format provides generic non-spatial access to a wide number of database formats through JDBC drivers - analagous to ODBC connectors. The advantage of the JDBC is that it is available on allplatformssupported by FME. Information on how to get started and configure the JDBC format is available in theGetting Started with JDBCdocumentation and the reader/writer documentation:JDBC format documentation.
CreateSpatialTable_2015.1.fmwis a simple workspace showing how to create a geometry column in a 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 now has a dedicatedSAP HANA Spatial Writerso this approach is no longer 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. T
ReadJDBC_2015.1.fmwshows how to read non-spatial data from one of our postgresql databases. This is no different than 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.
The workspaceWriteJDBC_2015.1.fmwshows 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 the FME 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 it available for some databases.
The following formats were previously availabe via the JDBC Reader/Writer and mentioned in the 'well supported' table below. They are now fully supported Readers/Writers and have been added here for continunity and links to the FME's official documentations.
Database | Documentation |
DB2 & DB2 Cloud | DB2 JDBC Non-Spatial 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 |
Informix Information |
MS Access |
MS Access Information |
SAP Sybase ASE |
SAP Sybase ASE |
Ingres |
Ingres Information |
Denodo |
Denodo Information |
*Azure Spatial Writing performance may be slow. Please contact Safe Software support if you are experiencing performance challenges when writing to Azure Spatial database with FME.
The following formats have been tested with the indicated driver. We will attempt to keep this up to date as we test new options. If there is a driver you are particularly interested in please let us know via oursupport webpage
Database | Driver | Table Listing | Schema Read | Read | Write | SQL Executor | ArcGIS Tabular Preview |
Hortonworks Hadoop Hive | Cloudera 2.5.6 / HiveJDBC4.jar please readKB article. |
Yes (FME2015.1+) |
Yes (FME2015.1+) | Yes (FME2015.1+) | No | ||
Hortonworks Hadoop Hive | Progress 5.1.3.000052 please readKB article. |
No | |||||
Hortonworks Hadoop Hive | Apache please readKB article. |
No | |||||
HP Vertica Community Edition v9.0.1 |
vertica-jdbc-9.0.1-7.jar |
Yes |
Yes |
Yes |
Yes |
Yes |
|
Neo4j | neo4J-jdbc-2.0.1-SNAPSHOT-jar-with-dependencies.jar | No | No | No | No | Yes | No |
Netezza | v7.2.0.0 / nzjdbc.jar | Yes (FME2015.1+) | Yes (FME2015.1+) | Yes (FME2015.1+) | No | ||
Oracle | ojdbc7.jar | Yes | Yes | Yes | Partial | Yes | |
PostgreSQL | postgresql-9.3-1102.jdbc41.jar | Yes | Yes | Yes | Yes | Yes | Yes |
Sharepoint | setup.jar from RSSBus |
Yes | Yes | Yes | No | ||
Firebird | jaybird-full-2.2.7.jar (java 6) | Yes | Yes | Yes | Partial | ||
HP Vertica | vertica.jar 07.00.0201 | Yes | Yes | ||||
MySQL | supports an encrypted connection | Yes | Yes | Yes | Yes | Yes | |
H2 | h2-1.4.187.jar | Yes | Yes | Yes | Yes | Yes | Yes |
MongoDB | Yes | Yes | Yes | No | Yes | ||
SAP Sybase ASE | SQL Anywhere | ||||||
SAP Sybase ASE | jTDS | Yes | Yes | Yes | Yes | Yes | |
FileMaker | fmjdbc.jarFileMaker ODBC/JDBC Link |
Performing spatial queries on database tables using the FeatureReader
Working with Foreign Keys: Writing Database Tables
Working with Alternatives or Versions in Smallworld
Let the Database Do the Work: Reading
Why when writing to Oracle, are the fieldnames in my table pre-fixed with Q_?
UNC Path Fails to Reference File Geodb in Reader or Joiner
Reading and writing Smallworld 3.x databases using FME
© 2020 Safe Software Inc |Legal