Create a Query to a Relational Database

This topic describes how to create a SQL query.

Before you begin

In order to proceed you must have a working SQL database, and the selected database must have stored procedures. You must also have created a data source for the relational database in Operations Hub. See Create a SQL Data Source (Classic).

Procedure

  1. In the main navigation menu, select QUERIES.
    The QUERIES workspace appears.

  2. Select Add new query.
    The Create Query window appears.
  3. In the Name field, enter a name, and then select Create. The name must contain at least one uppercase or lowercase letter.
    The available options for creating the query appear.
  4. In the Description field, enter a description for the query.
  5. In the Type field, select Relational Database .
  6. In the Data Source field, select the name of the data source from the list.
  7. In the Query Type field, observe that the field defaults to ‘Stored Procedure’ when you select ‘Relational Database’ as the Type.
  8. In the Schema field, select the database schema that you want to use. All the database schemas will be loaded for the selected data source. The default selection is dbo.
  9. In the Stored Procedure field, select the Stored Procedure that you want to use from the selected schema.

    After you selects all the required fields, the list of input parameters will be loaded if there are any, for the selected stored procedure.

    The value for the input parameters can be configured either while creating the query or while consuming the query in the page builder page.

  10. To assign the value for an input parameter while creating the query, select the type drop-down in the input parameter list. It has two options:
    • Fixed Value: If this option is selected, the value given will be taken as the input to the Stored Procedure. The input parameter will not be shown in the page builder page.
    • Input Field: If this option is selected, the value given will be taken as the default input to the Stored Procedure. The input parameter will be shown in the page builder page and if there is any input provided to while execution, the default value will be overridden.
  11. After the default values for input parameters are provided, click the Execute button under the Test category. You will then be presented with the various fields in Result Sets and Output Parameters sent out of the Stored Procedure.
    Note: Currently we do not support dynamic responses from the Stored Procedures. Meaning, the fields that we see while building the query after clicking the Execute button, should match the fields returned by the stored procedure while using the application.
  12. Add the required fields.

    The following figure displays an example:

  13. Select Save or Save And Exit.
    The query is created.