The JDBC translator bridges between SQL semantic and data type difference between Teiid and a target RDBMS. Teiid has a range of specific translators that target the most popular open source and proprietary databases.
Type names:* jdbc-ansi- declares support for most SQL constructs supported by Teiid, except for row limit/offset and EXCEPT/INTERCECT. Translates source SQL into ANSI compliant syntax. This translator should be used when another more specific type is not available.
set SESSION sql_mode = 'ANSI'
Execution Properties - shared by all JDBC Translators
Name | Description | Default |
---|---|---|
DatabaseTimeZone | The time zone of the database. Used when fetchings 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 |
TrimStrings | true to trim trailing whitespace from fixed length character strings. Note that Teiid only has a string, or varchar, type that treats trailing whitespace as meaningful. | false |
UseBindVariables | true to indicate 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 /comment/ leading comment with session/request id in source SQL query for informational purposes | false |
MaxPreparedInsertBatchSize | The max size of a prepared insert batch. | 2048 |
Importer Properties - shared by all JDBC Translators
Name | Description | Default |
---|---|---|
catalog | See DatabaseMetaData.getTablesFull [1] | null |
schemaPattern | See DatabaseMetaData.getTables [1] | null |
tableNamePattern | See DatabaseMetaData.getTables [1] | null |
procedurePatternName | 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 Teiid table name 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 Teiid procedure name 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 |
useFullSchemaName | When false, directs the importer to drop the source catalog/schema from the Teiid object name, so that the Teiid fully qualified name will be in the form of <model name>.<table name> - Note: when false this may lead to objects with duplicate names when importing from multiple schemas, which results in an exception. This option does not affect the name in source property. | true |
importKeys | true to import primary and foreign keys | true |
importIndexes | true to import index/unique key/cardinality information | true |
importApproximateIndexes | true to import approximate index information. See DatabaseMetaData.getIndexInfo [1] | 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. | true |
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 |
quoteNameInSource | false will override the default and direct Teiid to create source queries using unquoted identifiers. | true |
useProcedureSpecificName | true will allow the import of overloaded procedures (which will normally result in a duplicate procedure error) by using the unique procedure specific name as the Teiid name. This option will only work with JDBC 4.0 compatible drivers that report specific names. | false |
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 useFullSchemaName is also true. false will not use the catalog name in either the name in source or the Teiid runtime name. Should be set to false for sources that do not fully support a catalog concept, but return a non-null catalog name in their metadata - such as HSQL. | true |
useQualifiedName | true will use name qualification for both the Teiid name and name in source as dictated 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. Note: when false this may lead to objects with duplicate names when importing from multiple schemas, which results in an exception. | true |
[1] JavaDoc for [DatabaseMetaData|http://java.sun.com/javase/6/docs/api/java/sql/DatabaseMetaData.html]
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 schemaPattern and tableTypes.
This feature is turned off by default because of the security risk this exposes to execute any command against the source. To enable this feature, override the translator property called "SupportsNativeQueries" to true. Look for [Override Execution Properties|#Override Execution Properties] above.
JDBC translator also provides a procedure with name native that gives ability to execute any ad-hoc native SQL command that is specific to an underlying source directly against the source without any Teiid parsing or resolving. The metadata of this procedure's execution results are not known to the Teiid, and they are returned as object array. User can use [ARRAYTABLE|ARRAYTABLE] construct to build a tabular output for consumption by client applications. Example execution can be like below
SELECT x.* FROM (call pm1.native('select * from g1')) w, ARRAYTABLE(w.tuple COLUMNS "e1" integer , "e2" string) AS x
SELECT x.* FROM (call pm1.native('insert into g1 (e1,e2) values (?, ?)', 112, 'foo')) w, ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x
SELECT x.* FROM (call pm1.native('update g1 set e2=? where e1 = ?','blah', 112)) w, ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x
SELECT x.* FROM (call pm1.native('delete from g1 where e1 = ?', 112)) w, ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x
By default the name of the procedure that executes the queries directly is called native, however user can set override execution property vdb.xml file to change it. |
The resource adapter for this translator provided through data source in JBoss AS, Refer to Admin Guide for "JDBC Data Sources" configuration section.