Chapter 2. Teiid extensions to the JDBC API

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

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

2.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 2.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");
TeiidStatement ts = s1.unwrap(TeiidStatement.class);
String debugLog = ts.getDebugLog();

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

Below is an example of printing the list of failed sources:

statement.setExecutionProperty(ExecutionProperties.PROP_PARTIAL_RESULTS_MODE, “true”);
ResultSet results = statement.executeQuery(“SELECT Name FROM Accounts”);
SQLWarning warning = results.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();
    }
}

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

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

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