Node-RED中DB2节点如何统计INSERT/MERGE受影响行数并返回HTTP响应？

Hey there! Let’s walk through how to get those row counts and send a proper response back to your HTTP POST request—no fancy DB2 expertise required here.

• Step 1: Adjust Your DB2 Query to Capture Row Counts
  DB2 doesn’t spit out row counts automatically for INSERT/MERGE in a way Node-RED can grab easily, but we can use GET DIAGNOSTICS to capture that number explicitly. Here’s how to structure your queries:

  For INSERT:

  INSERT INTO your_table (col1, col2) VALUES ('val1', 'val2');
  GET DIAGNOSTICS @row_count = ROW_COUNT;
  SELECT @row_count AS affected_rows FROM SYSIBM.SYSDUMMY1;
  

  For MERGE:

  MERGE INTO your_target t
  USING (SELECT 'val1' AS col1, 'val2' AS col2 FROM SYSIBM.SYSDUMMY1) s
  ON t.id = s.id
  WHEN MATCHED THEN UPDATE SET t.col2 = s.col2
  WHEN NOT MATCHED THEN INSERT (col1, col2) VALUES (s.col1, s.col2);
  GET DIAGNOSTICS @row_count = ROW_COUNT;
  SELECT @row_count AS affected_rows FROM SYSIBM.SYSDUMMY1;
  

  This makes the DB2 node return a payload with the affected_rows value we need later.

• Step 2: Format the Response in Node-RED
  Drop a Function node right after your DB2 node. Inside it, we’ll pull the row count and build a clean success message:

  // Grab the number of affected rows from the DB2 result
  const rowCount = msg.payload[0].affected_rows;
  
  // Build your response payload
  msg.payload = {
    status: "success",
    message: "Operation completed without issues",
    affected_rows: rowCount
  };
  
  return msg;
  

• Step 3: Send the Response Back to the HTTP Request
  Connect the Function node to an HTTP Response node. The default settings (200 OK status) work perfectly here—it’ll send your formatted payload straight back to the original POST request sender.

• Bonus: Handle Errors Cleanly
  Don’t forget to wire the error output of the DB2 node to another Function + HTTP Response pair for error handling. For example:

  msg.payload = {
    status: "error",
    message: msg.error.message || "DB2 operation failed unexpectedly"
  };
  msg.statusCode = 500; // Mark as a server error
  return msg;
  

  This ensures your client gets a clear error message instead of a silent failure.

Quick check: Make sure your DB2 node is set to return the full result set (the default should be fine, but double-check the node settings to confirm it’s not just returning a success flag).

内容的提问来源于stack exchange，提问作者rtrigo