GET

GET
lac32
Retrieve rows from an endpoint, a table, a custom resource, or a custom endpoint using the GET verb.
GET calls require an auth token and results depends on the users roles.
In this article:
3
JavaScript Example
REST/JSON invocation is a standard practice, available in virtually every modern language. You can connect to API Creator using JavaScript. The following image shows the API Docs, Code samples tab:
Retrieval
The following are 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 these "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" : {
    "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,
Retrieve an Object by Primary Key
You can retrieve an object by appending its primary key to the resource using the following syntax:
https://server.acme.com/rest/default/demo/v1/<data source prefix:resource name>/<primary key>
For example:
https://server.acme.com/rest/default/demo/v1/demo:customer/Alpha and Sons
Security
Security is applied to all REST retrieval (GET) requests, including 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,
CA Live API Creator
 updates the 
@metadata
 link to designate secured columns.
 For more information:
Specify Filters 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. You can also specify filters using the URL encode/decode tool.
For more information:
 The following image shows a SQL where (
As Defined
):
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%27
Shar
%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%20
like
%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.
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
Subresource 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 subresource filters. For example, the following filter retrieves only those customers with orders in excess of 1000:
 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:
restlabrequesttab.png
Specify Filters using Column Names
Specify filters using non-aliased column names in 
where
 clauses and not column aliases. This arises out of the SQL considerations.
 For more information about the SQL considerations, see the MySQL website.
 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 and use single quotes.
The following image shows the
Args
checkbox on the Execute, REST Lab, Request tab:
restlabrequesttab2.png
Define Linked Resources
In large applications, it might 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 URIs 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. 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...