NodeJS: Verify if sql query was successful

Question

I made a very simple api in nodejs to use my flutter app with a sqlserver database and i have a doubt.

How can i make a verification if the query was successul to return different results?

I'm trying to do a update after a succesful insertm but i donw know how to check it, can someone help me?

Here is the api code part:

router.route("/addProduto").post((request, response) => {
    CodOS = request.body.CodOs;
    CodProduto = request.body.CodProduto;
    Qtde = request.body.Qtde;
    ValorUnitario = request.body.ValorUnitario;
    CodFuncionario = request.body.CodFuncionario;
    Sub = request.body.Sub;
    Tipo = request.body.Tipo;
    Operador = request.body.Operador;
    valorantigo = request.body.valorantigo;
    Custounit = request.body.Custounit;

    dboperations.addProduto(CodOS, CodProduto, Qtde, ValorUnitario, CodFuncionario, Sub, Tipo, Operador, valorantigo, Custounit).then(result => {


        console.log(result); 


        if (result === undefined) {
             response.json("ERROR");
         }
         else { 

        }
    })

})

As you can see i check if the result is undefined but i guess this is not the best way to do it.

So here is the query code part:

async function addProduto() {
    try {
        let pool = await sql.connect(config);
        let addProduto = await pool.request()
            .input('input_parameter1', sql.Int, CodOS)
            .input('input_parameter2', sql.Int, CodProduto)
            .input('input_parameter3', sql.Numeric, Qtde)
            .input('input_parameter4', sql.Numeric, ValorUnitario)
            .input('input_parameter5', sql.Int, CodFuncionario)
            .input('input_parameter6', sql.Numeric, Sub)
            .input('input_parameter7', sql.Char, Tipo)
            .input('input_parameter8', sql.VarChar, Operador)
            .input('input_parameter9', sql.Numeric, valorantigo)
            .input('input_parameter10', sql.Numeric, Custounit)
            .query("INSERT INTO OSProduto (CodOS ,CodProduto ,Qtde ,ValorUnitario ,CodFuncionario ,Sub ,Tipo ,Operador ,valorantigo ,Custounit) VALUES ( @input_parameter1, @input_parameter2, @input_parameter3, @input_parameter4, @input_parameter5, @input_parameter6, @input_parameter7, @input_parameter8, @input_parameter9, @input_parameter10)")
        
        return addProduto.recordsets;
    }
    catch (error) {
        console.log(error);
    }
}

In `response.json`method you can send whatever you want to your client. You could send errors or object that changed then update them on the client. — Almaju, Feb 17 '21 at 16:28

Darth · Answer 1 · 2021-02-17T14:50:34.870

You are using node-mssql, right?

Here you can see documentation for query results.

For example, you can check successfullness of insert using rowsAffected parameter:

async function addProduto() {
    try {
        const pool = await sql.connect(config);
        const addProduto = await pool.request()
            .input('input_parameter1', sql.Int, CodOS)
            .input('input_parameter2', sql.Int, CodProduto)
            .input('input_parameter3', sql.Numeric, Qtde)
            .input('input_parameter4', sql.Numeric, ValorUnitario)
            .input('input_parameter5', sql.Int, CodFuncionario)
            .input('input_parameter6', sql.Numeric, Sub)
            .input('input_parameter7', sql.Char, Tipo)
            .input('input_parameter8', sql.VarChar, Operador)
            .input('input_parameter9', sql.Numeric, valorantigo)
            .input('input_parameter10', sql.Numeric, Custounit)
            .query("INSERT INTO OSProduto (CodOS ,CodProduto ,Qtde ,ValorUnitario ,CodFuncionario ,Sub ,Tipo ,Operador ,valorantigo ,Custounit) VALUES ( @input_parameter1, @input_parameter2, @input_parameter3, @input_parameter4, @input_parameter5, @input_parameter6, @input_parameter7, @input_parameter8, @input_parameter9, @input_parameter10)")
        if (addProduto.rowsAffected == 0){
            throw 'Nothing was inserted!';
        }
        return addProduto.recordsets;
    } catch (error) {
        console.log(error);
        throw error; // catch error on level above
    }
}

For example, you can catch error by Promise catch:

router.route("/addProduto").post((request, response) => {
    dboperations.addProduto(request.body)
        .then(result => {
            console.log(result); 
            response.json("Ok!");
        })
        .catch(error => response.json({error}))
})

One more question, where should i implement the suggested solution? It could be basic but i have almost none knowledge on nodejs (i was just forced to make this api to solve a problem and make the app work) — abrev, Feb 17 '21 at 14:32
@abrev I update my answer with your code example. But it is only one variant, you can use some over way — Darth, Feb 17 '21 at 14:51
It worked in parts... but here is a problem yet, on the route part how can i check if there wasnt a error to make another action? I tried to verify if "response.son == "Ok!" but doesnt worked. — abrev, Feb 17 '21 at 20:48

NodeJS: Verify if sql query was successful

1 Answers1