A Translator is typically paired with a particular JCA resource adapter. In instances where pooling, environment dependent configuration management, advanced security handling, etc. are not needed, then a JCA resource adapter is not needed. The configuration of JCA ConnectionFactories for needed resource adapters is not part of this guide, please see the Teiid Administrator Guide and the kit examples for configuring resource adapters for use in JBossAS.

Translators can have a number of configurable properties. These are broken down into execution properties, which determine aspects of how data is retrieved, and import settings, which determine what metadata is read for import.

The execution properties for a translator typically have reasonable defaults. For specific translator types, e.g. the Derby translator, base execution properties are already tuned to match the source. In most cases the user will not need to adjust their values.

Base Execution Properties - shared by all translators

Only a subset of the supports metadata can be set through execution properties. If more control is needed, please consult the Developer's Guide.

There are no base importer settings.

Override Execution Properties

For all the translators to override Execution Properties can be configured in the vdb.xml file.

<translator type="oracle-override" name="oracle">
     <property value="RequiresCriteria" name="true"/>
</translator>

The above XML fragment is overriding the oracle translator and altering the behavior of RequiresCriteria property to true. Note that the modified translator is only available in the scope of this VDB.

Parameterizable Native Queries

In some situations the teiid_rel:native-query property and native procedures accept parameterizable strings that can positionally reference IN parameters. A parameter reference has the form $integer, i.e. $1 Note that 1 based indexing is used and that only IN parameters may be referenced. Dollar-sign integer is therefore reserved, but may be escaped with another $, i.e. $$1. The value will be bound as a prepared value or a literal is a source specific manner. The native query must return a result set that matches the expectation of the calling procedure.

For example the native-query "select c from g where c1 = $1 and c2 = '$$1'" results in a JDBC source query of "select c from g where c1 = ? and c2 = '$1'", where ? will be replaced with the actual value bound to parameter 1.

File Translator

The file translator, known by the type name file, exposes stored procedures to leverage file system resources exposed by the file resource adapter. It will commonly be used with the TEXTTABLE or XMLTABLE table functions to use CSV or XML formatted data.

Execution Properties

Usage

Retrieve all files as BLOBs with an optional extension at the given path.

call getFiles('path/*.ext')

If the extension path is specified, then it will filter all of the file in the directory referenced by the base path. If the extension pattern is not specified and the path is a directory, then all files in the directory will be returned. Otherwise the single file referenced will be returned. If the path doesn't exist, then no results will be returned if ExceptionIfFileNotFound is false, otherwise an exception will be raised.

Retrieve all files as CLOB(s) with the an optional extension at the given path.

call getTextFiles('path/*.ext')

All the same files a getFiles will be retrieved, the only difference is that the results will be CLOB values using the encoding execution property as the character set.

Save the CLOB, BLOB, or XML value to given path

call saveFile('path', value)

The path should reference a new file location or an existing file to overwrite completely.

Native queries Native or direct query execution is not supported on the File Translator.

JCA Resource Adapter

The resource adapter for this translator provided through "File Data Source", Refer to Admin Guide for configuration information.

JDBC Translator

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.

• jdbc-simple- same as jdbc-ansi, except disables support for function, UNION, and aggregate pushdown.

• access- for use with Microsoft Access 2003 or later.

• db2- for use with DB2 8 or later and DB2 for i 5.4 or later.
  DB2 specific execution properties:
  • DB2ForI- indicates that the the DB2 instance is DB2 for i. Defaults to false.

• derby- for use with Derby 10.1 or later.

• excel-odbc- for use with Excel 2003 or later via the JDBC-ODBC bridge.

• greenplum- for use with the Greenplum database.

• h2- for use with H2 version 1.1 or later.

• hive- For use with Hive database based on Hadoop. Hive is a data warehousing infrastructure based on the Hadoop. Hadoop provides massive scale out and fault tolerance capabilities for data storage and processing (using the map-reduce programming paradigm) on commodity hardware. Hive has limited support for data types as it supports integer variants, boolean, float, double and string. It is does not have native support for time based types, xml or LOBs. These limitations are reflected in the translator capabilities. The view table can use these types, however the transformation would need to specify the necessary transformations. Note that in those situations, the evaluations will be done in Teiid engine. Another limitation Hive has is, it only supports EQUI join, so using any other joins types on its source tables will result in in-efficient queries. Currently there is no tooling support for metadata import from Hive in Designer. To write criteria based on partitioned columns, they can be modelled on source table, but do not include in selection columns.

