### Install Dependencies

Source: https://github.com/mapepire-ibmi/mapepire-js/blob/main/readme.md

Run this command after cloning the repository to install all necessary project dependencies.

```bash
npm install
```

--------------------------------

### Install Mapepire JS

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Install the Mapepire JS package using npm.

```bash
npm install @ibm/mapepire-js
```

--------------------------------

### Prepared Statements with Parameter Binding

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Use the `parameters` option in `QueryOptions` for parameterized queries to prevent SQL injection and enable server-side statement caching. This example shows both a select query and an update statement with parameters.

```typescript
import { SQLJob } from "@ibm/mapepire-js";

const job = new SQLJob();
await job.connect({ host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" });

// Single-row parameterized query
const result = await job.execute<{ COUNT: number }>(
  "SELECT COUNT(*) AS COUNT FROM SAMPLE.EMPLOYEE WHERE WORKDEPT = ? AND SALARY > ?",
  { parameters: ["D11", 60000] }
);
console.log(result.data[0].COUNT); // e.g., 4

// Update with parameters
const updateResult = await job.execute(
  "UPDATE MYLIB.PRODUCTS SET PRICE = ? WHERE PRODID = ?",
  { parameters: [29.99, "PROD-001"] }
);
console.log(updateResult.update_count); // 1

await job.close();
```

--------------------------------

### Configure Environment Variables

Source: https://github.com/mapepire-ibmi/mapepire-js/blob/main/readme.md

Copy the sample environment file and update it with your Mapepire daemon server details and IBM i user credentials.

```bash
cp .env.sample .env
```

--------------------------------

### Initialize and Connect SQLJob

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Initialize an SQLJob with JDBC options and establish a WebSocket connection to the Db2 server. Ensure to set `rejectUnauthorized` to `true` in production with a valid CA.

```typescript
import { SQLJob } from "@ibm/mapepire-js";

const job = new SQLJob({
  "naming": "sql",
  "libraries": ["MYLIB", "QGPL"],
  "transaction isolation": "read uncommitted",
});

const result = await job.connect({
  host: "my-ibmi-server.example.com",
  port: 8076,
  user: "DBUSER",
  password: "s3cr3t",
  rejectUnauthorized: false, // set true in production with valid CA
});

console.log(result.job);   // "QSECOFR/QSECOFR/12345" — server job name
console.log(result.success); // true

await job.close();
```

--------------------------------

### Run Tests

Source: https://github.com/mapepire-ibmi/mapepire-js/blob/main/readme.md

Execute this command to run the project's test suite after configuring your environment variables.

```bash
npm run test
```

--------------------------------

### Query Plan Analysis with SQLJob.explain()

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Send a SQL statement to the server's Visual Explain (DOVE) engine. Use `ExplainType.RUN` to also execute the query, or `ExplainType.DO_NOT_RUN` to retrieve only the plan. Ensure the job is closed.

```typescript
import { SQLJob, States } from "@ibm/mapepire-js";

const job = new SQLJob();
await job.connect({ host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" });

const explainResult = await job.explain(
  "SELECT * FROM SAMPLE.EMPLOYEE WHERE WORKDEPT = 'D11'",
  States.ExplainType.DO_NOT_RUN
);

console.log(explainResult.success);    // true
console.log(explainResult.vemetadata); // query execution metadata
console.log(explainResult.vedata);     // visual explain data object

await job.close();
```

--------------------------------

### `SQLJob.explain()` — Query Plan Analysis

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Send a SQL statement to the server's Visual Explain (DOVE) engine to analyze its execution plan. You can choose to execute the query (`ExplainType.RUN`) or only retrieve the plan (`ExplainType.DO_NOT_RUN`).

```APIDOC
## `SQLJob.explain()` — Query Plan Analysis

`explain()` sends a SQL statement to the server's Visual Explain (DOVE) engine. Use `ExplainType.RUN` to also execute the query, or `ExplainType.DO_NOT_RUN` to retrieve only the plan.

```typescript
import { SQLJob, States } from "@ibm/mapepire-js";

