javascript - 如何从Node.JS正确执行到Oracle数据库的sql语句？

Question

我是 Node.JS 生态系统的新手。请不要向我扔西红柿。我需要一些建议。

我正在尝试将 Node.JS 项目与远程 Oracle 12c 数据库连接。我用oracledb此任务的驱动程序。

<小时/>

从错误中，我了解到 Promise 以及来自 url 的 start_date 和 end_date 等参数的问题未插入到我在 Controller 中使用的 SQL 语句中。如何解决这个问题？

错误:

(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.

<小时/>

网址格式: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;

模块/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;

Answer 1

终于找到问题了

在 controllers/articles.js 文件中将查询更改为:

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 = await oracleDatabase.executeSQLStatement(query, binds);