• hsql- for use with HSQLDB 1.7 or later.

• ingres- for use with Ingres 2006 or later.

• ingres93- for use with Ingres 9.3 or later.

• intersystems-cache- for use with Intersystems Cache Object database (only relational aspect of it)

• informix- for use with any Informix version.

• metamatrix- for use with MetaMatrix 5.5.0 or later.

• modeshape- for use with Modeshape 2.2.1 or later. The PATH, NAME, LOCALNODENAME, DEPTH, and SCORE functions should be accessed as pseudo-columns, e.g. "nt:base"."jcr:path". Teiid UFDs (prefixed by JCR_) are available for CONTIANS, ISCHILDNODE, ISDESCENDENT, ISSAMENODE, REFERENCE - see the JCRFunctions.xmi. If a selector name is needed in a JCR function, you should use the pseudo-column "jcr:path", e.g. JCR_ISCHILDNODE(foo.jcr_path, 'x/y') would become ISCHILDNODE(foo, 'x/y') in the ModeShape query. An additional pseudo-column "mode:properties" should be imported by setting the ModeShape JDBC connection property teiidsupport=true. The column "mode:properties" should be used by the JCR_REFERENCE and other functions that expect a .* selector name, e.g. JCR_REFERENCE(nt_base.jcr_properties) would become REFERENCE("nt:base".*) in the ModeShape query.

• mysql/mysql5- for use with MySQL version 4.x and 5 or later respectively.
  The MySQL Translators expect the database or session to be using ANSI mode. If the database is not using ANSI mode, an initialization query should be used on the pool to set ANSI mode:

  set SESSION sql_mode = 'ANSI'

• netezza - for use with any Netezza version.

• oracle- for use with Oracle 9i or later. Sequences may be used with the Oracle translator. A sequence may be modelled as a table with a name in source of DUAL and columns with the name in source set to <sequence name>.[nextval|currentval]. You can use a sequence as the default value for insert columns by setting the column to autoincrement and the name in source to <element name>:SEQUENCE=<sequence name>.<sequence value>. A rownum column can also added to any Oracle physical table to support the rownum pseudo-column. A rownum colum should have a name in source of rownum. These rownum columns do not have the same semantics as the Oracle rownum construct so care must be taken in their usage.
  Oracle specific execution properties:
  • OracleSuppliedDriver- indicates that the Oracle supplied driver (typically prefixed by ojdbc) is being used. Defaults to true. Set to false when using DataDirect or other Oracle JDBC drivers.

• postgresql- for use with 8.0 or later clients and 7.1 or later server.

• sqlserver- for use with SQL Server 2000 or later. A SQL Server JDBC driver version 2.0 or later (or compatible e.g. JTDS 1.2 or later) should be used.  SQL Server specific execution properties:
  • JtdsDriver- indicates that the open source JTDS driver is being used. Defaults to false.

• sybase- for use with Sybase version 12.5 or later.  Sybase specific execution properties:
  • JtdsDriver- indicates that the open source JTDS driver is being used. Defaults to false.

• teiid- for use with Teiid 6.0 or later.

• teradata- for use with Teradata V2R5.1 or later.

Execution Properties - shared by all JDBC Translators

Importer Properties - shared by all JDBC Translators

[1] JavaDoc for DatabaseMetaData

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.

Example importer settings to only import tables and views from my-schema.

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

Usage

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

Native Queries

Both physical tables 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 - see Parameterizable Native Queries.  The teiid_rel:non-prepared extension metadata property may be set to false to turn off parameter binding.  Note this option should be used with caution as inbound may allow 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 positionally matching the result set 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.

Native Procedure

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 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 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.

JCA Resource Adapter

The resource adapter for this translator provided through data source in JBoss AS, Refer to Admin Guide for "JDBC Data Sources" configuration section.

