Chapter 3. Teiid extensions to the JDBC API

3.1. Statement Extensions

The Teiid statement extension interface, org.teiid.jdbc.TeiidStatement, provides functionality beyond the JDBC standard. To use the extension interface, simply cast or unwap the statement returned by the Connection. The following methods are provided on the extension interface:

Table 3.1. Connection Properties

Method Name	Description
`getAnnotations`	Get the query engine annotations if the statement was last executed with SHOWPLAN ON\|DEBUG. Each `org.teiid.client.plan.Annotation` contains a description, a category, a severity, and possibly a resolution of notes recorded during query planning that can be used to understand choices made by the query planner.
`getDebugLog`	Get the debug log if the statement was last executed with SHOWPLAN DEBUG.
`getExecutionProperty`	Get the current value of an execution property on this statement object.
`getPlanDescription`	Get the query plan description if the statement was last executed with SHOWPLAN ON\|DEBUG. The plan is a tree made up of `org.teiid.client.plan.PlanNode` objects. Typically `PlanNode.toString()` or `PlanNode.toXml()` will be used to convert the plan into a textual form.
`getRequestIdentifier`	Get an identifier for the last command executed on this statement. If no command has been executed yet, null is returned.
`setExecutionProperty`	Set the execution property on this statement. See the execution properties section for more information. It is generally preferable to use the SET statement unless the execution property applies only to the statement being executed.
`setPayload`	Set a per-command payload to pass to translators. Currently the only built-in use is for sending hints for Oracle data source.

3.2. Execution Properties

Execution properties may be set on a per statement basis through the TeiidStatement interface or on the connection via the SET statement. For convenience, the property keys are defined by constants on the org.teiid.jdbc.ExecutionProperties interface.

Table 3.2. Execution Properties

Property Name/String Constant	Description
`PROP_TXN_AUTO_WRAP / autoCommitTxn`	Same as the connection property.
`PROP_PARTIAL_RESULTS_MODE / partialResultsMode`	See the partial results section.
`PROP_XML_FORMAT / XMLFormat`	Determines the formatting of XML documents returned by XML document models. See the document formatting section.
`PROP_XML_VALIDATION / XMLValidation`	Determines whether XML documents returned by XML document models will be validated against their schema after processing. See the Reference Guide's "XML SELECT Command" chapter and "document validation" section.
`RESULT_SET_CACHE_MODE / resultSetCacheMode`	Same as the connection property.
`SQL_OPTION_SHOWPLAN / SHOWPLAN`	Same as the connection property.
`NOEXEC / NOEXEC`	Same as the connection property.

3.3. SET Statement

Execution properties may also be set on the connection by using the SET statement. The SET statement is not yet a language feature of Teiid and is handled only in the JDBC client.

SET Syntax:

SET parameter value

Syntax Rules:

Both parameter and value must be simple literals - they cannot contain spaces.
The value is also not treated as an expression and will not be evaluated prior to being set as the parameter value.

The SET statement is most commonly used to control planning and execution.

SET SHOWPLAN (ON|DEBUG|OFF)
SET NOEXEC (ON|OFF)

Example 3.1. Enabling Plan Debug

Statement s = connection.createStatement();
s.execute("SET SHOWPLAN DEBUG");
...
Statement s1 = connection.createStatement();
ResultSet rs = s1.executeQuery("select col from table");

ResultSet planRs = s1.exeuteQuery("SHOW PLAN");
planRs.next();
String debugLog = planRs.getString("DEBUG_LOG");

3.4. SHOW Statement

The SHOW statement can be used to see a varitey of information. The SHOW statement is not yet a language feature of Teiid and is handled only in the JDBC client.

SHOW Usage:

SHOW PLAN - returns a resultset with a clob column PLAN_TEXT, an xml column PLAN_XML, and a clob column DEBUG_LOG with a row containing the values from the previously executed query. If SHOWPLAN is OFF or no plan is available, no rows are returned. If SHOWPLAN is not set to DEBUG, then DEBUG_LOG will return a null value.
SHOW ANNOTATIONS - returns a resultset with string columns CATEGORY, PRIORITY, ANNOTATION, RESOLUTION and a row for each annotation on the previously executed query. If SHOWPLAN is OFF or no plan is available, no rows are returned.
SHOW property - the inverse of SET, shows the property value for the given property, returns a resultset with a single string column with a name matching the property key.
SHOW ALL - returns a resultset with a NAME string column and a VALUE string column with a row entry for every property value.

