Skip to content

SQL Database pusher

You can now push data from ResIOT to your SQL Databases. As of March 2019, ResIOT supports pushing data to MySQL, SQL Server and PostgreSQL.

Due to the nature of the connector, ResIOT will not push any data automaticallly to these connectors. In order to push data a SQL connector, you will have to rely to automation and LUA scripting, specifically to the resiot_dc_push LUA function.

Configuration

Database: Select the type of database. MySQL, SQL Server and PostgreSQL are supported. Required.

Connection String: You need to supply a connection string for the database connection to open successfully! Required.

MySQL Connection String format

username:password@tcp(host:port)/dbname

SQL Server Connection String format

sqlserver://username:password@host:port/dbname

PostgreSQL Connection String format

postgres://username:password@host:port/dbname

Pushing data to a Database Pusher

ResIOT is able to execute SQL queries via LUA scripting and the function resiot_dc_push LUA function.

Notice that queries will be executed but no data will be returned, so the pusher is not intended for SELECT queries.

Here's how to push data to your connector using resiot_dc_push LUA function:

myConnector = "F26f6e333339" --Hex ID of your pusher
err = resiot_dc_push(myConnector, "insert into test(test) values(25)")
resiot_debug(err) --empty if everything went fine, otherwise contains error details

As you can see, using placeholders in your queries is not mandatory, but it's something ResIOT and resiot_dc_push LUA function support.

Due to the nature of the different database drivers implemented by the platform, each type of database uses its own syntax for placeholders.

Here's some examples to get you started:

MySQL placeholders

myConnector = "F26f6e333339" --Hex ID of your MySQL pusher
err = resiot_dc_push(myConnector, "insert into test(f1, f2) values(?, ?);", 25, "test")
resiot_debug(err) --empty if everything went fine, otherwise contains error details

SQL Server placeholders

myConnector = "F26f6e333339" --Hex ID of your SQL Server  pusher
err = resiot_dc_push(myConnector, "insert into test(f1, f2) values(@p1, @p2);", 25, "test")
resiot_debug(err) --empty if everything went fine, otherwise contains error details

PostgreSQL placeholders

myConnector = "F26f6e333339" --Hex ID of your PostgreSQL pusher
err = resiot_dc_push(myConnector, "insert into test(f1, f2) values($1, $2);", 25, "test")
resiot_debug(err) --empty if everything went fine, otherwise contains error details