DB query is returning undefined value in node.js with oracledb

Question

i am new to node.js and javascript and trying to learn the things. in my tests i need to pick a value from Oracle DB through select query and need to use it in to my code later. i am referring the same code given on https://blogs.oracle.com/opal/entry/introducing_node_oracledb_a_node and it is working fine but am not able to return the result value.

below is my code :

this.getTableData = function(){
        var res;
        oracledb.getConnection(
                {
                    user : "user",
                    password      : "password",
                    connectString : "db " 
                },
                function (err, connection) {
                    if (err) { 
                                console.error(err);
                                console.log("errorrrrrrrrrrr : "+err);
                                return;
                                }               
                    connection.execute("SELECT query",
                         function(err, result) { 
                                    if (err) { 
                                        console.error(err); 
                                            return; 
                                        }
                                    else if(result) {
                                        res = result.rows[0][0];
                                        console.log("result in else if: "+res);
                                        return res;
                                    }});
                    });
    };

the function returns undefined value.

What's the actual query? What's the value of result, as opposed to result.rows[0][0]? What's the value of result.rows? — Dave Salomon
– Dave Salomon, Commented Feb 3, 2016 at 9:22
The query is "SELECT OTPCODE FROM (SELECT OTPCODE FROM ONETIMEPASSWORD WHERE MSISDN = 91123456789 Order BY GENERATED_ON DESC) WHERE ROWNUM = 1" and the result.rows & result.rows[0][0] returns as six digit value from table like 123456 — Sonia Virk
– Sonia Virk, Commented Feb 3, 2016 at 9:27

Adam · Accepted Answer · 2016-02-03 13:18:24Z

8

Of course it returns undefined. It's because of async callback functions. You'll need to do something like this:

this.getTableData = function(callback){
    oracledb.getConnection(
        {
            user : "user",
            password      : "password",
            connectString : "db "
        },
        function (err, connection) {
            if (err) {
                console.error(err);
                console.log("errorrrrrrrrrrr : "+err);
                return;
            }
            connection.execute("SELECT query",
                function(err, result) {
                    if (err) {
                        console.error(err);
                        return;
                    }
                    else if(result) {
                        var res = result.rows[0][0];
                        console.log("result in else if: "+res);
                        callback(res);
                    }});
        });
};

getTableData(function (result) {
    console.log(result);
});

The other way you could solve this problem is using a Promise:

this.getTableData = function () {
    return new Promise(function (resolve, reject) {
        oracledb.getConnection(
            {
                user: "user",
                password: "password",
                connectString: "db "
            },
            function (err, connection) {
                if (err) {
                    console.error(err);
                    reject(err);
                    console.log("errorrrrrrrrrrr : " + err);
                    return;
                }
                connection.execute("SELECT query",
                    function (err, result) {
                        if (err) {
                            console.error(err);
                            reject(err);
                            return;
                        }
                        else if (result) {
                            var res = result.rows[0][0];
                            console.log("result in else if: " + res);
                            resolve(res);
                        }
                    });
            });
    });

};

getTableData()
    .then(function (result) {
        console.log(result);
    });

The code you've asked for in your comment:

var AddPage = function () {
    var self = this;
    this.enterOtpInput = element(by.model("beneDetail.otp"));
    this.enterMpinInput = element(by.model("retailerMpin"));
    this.verifyBeneficiaryButton = element(by.xpath("//div[2]/div/button"));
    this.verifyBene = function () {
        support.getTableData()
            .then(function (result) {
                console.log("adam: " + result);
                self.enterOtpInput.sendKeys(result);
                self.enterMpinInput.sendKeys("1111");
                self.verifyBeneficiaryButton.click();
            });

    };
}

edited Feb 3, 2016 at 13:18

answered Feb 3, 2016 at 9:21

Adam

5,2933 gold badges33 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Sonia Virk Over a year ago

Thank you so much :) the above code is working. but i have one more requirement as i need to input the value of result into a textfield on webpage as : this.element(by.model("otp")).sendKeys(result);

Sonia Virk Over a year ago

is their any way i can set value of result to some global variable and use that into sendKeys() as parameter value. I m using Protractor + Jasmine framework. Thanks for Help in advance.

Sonia Virk Over a year ago

Thanks a lot but i have tried in this way but it is not working. the statement self.element(by.model("otp")).sendKeys(result); is not getting executing.

Adam Over a year ago

I could only help if I saw where you're trying to call the function.

Sonia Virk Over a year ago

the code is a complete JS file ; var AddPage = function() { this.enterOtpInput = element(by.model("beneDetail.otp")); this.enterMpinInput = element(by.model("retailerMpin")); this.verifyBeneficiaryButton = element(by.xpath("//div[2]/div/button")); this.verifyBene = function() { support.getTableData().then(function (result) { console.log("adam: "+result); this.enterOtpInput.sendKeys(result); }); this.enterMpinInput.sendKeys("1111"); this.verifyBeneficiaryButton.click(); };

|

Collectives™ on Stack Overflow

DB query is returning undefined value in node.js with oracledb

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest