Multi-row fetch into data-structure array with parameters

The following example reads records from a table 50 rows at a time, into a data-structure array, using binding parameters for a search:

// The data structure array subfield definitions should match table column definitions
pjs.define("productsp_out", { type: 'data structure', qualified: true, dim: 50, elements: {
  "prid": { type: "packed decimal", length: 5, decimals: 0 },
  "prname": { type: "char", length: 30 },
  "prdesc": { type: "char", length: 60 }
}})

pjs.define("searchQuery", { type: "char", length: 30 });
 
var c1;
pjs.clear(productsp_out);
 
var descsearch = "Garmin";  // This can be replaced with user input

if (descsearch.trim() == "") {
  // If parameter is blank, just executeDirect
  c1 = pjs.allocStmt();
  c1.executeDirect("SELECT prid, prname, prdesc FROM demolib/productsp ORDER BY prid");
} else {
  // If parameter not blank, prepare and bind the parameter
  var searchQuery = '%' + descsearch + '%';
  c1 = pjs.prepare("SELECT prid, prname, prdesc FROM demolib/productsp WHERE prdesc LIKE ? ORDER BY prid");
  c1.bindParameters([searchQuery]);
  c1.execute();
}

if (sqlstate == '00000') {

  while (pjs.hasMoreRows(c1)) {

    pjs.fetch(c1, productsp_out, 50);
    for (var i = 1; i <= pjs.rowCount(c1); i++) {
      console.log({ 
        id: productsp_out[i].prid, 
        name: productsp_out[i].prname, 
        desc: productsp_out[i].prdesc 
      });
    }

  }

}

c1.close();

Profound Logic Documentation

Multi-row fetch into data-structure array with parameters

Analytics