Configure the Data Query properties

Configure the data query for the tags that you configured. The data query defines the next set of records to pull from the database.

To configure the Data Query properties

• Configure each of the Data Query properties.

  This query returns a value, quality, and timestamp for each integration variable. Currently, Quality is hard-coded to Good, but will be updated in the future to handle other quality types. You may want to filter non-Good quality samples in the query.

  Example query

  Select Value, Quality, DateTime from Example3 where VariableName = @TagName and DateTime > @LastSampleTime and DateTime <= @TimeHorizon and Quality = N’Good’

  The dataset returned from the database contains at a minimum the fields that map to Value, Quality, and DateTime data properties. The time for the query is start inclusive and excludes the TimeHorizon.

  For example:
  DateTime >= @TagLastSample AND DateTime < @TimeHorizon

  This is so that a sample does not have its own date time on the time horizon, as this is incorrect. It is advisable to limit the number of rows returned in one hit as this can exceed the max message size permitted by the web service. Use something like top(100) to process only 100 rows at a time for each variable.

  A SQL Stored Procedure query may look like this in AVEVA™ Production Management:

  Exec usp_GetData @TagName, @TagLastSample, @TimeHorizon

  1. Construct the data query that defines the next segment of data from the database table. Do this under the DataQuery properties section, DataQuery property.

     

     Example:
     select Value, Quality, DateTime from ConnectorTable where Variable = @TagName and DateTime >= @LastSampleTime AND DateTime < @TimeHorizon

     An @tag is a placeholder. Each time the data query is performed, the @tags are replaced with real data.

  2. Specify the data query command type as Text or Stored Procedure.

  3. Define the parameters in the data query using the Collection Editor.

     For example, the parameters @TagName, @LastSampleTime, and @TimeHorizon, specify each parameter name and ParameterValueType.

     Specify the data query parameters. In most cases, you configure three parameters: TagName, LastSampleTime, and TimeHorizon.

     

     Configure each of the required parameter properties. For more information on the parameter properties, see Data Query parameter properties.

  4. Configure the data query field mappings. This enables you to specify which columns returned in the data query map to Value, Quality and DateTime respectively.

     Use the Mappings dialog box to map these fields to any columns in the source database.

     

Related topics

• Database integration

• File integration

• About Integration Diagnostics