Try to search your question here, if you can't find : Ask Any Question Now ?

Get method – Why can't I return the values from an Oracle view?

HomeCategory: stackoverflowGet method – Why can't I return the values from an Oracle view?
Avatarwillson asked 4 months ago

I searched for a good example of Node+Express+Oracle. I need to read values from an Oracle view to create the response to a GET operation.

Most examples does not use “Express” (my choice of framework) and simply log the database information. Here are some of the best I found : from Express, from Oracle 1, from Oracle 2

Here is the one I preferred to base my code on. I like it because it includes Express, and actual manipulation of the response data. Sadly, it still does not work for me. And I am wondering what the issue is.

I think I understand the asynchronous nature of the JavaScript language (ie: callbacks), and I am beginning to suspect my problem might be tied to : A stupid variable name snafu, My version of Oracle (12c) or the fact that the source is a view (replicated from another schema).

I tried the following code in many other forms (including async functions), and also tried to switch from node 10 to 11… no changes.

Any help would be welcomed.

As of now, the code “works”, in the sense that the row data from the database is printed on screen, but the response in Postman (my test tool) is empty, with no http error code… just like the connection would have been cut before replying.

Note the commented, misplaced response code, that returns a response if uncommented (and then crashes the program since the header is written twice).

Here is the code.
It is based on this project/file (and simplifed):

    var express = require('express');
    var oracledb = require('oracledb');

    var app = express();

    var connAttrs = {
        "user": "user",
        "password": "pass",
        "connectString": "some_host/SCHEMANAME"
    }

    // Http Method: GET
    // URI        : /myObj
    app.get('/myObj', function (req, res) {
        "use strict";

        oracledb.getConnection(connAttrs, function (err, connection) {
            if (err) {
                // Error connecting to DB
                res.set('Content-Type', 'application/json');
                res.status(500).send(JSON.stringify({
                    status: 500,
                    message: "Error connecting to DB",
                    detailed_message: err.message
                }));
                return;
            }

            // THIS works if uncommented. This is obviously the wrong place to put this.
            //res.contentType('application/json').status(200);
            //res.send("Test.1.2");

            connection.execute("SELECT * FROM SCHEMA.SOMEVIEW", {}, {
                outFormat: oracledb.OBJECT // Return the result as Object
            }, function (err, result) {
                if (err) {
                    res.set('Content-Type', 'application/json');
                    res.status(500).send(JSON.stringify({
                        status: 500,
                        message: "Error getting the user profile",
                        detailed_message: err.message
                    }));
                } else {
                    //log first row... THIS WORKS !
                    console.log(result.rows[0]);

                    //This does not work, like if "res" could not be touched here !!!
                    res.contentType('application/json').status(200);
                    res.send(JSON.stringify(result.rows[0]));
                }
                // Release the connection
                connection.release(
                    function (err) {
                        if (err) {
                            console.error(err.message);
                        } else {
                            console.log("GET /myObj: Connection released");
                        }
                    });
            });
        });
    });
    var server = app.listen(3000, function () {
        "use strict";
        var host = server.address().address,
            port = server.address().port;
        console.log(' Server is listening at http://%s:%s', host, port);
    });

Here is a representation of the console:

{ field1: 'some data',
  field2: 'some more data' }
GET /myObj : Connection released

And here is the behaviour in postman:
enter image description here

1 Answers
Best Answer
AvatarMannu answered 4 months ago
Your Answer

17 + 1 =

Popular Tags

WP Facebook Auto Publish Powered By : XYZScripts.com