maxRows setting not being honored - defaults to 100 rows

[cheslyn]

Database ver : 12.1.0.2.0 node ver : v10.15.1

Problem The output always defaults to 100 rows and the formed output is truncated to something like this

[ 391765774,
    '391507868',
    'PRC: Interface Invoices to Receivables',
    32732,
    275,
    '999999999',
    'Standby',
    'Pending',
    '-',
    0],
  ... 611 more items ]

I have gone through this thread , but looks like it was not resolved https://github.com/strongloop/loopback-connector-oracle/issues/101

Note : I have the following 2 settings in my .js file

var oracledb = require('oracledb');
oracledb.maxRows = 1000;
...

      { maxRows: 1000

      },

Sample of the file

var oracledb = require('oracledb');
oracledb.maxRows = 1000;
var dbConfig = require('/orpppmd1/ppmapp/ohs11119/websrvr/instances/instance1/config/OHS/ohs1/htdocs/custrep/release/nodehtml/dbconfig.js');
var args0 = process.argv[1];
var args1 = process.argv[2];
var args2 = process.argv[3];
var args3 = process.argv[4];
var args4 = process.argv[5];

oracledb.getConnection(
  {
    user          : dbConfig.user,
    password      : dbConfig.password,
    connectString : dbConfig.connectString
  },
  function(err, connection) {
    if (err) {
      console.error(err.message);
      return;
    }
    connection.execute(
      // The statement to execute
      `  
         select 
         request_id,
         NVL(user_name,'-') c1,
         NVL(start_date,'-')  c2,
         NVL(compl_date,'-')  c3,
         NVL(round(running_for_min,2),0)  c4,
         NVL(phase_code_meaning,'-') c5,
         NVL(status_meaning,'-') c6,
         NVL(logfile_name,'-')  c7,
         NVL(outfile_name,'-') c8,
         NVL(responsibility_name,'-')  c9,
         NVL(argument_text,'-') c10
        from 
        ( select * from CONC_REQ_Q_V r  
           where 
            r.phase_code = 'C'
            and r.program_id = :args1 
            and r.application_id = :args2
            order by r.start_date desc 
        ) sql_1
        where rownum <= :args3   `,

       [args1, args2, args3],

      { maxRows: 1000

      },

      function(err, result) {
        if (err) {
          console.error(err.message);
          doRelease(connection);
          return;
        }
        //console.log(result.metaData); 
        console.log(result.rows);     
        doRelease(connection);
      });
  });

// Note: connections should always be released when not needed
function doRelease(connection) {
  connection.close(
    function(err) {
      if (err) {
        console.error(err.message);
      }
    });
}

[cjbj]

It works for me with node-oracledb 4.0.1:

var oracledb = require('oracledb');

oracledb.maxRows = 1000;
var dbConfig = require('./dbconfig.js');

oracledb.getConnection(
  {
    user          : dbConfig.user,
    password      : dbConfig.password,
    connectString : dbConfig.connectString
  },
  function(err, connection) {
    if (err) {
      console.error(err.message);
      return;
    }
    connection.execute(
      `select 1 from all_objects where rownum <= 2000 `,
       [],
      { maxRows: 1000 },

      function(err, result) {
        if (err) {
          console.error(err.message);
          doRelease(connection);
          return;
        }
        //console.log(result.metaData);
        console.log("Number of rows ", result.rows.length);
        doRelease(connection);
      });
  });

// Note: connections should always be released when not needed
function doRelease(connection) {
  connection.close(
    function(err) {
      if (err) {
        console.error(err.message);
      }
    });
}

Gives:

Number of rows  1000

Check your WHERE clause and argument usage.

If your problem is really with strongloop, then check with them.

Other comments: you may find the async/await style of programming easier to use. And with recent DBs, you can use the improved OFFSET / FETCH NEXT SQL syntax to limit rows.

[cheslyn]

@cjbj Thanks for responding to this thread , appreciate the same.

But the problem is your test case returns a rowcount of only 1 row in the resultset which will work fine. I ran your script on my env and do get the same output "Number of rows 1000"

Here is what you need to do, tweak 2 lines

1. Change the SQL to return 1000 rows

     `select rownum,object_name from all_objects where rownum <= 1000 `,

2. Change the return clause to return the data of 1000 rows i.e. console.log(result.rows);

       console.log(result.rows); 

Run the script now and maybe redirect the output to a .txt file you will see that it just returns the first 100 rows and the output file ends with .. 900 more items Which breaks the rest of my post processing code.

 [ 99, 'SQL_VERSION$' ],
  [ 100, 'I_SQL_VERSION$_VERSION#' ],
  ... 900 more items ]

So it is not an issue with the Where clause or argument usage, the return dataset always truncates to 100 rows.

[cjbj]

That's just how console.log() works. Compare it with:

    for (const row of result.rows)
      console.log(row);

Aside from the output display choices you have, as a suggestion if you're dealing with a lot of rows, then you may want to use a ResultSet.

[cheslyn]

Awesome , I am able to resolve the main problem. However could you please point me to documentation on how to format the result set ? or possibly give a solution to it if that is easier .

Code

    const rs = result.resultSet;
    let row;
    let i = 1;

    while ((row = await rs.getRow())) {
      console.log(row );
    }

Returns data in this format

[ 1, 'CON$MIG' ]
[ 2, 'UNDO$' ]
[ 3, 'C_COBJ#' ]
[ 4, 'PROXY_ROLE_DATA$' ]
[ 5, 'I_CDEF2' ]

What would it take to add a "," at the end of each array/row and encapsulate it in 2 main Sq brackets ? i.e. as returned by the non result-set data format. eg

[
[ 1, 'CON$MIG' ],
[ 2, 'UNDO$' ],
[ 3, 'C_COBJ#' ],
[ 4, 'PROXY_ROLE_DATA$' ],
[ 5, 'I_CDEF2' ]
]

[cjbj]

Try commands like console.log('[');