Live Data Deriver Agent – exportSqlProcedure

<< Click to Display Table of Contents >>

Navigation:  Live > Agents > Transformation Agents > Data Deriver > Examples >

Live Data Deriver Agent – exportSqlProcedure

After the agent has finished processing a derived series and added its derived data to DT_LOGGER_DATUM, it executes the derived series' exportSql or exportSqlProcedure (if exportSql or exportSqlProcedure are defined in the derived series' SERIES_FUNCTION_INFO). The exportSql or exportSqlProcedure can be used, for example, to copy the derived data from DT_LOGGER_DATUM to another table, or to do something else with the derived data.

 

The export SQL statements can be added to the derived series' SERIES_FUNCTION_INFO (e.g., exportSql:"INSERT dt_flow ..."). But the SERIES_FUNCTION_INFO has a maximum allowed length, which limits the length of the SQL statements that you can add. And the SQL statements may be complex, and more difficult to understand, if they are in the SERIES_FUNCTION_INFO. An alternative is to add the export SQL statements to a stored procedure, and add the stored procedure's name to the SERIES_FUNCTION_INFO (e.g., exportSqlProcedure:"equis.my_custom_procedure").

 

The stored procedure must have the following input parameters:

 

Parameter

Type

Description

deriver_logger_series_id

BIGINT

The derived series' LOGGER_SERIES_ID.

deriver_start_date

DATETIME2(0)

The derived data is between these start and end dates and times.

deriver_end_date

deriver_update_yn

CHAR(1)

The Data Deriver Agent sets this to 'n', and the Date Deriver Update Agent sets it to 'y'.

 

The following are a few exportSqlProcedure examples; they copy the derived data from DT_LOGGER_DATUM to other tables:

DT_FLOW

DT_RESULT

DT_WATER_LEVEL