One of the key pillars of Espresso Logic is the extensibility using JavaScript to call stored procedures. Many companies have COTS applications that use stored procedures to encapsulate specific logic. When they come to Espresso and ask about exposing REST services, they wonder and worry about how they will expose their logic.
First, Espresso Logic reads the schema of most modern databases and creates an ORM for each table, view, and stored procedure. The Instant REST nature of connecting to SQL makes this a good place to start. These objects in turn become the default REST endpoints and become the building blocks for Custom Resources (user defined nested documents) and business logic services. This post will explore exposing Stored Procedures as first class citizens in the REST and Logic area.
Call a Stored Procedure
All REST requests look similar in that they are HTTP GET calls to a server. The URL may vary based on the account and project but the concept of calling a procedure is identical to calling a table or view. However, the arguments are handled a bit different. In this example, the procedure has an argument ?given_employee_id? which is prefaced with ?arg.? similar to how C# may use the @ in binding a variable.
http://espressologic.com/rest/el-dev/sample/v1/demo:get_employee?arg.given_employee_id = 1Response
The response from a call to a procedure is a bit different in Espresso Logic. The system will return a JSON response that includes the arguments list, the column meta information, and any row results. This is helpful to the caller to understand what is being returned. Since a Sproc may return multiple result sets and multiple rows inside a result.
?arg?: {
?given_employee_id?: 1
},
columnMeta?: [
{
?catalog?: ?dbdev_demo?,
?schema?: ??,
?tableName?: ?PurchaseOrder?,
?columnName?: ?order_number?,
?columnLabel?: ?order_number?,
?columnType?: ?BIGINT?
}, ? partial list
?rows?: [
{
?order_number?: 7,
?amount_total?: 1860,
?paid?: false,
?notes?: ??,
?customer_name?: ?Echo Environmental Services?,
?salesrep_id?: 1
}, ? partial list
?rows?: [
{
?employee_id?: 1,
?plus_one?: null,
?login?: ?sam?,
?icon?: {
?type?: ?base64?,
?length?: 1185,
?value?: ?iVBORw0K?.?
},Procedures as Formula
Knowing that a procedure can be called as a REST resource we now combine the power of declarative logic to use these in our formula. A formula is an expression that returns a computed result. For example, we may want to create a new order in our order entry system. We allow our customer to pass in their customer information and item/quantity as a single POST in a nested document. Our existing system has a series of procedures that are called to create batch headers, order dates, shipping locations, warehouse notifications, order numbers, line sequences, kit part explosions, customer specific product item pricing.
Our legacy code may have been one large block of code or a series of functions calls which bind various user values as arguments to create the various intermediate values. We already understand how unit testing works ? that is ? we want to test the smallest unit of work, so in Espresso we create a formula on a table that contains both the user provided values and the system generated values. These formula will automatically be called in the correct order (much like a spreadsheet determines the correct order of calculations using an internal dependency tree).
Formula as a Pattern
Each of the attributes in the model are either provided by the client (customer number, ship method warehouse location) or they are calculated using a formula which will call a procedure. The pattern is the same for each formula ? identify the field, build up the argument list, process the procedure, and handle the results. In this example, we get the next line sequence no using the order no already calculated from a prior formula.
In this example, we call a procedure and pass in the order number (?row? is a full object model of the current table). We make an call, process the result and extract the new value.
Javascript Formula
var seqno = row.LineSequence;
if(seqno === null && row.OrderNumber !== null) {
var sproc = req.BaseUrl + 'v1/_getNextLine?arg.orderNo='+row.OrderNumber;
var settings = { headers: {Authorization: "Espresso data_full:123"}};
var result = SysUtility.restGet(sproc,null,settings);
var response = JSON.parse(result);
log.debug(result);
seqno = response.result[0].lineseqno;
}
return seqno;
Additional Attributes
One nice feature is that Espresso Logic will allow REST callers to POST additional attributes that are not found on the current table. To enable this feature, you need to tell the system to Ignore extra attributes. This can be done by passing it on the URL (IgnoreExtraAttributes=true) or by using a Request Event for specific POST resource names req.setUserProperty(?IgnoreExtraAttributes?,true);
In my code ? I would use the following to see if these extra attribute are present
var obj = JSON.parse(req.json);
var state = null;
if(obj[0].hasOwnProperty(?state?)) state = obj[0].state;What about GET
One feature that is often overlooked is the Response Event. What I did is create a Customer Resource for each GET stored procedure using type ?JavaScript? and simply return {}; (empty set). So in this example, we create a custom resource named getCustomerInfo. Now we create a response event looking for this named resource, call our stored procedure, and extract the response and append it to JSON.
if(req.verb == ?GET? && req.resourceName == ?getCustomerInfo?){
var pname = req.getUserProperties().get(?name?);
var url = req.baseUrl + ?v1/demo:getCustomer?arg.name = ??+pname +???;
log.debug(url);
var response = JSON.parse(SysUtility.restGet(url,null,settings));
json.info = response.result.rows;
}Summary
Using a REST server to integrate with your existing ERP or SQL backend can be quick and easy. Adding logic to move your stored procedures to the server in a few lines of Espresso makes it trivial to extend your existing investment and allow your to build new mobile and web front-ends. Take a look at Espresso Logic today for a free trial and connect your own procedures to rules.
