MagicMirror to msSQL

[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 ales.krohne@hotmail.com. 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