How to use a second query in a Promise in JS ? using mysql db-CodePudding

Basically i got a createorder that needs to add the order into the database , and all the products in the order into an Orderline extra table.

Been trying different ways to get the second query to work but the only thing that gets inserted into the db is the data from the first query.

the second query also needs to use the id from the inserted order of the first query. since the database uses autoincrement for the id of the order.

CreateOrder: (req) => {
        return new Promise((resolve, reject) => {
            const sqlInsert = 'INSERT INTO web3.orders (Created, firstName, lastName, street, number, postalCode, city, telephone, email, totalPrice) VALUES (?,?,?,?,?,?,?,?,?,?);';
            const sqlInsert2 = 'INSERT INTO web3.orderline (orderId,productId,qty,price) VALUES (?,?,?,?);';

            db.query(sqlInsert, [
                req.body.Created,
                req.body.firstName,
                req.body.lastName,
                req.body.street,
                req.body.number,
                req.body.postalCode,
                req.body.city,
                req.body.telephone,
                req.body.email,
                req.body.totalPrice],
                (err, result, fields) => {
                    if (err) {
                        reject(err);
                    }
                    else {
                        resolve(result);

                    }
                });
                db.query(sqlInsert2,
                    [
                        result.insertId,
                        req.body.producten.id,
                        req.body.producten.cartQuantity,
                        req.body.producten.price], (err, result, fields) => {
                            if (err) {
                                reject(err);
                            } else {
                                resolve(result);
                            }
                        });
                    });
                },
            }

CodePudding user response：

You'll need two promises: one for each query. And the second one should be created only when the first has resolved.

A nice way to do this, is to promisify db.query into a new function, and only use that function from then on. So define this first:

const promiseQuery = (sql, params) => new Promise((resolve, reject) =>
    db.query(sql, params, (err, result, fields) => err ? reject(err) : resolve(result))
);

Secondly, you can make your CreateOrder an async function so that you can use await:

async CreateOrder: (req) => {
    const sqlInsert = 'INSERT INTO web3.orders (Created, firstName, lastName, street, number, postalCode, city, telephone, email, totalPrice) VALUES (?,?,?,?,?,?,?,?,?,?);';
    const sqlInsert2 = 'INSERT INTO web3.orderline (orderId,productId,qty,price) VALUES (?,?,?,?);';
    let result = await promiseQuery(sqlInsert, [
        req.body.Created,
        req.body.firstName,
        req.body.lastName,
        req.body.street,
        req.body.number,
        req.body.postalCode,
        req.body.city,
        req.body.telephone,
        req.body.email,
        req.body.totalPrice
    ]);
    await promiseQuery(sqlInsert2, [
        result.insertId,
        req.body.producten.id,
        req.body.producten.cartQuantity,
        req.body.producten.price
    ]);
}

CodePudding user response：

the above code is correct same flow works here

async function CreateOrder(req) {
    const sqlInsert = 'INSERT INTO legs (leg) VALUES (?);';
    const sqlInsert2 = 'INSERT INTO destinations (destination,area) VALUES (?,?);';
    let result = await promiseQuery(sqlInsert, [
        "lw"
    ]);
  let res=   await promiseQuery(sqlInsert2, [
        "test",
        "test location"
    ]);
    console.log(res)
}

const promiseQuery = (sql, params) => new Promise((resolve, reject) =>
    db.query(sql, params, (err, result, fields) => err ? reject(err) : resolve(result))
);
async function test(){
   await CreateOrder()
}

try checking if any of the key is undefined or there is a mismatch in the field type and the value you are passing