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

Description

pjs.prepare() prepares an SQL statement for execution and returns a statement handle. It is the equivalent of the embedded SQL PREPARE statement.

A statement only needs to be prepared when it contains parameter markers, identified by ? symbols. After the statement is prepared, values are bound to the markers with pjs.bindParameters(), the statement is run with pjs.execute(), rows are read with pjs.fetch(), and the handle is released with pjs.close(). Statements without parameter markers can be run with pjs.allocStmt() and pjs.executeDirect() instead.

For the common case of running a statement and getting all of its rows back in one call, pjs.query() prepares, binds, executes, and fetches in a single step.

Syntax

var stmt = pjs.prepare(sqlString);                       // prepare on the default database connection
var stmt = pjs.prepare(pjs.getDB("crm"), sqlString);      // prepare on the named database connection "crm"

Parameters

Database connection (Object, optional) - A database connection definition object returned by pjs.getDB() or pjs.getRemoteDB(). If omitted, the default database connection is used.
SQL statement (String) - The SQL statement to prepare. Parameter markers are written as ?. The statement must be valid for the database that the connection uses.

Return Value

A statement handle object for the prepared statement. The handle is passed as the first parameter to pjs.bindParameters(), pjs.execute(), the fetch APIs, and pjs.close(), or those APIs are called as methods of the handle (for example stmt.execute()).

Exception Handling

SQL diagnostics are reported in the SQLCA (see the IBM documentation for SQLCA field descriptions). SQLCA fields are defined in Profound.js programs with their names in lowercase.

sqlcode - Set to 0 when the statement is prepared successfully.
sqlcode - Set to a negative value when the database reports an error; check sqlstate and sqlerrmc for details.

Additional SQL diagnostic output can be enabled with the PJS_SQL_DEBUG setting.

Examples

Example 1: Prepare, Bind, Execute, and Fetch into a Data Structure Array

This example prepares a search statement with one parameter marker, binds the search value, executes the statement, and fetches up to 50 matching rows into a data structure array in a single call. The data structure is defined from the table with extName, so its subfields match the table columns.

Prerequisite: a table PRODUCTSP in the library list with the columns PRID, PRNAME, and PRDESC. Substitute your own table.

Prepare a statement with a parameter marker

// Define a data structure array whose subfields come from the table definition.
pjs.define("productsp_out", { type: "data structure", qualified: true, dim: 50, extName: "productsp" });
pjs.define("searchQuery", { type: "char", length: 30 });

pjs.clear(productsp_out);

// Wrap the search text in % so that LIKE matches anywhere in the description.
searchQuery = "%" + "Garmin" + "%";

// Prepare the statement. The ? is replaced by a bound value at execution time,
// so the search text never has to be escaped.
var stmt = pjs.prepare("SELECT * FROM productsp WHERE prdesc LIKE ? ORDER BY prid");

// Bind the value, execute the statement, and fetch up to 50 rows into the array.
stmt.bindParameters(searchQuery.trim());
stmt.execute();
pjs.fetch(stmt, productsp_out, 50);

// pjs.rowCount() reports how many rows were placed in the array.
console.log(pjs.rowCount(stmt) + " products found");

stmt.close();

Example 2: Reuse a Prepared Statement with Different Values

Because the statement is prepared once, it can be executed several times with different bound values. Each execution opens a new cursor on the same handle; close the handle when the last execution is finished.

Execute a prepared statement more than once

pjs.define("prname", { type: "char", length: 30 });

var stmt = pjs.prepare("SELECT prname FROM productsp WHERE prid = ?");

var ids = [101, 102, 103];

ids.forEach(function (id) {
  // Bind the next id and run the statement again.
  pjs.bindParameters(stmt, [id]);
  pjs.execute(stmt);

  // Fetch the single matching row into prname. sqlcode is 100 when no row matches.
  pjs.fetch(stmt, prname);
  if (sqlcode === 0) {
    console.log("Product " + id + ": " + prname);
  }
});

pjs.close(stmt);

pjs.prepare()