Define Free SQL Resource Types

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.

Control Pagination

You can guarantee a defined order by including an order by clause. For private-facing API projects, you can include a Free SQL resource support query parameter replacement. For example, you can query the &arg_NamePattern=Alpha%25&arg_MinimumBalance=200 parameters using the following 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 query guarantees a defined order:

select * from deal order by ident

Emits as:

select * (select * from deal order by ident) el$top limit 21

Note: In some versions of MySQL, this may not perform as expected. Instead, you can use the following advanced option. The following image shows a Free SQL resource type on the Create, Resources, Details tab:

Advanced Features

Enable Security in Free SQL Resources

Enable security using %%prefix:table%% as a table name. The SQL is modified to include a select statement with security instead of the base table.

Manual Control of the SQL

(Most users will not need to do this.) You can take almost complete control of the SQL to solve issues, such as to solve performance issues by disabling the outer selects that Live API Creator can generate, URL-provided order by and filter clauses.

Note: 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.

Security is enabled and supports URL-provided order by and filter clauses.

Control Pagination

Control pagination by disabling the outer selects:
  • Include the following strings in the SQL:
    • @{LIMIT}. API Creator replaces this string with the appropriate value (1 + page size).
    • @{OFFSET}. API Creator replaces this string with the appropriate offset.
      Note: 
      If security is enabled, this may not be the value you expect.
    • @{WHERE}. API Creator replaces this string with (filter1) (repeated as necessary), where filter1 is the first URL provided filter (there may be more than one filter).
    • @{ORDER}
  • Include a where clause in your SQL query (@{WHERE}).
  • If you do not provide a URL filter or a different default, the following string is emitted:
1 = 1
  • If you provide URL ordering, @{ORDER} is replaced with the following (including the final comma):
(order1), (order2),
  • If you do not provide URL ordering, the following string is emitted:
    1 asc
Provide an order by clause. Events require proper pagination that is fully ordered. You can specify a different default ordering.

Provide Alternate Default Values

You can provide alternate default values using the following comment style:

-- 

The following code snippet shows an example:

-- 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}