CODEWITHSUNDEEP
node.jsoracle-databasenode-oracledb
Rituparno Bhattacharya
Rituparno Bhattacharya

Reputation: 363

Unable to insert using executeMany() into Oracle DB with Node.js

I am trying to insert data into Oracle database via Node JS. I am receiving data from Rest API. I am getting below error while running the code - 

Error: NJS-005: invalid value for parameter 2

Here is my JSON Data getting from API - 

[{ "id": 6, "type": "LOOKUP_ID", "value": "A", "description": "Access Group A", "instime": "2016-06-30", "updtime": null, "deltime": null, "insuser": "ADMIN", "upduser": null, "deluser": null },
 { "id": 5, "type": "LOOKUP_ID", "value": "B", "description": "Access group for B", "instime": "2016-03-07", "updtime": null, "deltime": null, "insuser": "ADMIN", "upduser": null, "deluser": null }, 
 { "id": 7, "type": "LOOKUP_ID", "value": "C", "description": "Access Group for C", "instime": "2017-07-11", "updtime": null, "deltime": null, "insuser": "ADMIN", "upduser": null, "deluser": null }, 
 { "id": 10, "type": "LOOKUP_ID", "value": "M", "description": "Access Group for M", "instime": "2018-02-28", "updtime": null, "deltime": null, "insuser": "ADMIN", "upduser": null, "deluser": null }];

Here is my Node JS code - 

'use strict';

const oracledb = require('oracledb');
const express = require('express');
var request = require("request")
const app = express();


app.get('/', (req, res) => {
    oracledb.getConnection(
        {
            user: 'uid',
            password: 'passwd',
            connectString:
                '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost)(PORT=1521)(SEND_BUF_SIZE=)(RECV_BUF_SIZE=))(LOAD_BALANCE=yes))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=dbhost)))',
        },
        connExecute
    );

     function connExecute(err, connection) {
        if (err) {
            console.error(err.message);
            res.send(err.message);
            return;
        }
        var url = "http://localhost:8080/api/employees"

        request({
            url: url,
            json: true
        }, async function (error, response, body) {
            try {
                if (!error && response.statusCode === 200) {

                    const data = JSON.stringify(body);
                    const sql = `INSERT INTO TABLE 
                    (ID, TYPE, VALUE, DESCR, INS, UPD, DELE, USER_INS, USER_UPD, USER_DELE) 
                    VALUES 
                (:id, :type, :value, :description, :instime, :updtime, 
                    :deltime, :insuser, :upduser, :deluser )`;

                    const binds = data;

                    const options = {
                        autoCommit: true,
                        bindDefs: {
                            id: { type: oracledb.NUMBER },
                            type: { type: oracledb.STRING, maxSize: 50 },
                            value: { type: oracledb.STRING, maxSize: 100 },
                            description: { type: oracledb.STRING, maxSize: 200 },
                            instime: { type: oracledb.DATE },
                            updtime: { type: oracledb.DATE },
                            deltime: { type: oracledb.DATE },
                            insuser: { type: oracledb.STRING, maxSize: 255 },
                            upduser: { type: oracledb.STRING, maxSize: 255 },
                            deluser: { type: oracledb.STRING, maxSize: 255 }
                        }
                    };

                    const result = await connection.executeMany(sql, binds, options);

                    console.log(result.rowsAffected);
                }
                } catch (error) {
                    console.log(error.message);
                }
        })
    }

    function connRelease(connection) {
        connection.close(function (err) {
            if (err) {
                console.error(err.message);
            }
        });
    }
});

const PORT = process.env.PORT || 8000;
app.listen(PORT, () => {
    console.log(`App listening on port ${PORT}`);
    console.log('Press Ctrl+C to quit.');
});

module.exports = app;

Here is my Table Structure -

Table Structure

I am not able to figure out what wrong I am doing here. Need some help to fix the issue.

Upvotes: 0

Views: 6654

Answers (2)

Christopher Jones
Christopher Jones

Reputation: 10506

In addition to the incorrect stringify that was already pointed out, you are binding strings like "2016-06-30" as the oracledb.DATE type so you will get an error.

There are various ways around this, but if your date is coming from an external source it's probably easier to bind as oracledb.STRING and make sure the DB knows what date format to expect. Here's one working example:

'use strict';

process.env.NLS_LANG='.AL32UTF8';
process.env.NLS_DATE_FORMAT='YYYY-MM-DD';
process.env.ORA_SDTZ = 'UTC';

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

async function run() {
  let connection;

  try {
    connection = await oracledb.getConnection(config);

    const sql = `INSERT INTO MTABLE 
                 (ID, TYPE, VALUE, DESCR, INS, UPD, DELE, USER_INS, USER_UPD, USER_DELE) 
                 VALUES 
                 (:id, :type, :value, :description, :instime, :updtime, :deltime, :insuser, :upduser, :deluser )`;

    const data = [
      { "id": 6, "type": "LOOKUP_ID", "value": "A", "description": "Access Group A", "instime": "2016-06-30", "updtime": null, "deltime": null, "insuser": "ADMIN", "upduser": null, "deluser": null },
      { "id": 5, "type": "LOOKUP_ID", "value": "B", "description": "Access group for B", "instime": "2016-03-07", "updtime": null, "deltime": null, "insuser": "ADMIN", "upduser": null, "deluser": null }, 
      { "id": 7, "type": "LOOKUP_ID", "value": "C", "description": "Access Group for C", "instime": "2017-07-11", "updtime": null, "deltime": null, "insuser": "ADMIN", "upduser": null, "deluser": null }, 
      { "id": 10, "type": "LOOKUP_ID", "value": "M", "description": "Access Group for M", "instime": "2018-02-28", "updtime": null, "deltime": null, "insuser": "ADMIN", "upduser": null, "deluser": null }
    ];

    const options = {
      autoCommit: true,
      bindDefs: {
        id: { type: oracledb.NUMBER },
        type: { type: oracledb.STRING, maxSize: 50 },
        value: { type: oracledb.STRING, maxSize: 100 },
        description: { type: oracledb.STRING, maxSize: 200 },
        instime: { type: oracledb.STRING, maxSize: 10 },
        updtime: { type: oracledb.STRING, maxSize: 10 },
        deltime: { type: oracledb.STRING, maxSize: 10 },
        insuser: { type: oracledb.STRING, maxSize: 255 },
        upduser: { type: oracledb.STRING, maxSize: 255 },
        deluser: { type: oracledb.STRING, maxSize: 255 }
      }
    };

    let result = await connection.executeMany(sql, data, options);
    console.log(result);
  } catch (err) {
    console.error(err);
  } finally {
    if (connection) {
      try {
    await connection.close();
      } catch (err) {
    console.error(err);
      }
    }
  }
}

run();

Upvotes: 0

Dan McGhan
Dan McGhan

Reputation: 4659

You're getting that error because you're stringifying the binds prior to passing them to executeMany. executeMany is expecting a JS array, not a string, as the second parameter.

A couple of other things... You are creating a database connection each time a request comes in. This will not scale. You need to create a connection pool and then get a connection from the pool.

I'm seeing nested callbacks and async/await combined. I recommend always using async/await unless you are forced to use callbacks (the API doesn't support promises). This will greatly simplify the code.

See this series on creating a REST API with Node.js and Oracle Database for more info: https://jsao.io/2018/03/creating-a-rest-api-with-node-js-and-oracle-database/

Upvotes: 1

Related Questions