Sunday, 19 March 2017

Bind array in node oracledb

I have written a service which successfully posts the json. I am using node oracldb to insert the data into my table using stored proc. The code I have written is like this -

app.use(bodyParser.urlencoded({
    extended: true
}));
app.use(bodyParser.json());

app.post('/', function(req, res){
    res.setHeader('Content-Type', 'application/json');
    if(req.body != 'undefined'){
        //console.log(req.body[0].p_criteria_column); 
    var p_loginR = req.body[0].p_login;
    var p_alert_nameR = req.body[0].p_alert_name;
    var p_criteria_columnR = req.body[0].p_criteria_column;
    var p_criteria_valR = req.body[0].p_criteria_val;
    var p_criteria_oprR = req.body[0].p_criteria_opr;
    var p_opr_flagR = req.body[0].p_opr_flag;
    var p_recipent_listR = req.body[0].p_recipent_list;
    var p_valid_dateR = req.body[0].p_valid_date;
    var p_active_flagR = req.body[0].p_active_flag;
    var p_alert_idR = req.body[0].p_alert_id    ;
    var p_alert_priorityR = req.body[0].p_alert_priority;
    var oracledb = require('oracledb');
    var dbConfig = require('./config.js');
    console.log(p_criteria_columnR);
    console.log(p_criteria_valR);
    console.log(p_criteria_oprR);
    var numRows = 10;  // number of rows to return from each call to getRows()
    //oracledb.outFormat = oracledb.OBJECT;
    oracledb.getConnection(
      {
        user          : dbConfig.user,
        password      : dbConfig.password,
        connectString : dbConfig.connectString
      },
      function(err, connection)
      {
        if (err) { console.error(err.message); return; }
        var bindvars = {
          p_login:  { type: oracledb.STRING, dir : oracledb.BIND_IN, val:p_loginR  },
          p_alert_name:  { type: oracledb.STRING, dir : oracledb.BIND_IN, val:p_alert_nameR },
          p_criteria_column:  { type: oracledb.STRING, dir : oracledb.BIND_IN, val:req.body[0].p_criteria_column },
          p_criteria_val:  { type: oracledb.STRING, dir : oracledb.BIND_IN, val: req.body[0].p_criteria_val},
          p_criteria_opr:  { type: oracledb.STRING, dir : oracledb.BIND_IN, val: req.body[0].p_criteria_opr },
          p_opr_flag:  { type: oracledb.STRING, dir : oracledb.BIND_IN, val: p_opr_flagR },
          p_recipent_list:  { type: oracledb.STRING, dir : oracledb.BIND_IN, val:p_recipent_listR  },
          p_valid_date:  { type: oracledb.STRING, dir : oracledb.BIND_IN ,val:p_valid_dateR},
          p_active_flag:  { type: oracledb.STRING, dir : oracledb.BIND_IN, val:p_active_flagR  },
          p_alert_id:  { type: oracledb.NUMBER, dir : oracledb.BIND_INOUT,val: p_alert_idR},
          p_alert_priority:  { type: oracledb.NUMBER, dir : oracledb.BIND_IN, val:p_alert_priorityR },
          error_code : { type: oracledb.STRING, dir: oracledb.BIND_OUT },
          error_msg : { type: oracledb.STRING, dir: oracledb.BIND_OUT }
        };
        connection.execute(
          "BEGIN prc(:p_login, :p_alert_name, :p_criteria_column, :p_criteria_val, :p_criteria_opr, :p_opr_flag, :p_recipent_list, :p_valid_date, :p_active_flag, :p_alert_id, :p_alert_priority); END;",
          bindvars,
          function(err, result)
          {
            if (err) {
              console.error(err.message);
              doRelease(connection);
              return;
            }
            //console.log(result.outBinds.cursor.metaData);
            fetchRowsFromRS(connection, result.outBinds.cursor, numRows);
          });
      });

    function fetchRowsFromRS(connection, resultSet, numRows)
    {
      resultSet.getRows( // get numRows rows
        numRows,
        function (err, rows)
        {
          if (err) {
            console.log(err);
            doClose(connection, resultSet); // always close the result set
          } else if (rows.length === 0) {    // no rows, or no more rows
            doClose(connection, resultSet); // always close the result set
          } else if (rows.length > 0) {
            //console.log("fetchRowsFromRS(): Got " + rows.length + " rows");
            console.log(rows);
            fetchRowsFromRS(connection, resultSet, numRows);
          }
        });
    }

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

    function doClose(connection, resultSet)
    {
      resultSet.close(
        function(err)
        {
          if (err) { console.error(err.message); }
          doRelease(connection);
        });
    } 
        }
});

When the proc is getting executed it shows the following error -

NJS-011: encountered bind value and type mismatch in parameter 2

Parameter 2,3,4 are all arrays of strings and they are properly being received from the client side. I have console.logged each to verify. I referred to this for binding instructions but still the error shows. Can someone help me know why is this error coming?



via Dalton2

No comments:

Post a Comment