Progress will discontinue Telerik Platform on May 10th, 2018. Learn more

Mapping Stored Procedures

Mapping Stored Procedures

Before you can execute a stored procedure or function using Telerik Platform you need to map it to an HTTP endpoint. You can do that either from the Telerik Platform portal or using the Backend Services RESTful API.

This article focuses on using the Backend Services RESTful API. For information on using the portal, see Getting Started with Stored Procedures.

To map a stored procedure, you need to take two steps:

Reading Metadata

Reading metadata from a datastore connected through a Data Connector allows you to get information about the available stored procedures and functions along with their parameters.

You need this information to programmatically map a stored procedure to an HTTP endpoint.

There are two types of metadata that you need to read:

Reading Stored Procedure Metadata

The following request reads all stored procedures available in the database that your Data Connector is pointing to:

Request:
    GET https://api.everlive.com/v1/Metadata/Applications/your-app-id/DataLinks/data-connector-id-here/Procedures
Headers:
    Content-Type: application/json
    Authorization: Masterkey your-master-key-here
Response:
    Status: 200 OK
    Content-Type: application/json
Payload - raw:
    {
        "Result":[
            {
                "Catalog":"telerik",
                "Schema":"dbo",
                "Name":"GetUpdatedRelations",
                "Type":2,
                "FullName":"dbo.GetUpdatedRelations"
            }      
        ]
    }

The result contains an array of objects each of which represents a stored procedure. See Raw Stored Procedure Object Fields for object field information.

Reading Stored Procedure Parameters Metadata

Mapping parameters is currently not supported when connecting to Salesforce. Requesting parameters metadata for Salesforce reports always returns an empty collection.

To obtain the list of parameters for a specified stored procedure or function you need the Name, Schema, and Catalog fields from the stored procedure's metadata. You can obtain these using the Read Stored Procedures from a Data Store operation.

After you obtain the necessary input data, you can substitute it in the following request to read the specified procedure's parameters as declared in the database:

Request:
    GET https://api.everlive.com/v1/Metadata/Applications/your-app-id/DataLinks/data-connector-id-here/Procedures/name=Name-here;schema=Schema-here;catalog=Catalog-here/Parameters
Headers:
    Content-Type: application/json
    Authorization: Masterkey your-master-key-here
Response:
    {  
        {
            "Result":[
                {
                    "Name":"@OperationType",
                    "Type":1,
                    "ByteLength":20,
                    "Position":1,
                    "TypeName":"nvarchar",
                    "EverliveType":"Text",
                    "DataType":12
                },
                {
                    "Name":"@TableName",
                    "Type":1,
                    "ByteLength":1000,
                    "Position":2,
                    "TypeName":"nvarchar",
                    "EverliveType":"Text",
                    "DataType":12
                },
                {
                    "Name":"@XML_ROW_PRESENTATION",
                    "Type":1,
                    "ByteLength":0,
                    "Position":3,
                    "TypeName":"xml",
                    "EverliveType":"Text",
                    "DataType":-1
                }
            ]
        }
    }

The result contains an array of objects each of which represents a stored procedure parameter. See Raw Stored Procedure Parameter Object Fields for object field information.

Create a Stored Procedure Endpoint

After you have read the metadata for a stored procedure, your next step is creating an endpoint for it.

The following request creates an HTTP endpoint for a specified stored procedure from your database. You can find more information about the payload fields in Mapped Stored Procedure Object Fields.

Request:
    POST https://api.everlive.com/v1/Metadata/Applications/your-app-id/Procedures
Headers:
    Content-Type: application/json
    Authorization: Masterkey your-master-key-here
Payload - raw:
    {  
       "Name":"getAccountsStats",
       "DataLinkId":"3c9838d0-1b2f-11e5-932b-ad6a8bf097b4",
       "ProcedureName":"GetAccountsStats",
       "ProcedureSchema":"dbo",
       "ProcedureCatalog":"telerik",
       "Mapping":[  
          {  
             "DefaultValue":null,
             "Name":"@AccountIds",
             "Type":1,
             "Position":1,
             "DataType":-1,
             "ServerName":"@AccountIds",
             "EverliveType":"Text",
             "Validators":{  
                "Required":true
             }
          },
          {  
             "Name":"@Result",
             "Type":2,
             "Position":2,
             "DataType":4,
             "DefaultValue":null,
             "Validators":{  
                "Required":false
             },
             "EverliveType":"Number",
             "ServerName":"@Result"
          }
       ]
    }
Response:
    Status: 201 Created
    Content-Type: application/json
Payload - raw:
    {
        "Result": {
            "Id": "5b7a73c0-1b30-11e5-94ef-cfd3e76e7fd9",
            "CreatedAt": "2015-06-25T12:00:30.469Z"
        }
    }

See Also

Start a free trial Request a demo
Contact us: +1-888-365-2779
sales@telerik.com
Copyright © 2016-2017, Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.