React Express - Not able to pass the value of custID to the insert sql statement

user_17187347 0
Viewed 37

Any idea on how to solve the issue? Here is the code, the console.log(custID) shows the correct value, but the value of custID did not get passed to the second SQL

Thanks

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);
    })
}
})
reactjs express
1 Answers

There are many ways to solve your issue, but the simplest (not the optimal), is to call the insert statement inside the callback function of the first select:

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);
    })
}

The reason why you don't get the custID value in the second statement, is because both run asynchronously, so the first statement is still running when the second is being called and custID has not been modified by the first statement.

You need to start working with Promises or use Async functions with calls using await, to stop the execution until the previous async function is completed.

Here is an example of both use cases and somehow how I would have implemented it so it is easier to read and maintain:

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();
}
user_35330040
Related