React Express -无法将custID的值传递给insert sql语句

Question

如何解决这个问题?下面是代码，console.log(custID)显示了正确的值，但custID的值没有传递给第二个SQL

感谢

app.post("/api/Cust", (req, rsp) => {
const func = req.body.func;
const custName = req.body.custName;
const IC = req.body.IC;
const gender = req.body.gender;
const custDOB = req.body.custDOB;
const addr1 = req.body.addr1;
const addr2 = req.body.addr2;
const addr3 = req.body.addr3;
const country = req.body.country;
const email = req.body.email;
const hp = req.body.hp;
var custID=''
console.log("inside db code");
console.log(req.body.func);
if (func==="new") {
const IDStmt = "select concat('TP-',lpad(nextcust,6,'0')) as nextcust from autoref;";
db.query( IDStmt, ( err, result) => {
nextcustList = result;
console.log("inside get start");
console.log(result);
result.map( e => { custID = e.nextcust });
console.log(custID);
console.log("inside get end");
})

const sqlStmt = "insert into cust_master (custID, custName, IC, MaleFemale, Birthday, Street, BLK, City, Country, Email, HP, register_date) values ( ?,?,?,?,?,?,?,?,?,?,?,curdate() );";
db.query( sqlStmt, [custID, custName, IC, gender, custDOB, addr1, addr2, addr3, country, email, hp], ( err, result) => {
console.log(err);
})
const updStmt = "update autoref set nextcust = nextcust + 1;"
db.query( updStmt, ( err, result) => {
console.log(err);
})
}
})

Answer 1

有许多方法可以解决您的问题，但最简单的(不是最佳的)是在第一个select的回调函数中调用insert语句:

if (func==="new") {
const IDStmt = "select concat('TP-',lpad(nextcust,6,'0')) as nextcust from autoref;";
db.query( IDStmt, ( err, result) => {
nextcustList = result;
console.log("inside get start");
console.log(result);
result.map( e => { custID = e.nextcust });
console.log(custID);
console.log("inside get end");

const sqlStmt = "insert into cust_master (custID, custName, IC, MaleFemale, Birthday, Street, BLK, City, Country, Email, HP, register_date) values ( ?,?,?,?,?,?,?,?,?,?,?,curdate() );";
db.query( sqlStmt, [custID, custName, IC, gender, custDOB, addr1, addr2, addr3, country, email, hp], ( err, result) => {
console.log(err);
})
})
const updStmt = "update autoref set nextcust = nextcust + 1;"
db.query( updStmt, ( err, result) => {
console.log(err);
})
}

在第二个语句中没有得到custID值的原因是，这两个语句都是异步运行的，所以当第二个语句被调用时，第一个语句仍然在运行，并且custID没有被第一个语句修改。

你需要开始使用promise或使用Async函数，使用await来停止执行，直到上一个Async函数完成。

下面是一个用例的例子，以及我如何实现它，使它更容易阅读和维护:

const getCustID = () => {
const IDStmt = "select concat('TP-',lpad(nextcust,6,'0')) as nextcust from autoref;";
return db.query( IDStmt, ( err, result) => {
return result.map( e => { custID = e.nextcust });
});
}
const insertCustMaster = (custID) => {
const sqlStmt = "insert into cust_master (custID, custName, IC, MaleFemale, Birthday, Street, BLK, City, Country, Email, HP, register_date) values ( ?,?,?,?,?,?,?,?,?,?,?,curdate() );";
return db.query( sqlStmt, [custID, custName, IC, gender, custDOB, addr1, addr2, addr3, country, email, hp], ( err, result) => {
console.log(err);
})
}
const updateAutoref = () => {
const updStmt = "update autoref set nextcust = nextcust + 1;"
return db.query( updStmt, ( err, result) => {
console.log(err);
})
}
//this is an async function that will handle the whole operation
const newFunc = async () =>{
//here we are waiting until getCustID() is completed. 
const custID = await getCustID();
// Add a validation for the custID being retrieved before calling insertCustMaster
//Here we are using a Promise to only update Autoref if the insert is successful using the chained .then function and .catch to display the error why insert failed
insertCustMaster(custID).then(()=>updateAutoref()).catch((err)=>{
console.log('ERROR inserting into cust_master',{err})
});

}
if(func === "new"){
newFunc();
}