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

Steven Hawkins edited comment on TEIID-5725 at 4/22/19 9:19 AM:
----------------------------------------------------------------

There are two aspects here, one is if there is documentation for the base (non-odata) case.  And the other is what is documented with regards to odata.  The former is mostly covered.  The latter is touched on only briefly.

Do you mostly need an expanded section in the OData docs discussing actions and functions as they relate to teiid procedures?

> Especially, how are OUT and INOUT params set and how can they be obtained via odata.

There is no concept of an OUT or INOUT parameter in OData.  There is only IN and RETURN.  A result set returned from a procedure is mapped to a complex return value.

> How is the return value of a virtual function set?

Are you asking from an OData or just sql perspective?   OData metadata currently only utilizes virtual functions where the function body is define with DDL.  This is somewhat by accident as they are in the metadata as both functions and procedures.  We should probably consider exposing all virtual and source functions as functions, rather than having special handling for just DDL defined virtual functions.

For core Teiid with a Java defined function (not exposed in OData) see: http://teiid.github.io/teiid-documents/master/content/dev/Support_for_User-Defined_Functions_Non-Pushdown.html

For just DDL see: http://teiid.github.io/teiid-documents/master/content/reference/DDL_Metadata.html Create Procedure/Function - I'll update this page to use a table of contents and unfortunately it does not explicitly show the usage of a RETURN statement http://teiid.github.io/teiid-documents/master/content/reference/Procedure_Language.html#_return_statement

{code}
CREATE VIRTUAL FUNCTION CustomerRank(customerid integer)
   RETURNS integer AS
   BEGIN
      ...
      RETURN expression;
   END
{code}

> How are collections and arrays of collections returned?

Collections/arrays as parameters are not allowed in OData - I recall logging an issue against Olingo 2.  

A result set is mapped automatically to a complex type collection result.  A array result will be mapped to a simple type collection.  

Multi-dimensional arrays are not supported by OData.


was (Author: shawkins):
There are two aspects here, one is if there is documentation for the base (non-odata) case.  And the other is what is documented with regards to odata.  The former is mostly covered.  The latter is touched on only briefly.

Do you mostly need an expanded section in the OData docs discussing actions and functions as they relate to teiid procedures?

> Especially, how are OUT and INOUT params set and how can they be obtained via odata.

There is no concept of an OUT or INOUT parameter in OData.  There is only IN and RETURN.  A result set returned from a procedure is mapped to a complex return value.

> How is the return value of a virtual function set?

Are you asking from an OData or just sql perspective?   OData metadata currently does not utilize Teiid defined functions. 

For core Teiid with a Java defined function see: http://teiid.github.io/teiid-documents/master/content/dev/Support_for_User-Defined_Functions_Non-Pushdown.html
For just DDL see: http://teiid.github.io/teiid-documents/master/content/reference/DDL_Metadata.html Create Procedure/Function - I'll update this page to use a table of contents and unfortunately it does not explicitly show the usage of a RETURN statement http://teiid.github.io/teiid-documents/master/content/reference/Procedure_Language.html#_return_statement

{code}
CREATE VIRTUAL FUNCTION CustomerRank(customerid integer)
   RETURNS integer AS
   BEGIN
      ...
      RETURN expression;
   END
{code}

> How are collections and arrays of collections returned?

Collections/arrays as parameters are not allowed in OData - I recall logging an issue against Olingo 2.  

A result set is mapped automatically to a complex type collection result.  A array result will be mapped to a simple type collection.  

Multi-dimensional arrays are not supported by OData.

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