pjs.fetch()

Fetch the next row from the cursor associated with the statement handle.

This API can either fetch data into declared fields / data structures / arrays, or return data as primitive objects / arrays.

When used to return data into primitive JavaScript objects / arrays, it can be called in the form of a method attached to a statement object as follows: stmt.fetch().

Parameters

Statement handle returned from pjs.allocStmt() or pjs.prepare(). The statement handle must be executed with pjs.execute() or pjs.executeDirect() before it can be used to fetch rows. This parameter is omitted if the API is called as a statement object method.
Optionally, one or more Profound.js field names can be passed. If field names are passed, they will be bound to columns in the resulting rows and populated by this API if the fetch is successful. If no Profound.js field names are passed, this API will return fetched rows as an Array of Objects with keys named for the columns and values as JavaScript Numbers for numeric columns, and JavaScript Strings for all other column types. An empty Array is returned if the fetch is not successful.
Following the optional field names, a Number can be passed that specifies the number of rows to fetch. Pass SQL_FETCH_ALL to fetch all rows. If not passed, 1 row will be fetched.

Exception Handling

SQL diagnostics are reported in the SQLCA:

https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/db2/rbafzfielddescsqlca.htm

SQLCA fields are defined in Profound.js programs with the names in lowercase. sqlcode will be set to zero if execution was successful.

Example

Fetch rows using Profound.js fields

pjs.define("prid", { type: "packed decimal", length: 5, decimals: 0 });
pjs.define("prname", { type: "char", length: 30 });
pjs.define("prdesc", { type: "char", length: 60 });
pjs.define("prprice", { type: "packed decimal", length: 7, decimals: 2 }); 
pjs.define("primage", { type: "char", length: 60 });
pjs.define("prqty", { type: "packed decimal", length: 5, decimals: 0 });
pjs.define("prcatid", { type: "packed decimal", length: 5, decimals: 0 });
 
var searchId = 103;
 
var stmt = pjs.prepare("select * from pjstest/productsp where prid > ?");
  
pjs.bindParameters(stmt, [
  [searchId, SQL_PARAM_INPUT]
]);
  
pjs.execute(stmt);
  
pjs.fetch(stmt, prid, prname, prdesc, prprice, primage, prqty, prcatid);

console.log("prid = %s", prid);
console.log("prname = %s", prname);
console.log("prdesc = %s", prdesc);
console.log("prprice = %s", prprice);
console.log("primage = %s", primage);
console.log("prqty = %s", prqty);
console.log("prcatid = %s", prcatid);
  
pjs.close(stmt);

Fetch rows as native JavaScript objects

var stmt = pjs.allocStmt();
stmt.executeDirect("select thiscol, thatcol from sometable");
var row = stmt.fetch();

console.log("thiscol = %s", row["thiscol"]);
console.log("thatcol = %s", row["thatcol"]);
stmt.close();

Requirements

When using an IBM i database, this API requires the Profound.js Connector module.