JDBC

Spectrum Spatial supports several JDBC data sources directly, including SQL Server, Oracle, PostGres/PostGIS, and GeoPackage. Drivers of type 3 and above are supported.

Spectrum Spatial can also access non-supported JDBC data sources, such as mySQL and DB2. These data sources will be non-spatial unless accessed as an XY table.

Spectrum Spatial treats a JDBC-based data source as a read-write data source.

To access a JDBC-based data source provider, add the database JDBC driver to both the /<spectrum root>/server/modules/spatial/lib and /<spectrum root>/server/lib directories to allow it to use the selected database.

Supported Data Types

Spectrum Spatial will read strings, dates and numbers from JDBC data sources. The table below shows how JDBC types are mapped to Spectrum Spatial types.

java.sql.Types Spectrum Spatial Data Type
BIGINT LONG_INTEGER
BINARY BINARY
BIT BOOLEAN
BLOB BINARY
BOOLEAN BOOLEAN
CHAR STRING
DATE DATE
DECIMAL DOUBLE
DOUBLE DOUBLE
FLOAT DOUBLE
INTEGER INTEGER
LONGVARBINARY BINARY
LONGVARCHAR STRING
NUMERIC DOUBLE
REAL DOUBLE
SMALLINT SHORT_INTEGER
TIME TIME
TIMESTAMP DATE_TIME
TINYINT SHORT_INTEGER
VARBINARY BINARY
VARCHAR STRING
NVARCHAR STRING
NCHAR STRING
OTHER (NVARCHAR2, NCHAR) STRING

Primary Key

The primary key of a table is always explicit and determined by the database metadata returned via JDBC.

Read-Only Columns

Columns are marked as read-only as determined by the database metadata. It marks auto-increment columns as read-only.

MI SQL Optimizations

The JDBC data source provider contains optimizations for the following MI SQL constructs:

For more information see the appendix Delegation to Data Source Providers.

Volatility

Spectrum Spatial does not support volatility in JDBC tables. Tables from this data source are not cached so Spectrum Spatial will not check to see if the metadata about the table has changed. It does, however, check to see if the named table definition has changed. If so, it will drop the named table and reload it. If it hasn’t changed, it will use the existing table and proceed with the data access operation.

There is support for volatility in Spectrum Spatial's supported JDBC data source providers.

XY Table Support

A JDBC table can be accessed as an XY table using the rules outlined in Support for XY Tables.

A table is identified as an XY table in several ways.

  • An entry exists in the MAPINFO_MAPCATALOG, the X and Y columns and coordinate system are identified. The X and Y columns will be fused together as a geometry column.
  • The XY table was created using Spectrum Spatial Manager. An entry in the MAPINFO_MAPCATALOG is not necessary. See Creating an XY Table.
  • The columns and coordinate system are explicitly defined in the named table definition.