Retrieve Auto-Generated Row Id after Insert into DB2 Table

IBM DB2 has two ways of retrieving an auto-generated row id after inserting into a table:

Using a SELECT FROM INSERT statement
Using the IDENTITY_VAL_LOCAL() function

In Profound.js it's necessary to use a SELECT FROM INSERT statement. The IDENTITY_VAL_LOCAL() function can't be used in Profound.js as it will always return NULL, due to some technical limitations of the function.

A SELECT FROM INSERT statement both inserts a row into the database and also returns the inserted row in the result set, including any auto-generated row id column.

For example:

Using pjs.query()

let name = "Acme Corporation";
let address = "123 Main St.";
let row = pjs.query("select * from new table(insert into customer(name, address) values(?, ?))", [name, address]);

Using pjs.prepare()/pjs.execute()/pjs.fetch()

  pjs.define("name", { type: "varchar", length: 20 });
  pjs.define("address", { type: "varchar", length: 20 });
  name = "Acme Corporation";
  address = "123 Main St.";
  let row;
  let stmt = pjs.prepare("select * from new table(insert into customer(name, address) values(?, ?))");
  if (sqlcode === 0) {
    pjs.bindParameters(stmt, [[name, SQL_PARAM_INPUT], [address, SQL_PARAM_INPUT]]);
  }
  if (sqlcode === 0) {
    pjs.execute(stmt);
  }
  if (sqlcode === 0) {
    row = pjs.fetch(stmt);
  }