ras-js

Node.js client for the Profound Logic Remote Access Server (RAS) for IBM i. RAS provides a secure interface for accessing IBM i data and objects.

ras-js can used on IBM i, or any other platform with an active LTS version of Node.js.

• When running on IBM i, connections can be made either directly to the local Db2 using a native interface, or by TCP/IP network connection to a RAS server instance running on the local or a remote IBM i system.
• When running on other platforms, connections are made by TCP/IP network connection to a RAS server instance running on the target IBM i system.

Installation

npm install @profoundlogic/ras-js

Getting Started

const ras = require("@profoundlogic/ras-js");

Creating a Connection

const connection = new ras.Connection({
  host: "my_ibmi",
  user: "bob",
  password: "secret"
});
await connection.connect();

Running an SQL Statement

const connection = new ras.Connection(opts);
await connection.connect();
try {
  const query = connection.query("select * from mytable");
  const result = await query.execute();
  console.log(result);
}
finally {
  await connection.close();
}

Using SQL Input Parameters

Pass an array of parameters to Connection.query() following the SQL statement.

Parameters can be of type string, number, bigint, Buffer or null, depending on the parameter type. For example, Buffer is only valid for binary parameters.

const query = connection.query("select * from qsys2.systables where table_schema = ? and iasp_number = 0 limit 5", ["QSYS2", 0]);
const result = await query.execute();

Calling Stored Procedures

const query = connection.query(
  "call my_stored_procedure(?, ?, ?)",
  [
    "abc",          // Input parameter.
    {},             // Output parameter.
    { input: 123 }  // Input/output parameter.
  ]
);
const result = await query.execute();

Block Fetching

const query = connection.query("select * from mytable", { rows: 100, close: false });
let result = await query.execute();
let rows = result.results;
while (result.state !== "complete") {
  result = await query.fetch();
  rows = rows.concat(result.results);
}
console.log(rows);

Query Results

The query and fetch methods return an object that contains:

• state: The current state of the query lifecycle. Can be "not_executed", "executing", "executed", or "complete".
• sqlcode: Db2 SQLCODE value.
• sqlstate: Db2 SQLSTATE value.
• message: Diagnostic message returned by Db2, if any.
• results: Array of rows represented as objects keyed by column name. Present only if statement returns a result set.
• columns: Array of result column metadata objects. Present only if statement returns a result set.
• affectedRows: Number of rows modified INSERT/UPDATE/DELETE statements. Not present for other statement types.
• outputParams: Object with keys/values representing stored procedure output parameter names/values.
• outputParamMetadata: Array of stored procedure output parameter metadata objects.

Result Metadata

Result Column Metadata

When a query returns a result set, the columns property contains an array of metadata objects describing each column. Each metadata object contains:

| Property | Type | Description | | --- | --- | --- | | name | string | Column name. | | type | string | SQL data type name. See table below for possible values. | | length | number | Column length in bytes. | | precision | number | Number of digits for numeric types, or character length for character types. | | scale | number | Number of digits after the decimal point for decimal/numeric types. | | ccsid | number | Coded Character Set Identifier (CCSID) for character types. | | nullable | boolean | Whether the column can contain null values. |

Output Parameter Metadata

When calling a stored procedure with output parameters, the outputParamMetadata property contains an array of metadata objects describing each parameter. Each metadata object contains:

| Property | Type | Description | | --- | --- | --- | | name | string | Parameter name. | | type | string | SQL data type name. See table below for possible values. | | ccsid | number | Coded Character Set Identifier (CCSID) for character types. |

SQL Type Names

The type property in metadata objects will be one of the following strings:

