Forum Stats

  • 3,741,181 Users
  • 2,248,384 Discussions
  • 7,861,663 Comments

Discussions

oracledb node driver clob output with \n in place of chr(10)

Pollocks01
Pollocks01 Member Posts: 127
edited Aug 25, 2017 8:53AM in Node.js

Any way to override apparent default behaviour where carraige return and line feed are substituted in string results with /r, /n?

Same behaviour can be seen for varchar2 and clob columns...which is not surprising since I'm saying oracledb.fetchAsString = [oracledb.CLOB];

test case:

create table otn_clob (id number generated always as identity,                       clob_column clob);insert into otn_clob (clob_column)values('this sentance has anew line in it');

const oracledb = require('oracledb');oracledb.createPool(  {poolAlias: 'defaultPoo'l,user: username,password: password,connectString: connstring  })  .then((pool) => {pool.getConnection()  .then((connection) => {oracledb.fetchAsString = [oracledb.CLOB];connection.execute('select id, clob_column from otn_clob', // sql  { }, // binds  { }) // options  .then((result) => {connection.close()  .then(() => { console.log(result.rows); });  })  .catch((queryErr) => {console.error('create pool: ' + queryErr.message);connection.close();  });  })  .catch((connectionErr) => {console.error('create pool: ' + connectionErr.message);  });  })  .catch((poolErr) => {console.error('create pool: ' + poolErr.message);  });
output:
[ [ 1, 'this sentance has a\nnew line in it' ] ]

I can understand why oracledb does this - control characters not allowed in JSON.

I have some code that takes these results and converts them to csv using json2csv. The CSV is generated with \n.

Question is: is there a way to override this default behaviour or do I need to parse the json before passing it to the csv converter? ...

Best Answer

  • danmcghan-Oracle
    danmcghan-Oracle Member Posts: 43
    edited Aug 5, 2017 6:31PM Accepted Answer

    Hi Pollocks01,

    Just so you know, the GitHub repo gets more eyes on it than this forum:

    https://github.com/oracle/node-oracledb/issues

    Thanks for the example, but it wouldn't run as it's written.  If you want the then and next handlers to wait for the completion of an async operation then you need to return the promise. Otherwise, the function returns undefined and the next handler is invoked immediately. See this for more details:

    https://jsao.io/2017/06/how-to-get-use-and-close-a-db-connection-using-promises/#promise-chaining

    I can understand why oracledb does this - control characters not allowed in JSON.

    Note that the driver returns JavaScript objects, not JSON. You can use JSON.stringify if you want to convert the results from the driver to JSON.

    What version of json2csv are you using? Have you seen this?

    https://github.com/zemirco/json2csv/issues/91

    Here's an example that does run and should show that the driver works as it should.

    Given the following table:

    create table t (  c clob)/

    This should work:

    const oracledb = require('oracledb');const config = require('./dbConfig.js');const message = 'hello\nworld';let conn;let err;console.log('Message before DB...');console.log(message); // Note this shows in the console as you'd expect: 2 linesoracledb.getConnection(config)  .then((c) => {    conn = c;    return conn.execute(      'insert into t (c) values (:message)',      {        message: message      },      {        autoCommit: true      }    );  })  .then(() => {    return conn.execute(     `select c "message",        dump(to_char(c)) "dump"      from t`,      [], // no binds      {        outFormat: oracledb.OBJECT,        fetchInfo: {          'message': {            type: oracledb.STRING          }        }      }    );  })  .then(result => {    console.log('Message after DB...');    console.log(result.rows[0].message);    if (result.rows[0].message === message) {      console.log('Value in === value out');    }    console.log('Dump value:', result.rows[0].dump); // Dump value: Typ=1 Len=11: 104,101,108,108,111,10,119,111,114,108,100                                                     //                           h   e   l   l   o   \n w   o   r   l   d                                                     //                                     10 == chr(10) == \n (in JS)    console.log('Stringified message');    console.log(JSON.stringify(result.rows[0].message)); // "hello\nworld"  })  .catch(err => {    console.log('Error in processing', err);  })  .then(() => {    if (conn) { // conn assignment worked, need to close      return conn.close();    }  })  .catch(err => {    console.log('Error during close', err);  });

    I hope that helps.

Answers

  • Pollocks01
    Pollocks01 Member Posts: 127
    edited Aug 4, 2017 12:23PM

    I now realise that the issues even bigger in that when we post data to node we need to encode it with \n and so if we don't then decode that again before dumping into the database then we end up the \n in the database.

    It seems like there ought to be some sort of an encoding component that's perhaps not got the associated decode functionality.....

    I can handle this in the stored procedure which receives the data but then we're at the mercy of the developer remembering to do this.

  • danmcghan-Oracle
    danmcghan-Oracle Member Posts: 43
    edited Aug 5, 2017 6:31PM Accepted Answer

    Hi Pollocks01,

    Just so you know, the GitHub repo gets more eyes on it than this forum:

    https://github.com/oracle/node-oracledb/issues

    Thanks for the example, but it wouldn't run as it's written.  If you want the then and next handlers to wait for the completion of an async operation then you need to return the promise. Otherwise, the function returns undefined and the next handler is invoked immediately. See this for more details:

    https://jsao.io/2017/06/how-to-get-use-and-close-a-db-connection-using-promises/#promise-chaining

    I can understand why oracledb does this - control characters not allowed in JSON.

    Note that the driver returns JavaScript objects, not JSON. You can use JSON.stringify if you want to convert the results from the driver to JSON.

    What version of json2csv are you using? Have you seen this?

    https://github.com/zemirco/json2csv/issues/91

    Here's an example that does run and should show that the driver works as it should.

    Given the following table:

    create table t (  c clob)/

    This should work:

    const oracledb = require('oracledb');const config = require('./dbConfig.js');const message = 'hello\nworld';let conn;let err;console.log('Message before DB...');console.log(message); // Note this shows in the console as you'd expect: 2 linesoracledb.getConnection(config)  .then((c) => {    conn = c;    return conn.execute(      'insert into t (c) values (:message)',      {        message: message      },      {        autoCommit: true      }    );  })  .then(() => {    return conn.execute(     `select c "message",        dump(to_char(c)) "dump"      from t`,      [], // no binds      {        outFormat: oracledb.OBJECT,        fetchInfo: {          'message': {            type: oracledb.STRING          }        }      }    );  })  .then(result => {    console.log('Message after DB...');    console.log(result.rows[0].message);    if (result.rows[0].message === message) {      console.log('Value in === value out');    }    console.log('Dump value:', result.rows[0].dump); // Dump value: Typ=1 Len=11: 104,101,108,108,111,10,119,111,114,108,100                                                     //                           h   e   l   l   o   \n w   o   r   l   d                                                     //                                     10 == chr(10) == \n (in JS)    console.log('Stringified message');    console.log(JSON.stringify(result.rows[0].message)); // "hello\nworld"  })  .catch(err => {    console.log('Error in processing', err);  })  .then(() => {    if (conn) { // conn assignment worked, need to close      return conn.close();    }  })  .catch(err => {    console.log('Error during close', err);  });

    I hope that helps.

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,636 Employee
    edited Aug 6, 2017 9:09PM
    Any way to override apparent default behaviour where carraige return and line feed are substituted in string results with /r, /n?

    To emphasize what Dan said, those meta characters are common ways of representing CR and NL in strings.  From your output, I'm not seeing any 'mapping' in the query results from node-oracledb.

    With another example:

    var oracledb = require('oracledb');var dbConfig = require('./dbconfig.js');var executeCbFunc = function(err, result){  if (err) {    console.error(err.message);  } else {    console.log('After query:');    console.log(result.rows[0]);    console.log(result.rows[0]['MESSAGE']);  }};oracledb.getConnection(  dbConfig,  function (err, connection) {    if (err) {      console.error(err.message);      return;    }    var message = 'x' + String.fromCharCode(10) + 'x';    console.log('Before query: ' + message);    var sql = "select '" + message + "' as message from dual";    connection.execute(sql, [], { outFormat: oracledb.OBJECT }, executeCbFunc);  });

    The script's output is:

    Before query: xxAfter query:{ MESSAGE: 'x\nx' }xx

    This shows the input is the same as the query string.

  • Pollocks01
    Pollocks01 Member Posts: 127
    edited Aug 25, 2017 8:53AM

    Thanks for the input and sorry for not replying before now.....

    It transpires that the only "issue" that I was having was related to json2csv. Here's the summary of my investigation:

    • response are JSON. JSON does not allow control characters so the oracledb driver automatically encodes e.g. chr(10) to \n
    • request bodies are JSON. We can’t put a chr(10) in them so it seems reasonable to use \n instead….:
      • I used a native text area control to post a multi-line string to nodeServer. Seemingly, the browser automatically formatted the string with and to my surprise when I passed the value to a procedure, it came through with chr(13)||chr(10), therefore nodeServer is automatically encoding/decoding linefeeds etc
    • This came to light because my first stab at CSV downloads resulted in the \n control character in the output file.
      • I’m trying an alternative node module for this functionality and it’s so far proving successful – no more \n’s in the output.
    • Whereas JavaScript will properly render the \n for us, HTML will not and so front-end will have to add something to replace the \n’s with <br>’s for display

    regards

Sign In or Register to comment.