Docs‎ > ‎CA Live API Creator‎ > ‎Create‎ > ‎Resources‎ > ‎REST APIs‎ > ‎

Data Integration

 RESTful servers are a compelling architecture because they are network aware.  So they are not only a great way to connect mobile devices and Web apps, but they provide Web Services: a great way to perform data integration functions.
Such Web Services exchange data in real time.  This is in contrast to previous batch-oriented approaches, which relied upon ETL (Extract, Transfer and Load).
API Creator provides a number of services designed to facilitate data integration, ranging from integrating retrieval over multiple data sources, to synchronizing updates.  This page outlines these services, and illustrates several examples.

Key Services

API Creator provides a number of services that are particularly useful in addressing common patterns for data integration.  These are outlined below.  It's important you have a general understanding of these, since they are used in the examples.

Resources for Name Mapping

One of the key requirements of data integration is Name Mapping: different systems name the same data differently (e.g., CustomerId vs AccountNumber).  Such mapping is required for receiving or sending data.  These are often called Transformations.
Custom Resources enable you to alias the names of tables and columns used to construct a Resource End Point.  The system provides the services to return GETs in this format, and update processing (PUT, POST and DELETE) to "de-aliasing" the resources so that they can share business logic.

Multi-Table Resources

Systems rarely need to exchange simply a flat set of rows (e.g., an Order Header), they need to exchange a set of related data often referred to as a Business Object (e.g., Order Header + Line Items + Shipment details).  Sub Resource support enables you to define such objects - including over multiple databases, as shown below:

In addition to automated SQL-based Sub Resources, you can also define Sub Resources based on other data source types: Mongo, JavaScript APIs (e.g., to other RESTful servers), and custom SQL.

Merge Insert

You can designate that post operations perform merge operations, such as when receiving data from an external system that may or may not already exist locally.

Business Logic

Business Logic is generally regarded as the code you write that retrieves data, and processes updates.  This involves not only the SQL, but also change propagation (e.g., paying an order reduces the customer balance).
API Creator provides Row Events that are triggered during GET operations, and various forms of Update Logic including update events.  These events are often used to invoke the APIs discussed below.

Get JSON from a Resource

You can invoke API Creator services to create a jsonString as defined by aResourceName:
  • jsonString = SysUtility.getResource(resourceName, details);
creates a jsonString using the named Resource and a filter, for example to be sent to a remote system.  
  • details is an optional JavaScript object

  • Normally pagesizeoffset and chunksize are defaulted (so not required, shown here in case you need them):