| Type Name | SQL Type | JavaScript Type | | --- | --- | --- | | CHAR | CHAR | string or Buffer (if CCSID 65535 and w/o force_translate) | | VARCHAR | VARCHAR | string or Buffer (if CCSID 65535 and w/o force_translate) | | GRAPHIC | GRAPHIC | string | | VARGRAPHIC | VARGRAPHIC | string | | CLOB | CLOB | string | | DBCLOB | DBCLOB | string | | DECIMAL | DECIMAL | number or string (if precision > 15) | | NUMERIC | NUMERIC | number or string (if precision > 15) | | SMALLINT | SMALLINT | number | | INTEGER | INTEGER | number | | BIGINT | BIGINT | bigint | | REAL | REAL | string | | DOUBLE | DOUBLE | string | | BINARY | BINARY | Buffer | | VARBINARY | VARBINARY | Buffer | | BLOB | BLOB | Buffer | | DECFLOAT | DECFLOAT | string |

Example:

const query = connection.query("select name, age, salary from employees");
const result = await query.execute();

console.log(result.columns);
// [
//   { name: "NAME", type: "VARCHAR", length: 50, precision: 50, scale: 0, ccsid: 37, nullable: true },
//   { name: "AGE", type: "INTEGER", length: 4, precision: 10, scale: 0, ccsid: 0, nullable: true },
//   { name: "SALARY", type: "DECIMAL", length: 2306, precision: 9, scale: 2, ccsid: 0, nullable: true }
// ]

Connection Options

The following connection options can be passed to the Connection constructor. Pass an object with any of these keys:

| Option | Type | Description | | --- | --- | --- | | host | string | Host name or IP address of the RAS server. Default: "localhost". Ignored when running on IBM i unless clientServerMode is true. | | port | number | RAS server port. Default: 8240. Ignored when running on IBM i unless clientServerMode is true. | user | string | IBM i user profile. | | password | string | IBM i password. | | rdb | string | Relational database name (RDB) to connect to. Default: *LOCAL. | | verifyServerCertificate | boolean | Set to false to allow self-signed certificates during development. Do not use this option in production environments or with live data. Defaults to true. | | ca | string \| Buffer \| Array<string \| Buffer> | Optionally override the trusted CA certificates. Can be specified as a string, Buffer, or array containing certificate strings or Buffers in PEM format. | | timeout | number | Connection handshake timeout, in milliseconds. | | clientServerMode | boolean | Set to true to force the use of the TCP/IP transport when running on IBM i. The native transport is used by default on IBM i when this is false. | | auto_commit | boolean | Enable or disable automatic commit of each SQL statement run. Default is true. | | commit | string | Db2 commit level. Accepts "none", "nc", "chg", "ur", "cs", "all", "rs", or "rr". Default is none. | | cursor_sensitivity | string | Db2 cursor sensitivity. Accepts "insensitive", "sensitive", or "asensitive". Default is "asensitive". | | date_format | string | Date format. Accepts "iso", "usa", "eur", "jis", "mdy", "dmy", "ymd", "jul", or "job". | | date_separator | string | Date separator, e.g. "/", "-", ".", ",", " ", or "job". | | decimal_separator | string | Decimal separator, ".", ",", or "job". | | force_translate | boolean | Set to true to force translation of CHAR/VARCHAR columns with CCSID 65535 to Unicode text using job CCSID. By default these values are treated as binary. | | max_size | number | Maximum size of memory to allocate for each query, in bytes. Defaults to 16777216 (16MiB) | | naming | string | Naming convention for unqualified object names. Accepts "sql" or "sys". | | rows | number | Number of rows to fetch per request. Defaults to 500. | | sort_sequence | string | Sort sequence table for character comparisons. Accepts "hex", "job", or "jobrun". | | time_format | string | Time format. Accepts "iso", "usa", "eur", "jis", or "hms". | | time_separator | string | Time separator. Accepts ":", ".", ",", " ", or "job". | | debug | boolean | Set to true to enable logging of debug messages to the server's stanard output log. |

Query Options

The following query options can be passed to the Connection.query method.

| Option | Type | Description | | --- | --- | --- | | close | boolean | Set to false to keep query open after initial execution. Can be used for block fetch. | | rows | number | See above. | | max_size | number | See above. | | debug | boolean | See above. | | force_translate | boolean | See above. | | cursor_sensitivity | string | See above. |

Query options are passed by passing an object following the SQL statement text, or following the parameters, if any. For example:

