Define Free SQL Resource Types

You can define Free SQL resource types but you can define them only as SQL select queries. Free SQL resource types are read-only. Live API Creator has no knowledge of what underlying table/rules should be executed. You can enable underlying security for tables in Free SQL resource types.

You can guarantee a defined order to support pagination by including an order by clause. For private-facing API projects, you can include a Free SQL Resources support query parameter replacement.

For example, you can use query parameters &arg_NamePattern=Alpha%25&arg_MinimumBalance=200 using the following query:

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 simple query:

select * from deal order by ident

emits as the following to support pagination (and any user-provided where filters):

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

In some versions of MySQL, for example, this may not perform as expected. You can use the advanced option as shown in the following image:

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

You can take almost complete control of the SQL to solve issues, such as to solve performance issues. Most users will not need to do this. You do this by disabling the outer selects that 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.

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

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.
      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 string '1 = 1'is emitted.
    • If you provide URL ordering, @{ORDER} is replaced with: (order1), (order2), (including the final comma).
    • If you do not provide URL ordering, the string '1 asc' is emitted. Provide an order by clause. Events require proper pagination that is fully ordered. You can specify a different default ordering.
You can provide alternate default values using '-- 'comment style, as shown in the following 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}