How to create a Database Driver using the Alleantia Editor

Creating a Driver to exchange data with a machine / system through an exchange SQL Table (e.g. SCADA System).

A machine exposes data through structured SQL tables as shown in the following image:

Alleantia can query the DB by invoking SQL queries properly configured through a machine driver.

Referring to the previous table, let's create a driver to retrieve all data related to the last operation performed by the machine within the "order_list" table (one line at a time).

The query will be:

SELECT TOP 1 * FROM dbo.orders_lite ORDER BY REC_TIME DESC;

This query will generate the following table:

The sample table has:

- 13 columns

- 10 variables with a numeric Data Type (ROW_ID, JOB_ID, ARTICLES_ID, REQUESTED, CYCLES_QTY, CYCLES_COUNTERS, ACTIVITY_TIME, STOP_TIME, CYCLE_TIME, TOTALE_PEZZI)

- 3 variables of String type (REC_TIME, VARIANTS, WORKSTATION_ID)

Identify the desired tables and variables, and then proceed with the creation of the driver.

SQL Tables can be more than one, the query has to be modified accordingly (ask support from your Company DB Admin). 

Let's go now to the Drivers' Web Editor

1. Access the driver editor on the Alleantia Cloud Portal (https://cloud.alleantia.com/xmod/createDriver.zul).
2. In the "Choose the protocol" dropdown menu, select the 'newdb' protocol .
3. Fill in the mandatory fields (*) with information about the machine.

Reading Mode

• To add a variable in reading, click the "+" button under the "Variables" section.
• In the "Name" field, enter the name of the desired variable.
• In the "Query" field, enter the machine interrogation query (e.g., `SELECT TOP 1 * FROM dbo.orders_lite ORDER BY REC_TIME DESC;`).
• In "Columns Cardinality," enter the total number of columns displayed by the query (e.g., 13).
• In "Row Index," indicate from which resulting table row to read the variable (e.g., 0).
• In "Columns Index," indicate which column of the query to point to read the variable (e.g., 2 for the variable Job_ID).
• In "Data Type," define whether it is a numeric, string, or boolean variable.

Writing:

• To add a variable in writing, click the "+" button under the "Variables" section.
• In the "Name" field, enter the name of the desired variable.
• In "Data Type," define whether it is a numeric, string, or boolean variable.

For writing to the DB driver, in addition to mapping the variables already exposed by the machine, we need to define another variable called 'TRIGGER' that, through the invocation of the INSERT query used for writing, will update the table with the new values. Therefore, we must first fill in all the writing variables and then assign a value to TRIGGER to trigger the event.

Configure the TRIGGER variable as follows:

• In "Query Update," enter the query to execute (`INSERT INTO dbo.orders_lite(JOB_ID, VARIANTS, ARTICLES_ID, WORKSTATION_ID, REQUESTED, CYCLE_QTY) VALUES ('%s','%s','%s','%s',%s,%s)`).
• Use `%s` as a placeholder for the values to be inserted into the variables at the time of writing. Note: use `%s` for numeric values and `'%s'` for string values.
• In "Write Mode," enter the writing mode in the database (in this case, "INSERT").
• Set "Access Type" to "Write."