<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.5//EN" "http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd" [
<!ENTITY % CustomDTD SYSTEM "../../../../../../docbook/custom.dtd">
%CustomDTD;
]>
<chapter id="jdbc_extensions">
    <title>Teiid extensions to the JDBC API</title>
    
    <section id="statement_extensions">
        <title>Statement Extensions</title>
        <para>The Teiid statement extension interface, 
        <code>org.teiid.jdbc.TeiidStatement</code>, 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:</para>
        
        <table frame='all'>
            <title>Connection Properties</title>
            <tgroup cols='2' align='left' colsep='1' rowsep='1'>
                <colspec colname='c1' colwidth="1*" />
                <colspec colname='c2' colwidth="2*" />
                <thead>
                    <row>
                        <entry>Method Name</entry>
                        <entry>Description</entry>
                    </row>
                </thead>
                <tbody>
                    <row>
                        <entry>
                            <code>getAnnotations</code>
                        </entry>
                        <entry>
                            <para>Get the query engine annotations if the statement was last executed with SHOWPLAN ON|DEBUG.  Each <code>org.teiid.client.plan.Annotation</code> 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.</para>
                        </entry>
                    </row>
                    
                    <row>
                        <entry>
                            <code>getDebugLog</code>
                        </entry>
                        <entry>
                            <para>Get the debug log if the statement was last executed with SHOWPLAN DEBUG.</para>
                        </entry>
                    </row>

                    <row>
                        <entry>
                            <code>getExecutionProperty</code>
                        </entry>
                        <entry>
                            <para>Get the current value of an execution property on this statement object.</para>
                        </entry>
                    </row>

                    <row>
                        <entry>
                            <code>getPlanDescription</code>
                        </entry>
                        <entry>
                            <para>Get the query plan description if the statement was last executed with SHOWPLAN ON|DEBUG.  The plan is a tree made up of <code>org.teiid.client.plan.PlanNode</code> objects.  
                            Typically <code>PlanNode.toString()</code> or <code>PlanNode.toXml()</code></para> will be used to convert the plan into a textual form.
                        </entry>
                    </row>
                    <row>
                        <entry>
                            <code>getRequestIdentifier</code>
                        </entry>
                        <entry>
                            <para>Get an identifier for the last command executed on this statement.  If no command has been executed yet, null is returned.</para>
                        </entry>
                    </row>

                    <row>
                        <entry>
                            <code>setExecutionProperty</code>
                        </entry>
                        <entry>
                            <para>Set the execution property on this statement.  See the <link linkend="execution_properties">execution properties</link> section for more information.  
                            It is generally preferable to use the <link linkend="set_statement">SET statement</link> unless the execution property applies only to the statement being executed.</para>
                        </entry>
                    </row>

                    <row>
                        <entry>
                            <code>setPayload</code>
                        </entry>
                        <entry>
                            <para>Set a per-command payload to pass to translators. Currently 
                            the only built-in use is for sending hints for Oracle 
                            data source.</para>
                        </entry>
                    </row>
                </tbody>
            </tgroup>
       	 </table>
       </section>
       
       <section id="execution_properties">
        <title>Execution Properties</title>
        <para>Execution properties may be set on a per statement basis through the <link linkend="statement_extensions"><code>TeiidStatement</code></link> interface or on the connection via the <link linkend="set_statement">SET statement</link>.
        For convenience, the property keys are defined by constants on the <code>org.teiid.jdbc.ExecutionProperties</code> interface.</para>
        
        <table frame='all'>
            <title>Execution Properties</title>
            <tgroup cols='2' align='left' colsep='1' rowsep='1'>
                <colspec colname='c1' colwidth="1*" />
                <colspec colname='c2' colwidth="2*" />
                <thead>
                    <row>
                        <entry>Property Name/String Constant</entry>
                        <entry>Description</entry>
                    </row>
                </thead>
                <tbody>
                    <row>
                        <entry>
                            <code>PROP_TXN_AUTO_WRAP / autoCommitTxn</code>
                        </entry>
                        <entry>
                            <para>Same as the connection property.</para>
                        </entry>
                    </row>
                    <row>
                        <entry>
                            <code>PROP_PARTIAL_RESULTS_MODE / partialResultsMode</code>
                        </entry>
                        <entry>
                            <para>See the <link linkend="partial_results">partial results</link> section.</para>
                        </entry>
                    </row>
                    <row>
                        <entry>
                            <code>PROP_XML_FORMAT / XMLFormat</code>
                        </entry>
                        <entry>
                            <para>Determines the formatting of XML documents returned by XML document models.  See the <link linkend="document_formatting">document formatting</link> section.</para>
                        </entry>
                    </row>      
                    <row>
                        <entry>
                            <code>PROP_XML_VALIDATION / XMLValidation</code>
                        </entry>
                        <entry>
                            <para>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.</para>
                        </entry>
                    </row>      
                    <row>
                        <entry>
                            <code>RESULT_SET_CACHE_MODE / resultSetCacheMode</code>
                        </entry>
                        <entry>
                            <para>Same as the connection property.</para>
                        </entry>
                    </row>
                    <row>
                        <entry>
                            <code>SQL_OPTION_SHOWPLAN / SHOWPLAN</code>
                        </entry>
                        <entry>
                            <para>Same as the connection property.</para>
                        </entry>
                    </row>
                    <row>
                        <entry>
                            <code>NOEXEC / NOEXEC</code>
                        </entry>
                        <entry>
                            <para>Same as the connection property.</para>
                        </entry>
                    </row>
                    <row>
                        <entry>
                            <code>JDBC4COLUMNNAMEANDLABELSEMANTICS / useJDBC4ColumnNameAndLabelSemantics</code>
                        </entry>
                        <entry>
                            <para>Same as the connection property.</para>
                        </entry>
                    </row>						
                 </tbody>
             </tgroup>
        </table>        
       </section>    
       
       <section id="set_statement">
        <title>SET Statement</title>
        
        <para>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.</para>
        
        <itemizedlist>
            <para>SET Syntax:
            </para>
            <listitem>
                <para>SET (parameter|SESSION AUTHORIZATION) value
                </para>
            </listitem>
        </itemizedlist>
        <itemizedlist>
            <para>Syntax Rules:
            </para>
            <listitem>
                <para>The parameter must be a non-quoted identifier - it cannot contain spaces.</para>
            </listitem>
            <listitem>
                <para>The value may be either a non-quoted identifier or a quoted string literal value.</para>
            </listitem>
        </itemizedlist>
        <para>The SET statement is most commonly used to control planning and execution.</para>
        <itemizedlist>
            <listitem>
                <para>SET SHOWPLAN (ON|DEBUG|OFF)</para>
            </listitem>
            <listitem>
                <para>SET NOEXEC (ON|OFF)</para>
            </listitem>
        </itemizedlist>        
        <example id="plan_debug">
        	<title>Enabling Plan Debug</title>
        	<programlisting>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"); 
        	</programlisting>
        </example>
        <para>The SET statement may also be used to control authorization.  
        A SET SESSION AUTHORIZATION statement will perform a <xref linkend="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.</para>
        <example>
        	<title>Changing Session Authorization</title>
        	<programlisting>Statement s = connection.createStatement();