LDAP Translator

The LDAP translator, known by the type name ldap, exposes an LDAP directory tree relationally with pushdown support for filtering via criteria. This is typically coupled with the LDAP resource adapter.

Execution Properties

There are no import settings for the ldap translator; it also does not provide metadata.

Metadata Directives

String columns with a default value of "multivalued-concat" will concatinate all attribute values together in alphabetical order using a ? delimiter. If a multivalued attribute does not have a default value of "multivalued-concat", then any value may be returned.

Native Queries

LDAP procedures may optionally have native queries associated with them - see Parameterizable Native Queries. The operation prefix (select;, insert;, update;, delete; - see the native procedure logic below for more) must be present in the native-query, but it will not be issued as part of the query to the source.

CREATE FOREIGN PROCEDURE proc (arg1 integer, arg2 string) OPTIONS ("teiid_rel:native-query" 'search;context-name=corporate;filter=(&(objectCategory=person)(objectClass=user)(!cn=$2));count-limit=5;timeout=$1;search-scope=ONELEVEL_SCOPE;attributes=uid,cn') returns (col1 string, col2 string);

Parameter values have reserved characters escaped, but are otherwise directly substituted into the query.

Native Procedure

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 above.

LDAP translator provides a procedure with name native that gives ability to execute any ad-hoc native LDAP queries 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 construct to build tabular output for consumption by client applications. Since there is no known direct query language for LDAP, Teiid exposes this procedure with a simple query structure as below

Search

SELECT x.* FROM (call pm1.native('search;context-name=corporate;filter=(objectClass=*);count-limit=5;timeout=6;search-scope=ONELEVEL_SCOPE;attributes=uid,cn')) w,
 ARRAYTABLE(w.tuple COLUMNS "uid" string , "cn" string) AS x

from the above code, the "search" keyword followed by below properties. Each property must be delimited by semi-colon (;) If a property contains a semi-colon (;), it should be escaped by another semi-colon - see also Parameterizable Native Queries and the native-query procedure example above.

Delete

SELECT x.* FROM (call pm1.native('delete;uid=doe,ou=people,o=teiid.org')) w,
 ARRAYTABLE(w.tuple COLUMNS "updatecount" integer) AS x

form the above code, the "delete" keyword followed the "DN" string. All the string contents after the "delete;" used as DN.

Create or Update

SELECT x.* FROM
 (call pm1.native('create;uid=doe,ou=people,o=teiid.org;attributes=one,two,three', 'one', 2, 3.0)) w,
 ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x

form the above code, the "create" keyword followed the "DN" string. All the string contents after the "create;" is used as DN. It also takes one property called "attributes" which is comma separated list of attributes. The values for each attribute is specified as separate argument to the "native" procedure.

Update is similar to create

SELECT x.* FROM
 (call pm1.native('update;uid=doe,ou=people,o=teiid.org;attributes=one,two,three', 'one', 2, 3.0)) 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.

JCA Resource Adapter

The resource adapter for this translator provided through "LDAP Data Source", Refer to Admin Guide for configuration.

Loopback Translator

The Loopback translator, known by the type name loopback, provides a quick testing solution. It supports all SQL constructs and returns default results, with configurable behavior.

Execution Properties

There are no import settings for the Loopback translator; it also does not provide metadata - it should be used as a testing stub.

JCA Resource Adapter

The source connection is required for this translator

Salesforce Translator

The Salesforce translator, known by the type name salesforce supports the SELECT, DELETE, INSERT and UPDATE operations against a Salesforce.com account. It is designed for use with the Teiid Salesforce resource adapter.

Execution Properties

The Salesforce translator can import metadata, but does not currently have import settings.

SQL Processing

Salesforce does not provide the same set of functionality as a relational database. For example, Salesforce does not support arbitrary joins between tables. However, working in combination with the Teiid Query Planner, the Salesforce connector supports nearly all of the SQL syntax supported by the Teiid.

The Salesforce Connector executes SQL commands by “pushing down” the command to Salesforce whenever possible, based on the supported capabilities. Teiid will automatically provide additional database functionality when the Salesforce Connector does not explicitly provide support for a given SQL construct. In these cases, the SQL construct cannot be “pushed down” to the data source, so it will be evaluated in Teiid, in order to ensure that the operation is performed.

