17.4.3 Example: Creating a REST Data Source on Top of a SQL Query

Review an example of creating a REST Data Source on top of a SQL Query executed on a REST Enabled SQL service reference.

REST Enabled SQL service references to a remote database include support for executing a SQL query on REST Enabled SQL reference. The following example creates a REST Data Source with a MySQL query using the fictional reference, MySQL REST Enabled SQL Example, described in Example: Creating REST Enabled SQL Service Reference on MySQL.

To create a REST Source on top of a SQL query executed on a REST Enabled SQL reference:

1. Navigate to the REST Data Sources page:
   1. On the Workspace home page, click App Builder.
   2. Select an application.
   3. On the Application home page, click Shared Components in the center of the page.
   4. Under Data Sources, select REST Data Sources.
2. On the REST Data Sources page, click Create.
3. Method - Select From Scratch and click Next.
4. General:
   1. REST Data Source Type - Select REST Enabled SQL Query.
   2. Name - Enter a descriptive name for this REST Data Source (for example, MySQL REST Data Source).
   3. REST Enabled SQL Reference - Select a previously created REST Enabled SQL Reference on which to execute a SQL query (for example, MySQL REST Enabled SQL Example).
   4. Click Next.
5. REST Enabled SQL Query:
   1. SQL Query - Enter a query to execute on the REST Enabled SQL Service for this REST Data Source. In the following example, you create a MySQL query using the fictional reference, MySQL REST Enabled SQL Example, described in Example: Creating REST Enabled SQL Service Reference on MySQL.

      select * from `film`

      Result discovery and data type detection is done based on the results of this query. On MySQL databases, the query executes within the default database, which is configured within the REST Enabled SQL reference. Tables in other databases can be referenced by simply prefixing them.

   2. Click Next.
6. Authentication:
   1. Credentials - Accept the default or choose existing Credentials to be used for authentication.
   2. Click Discover.

      APEX invokes the REST service URL and a Preview appears. From the REST Enabled SQL response, APEX derives attributes and their data types and uses this to create a Data Profile. Consider the following example:

      
      Description of the illustration rest_data_source_discovery.png
7. Click Create REST Data Source.

   The REST Data Source is created.

   The data from the film table is now available as a REST Data Source which can be used in APEX components, REST Source Catalogs or for REST Synchronizations.

   Note:

   When used in interactive reports or interactive grids, end user filters are converted to MySQL or Oracle specific SQL and pushed down to the REST Enabled SQL Service. You can also use Post Processing SQL which enables support for joins between a MySQL Query and a local Oracle table.