Docs‎ > ‎CA Live API Creator‎ > ‎Create‎ > ‎Resources‎ > ‎

Free SQL Resources

Defining a Free SQL Resource enables you to manually control the SQL, as shown here.  Note such queries are not updatable - the system has no knowledge of what underlying table / rules should be executed.  There is an advanced ability to enable underlying security for specific tables in these queries.

Note, that you SHOULD include an order by clause that guarantees a defined order, so that pagination will work correctly.  This is done for you automatically in normal SQL resources, but NOT in Free SQL.
As well, Free SQL Resources support query parameter replacement, but these are not recommended for public facing API's.  For example defining your query as

select *
  from customer
 where name like '@{arg_NamePattern}'
   and balance >= @{arg_MinimumBalance}
 order by upper(name), name

allows you to use query parameters &arg_NamePattern=Alpha%25&arg_MinimumBalance=200

If a parameter is not provided on the request, the original string is sent to the database, resulting in an error. Any SQL Resource defined with such parameters will therefore require these parameters to be provided with every invocation.

Keep in mind that the seemingly simple query

select * from deal order by ident 

will, at runtime, be emitted 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 and the advanced option described below may be used.

Advanced Features

Enabling security in Free SQL Resources
Using %%prefix:table%% as a table name, will enable security to be applied.  The SQL will be modified to include a select statement with security instead of the base table.

Manual control
For performance reasons, you may wish to disable the outer selects that API Creator may generate to control pagination, URL-provided order by and URL-provided filtering.

This should be used rarely, and is probably never needed in SQL Server and Oracle databases.  MySQL optimizer (as of v5.6) does not take advantage of the outer limit and where clauses and this can cause poor performance.  In this instance, you may take over and fully specify the SQL.

To do this, you must follow some strict rules as we still enable the security feature as described above and support URL provided order by and filter clauses.

To enable:
you MUST include all four strings @{LIMIT}, @{OFFSET}, @{WHERE} and @{ORDER} in the SQL.

API Creator will replace @{LIMIT} with the appropriate value (1 + page size), @{OFFSET} with the appropriate offset.  
Note that this may not be the expected value when security has been enabled.

@{WHERE} will be replaced with:
(filter1) (repeated as necessary) where filter1 is the first URL provided filter (there may be more than one filter). If you use this manual control, your SQL query MUST include a where clause, which MUST include @{WHERE}
If NO URL filter is provided, the string '1 = 1' is emitted unless a different default is provided.

@{ORDER} will be replaced with:
  (order1), (order2),  (including the final comma) when any URL ordering is provided.
When NO URL ordering is provided, the string '1 asc' is emitted.  You MUST provide some order by clause.  This should be fully ordered as it is required for proper pagination in any event.  You may specify a different default ordering.

As well, you may provide alternate default values using '-- ' comment style as shown in the example below.


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

Once again, this is required only if you need to take almost complete control of the SQL to solve e.g. performance issues. Most users will never need to do this.