NodeJS OracleDB bind parameters returning parameter name

Question

I am very new to NodeJS, but I have been working to use it to serve my Angular project. I need to access an Oracle DB and return some information using a select statement. I have one statement that works correctly using a bind parameter that is set up like this:

        var resultSet;
        connection.execute("SELECT column_name, decode(data_type, 'TIMESTAMP(3)','NUMBER'" 
                    + ",'VARCHAR2','STRING','CHAR', 'STRING','NUMBER') as \"DATA_TYPE\""
                    + "FROM someTable where table_name = :tableName",
            [table], //defined above
            {outFormat: oracledb.OBJECT},
            function (err, result) {
                if (err) {
                    console.error(err.message);
                    doRelease(connection);
                    return;
                }
                resultSet = result.rows;
                console.log("Received " + resultSet.length + " rows.");
                res.setHeader('Content-Type', 'application/json');
                var JSONresult = JSON.stringify(resultSet);
               // console.log(JSONresult);
                res.send(JSONresult);
                doRelease(connection);
            });

This returns exactly what I want it to, with the bound variable being what I wanted it to be. Below is the code that doesn't work:

        var resultSet;
        connection.execute(
            "SELECT DISTINCT :columnName from someTable",
            ['someColumn'],
            {outFormat: oracledb.OBJECT},
            function (err, result) {
                if (err) {
                    console.error(err.message);
                    doRelease(connection);
                    return;
                }
                resultSet = result.rows;
                console.log("Received " + resultSet.length + " rows.");
                res.setHeader('Content-Type', 'application/json');
                var JSONresult = JSON.stringify(resultSet);
                console.log(JSONresult);
                res.send(JSONresult);
                doRelease(connection);
            });

This returns {":COLUMNNAME": "someColumn"}. I do not understand why it won't display the results correctly. The two snippets of code are exactly the same, save the SQL query part. I know this a long question, but I really need help. Thank you!

this really shouldn't matter, but for fun, you could replace ['someColumn'] with {columnName: 'someColumn'} and see if that works? also, the 'LOB_ID' thing seems suspicious -- what datatype is the column? (LOB support is not finished yet). — user2524973
– user2524973, Commented Jul 21, 2015 at 17:26
LOB_ID is the name of the column actually. It stands for line of business. The data type is just a number. I edited it so that the result and my code match now. I had removed the column names but forgot in one place! Sorry for the confusion. Also,I took your advice and replaced the array with the object, but I am getting the same result :( — Erika
– Erika, Commented Jul 21, 2015 at 18:00
hmm. does it work without the DISTINCT? also, prob won't make a difference but you could add additional specifiers in, eg: { columnName: 'someColumn', dir: oracledb.BIND_IN, type: oracledb.NUMBER }. beyond that, i'm stumped! — user2524973
– user2524973, Commented Jul 22, 2015 at 13:36
It does not work without DISTINCT. it also does not work with specifiers. Thank you for your help though. I'm as stumped as you are! — Erika
– Erika, Commented Jul 22, 2015 at 20:24

Christopher Jones · Accepted Answer · 2015-07-23 12:22:08Z

2

You can bind data values, not the text of the statement itself.

answered Jul 23, 2015 at 12:22

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

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

2 Comments

Erika Over a year ago

So there is no way to select all the distinct values of a certain column name from the database - without knowing the column name? Is it possible to pass the variable and then create the sql statement and then run the query with the built sql statement that way?

Christopher Jones Over a year ago

If the column or table names are not known prior to runtime, you can (carefully) use string concatenation to construct the query text. You need to filter any user-entered values because you can never trust user input not to be malicious.

Collectives™ on Stack Overflow

NodeJS OracleDB bind parameters returning parameter name

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related