如何SerialPort丟訊息Node.js接訊息 在下sql指令insert至資料庫

[glennhome]

目的

我希望能在Node.js中接到來自SerialPort變數的值,再下Sql指令insert變數的值置資料庫中

使用的工具

我在 Windows 10 下使用 npm 安裝 node.js v4.5.0

程式碼

下面附上我的程式碼以及我遇到的錯誤

程式碼

var SerialPort = require("serialport").SerialPort;
var serialport = new SerialPort("COM3", {
baudrate: 9600
});

serialport.on('open', function() {
serialport.on('data', function(data) {
console.log(data.toString());
data3=data.toString();
});
});
//*/
var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : '8769',
database : 'world'
});

connection.connect();
var data=data3;
var data2={flow:data};

connection.query('select * from waterfee', function(err, rows, fields) {
if (err) throw err;

console.log('The solution is: ', rows[0].flow);
console.log('test:',data);
console.log('test2:',data2);

});

var query = connection.query('insert into waterfee set ?',data2, function(err, result) {
if(err){
console.log(err);
return;
}
// Neat!
console.log(query.sql); // INSERT INTO posts SET id = 1, title = 'Hello MySQL'

});

connection.end();

錯誤

var data=data3;
^

ReferenceError: data3 is not defined
at Object. (D:\meeting\project\mysqltest.js:23:10)
at Module._compile (module.js:409:26)
at Object.Module._extensions..js (module.js:416:10)
at Module.load (module.js:343:32)
at Function.Module._load (module.js:300:12)
at Function.Module.runMain (module.js:441:10)
at startup (node.js:139:18)
at node.js:974:3

[chentsulin]

幫排版

var SerialPort = require("serialport").SerialPort;
var serialport = new SerialPort("COM3", {
  baudrate: 9600
});

serialport.on('open', function() {
  serialport.on('data', function(data) {
    console.log(data.toString());
    data3 = data.toString();
  });
});

//*/
var mysql = require('mysql');
var connection = mysql.createConnection({
  host : 'localhost',
  user : 'root',
  password : '8769',
  database : 'world'
});

connection.connect();
var data = data3;
var data2 = { flow: data };

connection.query('select * from waterfee', function(err, rows, fields) {
  if (err) throw err;

  console.log('The solution is: ', rows[0].flow);
  console.log('test:', data);
  console.log('test2:', data2);
});

var query = connection.query('insert into waterfee set ?',data2, function(err, result) {
  if(err){
    console.log(err);
    return;
  }
  // Neat!
  console.log(query.sql); // INSERT INTO posts SET id = 1, title = 'Hello MySQL'
});

connection.end();

[chentsulin]

因為是非同步的，所以執行步驟：

1. require("serialport").SerialPort
2. new SerialPort
3. serialport.on('open', /* */) <----- 只有註冊 function 沒有 call
4. require('mysql');
5. mysql.createConnection
6. connection.connect
7. var data = data3; <------ 爆炸

結論:

第三步的 function 要 serialport open 而且收到 data 才會 call，所以第三步時 data3 還是 undefined，部分的程式碼需要都搬到 serialport.on('data, /* */) 裡面

[glennhome]

目前已把部分code移到serialport.on('data, /* */) 裡面,但卻出現其他錯誤訊息
var SerialPort = require("serialport").SerialPort;
var serialport = new SerialPort("COM3", {
baudrate: 9600
});

var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : '8769',
database : 'world'
});

connection.connect();

connection.query('select * from waterfee', function(err, rows, fields) {
if (err) throw err;

console.log('The solution is: ', rows[0].flow);

});
//var data3;
//*
serialport.on('open', function() {

serialport.on('data', function(data) {
    console.log("1"+data.toString());
    console.log(data);
    //parseInt("") = 123 (implicit decimal)
    var data3=parseInt(data.toString());
    console.log(data3);
    var query = connection.query('insert into waterfee set ?',data3, function(err, result) {
        if(err){
            console.log(err);
            return;
        }
    connection.end();   
    // Neat! 
console.log(query.sql); // INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL'

});

});

});

錯誤訊息:
10
<Buffer 30>
0
{ [Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0' at line 1]
code: 'ER_PARSE_ERROR',
errno: 1064,
sqlState: '42000',
index: 0 }

結論:
請問是型態轉換的問題嗎?

[glennhome]

請問如果要能一直循環接受來自SerialPort的資料,connection.connect()要擺在哪???目前只能寫進一筆資料
程式碼:
var SerialPort = require("serialport").SerialPort;
var serialport = new SerialPort("COM3", {
baudrate: 9600
});

var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : '8769',
database : 'world'
});

//connection.connect();

connection.query('select * from waterfee', function(err, rows, fields) {
if (err) throw err;

console.log('The solution is: ', rows[0].flow);

});
//var data3;
//*
serialport.on('open', function() {
//connection.connect();
serialport.on('data', function(data) {
connection.connect();
console.log("1"+data.toString());
console.log(data);
//parseInt("") = 123 (implicit decimal)
var data3=parseInt(data.toString());
console.log(data3);
var query = connection.query('insert into waterfee set flow=?',data3, function(err, result) {
if(err){
console.log(err);
return;
}
//connection.end();
// Neat!
console.log(query.sql); // INSERT INTO posts SET id = 1, title = 'Hello MySQL'
//data3=data.toString();
//console.log("2"+data3);
connection.end();
});
});
錯誤訊息:
The solution is: 6
11080
<Buffer 31 30 38 30>
1080
events.js:141
throw er; // Unhandled 'error' event
^

Error: Cannot enqueue Handshake after already enqueuing a Handshake.
at Protocol._validateEnqueue (D:\meeting\project\node_modules\mysql\lib\protocol\Protocol.js:208:16)
at Protocol._enqueue (D:\meeting\project\node_modules\mysql\lib\protocol\Protocol.js:135:13)
at Protocol.handshake (D:\meeting\project\node_modules\mysql\lib\protocol\Protocol.js:52:41)
at Connection.connect (D:\meeting\project\node_modules\mysql\lib\Connection.js:136:18)
at SerialPort. (D:\meeting\project\test.js:27:14)
at emitOne (events.js:77:13)
at SerialPort.emit (events.js:169:7)
at Object.module.exports.raw as parser
at SerialPort. (D:\meeting\project\node_modules\serialport\lib\serialport.js:147:14)

[chentsulin]

connect 一次就好了，你這邊的問題是還不懂 event callback 的執行方式，把 async 的 code 當作 sync 在寫，建議先去了解一下 js event callback