const query = connection.query("select * from my_table", { rows: 500 });
const query2 = connection.query("select * from my_table where mycol = ?", ["my_value"], { cursor_sensitivity: "sensitive" })

Connection Pooling

A connection pool can be created with the createPool() method:

const ras = require("@profoundlogic/ras-js");
const pool = await ras.createPool({
  host: "my_ibmi",
  user: "bob",
  password: "secret",
  pool: {
    min: 1,
    max: 5,
    acquireTimeoutMillis: 2000
  }
});

All of the connection options listed above can be passed using the createPool() options object. The options object also has a pool key that is used to pass pool-specifc options:

| Option | Type | Description | | --- | --- | --- | | max | number | Maximum number of connections to create at any given time. Default is 1. | | min | number | Minimum number of connections to keep in pool at any given time. | maxWaitingClients | number | Maximum number of queued requests allowed. Additional acquire() calls will reject. | | testOnBorrow | boolean | Set to true to validate connections before they are acquired from the pool. | | testOnReturn | boolean | Set to true to validate connections when they are returned to the pool. | | acquireTimeoutMillis | number | Maximum time in milliseconds an acquire() call will wait for an avialable connection before rejecting. | | destroyTimeoutMillis | number | Maximum time in milliseconds a destroy() call will wait before rejecting. | | fifo | boolean | If true, the oldest connections will be first to be allocated. If false, the most recently released connections will be the first to be allocated. This in effect turns the pool's behaviour from a queue into a stack. Default is true. | | priorityRange | number | Integer between 1 and x - if set, borrowers can specify their relative priority in the queue if no connections are available. Default is 1. | | autostart | boolean | Should the pool start creating connections, initialize the evictor, etc once the constructor is called. If false, the pool can be started by calling pool.start(), otherwise the first call to acquire() will start the pool. Default is true. | | evictionRunIntervalMillis | number | How often to run eviction checks. Default is 0 (does not run). | | numTestsPerEvictionRun | number | Number of connections to check each eviction run. Default: 3. | | idleTimeoutMillis | number | The minimum amount of time that an object may sit idle in the pool before it is eligible for eviction due to idle time. Default: 30000 |

Acquiring and Releasing Pool Connections

const connection = await pool.acquire();
const query = connection.query("select * from mytable");
const result = await query.execute();
console.log(result);
await connection.release();

Connections can also be released with the pool's release() method:

await pool.release(connection);

To destroy a connection on returning it to the pool, rather than making it available again:

await connection.destroy();

Connections can also be destroyed using the pool's destroy() method:

await pool.destroy(connection);

The pool also provides a query() method that is a shortcut for acquring a connection, running a query, and releasing it in one call.

const result = await pool.query("select * from mytable where mycol = ?", [123]);

The parameters to Pool.query() are the same as for Connection.query().

Ending a Connection Pool

When the pool is no longer needed, call the end() method to destroy all connections and shut down:

await pool.end();

A Promise is returned that resolves once all connections have been returned and destroyed.

If the end() method is not called, the process will never end.

Additional Pool Methods and Properties

• ready() returns a Promise that resolves when min number of connections have been successfully created.
• size contains the number of total (free or in use) connections in the pool.
• available contains the number of unused connections in the pool.
• borrowed contains the number of acquired / in-use connections.
• pending contains the number of callers waiting to acquire a connection.
• max contains the maximum number of connections allowed in the pool.
• min contains the minimum number of connections allowed in the pool.

Pool Events

pool.on("createError", error => {
  console.error("createError", error);
});

pool.on("destroyError", error => {
  console.error("destroyError", error);
});

Error handling

The Query.execute() and Query.fetch() methods return Promises that reject for any error attempting to execute the statement.

SQL-related errors reject with a sub-class of Error named SQLError which has additional properties sqlcode, sqlstate, and message that describe the problem.

Errors that are not related to a specific query, such as socket errors, server disconnects etc., are emitted as standard "error" events from the connection object. You should add a handler for this event, as the process wil terminate if an event is emitted and not handled.

For example:

connection.on("error", function(error) => {
  console.log("Connection error", error);
});