[JBoss JIRA] (TEIID-5725) Please improve documentation on definition of stored procedures, virtual functions and virtual procedures
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5725?page=com.atlassian.jira.plugin... ]
Steven Hawkins edited comment on TEIID-5725 at 4/22/19 11:11 AM:
-----------------------------------------------------------------
> 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
{code}
[{a='aval1', b='bval1'}, {a='aval2', b='bval2'}, ...]
{code}
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
{code}
['val1', 'val2', ...]
{code}
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...
And see the grammar as well: http://teiid.github.io/teiid-documents/master/content/reference/BNF_for_S...
> 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 ...";
was (Author: shawkins):
> 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...
And see the grammar as well: http://teiid.github.io/teiid-documents/master/content/reference/BNF_for_S...
> 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)
5 years, 8 months
[JBoss JIRA] (TEIID-5725) Please improve documentation on definition of stored procedures, virtual functions and virtual procedures
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5725?page=com.atlassian.jira.plugin... ]
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...
And see the grammar as well: http://teiid.github.io/teiid-documents/master/content/reference/BNF_for_S...
> 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)
5 years, 8 months
[JBoss JIRA] (TEIID-5725) Please improve documentation on definition of stored procedures, virtual functions and virtual procedures
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5725?page=com.atlassian.jira.plugin... ]
Christoph John commented on TEIID-5725:
---------------------------------------
Hello Steven,
thanks for the answer.
>> 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 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.
>> 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.
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?
>> I'll update this page to use a table of contents and unfortunately it does not explicitly show the usage of a RETURN statement
Yes having a return statement in the examples for a virtual procedure and function has helped
>> A result set is mapped automatically to a complex type collection result. A array result will be mapped to a simple type collection.
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?
I am currently trying to get some functions implemented in DDL. It looks like I have quite some trouble with the syntax. So what kind of SQL version is used here? Could you recommend a tutorial on it?
For example I expected this to work:
DECLARE retVal INTEGER DEFAULT 1;
However, looks like the correct syntax looks like this:
DECLARE INTEGER retVal = 1;
And I tried using count and select into, like follows:
SELECT COUNT(Weekday) INTO retVal FROM NutritionGoal WHERE fkProfile = 2;
but the statement does not work as probably the syntax is also wrong in Teiid.
It would be great if you could provide a link to a tutorial for the relevant sql version that Teiid is using in DDL and add a info to the documentation as well.
Thanks for your help.
> 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)
5 years, 8 months
[JBoss JIRA] (TEIID-5725) Please improve documentation on definition of stored procedures, virtual functions and virtual procedures
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5725?page=com.atlassian.jira.plugin... ]
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_Use...
For just DDL see: http://teiid.github.io/teiid-documents/master/content/reference/DDL_Metad... 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...
{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_Use...
For just DDL see: http://teiid.github.io/teiid-documents/master/content/reference/DDL_Metad... 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...
{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)
5 years, 8 months
[JBoss JIRA] (TEIID-5725) Please improve documentation on definition of stored procedures, virtual functions and virtual procedures
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5725?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5725:
---------------------------------------
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_Use...
For just DDL see: http://teiid.github.io/teiid-documents/master/content/reference/DDL_Metad... 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...
{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)
5 years, 8 months