如何从node-oracledb toQueryStream()中检索值作为Promise

Question

在尝试使用toQueryStream()从resultSet获取行时，我只能在console中找到这些行。但不能使用promises返回值，因为toQueryStream()函数使用eventListener解析行。我的代码如下所示，请建议获取行值。

function getPosCounter() {
    return oracledb.getConnection(kcdConnStr)
        .then(function (conn) {
            return conn.execute(`BEGIN GETPOSCOUNTER(:CV_1); END;`, { // EXECUTE ORACLE PROCEDURE
                CV_1: { dir: oracledb.BIND_OUT, type: oracledb.CURSOR } //CURSOR DEFINED FOR OUT PARAM
            })
                .then((result) => {
                    var resRows = new Array();
                    var resultSet = result.outBinds.CV_1; //RESULT SET FOR OUTPUT
                    var queryStream = resultSet.toQueryStream(); //QUERYSTREAM INITIALIZED FOR CURSOR VALUES
                    var consumeStream = new Promise((resolve, reject) => {
                        queryStream.on('data', function (row) {
                            console.log(row);
                        });
                        queryStream.on('error', reject);
                        queryStream.on('close', resolve);
                    })
                        .then(rows => {
                            console.dir(rows); //RETURN ROW VALUES 
                        });

                })
                .catch((err) => {
                    conn.close();
                    console.error(err);
                    return 'failure';
                })
        });
}

Answer 1

在我面对这个问题之前，我不知道nodeJs-Stream的特性，这对我来说很难理解。所以我在Oracle社区上找到了一篇关于从结果集中获取行的文档。用下面的方法来实现它。

function getPosCounter() {
    return oracledb.getConnection(kcdConnStr)
        .then(function (conn) {
            return conn.execute(`BEGIN USP_POSCOUNTER(:CV_1); END;`, { // EXECUTE ORACLE PROCEDURE
                CV_1: { dir: oracledb.BIND_OUT, type: oracledb.CURSOR } //CURSOR DEFINED FOR OUT PARAM
            })
                .then((result) => {
                    var resRows = [];
                    var resultSet = result.outBinds.CV_1; //RESULT SET FOR OUTPUT
                    var queryStream = resultSet.toQueryStream(); //QUERYSTREAM INITIALIZED FOR CURSOR VALUES
                    return consumeStream = new Promise((resolve, reject) => {
                        queryStream.on('data', (row) => {
                            resRows.push(row); //STORE ROWS IN TO BLANK ARRAY 
                        });
                        queryStream.on('error', reject);
                        queryStream.on('close', () => {
                            resolve(resRows); //RETURN ON RESOLVING ALL THE ROWS
                            conn.close();
                        });
                    });
                })
                .catch((err) => {
                    conn.close();
                    //console.error(err);
                    return 'failure';
                })
        });
}

Answer 2

您可以像这样编写代码，而不是摆弄promises和streams：

const oracledb = require('oracledb');
const dbConfig = require('./dbconfig.js');

if (process.platform === 'darwin') {
  oracledb.initOracleClient({libDir: process.env.HOME + '/Downloads/instantclient_19_8'});
}

async function run() {
  let connection;

  try {

    connection = await oracledb.getConnection(dbConfig);

    await connection.execute(
      `create or replace procedure usp_poscounter(p out sys_refcursor) as
       begin
         open p for select postal_code from locations order by location_id;
       end;`);

    const r = await getPosCounter();
    console.dir(r, { depth: null });

  } catch (err) {
    console.error(err);
  } finally {
    if (connection) {
      try {
        await connection.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

async function getPosCounter() {

  let conn;

  try {
    conn = await oracledb.getConnection(dbConfig); // should really use a pool instead

    const result = await conn.execute(
      `BEGIN USP_POSCOUNTER(:CV_1); END;`,
      { CV_1: { dir: oracledb.BIND_OUT, type: oracledb.CURSOR } }
    );

    let row, outRows = [];
    while ((row = await result.outBinds.CV_1.getRow())) {
      outRows.push(row);
    }

    // With node-oracledb 5.2 you will be able to simplify this loop to be the
    // following one line.  Note with any node-oracledb version, if the number
    // of rows is large, then you will want your architecture to deal with
    // batches of rows insteading of returning one big array.
    //
    // const outRows = await result.outBinds.CV_1.getRows();  // empty arg gets all rows

    return (outRows);

  } catch (err) {
    console.error(err);
  } finally {
    if (conn) {
      try {
        await conn.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

run();