Define Free SQL Resource Types

Free SQL resources are resources that you specify the SQL to execute. These resources are read-only, but you can set up the SQL the way that you want. You can pass filters, sorts, and pagination to your custom SQL. You can configure the security for tables and views and control their availability in Free SQL resource types.
lac40
Free SQL resources are resources that you specify the SQL to execute. These resources are read-only, but you can set up the SQL the way that you want. You can pass filters, sorts, and pagination to your custom SQL. You can configure the security for tables and views and control their availability in Free SQL resource types.
In this article:
3
Create a Free SQL Resource
  1. With your API open, in the Create section, click 
    Resources
    .
    If you have not defined any resources yet, the Welcome to Resources page appears. If you have at least one resource that is defined, the Resource tab displays.
  2. Do
    one
    of the following options:
    • If you have not defined a resource yet, create a resource by clicking 
      Create a Resource
      .
    • If you have at least one resource defined, click 
      New Resource
      .
    The Add Resource window opens.
  3. Complete the following fields, and then click 
    Add
    :
    Resource Type
    Defines the type of resource you want to create. Select 
    Free SQL Resource
    .
    Values:
    • Ⓣ Table-based Resource.
       Resources that are linked to existing base SQL entities (tables and views) and automate SQL handling. You can integrate other data sources and control SQL by defining the resource type.
    • Ⓢ Free SQL
       Resource
      . Resources that you specify the SQL to execute.
    • Ⓙ JavaScript
       Resource
      . (Advanced users) Resources that you supply the server-side JavaScript and that is executed whenever the resource is accessed and returns JSON.
    • Ⓜ MongoDB
       Resource
      . Resources that you can connect to a specific MongoDB server, database, and collection.
    Default:
     Table-based
    Prefix
    Defines the data source that you want to execute the query SQL for your Free SQL resource. The available options are based on the data source prefix of the data source connections that you have defined.
    Resource Name
    The resource name is the container name with JSON.
    Unique:
     Yes
  4. Define the SQL for your Free SQL resource in the
    Code
    field, and then save your changes. You can define your Free SQL resource for filtering and sorting requests that use query parameters or that use regular filters.
    For more information about how to set up your Free SQL resource for filtering and sorting requests, see the "Set up your Free SQL Resource for Filtering and Sorting Requests" section.
Your Free SQL resource is created and displays in the list of resources.
Set up your Free SQL Resource for Filtering and Sorting Requests
You can prevent unsecured filtering and sorting requests from accessing your data and the possibility of SQL injections. By default, requests can filter your Free SQL resource by specifying regular filters and sorts in the URL. Regular filters and sorts are pieces of SQL code that filtering and sorting requests can send directly to your database. To prevent these kinds of filtering and sorting requests, define your Free SQL resource to accept only filtering requests that use query parameters.
Unless you trust the callers, secure your data by requiring that filtering and sorting requests specify only named filters (system filters (
sysfilter
) and user filters (
userfilter
)) and named sorts (system sorts (
sysorder
) and user sorts (
userorder
)).
For more information:
You can define Free SQL resource types only as SQL select queries.
For more information:
Define your Free SQL Resource for Requests that Filter and Sort using Query Parameters
You can prevent unsecured filtering and sorting requests from accessing your data and the possibility of SQL injections by defining up your Free SQL resource to accept only filtering requests that use query parameters. The request specifies query parameters that are defined in the SQL of the Free SQL resource using 
arg_
.
CA Live API Creator
 passes named filters into the 
@{WHERE}
 condition that are in the SQL of your Free SQL resource.
