Editor datetime get(set)Formatter issue node.js oracle

perryjm

here's the complete server side endpoint:

let db = require('../db');
let router = require('express').Router();
let moment = require('moment');
let clientFrmt = "M/D/YYYY h:mm a";
// let dbFrmt = "YYYY-MMM-DD";
let dbFrmt = "YYYY-MMM-DD HH:mm:ss.SS";


let {
    Editor,
    Field,
    Validate
} = require("datatables.net-editor-server");

router.all('/dtables/inout/:EMPNO', async function (req, res) {
    let editor = new Editor(db, 'IN_OUT', 'IDNO')
        .fields(
            new Field('IO_DATE').validator(Validate.notEmpty())
                .getFormatter((val, data)=>{return moment(val).format(clientFrmt)})
                .setFormatter((val, data)=>{return moment(val).format(dbFrmt)}),
            new Field('ORIG_IO_DATE').validator(Validate.notEmpty())
                .getFormatter((val, data)=>{return moment(val).format(clientFrmt)})
                .setFormatter((val, data)=>{return moment(val).format(dbFrmt)}),
            new Field('STATUS').validator(Validate.notEmpty()),
            new Field('CMNTS')
        )
        .where('EMPNO', req.params.EMPNO)
        .where('ORIG_IO_DATE', '>', 
            moment()
                .subtract(3, 'weeks')
                .format('DD-MMM-YY') // had to do the date this way, in order to comply with nls database/oracle standard
                .toUpperCase());

    
    await editor.process(req.body);
    res.json(editor.data());
});

module.exports = router;

If I change dbFrmt to 'YYYY-MMM-DD' it works, but sets the time to midnight (expected).
But I want to be including time information in my updates. So when I set dbFrmt to 'YYYY-MMM-DD HH:mm:ss.SS' I get the following error:

Unhandled promise error:  [object Promise]Error: update IN_OUT set IO_DATE = :1 where IDNO = :2 - ORA-01861: literal does not match format string
stack: Error: ORA-01861: literal does not match format string

Please anyone help? I know i'm most likely doing something very small and silly in error.

allan

Ah - good old Oracle and dates. I've not long since made a few changes to the Node demo package for Oracle - its not yet released, but the changes are more or less contained in the db.js file - if you change it to:

let knex = require('knex');
let dbConfig = {
    client: '',

    connection: {
        user: '',
        password: '',
        database: '',
        host: '',
        filename: '', // Only used for SQLite
        dateStrings: true
    }
};

// Need a bit of customisation for Oracle to use ISO date stamps
if ( dbConfig.client === 'oracledb') {
    dbConfig.fetchAsString = [ 'date', 'number', 'clob' ];
    dbConfig.pool = {
        afterCreate: function (conn, done) {
            conn.execute("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'", function (err) {
                if (err) {
                    done(err, conn);
                }
                else {
                    done(err, conn);
                }
            });
        }
    };
}

module.exports = knex(dbConfig);

adding the required db connection information of course, then it should work with Oracle. We've got an Oracle VM running in our unit test suite now to make sure anything "off" gets caught.

Allan

perryjm

Thanks for the timely response Allan! It still doesn't appear to be working though. I'm getting the exact same error. And a few console.log() msg's are telling me I'm at least putting together a string that is supposed to match your suggested YYYY-MM-DD HH24:MI:SS nls format. I wondered if there was a problem having a space in the format (maybe knex is thinking it's multiple parameters?). To no avail. Same error as above.

I've also tried switching the dbcolumn to a timestamp datatype, and using a different alter session statement as follows:

//db.js
// Need a bit of customisation for Oracle to use ISO date stamps
if ( db.client === 'oracledb') {
    db.fetchAsString = [ 'datetime', 'date', 'number', 'clob' ];
    db.pool = {
        afterCreate: function (conn, done) {
            // conn.execute(`ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'`, function (err) {
            conn.execute("ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'", function (err) {
                if (err) {
                                        console.log(err);
                    done(err, conn);
                }
                else {
                                        console.log(err);
                    done(err, conn);
                }
            });
        }
    };
}

and a corresponding momentjs format:

let momentDbFrmt =  "YYYY-MM-DD HH:mm:ss.SS Z";

This produces a different database error, but is still no more helpful to me:

Unhandled promise error:  [object Promise]Error: update IN_OUT set IO_DATE = :1 where IDNO = :2 - ORA-01843: not a valid month
stack: Error: ORA-01843: not a valid month

perryjm

I've attempted to dynamically check that the nls parameter is being altered in my session with this:

    db.raw(`SELECT value
                FROM   nls_session_parameters
                WHERE  parameter = ?`, ['NLS_DATE_FORMAT'])
    .then(resp => {
        console.log(resp)
    });

And it appears to not be. the results I continually get are:

[ { VALUE: 'DD-MON-RR' } ]

Which tells me that the alter session command isn't actually running successfully. But yet, i can run the same command in sqldeveloper and it outputs dates just fine.

perryjm

Nevermind. Helps to read code the way you've posted it.