Skip navigation
All People > banty01 > Tyler Band's Blog > 2015 > December > 18

Tyler Band's Blog

December 18, 2015 Previous day Next day

Many companies have COTS (commercial off-the-shelf) applications that use stored procedures to encapsulate business logic.  When they come to Live API Creator and ask about  REST services, they wonder and worry about how they will expose their logic.


First, API Creator 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 Resources (user defined nested documents) and business logic services. This post will explore exposing Stored Procedures as first class citizens as a REST endpoint and calling these procedures in logic rules.


Calling a Stored Procedure using REST

All REST requests look similar in that they are HTTP calls to an API 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 get_employee has an argument ‘given_employee_id‘  which is prefaced with ‘arg.’ similar to how C# may use the @ in binding a variable. = 1



The response from a call to a procedure is a bit different in API Creator.  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

Procedures and 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 API Creator 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).

Our existing system does not expose a data model that represent something we can expose as a REST API.  So we create a new set of tables that contain only the required attributes (and the attributes we will use as formula to calculate and call our stored procedures).  This model is like an audit record of every POST/PUT/DELETE sent to our system.  This request pattern allows us to add reactive logic rules and events to interact with the base system to insert, update, and delete orders, notify warehouse for shipping, send emails to various actors, etc.


Reactive Logic will automatically order our rules so if we POST a new Order and one or more Items - the system can calculate formula values for the batchNo, OrderNumber, warehouse to ship from, item sequence, price lookup, discounts, availability, kit explosion, etc.  Each of these field values are calculated using values returned from a stored procedure.

A formula would simply call the helper function var result = SysUtilty,getResource(procUrl, params) - the procURL is the name of the procedure and its arguments (shown above).  The result is parsed to get the result.rows[0].attributeName;




Calling a Stored Procedure using GET

One feature that is often overlooked is the Request/Response Event.  Simply create a 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? = ‘”+pname +”‘”;


var response = JSON.parse(SysUtility.restGet(url,null,settings)); = response.result.rows;





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 JavaScript makes it trivial to extend your existing investment and allow your to build new mobile and web front-ends.

In this post, we will look at the features provided for auditing or user transactions, logging, and metrics.  We will also look at how to use MongoDB to store our audit records.

I often am asked about our logging features and then find out that our customer wanted an audit transaction service instead.  The loggers are a record of what happened for a specific transaction (both internal system functions and user defined logging).  Live API Creator has a wide range of information that can be logged and various loggers can be turned on/off or set to different levels of granularity.


The Audit transaction service is a more specific in that it will record who made a change to system data, when the change was made, the entity object(s) involved, the values  before and after and any related transaction summary (that is, did this transaction impact other objects).


SQL Metrics are a record of each SQL query, both user and system, and the response time required in the execution of the request.  This can help with system performance tuning as well as identify any bottlenecks in REST calls.



The loggers are tied to each REST request, GET, POST, PUT, DELETE and a specific Auth Token.  The loggers are based on the common log4J pattern (Off, Error, Warning, Info, Debug, Fine, Finer, Finest).  An Auth Token can be created by user authentication or by creating a predefined role based value used for specific access to internal API objects.  The predefined Auth Token can be used to set different internal logging values (Administration, Business Logic, Dependency Analysis, General, Database Access, System ,  Security).


The API Creator Log is shown below for a specific Auth Token GET REST call.




Audit Transactions

The audit user transaction service is enabled for each API project by checking the API Properties/Details check box (Audit User Transactions) for all POST, PUT, DELETE.  If a named Resource is created named _USER_TX_AUDIT_ with a server, database, and collection name, then all future audit records will be appended to MongoDB instead of SQL  The action_type (I,U,D, @) correspond to the REST types (insert, update, delete, @ – authentication or function)



Using MongoDB Log to store Audit records
The design of the Audit feature will allow users to enable the Audit service for each API project.  Additionally, the service can append audit records to MongoDB by adding a custom resource to the project  (resource name _USER_TX_AUDIT_) with the server, database, and collection name.

Screen Shot 2015-12-18 at 3.31.13 PM.png

View the Audit Transactions in REST Lab using the Resource

SQL Metrics

API Creator provides an internal service that tracks all SQL requests (both user requests and internal system) showing the actual SQL Query and the statistics for each query.


Total# execsAvg time
select ident ,ts ,level_stored ,description fromdblocal_sample.valid_customerlevel el$thiswhere (ident = ?)order by ident limit 2102.9 msec721.4 msec
First execution:33230 days ago
Last execution:33230 days ago
Shortest execution:466 µsec
Longest execution:7.9 msec




Live API Creator provides these features for logging internal system and logic changes, audits of user change transactions and SQL Metrics to measure system performance. Combined with MongoDB to store and view changes gives the enterprise developer a 360 view of the user and system processes.

A Node.js command-line tool to access CA Live API Creator REST API and Logic services. Refer to online documentation of creating and using  REST API

Locate the source from here.

  1. Make sure node.js is installed
  2. Install using npm by running the following:

$ npm install -g liveapicreator-cli


$ liveapicreator --help


  Usage: liveapicreator \[options] [command]




    login [options]               Login to an API server

    logout [options]              Logout from the current server, or a specific server

    use <alias>                   Use the specified server by default

    status                        Show the current server, and any defined server aliases

    get <resource> [options]      Retrieve some data for the given resource/table/view

    post <resource> [options]     Insert some data

    put <resource> [options]      Update some data

    delete <resource> [options]   Delete some data

    describe <resource> [options] Describe the specified resource, can be: tables[/tablename], views[/viewname], procedures, resources, license, serverinfo




    -h, --help     output usage information

    -V, --version  output the version number


$ liveapicreator login http://localhost:8080/rest/default/demo/v1 -u username -p mypassword

Logging in...

This server licensed to: Live API Creator

Login successful, API key will expire on: 2015-11-18T15:03:37.342Z


$ liveapicreator get employee


demo:employee/1 employee_id:1 login:sam name:Sam Yosemite

demo:employee/2 employee_id:2 login:mlittlelamb name:Mary Little-Lamb

demo:employee/3 employee_id:3 login:sconnor name:Sarah Connor

demo:employee/4 employee_id:4 login:jkim name:John Kim

demo:employee/5 employee_id:5 login:bmcmanus name:Becky McManus


The Live API Creator has a 'New API' button that requires a SQL connection to create a new default project.  How can I create a new API project before I have my SQL credentials?  We have 2 solutions - the first involves using the admin REST API services (found in the docs) - this involves using the same REST endpoints that the API Creator uses to get auth tokens and doing a POST to create a new project.


POST https://myserver[:port]/rest/abl/admin/v2/admin:projects


  "name": "My new API",

  "url_name": "myapi",

  "is_active": true,

  "account_ident": 99999,

  "authprovider_ident": 9999


notice that the URL is a bit different using /abl/admin/v2 - this is the internal REST endpoint.


The other option is to use a NodeJS library that was written as a command line interface for administrators. 

Once you download and install NodeJS you can locate and install the admin command line tool and find the documentation for this here


     $npm install liveapicreator-admin-cli -g

$liveapicreatoradmin login <url> -u <user-name> -p <password> [-a <alias>] 

The URL will normally be the address of the server, such as:


Once you have connected to your API Server, simply execute the command line interface shown below.


$liveapicreatoradmin project create --project_name <name> --url_name <url_name> [--status <A|I>]

  [--comments <comments>] [--verbose]


Your project will appear on the Home page and you can add data sources manually or create custom Resources that do not need a data source.