Node-oracledb: not able to convert sys_refcursor bind_out parameter to json for REST api

[rahthakor]

I'm new to node js and I came across oracledb.

var bindvars = {
p_data: { type: oracledb.CURSOR,
dir: oracledb.BIND_OUT
} // Bind type is determined from the data. Default direction is BIND_IN
};
connection.execute("BEGIN pkg_com_utility.prc_fill_proc(:p_data); END;",
bindvars,
{ outFormat: oracledb.OBJECT },
function (err, result){
if (err) { console.error(err.message); return; }
var rawJSON;
fetchRowsFromRS(connection, result.outBinds.p_data, numRows);//as metioned in Documentation
res.contentType('application/json').status(200);
//res.send(JSON.stringify(result.outBinds.p_data));
res.send(result.outBinds.p_data);//Working but only gives metadata
});

I don't have any clue how to get all rows in json just like normal queries

res.send(JSON.stringify(result.rows));// Works fine with inline query

fetchRowsFromRS method prints console output as expeted.
Thanks in advance

[sagiegurari]

see https://github.com/sagiegurari/simple-oracledb#usage-query
that allows you to query and get all data as a json object.

[dmcghan]

@rahthakor Have a look here:
https://github.com/oracle/node-oracledb/blob/master/doc/api.md#refcursors

As you can see, when your out parameter is a cursor, you actually get a ResultSet object. ResultSets must be processed a little differently than the results from running execute (you call getRow or getRows). That link has an example of how it works.

[dmcghan]

Sorry, I just saw that you do have fetchRowsFromRS implemented.

You just need to call res.send from within fetchRowsFromRS (make sure to pass res in as a parameter). Right after you call getRows(s), you should have the row(s) that you can stringify and send.

If you'd like more help, please show more of the code, specifically fetchRowsFromRS.

[rahthakor]

@dmcghan My fetchRowsFromRs method has following a code.

function fetchRowsFromRS(connection, curOut, numRows)
{
curOut.getRows( // get numRows rows
numRows,
function (err, rows)
{
if (err) {
console.error(err.message);
return; // close the result set and release the connection
} else if (rows.length == 0) { // no rows, or no more rows
console.log('No more rows'); // close the result set and release the connection
} else if (rows.length > 0) {
console.log(rows);
fetchRowsFromRS(connection, curOut, numRows); // get next set of rows
}
console.log('Total Rows %s', rowCount);
});
}

Currently, I haven't implemented res.send in this method as you have suggested.
I don't know how to implement what you are suggesting so if you can just show me snippet it would be very helpful.

Thanks for suggesting I'm trying it meanwhile.

[rahthakor]

@sagiegurari I'm trying your wrapper for solving my problem.

[dmcghan]

@rahthakor I've not tested this, but here's a mockup with your function:

function fetchRowsFromRS(connection, curOut, numRows, res)
{
  curOut.getRows(// get numRows rows
    numRows,
    function (err, rows)
    {
      if (err) {
        console.error(err.message);
        return; // close the result set and release the connection
      } else if (rows.length == 0) { // no rows, or no more rows
        console.log('No more rows'); // close the result set and release the connection
      } else if (rows.length > 0) {
        console.log(rows);

        rows.forEach(function (row) {
          res.send(JSON.stringify(row));
        });

        fetchRowsFromRS(connection, curOut, numRows); // get next set of rows
      }
      console.log('Total Rows %s', rowCount);
    });
}

Remember to pass the response (res) to fetchRowsFromRS as the last (new) parameter.

[rahthakor]

@dmcghan res.send won't work in recursive call cause it will give error like
_Error: Can't set headers after they are sent._
because function(err,rows) is call back function
so can't send a response until a call is finished.

[dmcghan]

I'm not sure I follow your logic. That error is pointing you in another direction.

Please provide a reproducible test case that demonstrates the problem. Without that I can't really help you.

[rahthakor]

@dmcghan Finally able to do it with your logic.

I changed my code as below.

connection.execute("BEGIN pkg_com_utility.prc_fill_proc(:p_data); END;",
bindvars,
{ outFormat: oracledb.OBJECT },
function (err, result){
if (err) {
console.error(err.message);
doRelease(connection);
}
res.contentType('application/json').status(200);
fetchRowsFromRS(connection, result.outBinds.p_data, numRows,res);
//res.send(result.outBinds.p_data);//Working
});

function fetchRowsFromRS(connection, resultSet, numRows,res)
{
  resultSet.getRows( // get numRows rows
    numRows,
    function (err, rows)
    {
      if (err) {
        console.error(err.message);
        doClose(connection, resultSet);
      } else if (rows.length == 0) {
        console.log('No rows or No more rows');
        doClose(connection, resultSet);
      } else if (rows.length > 0) {
        console.log(rows);
        res.send(JSON.stringify(rows));
        fetchRowsFromRS(connection, resultSet, numRows,res);
      }
    });
  }

This code is providing an output as I expected. But just one issue with this code is I'm not able to release a connection.

And How do I can send multiple out ref cursor to response body.??

[sagiegurari]

Use the new queryStream function and pipe it to the response

[rahthakor]

@sagiegurari thank you for a suggestion. That might help actually.

[sagiegurari]

Don't forget to transform each row to json string or something. So it's 2 pipes. Query -> transform -> socket

[rahthakor]

@sagiegurari sorry but as I'm a newbie to NodeJS as well as Javascript so I really couldn't understand what you said.

[cjbj]

Closing. If you have more detailed questions, open new issues.