[teiid-issues] [JBoss JIRA] Updated: (TEIID-669) Support "pass though" SQL commands to sources

Sun Jul 19 10:41:40 EDT 2009

     [ https://jira.jboss.org/jira/browse/TEIID-669?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins updated TEIID-669:
---------------------------------

    Description: 
Currently there is no mechanism in Teiid, when a query is submitted to a runtime engine that is passed through the query engine unadulterated and unmodified and submitted directly to a source connector. All queries are parsed, optimized, re-written.  Some times this limits the functionality in Teiid, when a user would like to execute a query that is very "source" specific. For example Oracle supports lots of Oracle specific functions that are not supported by the SQL spec, to support execution of these in Teiid user need to resort to UDF, which is cumbersome and involves modelling. 

Another benefit of this is also to pass DDL to sources, currently since Teiid does not support DDL, this is not possible at all.

  was:
Currently there is no mechanism in Teiid, when a query is submitted to a runtime engine that is passed through the query engine unadulterated and unmodified and submitted directly to a source connector. All queries are parsed, optimized, re-written.  Some times this limits the functionality in Teiid, when a user would like to execute a query that is very "source" specific. For example Oracle supports lots of Oracle specific functions that are not supported by the SQL spec, to support execution of these in Teiid user need to resort to UDF, which is cumbersome and involves modelling. 

Another benefit of this is also to pass DDL to sources, currently since Teiid does not support DDL, this is not possible at all.

There is no-expectation that these queries are part of any data integration, these are one off queries that enhance user experience with Teiid if they use this feature. Command Payload mechanism is similar system that is used for authentication and authorization needs at the connector level, this feature can use similar techniques or use some custom extension to the language to define a query etc.

        Affects: [Documentation (Ref Guide, User Guide, etc.), Release Notes]

I see two primary scenarios for pass-through.  

1. DDL direct from our JDBC client is a development time aid that adds to our interoperability with hibernate usage and most db tooling.  A possible implementation path is to look for a set statement, such as "set pass_through=<model name>" with a corresponding end statement.  When pass through is enabled we would treat each statement as an update to be issued directly to the source via enhancements to the connector api.  Issues with this approach include the lack of security, problems with heterogeneous multi-source, the introduction of a new statement type, possible connection handling issues (the source connection may need to be in exclusive use similar to a global transaction).  The benefit is that source ddl/update scripts/statements could be issues with minimal modification.  I'm inclined to push work along these lines to 6.3

2. Data query pass-trough would allow for source execution of complex sql, salesforce ql, ldap, etc. queries.  A possible implementation path here is to reuse our dynamic sql syntax - EXECUTE STRING <string value> AS <column name> <column type>, ... ON <model name> - with the addition of the on clause to indicate what model should be used.  If we restrict the usage of the statement to virtual procedures then security is already handled (however heterogeneous multi-source could still be a problem).  There are also only minimal changes to required to parsing and display logic.  We would still need enhancements to the connector api to accept a string rather than a data command for execution.  Since this pass trough mode has been a customer request in the past, I consider it of higher value and something that should get worked in 6.2.

> Support "pass though" SQL commands to sources
> ---------------------------------------------
>
>                 Key: TEIID-669
>                 URL: https://jira.jboss.org/jira/browse/TEIID-669
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Query Engine
>            Reporter: Ramesh Reddy
>            Assignee: Steven Hawkins
>             Fix For: 6.2.0
>
>
> Currently there is no mechanism in Teiid, when a query is submitted to a runtime engine that is passed through the query engine unadulterated and unmodified and submitted directly to a source connector. All queries are parsed, optimized, re-written.  Some times this limits the functionality in Teiid, when a user would like to execute a query that is very "source" specific. For example Oracle supports lots of Oracle specific functions that are not supported by the SQL spec, to support execution of these in Teiid user need to resort to UDF, which is cumbersome and involves modelling. 
> Another benefit of this is also to pass DDL to sources, currently since Teiid does not support DDL, this is not possible at all.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira