Overview

This example reads records from a table in blocks of 50 rows at a time into a data structure array declared with pjs.define(). Fetching multiple rows per call reduces the number of round trips to the database, which can significantly improve performance when reading large result sets.

Use this approach when you need strongly typed values and are reading many rows, or when converting RPG programs that use multiple-row FETCH into a data structure array.

The examples in this section use a sample IBM i Db2 table, demolib/productsp, with the following columns: prid (packed decimal 5,0), prname (char 30), and prdesc (char 60). Replace the library, table, and column names with your own. When a field or data structure is used to receive fetched data, its definition should match the corresponding table column.

Example

Fetch 50 rows at a time into a data structure array

// Define a data structure array with 50 elements (dim: 50). Each element
// receives one row. The subfield definitions should match the table columns.
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 }
}});

// Clear the array before the first fetch.
pjs.clear(productsp_out);

// Allocate a statement handle and run the SELECT statement directly.
var c1 = pjs.allocStmt();
c1.executeDirect("SELECT prid, prname, prdesc FROM demolib/productsp ORDER BY prid");

// sqlstate is updated after every SQL API call. "00000" means success.
if (sqlstate == "00000") {

  // hasMoreRows() returns false once a fetch finds no more rows.
  while (c1.hasMoreRows()) {

    // Fetch up to 50 rows at a time into the data structure array.
    pjs.fetch(c1, productsp_out, 50);

    // rowCount() is the number of rows actually returned by the last fetch.
    // It can be less than 50 on the final block. Data structure arrays are 1-based.
    for (var i = 1; i <= c1.rowCount(); i++) {
      console.log({
        id: productsp_out[i].prid,
        name: productsp_out[i].prname,
        desc: productsp_out[i].prdesc
      });
    }

  }

}

// Close the cursor and free the statement handle.
c1.close();

How It Works

Data structure array - The dim: 50 option creates an array of 50 data structures. Each fetched row fills one element, and each subfield is filled from the column in the same position in the SELECT statement.
Running the statement - pjs.allocStmt() allocates a statement handle, and pjs.executeDirect() runs the SELECT statement and opens a cursor.
Checking for errors - The global sqlstate and sqlcode variables are updated after every SQL API call. A sqlstate of 00000 (or a sqlcode of 0) means the call succeeded (see the SQL Diagnostics page for details).
Multi-row fetch - Passing a number as the last argument of pjs.fetch() sets the maximum number of rows to fetch. Elements beyond the number of rows returned are cleared, so the final (partial) block does not contain data from the previous block.
Row count - pjs.rowCount() (or c1.rowCount()) returns the number of rows returned by the most recent fetch. Use it, rather than the fixed size of 50, as the loop limit.
Fetch loop - pjs.hasMoreRows() returns false once a fetch finds no more rows, which ends the loop.
Closing the statement - pjs.close() closes the cursor and frees the statement handle. Always close statements when you are finished with them.

Data structure arrays in Profound.js are 1-based, like RPG arrays: the first element is productsp_out[1]. Arrays of primitive JavaScript objects, as used in the Multi-row fetch into primitive object example, are 0-based.

Multi-row Fetch Into Data-Structure Array

Overview

Example

Fetch 50 rows at a time into a data structure array

How It Works

Related Pages