I would like to seek help on my Temperature REST API. I just started node.js two weeks ago and manage to created a basic REST API with getting DS18B20 Temperature Sensor ID and Reading. Able to work perfectly without any errors or problems but when I start to implement function of storing to mySQL database into my index.js and execute app.js, I got an error and I'm lost now.
Hope you guys could help me out with it as soon as possible, thanks in advance, guys.
This is my app.js:
var express = require('express');
var path = require('path');
var favicon = require('static-favicon');
var logger = require('morgan');
var cookieParser = require('cookie-parser');
var bodyParser = require('body-parser');
var index = require('./routes/index');
var app = express();
// View engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');
app.set('port', process.env.PORT || 3000);
app.use(favicon());
app.use(logger('dev'));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded());
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));
app.use('/', index);
// Catch 404 and forward to error handler
app.use(function(req, res, next) {
var err = new Error('Not Found');
err.status = 404;
next(err);
});
// error handlers
// development error handler
// will print stacktrace
if (app.get('env') === 'development') {
app.use(function(err, req, res, next) {
res.status(err.status || 500);
res.render('error', {
message: err.message,
error: err
});
});
}
// production error handler
// no stacktraces leaked to user
app.use(function(err, req, res, next) {
res.status(err.status || 500);
res.render('error', {
message: err.message,
error: {}
});
});
module.exports = app;
var server = app.listen(app.get('port'), function() {
console.log('Listening on port %d', server.address().port);
});
This is my /routes/index.js:
var express = require('express');
var router = express.Router();
var mysql = require('mysql');
var gpio = require('pi-gpio');
var sensor = require('ds18b20');
var conn = {
host: 'localhost',
user: 'user',
password: 'password',
database: 'rest_api',
port: '3306'
};
router.get('/', function(req, res) {
var pin = req.params.pin;
var connection = mysql.createConnection(conn);
//Get ID and Reading from DS18B20 Temperature Sensor
sensor.getAll(function (err, tempObj) {
console.log(tempObj);
});
//Store to mySQL database
sensor.get('10-000802be4633', function (err, temp) {
connection.query('INSERT INTO sensorLog(sensorId, logTime, sensorValue) VALUES(4, current_timestamp(), temp)', function(err) {
if (err) {
throw err;
} else {
console.log("Successfully store to mySQL database");
}
});
});
});
module.exports = router;
This is my /views/index.ejs:
<!DOCTYPE html>
<html>
<head>
<title><%= title %></title>
<link rel='stylesheet' href='/stylesheets/style.css' />
</head>
<body>
</body>
</html>
This is my error message:
/var/www/node_modules/mysql/lib/protocol/Parser.js:82
throw err;
^
Error: ER_BAD_FIELD_ERROR: Unknown column 'temp' in 'field list'
at Query.Sequence._packetToError (/var/www/node_modules/mysql/lib/protocol/s equences/Sequence.js:48:14)
at Query.ErrorPacket (/var/www/node_modules/mysql/lib/protocol/sequences/Que ry.js:82:18)
at Protocol._parsePacket (/var/www/node_modules/mysql/lib/protocol/Protocol. js:251:24)
at Parser.write (/var/www/node_modules/mysql/lib/protocol/Parser.js:77:12)
at Protocol.write (/var/www/node_modules/mysql/lib/protocol/Protocol.js:39:1 6)
at Socket.<anonymous> (/var/www/node_modules/mysql/lib/Connection.js:82:28)
at Socket.emit (events.js:95:17)
at Socket.<anonymous> (_stream_readable.js:748:14)
at Socket.emit (events.js:92:17)
at emitReadable_ (_stream_readable.js:410:10)
--------------------
at Protocol._enqueue (/var/www/node_modules/mysql/lib/protocol/Protocol.js:1 35:48)
at Connection.query (/var/www/node_modules/mysql/lib/Connection.js:184:25)
at /var/www/routes/index.js:38:15
at /var/www/node_modules/ds18x20/lib/ds18x20.js:47:20
at /var/www/node_modules/ds18x20/node_modules/async/lib/async.js:232:13
at /var/www/node_modules/ds18x20/node_modules/async/lib/async.js:119:25
at /var/www/node_modules/ds18x20/node_modules/async/lib/async.js:24:16
at /var/www/node_modules/ds18x20/node_modules/async/lib/async.js:229:17
at /var/www/node_modules/ds18x20/lib/ds18x20.js:42:24
at /var/www/node_modules/ds18x20/lib/reader.js:29:10
I believe you need to gpio.close(...) when you're done with the pin.
E.g.
router.get('/:pin/1', function(req, res) {
// ...
gpio.open(pin, 'output', function(err) {
// ...
gpio.close(pin, function(err) {
// done
});
});
});
Otherwise the pin may still be open from a previous request.
Edit:
Additionally, you need to use the value of the variable temp in your SQL query, not literally temp. Right now, your query looks something like this:
INSERT INTO sensorLog (sensorId, logTime, sensorValue)
VALUES (4, current_timestamp(), temp)
What you actually need to be using is something like:
INSERT INTO sensorLog (sensorId, logTime, sensorValue)
VALUES (4, current_timestamp(), 0)
That is, the SQL query needs to contain a number there. The database has no idea what temp means; it thinks you're referring to a column, but there isn't a column with that name.
You need to change your query to use the actual value of temp like so:
connection.query('INSERT INTO sensorLog (sensorId, logTime, sensorValue) '
+ 'VALUES (4, current_timestamp(), ' + temp + ')', function(err) {
// function body
});