Free SQL Resources

Free SQL Resources
lac31
You can define Free SQL resource types only as SQL select queries. Free SQL resource types are read-only. API Creator does not know the underlying table/rules to execute. You can enable underlying security for tables in Free SQL resource types.
In this article:
3
Control Pagination
You can guarantee a defined order by including an 
order by
 clause. For private-facing APIs, you can include a Free SQL resource that replaces query parameters. For example, you can query the 
&arg_NamePattern=Alpha%25
and the
&arg_MinimumBalance=200
 parameters using the following SQL query, which includes user-provided 
where
 filters: 
select *
  from customer
 
where
 name like '@{arg_NamePattern}'
   and balance >= @{arg_MinimumBalance}
 
order by
 upper(name), name
Provide a parameter on the request. The following example SQL query ensures a defined order: 
select * from deal 
order by
 ident
This SQL query emits as:
select * (select * from deal order by ident) el$top limit 21
In some versions of MySQL, this SQL query might not emit the value that you expect. Instead, you can use the following advanced query option:
Advanced Features
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%%
The SQL is modified to include a select statement with security instead of the base table.
Enable Manual Control of the SQL
If you are using Microsoft 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. Most users will not need to enable manual control.
You can take almost complete control of the SQL to solve issues, such as to solve performance issues, by disabling the outer selects that
CA Live API Creator
can generate and use URL-provided
order by
and
filter
clauses. Security is enabled and supports URL-provided 
order by
 and
filter
clauses.
Control pagination by disabling the outer selects. You can include the following strings in your SQL query:
  • @{LIMIT}
    . API Creator replaces this string with the appropriate value (1 + page size).
  • @{OFFSET}
    . API Creator replaces this string with the appropriate offset.
    If you have enabled security, this SQL query might not emit the value that you expect.
     
  • @{WHERE}
    . You can include a 
    where
     clause in your SQL query (
    @{WHERE})
    . API Creator replaces this string with (
    filter1
    ), where 
    filter1
     is the first URL-provided filter. You can have one or more filters. Repeat this string for each filter.
  • @{ORDER}
    If you do not provide a URL filter or a different default, the following string is emitted: 
    1 = 1
    If you provide URL ordering, API Creator replaces the 
    @{ORDER}
     variable with the following string, including the final comma:
    (order1), (order2),
    If you do not provide URL ordering, the following string is emitted:
    1 asc
The following code snippet shows an example of how these variables work:
-- 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}
You can provide alternate default values in a comment using the following comment style:
--
 
Free SQL as a Subresource using @JOIN
Prerequisite:
 You know the name of your parent attributes.
You can pass in name parameters using 
@JOIN
. You can JOIN information using 
@{JOIN.childName}
 in the SQL.
When FreeSQL is a subresource of a (parent) table, the 
Join
 field displays on the page. Define the relationship between the parent and the child parameter names in this field. The JOIN syntax is:
theName = [name]
Example:
The following FreeSQL shows how to use the 
@JOIN
 and parameter names. In this example, 
theName
 is the name of the parameter you want to pass 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, but you must include it 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]