How do I create an API using an SQL definition?

Adding an API

Illuminate has several many ways to create APIs for use at your institution. In this case, we will walk through creating an API using a SQL definition, which can be started by clicking on the SQL option on the API Builder screen. This selection will move you to the start of the API construction process as shown below.

The General Details screen allows you to create the API itself by writing in the SQL query you want it to run. For ease of use, this screen is similar to the screen used in other types of API creation, although the API method is limited to GET due to the nature of this API type. The query used will retrieve the specified data from your chosen source. The source choices are drawn from those specified in the manage connections section , you can always come back and edit the API after construction if you create it before the source is configured.

In addition, you can also create tags for you new API here that you can use in the search bar of the builder's main screen. Simply type the desired tag in the field before taping the 'Tags/label' button on the right to create a tag with that text.

For the query itself, Illuminate is configured to make the construction as easy as possible. In this example, we will be inserting the following SQL code:

SELECT ENAME, DNAME, JOB, EMPNO, HIREDATE, LOC
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO and ENAME
LIKE & EMPLOYEENAME
ORDER BY ENAME;

After which, the data is automatically formatted and set to be configured by selecting the 'Generate Model' button below the text input field.

At this point, your API is fully defined, although you can use the pencil icon at the left edge of the parameters to further customize them. This includes changing their types, adding a description for later administrators to be able to reference, adding a default value, or, in the case of the input parameter, specifying whether that parameter is required or not.

Once you are done, click 'Save & Next' in the bottom right to move on. This will first take you to a screen that will allow you to input any XSLT transformation that you would like before continuing further will take you to the Field Configurations screen.

This portion of the API builder allows you to further customize your API and can help facilitate the interaction between two different pieces of software that have different input and output parameter names by allowing you to choose what parameter name the API reports in its output. Once you have specified a template name and any translated field names you want, simply click next to move on.

At this point, all you need to do is confirm how you want the API to output your data and then choose a consumer to send the data to. For the ease of the user, Illuminate will also display an empty example of the output for you to verify before you finalize your choice.

Once you are satisfied, click 'Apply To Consumer' to go to the last step.

In this final step of the API builder, the consumers you have created in the Clients and Consumers menu will be displayed. Simply select to which consumers you wish to send the output data and select next to complete the construction of the API and move on to the publishing step. This specific step will allow you to publish your API to the Illuminate community at large and is entirely optional. Click 'Skip & Continue' to view the automatically generated documentation for your API before clicking 'Done' to leave the API builder.

Now that your API is complete, you can edit any details you want to change, delete it, try it out with real data, or authorize it for a consumer. Authorizing the API allows the consumer to use the API to draw data from your system and can be reversed by simply selecting the authorization again.