In cases where certain SQL capabilities cannot be pushed down to Salesforce, Teiid will push down the capabilities that are supported, and fetch a set of data from Salesforce. Then, Teiid will evaluate the additional capabilities, creating a subset of the original data set. Finally, Teiid will pass the result to the client.

SELECT sum(Reports) FROM Supervisor where Division = 'customer support';

Neither Salesforce nor the Salesforce Connector support the sum() scalar function, but they do support CompareCriteriaEquals, so the query that is passed to Salesforce by the connector will be transformed to this query.

SELECT Reports FROM Supervisor where Division = 'customer support';

The sum() scalar function will be applied by the Teiid Query Engine to the result set returned by the connector.

In some cases multiple calls to the Salesforce application will be made to support the SQL passed to the connector.

DELETE From Case WHERE Status = 'Closed';

The API in Salesforce to delete objects only supports deleting by ID. In order to accomplish this the Salesforce connector will first execute a query to get the IDs of the correct objects, and then delete those objects. So the above DELETE command will result in the following two commands.

SELECT ID From Case WHERE Status = 'Closed';
DELETE From Case where ID IN (<result of query>);

*The Salesforce API DELETE call is not expressed in SQL, but the above is an SQL equivalent expression.

It's useful to be aware of unsupported capabilities, in order to avoid fetching large data sets from Salesforce and making you queries as performant as possible. See all Supported Capabilities.

Selecting from Multi-Select Picklists

A multi-select picklist is a field type in Salesforce that can contain multiple values in a single field. Query criteria operators for fields of this type in SOQL are limited to EQ, NE, includes and excludes. The full Salesforce documentation for selecting from mullti-select picklists can be found at the following link. Querying Mulit-select Picklists

Teiid SQL does not support the includes or excludes operators, but the Salesforce connector provides user defined function definitions for these operators that provided equivalent functionality for fields of type multi-select. The definition for the functions is:

boolean includes(Column column, String param)
boolean excludes(Column column, String param)

For example, take a single multi-select picklist column called Status that contains all of these values.

• current

• working

• critical

For that column, all of the below are valid queries:

SELECT * FROM Issue WHERE true = includes (Status, 'current, working' );
SELECT * FROM Issue WHERE true = excludes (Status, 'current, working' );
SELECT * FROM Issue WHERE true = includes (Status, 'current;working, critical' );

EQ and NE criteria will pass to Salesforce as supplied. For example, these queries will not be modified by the connector.

SELECT * FROM Issue WHERE Status = 'current';
SELECT * FROM Issue WHERE Status = 'current;critical';
SELECT * FROM Issue WHERE Status != 'current;working';

Selecting All Objects

The Salesforce connector supports the calling the queryAll operation from the Salesforce API. The queryAll operation is equivalent to the query operation with the exception that it returns data about all current and deleted objects in the system.

The connector determines if it will call the query or queryAll operation via reference to the isDeleted property present on each Salesforce object, and modeled as a column on each table generated by the importer. By default this value is set to False when the model is generated and thus the connector calls query. Users are free to change the value in the model to True, changing the default behavior of the connector to be queryAll.

The behavior is different if isDeleted is used as a parameter in the query. If the isDeleted column is used as a parameter in the query, and the value is 'true' the connector will call queryAll.

select * from Contact where isDeleted = true;

If the isDeleted column is used as a parameter in the query, and the value is 'false' the connector perform the default behavior will call query.

select * from Contact where isDeleted = false;

Selecting Updated Objects

If the option is selected when importing metadata from Salesforce, a GetUpdated procedure is generated in the model with the following structure:

GetUpdated (ObjectName IN string,
	StartDate IN datetime,
	EndDate IN datetime,
	LatestDateCovered OUT datetime)
returns
	ID string

See the description of the GetUpdated operation in the Salesforce documentation for usage details.

Selecting Deleted Objects

If the option is selected when importing metadata from Salesforce, a GetDeleted procedure is generated in the model with the following structure:

