HTTPS POST to mySQL on localhost


#21

For one, you are doing exactly the same thing in all three if() blocks, so there is no need for a distinction between the cases.
But if I understand correctly the main point is that you are receiving three events independent/asynchronous of eachother where each of them only provides one value. Hence you won’t be able to use a single INSERT statement which would expect to find all data points in the incoming JSON.

Before we can advise you better you need to tell us how you intend to synchronise your data as you probably want the three columns somehow related to eachother - or do you just want to re-insert any previous value of the other two data points when any one of the three gets reported?

BTW, you have console.log("storeData", data) in your store function.
Have you had a look at that log and tried to figure why your approach didn’t work?


#22

Thanks for the reply, this is my first encounter with JavaScript so sorry if my question was difficult to understand. I do want all three sensors related to each other by the timestamp although I know they are sent less than a second from each other. I have one table with three columns (bpm, emg, gsr) and want them to be inserted together in the same row. I understand now that the cloudEventHandler gets each event separately and returns that data to the next function so I need to find a way to store all three data values at a time, and then send those three to mySQL before the next three are received.


#23

Hello, I’ve actually solved my own question here and forgot to reply with my answer in case it will help anyone in the future.
In my case, I am reading three different sensors, collecting the values from all three sensors on the cloud and inserting them into a mysql table containing all three in different columns. Here is my mysqlsample.js file I have edited to do so.

var config = require('./config.js');
var Particle = require('particle-api-js');
var particle = new Particle();

var mysql = require('mysql');
console.log("starting database connection");
var con = mysql.createConnection(config.mysql);
console.log("starting event stream listener");
var values = [[0,0,0]];


con.connect(function(err) {
	if (err) throw (err);
	console.log("Connected!")
});

particle.login({username: 'myemail@gmail.com', password: '********'}).then(
	function(data) {
		token = data.body.access_token;
	},
	function(err) {
		console.log('Could not login.', err);
});

particle.getEventStream({deviceId: 'e00fce68cb****************', auth: config.authToken}).then(
	function(stream) {
		stream.on('event', cloudEventHandler);
	
	},
	function(err) {
		console.log("error starting event listener", err);
		process.exit(1)
	});
	
cloudEventHandler = function(data) {
	//console.log(bpmData, emgData, gsrData);
	
	if (data.name == 'bpm'){
		var	bpm = JSON.parse(data.data);
		values.splice(0,1,bpm);
		
	}	
	if (data.name == 'emg'){
		var emg = JSON.parse(data.data);
		values.splice(1,1,emg);
		
	}
	if (data.name == 'gsr'){	
		var gsr = JSON.parse(data.data);
		values.splice(2,1,gsr);
		storeData(values);
		
	}
	
	
}


function storeData(values) {
	var sql = "INSERT INTO sensors (bpm, emg, gsr) VALUES ?";
	console.log(values);
	con.query(sql, [[values]], function(err,result) {
		if (err) throw err;
		console.log("id=" + result.insertId);
		
 	});
}

This inserts all three individual sensor values into my table corresponding to the same time stamp. I have seen where one of my sensors publishes before the other two can publish again, and my code still knows to insert the correct ones into the corresponding column.
Hope this can help someone in the future!


#24

Hi Rickkasy -

With the help of ScrruffR I have managed to get the data I require to be sent successfully to Particle cloud, but the challenge for me is that from here I was hoping to send the data to a mySQL DB hosted on a shared server we have with Hetzner. We are building a UI that will in turn retrieve the data form the mySQL DB. I was hoping you can assist?

I have done quite a bit of research into existing cloud services but most of them seems overkill for our purposes as we only really require the data to be published into the SQL DB.

I am extremely new to this, so please accept my apologies is the question is more trivial than I think :slight_smile:

Regards,
Friedl


#25

This does work and i am also using but it is very slow. Establishing initial connection takes quite a bit of time sometimes even 1 minute or more.