[teiid-issues] [JBoss JIRA] (TEIID-5725) Please improve documentation on definition of stored procedures, virtual functions and virtual procedures

[Steven Hawkins (Jira)]

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

Steven Hawkins commented on TEIID-5725:
---------------------------------------

> Do I explicitly have to wrap my virtual procedures and functions which I write in the ddl file into a transaction? 

It's up to you how much consistency you expect.   The default behavior of the database is to auto commit on each statement.  The default isolation level will vary by database.  See https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

Under the auto-commit paradigm Teiid will create XA transactions when needed - if it appears that multiple accesses to transactional resources may be needed under a single user statement.

> And how would the command syntax for this look like?

In Teiid DDL if you want the statement to explicitly declare a transaction boundary the procedure syntax is:

begin atomic
   ...
end

See http://teiid.github.io/teiid-documents/master/content/reference/Procedure_Language.html

There are discussions of various implicit and user direct transactions under: http://teiid.github.io/teiid-documents/master/content/client-dev/Transactions.html

> My thinking is as follows; If I have a stored procedure or function in the source database, I would like to use it within my virtual procedures but at the same time would not like to allow access via odata. Would it be possible to add a visibility option to the procedure definition in the dll definition? Or does something similar already exist?

If marking the source schema as hidden causes too much extra work, you could consider using data roles to selectively control access.



> Please improve documentation on definition of stored procedures, virtual functions and virtual procedures
> ---------------------------------------------------------------------------------------------------------
>
>                 Key: TEIID-5725
>                 URL: https://issues.jboss.org/browse/TEIID-5725
>             Project: Teiid
>          Issue Type: Enhancement
>            Reporter: Christoph John
>            Assignee: Steven Hawkins
>            Priority: Critical
>
> I refer to the discussion with Ramesh in 
> https://developer.jboss.org/message/989048#989048 and would like to ask for a more detailed explanation on how to write virtual procedures and functions. 
> -Especially, how are OUT and INOUT params set and how can they be obtained via odata. 
> - How is the return value of a virtual function set?
> - How are collections and arrays of collections returned?



--
This message was sent by Atlassian Jira
(v7.12.1#712002)