Invoking APIs‎ > ‎

GET

Retrieve rows from an endpoint, a table, a custom resource, or a custom endpoint using the GET verb.

Note: Get calls require an auth token and results depends on the users roles.

JavaScript Example

REST/JSON invocation is a standard practice, available in virtually every modern language. The following image illustrates a an example in JavaScript on the Execute, API Docs, Code samples tab:

Note: The content on this tab reflects your current API.

Retrieval

The following sub-sections summarize the key concepts of retrieval, including detailed programming information on retrieval.

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 and 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. You can define such "rich" resources by creating a resource definition, as illustrated in the following excerpt, which show 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, for example:

https://server.acme.com/rest/default/demo/v1/customer/Alpha and Sons

Filters provide a powerful mechanism and remove presumptions in the code regarding the key.

Security

Live API Creator applies security automatically to all REST retrieval (GET) 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

GET requests commonly specify filtering and ordering for the top-most resource. Since these are coded into the URL, you must employ proper escape sequences. You can specify filters in a GET using the REST Lab. Click the Args checkbox to view the filtering and ordering section.

Note: You can also specify filters using the URL encode/decode tool. For more information about the URL encode/decode tool, see the URL Encode/Decode tool.

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


In this example, a SQL where (As Defined) is used, as shown in the following image:

Note: This filter is more susceptible to injection.

The following URL 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 URL 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, for example:

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 (for example, 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

If you are using a database with quoted identifiers, in the filtering section of the REST Lab, click Add Filter and specify the following in filter field:

"name" = 'Alpha and Sons'

Best practice: Use a structured filter (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

Sub-resource filters affect retrieval of only the designated resource, that is, they do not affect parent joins. For example, a Resource of Customers and Orders with this filter does not filter Customers. You can achieve these effects with more elaborate examples.

You can specify more elaborate sub-resource filters. For example, the following filter retrieves only those customers with orders in excess of 1000:

Important! Use caution when specifying these kinds of filters. They can affect performance, etc.

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

The following image shows the filter in the URL field on the Execute, REST Lab, Request tab:

Specify Filters using Column Names

Specify filters using non-aliased column names in where clauses and not column aliasesThis arises out of the following SQL consideration.

For example, the following SQL query is valid:

select name as TheName from Customer where name = 'Acme'

The following SQL query is not valid:

select name as TheName from Customer where TheName = 'Acme'

For more explanations about why you cannot specify column aliases in where clause, see the Beyond Relational site.

Test in the REST Lab

You can test GET using the REST Lab. You can eliminate the need for HTTP escape characters and simplify testing by selecting the Args checkbox. Supply arguments in the URL.

Note: When using this option, use single quotes.

The following image shows the Args checkbox on the Execute, REST Lab, Request tab:

Linked Resources

In large applications, it may be desirable to load all the information required for a page in one 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 an option 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
  }

jsonObject Response Format

You can set the default format for the response for the entire API project. You can override this setting on a per-request basis by adding an HTTP header to the request. The header's name is X-CALiveapi-ResponseFormat and then enter the value for the desired format. The jsonObject response format specifies a different format for GET responses that includes the data in an object. For example:

{
  "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 response format also puts the next_batch and previous_batch links out of the arrays of objects. For example:

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