Huge News!Announcing our $40M Series B led by Abstract Ventures.Learn More →

idb-pconnector

Package Overview

Dependencies

Advanced tools

Install Socket

Detect and block malicious and high-risk dependencies

Install

idb-pconnector

Promised-based Db2 Connector for IBM i (production-ready as a "technology preview")

0.1.1
Source
npm

Version published: 6 years ago

Weekly downloads: 89; decreased by-36.43%

Maintainers: 4

Weekly downloads

Created: 7 years ago

Source

idb-pconnector

Promised-based Db2 Connector for IBM i (production-ready as a "technology preview") The objective of this project is to provide a database connector for Db2 on i that enables usage of the "await" keyword. As Well As Providing Connection Pooling Support. The DBPool has integrated aggregates that make it easier to Prepare & Execute, Prepare, Bind, & Execute, and Quickly Execute SQL.

Examples

Simple example of using a prepared statement to insert some values into a table, then querying all contents of that table:

const idbp = require('idb-pconnector');

async function runInsertAndSelect() {
   try {
       let statement =  new idbp.Connection().connect().getStatement();
       
       await statement.prepare('INSERT INTO MYSCHEMA.TABLE VALUES (?,?)');

       await statement.bind([ [2018, idbp.SQL_PARAM_INPUT, idbp.SQL_BIND_NUMERIC],
                              ['Dog', idbp.SQL_PARAM_INPUT, idbp.SQL_BIND_CHAR] 
                            ]);
       await statement.execute();

       let result = await statement.exec('SELECT * FROM MYSCHEMA.TABLE');
       console.log(`Select results: \n${JSON.stringify(result)}`);
   } catch(err) {
       console.log(`Error was: \n${err.stack}`);
   }
}

runInsertAndSelect();

Example Using DBPool to attach a connection , execute a stored procedure , and executing a simple select statement. Finally detach the connection.

const {DBPool} = require('idb-pconnector');
//set the debug to true to view verbose output call
const pool = new DBPool({}, {debug: true});
//remember to use await you must wrap within async Function.
async function poolExample(){
 //attach() returns an available connection from the pool.
 let connection = pool.attach(),
   statement = connection.getStatement(),
   results = null;

 try {
   await statement.prepare("CALL QIWS.GET_MEMBERS('QIWS','QCUSTCDT')");
   await statement.execute();
   results = await statement.fetchAll();

   if (results !== null){
     console.log(`\n\nResults: \n${JSON.stringify(results)}`);
   }
   //closes statements makes the Connection available for reuse.
   await pool.detach(connection);

 } catch (err){
   console.log(`Error was: \n\n${err.stack}`);
   pool.retire(connection);
 }
}

poolExample();

Example Using DBPool aggregates to Prepare & Execute , Prepare Bind Execute , and Execute a statement.

const {DBPool} = require('idb-pconnector');
//optional to set the debug to true to view verbose output call
const pool = new DBPool({}, {debug: true});
//remember to use await you must wrap within async Function.
async function aggregatesRun(){
 //Prepare and execute an SQL statement.
 try {
   console.log('\Prepare and Execute\n');
   let results = await pool.prepareExecute("CALL QIWS.GET_MEMBERS('QIWS','QCUSTCDT')");
    if (results !== null) {
     console.log(`\n\n${JSON.stringify(results)}\n\n`);
   }
   /*
   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(i.e. '?').
 */
   console.log('\nPrepare Bind & Execute\n');
   let sql = 'INSERT INTO QIWS.QCUSTCDT VALUES (?,?,?,?,?,?,?,?,?,?,?) with NONE',
     params = [4949, 'Johnson', 'T J', '452 Broadway', 'MSP', 'MN', 9810, 2000, 1, 250, 0.00],
     results2 = await pool.prepareExecute(sql, params);

   if (results2 !== null){
     console.log(`\n\n${JSON.stringify(results2)}\n\n`);
   }
   /*
   Quickly execute a statement by providing the SQL to the runSql() function
   NOTE: Stored Procedures should use the prepareExecute() method instead.
 */
   console.log('\nRun a Query\n');
   let results3 = await pool.runSql(`SELECT * FROM QIWS.QCUSTCDT WHERE CUSNUM = ${params[0]}`);
    if (results3 !== null) {
     console.log(`\n${JSON.stringify(results3)}`);
   }
   console.log('\nDone');

 } catch (err){
   console.log(`Error was: ${err.stack}`);
 }
}
aggregatesRun();

API

Class: Connection

Connection.connect(dbname)

Establishes a Connection to the database.

Parameters

dbname: string, the name of the database to connect to. If a name is not specified, the dbname is defaulted to "*LOCAL".

Returns: object, - the dbConn Object with an established connection.

Connection.getStatement()

returns a Statement Object initialized to the current dbConn Connection.

Returns: object, - a new Statement initialized with the current dbconn.

Connection.close()

closes the Connection to the DB and frees the connection object.

Returns: Promise, - Promise object represents the closure of the Connection.

Connection.disconn()

disconnects an existing connection to the database.

Returns: Promise, - Promise object represents the disconnect of the Connection.

Connection.debug(choice)

prints more detailed info if choice = true. Turned off by setting choice = false.

Parameters

choice: boolean, the option either true or false to turn on debugging.

Returns: Promise, - Promise object represents the debug method being set to the choice specified.

Connection.getConnAttr(attribute)

if connection attribute exists should return type String or Int depending on the attribute type

Parameters

attribute: number, if connection attribute exists should return type String or Int depending on the attribute type

Returns: Promise, - Promise object represents the the current settings for the specified connection attribute.

