[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=13725024#comment-13725024 ] 

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

> The fact that there is a difference between an odata and non-odata case did not become clear for me from the documentation. Maybe this could be explained together with the differences.

OData access is quite different than just JDBC / pg access.

> So I assume this is the difference, if I want to export the functions as ODATA I am not allowed to use OUT or INOUT?

OData treats INOUTS as just IN, and OUT is ignored.  So they can be present in the Teiid metadata, just not utilized over OData.

> Not sure what this means. I will try out what hapens if I return an array of collection items -> did you mean this by multidimensional?

A procedure with a result set:

create virtual procedure rs () returns table (a string, b string) ...

Will return a collection of a complex type containing a, b.  For example in json [{a='aval1', b='bval1'}, {a='aval2', b='bval2'}, ...]

A procedure with an array result:

create virtual procedure rs () returns string[] ...

Will return a collection of a string simple type.  For example in json ['val1', 'val2', ...]

The usage of something multi-dimensional like string[][] in odata is not currently supported.

> So what kind of SQL version is used here? Could you recommend a tutorial on it?

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

And see the grammar as well: http://teiid.github.io/teiid-documents/master/content/reference/BNF_for_SQL_Grammar.html

> but the statement does not work as probably the syntax is also wrong in Teiid.

Yes, the select into construct is only used for tables in Teiid - it's just a different form of "insert into tbl select ...";







> 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)