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

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-669?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12579653#comment-12579653 ] 

Steven Hawkins commented on TEIID-669:
--------------------------------------

I would like to introduce a variant of scenario 2 about.  Rather than allow a direct statement for execution, which has security implications, we could allow physical stored procedures to specify native sql using a syntax that supports bind variables.  There would be restrictions such as only in parameters and it must return a result set.  The translator would also need to support passthrough commands.  

However, this approach pushes a lot work to the designer (allowing "transformation sql" on a physical model, additional validation, etc.)

If we want to largely bypass designer/emf then translator/executionfactory could be updated to allow for a translator definition to take native procedures. These would need hand modeled in designer or would be automatically exposed by dynamic vdbs.  
e.g.
<translator name="..." type= ...>
  <nativeProcedure name="procName" returning="x string, y integer">
     SELECT /*+ complicated hint or something exotic that can't be represented well in Teiid */ ... :param1 ... :param2
  </nativeProcedure>
  ...
</translator>

The inputs can be derived from the native query.  The advantage here is that this is getting fairly close to a traditional DDL statement for defining a proc with an alternative language.

> Support "pass though" SQL commands to sources
> ---------------------------------------------
>
>                 Key: TEIID-669
>                 URL: https://issues.jboss.org/browse/TEIID-669
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Query Engine
>            Reporter: Ramesh Reddy
>            Assignee: Steven Hawkins
>             Fix For: 7.4
>
>
> 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.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira