6.1 Defining a Query

The first step is to define the basic query that instructs the adapter to pull information from a specific database table or set of tables.

HINT:To retrieve data and seed the adapter upon startup or restart, define an initial query that runs once, then define a second query to retrieve new data or changes after the initial query. Otherwise, the adapter definition reads the entire database every time the adapter is started or restarted, potentially causing performance issues.

To define a query:

  1. Select an element or alarm in the Definition Navigator pane.

  2. In the right pane, click the Query icon to update the Query Results pane.

    Use the Editor tab to define the basic parameters of the query.

  3. Under Query Type, do one of the following.

    • To automatically generate the query source code, click the Generated radio button.

      The source code is then not editable in the Source tab.

    • To enter a custom query where the query source code is editable, click the Custom radio button.

      The source code is editable in the Source tab, but the Database Tables and Where text areas are not editable in the Editor tab.

  4. Do any of the following to specify the database tables:

    • Enter the database tables in The Query Uses the Following Database Tables text area.

      Use a comma to separate database table names.

    • Drag and drop the database tables and columns directly from the Database Navigator pane

    • Click (Edit Tables) to open an edit dialog box for additional typing space.

  5. Depending on the Query Type, do one of the following:

    • In the Editor tab, enter a Where clause in the Where text area or click (Edit Where) to open an edit dialog box for more space to enter the Where clause:

    • If defining a custom query, switch to the Source tab to edit the source code directly.

    If no lock mode is set in the Data Integrator Query (for example, select * from table WITH (ROWLOCK) when using SQL Server), the select statements use the default locking strategy for the database.

  6. To determine whether to remove all alarms that are not found in a query, select one of the following Remove Alarms Not in Query radio buttons:

    • Use Adapter Property: Select to use the current value for the adapter property. For more information, see Section 4.4, Specifying Run-Time Adapter Properties.

    • Yes: Select to remove all alarms not listed in the subsequent query.

    • No: Select to keep all alarms regardless of the query results.

  7. Switch to the Property Details tab to define how each property value is extracted from the database using the query.

    For instructions on defining properties, see Section 6.2, Defining Element or Alarm Properties.

  8. Use the FetchRows tab at the bottom of the Database Navigator pane to run a quick sampling from a database table.