NodeRed Function Node Crashes - [red] Uncaught Exception:

Question

I am having a hard time understanding why my Node-Red Flow keeps crashing with an [RED] Uncaught Exception. It almost seems like the function node is crashing and my code inside of the function node never has a chance to catch it.

I have a very simple Node-Red flow that executes a IBMDB node.js library to insert data into my database. In order to use the IBMDB library, I had to add the package to the list of packages in my packages.json file. I also had to setup a global context variable inside the Bluemix-setings.js file. I named this global context variable IBMDB, which is the equivalent to the require statement. Once that was done, I am able to make use of the library inside a function node.

** Here is the Node.js IBMDB library, I am using. https://github.com/ibmdb/node-ibm_db

** Here is the Flow

** Here is the code inside the function.

try {
context.global.ibmdb.open("DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;", function (err,conn) {

  conn.beginTransaction(function (err) {
    if (err) {
      //could not begin a transaction for some reason.
      console.log(err);
      return conn.closeSync();
    }

    for (i = 0; i < msg.payload.Readings.length; i++) 
    { 
        var result = conn.querySync("INSERT INTO db2.table (" + 
                                    "I_ID," +
                                    "D_ID," +
                                    "field1," +
                                    "field2," +
                                    "field3," +
                                    "field4," +
                                    "field5," +
                                    "field6," +
                                    "field7," +
                                    "field8," +
                                    "field9," +
                                    "field10)" +
                                    "values (" +
                                    "1A," +
                                    "'"+ msg.payload.field1 + "',"+
                                    "'" + JSON.stringify(msg.payload.Readings[i].field2) + "'," +
                                    msg.payload.Readings[i].field3 + ","+
                                    msg.payload.Readings[i].field4 + ","+
                                    msg.payload.Readings[i].field5 + ","+
                                    msg.payload.Readings[i].field6 + ","+
                                    msg.payload.Readings[i].field7 + ","+
                                    msg.payload.Readings[i].field8 + ","+
                                    msg.payload.Readings[i].field9 + ","+
                                    msg.payload.Readings[i].field10 + ","+
                                    "'2016-05-31 22:28:51.000000'" +
                                    ");");
    }

    conn.commitTransaction(function (err) {
      if (err) {
        //error during commit
        console.log("****ERROR: " + err);
        node.error("**** ERROR: ", err);
        //return conn.closeSync();
      }

      //Close the connection
      conn.closeSync();
    });
  });
});
} catch (e) {
    node.error("**** ERROR: ", err); 
}

When I run this with a valid SQL statement and all the data types are correctly sent, I get no errors. Everything works!!

*** The issue: When I did some testing to force a SQL error, the Node-Red instance crashes. I forced the error by sending a valid SQL statement, but the data elements in one of the fields is a non-numeric value where the table definition is expecting numeric only. I see in the error logs (console out) two lines..

The error SQL error message displayed is exactly what I was expecting to catch in my application. Instead the application just crashes.

[red] Uncaught Exception: Error: [IBM][CLI Driver][DB2/LINUXX8664] SQL0103N The numeric literal "1A" is not valid. SQLSTATE=42604

Any insight on what is really happening would be greatly appreciated. I am wondering if I put this code inside a custom built node would I be able to catch the error. Could this be a limitation of using the function node as a wrapper to my code. ???? *

Try wrapping the conn.querySync call in it's own try/catch block. The current block is out of scope because the call happens in another function (the one passed into beginTransaction). Also any reason you're rolling your own rather than the existing node npmjs.com/package/node-red-contrib-db2? — hardillb
THANK YOU.. !!!! That worked!!! - I did some experimenting with the contrib-db2 node, but since my data comes into the node as an array of elements that needs to be individually inserted into the database, I was struggling with how I could run multiple SQL inserts in a for loop. I guess I could have done a loop in the flow, but I started down this path thinking it would be easier. Looking back, I probably made a bad decision. Again.. THANK YOU.. You saved the day!! — Dave Krier

hardillb hardillb · Accepted Answer · 2016-06-07T21:40:30

Try wrapping the conn.querySync call in it's own try/catch block. The current block is out of scope because the call happens in another function (the one passed into beginTransaction).

e.g.

try {
context.global.ibmdb.open("DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;", function (err,conn) {

  conn.beginTransaction(function (err) {
    if (err) {
      //could not begin a transaction for some reason.
      console.log(err);
      return conn.closeSync();
    }

    for (i = 0; i < msg.payload.Readings.length; i++) 
    {
      try {
        var result = conn.querySync("INSERT INTO db2.table (" + 
                                    "I_ID," +
                                    "D_ID," +
                                    "field1," +
                                    "field2," +
                                    "field3," +
                                    "field4," +
                                    "field5," +
                                    "field6," +
                                    "field7," +
                                    "field8," +
                                    "field9," +
                                    "field10)" +
                                    "values (" +
                                    "1A," +
                                    "'"+ msg.payload.field1 + "',"+
                                    "'" + JSON.stringify(msg.payload.Readings[i].field2) + "'," +
                                    msg.payload.Readings[i].field3 + ","+
                                    msg.payload.Readings[i].field4 + ","+
                                    msg.payload.Readings[i].field5 + ","+
                                    msg.payload.Readings[i].field6 + ","+
                                    msg.payload.Readings[i].field7 + ","+
                                    msg.payload.Readings[i].field8 + ","+
                                    msg.payload.Readings[i].field9 + ","+
                                    msg.payload.Readings[i].field10 + ","+
                                    "'2016-05-31 22:28:51.000000'" +
                                    ");");
      } catch (excp) {
         //do stuff
      }
    }

    conn.commitTransaction(function (err) {
      if (err) {
        //error during commit
        console.log("****ERROR: " + err);
        node.error("**** ERROR: ", err);
        //return conn.closeSync();
      }

      //Close the connection
      conn.closeSync();
    });
  });
});
} catch (e) {
    node.error("**** ERROR: ", err); 
}

NodeRed Function Node Crashes - [red] Uncaught Exception:

1 Answers