# Save data to SQLServer

Set up a SQLServer database and set the user name and password to sa/mqtt_public. Take MacOS X as an example:

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=mqtt_public' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest
1

Enter the SQLServer container and initialize the SQLServer table:

$ /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P mqtt_public -d master
$ mysql -u root -h localhost -ppublic
1
2

Create the "test" database:

CREATE DATABASE test;
go;
1
2

Create the t_mqtt_msg table:

USE test;
go;
CREATE TABLE t_mqtt_msg (id int PRIMARY KEY IDENTITY(1000000001,1) NOT NULL,
                         msgid   VARCHAR(64) NULL,
                         topic   VARCHAR(100) NULL,
                         qos     tinyint NOT NULL DEFAULT 0,
                         payload NVARCHAR(100) NULL,
                         arrived DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);
go;
1
2
3
4
5
6
7
8
9

Configure odbc driver in Mac:

$ brew install unixodbc freetds
$ vim /usr/local/etc/odbcinst.ini
[ms-sql]
Description = ODBC for FreeTDS
Driver      = /usr/local/lib/libtdsodbc.so
Setup       = /usr/local/lib/libtdsodbc.so
FileUsage   = 1
1
2
3
4
5
6
7

Configure odbc driver in CentOS:

$ yum install unixODBC unixODBC-devel freetds freetds-devel perl-DBD-ODBC perl-local-lib
$ vim /etc/odbcinst.ini
# add as below
[ms-sql]
Description = ODBC for FreeTDS
Driver      = /usr/lib64/libtdsodbc.so
Setup       = /usr/lib64/libtdsS.so.2
Driver64    = /usr/lib64/libtdsodbc.so
Setup64     = /usr/lib64/libtdsS.so.2
FileUsage   = 1
1
2
3
4
5
6
7
8
9
10

Configure odbc dirver in Ubuntu:

$ apt-get install unixodbc unixodbc-dev unixodbc-bin tdsodbc freetds-bin freetds-common freetds-dev libdbd-odbc-perl liblocal-lib-perl
$ vim /etc/odbcinst.ini
# add as below
[ms-sql]
Description = ODBC for FreeTDS
Driver      = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup       = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
FileUsage   = 1
1
2
3
4
5
6
7
8

Create rules:

Open EMQ X Dashboard (opens new window) and select the "Rules" tab on the left.

Fill in the rule SQL:

SELECT * FROM "t/#"
1

image

Related actions:

On the "Response Action" interface, select "Add", and then select "Save Data to SQLServer" in the "Action" drop-down box.

image

Fill in the action parameters:

The "Save data to SQLServer" action requires two parameters: 1). SQL template. In this example, we insert a piece of data into SQLServer, and the SQL template is:

insert into t_mqtt_msg(msgid, topic, qos, payload) values ('${id}', '${topic}', ${qos}, '${payload}')
1

image

2). The ID of the associated resource. Now the resource drop-down box is empty, and you can click "New Resource" in the upper right corner to create a SQLServer resource:

Fill in the resource configuration: Fill in “mqtt” for database name, “sa” for user name, and “mqtt_public” for password

image

Click the "New" button.

Return to the response action interface and click "OK".

image

Return to the rule creation interface and click "Create".

image

In the rule list, click the "View" button or the rule ID connection to preview the rule you just created:

image

The rule has been created. Now, send a piece of data:

Topic: "t/a"
QoS: 1
Payload: "hello"
1
2
3

Then check the SQLServer table to see whether the new record is added successfully:

image