s.execute("SET PASSWORD 'someval'");
s.execute("SET SESSION AUTHORIZATION 'newuser'");
        	</programlisting>
        </example>
       </section>

       <section id="show_statement">
        <title>SHOW Statement</title>
        
        <para>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.</para>
        
        <itemizedlist>
            <para>SHOW Usage:
            </para>
            <listitem>
                <para>SHOW <emphasis>PLAN</emphasis> - 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.
                </para>
            </listitem>
            <listitem>
                <para>SHOW <emphasis>ANNOTATIONS</emphasis> - 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.
                </para>
            </listitem>
            <listitem>
                <para>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.
                </para>
            </listitem>
            <listitem>
                <para>SHOW <emphasis>ALL</emphasis> - returns a resultset with a NAME string column and a VALUE string column with a row entry for every property value.
                </para>
            </listitem>
        </itemizedlist>
        <para>The SHOW statement is most commonly used to retrieve the query plan, see the plan <link linkend="plan_debug">debug example</link>.</para>        
       </section>
       
       <section id="transaction_statement">
       	<title>Transaction Statements</title>
       	<para>In situations where the direct use of the JDBC connection is not possible, transaction statements can be used to control a local transaction.</para>
       	<itemizedlist>
       		<listitem><para><emphasis>START TRANSACTION</emphasis> - synonym for <code>connection.setAutoCommit(false)</code></para>
       		</listitem>
       		<listitem><para><emphasis>COMMIT</emphasis> - synonym for <code>connection.setAutoCommit(true)</code></para>
       		</listitem>
       		<listitem><para><emphasis>ROLLBACK</emphasis> - synonym for <code>connection.rollback()</code> and returning to auto commit mode.</para>
       		</listitem>
       	</itemizedlist>
       </section>
                            
       <section id="partial_results">
        <title>Partial Results Mode</title>
        <para>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.</para>
        
        <para>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.</para>
        
        <para>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. </para>
        
        <para>This behavior is most useful when using <code>UNION</code> or <code>OUTER JOIN</code> 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. </para>
        
        <para>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 
        <code>Statement.getWarnings()</code> method.  This method returns a <code>SQLWarning</code> object but 
        in the case of "partial results" warnings, this will be an object of 
        type <code>org.teiid.jdbc.PartialResultsWarning</code> 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. </para>
        
        <note>
          <para> 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.</para>
        </note>
        
        <para>
          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 <link linkend="set_statement">set statement</link>.
        </para>
        <example>
          <title>Setting Partial Results Mode</title>
          <programlisting>Statement statement = ...obtain statement from Connection... 