GetDeleted (ObjectName IN string,
	StartDate IN datetime,
	EndDate IN datetime,
	EarliestDateAvailable OUT datetime,
	LatestDateCovered OUT datetime)
returns
	ID string,
	DeletedDate datetime

See the description of the GetDeleted operation in the Salesforce documentation for usage details.

Relationship Queries

Salesforce does not support joins like a relational database, but it does have support for queries that include parent-to-child or child-to-parent relationships between objects. These are termed Relationship Queries. The SalesForce connector supports Relationship Queries through Outer Join syntax.

SELECT Account.name, Contact.Name from Contact LEFT OUTER JOIN Account
on Contact.Accountid = Account.id

This query shows the correct syntax to query a SalesForce model with to produce a relationship query from child to parent. It resolves to the following query to SalesForce.

SELECT Contact.Account.Name, Contact.Name FROM Contact

select Contact.Name, Account.Name from Account Left outer Join Contact
on Contact.Accountid = Account.id

This query shows the correct syntax to query a SalesForce model with to produce a relationship query from parent to child. It resolves to the following query to SalesForce.

SELECT Account.Name, (SELECT Contact.Name FROM
Account.Contacts) FROM Account

See the description of the Relationship Queries operation in the SalesForce documentation for limitations.

Supported Capabilities

The following are the the connector capabilities supported by the Salesforce Connector. These SQL constructs will be pushed down to Salesforce.

• SELECT command

• INSERT Command

• UPDATE Command

• DELETE Command

• CompareCriteriaEquals

• InCriteria

• LikeCriteria - Supported for String fields only.

• RowLimit

• AggregatesCountStar

• NotCriteria

• OrCriteria

• CompareCriteriaOrdered

• OuterJoins with join criteria KEY

Native Queries

Salesforce procedures may optionally have native queries associated with them - see Parameterizable Native Queries. The operation prefix (select;, insert;, update;, delete; - see the native procedure logic below for more) must be present in the native-query, but it will not be issued as part of the query to the source.

CREATE FOREIGN PROCEDURE proc (arg1 integer, arg2 string) OPTIONS ("teiid_rel:native-query" 'search;SELECT ... complex SOQL ... WHERE col1 = $1 and col2 = $2') returns (col1 string, col2 string, col3 timestamp);

Native Procedure

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 above.

SalesForce translator provides a procedure with name native that gives ability to execute any ad-hoc native Salesforce queries 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 construct to build a tabular output for consumption by client applications. Teiid exposes this procedure with a simple query structure as below

Select

SELECT x.* FROM (call pm1.native('search;SELECT Account.Id, Account.Type, Account.Name FROM Account')) w,
 ARRAYTABLE(w.tuple COLUMNS "id" string , "type" string, "name" String) AS x

from the above code, the "search" keyword followed by a query statement.

The SOQL is treated as a parameterized native query so that parameter values may be inserted in the query string properly - see Parameterizable Native Queries

Delete

SELECT x.* FROM (call pm1.native('delete;', 'id1', 'id2')) w,
 ARRAYTABLE(w.tuple COLUMNS "updatecount" integer) AS x

form the above code, the "delete;" keyword followed by the ids to delete as varargs.

Create or Update

SELECT x.* FROM
 (call pm1.native('create;type=table;attributes=one,two,three', 'one', 2, 3.0)) w,
 ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x

form the above code, the "create" or "update" keyword must be followed by the following properties.  Attributes must be matched positionally by the procedure variables - thus in the example attribute two will be set to 2.

The values for each attribute is specified as separate argument to the "native" procedure.

Update is similar to create, with one more extra property called "id", which defines identifier for the record.

SELECT x.* FROM
 (call pm1.native('update;id=pk;type=table;attributes=one,two,three', 'one', 2, 3.0)) 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.

JCA Resource Adapter

The resource adapter for this translator provided through "Salesforce Data Source", Refer to Admin Guide for configuration.

Web Services Translator

The Web Services translator, known by the type name ws, exposes stored procedures for calling web services backed by a Teiid WS resource adapter. It will commonly be used with the TEXTTABLE or XMLTABLE table functions to use CSV or XML formated data.

