Question about the return type of Teiid native query

[haifen_bi]

We need call a DB function for system generated keys and expose it as stored procedure in the view model. Following is the DLL:

<model name="EDM_NEXVAL" type="VIRTUAL" visible = "true" >

  <metadata type="DDL"><![CDATA[     

    CREATE VIRTUAL PROCEDURE nextval(IN tableName String) RETURNS (SysGenId object)

            AS

            BEGIN

                   SELECT p1.* from (call native('select dbo.generate_key(10, newid())')) AS p1;

            END    

  ]]> </metadata>

</model>

EXEC above SP returns a resultset object. But what we need is the "String" from native DB function call. How can we get the actual system generated ID (a string) from SP resultset? Any help is highly  appreciated.

Thanks!

[rareddy]

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

[shawkins]

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

[haifen_bi]

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!