Creating a Data Manipulator

After creating a database connection, you can create a generic command to modify the database, executable on demand. In GENESIS, commands that can be executed on demand are called Data Manipulators.

Data Manipulators cannot return data sets, so they are typically used to modify data in the database (i.e.: create a work order, delete the contents of a table, etc.). Data Manipulators can return simple scalar results using output parameters or return values.

NOTE: This procedure uses the SQL Server database, RollerChainManufacturing, as an example.

To create a Data Manipulator:

1. Open Workbench and expand your project folder, and then go to Data Connectivity > Databases > SQL Connections.

2. Right-click the desired database connection, and then select Add Data Manipulator. The Data Manipulator Configuration window is displayed.

   NOTE: to continue the example used in Connecting to an Existing SQL Server Database, right-click RollerChainManufacturing.

   

3. Enter a name for the Data Manipulator. To follow the example, use UpdateBadParts.

4. Select the Configure Command link to display the Configure Manipulator Command dialog, which you use to configure your query.

   

5. From the Command Type drop-down select the type of command you want to create. The possible options for Command Type are:

   Command Type options	Description
   Custom Query	Allows to manually enter a SQL query.
   Table Query	Automatically builds a SQL query to retrieve all the data from a single table. The query can be further customized.
   Stored Procedure	Calls a stored procedure.

   NOTE: To follow the example, select Table Query.

6. In Command Timeout enter an appropriate timeout for the query. This value represents how long the ICONICS Databases Point Manager will wait for the query to complete before returning an error.

   TIP: If you are running a complex query, or if you are reading a large amount of data from the database, you may want to increase this timeout.

7. In the Select a query section, expand dbo > Tables, expand the desired table and select one of the possible options. Select Next to display the pre-built query.

   Select a query option	Description
   For INSERT	Generates a parameterized query to insert data in the selected table.
   For UPDATE	Generates a parameterized query to update data in the selected table.
   For DELETE	Generates a parameterized query to delete data from the selected table.

   NOTE: To follow the example, expand the PartsDetails_Daily table and select FOR UPDATE.

   

8. The generated query allows to update all the three columns GoodParts, BadParts and PartsProduced for a specific day and piece of equipment. Modify the query as follows, so it does only update the BadParts column:

   UPDATE dbo.PartsDetails_Daily SET BadParts = @BadParts WHERE [Timestamp] = @Timestamp AND Equipment = @Equipment

9. Select Next to go over the Filter Configuration and Configure ORDER BY screens. In the Configure Parameters screen, verify that the parameters for the query are correctly displayed, along with their data types.

   

   NOTE: For more information about the Filter Configuration and Configure ORDER BY screens, refer to Create a Data Source and Create a parameterized Data Source.

10. Select Finish to close the Configure Manipulator Command dialog. The query now appears in the Manipulator Command box.

    TIP: The Manipulator Command box is read-only. To edit the query, select the Configure Command link to open the dialog again.

11. Select the Associated Data Sources tab. Under Configure the data sources refreshed automatically when the manipulator is executed select Click here to add new item. A new item will be added to the list.

12. Select the newly added item to expand the list of currently configured data sources and, within the list, select the Data Source that you want to refresh when the manipulator is executed. Select Apply to save the Data Manipulator.

    NOTE: To follow the example, select RollerChainManufacturing.PartsDetails_Daily.