overview
This section explains an example of using the "Execute SQL (ODBC) action" to execute SQL and update values in the database.
In this sample, data is added using the insert command, and table data is deleted using the delete command.
Action used: Execute SQL (ODBC) Action
|
SQL statements registered in Execute SQL (ODBC) actions cannot be edited dynamically after going online. For example, if you need to issue more complex SQL commands, such as when you want to switch the contents of the SQL statements issued using some logic, you can use a script in the Script Ver2 action. For details, see "Accessing the DB from Script Ver2". |
Download the sample
The creation examples shown on this page are provided with samples.
The sample can be downloaded from below.
The above sample includes a SQL file (DDL) to generate the table. Use it as a reference when creating the table.
Operation check
This sample connects to the "DATA_TABLE" table on the database via ODBC and issues SQL. To run the sample, prepare the database to connect to and set up the ODBC connection. Once the database is ready, load the sample server configuration file from FA-Server and bring the application online (yellow arrow).
In this example, we assume that the following tables have been created:
(Example for SQLServer2017)
After going online, please confirm that when you write TRUE to U01.F01.T01, a new record is added to the DATA_TABLE.
Then, when you write TRUE to U01.F01.T02, verify that the record in DATA_TABLE is deleted.
Setup Procedure
Preparing the Database
1.Prepare the database.
Create a table called "DATA_TABLE" in the database you want to connect to, and set the layout as shown below.
(Example for SQLServer2017)
Tag configuration
1.Set tags.
Add a unit called "U01" as a virtual device and create the following two tags under the F01 folder. These tags will be used as triggers for Tag event.
Tag Name |
address |
the purpose |
|---|---|---|
U01.F01.T01 |
X0000 |
insert A tag that triggers the execution of a command |
U01.F01.T02 |
X0001 |
The tag that triggers the execution of the delete command. |
Action Settings
1.Add two Execute SQL (ODBC) actions.
In this example, "A01" and "A02" actions are added.
2.Configure the properties for each action.
Use the
ODBC Settings tab to configure the ODBC data source and write SQL statements.
The
ODBC setting procedure can be performed by common operations for each DBMS. For ODBC settings, please refer to the description in "ODBC Settings" in the common settings for actions.
The
SQL statement sets the following contents.
(A01)
INSERT INTO DATA_TABLE ( FIELD1, FIELD2 ) VALUES (1, 'abc'); |
(A02)
DELETE FROM DATA_TABLE; |
Event Settings
1.Create a Tag event to perform the action.
Register two Tag event, "E01" and "E02".
2.Set E01.
E01 sets the action to be executed when U01.F01.T01 becomes TRUE. Specify "A01" as the action to be executed.
3.Set E02
E02 sets the action to be executed when U01.F01.T02 becomes TRUE. Specify "A02" as the action to be executed.