Setting the proper binding value on the translator is recommended as it removes the need for callers to pass an explict value. If your service is actually uses SOAP11, but the binding used SOAP12 you will receive execution failures.

Execution Properties

There are ws importer settings, but it does provide metadata for dynamic VDBs.

Usage

The WS translator exposes low level procedures for accessing web services. See also the twitter example in the kit.

Invoke Procedure

Invoke allows for multiple binding, or protocol modes, including HTTP, SOAP11, and SOAP12.

Procedure invoke(binding in STRING, action in STRING, request in XML, endpoint in STRING, stream in BOOLEAN) returns XML

The binding may be one of null (to use the default) HTTP, SOAP11, or SOAP12. Action with a SOAP binding indicates the SOAPAction value. Action with a HTTP binding indicates the HTTP method (GET, POST, etc.), which defaults to POST.

A null value for the binding or endpoint will use the default value. The default endpoint is specified in the WS resource adapter configuration. The endpoint URL may be absolute or relative. If it's relative then it will be combined with the default endpoint.

Since multiple parameters are not required to have values, it is often more clear to call the invoke procedure with named parameter syntax.

call invoke(binding=>'HTTP', action=>'GET')

The request XML should be a valid XML document or root element.

If the stream parameter is set the true, the resulting value document can only be read once. This is appropriate when directly passing the XML into XMLQUERY or XMLTABLE and only a single pass against the document is needed. If stream is null or false, then the engine may need to save a copy of the document for repeated use.

InvokeHTTP Procedure

invokeHttp can return the byte contents of an HTTP(S) call.

Procedure invokeHttp(action in STRING, request in OBJECT, endpoint in STRING, stream in BOOLEAN, contentType out STRING) returns BLOB

Action indicates the HTTP method (GET, POST, etc.), which defaults to POST.

A null value for endpoint will use the default value. The default endpoint is specified in the WS resource adapter configuration. The endpoint URL may be absolute or relative. If it's relative then it will be combined with the default endpoint.

Since multiple parameters are not required to have values, it is often more clear to call the invoke procedure with named parameter syntax.

call invokeHttp(action=>'GET')

The request can be one of SQLXML, STRING, BLOB, or CLOB. The request will be sent as the POST payload in byte form. For STRING/CLOB values this will default to the UTF-8 encoding. To control the byte encoding, see the to_bytes function.

If the stream parameter is set the true, the resulting value document can only be read once. If stream is null or false, then the engine may need to save a copy of the result for repeated use.

Native queries Native or direct query execution is not supported on the File Translator

JCA Resource Adapter

Theresource adapter for this translator provided through "Web Service Data Source", Refer to Admin Guide for configuration.

OLAP Translator

The OLAP Services translator, known by the type name olap, exposes stored procedures for calling analysis sevices backed by a OLAP server using MDX query lanaguage. This translator exposes a stored procedure, invokeMDX, that returns a result set containing tuple array values for a given MDX query. invokeMDX will commonly be used with the ARRAYTABLE table function to extract the results.

Since the Cube metadata exposed by the OLAP servers and relational database metadata are so different, there is no single way to map the metadata from one to other. It is best to query OLAP system using its own native MDX language through. MDX queries my be defined statically or built dynamically in Teiid's abstraction layers.

Usage

The olap translator exposes one low level procedure for accessing olap services.

InvokeMDX Procedure

invokeMdx returns a resultset of the tuples as array values.

Procedure invokeMdx(mdx in STRING, params VARIADIC OBJECT) returns table (tuple object)

The mdx parameter is a MDX query to be executed on the OLAP server.

The results of the query will be returned such that each row on the row axis will be packed into an array value that will first contain each hierarcy member name on the row axis then each measure value from the column axis.

The use of Data Roles should be considered to prevent arbitrary MDX from being submitted to the invokeMDX procedure.

Native Queries

OLAP source procedures may be created using the teiid_rel:native-query extension - see Parameterizable Native Queries.

The parameter value substitution directly inserts boolean, and number values, and treats all other values as string literals.

The procedure will invoke the native-query similar to an invokeMdx call with the benefits that the query is predetermined and that result column types are known, rather than requiring the use of ARRAYTABLE or similar functionality.

