overview
This section explains how to use the logger action to log to a database.
As an example, we will use SQLServer2017 as a sample.
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 table creation DDL (logtable.sql) for each DBMS. The sample DDL is stored in the following folder.
(Sample folder) \table_DDL
\myslq\logtable.sql
\oracle\logtable.sql
\postgresql\logtable.sql
\sqlserver\logtable.sql
|
The latest information on supported database types, versions, etc. can also be found in the FAQ on our website, "What database products are supported?". |
Setup Procedure
■Preparing the database
To run this sample, you must prepare a table for logging in the database.
First, prepare a database. You can name the database freely.
Below is an example of the display in the Object Explorer of SQLServer Management Studio. In the example below, a database named "LOGGERDB" has been created.
Note that the logger (ODBC) action does not necessarily require a dedicated database for logging. There is no problem even if the connected database contains other tables.
■ Prepare a logging table in the database
Once you have created the database, register a table where the logger actions can store their log data.
You can name the table freely, but as an example, we will assume that a table named "LOGTABLE" has been created.
Next, prepare a table in the above database to store the log data. You can name the table freely, but for this example, we will assume that a table named "LOGTABLE" has been created in the LOGGERDB database.
Please create the following fields in the LOGTABLE table. You can also create the table using the table creation DDL (logtable.sql) included in the sample (*The following is an example for SQL Server).
Note that the data types below are just examples. Be sure to select the appropriate data type depending on the type of tag you are logging.
Field Name |
Data Types |
Key Settings |
NULL Tolerance |
supplement |
F01 |
datetime |
(none) |
do not |
The date and time the record was created. |
SD0008 |
int |
(none) |
do |
Prepare a numeric type field to store numeric tags. |
SD0009 |
int |
(none) |
do |
Ibid. |
SD0010 |
int |
(none) |
do |
Ibid. |
SD0011 |
int |
(none) |
do |
Ibid. |
SD0012 |
int |
(none) |
do |
Ibid. |
SD0013 |
int |
(none) |
do |
Ibid. |
SD0014 |
int |
(none) |
do |
Ibid. |
SD0015 |
int |
(none) |
do |
Ibid. |
Below is an example displayed on SQLServer.
|
This sample includes table creation DDL (logtable.sql) for DBMS other than SQLServer. Please use it as a reference when defining tables in the DBMS you actually use. |
|
The names of data types of fields defined in the table on the database side (for example, "INT", "INTEGER", "NUMBER", "FLOAT", "DOUBLE", etc.) vary depending on the type of DBMS. For actions that require you to manually create a table in advance, such as Alarm server (ODBC), you must define the table appropriately on the connected DBMS side. For recommended data types for each connectable DBMS, see "Setting up a connection to the database" in the Appendix. |
|
If you want to save NULL in the database record when an error occurs in communication with PLC etc., make sure to allow writing NULL in the settings of the database field used to save the tag field value. |
|
When connecting to a database that uses PostgreSQL, the table names and field names in the tables prepared in the database must all be defined in lowercase. |
■ Tag settings
1.Register tags for data collection.
As an example, let's assume that eight SD tags, U01.F01.SD0008 to U01.F01.SD0015, are registered as SD tags for a virtual device.
Unit: Virtual device "U01"
Create folder: "F01"
Tag: Register "SD0008" to "SD0015"
■Action settings
1.Add a Logger (ODBC) action
Right-click Action in the left tree of FA-Server to display the menu, then select "Add" and "Action".
The Add Action dialog will appear, so select "Logger (ODBC)" and click the OK button.
A new Logger (ODBC) action has been added under Action as "A01". You can change the name, but for this example we will leave it as is.
2.Logger (ODBC) action settings
Set the properties of the Logger (ODBC) action "A01".
|
On the left is the "ODBC Settings" tab.
From the database selection, select the DBMS to connect to. In this example, select "SQLServer".
For the data source, set the ODBC data source to connect to the database "LOGGERDB" that you prepared in advance. The data source setting procedure can be done by common operations for each DBMS. For ODBC settings, please refer to the description in "ODBC Settings" in the common settings for actions.
For the table name, specify the table to be logged. Here, it is "LOGTABLE".
|
|
When connecting to a database using PostgreSQL, the table names and field names in the tables prepared on the database side must all be defined in lower case. Therefore, make sure to specify the table names below in lower case.
(Example for PostgreSQL)
|
|
In the Log Details tab, you can configure detailed settings such as the automatic deletion of old log data, how to handle errors, and how to handle when the PC clock is set back.
Leave the default settings and click "OK" to save the settings.
|
3.Define the fields of the logger (ODBC)
Next, define the fields for the logger (ODBC) action. Note that the names of the fields you add here must exactly match the field names in the database table you prepared earlier.
Right-click "A01" in the tree on the left and select "Add" and "Time Field".
A field called "F01" has been added to A01.
Next, define a field to store the tag value.
There are several ways to define tag fields, but you can easily register fields by dragging and dropping the target tag.
First, with the "A01" action displayed, select the F01 folder in the U01 unit of the tag to display the tag in the multiview.
Then, select multiple tags for which you want to perform logging, and drag and drop them onto "A01".
The Add Tag Fields dialog box will then appear.
|
In this example, leave the default settings and click Next.
|
|
Here, you specify how to create field names:
•Select "Create sequential numbers." •Specify "SD" in the header. •Specify "8" as the start number and "4" as the number of digits for sequential numbering.
Set the above conditions and click the "Finish" button.
|
|
When connecting to a database using PostgreSQL, the table names and field names in the tables prepared in the database must all be defined in lower case. Therefore, make sure to specify each field name defined in the action in lower case as well.
example) f01 sd0008 sd0009 sd0010 sd0011 sd0012 sd0013 sd0014 sd0015
|
When you display the contents of the A01 action in multiview, you can see that the following fields are registered:
Based on the above, the format of the CSV file of the log data that is generated will be such that the first column contains the date and time, and the second and subsequent columns contain the tag values from SD0008 to SD0015.
4.Define Periodic event
Next, add Periodic event which calls the logger action added above. The call cycle from this event will be the logging cycle for the log data.
Right-click on the event in the left tree of FA-Server and select "Add" "Periodic event".
This will add an Periodic event called E01. You can change the name, but for this example we will leave it as is.
Open the properties of E01 and configure it.
|
The Periodic event tab allows you to set the frequency at which the event will occur.
For now, leave it at the default setting of 1 second.
|
|
Open the Execution Action tab, select "A01" from the action name combo box, and click the "Add" button.
Verify that A01 appears in the list at the bottom, then close by clicking the "OK" button.
|
Operation check
Now let's check how it works.
Click on Online (yellow arrow) at the top of the FA-Server screen.
When you select Action from the left tree of FA-Server, "A01" will be displayed in the multiview. Confirm that the number of completed and requested items has increased.
Please make sure that the contents of the SD tags are being logged to the database. Below is an example of the contents of the LOGTABLE in SQLServer Management Studio.
