JDBC translators

The JDBC translators bridge the SQL semantics and data type differences between Teiid and a target RDBMS. Teiid has a range of specific translators that target the most popular open source and proprietary relational databases.

Usage

Usage of a JDBC source is straight-forward. Using Teiid SQL, the source can be queried as if the tables and procedures were local to the Teiid system.

If you are using a relational data source, or a data source that has a JDBC driver, and you do not find a specific translator available for that data source type, then start with the JDBC ANSI translator. The JDBC ANSI translator should enable you to perform the SQL basics. If there specific data source capabilities that are not available, you can define a custom translator that does what you need. For more information, see Translator Development.

Type conventions

UID types including UUID, GUID, or UNIQUEIDENTIFIER are typically mapped to the Teiid string type. JDBC data sources treat UID strings as non-case sensitive, but they are case-sensitive in Teiid. The source may also not support the implicit conversion to the string type, thus usage in functions expecting a string value may fail at the source.

The following table lists the execution properties that are shared by all JDBC translators.

Table 1. Execution properties — Shared by all JDBC translators
Name Description Default

DatabaseTimeZone

The time zone of the database. Used when fetching date, time, or timestamp values.

The system default time zone

DatabaseVersion

The specific database version. Used to further tune pushdown support.

The base supported version, or the version that is derived from the DatabaseMetadata.getDatabaseProductVersion string. Automatic detection requires a connection. If there are circumstances where you are getting an exception due to capabilities being unavailable (for example, because a connection is not available), then set DatabaseVersion property. Use the JDBCExecutionFactory.usesDatabaseVersion()` method to control whether your translator requires a connection to determine capabilities.

TrimStrings

true trims trailing whitespace from fixed length character strings. Note that Teiid only has a string, or varchar, type that treats trailing whitespace as meaningful.

false

RemovePushdownCharacters

Set to a regular expression to remove characters that not allowed or undesirable for the source. For example [\u0000] will remove the null character, which is problematic for sources such as PostgreSQL and Oracle. Note that this does effectively change the meaning of the affected string literals and bind values, which must be carefully considered.

UseBindVariables

true indicates that PreparedStatements should be used and that literal values in the source query should be replace with bind variables. If false only LOB values will trigger the use of PreparedStatements.

true

UseCommentsInSourceQuery

This will embed a leading comment with session/request id in the source SQL for informational purposes. Can be customized with the CommentFormat property.

false

CommentFormat

MessageFormat string to be used if UseCommentsInSourceQuery is enabled. You can set the format to one of the following values:

  • 0 - Session ID string.

  • 1 - Parent request ID string.

  • 2 - Request part ID string.

  • 3 - Execution count ID string.

  • 4 - User name string.

  • 5 - VDB name string.

  • 6 - VDB version integer.

  • 7 - Is transactional boolean.

/*teiid sessionid:\{0}, requestid:\{1}.\{2}*/

MaxPreparedInsertBatchSize

The max size of a prepared insert batch.

2048

StructRetrieval

Specify one of the following Struct retrieval modes:

  • OBJECT - getObject value returned.

  • COPY - Returned as a SerialStruct.

  • ARRAY - Returned as an array.

OBJECT

EnableDependentJoins

Allow dependent join pushdown for sources that support temporary tables (DB2, Derby, H2, HSQL 2.0+, MySQL 5.0+, Oracle, PostgreSQL, SQLServer, SQP IQ, Sybase).

false
Importer properties — Shared by all JDBC translators

When specifying the importer property, it must be prefixed with importer.. Example: importer.tableTypes

Name Description Default

catalog

See DatabaseMetaData.getTables [1]

null

schemaName

Recommended setting to import from a single schema. The schema name will be converted into an escaped pattern,overriding schemaPattern if it is also set.

null

schemaPattern

See DatabaseMetaData.getTables [1]

null

tableNamePattern

See DatabaseMetaData.getTables [1]

null

procedureNamePattern

See DatabaseMetaData.getProcedures [1]

null

tableTypes

Comma separated list — without spaces — of imported table types. See DatabaseMetaData.getTables [1]

null

excludeTables

A case-insensitive regular expression that when matched against a fully qualified table name [2] will exclude it from import. Applied after table names are retrieved. Use a negative look-ahead (?!<inclusion pattern>).* to act as an inclusion filter.

null

excludeProcedures

A case-insensitive regular expression that when matched against a fully qualified procedure name [2] will exclude it from import. Applied after procedure names are retrieved. Use a negative look-ahead (?!<inclusion pattern>).* to act as an inclusion filter.

null

importKeys

true to import primary and foreign keys.

NOTE: Foreign keys to tables that are not imported will be ignored.

true

autoCreateUniqueConstraints

true to create a unique constraint if one is not found for a foreign keys

true

importIndexes

true to import index/unique key/cardinality information

false

importApproximateIndexes

true to import approximate index information. See DatabaseMetaData.getIndexInfo [1].

WARNING: Setting to false may cause lengthy import times.

true

importProcedures

true to import procedures and procedure columns - Note that it is not always possible to import procedure result set columns due to database limitations. It is also not currently possible to import overloaded procedures.

false

importSequences

true to import sequences. Supported only for Db2, Oracle, PostgreSQL, SQL Server, and H2. A matching sequence will be imported to a 0-argument Teiid function name_nextval.

false

sequenceNamePattern

LIKE pattern string to use when importing sequences. Null or % will match all.

null

useFullSchemaName

When false, directs the importer to use only the object name as the Teiid name. It is expected that all objects will come from the same foreign schema. When true (not recommended) the Teiid name will be formed using the catalog and schema names as directed by the useCatalogName and useQualifiedName properties, and it will be allowed for objects to come from multiple foreign schema. This option does not affect the name in source property.

false (only change when importing from multiple foreign schema).

useQualifiedName

true will use name qualification for both the Teiid name and name in source as further refined by the useCatalogName and useFullSchemaName properties. Set to false to disable all qualification for both the Teiid name and the name in source, which effectively ignores the useCatalogName and useFullSchemaName properties.

WARNING: When you set this option to false, it can lead to objects with duplicate names when importing from multiple schemas, which results in an exception.

true (rarely needs changed)

useCatalogName

true will use any non-null/non-empty catalog name as part of the name in source, e.g. "catalog"."schema"."table"."column", and in the Teiid runtime name if applicable. false will not use the catalog name in either the name in source nor the Teiid runtime name. Only required to be set to false for sources such as HSQL that do not support a catalog concept, but return a non-null/non-empty catalog name in their metadata.

true (rarely needs changed)

widenUnsignedTypes

true to convert unsigned types to the next widest type. For example, SQL Server reports tinyint as an unsigned type. With this option enabled, tinyint would be imported as a short instead of a byte.

true

useIntegralTypes

true to use integral types rather than decimal when the scale is 0.

false

quoteNameInSource

false will override the default and direct Teiid to create source queries using unquoted identifiers.

true

useAnyIndexCardinality

true will use the maximum cardinality returned from DatabaseMetaData.getIndexInfo. importKeys or importIndexes needs to be enabled for this setting to have an effect. This allows for better stats gathering from sources that don’t support returning a statistical index.

false

importStatistics

true will use database dependent logic to determine the cardinality if none is determined. Not yet supported by all database types — currently only supported by Oracle and MySQL.

false

importRowIdAsBinary

true will import RowId columns as varbinary values.

false

importLargeAsLob

true will import character and binary types larger than the Teiid max as CLOB or BLOB respectively. If you experience memory issues even with the property enabled, you should use the copyLob execution property as well.

false

[1] JavaDoc for DatabaseMetaData
[2] The fully qualified name for exclusion is based upon the settings of the translator and the particulars of the database. All of the applicable name parts used by the translator settings (see useQualifiedName and useCatalogName) including catalog, schema, table will be combined as catalogName.schemaName.tableName with no quoting. For example, Oracle does not report a catalog, so the name used with default settings for comparison would be just schemaName.tableName.

Warning
 The default import settings will crawl all available metadata. This import process is time-consuming, and full metadata import is not needed in most situations. Most commonly you’ll want to limit the import by at least schemaName or schemaPattern and tableTypes.
Example: Importer settings to import only tables and views from my-schema.
SET SCHEMA ora;

IMPORT FOREIGN SCHEMA "my-schema" FROM SERVER ora INTO ora OPTIONS ("importer.tableTypes" 'TABLE,VIEW');

Or in an xml vdb:

<model ...

  <property name="importer.tableTypes" value="TABLE,VIEW"/>
  <property name="importer.schemaName" value="my-schema"/>
  ...
</model>

For more information about importer settings, see Virtual databases.

Native queries

Physical tables, functions, and procedures may optionally have native queries associated with them. No validation of the native query is performed, it is simply used in a straight-forward manner to generate the source SQL. For a physical table setting the teiid_rel:native-query extension metadata will execute the native query as an inline view in the source query. This feature should only be used against sources that support inline views. The native query is used as is and is not treated as a parameterized string. For example, on a physical table y with nameInSource="x"` and teiid_rel:native-query="select c from g", the Teiid source query "SELECT c FROM y" would generate the SQL query "SELECT c FROM (select c from g) as x". Note that the column names in the native query must match the nameInSource of the physical table columns for the resulting SQL to be valid.

