CODEWITHSUNDEEP
javascriptmysqlsqlnode.js
user9229318
user9229318

Reputation:

I need to run two queries

I have an application where I am entering a new record onto a database with an identifier.

My first statement is an if statement that wraps the entire document:

if(body['id'] && body['business'] && body['contact_no'] && body['contact_email'] && body['addr_1'] && body['addr_2'] && body['post_code'] && body['gs_unit'] && body['timezone']){

     (everything goes here)
}

The if statement above just makes sure that there is a value for everything (or that it exists).

The next step is to run a query that checks if something is registered with that id:

var sql1 = "SELECT * FROM epas_plc WHERE uid = ?";

conn.query(sql1,[id], function (err1, resp1) {

    console.log(resp1.length);
    var rlength = resp1.length;

    if(resp1.length == 1){
            res.send({'status' : 'error', 'information' : 'id already exsists'});
            return;
    }

});

The above query works, and will give the response listed if there is a match. The next query should execute if there is no match, rlength as defined in the query above:

if (rlength == 0) {
    var sql = "INSERT INTO epas_plc (uid, business, contact_no, contact_email, addr_1, addr_2, post_code, gs_unit, timezone) VALUES(?,?,?,?,?,?,?,?,?)";

    conn.query(sql,[id, body['business'], body['contact_no'], body['contact_email'], body['addr_1'], body['addr_2'], body['post_code'], body['gs_unit'], body['timezone']], function(error, response){

        if(error) throw error;

        res.send({'status' : 'success', 'information' : 'successfully created entry with id: ' + req.body.id});
    })

}

This does not work unfortunately as Can't set headers after they are sent.

Does anyone know how to solve this problem, and/or have nested queries?

Edit: SQL Module is MYSQL

Upvotes: 1

Views: 140

Answers (2)

BrTkCa
BrTkCa

Reputation: 4783

I can see some troubles in your code, like the return after send statement, and when to check the error. I propose this solution:

var sql1 = "SELECT * FROM epas_plc WHERE uid = ?";

conn.query(sql1, [id], function(err1, resp1) {

    console.log(resp1.length);
    var rlength = resp1.length;
    if (err1) res.status(500).send({
        'msg': 'error found'
    });

    if (rlength == 1) {
        res.send({
            'status': 'error',
            'information': 'id already exsists'
        });
    } else {
        var sql = "INSERT INTO epas_plc (uid, business, contact_no, contact_email, addr_1, addr_2, post_code, gs_unit, timezone) VALUES(?,?,?,?,?,?,?,?,?)";

        conn.query(sql, [id, body['business'], body['contact_no'], body['contact_email'], body['addr_1'], body['addr_2'], body['post_code'], body['gs_unit'], body['timezone']], function(error, response) {

            if (error) res.status(500).send({
                'msg': 'error found'
            });

            res.send({
                'status': 'success',
                'information': 'successfully created entry with id: ' + req.body.id
            });
        })
    }

});

It's important to keep the code clear, doesn't make sense to use var rlength = resp1.length; and after check it with if(resp1.length == 1){. When some error is found, you can send it to who calls because it is in a request scope.

Upvotes: 0

scagood
scagood

Reputation: 782

I think the problem you're having is 'scope' and 'async' related.

Firstly, Scope:

The 'scope' issue here is that var rlength = resp1.length; is declared within the first query's function.

This means if you have the two queries inline with each other like:

conn.query(sql1,[id], function (err1, resp1) {
    // ... 
    var rlength = resp1.length;
    // ... 
});

// You will find that 'rlength' is not defined
// console.log(typeof rlength);
if (rlength == 0) {
    // ...
}

This is solved by placing the 'if statement' inside the first query function like so:

conn.query(sql1,[id], function (err1, resp1) {
    // ...
    var rlength = resp1.length;
    // ...
    if (rlength == 0) {
        // ...
    }
});

Secondly, async

This problem is slightly less obvious, if the code is structured like so:

conn.query(sql1,[id], function (err1, resp1) {
    // ...
});

if (rlength == 0) {
    // ...
}

The 'if statement' will execute independently of whether the query is complete. Meaning the 'if statement' could execute before the query.

Upvotes: 0

Related Questions