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/instancename

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 dbname.dbo.tablename(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