Btukfyl

I am new in Node.JS ecosystem. Don't throw tomatoes at me please. I need some advice.

I am trying connect Node.JS project with remote Oracle 12c database. I use oracledb driver for this task.

From error I understand that problem with Promise and parameters like start_date and end_date from url not inserted in SQL statement which I use in controller. How to fix this problems?

ERROR:

(node:39204) UnhandledPromiseRejectionWarning: Error: ORA-01036: illegal variable name/number

(node:39204) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 1)

(node:39204) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

URL format: GET /period/?start_date=2018-10-01&end_date=2018-10-31

routes/articles.js:

const express = require('express');

const router = express.Router();

const articleControllers = require('../controllers/articles');



router.get('/period', articleControllers.get_period_articles);



module.exports = router;

controllers/articles.js:

const oracleDatabase = require('modules/oracle_database');



async function get_period_articles(req, res, next) {

    try {

        let start_date = req.query.start_date;

        let end_date = req.query.end_date;



        const binds = {};

        binds.start_date = start_date;

        binds.end_date = end_date;



        let query = `

            SELECT * FROM ArticleTable

            WHERE

                CREATE_DATE BETWEEN TO_DATE(':start_date', 'YYYY-MM-DD') AND TO_DATE(':end_date', 'YYYY-MM-DD');

        `;



        const result = oracleDatabase.executeSQLStatement(query, binds);



        console.log(result); // <- undefined

    } catch (error) {

        next(error);

    }

}



module.exports.get_period_incidents = get_period_incidents;

modules/oracle_database.js:

const oracledb = require('oracledb');

const oracleDatabaseConfiguration = require('../config/oracle_database');



async function initialization() {

    await oracledb.createPool(oracleDatabaseConfiguration);

}



module.exports.initialization = initialization;



function executeSQLStatement(query, binds = , options = {}) {

    return new Promise(async (resolve, reject) => {

        let connection;



        options.outFormat = oracledb.OBJECT;

        options.autoCommit = true;



        try {

            connection = await oracledb.getConnection();

            const result = await connection.execute(query, binds, options);

            resolve(result);

        } catch (error) {

            reject(error);

        } finally {

            if (connection) {

                try {

                    await connection.close();

                } catch (error) {

                    console.log(error);

                }

            }

        }

    });

}



module.exports.executeSQLStatement = executeSQLStatement;

Finally I found the problem.

In controllers/articles.js file change query to:

let query = `

SELECT * FROM ArticleTable

WHERE CREATE_DATE BETWEEN TO_DATE(:start_date, 'YYYY-MM-DD') AND TO_DATE(:end_date, 'YYYY-MM-DD');

`;

As you can see I just remove apostrophe '.

Also in the same file I change to:

const result = await oracleDatabase.executeSQLStatement(query, binds);