statement.execute("set partialResultsMode true");</programlisting>
        </example>
      
        <example>
          <title>Getting Partial Results Warnings
          </title>
          <programlisting>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();
  }
}</programlisting>
        </example>         
       </section>
       
       <section>
        <title>XML extensions</title>
        <para>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.</para>
        <section id="document_formatting">
            <title>Document formatting</title>
            <para>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:</para>
            <orderedlist>
                <listitem><para><code>XML_TREE_FORMAT</code> - 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.</para></listitem>
                <listitem><para><code>XML_COMPACT_FORMAT</code> - Returns a version of the XML formatted for 
                optimal performance.  The XML is a single long string without any 
                unnecessary white space.  </para></listitem>
                <listitem><para>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.</para></listitem>
            </orderedlist>
        </section>
        
        <section id="schema_validation">
            <title>Schema validation</title>
            <para>The <code>PROP_XML_VALIDATION</code> 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. </para>
        </section>
       </section>
       
	<section id="non_blocking">
		<title>Non-blocking Statement Execution</title>	
		<para>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 
		<code>org.teiid.jdbc.TeiidStatement</code> and <code>org.teiid.jdbc.TeiidPreparedStatement</code> interfaces to execute queries with a callback <code>org.teiid.jdbc.StatementCallback</code> 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.  
		</para>
        <example>
          <title>Non-blocking Prepared Statement Execution</title>
          <programlisting>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();
    }, new RequestOptions()
);</programlisting>
		</example>
		<note><para>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.</para></note>
		
		<section>
			<title>Continous Execution</title>
			<para>The <code>RequestOptions</code> object may be used to specify a special type of continuous asynch execution via the <code>continuous</code> or <code>setContinuous</code> methods.  
			In continuous mode the statement will be continously re-executed.  This is intended for consuming real-time or other data streams processed through a SQL plan.  
			A continuous query will only terminate on an error or when the statement is explicitly closed.  The SQL for a continuous query is no different than any other statement.  
			Care should be taken to ensure that retrivals from non-continuous sources is appropriately cached for reuse, such as by using materialized views or session scoped temp tables.  
			A continous query must return a result set, must be executed with a forward-only resultset, and cannot be used in the scope of a transaction.  Since resource consumptions is expected to be
			different in a continuous plan, it does not count against the server max active plan limit.
			Typically custom sources will be used to provide data streams.  See the Developer's Guide, in particular the section on ReusableExecutions for more.</para>
		</section>	
		
	</section>
 
</chapter>