Overview

This example reads records from a table one row at a time into individual fields that were declared with pjs.define(). Each call to pjs.fetch() places the value of each selected column into the field listed in the same position, so the order of the fields passed to pjs.fetch() must match the order of the columns in the SELECT statement.

Use this approach when you want strongly typed values (for example, a packed decimal field for a numeric column) or when converting RPG programs that use embedded SQL with host variables.

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 one row at a time into fields

// Define one field for each column that will be fetched.
// The field definitions should match the table column definitions.
pjs.define("prid", { type: "packed decimal", length: 5, decimals: 0 });
pjs.define("prname", { type: "char", length: 30 });
pjs.define("prdesc", { type: "char", length: 60 });

// 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") {

  // Fetch the first row. Each column value is placed into the matching field.
  pjs.fetch(c1, prid, prname, prdesc);

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

    // Process the fetched row here. For example:
    console.log("Product " + prid + ": " + prname);

    // Fetch the next row into the same fields.
    pjs.fetch(c1, prid, prname, prdesc);
  }

}

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

How It Works

Field definitions - The three pjs.define() calls declare fields whose types and lengths match the prid, prname, and prdesc columns.
Running the statement - pjs.allocStmt() allocates a statement handle, and pjs.executeDirect() runs the SELECT statement and opens a cursor. Statement methods such as c1.executeDirect() are equivalent to calling pjs.executeDirect(c1, ...).
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).
Fetch loop - The first pjs.fetch() is done before the loop. When a fetch finds no more rows, pjs.hasMoreRows() returns false and the loop ends. The fields retain the values from the last successful fetch.
Closing the statement - pjs.close() closes the cursor and frees the statement handle. Always close statements when you are finished with them.

Fetch Into Fields

Overview

Example

Fetch one row at a time into fields

How It Works

Related Pages