Native Procedure

The invokeMdx procedure is the native procedure for the OLAP translator. It may be disabled or have it's name changed via the common native translator properties just like any other source. A call to a native procedure without any parameters will not attempt to parse the mdx query for parameterization. If parameters are used, the value substitution directly inserts boolean, and number values, and treats all other values as string literals.

JCA Resource Adapter

The resource adapter for this translator provided through data source in JBoss AS, Refer to Admin Guide for "JDBC Data Sources" configuration section. Two sample -ds.xml files provided for accessing OLAP servers in teiid-examples section. One is Mondrian specific, when Mondrian server is deloyed in the same JBoss AS as Teiid (mondrian-ds.xml). To access any other OLAP servers using XMLA interface, the data source for them can be created using them example template olap-xmla-ds.xml

JPA Translator

Think this one as a reverse to what Hibernate provides. If you have JPA based store and have a supporting object model, this translator can will reverse your object model into relational model, where you can integrate with other relational or non-relational sources.

Native Queries

JPA source procedures may be created using the teiid_rel:native-query extension - see Parameterizable Native Queries. The procedure will invoke the native-query similar to an native procedure call with the benefits that the query is predetermined and that result column types are known, rather than requiring the use of ARRAYTABLE or similar functionality.

Native Procedure

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 above.

JPA translator provides a procedure with name native that gives ability to execute any ad-hoc native JPA-QL queries directly against the source without any Teiid parsing or resolving. Since 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 to construct a build a tabular output for consumption by client applications. Teiid exposes this procedure with a simple query structure as below

Select

SELECT x.* FROM (call pm1.native('search;FROM Account')) w,
 ARRAYTABLE(w.tuple COLUMNS "id" string , "type" string, "name" String) AS x

from the above code, the "search" keyword followed by a query statement - see Parameterizable Native Queries to substitute parameter values.

Delete

SELECT x.* FROM (call pm1.native('delete;<jpa-ql>')) w,
 ARRAYTABLE(w.tuple COLUMNS "updatecount" integer) AS x

form the above code, the "delete" keyword followed by JPA-QL for delete operation.

Update

SELECT x.* FROM
 (call pm1.native('update;<jpa-ql>')) w,
 ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x

form the above code, the "update" keyword must be followed by JPA-QL for the update statement.

Create

SELECT x.* FROM
 (call pm1.native('create;', <entity>)) w,
 ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x

Create operation needs to send "create" word as marker and send the entity as a the first parameter.

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.

Object Translator

The Object translator is a bridge for reading java objects from external sources (i.e., Infinispan Cache or Map cache) and delivering them to the engine for processing.  And to assist in providing that bridge, the OBJECTABLE function must be used to transform the java object into rows and columns.   

The following are the translator types and their respective supported data source:

• infinispan-cache - supports local Infinispan cache and using either Key searching (for non-annotated objects) or Hibernate/Lucene searching
• infinispanremote-cache - supports remote Infinispan cache using HotRod client, but only supports Key searching (Infinispan does not currently support performing Hibernate/Lucene search on a RemoteCache)
• map-cache - supports a local cache that is of type Map and using Key searching

Execution Properties - infinispan-cache

Either CacheJndiName or ConfigurationFileName must be specified so that Infinispan Cache Manager can be obtained.

Execution Properties - infinispanremote-cache

Either CacheJndiName, RemoteServerList or ConfigurationFileName must be specified so that Infinispan Cache Manager can be obtained.

Execution Properties - map-cache

Supported Capabilities

The following are the connector capabilities when Key Searching is used:

• SELECT command
• CompareCriteria - only EQ
• InCriteria

The following are the connector capabilities when Hibernate/Lucene Searching is enabled:

• SELECT command
• CompareCriteria - EQ, NE, LT, GT, etc.
• InCriteria
• OrCriteria

Usage

Retrieve objects from a cache and transform into rows and columns.

• The primary object returned by the cache should have a name in source of 'this'.  All other columns will have their name in source (which defaults to the column name) interpreted as the path to the column value from the primary object.
• All columns that are not the primary key nor covered by a lucene index should be marked as SEARCHABLE 'Unsearchable'.

