=====Scripting for Database Servers (in MIStudio and TransSECS)=====

Although there is a lot you can do with just using Database Servers set up in the MIStudio logic and in the Devices of TransSECS and MIStudio, using scripts that reference these beans can be a powerful tool. Recipe Management is the most common use case for using TransSECS Devices with databases. If the recipes are stored locally (file based recipes) then you might want to consider the automatic file-based recipe handling of TransSECS (by setting transsecs.recipemanager=1 in the ErgoTechConfiguration.properties of your project deployment). 

These examples assume that you have already set up your database (either local or remote) and have set up the connection in your project using a generic (or specific) DatabaseConnectionManager for your database. In MIStudio you will need to be in LIVE mode (toggle from SIM -> LIVE) to enable the database connection during development.

====Triggering a Database Server====

Database servers can be triggered in a script to run the existing SQL statement using any value, of any type, sent to the trigger() method of the bean. Be sure the SQL statement is set before triggering the bean. Use setSQLStatement() to set the SQL statement, if needed. Also make sure the server has been properly configured to use the DatabaseConnectionManager.

If the database bean is writing data to the database, the SQL Statement should include this current data to write to specific columns of the table. This can be either an UPDATE or INSERT statement. No output is generated from the bean when it is triggered. This type of bean can also use a CREATE statement.

If the database bean is reading data from the database, the output of the bean after triggering will contain a result (either a single value, array, or 2D array, depending on the bean type). The SQL statement would typically be a SELECT statement to read this data. 


====Triggered Historical====

The TriggeredHistorical server can also be triggered from a script. However, this bean does not use a SQL Statement, instead it uses the current values connected to its input connection. When triggered the current values and the timestamp of the trigger are recorded in a row in the specified database table. The column names used in the table must match the name of the data sources connected to the input.

Instead of using values connected directly into the TriggeredHistorical input, you can trigger the database write using one of two alternate inputs: mapTrigger() or jsonTrigger(). mapTrigger() takes a ValueObject with a "MAP" property. The MAP property of the ValueObject contains a hashmap of (column,value) to write the value data to the specific column in the table. The timestamp of the ValueObject is also written, as usual, to the ReadingTime column of the table.

Using mapTrigger and jsonTrigger in scripting allows you more flexibility in using various data sources and specifying individual column names in the table for this data. For the regular trigger() in TriggeredHistorical, the names of the data sources must match up with the column names, which is sometimes inconvenient.

The script below assumes there is a bean called TriggeredHistorical set up with a valid DatabaseConnectionManager with a table set up (and the CreateTable property set to true). It is triggered using mapTrigger() with a set of data for two columns in the table called value1 and value2. These values are read from two BroadcastServers in the logic called "BCS1" and "BCS2". When the TriggeredHistorical is triggered with the mapTrigger() method, the value for BCS1 will be written to the "value1" column, and likewise, the value for BCS2 is going to be written to the "value2" column of the table.

{{:pasted:20210728-141730.png?400}}

<code JavaScript>
//test writing to triggeredhistorical using mapTrigger()

var LinkedHashMap = Java.type("java.util.LinkedHashMap"); 
var StringValueObject = Java.type("com.ergotech.vib.valueobjects.StringValueObject");
var ValueChangedEvent = Java.type("com.ergotech.vib.valueobjects.ValueChangedEvent");

dataMap=new LinkedHashMap();

//put the data into the map

bcs1 = BCS1->getValueObject(); //BCS1 is a server in the logic containing the value we want to store in column "value1"
bcs2 = BCS2->getValueObject(); //BCS2 is a server in the logic containing the value we want to store in column "value2"

dataMap.put("value1",bcs1); //value1 is the column name for the data in the server called "BCS1"
dataMap.put("value2",bcs2); //value2 is the column name for the data in the server called "BCS2"


triggerValue = new StringValueObject("new data"); //this string value will be ignored, it is just used to contain the map data for the trigger.
triggerValue.setProperty("MAP",dataMap); //dataMap has the data to be stored
 
//write to database -- this assumes TriggeredHistorical is set up and the database is live

 
TriggeredHistorical->mapTrigger(new ValueChangedEvent(this,triggerValue));
</code>




Another example of using mapTrigger() (this example uses a TriggeredHistorical in the Devices node of the project):

[[host_connection_status#set_up_alarm_listeners|TransSECS Example using mapTrigger]]