3 Replies Latest reply on Feb 26, 2014 12:23 AM by H B

    Question about the return type of Teiid native query

    H B Newbie

      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!

        • 1. Re: Question about the return type of Teiid native query
          Ramesh Reddy Master

          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
            Steven Hawkins Master

            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
              H B Newbie

              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!