The following is an example of a key search. It uses a dynamic vdb to define the physical source and views using DDL.   It uses a TeamObject class, shown below, with a teamName field that is used as its cache key and a String list of players. 

public class TeamObject {

   private String teamName;
   private List<String> players = new ArrayList<String>();

   public String getTeamName() {
      return teamName;
   }

   public void setTeamName(String teamName) {
	   this.teamName = teamName;
   }

   public List<String> getPlayers() {
	   return players;
   }

}

Notice the use of the OBJECTABLE function to parse the object from Team and transform into rows and column. This metadata could also be defined by using Teiid Designer.  

<vdb name="team" version="1">
    <description>Shows how to call a remoteInfinispan Cache</description>
    <property name="UseConnectorMetadata" value="cached" />
    <model name="Team" visible="false">
        <source name="objsource" translator-name="infinispan1" />
        <metadata type="DDL"><![CDATA[

            CREATE FOREIGN TABLE Team (TeamObject Object OPTIONS (NAMEINSOURCE 'this', SEARCHABLE 'Unsearchable'), teamName varchar(255) PRIMARY KEY);

         ]]> </metadata>
    </model>
    <model name="TeamView" type="VIRTUAL">
         <metadata type="DDL"><![CDATA[
             CREATE VIEW Players (
                  TeamName varchar(255) PRIMARY KEY,
                  PlayerName varchar(255)
             )
             AS
             SELECT t.TeamName, y.Name FROM Team as T, 
                   OBJECTTABLE('m.players' PASSING T.TeamObject as m COLUMNS Name string 'teiid_row') as y;          

        ]]> </metadata>
    </model>


    <translator name="infinispan1" type="infinispanremote-cache">
        <property name="CacheName" value="teams"/>
        <property name="RootClassName" value="com.jboss.datagrid.hotrod.Team"/>
        <property name="RemoteServerList" value="localhost:11222"/>
        <!--  optional properties for obtaining the RemoteCacheManager  -->
        <!--
         <property name="CacheJndiName" value="java/CacheManager"/>
         <property name="ConfigurationFileName" value="<dir>/infinispan-config.xml"/>
         -->
    </translator>
</vdb>

JCA Resource Adapter

There is no resource adapter for this translator.   The cache container is either obtained via JNDI or created (i.e., ConfigurationFileName or RemoteServerList). 

Delegating Translators

You may create a delegating translator by extending the org.teiid.translator.BaseDelegatingExecutionFactory. Once your classes are then packaged as a custom translator, you will be able to wire another translator instance into your delegating translator at runtime in order to intercept all of the calls to the delegate. This base class does not provide any functionality on its own, other than delegation.

Execution Properties

Lets say you are currently using "oracle" translator in your VDB, you want to intercept the calls going through this translator, then you first write a custom delegating translator like

@Translator(name="interceptor", description="interceptor")
public class InterceptorExecutionFactory extends org.teiid.translator.BaseDelegatingExecutionFactory{
    @Override
    public void getMetadata(MetadataFactory metadataFactory, C conn) throws TranslatorException {
        // do intercepting code here..

        // If you want call the original delegate, do not call if do not need to.
        // but if you did not call the delegate fullfill the method contract
        super.getMetadata(metadataFactory, conn);

        // do more intercepting code here..
    }
}

Now deploy this translator in Teiid engine. Then in your -vdb.xml or .vdb file define like below.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="myvdb" version="1">

    <model name="mymodel">
        <source name="source" translator-name="oracle-interceptor" connection-jndi-name="java:oracle-ds"/>
    </model>

    <!-- the below it is called translator overriding, where you can set different properties -->
    <translator name="orcle-interceptor" type="interceptor" />
        <property name="delegateName" value="oracle" />
   </translator>
</vdb>

We have defined a "translator" override called "oracle-interceptor", which is based on the custom translator "interceptor" from above, and supplied the translator it needs to delegate to "oracle" as its delegateName. Then, we used this override translator "oracle-interceptor" in your VDB. Now any calls going into this VDB model's translator will be intercepted by YOUR code to do whatever you want to do.

select