Create SQL Query (Classic)

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.

About this task

To use a relational database in an Operations Hub application, you must:
  • Create a Data Source (Classic) with a Relational Database type and provide the details of the external database.
  • Create a SQL Query, and specify the expected inputs and outputs of a Stored Procedure. (Described in this topic in the following steps.)
  • Map the query in the page designer to use the query to fetch the data from the external database. See Use the Relational Database Query in the Designer.

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 select all the required fields, the list of input parameters will be loaded if there are any, for the selected stored procedure.

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

    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.

    Specify the triggering/non-triggering options for the input parameters. See Triggering/Non-triggering SQL Queries.

    After the default values for input parameters are provided, select 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. This means while building the query, the fields that appear after selecting Execute, should match the fields returned by the stored procedure while using the application.
  10. By default, the Convert Datetime check box is checked. It implies that the datetime values are affected in the following manner:
    • the datetime values retrieved from the SQL database are shifted from UTC to the browser’s local time
    • datetime values from the Operations Hub Server have a 'Z' appended to indicate that they are in UTC
    • for input parameters sent to the Operations Hub Server, datetime values are time-shifted from the local time to UTC.
    • the datetime conversion behavior can also change based on the options available in the DatePicker plug-in and System datetime globals.
    If Convert Datetime is unchecked, the datetime values retrieved from the database will not be time-shifted. Operations Hub will not append the 'Z' to indicate UTC, and the values will be displayed as they are in the SQL Server. For input parameters, the datetime values will not be time-shifted. This behavior can also change based on the options available in the DatePicker plug-in and System datetime globals.
  11. Select Save or Save And Exit.
    The query is created.