The SHOW statement is most commonly used to retrieve the query plan, see the plan debug example.

3.5. Transaction Statements

In situations where the direct use of the JDBC connection is not possible, transaction statements can be used to control a local transaction.

START TRANSACTION - synonym for connection.setAutoCommit(false)
COMMIT - synonym for connection.setAutoCommit(true)
ROLLBACK - synonym for connection.rollback() and returning to auto commit mode.

3.6. Partial Results Mode

The Teiid Server supports a "partial results" query mode. This mode changes the behavior of the query processor so the server returns results even when some data sources are unavailable.

For example, suppose that two data sources exist for different suppliers and your data Designers have created a virtual group that creates a union between the information from the two suppliers. If your application submits a query without using partial results query mode and one of the suppliers’ databases is down, the query against the virtual group returns an exception. However, if your application runs the same query in “partial results” query mode, the server returns data from the running data source and no data from the data source that is down.

When using "partial results" mode, if a source throws an exception during processing it does not cause the user’s query to fail. Rather, that source is treated as returning no more rows after the failure point. Most commonly, that source will return 0 rows.

This behavior is most useful when using UNION or OUTER JOIN queries as these operations handle missing information in a useful way. Most other kinds of queries will simply return 0 rows to the user when used in partial results mode and the source is unavailable.

For each source that is excluded from the query, a warning will be generated describing the source and the failure. These warnings can be obtained from the Statement.getWarnings() method. This method returns a SQLWarning object but in the case of "partial results" warnings, this will be an object of type org.teiid.jdbc.PartialResultsWarning class. This class can be used to obtain a list of all the failed sources by name and to obtain the specific exception thrown by each resource adaptor.

Note

Since Teiid supports cursoring before the entire result is formed, it is possible that a data source failure will not be determined until after the first batch of results have been returned to the client. This can happen in the case of unions, but not joins. To ensure that all warnings have been accumulated, the statement should be checked after the entire result set has been read.

Partial results mode is off by default but can be turned on for all queries in a Connection with either setPartialResultsMode("true") on a DataSource or partialResultsMode=true on a JDBC URL. In either case, partial results mode may be toggled later with a set statement.

Example 3.2. Setting Partial Results Mode

Statement statement = ...obtain statement from Connection... 
statement.execute("set partialResultsMode true");

Example 3.3. Getting Partial Results Warnings

statement.execute("set partialResultsMode true");
ResultSet results = statement.executeQuery("SELECT Name FROM Accounts");
while (results.next()) {
  ... //process the result set
} 
SQLWarning warning = statement.getWarnings(); 
if(warning instanceof PartialResultsWarning) { 
  PartialResultsWarning partialWarning = (PartialResultsWarning)warning; 
  Collection failedConnectors = partialWarning.getFailedConnectors(); 
  Iterator iter = failedConnectors.iterator(); 
  while(iter.hasNext()) { 
    String connectorName = (String) iter.next(); 
    SQLException connectorException = partialWarning.getConnectorException(connectorName); 
    System.out.println(connectorName + ": " + ConnectorException.getMessage();
  }
}

3.7. XML extensions

The XML extensions apply on to XML resutls from queries to XML document models, and not to XML produced by SQL/XML or read from some other source.

3.7.1. Document formatting

The PROP_XML_FORMAT execution property can be set to modify the way that XML documents are formatted from XML document models. Valid values for the constant are defined in the same ExecutionProperties interface:

XML_TREE_FORMAT - Returns a version of the XML formatted for display. The XML will use line breaks and tabs as appropriate to format the XML as a tree. This format is slower due to the formatting time and the larger document size.
XML_COMPACT_FORMAT - Returns a version of the XML formatted for optimal performance. The XML is a single long string without any unnecessary white space.
Not Set - If no format is set, the formatting flag on the XML document in the original model is honored. This may produce either the “tree” or “compact” form of the document depending on the document setting.

3.7.2. Schema validation

The PROP_XML_VALIDATION execution property can be set to indicate that the server should validate XML document model documents against their schema before returning them to the client. If schema validation is on, then the server send a SQLWarning if the document does not conform to the schema it is associated with. Using schema validation will reduce the performance of your XML queries.