The following example is a filtering and sorting request that specifies the
NamePattern
and 
MinimumBalance
query parameters:
<endpoint>?
arg_NamePattern
=Alpha%25&
arg_MinimumBalance
=200
Follow these steps:
  1. Disallow requests from using regular filters and sorts (turn off regular filters and sorts) in the API.
    For more information about how to turn off regular filters and sorts, see API Properties.
  2. In the
    Code
    field for your Free SQL resource, define the query parameters using the 
    where
     and
    order by
    clauses with the 
    @{arg_paramname}
     syntax, for example:
    select *   from customer  
    where
     name like '@{arg_NamePattern}'    and balance >= @{arg_MinimumBalance}  
    order by
     upper(name), name
    The SQL syntax that you use depends on the underlying database and connection properties. For example, some databases require that you quote ('') names).
Define your Free SQL Resource for Requests that Filter and Sort using Regular Filters and Sorts
By default, requests can filter your Free SQL resource by specifying regular filters and sorts in the URL. 
CA Live API Creator
 replaces the 
@{WHERE}
 and 
{@ORDER}
 conditions that are in the SQL of your Free SQL resource with the filters and sorts that the request specifies in the URL.
The following example is a request that specifies a regular filter and sort using
filter
and
order
arguments:
<endpoint>?
filter
="balance"<1000&
order
="name" desc,"credit_limit" asc
If you require additional parameters, append with the
&
clause, for example:
<endpoint>?filter="balance"<1000
&
"credit_limit">1000
Follow these steps:
  1. Ensure that requests can use regular filters and sorts (turn on regular filters and sorts) in the API. By default, regular filters and sorts are allowed.
    For more information about how to turn off regular filters and sorts, see API Properties.
  2. In the 
    Code
     field for your Free SQL resource, define the filter and sort parameters using the 
    where
     and
    order by
    clauses, for example:  
    The 
    where
     and 
    order by
     clauses are required. The SQL syntax that you use depends on the underlying database and connection properties. For example, some databases require that you quote ('') names).
    select * from "customer"
    where
     @{WHERE} 
    order by
     @{ORDER} 
    OR
    select * from `customer`
    where
     @{WHERE} 
    order by
     @{ORDER}
    When defining filters on columns that have the same name, you can have
    CA Live API Creator
    apply filters on aliases by providing the alias for those columns and wrapping your SELECT statement within another SELECT clause, for example:
    select *
    from (select c."type" as custtype, 
                   o."type" as potype, 
                   c."name", 
                   o."order_number", 
                   o."amount_total"
             from "@{SCHEMA}"."PurchaseOrder" o          inner join "@{SCHEMA}"."customer" c          on o."customer_name" = c."name") "MyTable"
     where @{WHERE}
     order by @{ORDER}
FreeSQL Resource Limitations
When calling FreeSQL resources that are connected to a SQL Server data source and you have SUM or COUNT clause in your SELECT statement, you might encounter the following error:
  "statusCode": 500, 
  "errorCode": 50031, 
  "errorMessage": "Execution of resource HASH_SQL has resulted in a SQL error: No column name was specified for column 1 of 'el__top'." 
SQL Server does not generate a name for the SUM(x) or COUNT(x) column. This causes the outer SQL that 
CA Live API Creator
 wraps around the defined SQL to be invalid. You can include an alias in your SQL to avoid this issue.  
The following code example shows how you should define your SQL in your FreeSQL resource connected to SQL Server:
// Use alias for sum or count columns to provide a name
select sum(colX) as a from orders 
Advanced Options
The following options are for advanced users.
Enable Security in Free SQL Resources
You can have 
CA Live API Creator
 apply row-level security for underlying tables by using the following syntax as the table name:
%%prefix:table%%
CA Live API Creator
 modifies the SQL to include a select statement with security instead of the base table.
