2 Replies Latest reply on May 29, 2014 7:00 PM by shawkins

    ODATA query to access stored procedure

    iginsk
    iginsk Mar 17, 2014 10:43 PM

      A stored procedure exists in SQL server 2010 that takes two string parameters and returns multiple column values from multiple tables.

      Created vdb for the same and deployed (teiid 8.4|DV 6.1.1) .

      I can access http://<host>:<port>/odata/<vdbname>?$metadata.

      How do I build ODATA URL to access the output of stored procedure.

      I have tried http://<host>:<port>/odata/<vdbname>/FunctionImportname(param1=value1,param2=value2), I get error FunctionImportname is not identified. Infact, the functionimportname is a combination of dbname.schemaname.virtualprocedurename and it exists in metadata. Any help?


      http://<host>:<port>/odata/<vdbname>/viewmodel.functionimport/?$format=JSON

      returns a blank output

      {

      "d" : [

       

      ]

      }

      http://<host>:<port>/odata/<vdbname>/viewmodel.functionimport/

      returns a blank output

      <d:Collection_view.database.schema.source_result_ xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices"xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"/>

        • 1. Re: ODATA query to access stored procedure
          lewis.watson
          lewis.watson May 29, 2014 9:41 AM (in response to iginsk)

          Hello Kiran.

           

          I have a Teiid User Defined Function that links through to a PostgreSQL stored procedure and I find that I need to use

           

          http://<host>:<port>/odata/<vdbname>/<functionname>?param1=x&param2=y

           

          In order to get data back something like

           

          <d:Collection_VDBView_procedure_view.inc_NewProcedureResult_><d:element m:type="Collection(VDBView_procedure_view.inc_NewProcedureResult)"><d:returnValue m:type="Edm.Int32">3</d:returnValue></d:element></d:Collection_VDBView_procedure_view.inc_NewProcedureResult_>

           

          When I try

           

          http://<host>:<port>/odata/<vdbname>/<functionname(param1=x,param2=y)

           

          I get

           

          <error><code>NotFoundException</code><message lang="en-US">TEIID16011 EntitySet "inc" is not found; Check the spelling, use modelName.tableName; The table that representing the Entity type must either have a PRIMARY KEY or UNIQUE key(s)</message></error>

           

          Hope this helps.

           

          Lewis

            • Actions
          • 2. Re: ODATA query to access stored procedure
            shawkins
            shawkins May 29, 2014 7:00 PM (in response to lewis.watson)

            Yes, the paren syntax is for identifying an entity rather than invoking an OData function.  See http://www.odata.org/documentation/odata-version-2-0/uri-conventions/ under Addressing Service Operations.

              • Actions