Last active
August 30, 2022 01:28
-
-
Save smching/e202f8c8bf91849b94e206e3733b5ea8 to your computer and use it in GitHub Desktop.
Node.js application: Store messages from Mosquitto MQTT broker into SQL Database
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
var mqtt = require('mqtt'); | |
var Topic = '#'; //subscribe to all topics | |
var Broker_URL = 'mqtt://192.168.1.123'; | |
var Database_URL = '192.168.1.123'; | |
var options = { | |
clientId: 'MyMQTT', | |
port: 1883, | |
//username: 'mqtt_user', | |
//password: 'mqtt_password', | |
keepalive : 60 | |
}; | |
var client = mqtt.connect(Broker_URL, options); | |
client.on('connect', mqtt_connect); | |
client.on('reconnect', mqtt_reconnect); | |
client.on('error', mqtt_error); | |
client.on('message', mqtt_messsageReceived); | |
client.on('close', mqtt_close); | |
function mqtt_connect() { | |
//console.log("Connecting MQTT"); | |
client.subscribe(Topic, mqtt_subscribe); | |
} | |
function mqtt_subscribe(err, granted) { | |
console.log("Subscribed to " + Topic); | |
if (err) {console.log(err);} | |
} | |
function mqtt_reconnect(err) { | |
//console.log("Reconnect MQTT"); | |
//if (err) {console.log(err);} | |
client = mqtt.connect(Broker_URL, options); | |
} | |
function mqtt_error(err) { | |
//console.log("Error!"); | |
//if (err) {console.log(err);} | |
} | |
function after_publish() { | |
//do nothing | |
} | |
function mqtt_messsageReceived(topic, message, packet) { | |
//console.log('Message received = ' + message); | |
insert_message(topic, message, packet); | |
} | |
function mqtt_close() { | |
//console.log("Close MQTT"); | |
} | |
//////////////////////////////////////////////////// | |
///////////////////// MYSQL //////////////////////// | |
//////////////////////////////////////////////////// | |
var mysql = require('mysql'); | |
//Create Connection | |
var connection = mysql.createConnection({ | |
host: Database_URL, | |
user: "newuser", | |
password: "mypassword", | |
database: "mydb" | |
}); | |
connection.connect(function(err) { | |
if (err) throw err; | |
//console.log("Database Connected!"); | |
}); | |
//insert a row into the tbl_messages table | |
function insert_message(topic, message, packet) { | |
var clientID= "client001"; | |
var sql = "INSERT INTO ?? (??,??,??) VALUES (?,?,?)"; | |
var params = ['tbl_messages', 'clientID', 'topic', 'message', clientID, topic, message]; | |
sql = mysql.format(sql, params); | |
connection.query(sql, function (error, results) { | |
if (error) throw error; | |
console.log("1 record inserted"); | |
}); | |
}; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment