idb-pconnector

Promise based DB2 Connector for IBM i

Downloads in past

Stats

StarsIssuesVersionUpdatedCreatedSize
idb-pconnector
1.1.1a year ago6 years agoMinified + gzip package size for idb-pconnector in KB

Readme

npm ryver-chat ryver-signup docs
idb-pconnector - Promise-based DB2 Connector for IBM i
Project Status: (production ready as a "technology preview")
Objective: provide a promise-based database connector for DB2 on IBM i.
This project is a promise-based wrapper over the idb-connector project that enables the use of modern JavaScript's async/await syntax.
Connection Pooling is supported by using the DBPool class.
The DBPool class includes integrated aggregates (runSql and prepareExecute) to simplify your code.
Table of Contents
- [exec](#exec)
- [prepare bind execute](#prepare-bind-execute)
  - [insert example](#insert-example)
  - [select example](#select-example)
- [DBPool](#dbpool)
- [prepareExecute](#prepareexecute)
- [runSql](#runsql)
- [setLibraryList](#setlibrarylist)
Install
npm install idb-pconnector
NOTE
This package only installs on IBM i systems.
Examples

exec

Using exec method to run a select statement and return the result set:
const { Connection, Statement, } = require('idb-pconnector');

async function execExample() {
  const connection = new Connection({ url: '*LOCAL' });
  const statement = new Statement(connection);

  const results = await statement.exec('SELECT * FROM QIWS.QCUSTCDT');

  console.log(`results:\n ${JSON.stringify(results)}`);
}

execExample().catch((error) => {
  console.error(error);
});

prepare bind execute

Using prepare, bind, and execute methods to insert data:

insert example

const {
  Connection, Statement, IN, NUMERIC, CHAR,
} = require('idb-pconnector');

async function pbeExample() {
  const connection = new Connection({ url: '*LOCAL' });
  const statement = new Statement(connection);
  const sql = 'INSERT INTO US_STATES(id, name, abbr, region) VALUES (?,?,?,?)';

  await statement.prepare(sql);
  await statement.bindParameters([1, 'Alabama', 'AL' ,'south']);
  await statement.execute();
}

pbeExample().catch((error) => {
  console.error(error);
});

select example

const {
  Connection, Statement, IN, CHAR,
} = require('idb-pconnector');

async function pbeExample() {
  const connection = new Connection({ url: '*LOCAL' });
  const statement = new Statement(connection);
  const sql = 'SELECT * FROM QIWS.QCUSTCDT WHERE CITY = ? AND STATE = ?';

  await statement.prepare(sql);
  await statement.bindParameters(['Dallas','TX']);
  await statement.execute();
  
  let resultSet = await statement.fetchAll();
  
  console.log(resultSet) // array with response
}

pbeExample().catch((error) => {
  console.error(error);
});

DBPool

Using DBPool to return a connection then call a stored procedure:
const { DBPool } = require('idb-pconnector');

async function poolExample() {
  const pool = new DBPool();

  const connection = pool.attach();

  const statement = connection.getStatement();

  const sql = `CALL QSYS2.SET_PASE_SHELL_INFO('*CURRENT', '/QOpenSys/pkgs/bin/bash')`

  await statement.prepare(sql);
  await statement.execute();

  if (results) {
    console.log(`results:\n ${JSON.stringify(results)}`);
  }
  await pool.detach(connection);
}

poolExample().catch((error) => {
  console.error(error);
});

prepareExecute

Using prepareExecute method to insert data:
const { DBPool } = require('idb-pconnector');

async function prepareExecuteExample() {
  /*
   * Prepare and execute an SQL statement.
   * Params are passed as an array values.
   * The order of the params indexed in the array
   * should map to the order of the parameter markers
   */
  const pool = new DBPool();

  const sql = 'INSERT INTO QIWS.QCUSTCDT VALUES (?,?,?,?,?,?,?,?,?,?,?) with NONE';

  const params = [4949, 'Johnson', 'T J', '452 Broadway', 'MSP', 'MN',
    9810, 2000, 1, 250, 0.00];

  await pool.prepareExecute(sql, params);
}

prepareExecuteExample().catch((error) => {
  console.error(error);
});

runSql

Using runSql method to directly execute a select statement:
NOTE
This method will not work with stored procedures use prepareExecute() instead.
const { DBPool } = require('idb-pconnector');

async function runSqlExample() {
  /*
   * Directly execute a statement by providing the SQL to the runSql() function.
   */
  const pool = new DBPool();

  const results = await pool.runSql('SELECT * FROM QIWS.QCUSTCDT');

  if (results) {
    console.log(`results:\n ${JSON.stringify(results)}`);
  }
}

runSqlExample().catch((error) => {
  console.error(error);
});

setLibraryList

Change to system naming and set the library list (using CHGLIBL) of the connection.
const { Connection } = require('idb-pconnector');

async function setLibListExample() {
  const connection = new Connection({ url: '*LOCAL' });

  await connection.setLibraryList(['QIWS', 'QXMLSERV']);

  const statement = connection.getStatement();
  const results = await statement.exec('SELECT * FROM QCUSTCDT');
  console.log(`results:\n ${JSON.stringify(results)}`);
  await statement.close();
}

setLibListExample().catch((error) => {
  console.error(error);
});
Documentation
Please read the docs.
License
MIT
Contributing
Please read the contribution guidelines.