Calling a stored procedure from the REST API
- Mike Sheen
- 1 Overview
- 1.1 Prequisites
- 2 Jiwa Stored Procedure
- 2.1 Configure
- 2.2 Requests
- 2.3 Responses
- 2.3.1 Model / Response Class
- 2.4 Services
- 2.4.1 Service Method
- 3 Custom Stored Procedure
- 3.1 Configure
- 3.2 Requests
- 3.3 Responses
- 3.3.1 Model / Response Class
- 3.4 Services
- 3.4.1 Service Method
- 4 Related articles
Overview
Adding a route to return the results of a stored procedure, including with or without parameter passing is possible. This article shows how to invoke a either a standard Jiwa stored procedure , or your own custom stored procedure.
Prequisites
This article assumes you have read the Adding Custom Routes to the REST API article.
Jiwa Stored Procedure
Configure
A route must be defined - our preferred method is to do this in the configure method of the plugin:
Adding Route in Configure
AppHost.Routes.Add(typeof(MySearchRequest), "/Search/{SearchTerm}", "GET", "Performs a system wide search for a search term using a Jiwa stored procedure", "");Requests
This is where you would define any stored procedure parameters you want the consumer of the route to provide.
Defining Request with parameters
[ApiResponse(200, "OK")]
[ApiResponse(401, "Not authenticated")]
[ApiResponse(404, "No results found")]
public class MySearchRequest : IReturn<List<MySearchResult>>
{
public string SearchTerm { get; set; }
}
Responses
A class should be made to hold the response - this allows clients to benefit from strong typing.
Model / Response Class
// This class is holds the results from the stored procedure usp_JIWA_Search - one property for each column of the result set, with matching data types
public class MySearchResult
{
public string SourceID {get; set;}
public string SourceType {get; set;}
public string SourceDocumentNo {get; set;}
public string PhysicalWarehouse {get; set;}
public string LogicalWarehouse {get; set;}
public string MatchedOn {get; set;}
public string MatchValue {get; set;}
public string SY_Forms_ClassName {get; set;}
}
Services
Jiwa stored procedures have a method backing them in the JiwaServiceModel.StoredProcedures.SPList class (usp_JIWA_Search in this example) - so this method is invoked, passing any parameters required.
Service Method
Custom Stored Procedure
Configure
A route must be defined - our preferred method is to do this in the configure method of the plugin:
Adding Route in Configure
Requests
This is where you would define any stored procedure parameters you want the consumer of the route to provide.
Defining Request with parameters
Responses
A class should be made to hold the response - this allows clients to benefit from strong typing.
Model / Response Class
Services
Here we invoke a stored procedure using a string value, and pass the parameters.