Virtual Schema Common Module for JDBC-based Data Access

Overview

This module contains common parts for Virtual Schema Adapters that use JDBC to access the remote data source.

This module is part of a larger project called Virtual Schemas covering JDBC based dialects as well as others, see complete list of dialects.

Customer Support

This is an open source project which is officially supported by Exasol. For any question, you can contact our support team.

Information for Users

• Changelog
• Dependencies

Adapter Properties for JDBC-Based Virtual Schemas

Besides the common properties for all Virtual Schemas there is a property specific to JDBC-based Virtual Schemas.

Property IMPORT_DATA_TYPES (DEPRECATED)

The IMPORT_DATA_TYPES property is DEPRECATED. The only supported option is now the default, EXASOL_CALCULATED.

Supported values:

Value	Description
EXASOL_CALCULATED (default)	Use data types calculated by Exasol database from the query and connection metadata.
FROM_RESULT_SET (deprecated)	DEPRECATED: Infer data types from values of the result set.

The algorithm behind EXASOL_CALCULATED was introduced with VSCJDBC version 10.0.0 and is only available with from Exasol 7.1.14 on in the 7.1.x series and from Exasol 8.6.0 on and above.

With the new algorithm compatibility problems with the source database could happen under the following circumstances:

• data type CHAR or VARCHAR

• 8-bit character sets with encodings like latin1 or ISO-8859-1

• characters being not strictly ASCII, e.g. German umlaut "Ü"

  ~~As a workaround, you could set the property IMPORT_DATA_TYPES to FROM_RESULT_SET to switch to the previous algorithm. VSCJDBC will then infer encoding UTF-8 from the data values in the result set which allows Exasol database to accept these values. Note that there is an extra database connection and metadata query required for this mechanism, so it is slightly less efficient than the new one. ~~

Since version 12.0.0 of virtual-schema-common-jdbc the FROM_RESULT_SET option for the IMPORT_DATA_TYPES property is DEPRECATED. We now always import CHAR or VARCHAR data types with character set UTF-8 which solves the compatibility issues described above in a consistent fault-proof way.

Here is an example:

CREATE VIRTUAL SCHEMA <virtual schema name>
    USING SCHEMA_FOR_VS_SCRIPT.<adapter>
    WITH CONNECTION_NAME = '<connection>'
    IMPORT_DATA_TYPES = 'EXASOL_CALCULATED' ;

Property MAX_TABLE_COUNT

Supported values: positive integers; default 1000

When creating or refreshing a virtual schema, reading the table metadata can take a long time. Additionally, the collected metadata must fit into a single internal data packet. To avoid unpleasant surprises in this area, VSCJDBC limits the acceptable amount of mapped tables and will generate an E-VSCJDBC-42 error when the limit is exceeded.

The limit can be changed at creation time or using ALTER VIRTUAL SCHEMA after creation; the changed value will then take effect on the next REFRESH call.

ALTER VIRTUAL SCHEMA <virtual schema name>
   SET MAX_TABLE_COUNT = '3000';

Please note that time required to generate or refresh table metadata will scale with the number of tables, and the internal packet size limit will still be in effect.

Information for Developers

• Virtual Schema API Documentation
• Developing and Testing an SQL Dialect
• Step-by-step guide to writing your own SQL dialect