Skip to content

Instantly share code, notes, and snippets.

@smching

smching/app_mqtt_mysql.js

Last active August 30, 2022 01:28
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save smching/e202f8c8bf91849b94e206e3733b5ea8 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save smching/e202f8c8bf91849b94e206e3733b5ea8 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. smching revised this gist Jul 9, 2017. 1 changed file with 4 additions and 14 deletions.
    18 changes: 4 additions & 14 deletions app_mqtt_mysql.js
    View file
    Original file line number Diff line number Diff line change
    @@ -23,42 +23,35 @@ function mqtt_connect() {
    client.subscribe(Topic, mqtt_subscribe);
    }


    function mqtt_subscribe(err, granted) {
    //console.log("Subscribed to " + Topic);
    //if (err) {console.log(err);}
    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(message);
    insert_message(topic, message, packet);
    }


    function mqtt_close() {
    //console.log("Close MQTT");
    }


    ////////////////////////////////////////////////////
    ///////////////////// MYSQL ////////////////////////
    ////////////////////////////////////////////////////
    @@ -72,17 +65,14 @@ var connection = mysql.createConnection({
    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(message) {
    function insert_message(topic, message, packet) {
    var clientID= "client001";
    var topic = "myhome/kitchen";
    var sql = "INSERT INTO ?? (??,??,??) VALUES (?,?,?)";
    var params = ['tbl_messages', 'clientID', 'topic', 'message', clientID, topic, message];
    sql = mysql.format(sql, params);
  2. smching created this gist Jul 2, 2017.
    93 changes: 93 additions & 0 deletions app_mqtt_mysql.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,93 @@
    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(message);
    }


    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(message) {
    var clientID= "client001";
    var topic = "myhome/kitchen";
    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");
    });
    };