const job = new SQLJob();
await job.connect({ host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" });

const explainResult = await job.explain(
  "SELECT * FROM SAMPLE.EMPLOYEE WHERE WORKDEPT = 'D11'",
  States.ExplainType.DO_NOT_RUN
);

console.log(explainResult.success);    // true
console.log(explainResult.vemetadata); // query execution metadata
console.log(explainResult.vedata);     // visual explain data object

await job.close();
```
```

--------------------------------

### Server Version Check with SQLJob.getVersion()

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Retrieve the Mapepire daemon's version and build date. Ensure the job is connected and closed properly.

```typescript
import { SQLJob } from "@ibm/mapepire-js";

const job = new SQLJob();
await job.connect({ host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" });

const version = await job.getVersion();
console.log(version.version);    // e.g., "0.6.0"
console.log(version.build_date); // e.g., "2024-05-01"

await job.close();
```

--------------------------------

### Run CL Commands with SQLJob.clcommand()

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Execute IBM i Control Language (CL) commands directly. The result includes a `joblog` array with message entries produced during execution. Remember to close the query and job.

```typescript
import { SQLJob, CLCommandResult } from "@ibm/mapepire-js";

const job = new SQLJob();
await job.connect({ host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" });

const clQuery = job.clcommand("CRTLIB LIB(TESTLIB) TEXT('Test Library')");
const result = await clQuery.execute() as unknown as CLCommandResult;

console.log(result.success); // true
result.joblog.forEach((entry) => {
  console.log(`[${entry.SEVERITY}] ${entry.MESSAGE_ID}: ${entry.MESSAGE_TEXT}`);
});
// [00] CPF2110: Library TESTLIB created.

await clQuery.close();
await job.close();
```

--------------------------------

### `SQLJob.getVersion()` — Server Version Check

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Retrieve the version and build date of the Mapepire daemon running on the server. This is useful for compatibility checks and diagnostics.

```APIDOC
## `SQLJob.getVersion()` — Server Version Check

Retrieve the Mapepire daemon's version and build date.

```typescript
import { SQLJob } from "@ibm/mapepire-js";

const job = new SQLJob();
await job.connect({ host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" });

const version = await job.getVersion();
console.log(version.version);    // e.g., "0.6.0"
console.log(version.build_date); // e.g., "2024-05-01"

await job.close();
```
```

--------------------------------

### SQLJob.execute() - Run SQL and Return All Results

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Executes a SQL statement, fetches all results, closes the cursor, and returns the typed result set. Throws an error if the SQL execution fails.

```APIDOC
## SQLJob.execute<T>(sql: string, options?: QueryOptions): Promise<ExecuteResult<T>>

### Description
`execute()` is the simplest way to run a SQL statement. It creates a `Query`, runs it to completion, closes the cursor, and returns the typed result set. Throws on SQL error.

### Parameters
#### `sql` (string) - Required
The SQL statement to execute.

#### `options` (QueryOptions) - Optional
Options for the query execution.

- **parameters** (Array<any>) - Optional - An array of parameters to bind to the SQL statement for prepared statements.

### Returns
`Promise<ExecuteResult<T>>` - A promise that resolves to an object containing the query results.

- **success** (boolean) - Indicates if the execution was successful.
- **has_results** (boolean) - Indicates if the query returned any results.
- **data** (Array<T>) - An array of result objects, typed according to the generic parameter `T`.
- **metadata** (object) - Metadata about the result set, including column information.
- **update_count** (number) - The number of rows affected by an UPDATE or DELETE statement.

### Example
```typescript
import { SQLJob } from "@ibm/mapepire-js";

interface Employee {
  EMPNO: string;
  FIRSTNME: string;
  LASTNAME: string;
  SALARY: number;
}

const job = new SQLJob();
await job.connect({ host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" });

try {
  const result = await job.execute<Employee>(
    "SELECT EMPNO, FIRSTNME, LASTNAME, SALARY FROM SAMPLE.EMPLOYEE WHERE SALARY > 50000"
  );

  console.log(result.success);      // true
  console.log(result.has_results);  // true
  console.log(result.data.length);  // number of rows returned
  console.log(result.data[0]);
  // { EMPNO: "000010", FIRSTNME: "CHRISTINE", LASTNAME: "HAAS", SALARY: 152750 }
  console.log(result.metadata.columns?.[0].name); // "EMPNO"
} catch (err) {
  console.error("SQL error:", err.message);
} finally {
  await job.close();
}
```
```

--------------------------------

### Server-Side Tracing with setTraceConfig() and getTraceData()

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Enable server-side trace logging for diagnostics. Trace output can go to a file or in-memory buffer. Remember to turn tracing off and close the job when finished.

```typescript
import { SQLJob } from "@ibm/mapepire-js";

const job = new SQLJob();
await job.connect({ host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" });

// Start tracing — write to in-memory buffer at full datastream level
await job.setTraceConfig("IN_MEM", "DATASTREAM");
console.log(job.getTraceFilePath()); // undefined for IN_MEM, path string for FILE

await job.execute("SELECT * FROM QSYS2.SYSSCHEMAS FETCH FIRST 5 ROWS ONLY");

const traceResult = await job.getTraceData();
console.log(traceResult.tracedata); // raw trace string

// Turn tracing off
await job.setTraceConfig("IN_MEM", "OFF");
await job.close();
```

--------------------------------

### Initialize and Use a Connection Pool

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Use `Pool` to manage multiple `SQLJob` connections for concurrent workloads. It scales automatically and selects the least-loaded job. Ensure `pool.init()` is called before executing queries and `pool.end()` to shut down connections.

```typescript
import { Pool } from "@ibm/mapepire-js";

const pool = new Pool({
  creds: {
    host: "ibmi.example.com",
    port: 8076,
    user: "USER",
    password: "PASS",
  },
  opts: { "naming": "sql" },
  maxSize: 10,
  startingSize: 3,
});

await pool.init(); // Opens 3 connections immediately

// Execute queries — pool picks the best available job
const result = await pool.execute<{ COUNT: number }ы>(
  "SELECT COUNT(*) AS COUNT FROM SAMPLE.EMPLOYEE"
);
console.log(result.data[0].COUNT); // e.g., 42

console.log(pool.getActiveJobCount()); // 3 (or more if scaled up)

// Shut down all connections
pool.end();
```

--------------------------------

### Batch Execution with addToBatch()

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Accumulate multiple parameter sets for a single prepared statement to execute them in one round-trip. Ensure to close the query and job when finished.

```typescript
import { SQLJob } from "@ibm/mapepire-js";

const job = new SQLJob();
await job.connect({ host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" });

const insertQuery = job.query(
  "INSERT INTO MYLIB.LOGS (LEVEL, MESSAGE, LOGTIME) VALUES (?, ?, CURRENT_TIMESTAMP)"
);

insertQuery.addToBatch([["INFO", "Application started"]]);
insertQuery.addToBatch([["WARN", "Low memory detected"]]);
insertQuery.addToBatch([["ERROR", "Connection timeout"]]);

const result = await insertQuery.execute();
console.log(result.update_count); // 3
console.log(result.success);      // true

await insertQuery.close();
await job.close();
```

--------------------------------

### SQLJob.query() + Query.execute() + Query.fetchMore() - Paginated Results

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Handles large result sets by creating a `Query` object. `execute()` fetches the first page, and `fetchMore()` retrieves subsequent pages until all data is fetched.

```APIDOC
## SQLJob.query<T>(sql: string): Query<T>

### Description
For large result sets, create a `Query` object directly. Call `execute(rowsToFetch)` to get the first page and `fetchMore(rowsToFetch)` to retrieve subsequent pages until `is_done` is `true`.

### Parameters
#### `sql` (string) - Required
The SQL statement to query.

### Methods on Query Object
#### `execute(rowsToFetch: number): Promise<PageResult<T>>`
Fetches the first page of results.

#### `fetchMore(rowsToFetch: number): Promise<PageResult<T>>`
Fetches the next page of results.

#### `close(): Promise<void>`
Closes the query cursor.

### Returns
`Query<T>` - A `Query` object that can be used to fetch paginated results.

### PageResult<T> Object
- **data** (Array<T>) - The rows fetched in the current page.
- **is_done** (boolean) - Indicates if all rows have been fetched.

### Example
```typescript
import { SQLJob } from "@ibm/mapepire-js";

interface Order { ORDNO: number; CUSTNO: number; AMOUNT: number; }

const job = new SQLJob();
await job.connect({ host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" });

const query = job.query<Order>("SELECT * FROM MYLIB.ORDERS ORDER BY ORDNO");

// Fetch first 50 rows
let page = await query.execute(50);
console.log(page.data.length); // up to 50
console.log(page.is_done);     // false if more rows remain

// Fetch additional pages
while (!page.is_done) {
  page = await query.fetchMore(50);
  console.log(`Fetched ${page.data.length} more rows`);
}

await query.close();
await job.close();
```
```

--------------------------------

### Batch Execution with `Query.addToBatch()`

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Accumulate multiple parameter sets for a single prepared statement and execute them in one round-trip to the server. This is useful for inserting or updating multiple rows efficiently.

```APIDOC
## Batch Execution with `Query.addToBatch()`

`addToBatch()` accumulates multiple parameter sets for a single prepared statement, executing them all in one round-trip.

```typescript
import { SQLJob } from "@ibm/mapepire-js";

const job = new SQLJob();
await job.connect({ host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" });

const insertQuery = job.query(
  "INSERT INTO MYLIB.LOGS (LEVEL, MESSAGE, LOGTIME) VALUES (?, ?, CURRENT_TIMESTAMP)"
);

insertQuery.addToBatch([["INFO", "Application started"]]);
insertQuery.addToBatch([["WARN", "Low memory detected"]]);
insertQuery.addToBatch([["ERROR", "Connection timeout"]]);

const result = await insertQuery.execute();
console.log(result.update_count); // 3
console.log(result.success);      // true

await insertQuery.close();
await job.close();
```
```

--------------------------------

### `SQLJob.clcommand()` — Run CL Commands

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Execute IBM i Control Language (CL) commands directly from your application. The result includes a `joblog` array containing message entries generated during the command's execution.

```APIDOC
## `SQLJob.clcommand()` — Run CL Commands

Execute IBM i Control Language (CL) commands directly. The result includes a `joblog` array with message entries produced during execution.

```typescript
import { SQLJob, CLCommandResult } from "@ibm/mapepire-js";

const job = new SQLJob();
await job.connect({ host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" });

const clQuery = job.clcommand("CRTLIB LIB(TESTLIB) TEXT('Test Library')");
const result = await clQuery.execute() as unknown as CLCommandResult;

console.log(result.success); // true
result.joblog.forEach((entry) => {
  console.log(`[${entry.SEVERITY}] ${entry.MESSAGE_ID}: ${entry.MESSAGE_TEXT}`);
});
// [00] CPF2110: Library TESTLIB created.

await clQuery.close();
await job.close();
```
```

--------------------------------

### Paginated Results with Query, Execute, and FetchMore

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

For large result sets, create a `Query` object. Use `execute(rowsToFetch)` for the first page and `fetchMore(rowsToFetch)` for subsequent pages until `is_done` is true. Ensure to close the query and job connections.

```typescript
import { SQLJob } from "@ibm/mapepire-js";

interface Order { ORDNO: number; CUSTNO: number; AMOUNT: number; }

const job = new SQLJob();
await job.connect({ host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" });

const query = job.query<Order>("SELECT * FROM MYLIB.ORDERS ORDER BY ORDNO");

// Fetch first 50 rows
let page = await query.execute(50);
console.log(page.data.length); // up to 50
console.log(page.is_done);     // false if more rows remain

// Fetch additional pages
while (!page.is_done) {
  page = await query.fetchMore(50);
  console.log(`Fetched ${page.data.length} more rows`);
}

await query.close();
await job.close();
```

--------------------------------

### `SQLJob.setTraceConfig()` + `getTraceData()` — Server-Side Tracing

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Enable and retrieve server-side trace logs for diagnostic purposes. Tracing can be configured to output to a file or an in-memory buffer.

```APIDOC
## `SQLJob.setTraceConfig()` + `getTraceData()` — Server-Side Tracing

Enable server-side trace logging for diagnostics. Trace output can go to a file or in-memory buffer.

```typescript
import { SQLJob } from "@ibm/mapepire-js";

const job = new SQLJob();
await job.connect({ host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" });

// Start tracing — write to in-memory buffer at full datastream level
await job.setTraceConfig("IN_MEM", "DATASTREAM");
console.log(job.getTraceFilePath()); // undefined for IN_MEM, path string for FILE

await job.execute("SELECT * FROM QSYS2.SYSSCHEMAS FETCH FIRST 5 ROWS ONLY");

const traceResult = await job.getTraceData();
console.log(traceResult.tracedata); // raw trace string

// Turn tracing off
await job.setTraceConfig("IN_MEM", "OFF");
await job.close();
```
```

--------------------------------

### Prepared Statements with Parameter Binding

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Utilizes parameterized queries by passing a `parameters` array in `QueryOptions` to prevent SQL injection and enable server-side statement caching.

```APIDOC
## SQLJob.execute() with Parameters

### Description
Pass a `parameters` array in `QueryOptions` to use parameterized queries, preventing SQL injection and enabling server-side statement caching.

### Parameters
#### `sql` (string) - Required
The SQL statement with placeholders (e.g., '?').

#### `options` (QueryOptions) - Optional
Options for the query execution.

- **parameters** (Array<any>) - Required - An array of values to bind to the placeholders in the SQL statement, in order.

### Example
```typescript
import { SQLJob } from "@ibm/mapepire-js";

const job = new SQLJob();
await job.connect({ host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" });

// Single-row parameterized query
const result = await job.execute<{ COUNT: number }>( 
  "SELECT COUNT(*) AS COUNT FROM SAMPLE.EMPLOYEE WHERE WORKDEPT = ? AND SALARY > ?",
  { parameters: ["D11", 60000] }
);
console.log(result.data[0].COUNT); // e.g., 4

// Update with parameters
const updateResult = await job.execute(
  "UPDATE MYLIB.PRODUCTS SET PRICE = ? WHERE PRODID = ?",
  { parameters: [29.99, "PROD-001"] }
);
console.log(updateResult.update_count); // 1

await job.close();
```
```

--------------------------------

### Enable Local WebSocket Debug Logging with enableLocalTrace()

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Call `SQLJob.enableLocalTrace()` to enable console logging of all raw WebSocket messages for debugging. This is useful for development without altering server trace configurations. Ensure `SQLJob.connect()` is called after enabling trace.

```typescript
import { SQLJob } from "@ibm/mapepire-js";

const job = new SQLJob();
job.enableLocalTrace(); // Logs all inbound/outbound WS frames to console

await job.connect({ host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" });
// Console output: { id: "id1", type: "connect", ... }
// Console output: { id: "id1", success: true, job: "QSECOFR/..", ... }

await job.execute("VALUES CURRENT_DATE");
// Console output: { id: "query2", type: "sql", sql: "VALUES CURRENT_DATE", rows: 100 }
// Console output: { id: "query2", success: true, data: [{ "00001": "2024-09-15" }], ... }

await job.close();
```

--------------------------------

### Execute SQL and Fetch All Results

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Use `SQLJob.execute()` for simple queries. It runs the SQL statement, closes the cursor, and returns all typed results. Catches SQL errors using a try-catch block and ensures the connection is closed in a finally block.

```typescript
import { SQLJob } from "@ibm/mapepire-js";

interface Employee {
  EMPNO: string;
  FIRSTNME: string;
  LASTNAME: string;
  SALARY: number;
}

const job = new SQLJob();
await job.connect({ host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" });

try {
  const result = await job.execute<Employee>(
    "SELECT EMPNO, FIRSTNME, LASTNAME, SALARY FROM SAMPLE.EMPLOYEE WHERE SALARY > 50000"
  );

  console.log(result.success);      // true
  console.log(result.has_results);  // true
  console.log(result.data.length);  // number of rows returned
  console.log(result.data[0]);
  // { EMPNO: "000010", FIRSTNME: "CHRISTINE", LASTNAME: "HAAS", SALARY: 152750 }
  console.log(result.metadata.columns?.[0].name); // "EMPNO"
} catch (err) {
  console.error("SQL error:", err.message);
} finally {
  await job.close();
}
```

--------------------------------

### SQLJob.enableLocalTrace() - Local WebSocket Debug Logging

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

The `enableLocalTrace()` method on `SQLJob` enables local console logging of all raw WebSocket messages. This is useful for debugging WebSocket communication during development without altering server-side trace configurations.

```APIDOC
## `SQLJob.enableLocalTrace()` — Local WebSocket Debug Logging

Enable local console logging of all raw WebSocket messages for development-time debugging without touching server trace configuration.

```typescript
import { SQLJob } from "@ibm/mapepire-js";

const job = new SQLJob();
job.enableLocalTrace(); // Logs all inbound/outbound WS frames to console

await job.connect({ host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" });
// Console output: { id: "id1", type: "connect", ... }
// Console output: { id: "id1", success: true, job: "QSECOFR/..", ... }

await job.execute("VALUES CURRENT_DATE");
// Console output: { id: "query2", type: "sql", sql: "VALUES CURRENT_DATE", rows: 100 }
// Console output: { id: "query2", success: true, data: [{ "00001": "2024-09-15" }], ... }

await job.close();
```
```

--------------------------------

### getCertificate() and getRootCertificate() - TLS Certificate Utilities

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

These utility functions retrieve TLS certificate information for a given server. `getCertificate()` fetches the peer certificate chain, while `getRootCertificate()` retrieves the root CA certificate, useful for certificate pinning or validating self-signed CAs.

```APIDOC
## `getCertificate()` + `getRootCertificate()` — TLS Certificate Utilities

Retrieve the server's TLS certificate chain before establishing a connection, enabling certificate pinning or self-signed CA validation.

```typescript
import { getCertificate, getRootCertificate, SQLJob } from "@ibm/mapepire-js";

const server = { host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" };

// Inspect the full peer certificate
const cert = await getCertificate(server);
console.log(cert.subject.CN);  // e.g., "ibmi.example.com"
console.log(cert.valid_to);    // e.g., "Dec 31 23:59:59 2025 GMT"

// Get the root CA certificate (undefined if publicly trusted)
const rootCA = await getRootCertificate(server);

if (rootCA) {
  console.log("Self-signed or private CA detected — pinning certificate");
  const job = new SQLJob();
  await job.connect({ ...server, ca: rootCA, rejectUnauthorized: true });
  console.log("Connected with pinned CA certificate");
  await job.close();
} else {
  console.log("Publicly trusted CA — no pinning needed");
}
```
```

--------------------------------

### SQLJob - Single Connection

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Manages a single persistent WebSocket connection to the Mapepire daemon server. It can be configured with JDBC options and handles all database interactions for that connection.

```APIDOC
## SQLJob

### Description
Manages a single WebSocket connection to the Mapepire daemon server. It accepts optional `JDBCOptions` at construction time to control naming convention, library lists, transaction isolation, and dozens of other JDBC properties. All database interactions on the connection flow through this object.

### Constructor
`new SQLJob(jdbcOptions?: JDBCOptions)`

### Methods
#### `connect(connectionOptions: ConnectionOptions): Promise<ConnectResult>`
Establishes a connection to the Mapepire daemon.

- **host** (string) - Required - The hostname or IP address of the IBM i server.
- **port** (number) - Required - The port number for the Mapepire daemon.
- **user** (string) - Required - The username for authentication.
- **password** (string) - Required - The password for authentication.
- **rejectUnauthorized** (boolean) - Optional - Whether to reject unauthorized TLS certificates. Set to `true` in production with a valid CA.

#### `close(): Promise<void>`
Closes the WebSocket connection.

### Example
```typescript
import { SQLJob } from "@ibm/mapepire-js";

const job = new SQLJob({
  "naming": "sql",
  "libraries": ["MYLIB", "QGPL"],
  "transaction isolation": "read uncommitted",
});

const result = await job.connect({
  host: "my-ibmi-server.example.com",
  port: 8076,
  user: "DBUSER",
  password: "s3cr3t",
  rejectUnauthorized: false, // set true in production with valid CA
});

console.log(result.job);   // "QSECOFR/QSECOFR/12345" — server job name
console.log(result.success); // true

await job.close();
```
```

--------------------------------

### Execute Parameterized Queries with Pool.sql()

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Use `Pool.sql()` for safe, parameterized queries via tagged template literals. Interpolated values are automatically bound as parameters. Ensure the pool is initialized before use.

```typescript
import { Pool } from "@ibm/mapepire-js";

interface Product { PRODID: string; NAME: string; PRICE: number; }

const pool = new Pool({
  creds: { host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" },
  maxSize: 5,
  startingSize: 2,
});
await pool.init();

const dept = "D11";
const minSalary = 70000;

const result = await pool.sql<{ EMPNO: string; LASTNAME: string }>`
  SELECT EMPNO, LASTNAME FROM SAMPLE.EMPLOYEE
  WHERE WORKDEPT = ${dept} AND SALARY >= ${minSalary}
`;

console.log(result.data);
// [{ EMPNO: "000060", LASTNAME: "STERN" }, { EMPNO: "000150", LASTNAME: "ADAMSON" }]

pool.end();
```

--------------------------------

### UrlToDaemon() - Parse Connection URI

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

The `UrlToDaemon()` function parses a `db2i://` URI string into a `DaemonServer` object. This object can then be used with `SQLJob.connect()` or `Pool` for establishing database connections.

```APIDOC
## `UrlToDaemon()` — Parse Connection URI

Convert a `db2i://` URI string into a `DaemonServer` object for use with `SQLJob.connect()` or `Pool`.

```typescript
import { SQLJob, UrlToDaemon } from "@ibm/mapepire-js";

// Password must be base64-encoded in the URI
const encodedPassword = Buffer.from("s3cr3t").toString("base64");
const uri = `db2i://DBUSER:${encodedPassword}@ibmi.example.com:8076`;

const serverConfig = UrlToDaemon(uri);
console.log(serverConfig);
// { host: "ibmi.example.com", port: 8076, user: "DBUSER", password: "s3cr3t" }

const job = new SQLJob();
await job.connect(serverConfig);
const ver = await job.getVersion();
console.log(ver.version);
await job.close();
```
```

--------------------------------

### Pool.sql() - Tagged Template Literal Queries

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

`Pool.sql()` provides a safe tagged template literal API for parameterized queries. It automatically binds interpolated values as parameters, preventing SQL injection vulnerabilities.

```APIDOC
## `Pool.sql()` — Tagged Template Literal Queries

`Pool.sql()` provides a safe tagged template literal API for parameterized queries, automatically binding interpolated values as parameters.

```typescript
import { Pool } from "@ibm/mapepire-js";

interface Product { PRODID: string; NAME: string; PRICE: number; }

const pool = new Pool({
  creds: { host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" },
  maxSize: 5,
  startingSize: 2,
});
await pool.init();

const dept = "D11";
const minSalary = 70000;

const result = await pool.sql<{ EMPNO: string; LASTNAME: string }>`
  SELECT EMPNO, LASTNAME FROM SAMPLE.EMPLOYEE
  WHERE WORKDEPT = ${dept} AND SALARY >= ${minSalary}
`;

console.log(result.data);
// [{ EMPNO: "000060", LASTNAME: "STERN" }, { EMPNO: "000150", LASTNAME: "ADAMSON" }]

pool.end();
```
```

--------------------------------

### Parse Connection URI with UrlToDaemon()

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Convert a `db2i://` URI string into a `DaemonServer` object using `UrlToDaemon()`. The password in the URI must be base64-encoded. This object can then be used with `SQLJob.connect()` or `Pool`.

```typescript
import { SQLJob, UrlToDaemon } from "@ibm/mapepire-js";

// Password must be base64-encoded in the URI
const encodedPassword = Buffer.from("s3cr3t").toString("base64");
const uri = `db2i://DBUSER:${encodedPassword}@ibmi.example.com:8076`;

const serverConfig = UrlToDaemon(uri);
console.log(serverConfig);
// { host: "ibmi.example.com", port: 8076, user: "DBUSER", password: "s3cr3t" }

const job = new SQLJob();
await job.connect(serverConfig);
const ver = await job.getVersion();
console.log(ver.version);
await job.close();
```

--------------------------------

### Pool - Connection Pool Management

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

The `Pool` class manages multiple `SQLJob` connections for concurrent workloads. It automatically scales connections up to `maxSize` and selects the least-loaded job for each request. Use `init()` to open connections and `end()` to close them.

```APIDOC
## `Pool` — Connection Pool

`Pool` manages multiple `SQLJob` connections for concurrent workloads. It automatically scales up (within `maxSize`) when all connections are busy and selects the least-loaded job for each request.

```typescript
import { Pool } from "@ibm/mapepire-js";

const pool = new Pool({
  creds: {
    host: "ibmi.example.com",
    port: 8076,
    user: "USER",
    password: "PASS",
  },
  opts: { "naming": "sql" },
  maxSize: 10,
  startingSize: 3,
});

await pool.init(); // Opens 3 connections immediately

// Execute queries — pool picks the best available job
const result = await pool.execute<{ COUNT: number }>(
  "SELECT COUNT(*) AS COUNT FROM SAMPLE.EMPLOYEE"
);
console.log(result.data[0].COUNT); // e.g., 42

console.log(pool.getActiveJobCount()); // 3 (or more if scaled up)

// Shut down all connections
pool.end();
```
```

--------------------------------

### Retrieve TLS Certificates with getCertificate()

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Use `getCertificate()` and `getRootCertificate()` to retrieve TLS certificate chains for certificate pinning or self-signed CA validation before establishing a connection. `getRootCertificate()` returns `undefined` if the CA is publicly trusted.

```typescript
import { getCertificate, getRootCertificate, SQLJob } from "@ibm/mapepire-js";

const server = { host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" };

// Inspect the full peer certificate
const cert = await getCertificate(server);
console.log(cert.subject.CN);  // e.g., "ibmi.example.com"
console.log(cert.valid_to);    // e.g., "Dec 31 23:59:59 2025 GMT"

// Get the root CA certificate (undefined if publicly trusted)
const rootCA = await getRootCertificate(server);

if (rootCA) {
  console.log("Self-signed or private CA detected — pinning certificate");
  const job = new SQLJob();
  await job.connect({ ...server, ca: rootCA, rejectUnauthorized: true });
  console.log("Connected with pinned CA certificate");
  await job.close();
} else {
  console.log("Publicly trusted CA — no pinning needed");
}
```

--------------------------------

### Transaction Management with endTransaction()

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Enable transaction management by setting `"transaction isolation"` in `JDBCOptions`. Use `endTransaction()` to commit or roll back changes. Check `getPendingTransactions()` to determine if changes are pending.

```typescript
import { SQLJob, States } from "@ibm/mapepire-js";

const job = new SQLJob({ "transaction isolation": "read committed" });
await job.connect({ host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" });

console.log(job.underCommitControl()); // true

await job.execute("INSERT INTO MYLIB.ACCOUNTS (ID, BALANCE) VALUES (101, 1000.00)");
await job.execute("UPDATE MYLIB.ACCOUNTS SET BALANCE = BALANCE - 200 WHERE ID = 100");

const pending = await job.getPendingTransactions();
console.log(pending); // > 0 if changes are pending

if (pending > 0) {
  await job.endTransaction(States.TransactionEndType.COMMIT);
  console.log("Transaction committed");
}
// On error:
// await job.endTransaction(States.TransactionEndType.ROLLBACK);

await job.close();
```

--------------------------------

### Transactions — `endTransaction()`, `underCommitControl()`, `getPendingTransactions()`

Source: https://context7.com/mapepire-ibmi/mapepire-js/llms.txt

Manage database transactions by configuring transaction isolation and using `endTransaction()` to commit or roll back changes. `underCommitControl()` checks if transactions are enabled, and `getPendingTransactions()` returns the count of uncommitted changes.

```APIDOC
## Transactions — `endTransaction()`, `underCommitControl()`, `getPendingTransactions()`

Enable transaction management by setting `"transaction isolation"` in `JDBCOptions`. Use `endTransaction()` to commit or roll back.

```typescript
import { SQLJob, States } from "@ibm/mapepire-js";

const job = new SQLJob({ "transaction isolation": "read committed" });
await job.connect({ host: "ibmi.example.com", port: 8076, user: "USER", password: "PASS" });

console.log(job.underCommitControl()); // true

await job.execute("INSERT INTO MYLIB.ACCOUNTS (ID, BALANCE) VALUES (101, 1000.00)");
await job.execute("UPDATE MYLIB.ACCOUNTS SET BALANCE = BALANCE - 200 WHERE ID = 100");

const pending = await job.getPendingTransactions();
console.log(pending); // > 0 if changes are pending

if (pending > 0) {
  await job.endTransaction(States.TransactionEndType.COMMIT);
  console.log("Transaction committed");
}
// On error:
// await job.endTransaction(States.TransactionEndType.ROLLBACK);

await job.close();
```
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.