FAQ
- DeviceGateway
How to access the database with DeviceGateway on Docker.
2024.12.11
DeviceGateway uses a SQL client tool "relaySQL" to access the database.
If there is a setup database and you would like to connect to it, please read this section.
If you would like to set up the PostgreSQL as well as relaySQL, please read this section.
To access the database with relaySQL
This section shows how to access the following database from DeviceGateway as an example.
Database Example (PostgreSQL)
In this article, PostgreSQL is used. Here is an example of the database information.
*You can scroll this table sideways.
| Database | PostgreSQL |
|---|---|
| Location | Installed on the host machine (localhost) |
| Port No. | 5432 |
| Database Name | db1 |
| Table Name | table1 |
| Table Columns |
inserted_at (timestamp) |
| Access User (Password) | user1 (user1) |
| Others | External access permission has been granted. |
relaySQL (Docker Container)
Download the relaySQL container "SQL client tool: relaySQL on Docker" from the download page.
Extract the downloaded file, and edit the "ds.json" fiile according to the target database.
In this example case, there is no need to change the "ds.json" file.
Launch relaySQL container with the shell script "start.sh", and it will automatically download the necessary components.
(ds.json)
DeviceGateway (Docker Container)
Edit the "docker-compose.yml" file of DeviceGateway, and insert the following text in "extra_hosts" section.
After that, launch the container of DeviceGateway with the shell script "start.sh".
extra_hosts:
- cm_server:host-gateway
- relaysql:host-gateway
(docker-compose.yml)
DeviceGateway Configuration
IoT Interface (SQL)
Create a SQL Client in IoT Inteface tab, and set the settings as follows.
This settings are related to the "ds.json" file of relaySQL.
(OPC Client settings)
*You can scroll this table sideways.
| DB relay server address | relaysql |
|---|---|
| Data source ID | psql1 |
| User name | user01 |
| Password | pass |
Event (Send SQL action)
Create an event and set a trigger.
In action list, create an "Send SQL" action and set it as follows.
*You can scroll this table sideways.
| Destination Server | The SQL client setting name you created in the "IoT Interface" tab. |
|---|---|
| SQL query (Example) |
INSERT INTO table1 (inserted_at,int_data1, real_data1, text_data1) VALUES ('<%=@nowLocal("yyyyMMdd HH:mm:ss.ms")%>', 1234, 56.789, 'abcdefg') |
| SELECT query |
Invalid |
Results
DeviceGateway will execute SQL queries to the database according to the Send SQL action settings.
If the SQL queries do not work correctly, please check the log messages, the running status of both the SQL Client and the SQL Send action in DeviceGateway.
To setup the database and relaySQL
This section shows how to setup relaySQL and PostgreSQL database on Docker container.
relaySQL with PostgreSQL on Docker
Download the relayPostgreSQL container "SQL client tool: relaySQL with PostgreSQL on Docker" from the download page.
Extract the downloaded file, and edit the '/PostgreSQL/initdb/1_create_table1.sql' file to configure the table creation script.
This script defines a table to be create at the time of database creation.
In this example case, you don't need to change the scipt file.
Launch the relayPostgreSQL container with the shell script "start.sh", and it will automatically download the necessary components.
(PostgreSQL/initdb/1_create_table1.sql)
DeviceGateway (Docker Container)
Edit the "docker-compose.yml" file of DeviceGateway, and insert the following text in "extra_hosts" section.
After that, launch the container of DeviceGateway with the shell script "start.sh".
extra_hosts:
- cm_server:host-gateway
- relaysql:host-gateway
(docker-compose.yml)
DeviceGateway Configuration
IoT Interface (SQL)
Create a SQL Client in IoT Inteface tab, and set the settings as follows.
This settings are related to the "docker-compose.yml" file of relayPostgreSQL.
(SQL Client settings)
*You can scroll this table sideways.
| DB relay server address | relaysql |
|---|---|
| Data source ID | psql1 |
| User name | user01 |
| Password | pass |
Event (Send SQL action)
Create an event and set a trigger.
In action list, create an "Send SQL" action and set it as follows.
*You can scroll this table sideways.
| Destination Server | The SQL client name you created in the "IoT Interface" tab. |
|---|---|
| SQL query (Example) |
INSERT INTO table1 (created_at, string1, data1, data2) VALUES ('<%=@nowUtc("yyyyMMdd HH:mm:ss.ms")%>', 'DeviceGateway', 12345, 6789) |
| SELECT query |
Invalid |
Results
DeviceGateway will execute SQL queries on the database according to the Send SQL action settings.
If the SQL queries do not work correctly, please check the log messages, the running status of both the SQL Client and the SQL Send action in DeviceGateway.
