Node-oracledb: Problem when i using connection class.

[maalfarom]

node version: v15.2.0
oracledb version: 5.0.0
oracle: 18c

Hello, I have a problem when I try using a connection class.

When I called my result set this is undefined but when I not using connection class this is right.

My output:

GET DEPARTAMENTOS
C:\Users\Matias\Documents\Duoc UC\portafolio\backend\server\server.js:69
    while ((row = await resultSet.getRow())) {
                                  ^

TypeError: Cannot read property 'getRow' of undefined

my connection class:

const oracledb = require('oracledb');

connection = {
    user: 'matias',
    password: '123',
    connectString: "localhost:1521/XE"
};

async function Open(sql, binds, autoCommit) {
    let conex;
    try {
        conex = await oracledb.getConnection(connection);
        result = await conex.execute(sql, binds, { autoCommit });
    } catch (err) {
        console.error(err);
    } finally {
        if (conex) {
            try {
                await conex.close();
            } catch (err) {
                console.error(err);
            }
        }
    }
    return result;
}

exports.Open = Open;

my code:

app.get('/departamentos', async(req, res) => {
    console.log('GET DEPARTAMENTOS');

    const sql = `BEGIN LEER_DEPARTAMENTOS( :CURSOR_ ); END;`;

    const binds = { CURSOR_: { type: oracledb.CURSOR, dir: oracledb.BIND_INOUT } };

    const result = await con.Open(sql, binds, false);

    const resultSet = result.outBinds.binds;

    let row;

    Departamentos = [];

    while ((row = await resultSet.getRow())) {
        let dptoSchema = {
            'id_departamento': row[0],
            'nombre_departamento': row[1],
            'numero_departamento': row[2],
            'inventario_departamento': row[3],
            'tarifa': row[4],
            'nombre_comuna': row[5],
            'disponibilidad': row[6],
            'direccion': row[7]
        };
        Departamentos.push(dptoSchema);
    }

    res.json(Departamentos);
});

this code works fine:

app.get('/departamentooos', async(req, res) => {
    console.log('GET DEPARTAMENTOS');

    let connection;

    try {
        connection = await oracledb.getConnection({
            user: 'matias',
            password: '123',
            connectString: "localhost:1521/XE"
        });

        const result = await connection.execute(
            `BEGIN LEER_DEPARTAMENTOS( :CURSOR_ ); END;`, {
                CURSOR_: { type: oracledb.CURSOR, dir: oracledb.BIND_INOUT }
            }, {}
        );

        console.log(result);

        const resultSet = result.outBinds.CURSOR_;

        let row;

        Departamentos = [];

        while ((row = await resultSet.getRow())) {
            let dptoSchema = {
                'id_departamento': row[0],
                'nombre_departamento': row[1],
                'numero_departamento': row[2],
                'inventario_departamento': row[3],
                'tarifa': row[4],
                'nombre_comuna': row[5],
                'disponibilidad': row[6],
                'direccion': row[7]
            };
            Departamentos.push(dptoSchema);
        }
        res.json(Departamentos);
    } catch (err) {
        console.error(err);
    } finally {
        if (connection) {
            try {
                await connection.close();
            } catch (err) {
                console.error(err);
            }
        }
    }

});

[cjbj]

In your failing code, the line const resultSet = result.outBinds.binds is wrong. In the working code you seem to have it correct: const resultSet = result.outBinds.CURSOR_;

[maalfarom]

I have the same error, I had already tried :(

[cjbj]

Since you're returning a result set, you need to keep the connection open until you have finished getting rows from the DB. Currently you are closing it after the execute.

Can you update your question with a standalone script that shows the problem, i.e. not using a web server? That would make it easier for us to run and talk about.

Since you're getting all rows, do you need to use a resultSet and loop over the data? You could use outFormat: oracledb.OUT_FORMAT_OBJECT and then either use the returned column names in the rest of the app, or change the query to something like select nastyname as "id_departamento", ...

[maalfarom]

Now i using a new function to close conex.
thank you for you help 😄 .