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/app/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:
- Attribute operators (Examples)
- Between (Examples)
- IN List (Examples)
- Like (Examples)
- AND (Examples)
- OR and NOT (Examples)
- AVG, SUM, COUNT, MIN, MAX
- GROUP BY, ORDER BY
- LIMIT and OFFSET
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.