JBoss.orgCommunity Documentation
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 |
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 |
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. |
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. |
JDBC4COLUMNNAMEANDLABELSEMANTICS / useJDBC4ColumnNameAndLabelSemantics
|
Same as the connection property. |
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|SESSION AUTHORIZATION) value
Syntax Rules:
The parameter must be a non-quoted identifier - it cannot contain spaces.
The value may be either a non-quoted identifier or a quoted string literal 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");
The SET statement may also be used to control authorization. A SET SESSION AUTHORIZATION statement will perform a Section 1.6, “Reauthentication” given the credentials currently set on the connection. The connection credentials may be changed by issuing a SET PASSWORD statement. A SET PASSWORD statement does not perform a reauthentication.
Example 3.2. Changing Session Authorization
Statement s = connection.createStatement();
s.execute("SET PASSWORD 'someval'");
s.execute("SET SESSION AUTHORIZATION 'newuser'");
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.
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.
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.
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.3. Setting Partial Results Mode
Statement statement = ...obtain statement from Connection...
statement.execute("set partialResultsMode true");Example 3.4. 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();
}
}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.
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.
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.
JDBC query execution can indefinitely block the calling thread when a statement is executed or a resultset is being iterated.
In some situations you may wish to have your calling threads held in these blocked states. When using embedded connections, you may optionally use the
org.teiid.jdbc.TeiidStatement and org.teiid.jdbc.TeiidPreparedStatement interfaces to execute queries with a callback org.teiid.jdbc.StatementCallback that will be notified
of statement events, such as an available row, an exception, or completion. Your calling thread will be free to perform other work. The callback will be executed by an engine processing thread as needed. If your results processing is itself blocking and you want query processing to be concurrent with results processing, then your callback should implement onRow
handling in a multi-threaded manner to allow the engine thread to continue.
Example 3.5. Non-blocking Prepared Statement Execution
PreparedStatemnt stmt = connection.prepareStatement(sql);
TeiidPreparedStatement tStmt = stmt.unwrap(TeiidPreparedStatement.class);
tStmt.submitExecute(new StatementCallback() {
@Override
public void onRow(Statement s, ResultSet rs) {
//any logic that accesses the current row ...
System.out.println(rs.getString(1));
}
@Override
public void onException(Statement s, Exception e) throws Exception {
s.close();
}
@Override
public void onComplete(Statement s) throws Exception {
s.close();
}
);The non-blocking logic is limited to statement execution only. Other JDBC operations, such as connection creation or batched executions do not yet have non-blocking options.