var details = {
    filter: "name like 'ABC%',
    order: "name asc",
    pagesize: 20,
    offset: 0,
    chunksize: 10

    REST Utilities

    You can invoke other RESTful services, for example from business logic or row events
    • SysUtility.restGet(url, params, settings)
    returns a Stringified version of the result of the GET to the specified url.

    • SysUtility.restPut(url, params, settings, requestData)
    returns a Stringified version of the response of a PUT to the specified url using the requestData (perhaps obtained through SysUtility.restGet)

    • SysUtility.restPost(url, params, settings, requestData)
    returns a Stringified version of the response of a POST to the specified url using the requestData (perhaps obtained through SysUtility.restGet)

    • SysUtility.restDelete(url, params, settings)
    returns a Stringified version of the response of a DELETE to the specified url.

    Settings is an option JavaScript object.  Only an id "headers" is examined.  Any other ids are ignored.

    var settings = {
        "headers" : {
           "Authorization" : "Basic base64string",
           "Cache-Control" : "no-cache"

    For more info see Wikipedia Entry to for HTTP Headers


    Here are some common scenarios we have seen.  The teal node denotes API Creator, the other nodes are other systems, which may be Enterprise Service Bus products, other API Creator Servers, NoSQL machines, etc.

    GET-triggered Integration

    The simplest examples are triggered by GET requests.

    Restify a SQL Database to a desired format

    Here, a partner's application server needed to deal with many different databases, each with their own similar but unique schema.  The app server coded wanted identical JSON for each, so adding a new database would not require any code changes.
    Their solution was to declare an API Creator Server for each database, where Resources were defined on each Schema to match a canonical format.  This required no code changes on either the app server or each database - a resource definition was all that was required.

    Mongo Integration

    You may need to integrate Mongo and SQL data into a common mashup API.  Direct Mongo support is described here.
    The next section addresses mashups with general RESTful servers.

    get mashup

    A common requirement is to enable a client to issue a get where the results return data from both a local database, as well as another RESTful service:

    You can specify JavaScript SubResources that can materialize subresources by invoking a RESTful service:
    In this particular example, the called RESTful service is another API Creator instance (from Demo), illustrating you can use API Creator as both a client (per the code above) and as a server (the called instance).  Certainly there are no restrictions - the called service can be any RESTful server - Mongo, Enterprise Data Sources, and so forth.
    Observe you can also use Row Events to compute data, even add new attributes.

    Time-triggered Integration

    The upsert example below illustrates a "push" from a related system.   At time, you need to poll and "pull" the data you need, periodically.  One common approach is to create a Chron job (outside API Creator), and issue RESTful requests to API Creator.

    Update-triggered integration

    It is also common that updates (put, post, delete) might trigger integration.  You can use business logic update events to address these as outline below.

    upsert External Request

    You RESTful service may need to process data submitted by another service, perhaps a Master Data node.  It may also be required that the Master dictate the names of the submitted objects / attributes:
    You can define Resources whose aliased names match the partner system.
    This scenario is also common in a Master Data Management configuration, where the Master may send changes to subscribing systems.  It is often the case that the submitted data may - or may not already exist in your site.  API Creator therefore supports the Merge Insert functionality, where the posted data may be regarded as either an insert or is merged in as an update.

    post to External System

    Your service may need to publish JSON changes to other systems, in the form of multi-table business objects whose names match the target system.

    A good way to approach this is:
    1. Define a Resource whose aliased table/attribute names match the target (cust in the example below)
    2. Provide an update event that invokes resourceGet to create a JSON string for this resource, and send it to the target (e.g. as a post)

    get External Data for current transaction

    Your business logic may need to 
    1. obtain data from an external system in order to process a transaction. 
    2. transform the external data into a local names
    3. store the external data, including transformations / derivations (e.g., sums, counts)
    4. use the resultant local data in the current transaction (e.g., for validation)
    A reasonable approach would be:
    1. Define a resource R whose alias names match the external system
    2. Use restCaller to get the external data
    3. Use resourcePost to post this data into R



    Integrated Example

    JavaScript events can call local and remote REST services.  This enables you to integrate multiple systems together.  In the example below:
    1. Partner Request: a Partner sends an Order to us - a RESTful POST
    2. Compliance Message: we process it, where part of the business logic detects that the order includes a controlled substance which requires we send a Controlled Substance Message to a Regulatory Agency
    3. Partner Response: the Return Response to the Partner is an Account Summary.  This is in lieu of our normal transaction summary, which is intended for client refresh processing.

    Live API, operating in conjunction with Live Logic, enables us to process this entire transaction with several rules and the Live API JavaScript below - about 20 lines of JavaScript.  API Creator provides all the other REST/ SQL handling.
    It would also be invoked as a consequence of an interactively entered order.  
    Note that the logic is not coded into some button's controller in a mobile/web app - the logic is automatically partitioned for re-use. 

    Partner Request

    This is a simple RESTful request, such as POSTing an order and 2 items.  Here, we POST the following JSON in the Logic Sample database to REST end-point cust.orders_c, 
     "customer": "Max Air",
     "IsReady": true,
     "items_o": [
        "ProductName": "Dynamite",
        "QuantityOrdered": 1
        "ProductName": "Hammer",
        "QuantityOrdered": 1
    In later versions of Logic Sample, you can also post this to PartnerOrder:

     "customer": "Max Air",
     "isReady": true,
     "LineItems": [
        "productName": "Dynamite",
        "quantityOrdered": 1
        "productName": "Hammer",
        "quantityOrdered": 1

    Compliance Message

    Certain substances are controlled, and require regulatory reporting.  We can use Live Logic to define an Event on the products table:

    For simplicity, this logs the message instead of sending it; look for a log entry like (ensure your API Keys debug setting are correct):
    ***sending message

    This logic is invoked by the Logic Engine as a consequence of processing the order, as follows.  Since we are POSTing the order as IsReady=true, this invokes the Make Ready logic; the event is triggered as a consequence of the adjustment to the Product.totalQtyOrdered.

    Partner Response

    The normal response JSON is the transaction summary.  But in this case, we want to reformat the response per our understanding of our partners' message formats - object and attribute names.  The solution is simple:
    1. We define a RESTful resource that maps our tables onto their names, using Resource aliases to provide the proper names
    2. We define a Request Event, where we can override the normal response, like this:
    You can see the accountSummary field (added in the last line, above) in the response JSON, shown in the first screen shot above.