[teiid-issues] [JBoss JIRA] (TEIID-2577) Translator API to provide session backed connection

[Steven Hawkins (JIRA)]

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

Steven Hawkins commented on TEIID-2577:
---------------------------------------

For a Java db like H2 or Derby without a procedure language their approach is to provide the session's Connection for use at the procedure/function extension point.  So I do see this as a valid approach, but the desire for temp table escaping is not quite the same as our virtual procedure current allow - which forces the temp table to be local to their creating block.  For us to implement this properly the returned Connection would have to operate as if it were in a block implying that transaction control may be somewhat limited and that local temporary tables are dropped on exit.

As a way to somewhat sidestep this issue and the need to provide explicit cursor returns we could be formal and introduce postgresql style session scoped temp tables.  For example from a procedure:

{code}
BEGIN 
CREATE TEMPORARY TABLE FOO IF NOT EXISTS ...
...
INSERT INTO FOO SELECT ...
END
{code}

Note that we're not using the LOCAL keyword.  Then from JDBC:

{code}
CALL PROC()
{code}

{code}
SELECT * FROM FOO
{code}

This would also require the usage of the LOCAL keyword with the current CREATE FOREIGN TEMPORARY TABLE syntax.

Which brings us back to the issue at hand.  Suppose that we offered the ability to execute anonymous procedure blocks like the one above with the ability to create/reference session scoped tables, would that be sufficient/useful or would still want the ability to do this programatically at the translator layer?
                
> Translator API to provide session backed connection
> ---------------------------------------------------
>
>                 Key: TEIID-2577
>                 URL: https://issues.jboss.org/browse/TEIID-2577
>             Project: Teiid
>          Issue Type: Feature Request
>            Reporter: Mark Addleman
>            Assignee: Steven Hawkins
>
> Provide an API (probably from an ExecutionContext) to obtain a JDBC connection to the same Teiid instance under the client's session.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira