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
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.
IMPORT_DATA_TYPES
(DEPRECATED)
Property 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
orVARCHAR
-
8-bit character sets with encodings like
latin1
orISO-8859-1
-
characters being not strictly ASCII, e.g. German umlaut "Ü"
~~As a workaround, you could set the property
IMPORT_DATA_TYPES
toFROM_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' ;
MAX_TABLE_COUNT
Property 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.