For physical procedures you may also set the teiid_rel:native-query extension metadata to a desired query string with the added ability to positionally reference IN parameters . For more information, see Parameterizable native queries in Translators. The teiid_rel:non-prepared extension metadata property can be set to false to turn off parameter binding.

Be careful in setting this option, because inbound allows for SQL injection attacks if not properly validated. The native query does not need to call a stored procedure. Any SQL that returns a result set that positionally matches the result set that is expected by the physical stored procedure metadata will work. For example on a stored procedure x with teiid_rel:native-query="select c from g where c1 = $1 and c2 = `$$1"', the Teiid source query "CALL x(?)" would generate the SQL query "select c from g where c1 = ? and c2 = $1"'. Note that `? in this example will be replaced with the actual value bound to parameter 1.

Direct query procedure

This feature is turned off by default, because of the inherent security risk in allowing any command to be run against the source. To enable this feature, override the execution property called SupportsDirectQueryProcedure and set it to true. For more information, see Override execution properties in Translators.

By default, the name of the procedure that executes the queries directly is native. To change the name, override the execution property DirectQueryProcedureName.

The JDBC translator provides a procedure to execute any ad-hoc SQL query directly against the source without Teiid parsing or resolving. Since the metadata of this procedure’s results are not known to Teiid, they are returned as an object array. ARRAYTABLE can be used construct tabular output for consumption by client applications. For more information, see arraytable.

SELECT example
SELECT x.* FROM (call jdbc_source.native('select * from g1')) w,
 ARRAYTABLE(w.tuple COLUMNS "e1" integer , "e2" string) AS x
INSERT example
SELECT x.* FROM (call jdbc_source.native('insert into g1 (e1,e2) values (?, ?)', 112, 'foo')) w,
 ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x
UPDATE example
SELECT x.* FROM (call jdbc_source.native('update g1 set e2=? where e1 = ?','blah', 112)) w,
 ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x
DELETE example
SELECT x.* FROM (call jdbc_source.native('delete from g1 where e1 = ?', 112)) w,
 ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x
JCA resource adapter

The resource adapter for this translator provided through data source in WildFly, See to Admin Guide section WildFly Data Sources for configuration.

results matching ""

    No results matching ""