MagicMirror to msSQL

ales.krohne

Hi everybody!
I have tried to develop a module, which takes data from SQL database and shows it on the screen of MagicMirror. However, the result that comes out is always blank. SQL database is on another computer in local network. Connection is tested and works. I have installed “npm install mssql” and I have the following code in my module:

Module.register("todolist", {

    defaults: {
    },

    // Define required scripts.
    getStyles: function() {
        return ["todolist.css"];
    },

    

    getDom: function() {
        var finished = false;
        var wrapper = document.createElement("div");
        wrapper.innerHTML = "tekst";

        const sql = require('mssql');

        sql.connect('Server=192.168.1.103, 1433;Database=todo;User Id=rpii;Password=12345', function (err) {
            if (err) {
                wrapper.innerHTML += "Error1: "+err;
                finished =true;
                return;
            }

            new sql.Request().query('SELECT task FROM todo', function (err, result) {
                if (err) {
                    wrapper.innerHTML += "Error2:"+err;
                    finished =true;
                    return;
                }
                
                wrapper.innerHTML += "Result:"+result;
                finished =true;
            });
            
        });
        
        //while(!finished) {}
        return wrapper;
    }
});

The code decompiles without any problems, the module shows on the screen, but the result section is empty.
I would really appreciate your help since this is my school project.
Thank you in advance and have a nice day :)

Note from admin: Please use Markdown on code snippets for easier reading!

strawberry 3.141

@ales.krohne it’s not possible to use require in the browser like this. You need to read about the node_helper or check other modules. Require is a feature of nodejs and therefore can only be used there. You have to create one of this node_helper for your module and then communicate between your module via socket notifications

cowboysdude

What I would do and do with my todo list is run it off of the other server under apache and it will have a localhost address ie 192.168.x.x then put it in an iframe.

I have a couple ‘modules’ running that way…

I put ip in the top of the config.js file under units so I could access it there by typing in this.config.ip so if that changed then I"d only have to go to one place to change it, instead of having to change it in multiple places :)

I’m running my todo list and a thing called House Panel [https://github.com/open-dash/HousePanel] using this method.

OF course the other thing is that I have to run in node serveronly method and then localhost:8080 in a web browser but it works just fine!

Works on mine :)

E3V3A

My guess is that the remote query must be an asynchronous task and thus the values you get are just the object placeholders for the promise. Somehow, I suspect you need to wait to make sure there actually is any data in your results. (Unless this is already handles automagically in the mysql node package.)

Alternatively, you could just put your sql query in a command line script (Bash or python) and execute that with your node_helper. In that way you could also “pre-format” your output data into CSV or JSON or what have you, and then present it using Tabulator (which doesn’t require any DOM coding, or at least extremely little, since it only need one HTML tag and some css.)

ales.krohne

Thank you for all the replies. I have now taken a look and changed my code into following, but still no success :(

node_helper.js code:

"use strict";

const NodeHelper = require("node_helper");
const sql = require('mssql');

module.exports = NodeHelper.create({
    start: function() {
        console.log("Starting node helper for: " + this.name);
    },

    socketNotificationReceived: function(notification, payload) {
        if (notification === "CONFIG") {
		this.sendSocketNotification("STARTED",true);
            	this.config = payload;
            	this.fetchTodos();
        }
    },
    
    fetchTodos : function() {
        var self = this;

        sql.connect('Server=10.1.150.17\SQLEXPRESS;Database=todo;User Id=rpi;Password=12345', function (err) {
		if (err) {
			return self.sendSocketNotification("TASKS", eval("Connection error"));;
		}
	
		new sql.Request().query('SELECT task FROM todo', function (err, result) {
			if (err) {
				return self.sendSocketNotification("TASKS", eval("Query error"));;
			}
			
			self.sendSocketNotification("TASKS", result);
		});
		
	});


	setTimeout(function() { self.fetchTodos(); }, 60*60*1000);
    }
});

And todolist.js code:

Module.register("todolist", {

	defaults: {
	},

	// Define required scripts.
	getStyles: function() {
		return ["MMM-Todoist.css"];
	},


	start: function() {
		Log.info('Starting module: ' + this.name);

		this.loaded = false;
		this.sendSocketNotification('CONFIG', this.config);
	},

	
	socketNotificationReceived: function(notification, payload) {
    		if (notification === "STARTED") {
				this.updateDom();
			}
			else if (notification === "TASKS") {
				this.data = JSON.parse(payload);
				this.loaded = true;
				this.updateDom();
    		}
	},





	getDom: function() {
		var wrapper = document.createElement("div");
		
		if (!this.loaded) {
			wrapper.innerHTML = 'LOADING';
			return wrapper;
		}

		if (!this.data) {
			wrapper.innerHTML = "No data";
			return wrapper;
		}

		wrapper.innerHTML = JSON.stringify(this.data);
		

		return wrapper;
	}
});

Fistandantilus

Hi, is it working now? I need to query a database as well to get some information from my home automation system. Currently I´m thinking about how to solve that best.

F.

ales.krohne

Fistandantilus hi!

Yes I have managed to get the connection working. I have used Sequelize library and it works great now. If you want to know more, please write me to [email protected]. I can send you whole code there.

Have a nice day.

rsellmer

Hi, I’ve sent an e-mail to you because I am new in MM and I need to do something like yours.
Please, help me.
Tks,
Renato