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

Get


JavaScript Example

REST/JSON invocation is a standard practice, available in virtually every modern language.  Here we illustrate a an example in JavaScript.

You can examine the code below in the Demo sample application.  
  • The top window is invoking makeCall, with a closure to process the JSON result (a Customer, stored into currentCustomer)

  • The lower window shows a portion of makeCall where the Rest call is issued and the closure is executed
You can of course use well-known JavaScript debugging tools to monitor execution, network traffic etc in the usual manner.



Retrieval

See here for detail programming information on Retrieval.  The sub-sections below summarize the key concepts.

Database Abstraction

In most cases, you retrieve data through explicitly defined Resources.  The resource/attribute names are distinct from the underlying database column names.  So, you can devise more appropriate names, and insulate your client applications from subsequent changes in the database structure.

Rich Objects: Client simplification, Network Latency

Conventional database operations that retrieve "flat" results: every row has the same columns, and columns are always single-valued.  This does not always serve well the needs of client applications which require data of multiple types (Customer, Order) in a hierarchy.

API Creator Resource definition enables you define such "rich" resources, as illustrated in the following excerpt that shows that the Orders attribute for a customer is a list of Orders.

[ {
  "@metadata" : {
    "href" : "http://localhost:8080/APICreator/rest/demo1/Customers/Alpha%20and%20Sons",
    "checksum" : "595d608c67056bf308fd36e48a3d4916"
  },
  "name" : "Alpha and Sons",
  "Balance" : 85,
  "CreditLimit" : 900,
  "Orders" : [ {
    "@metadata" : {
      "href" : "http://localhost:8080/APICreator/rest/demo1/Orders/1",
      "checksum" : "7aa03a46f8a6d40755c22d233b32eddf"
    },
    "order_number" : 1,
    "TotalAmount" : 35,


Get by primary key

You can retrieve an object by appending "?" and its primary key to the resource, as shown below.  (Filters, discussed below, provide a more powerful mechanism, and remove presumptions in the code regarding the key).  Here is an example:

 
https://val2.my.espressologic.com/rest/val2/demo/v1/customer/Alpha and Sons
 

Security

Security is automatically applied to all retrieval requests.  This applies to Resources that pre-date security specifications.

Column-instance security means that a column might be available for some rows, but not others.  Secured columns are delivered as null values.   So that clients can distinguish these from actual stored null values, the system update the @metadata link to designate secured columns.

You can browse the Security Examples.

Filter and OrderBy

REST retrieval requests commonly specify filtering and ordering for the top-most Resource.  See below for Sub Resource Filtering.

Since these are coded into the URL, proper escape sequences must be employed.  The REST Lab provides a simplified mechanism to specify filters using the Get option (or, you can use this tool).  



Here is a GET using a simple filter:

https://val2.my.acme.com/rest/val2/demo/v1/customer?filter=name%3D%27Alpha+and+Sons%27


Here is a GET request for customers with name < 'Shari', ordered by name (descending):
http://localhost:8080/APICreator/rest/abl/sample/v1/customers?filter=name%3C%27Shar%27&order=name%20desc,balance


Filters are sql WHERE clauses, so you can use familiar functions such as LIKE:

https://val2.my.acme.com/rest/val2/demo/v1/customer?filter=name%20like%20%27Alpha%%27


Other SQL rules apply as well, such as interchanging quotes for double-quotes, checking for null (e.g., filter=name+IS+NOT+NULL), and so forth.

If you are calling Stored Procedures, you can pass in their arguments - not as a filter expression, but as an argument like this:

https://demodev.acme.com/rest/el-dev/advwrk/v1/dbo%3AuspGetEmployeeManagers?arg.BusinessEntityID=2

And, if you are using a database with quoted identifiers, specify the following in the RESTLab filter field:

"name" = 'Alpha and Sons'


Best practice is use of Structured Filters (as a URL argument):

?sysfilter(name: 'Alpha and Sons')


Explore the other parameters for GET here (see ResourceList and ResourceSingle).


Sub Resource Filter

You can also specify fully-qualified filters on Sub Resources that filter them, like this on the Resource CustomerBusinessObject:

?filter.CustomerBusinessObject.Orders=amount_total>10000

Note these affect retrieval of only the designated resource, that is, they do not affect parent joins.  For example, a Resource of Customers and Orders with the above filter would not filter Customers.  You can achieve such effects with more elaborate examples as shown below.

You can specify more elaborate Sub Resource filters as shown below (though be careful about performance etc), such as this to retrieve only those customers with orders in excess of 1000:

CustomerBusinessObject?filter.CustomerBusinessObject=exists ( select * from <schema>.PurchaseOrder co where amount_total>1000 and co.customer_name = name)





Filters use Tables Names (not alias names)

Filters should be specified using non-aliased column names, not aliases.  This arises out of the following SQL consideration.

The long and short of it is that the following SQL query:

select name as TheName from Customer where TheName = 'Acme'  -- This is invalid

is not a valid SQL query -- in any SQL database. It is simply not possible to use a column alias in a where clause. That arises from the SQL standard.

The valid form for this query is:

select name as TheName from Customer where name = 'Acme'  -- This is valid

Many more explanations for this can be found here.


Testing in the REST Lab

You can test GET using the REST Lab.  Note that the Args option simplifies testing by eliminating the need for HTTP escape characters.  In using this option, be sure to use single quotes, as shown below.




Pagination

The server supports large result sets configurable pagination, which you can configure as a project default, and override with a pagesize query parameter such as:

/Customers?pagesize=10


To enable you to obtain further results, the system delivers a special "pagination" object as shown below.

}, {
    "@metadata" : {
        "next_batch" : "http://localhost:8080/APICreator/rest/demo1/Customers?pagesize=10&offset=10&order="
    }
}]

You can use the next_batch uri value to obtain the next "page" of results, which will include all Sub Resources.  Note that offset may not change as you might expect.  Row Event and security will affect this value so that it may not move in increments on pagesize.


Multi-Level Pagination

Pagination is provided at each level of nesting for Nested Resources.  So, you can retrieve the next batch of customers, or the next batch of orders within a customer.



Linked Resources

In large applications, it may be desirable to load all the information required for a page in 1 transmission, but also provide buttons that navigate to other pages to "zoom in" to detail information.  For example, a Customer page might show orders and line items with product name and price, with a page transition button to see detail product information.

This scenario is supported by defining Linked Resources.  These are returned as link objects containing href uri's to retrieve related resources, as illustrated below:

"lineitem_id" : 11,
"ProductNumber" : 2,
"OrderNumber" : 6,
"Quantity" : 2,
"Price" : 25,
"Amount" : 50,
"Product" : {
  "@metadata" : {
    "href" : "http://localhost:8080/APICreator/rest/demo1/Product/2",
    "checksum" : "A:48902e67a13d55bea694c7ff99ff35bb",
    "links" : [ {
        "href" : "http://localhost:8080/APICreator/rest/demo1/ProductDetailLink?filter%3Dproduct_number%3D2",
        "rel" : "children",
        "title" : "ProductInfo",
        "type" : "ProductDetailLink"
      }]
     },
  "product_number" : 2,
  "Name" : "Shovel",
  "Price" : 25
  }

Response format

The format of the response can be controlled by a project option, but it can be overridden on a request basis by adding an HTTP header to the request. The header's name is X-CALiveapi-ResponseFormat, and the value should be the desired format. As of this writing, there are only two valid values:
  • json is the standard format
  • jsonObject specifies a different format for GET responses that includes the data in an object:

{
  "success": true,
  "data": [
    {
      "@metadata": {
        "href": "http://localhost:8080/APICreator/rest/el-local/demo/v1/Customers/Alpha%20and%20Sons",
        "checksum": "A:e86aea2e0a4e74bf"
      },
      "Name": "Alpha and Sons",
      "Balance": 4484,
      "CreditLimit": 9000,
      "Orders": {
        "data": [
          {
            "@metadata": {
              "href": "http://localhost:8080/APICreator/rest/el-local/demo/v1/Customers.Orders/1057",
              "checksum": "A:c55ae9a21faa5a45"
            },
            "OrderNumber": 1057,
            "TotalAmount": 0,
etc...

The jsonObject (set using API Properties - Settings) format also puts the next_batch and previous_batch links out of the arrays of objects, e.g.:

{
  "success": true,
  "next_batch": "http://localhost:8080/APICreator/rest/el-local/demo/v1/demo:LineItem?pagesize=5&offset=15",
  "previous_batch": "http://localhost:8080/APICreator/rest/el-local/demo/v1/demo:LineItem?pagesize=5&offset=5",
  "data": [
    {
      "@metadata": {
        "href": "http://localhost:8080/APICreator/rest/el-local/demo/v1/demo:LineItem/11",
        "checksum": "A:2e3d8cb0bff42763",
      },
      "lineitem_id": 11,
      "product_number": 2,
      "order_number": 6,
etc...