-
1. Re: Question about the return type of Teiid native query
rareddy Feb 25, 2014 6:24 PM (in response to haifen_bi)Change it to
CREATE VIRTUAL PROCEDURE nextval(IN tableName String) RETURNS string
You can return either scalar or table based (resultset) values. You can also you use OUT params if you want. See DDL Metadata - Teiid 8.7
Ramesh..
-
2. Re: Question about the return type of Teiid native query
shawkins Feb 25, 2014 6:49 PM (in response to rareddy)You have several options here. One is to use a the native call as you have above. It returns a resultset of object arrays. To build on what Ramesh shows, assuming that a single row is returned you'll need to pull out the result. You can do this with array_get against a scalar subquery:
CREATE VIRTUAL PROCEDURE nextval(IN tableName String) RETURNS string
AS
BEGIN
SELECT array_get(call native('select dbo.generate_key(10, newid())'), 1);
END
Another option is use a UDF, with DDL as (and assuming that tableName is unused):
CREATE FOREIGN FUNCTION nextval(IN tableName String) RETURNS string OPTIONS ("teiid_rel:native-query" 'dbo.generate_key(10, newid())')
Here you can call nextval just as any other function in Teiid and we'll understand to replace the source sql appropriately.
Or you can take a similar approach to define a FOREIGN PROCEDURE. With these latter approaches, we'll understand from the metadata what to expect from the resultset.
Steve
-
3. Re: Question about the return type of Teiid native query
haifen_bi Feb 26, 2014 12:23 AM (in response to shawkins)Thanks Steven and Ramesh for the quick responses.
using array_get(), now I am getting system generated ID returned from DB function.
Thank you all again!