Link: Further Documentation ON Connection Attributes

Connection.setConnAttr(attribute, value)

Sets the ConnAttr , Attribute should be INT.

Parameters

attribute: number, the attribute to be set refer to the getConAttr example to view available attributes.

value: string | number, the value to set the attribute to. Can be String or Int depending the attribute.

Returns: Promise, - Promise object represents the execution of the setConnAttr().

Connection.validStmt(sql)

Checks if the given SQL is valid and interprets vendor escape clauses.

Parameters

sql: string, the sql string to be validated.

Returns: Promise, - Promise object represents the transformed SQL string that is seen by the data source.

Class: Statement

Statement.bindParam(params)

associates parameter markers in an SQL statement to app variables.

Parameters

params: Array, this should be an Array of the parameter list. Each parameter element will also be an Array with 3 values ( Value, In/out Type ,Indicator ).

Returns: Promise, - Promise object represents the execution of bindParam().

Example:

statement.bindParam([
       [2099, dba.SQL_PARAM_INPUT, dba.SQL_BIND_NUMERIC],
       ['Node.Js', dba.SQL_PARAM_INPUT,dba.SQL_BIND_CHAR]
        ]);
        
       IN/OUT TYPE CAN BE:
          - SQL_PARAM_INPUT
          - SQL_PARAM_OUTPUT
          - SQL_PARAM_INPUT_OUTPUT
          
       INDICATORS CAN BE:
           - SQL_BIND_CHAR
           - SQL_BIND_INT
           - SQL_BIND_NUMERIC
           - SQL_BIND_BINARY
           - SQL_BIND_BLOB
           - SQL_BIND_CLOB
           - SQL_BIND_BOOLEAN
           - SQL_BIND_NULL_DATA

Statement.bind(params)

Shorthand for bindParam

Parameters

params: Array, this should be an Array of the parameter list. Each parameter element will also be an Array with 3 values ( Value, In/Out Type ,Indicator ).

Statement.close()

Ends and frees the statement object.

Returns: Promise, - Promise object represents the execution of close().

Statement.closeCursor()

closes the cursor associated with the dbstmt object and discards any pending results.

Returns: Promise, - Promise object represents the execution of closeCursor().

Statement.commit()

adds all changes to the database that have been made on the connection since connect time.

Returns: Promise, - Promise object represents the execution of Commit().

Statement.exec(sqlString)

performs action of given SQL String. The exec() method does not work with stored procedure calls use execute() instead.

Parameters

sqlString: string, performs action of given SQL String. The exec() method does not work with stored procedure calls use execute() instead.

Returns: , the result set as an array. Returns: Promise, - Promise object represents the result set from the execution of exec().

Statement.execute()

Runs a statement that was successfully prepared using prepare(). Use execute() for stored procedure calls.

Returns: Promise, - Promise object represents the execution of execute().

Example:

- Calling a stored Procedure that returns a result set with execute() & displaying the result set.
       
  const idbp = require('idb-pconnector');

  async function storedProcedure(){

    try {
      // note that that calling the new Statement() without the DbConn as a parameter
      // creates a new connection implicitly and uses that for the Statement.
      let statement = new idbp.Statement(),
        sql = 'CALL MYSCHEMA.SAMPLEPROC';
      
    	await statement.prepare(sql);
      await statement.execute();
      
      //if a result set exists you can fetch it
    	let result = await statement.fetchAll();
    	console.log(`Result is\n: ${JSON.stringify(result)}`);
    } catch(error){
    	console.log(error.stack);
      }
  }

  storedProcedure();
    
- Insert Example With Prepare , Binding Parameter , and Execution
	
  const idbp = require('idb-pconnector');
  
  async function prepareBindExecute(){
    try {
      let statement = new idbp.Statement(),
        sql = 'INSERT INTO MYSCHEMA.MYTABLE VALUES (?,?)';

      await statement.prepare(sql);
      //binding a number and null data types
      await statement.bind([ [2018,idbp.SQL_PARAM_INPUT,idbp.SQL_BIND_NUMERIC], [null ,idbp.PARM_TYPE_INPUT, idbp.SQL_BIND_NULL_DATA ] ]);
      await statement.execute();

      let result = await statement.exec('SELECT * FROM MYSCHEMA.MYTABLE');
      console.log(`Select results: \nJSON.stringify(result)`);
    } catch(error){
    	console.log(error.stack);
      }
  }

  prepareBindExecute();

Statement.fetch()

if a result exists , retrieves a row from the result set

Returns: Promise, - Promise object represents the row that was retrieved from the execution of fetch(). If there is no data to be fetched null will be returned indicating the end of the result set.


- Example Fetching a result set until there is no more data to fetch.
const idbp = require('idb-pconnector');

async function fetch(){
  try {
    let sql = 'SELECT * FROM QIWS.QCUSTCDT',
    connection = new idbp.Connection();

    connection.debug(true);
    let statement = connection.connect().getStatement();

    await statement.prepare(sql);
    await statement.execute();
    let result = await statement.fetch();

    while (result !== null ){
      console.log(`Fetch result:\n ${JSON.stringify(result)}`);
      result = await statement.fetch();
    }
  } catch(error){
    	console.log(error.stack);
      }
}

fetch();

Statement.fetchAll()

if a result set exists , retrieves all the rows of data from the result set.

Returns: Promise, - Promise object represents the the an array containing the result that was retrieved from the execution of fetchAll().

Statement.fieldName(index)

requires an int index parameter. If a valid index is provided, returns the name of the indicated field.

Parameters