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 following code 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 use well-known JavaScript debugging tools to monitor execution, network traffic etc in the usual manner.

Retrieval

The following includes detail programming information on retrieval. The following sub-sections 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. 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, that is, every row has the same columns and columns are always single-valued, 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. Filters provide a powerful mechanism and remove presumptions in the code regarding the key. The following is an example:
 
https://val2.my.espressologic.com/rest/val2/demo/v1/customer/Alpha and Sons

Security

Live API Creator applies security automatically 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, API Creator updates the @metadata link to designate secured columns.

For more information:

Filter and OrderBy

REST retrieval requests commonly specify filtering and ordering for the top-most Resource. 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 the URL Encode/Decode tool).

For example, we can use a system-defined filter (sysfilter), as shown in the following image:

In this example, we use a SQL where (As Defined), as shown in the following image:
Note: This filter is more susceptible to injection.
The following is a GET using a simple filter:
https://val2.my.acme.com/rest/val2/demo/v1/customer?filter=name%3D%27Alpha+and+Sons%27

The following 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 OR and 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: Use a structured filters (as a URL argument):

?sysfilter(name: 'Alpha and Sons')

For more information:
  • About structured filters, see Structured Filters.
  • About other parameters for GET, 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.

You can specify more elaborate Sub Resource filters (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.

Test in the REST Lab

You can test GET using the REST Lab. You can simpligy testing by eliminating the need for HTTP escape characters by selecting the Args checkbox. When using this option, use single quotes.

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:
}, {
    "@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. 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. 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 in the following code:

"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...