Enable Manual Control of the SQL
You can solve issues, such as performance issues, by taking almost complete control of the SQL. Disable the outer selects that
CA Live API Creator
can generate, URL-provided
order by
and
filter
clauses.
Most users do not need to enable manual control of the SQL.
If you are using SQL Server and Oracle databases, you probably never need to enable manual control. If you are using MySQL optimizer, take over and fully specify the SQL. Screen Shot 2017-08-17 at 11.00.44 AM.png
Control Pagination
You can control pagination by disabling the outer selects. You can include the following strings in your SQL query:
  • @{LIMIT}
    .
    CA Live API Creator
     replaces this string with the appropriate value (1 + page size).
  • @{OFFSET}
    .
    CA Live API Creator
     replaces this string with the appropriate offset.
    If have enabled security, the
    offset
    value might not be the value that you expect.
  • @{WHERE}
    . You can specify the 
    where
     clause (the
    @{WHERE}
     condition) in your SQL query. 
    CA Live API Creator
    replaces this string with (the 
    FILTER
     clause), where 
    filter
     is constructed using the URL-provided filters. You can specify one or more filters. Repeat this string for each filter.
  • @{ORDER}
    . You can specify the
    order by
    clause (the 
    @{ORDER}
     condition) in your SQL query.
  • @{CATALOG}
    . You can specify the SQL to include the catalog name, as defined by the data source.
  • @{SCHEMA}
    . You can specify the SQL to include the schema/owner, as defined by the data source. 
If you do not provide a URL filter or a different default,
CA Live API Creator
 emits the following string:
1 = 1
If you provide URL ordering,
CA Live API Creator
 replaces the 
@{ORDER}
 string with the following string (including the final comma):
(order1), (order2),
If you do not provide URL ordering,
CA Live API Creator
 emits the following string:
1 asc
The following code snippet shows an example of how these variables work:
 You can provide alternate default values in a comment using the following comment style:
-- DEFAULT
keyword
default_value
-- DEFAULT ORDER upper(name) desc, name desc, balance -- DEFAULT WHERE balance > 100 select name, balance from customer where upper(name) like '%A%' and @{WHERE} order by @{ORDER} limit @{LIMIT} offset @{OFFSET}
The following code snippet shows an example of how to invoke your SQL resource using a URL of the form:
https://api.acme.com/rest/default/abcde/v1/Customers?sysfilter=lessequal(id:7)&sysfilter=greaterequal(id:3)
Free SQL as a Subresource using @JOIN
Prerequisite:
 You know the name of your parent attributes.
You can pass in name parameters using the 
@JOIN
string. You can join information using the 
@{JOIN.childName}
 string in the SQL. When a Free SQL resource is a subresource of a (parent) table, the 
Join
 field displays on the Resource page. Define the relationship between the parent and the child parameter names in this field. Use the following join syntax:
theName = [name]
Example:
The following example shows how to use the 
@JOIN
 string and pass the 
theName
 parameter name to your JOIN:
SELECT a.* FROM `PurchaseOrder` a WHERE ( `customer_name` = @{JOIN.theName}) and @{WHERE} ORDER BY @{ORDER}
You can optionally add the 
@{WHERE}
 condition after the JOIN portions. You can identify multiple parameters using double quotes ("") and the 
AND
 condition.
In the following example, 
theYear1
 and 
theYear2
 are your user-defined join attributes:
"theYear1" = [model_yr] AND "theYear2" = [make_yr]
Add each
@{JOIN.attrname}
 string to separate lines in your Free SQL definition, for example:
SELECT a.* FROM `myOrder` a WHERE  a.`model_year` >= @{JOIN.theYear1}AND a.`make_year` <= @{JOIN.theYear2}and @{WHERE} ORDER BY @{ORDER}
Add the catalog and schema name that is defined in the data source to your SQL statement, for example:
SELECT a.*
FROM "@{CATALOG}"."@{SCHEMA}"."myOrder" a
WHERE  a."model_year" >= @{JOIN.theYear1}AND a."make_year" <= @{JOIN.theYear2}AND @{WHERE}
ORDER BY @{ORDER}
OR
SELECT a.*
FROM `@{CATALOG}`.`myOrder` a
WHERE  a.`model_year` >= @{JOIN.theYear1}AND a.`make_year` <= @{JOIN.theYear2}AND @{WHERE}
ORDER BY @{ORDER}
The SQL syntax that you use depends on the underlying database and connection properties. For example, some databases require that you quote ('') names).