Define Free SQL Resource Types

 typically automates SQL operations, but you can address more complex cases with Free SQL resources. Free SQL resources are read-only resources that you specify the SQL to execute.
lac52
 
CA Live API Creator
 typically automates SQL operations, but you can address more complex cases with Free SQL resources. Free SQL resources are read-only resources that you specify the SQL to execute.
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.
    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 set up your Free SQL resource for filtering and sorting requests that use query parameters or that use regular filters.
Your Free SQL resource is created and displays in the list of resources.
Manage Free SQL Resources
You can:
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 and sort 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 and sorting 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 the 
@{ARGUMENT.[parameterName]}
 syntax. 
CA Live API Creator
 passes named filters into the 
@{WHERE}
 condition that are in the SQL of your Free SQL resource.
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. Do one of the following in the 
    Code
     field for your Free SQL resource:
    • Define the query parameters using the 
      where
       and 
      order by
       clauses with the 
      @ {WHERE
      } and 
      @{ORDER}
      , for example:
      select * from @{SCHEMA}."customer"
      where
      @{WHERE} and @{ORDER}
      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 use double quote ('') or single quote (') to surround attribute names or values).
    • Define the query parameters using the argument name replacement in the 
      where
       or 
      order by
       clauses with the 
      @{ARGUMENT.[parameterName]=[prefix].[tableName].[columnName]}
       syntax, for example:
      select *
      from @{SCHEMA}."customer"
      where
      "name" like @{ARGUMENT.NamePattern=demo:customer.name}
      and "balance" >= @{ARGUMENT.MinimumBalance=demo:customer.balance}
      and @{WHERE}
      order by
      @{ORDER}
      The following example is a request that filters using two query parameters, 
      NamePattern
       and 
      MinimumBalance.
       
      http://localhost:8080/rest/default/demo/v1/FreeSQLCustomers?args={"NamePattern":"%Alpha%","MinimumBalance":200}
      Some Java containers, such as Apache Tomcat, expect that you encode the URL when you call Free SQL resources with query parameters. You might encounter the following HTTP status code 400 error:
      The server responded with a 400 error, you may have an improperly formatted URL
      The following URL examples show the encoded URLs for a Free SQL resource:
      http://localhost:8080/rest/default/demo/v1/FreeSQLCustomers?args=
      %7B
      "NamePattern":"%Alpha%","MinimumBalance":200
      %7D
      OR
      http://localhost:8080/rest/default/demo/v1/FreeSQLCustomers?args=
      %7B
      "NamePattern":"%Alpha%","MinimumBalance":200
      %7D&order=%60name%60%20desc
      You can also configure the Tomcat Java container to accept special characters in the URL requests it receives. For more information, see Install on Apache Tomcat.
You have defined your Free SQL resource for requests that filter and sort using query parameters.
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"%20desc,"credit_limit"%20asc
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}
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 the underlying table 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.
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.
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 resource attributes.
You can pass in name parameters using the 
@JOIN
 string. You can join information using the 
@{JOIN.theName}
 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:
@{JOIN.theName} = [parent 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 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).
Call Free SQL Resources
Free SQL resources are read-only. You can call them using only GET requests.
Free SQL Resource Limitations
When calling Free SQL 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 to define your SQL in your Free SQL resource connected to SQL Server:
// Use alias for sum or count columns to provide a name
select sum(colX) as a from orders