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

idb-pconnector

Package Overview
Dependencies
Maintainers
4
Versions
21
Alerts
File Explorer

Advanced tools

License
Socket logo

Install Socket

Detect and block malicious and high-risk dependencies

Install

idb-pconnector - npm Package Compare versions

Comparing version 1.0.0 to 1.0.1

AUTHORS.md
docs/README.md

214

lib/dbPool.js

@@ -69,3 +69,4 @@ const idbp = require('./idb-pconnector');

async newStatement() {
let me = this;
let me = this,
reason;

@@ -76,3 +77,5 @@ if (me.statement) {

} catch (error) {
return 'Statement failed to close. New Statement could not be created.';
reason = new Error('Statement failed to close. New Statement could not be created.');
reason.stack += `\nCaused By:\n ${error.stack}`;
throw reason;
}

@@ -96,3 +99,4 @@ }

async close() {
let me = this;
let me = this,
reason;

@@ -103,3 +107,5 @@ try {

} catch (error) {
return error;
reason = new Error('Connection failed to close');
reason.stack += `\nCaused By:\n ${error.stack}`;
throw reason;
}

@@ -111,3 +117,3 @@

/**
* Creates a new statement
* Cleans up the old statement and gets a new one, if retiring closes the connection.
* @param {boolean} retire If true, retires the connection so it can be removed from the pool

@@ -117,3 +123,4 @@ * @returns {boolean | object} True if retiring, or the detached connection

async detach(retire = false) {
let me = this;
let me = this,
reason;

@@ -128,3 +135,5 @@ try {

} catch (error) {
return `Connection failed to ${retire ? 'retire' : 'detach'}. Connection will be retired until the process exits.`;
reason = new Error(`DBPoolConnection: failed to ${retire ? 'retire' : 'detach'}.`);
reason.stack += `\nCaused By:\n ${error.stack}`;
throw reason;
}

@@ -220,3 +229,4 @@

connections = me.connections,
connection = {};
connection = {},
reason;

@@ -227,3 +237,5 @@ for (connection of connections) {

} catch (error) {
return `Error detaching all connections.\n${error}`;
reason = new Error('DBPool: Failed to detachAll()');
reason.stack += `\nCaused By:\n ${error.stack}`;
throw reason;
}

@@ -241,3 +253,4 @@ }

let me = this,
connections = me.connections;
connections = me.connections,
reason;

@@ -250,4 +263,6 @@ try {

}
} catch (err){
return `Error detaching all connections.\n${error}`;
} catch (error){
reason = new Error('DBPool: Failed to retireAll()');
reason.stack += `\nCaused By:\n ${error.stack}`;
throw reason;
}

@@ -262,6 +277,8 @@ return true;

* @param {DBPoolConnection} connection
* @returns {boolean} - true if detached succesfully
*/
async detach(connection) {
let me = this,
index = connection.poolIndex;
index = connection.poolIndex,
reason;

@@ -272,5 +289,8 @@ me.log(`Detaching Connection ${index}...`);

} catch (error) {
return error;
reason = new Error('DBPool: Failed to detach()');
reason.stack += `\nCaused By:\n ${error.stack}`;
throw reason;
}
me.log(`Connection ${index} detached`);
return true;
}

@@ -285,3 +305,4 @@

connections = me.connections,
index = connection.poolIndex;
index = connection.poolIndex,
reason;

@@ -295,3 +316,5 @@ me.log(`Retiring Connection ${index}...`);

} catch (error) {
return error;
reason = new Error(`DBPool: Failed to retire() Connection #${poolIndex}`);
reason.stack += `\nCaused By:\n ${error.stack}`;
throw reason;
}

@@ -360,19 +383,25 @@ me.log(`Connection ${index} retired`);

statement = connection.getStatement(),
resultSet = [];
resultSet = [],
reason;
try {
me.log(`Executing SQL...\nSQL Statement: ${sql}`);
resultSet = await statement.exec(sql);
me.log(`Executed SQL`);
} catch (err) {
me.log(`SQL Error: ${err}`);
await me.retire(connection);
}
try {
await me.detach(connection);
} catch (error) {
await me.retire(connection);
console.log(`Connection Error: ${error}`);
}
if (!resultSet.length){
me.log(`Executing SQL...\nSQL Statement: ${sql}`);
resultSet = await statement.exec(sql)
.catch(async (error) => {
reason = new Error('runSql: Failed to exec()');
reason.stack += `\nCaused By:\n ${error.stack}`;
await me.detach(connection);
throw reason;
});
me.log(`Executed SQL`);
await connection.detach()
.catch(async (error) => {
reason = new Error('runSql: Failed to detach() the connection');
reason.stack += `\nCaused By:\n ${error.stack}`;
await me.retire(connection);
throw reason;
});
if (!resultSet){
me.log('SQL did not return a result set.');

@@ -399,40 +428,65 @@ return null;

statement = connection.getStatement(),
resultSet = [],
outParams = [],
data = {};
resultSet = null,
outParams = null,
data = {},
reason;
try {
me.log(`Preparing Statement...`);
await statement.prepare(sql);
if (params.length > 0){
me.log(`Binding Parameters...`);
await statement.bindParam(me.setupParams(params, options));
}
outParams = await statement.execute();
} catch (err) {
me.log(` SQL Error: ${err}`);
me.retire(connection);
me.log(`Preparing Statement...`);
await statement.prepare(sql)
.catch(async (error) => {
reason = new Error('PE: Failed to prepare() the Statement');
reason.stack += `\nCaused By:\n ${error.stack}`;
await me.detach(connection);
throw reason;
});
if (params.length > 0){
me.log(`Binding Parameters...`);
await statement.bindParam(me.setupParams(params, options))
.catch(async (error) => {
reason = new Error('PE: Failed to bindParam()');
reason.stack += `\nCaused By:\n ${error.stack}`;
await me.detach(connection);
throw reason;
});
}
try {
me.log(`Fetching Result Set...`);
resultSet = await statement.fetchAll();
me.log(`Fetched Result Set`);
} catch (err){
//tried to get the results no were results were available.
//this is normal behavior for some queries for example "INSERT" or "UPDATE"
me.log('SQL did not return a result set.');
// execute() returns an empty [] when no outParams availble, if bindParams was called first.
outParams = await statement.execute()
.catch(async (error) => {
reason = new Error('PE: Failed to execute() statement');
reason.stack += `\nCaused By:\n ${error.stack}`;
await me.detach(connection);
throw reason;
});
if (outParams !== null){
// set outParams to null if its an empty []
outParams.length === 0 ? outParams = null : outParams = outParams;
}
try {
await connection.detach();
} catch (err){
me.log(`Connection Err:\n ${err}`);
await me.retire(connection);
}
if (!resultSet.length && !outParams.length){
me.log(`Fetching Result Set...`);
resultSet = await statement.fetchAll()
.catch ( () => {
//tried to get the results no were results were available.
//this is normal behavior for some queries for example "INSERT" or "UPDATE"
me.log('SQL did not return a result set.');
});
await connection.detach()
.catch(async (error) => {
reason = new Error('PE: Failed to detach() the connection');
reason.stack += `\nCaused By:\n ${error.stack}`;
await me.retire(connection);
throw reason;
});
// no resultSet or outParams to return
if (!resultSet && !outParams){
return null;
}
if (resultSet.length){
// return resultSet if its not null and length is not 0
if (resultSet && resultSet.length){
data.resultSet = resultSet;
}
if (outParams.length){
// return outParams if its not null
if (outParams){
data.outputParams = outParams;

@@ -460,7 +514,7 @@ }

for ( parameter of params ){
if (typeof parameter === 'object'){
if (parameter instanceof Object){
let {value, asClob = false} = parameter;
if (typeof value === 'undefined' ){
throw Error('The parameter object must define a value property');
if (!value){
throw new Error('The parameter object must define a value property');
}

@@ -488,21 +542,21 @@ // assigning the value of io from the parameter object to the io variable declared before.

io = idbp[`SQL_PARAM_${io === 'both' ? 'INPUT_OUTPUT': (io.toUpperCase() + 'PUT')}`];
io = idbp[`${io === 'both' ? 'INOUT': io.toUpperCase()}`];
if (typeof value === 'string'){ //String
if (asClob){ //as clob
boundParams.push([value, io, idbp.BIND_CLOB]);
boundParams.push([value, io, idbp.CLOB]);
} else { // as string
boundParams.push([value, io, idbp.BIND_STRING]);
boundParams.push([value, io, idbp.CHAR]);
}
} else if (typeof value === 'number') { //Number
boundParams.push([value, io, idbp[`SQL_BIND_${Number.isInteger(value) ? 'INT' : 'NUMERIC'}`]]);
boundParams.push([value, io, idbp[`${Number.isInteger(value) ? 'INT' : 'NUMERIC'}`]]);
} else if (value === null) { //Null
boundParams.push([value, io, idbp.SQL_BIND_NULL_DATA]);
boundParams.push([value, io, idbp.NULL]);
} else if (Buffer.isBuffer(value)){ //Binary/blob
boundParams.push([value, io, idbp.SQL_BIND_BINARY]);
boundParams.push([value, io, idbp.BINARY]);
} else if (typeof value === 'boolean'){ //Boolean
boundParams.push([value, io, idbp.SQL_BIND_BOOLEAN]);
boundParams.push([value, io, idbp.BOOLEAN]);
} else {
me.log(`Parameter that caused error was ${JSON.stringify(value)}`);
throw TypeError('Parameters to bind should be String, Number, null, boolean or Buffer');
this.log(`Parameter that caused error was ${JSON.stringify(value)}`);
throw new TypeError('Parameters to bind should be String, Number, null, boolean or Buffer');
}

@@ -517,5 +571,5 @@ }

let me = this;
if (!connectionAttribute instanceof Object){
throw Error('Connection Attribute should be an Object');
//an object was not passed
if ( !(connectionAttribute instanceof Object) ){
throw new Error('Connection Attribute should be an Object');
}

@@ -533,7 +587,7 @@

.catch( () => {
throw Error('Setting Connection Attributes failed, check your parameters');
throw new Error('Setting Connection Attributes failed, check your parameters');
});
}
} else {
throw Error('Provide a valid Connection Attribute and Value');
throw new Error('Provide a valid Connection Attribute and Value');
}

@@ -540,0 +594,0 @@ }

166

lib/idb-pconnector.js

@@ -49,3 +49,3 @@ const idb = require('idb-connector');

getStatement() {
return new Statement(this.dbconn);
return new Statement(this);
}

@@ -109,35 +109,2 @@

* @returns {Promise} - Promise object represents the the current settings for the specified connection attribute.
* @example Available Set & Get Connection Attributes:
* SQL_ATTR_2ND_LEVEL_TEXT
* SQL_ATTR_AUTOCOMMIT
* SQL_ATTR_CONCURRENT_ACCESS_RESOLUTION
* SQL_ATTR_CONN_SORT_SEQUENCE
* SQL_ATTR_COMMIT
* SQL_ATTR_DBC_DEFAULT_LIB
* SQL_ATTR_DECFLOAT_ROUNDING_MODE
* SQL_ATTR_DECIMAL_SEP
* SQL_ATTR_EXTENDED_COL_INFO
* SQL_ATTR_EXTENDED_INDICATORS
* SQL_ATTR_FREE_LOCATORS
* SQL_ATTR_HEX_LITERALS
* SQL_ATTR_INFO_ACCTSTR
* SQL_ATTR_INFO_APPLNAME
* SQL_ATTR_INFO_PROGRAMID
* SQL_ATTR_INFO_USERID
* SQL_ATTR_INFO_WRKSTNNAME
* SQL_ATTR_MAX_PRECISION
* SQL_ATTR_MAX_SCALE
* SQL_ATTR_MIN_DIVIDE_SCALE
* SQL_ATTR_OLD_MTADTA_BEHAVIOR
* SQL_ATTR_NULLT_ARRAY_RESULTS
* SQL_ATTR_NULLT_OUTPUT_PARMS
* SQL_ATTR_QUERY_OPTIMIZE_GOAL
* SQL_ATTR_SAVEPOINT_NAME
* SQL_ATTR_TIME_FMT
* SQL_ATTR_TIME_SEP
* SQL_ATTR_TIMESTAMP_PREC
* SQL_ATTR_TXN_EXTERNAL
* SQL_ATTR_TXN_INFO
* SQL_ATTR_UCS2
*
* Further Documentation {@link https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/cli/rzadpfnsconx.htm HERE}

@@ -205,9 +172,9 @@ * @memberof Connection

* @constructor
* @param {Object} [dbconn] - A Connection Object to initialize the Statement. If a connection is not provided one will be initialized for the statement.
* @param {Object} [connection] - A Connection Object to initialize the Statement. If a connection is not provided one will be initialized for the statement.
*/
class Statement {
constructor(dbconn = new Connection().connect().dbconn) {
constructor(connection = new Connection().connect()) {
let me = this;
me.dbc = dbconn;
me.dbc = connection.dbconn;
me.stmt = new idb.dbstmt(me.dbc);

@@ -219,19 +186,2 @@ }

* @param {Array} params - An Array of the parameter list. Each parameter element will also be an Array with 3 values (Value, In/out Type, Indicator).
* @example dbStmt.bindParam([
* [2099, idb.SQL_PARAM_INPUT, idb.SQL_BIND_NUMERIC],
* ['Node.Js', idb.SQL_PARAM_INPUT,idb.SQL_BIND_CHAR]
* ]);
* IN/OUT TYPE CAN BE:
* - SQL_PARAM_INPUT
* - SQL_PARAM_OUTPUT
* - SQL_PARAM_INPUT_OUTPUT
* INDICATORS CAN BE:
* - SQL_BIND_CLOB
* - SQL_BIND_CHAR
* - SQL_BIND_INT
* - SQL_BIND_NULL_DATA
* - SQL_BIND_NUMERIC
* - SQL_BIND_BOOLEAN
* - SQL_BIND_BINARY
* - SQL_BIND_BLOB
* @returns {Promise} - Promise object represents the execution of bindParam().

@@ -246,3 +196,3 @@ * @memberof Statement

if (error) {
reject(error);
reject(new Error(error.message));
} else {

@@ -328,3 +278,3 @@ resolve(result);

if (error) {
reject(error);
reject(new Error(error.message));
} else {

@@ -340,42 +290,2 @@ resolve(result);

* Use execute() for stored procedure calls.
* @example
* - Calling a stored Procedure that returns a result set with execute() & displaying the result set.
const idb = require('idb-pconnector');
try {
// note that that calling the new Statement() without the DbConn as a parameter
// creates a new connection automatically and uses that for the Statement.
let dbStmt = new idb.Statement();,
sql = 'call QIWS.sampleProc',
response;
await dbStmt.prepare(sql);
await dbStmt.execute();
response = await dbStmt.fetchAll();
console.log(`Result is\n: ${JSON.stringify(response)}`);
} catch(error){
console.log(error.stack);
}
* @example
* - Insert Example With Prepare , Binding Parameter , and Execution
const idb = require('idb-pconnector');
try {
// note that that calling the new Statement() without the DbConn as a parameter
// creates a new connection automatically and uses that for the Statement.
let dbStmt = new idb.Statement(),
response;
await dbStmt.prepare('INSERT INTO AMUSSE.TABLE1 VALUES (?,?)');
await dbStmt.bind([
[2018, idb.SQL_PARAM_INPUT, idb.SQL_BIND_NUMERIC],
[null, idb.PARM_TYPE_INPUT, idb.SQL_BIND_NULL_DATA]
]);
await dbStmt.execute();
response = await dbStmt.exec('SELECT * FROM AMUSSE.TABLE1');
console.log(`Select results: ${JSON.stringify(response)}`);
} catch (error) {
console.log(error.stack);
}
* @returns {Promise} - Promise object represents the execution of execute().

@@ -390,3 +300,3 @@ * @memberof Statement

if (error) {
reject(error);
reject(new Error(error.message));
} else {

@@ -415,3 +325,3 @@ resolve(result);

}
reject('Error');
reject(new Error('Unable to fetch result'));
});

@@ -432,3 +342,3 @@ });

if (error) {
reject(error);
reject(new Error(error.message));
} else {

@@ -537,23 +447,2 @@ resolve(result);

* @memberof Statement
* @example Available Set & Get Statement Attributes
* SQL_ATTR_APP_PARAM_DESC
* SQL_ATTR_APP_ROW_DESC
* SQL_ATTR_BIND_TYPE
* SQL_ATTR_CURSOR_HOLD
* SQL_ATTR_CURSOR_SCROLLABLE
* SQL_ATTR_CURSOR_SENSITIVITY
* SQL_ATTR_CURSOR_TYPE
* SQL_ATTR_EXTENDED_COL_INFO
* SQL_ATTR_FOR_FETCH_ONLY
* SQL_ATTR_FULL_OPEN
* SQL_ATTR_NUMBER_RESULTSET_ROWS_PTR
* SQL_ATTR_PARAM_BIND_TYPE
* SQL_ATTR_PARAM_STATUS_PTR
* SQL_ATTR_PARAMS_PROCESSED_PTR
* SQL_ATTR_PARAMSET_SIZE
* SQL_ATTR_ROW_BIND_TYPE
* SQL_ATTR_ROW_STATUS_PTR
* SQL_ATTR_ROWS_FETCHED_PTR
* SQL_ATTR_ROWSET_SIZE
*
* Further Documentation {@link https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/cli/rzadpfnsstma.htm HERE}

@@ -637,3 +526,3 @@ */

if (error) {
reject(error);
reject(new Error(error.message));
} else {

@@ -701,7 +590,7 @@ resolve(result);

if (index < 1) {
reject('Index must be at least 1.');
reject(new Error('Index must be at least 1.'));
} else {
stmt.stmtError(hType, index, (result, error) => {
if (error) {
reject(error);
reject(new Error(error.message));
} else {

@@ -720,22 +609,15 @@ resolve(result);

exports.DBPool = require('./dbPool.js').DBPool;
exports.SQL_BIND_CLOB = 0;
exports.SQL_BIND_CHAR = 1;
exports.SQL_BIND_INT = 2;
exports.SQL_BIND_NULL_DATA = 3;
exports.SQL_BIND_NUMERIC = 4;
exports.SQL_BIND_BOOLEAN = 5;
exports.SQL_BIND_BINARY = idb.SQL_BINARY;
exports.SQL_BIND_BLOB = idb.SQL_BLOB;
//alias variables
exports.BIND_CLOB = idb.BIND_CLOB;
exports.BIND_STRING = idb.BIND_STRING;
exports.BIND_INT = idb.BIND_INT;
exports.BIND_NULL = idb.BIND_NULL;
exports.BIND_NUMERIC = idb.BIND_NUMERIC;
exports.BIND_BOOLEAN = idb.BIND_BOOLEAN;
exports.BIND_BINARY = idb.BIND_BINARY;
exports.BIND_BLOB = idb.BIND_BLOB;
exports.PARAM_INPUT = idb.SQL_PARAM_INPUT;
exports.PARAM_OUTPUT = idb.SQL_PARAM_OUTPUT;
exports.PARAM_INPUT_OUTPUT = idb.SQL_PARAM_INPUT_OUTPUT;
exports.CLOB = idb.BIND_CLOB;
exports.STRING = idb.BIND_STRING;
exports.CHAR = idb.BIND_STRING;
exports.INT = idb.BIND_INT;
exports.NULL = idb.BIND_NULL;
exports.NUMERIC = idb.BIND_NUMERIC;
exports.BOOLEAN = idb.BIND_BOOLEAN;
exports.BINARY = idb.BIND_BINARY;
exports.BLOB = idb.BIND_BLOB;
exports.IN = idb.SQL_PARAM_INPUT;
exports.OUT = idb.SQL_PARAM_OUTPUT;
exports.INOUT = idb.SQL_PARAM_INPUT_OUTPUT;

@@ -742,0 +624,0 @@ // export variables from original idb-connector

11

package.json
{
"name": "idb-pconnector",
"version": "1.0.0",
"version": "1.0.1",
"description": "Promised-based Db2 Connector for IBM i (production-ready as a \"technology preview\")",

@@ -20,13 +20,6 @@ "main": "lib/idb-pconnector.js",

"author": "Jesse Gorzinski",
"contributors": [
{
"name": "Abdirahim Musse",
"name": "Brian Jerome",
"name": "Danny Roessner"
}
],
"license": "MIT",
"homepage": "https://bitbucket.org/litmis/nodejs-idb-pconnector#readme",
"dependencies": {
"idb-connector": "^1.1.2"
"idb-connector": "^1.1.4"
},

@@ -33,0 +26,0 @@ "devDependencies": {

533

README.md

@@ -1,17 +0,23 @@

# **idb-pconnector - promised based DB2 Connector for IBM i**
# **idb-pconnector - Promise based DB2 Connector for IBM i**
**Project Status**: (production-ready as a "technology preview")
The objective of this project is to provide a promise based database connector API for DB2 on IBM i.
The objective of this project is to provide a promise based database connector API for DB2 on IBM i.
This project is a wrapper over the [`idb-connector`](https://bitbucket.org/litmis/nodejs-idb-connector) project but returning promises instead of using callbacks.
Connection Pooling is supported giving you better control.
The `DBPool` class includes integrated aggregates (prepareExecute, runSql), which make it easier to Prepare & Execute & directly Execute SQL. When using the aggregates opening and closing of statements will be handled by the pool.
Using Node version ^8.X.X you can take advantage of async & await to simplifying your code.
Remember to use the `await` keyword your code must be wrapped within an `async function`.
Connection Pooling is supported by using the `DBPool` class giving you better control.
Please refer to the documentation below for installation and usage of the `idb-pconnector`.
[TOC]
The `DBPool` class includes integrated aggregates (prepareExecute, runSql), which make it easier to Prepare & Execute & directly Execute SQL.
Using Node version ^8.X.X you can take advantage of `async` & `await` keywords when working with `promises`.
***NOTE***: to use the `await` keyword your code must be wrapped within an `async function`.
# **Install**

@@ -24,2 +30,4 @@ This project is a Node.js module available through npm (node package manager).

***NOTE***: `idb-pconnector` currently only supports IBM i installation
# **Examples**

@@ -55,3 +63,3 @@

const idbp = require('idb-pconnector');
const Connection = idbp.Connection;
const {Connection} = idbp;

@@ -64,5 +72,5 @@ async function pbeExample() {

await statement.bind([
[2018, idbp.PARAM_INPUT, idbp.BIND_INT],
['example', idbp.PARAM_INPUT, idbp.BIND_STRING]
await statement.bindParam([
[2018, idbp.IN, idbp.INT],
['example', idbp.IN, idbp.CHAR]
]);

@@ -117,4 +125,6 @@ await statement.execute();

Example Using DBPool prepareExecute(sql,params,options) method to Prepare and Execute a statement.
If you want to bind variables pass an array of values as the second parameter.
```javascript

@@ -151,4 +161,6 @@ const {DBPool} = require('idb-pconnector');

Example Using DBPool runSql(sql) method to directly run an sql statement.
NOTE: This method will not work with stored procedures use prepareExecute() instead.
***NOTE***: This method will not work with stored procedures use prepareExecute() instead.
```javascript

@@ -182,497 +194,10 @@ const {DBPool} = require('idb-pconnector');

# Class: Connection
Please refer to the [documentation](https://bitbucket.org/litmis/nodejs-idb-pconnector/src/master/docs/README.md) for usage of the `idb-pconnector`.
## Constructor: Connection()
The Connection constructor accepts an optional `db` parameter which can be used to connect to the database. If `db` is not provided make sure to use the `connect()` before performing any other methods.
**Parameters**:
- **db**: `Object` includes the properties `url` location of the database, use '*LOCAL' for a local database, `username` for the database user, `password` for the databse user. If connecting using '*LOCAL' it is not required to pass the `username` & `password` but ensure that the the object contains `url: '*LOCAL'`.
## Connection.connect(url, username, password)
Establishes a Connection to the database.
**Parameters**:
- **url**: `String` the url of the database to connect to. If a url is not specified, it defaults to "*LOCAL".
- **username**: `String` the username for the database user.
- **password**: `String` the password for the database user.
**Returns**: `Object` the Connection object with an established connection.
## Connection.getStatement()
Returns a Statement Object initialized to the current Connection. Ensure that the Connection object is connected first before attempting to get a Statement. The [isConnected](#markdown-header-connectionisconnected) method can be used to check if the Connection object is currently connected
**Returns**: `Object` a new Statement initialized with the current Connection.
## Connection.close()
Closes the Connection to the DB and frees the connection object.
**Returns**: `Promise` when resolved will return `true` indicating successful closure, or the promise will be rejected.
## Connection.disconn()
Disconnects an existing connection to the database.
**Returns**: `Promise` when resolved will return `true` indicating successful disconnection, or the promise will be rejected.
## Connection.debug(choice)
Prints verbose detailed info to the console if choice is set `true`. Can be turned off by setting choice = false.
**Parameters**:
- **choice**: `boolean` the option either true or false to turn debug on/off.
**Returns**: `Promise` when resolved will return `true | false` indicating the current state of debug, or the promise will be rejected.
## Connection.getConnAttr(attribute)
If the `attribute` exists will return the current value of the attribute.
**Parameters**:
- **attribute**: `Number` the attribute to retrieve the current value from.
**Returns**: `Promise` when resolved will return the specified connection attribute settings either `Number | String`, or the promise will be rejected.
**Link**:
[Connection Attributes](https://www.ibm.com/support/knowledgecenter/en/ssw\_ibm\_i_73/cli/rzadpfnsconx.htm)
## Connection.isConnected()
Checks if the Connection object is currentl connected to the database.
**Returns**: `true` or `false` indicating if the Connection object is currently connected.
## Connection.setConnAttr(attribute, value)
Sets the the value for a given `attribute`.
**Parameters**:
- **attribute**: `Number` the attribute to be set.
- **value**: `string | number` the value to set the attribute to.
**Returns**: `Promise` when resolved will return `true` indicating success or the promise will be rejected.
## 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` when resolved will return the transformed sql string that is seen by the data source, or the promise will be rejected.
# Class: Statement
## Constructor: Statement(connection)
**Parameters**:
- **connection**: optional `dbconn` Object for the connection to use. If you don't pass a `connection` one will be implicitly created and used for the statement.
## Statement.bindParam(params)
Associates parameter markers in an sql statement to application variables.
**Parameters**:
- **params**: `Array` the parameter list in order corresponding to the parameter markers. Each parameter element will also be an Array with 3 values ( value, in/out type ,indicator ).
```
IN/OUT TYPE CAN BE:
- SQL_PARAM_INPUT or PARAM_INPUT
- SQL_PARAM_OUTPUT or PARAM_OUTOUT
- SQL_PARAM_INPUT_OUTPUT or INPUT_OUTPUT
INDICATORS CAN BE:
- SQL_BIND_CHAR or BIND_STRING
- SQL_BIND_INT or BIND_INT
- SQL_BIND_NUMERIC or BIND_NUMERIC
- SQL_BIND_BINARY or BIND_BINARY
- SQL_BIND_BLOB or BIND_BINARY
- SQL_BIND_CLOB or BIND_CLOB
- SQL_BIND_BOOLEAN or BIND_BOOLEAN
- SQL_BIND_NULL_DATA or BIND_NULL
```
These values are constants which are attached to object returned when you `const idbp = require('idb-pconnector')`.
You can access said values like so : `idbp.PARAM_INPUT`
**Returns**: `Promise` when resolved there is no return value but if an error occurred the promise will be rejected.
**Example**: [Here](#markdown-header-prepare-bind-execute)
## Statement.bind(params)
Shorthand equivalent of bindParam(params) above.
## Statement.close()
Ends and frees the statement object.
**Returns**: `Promise` when resolved will return true indicating successful closure, or the promise will be rejected.
## Statement.closeCursor()
Closes the cursor associated with the Statement object and discards any pending results.
**Returns**: `Promise` when resolved will return true indicating successful closure, or the promise will be rejected.
## Statement.commit()
Adds all changes to the database that have been made on the connection since connect time.
**Returns**: `Promise` when resolved will return true indicating successful commit, or the promise will be rejected.
## Statement.exec(sql)
Directly executes a given sql String. The exec() method does not work with stored procedure use execute() method instead.
**Parameters**:
- **sql**: `String` the sql command to execute.
**Returns**: `Promise` when resolved if available will return the result set as an `Array` , or the promise will be rejected.
**Example**: [Here](#markdown-header-exec)
## Statement.execute()
Runs a statement that was successfully prepared using prepare(). Used to call stored procedure calls. Important to note that execute() will return output parameters and not a result set. If available you can retrieve the result set by either running fetch() or fetchAll().
**Returns**: `Promise` when resolved if available will return output parameters as an `Array`, or the promise will be rejected.
**Example**: [Here](#markdown-header-prepare-bind-execute)
## Statement.fetch()
If a result set exists, fetch() will retrieve a row from the result set. The row is an `Object`. Fetch can be continuously run until there is no data. If there is no data to be fetched null will be returned indicating the end of the result set.
**Returns**: `Promise` when resolved will return an `Object` representing the row that was retrieved. If there is no data remaining to be fetched in the result set `null` will be returned indicating the end of the result set. Or if there was never a result set to be fetched the promise will be rejected.
**Example Fetching a result set until there is no more data to fetch**:
```javascript
const {Connection} = require('idb-pconnector');
async function fetch(){
try {
let sql = 'SELECT * FROM QIWS.QCUSTCDT',
connection = new 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 , fetchAll() retrieves all the rows from the result set.
**Returns**: `Promise` when resolved will return an `Array` of `Objects` representing the result set if its available, or the promise will be rejected.
## Statement.fieldName(index)
If a valid index is provided, returns the name of the indicated field.
**Parameters**:
- **index**: `Number` the position of the field within the table. It is 0 based.
**Returns**: `Promise` when resolved will return `String` name of the field or the promise will be rejected.
## Statement.fieldNullable(index)
If a valid index is provided, returns `true | false` if the indicated field can be set to `null`.
**Parameters**:
- **index**: `Number` the position of the field within the table. It is 0 based.
**Returns**: `Promise` when resolved will return `true | false` or the promise will be rejected.
## Statement.fieldPrecise(index)
If a valid index is provided, returns the precision of the indicated field
**Parameters**:
- **index**: `Number` the position of the field within the table. It is 0 based.
**Returns**: `Promise` when resolved will return `Number` or the promise will be rejected.
## Statement.fieldScale(index)
If a valid index is provided, returns the scale of the indicated column.
**Parameters**:
- **index**: `Number` the position of the field within the table. It is 0 based.
**Returns**: `Promise` when resolved will return a `Number` or the promise will be rejected.
## Statement.fieldType(index)
If a valid index is provided, returns the data type of the indicated field.
**Parameters**:
- **index**: `Number` the position of the field within the table. It is 0 based.
**Returns**: `Promise` when resolved will return a `Number` or the promise will be rejected.
## Statement.fieldWidth(index)
If a valid index is provided, returns the field width of the indicated field
**Parameters**:
- **index**: `Number` the position of the field within the table. It is 0 based.
**Returns**: `Promise`, when resolved will return a `Number` or the promise will be rejected.
## Statement.getStmtAttr(attribute)
If a valid Statement attribute is provided , returns the current settings for the specified Statement attribute.
Refer to the list below for valid Statement Attributes.
**Parameters**:
- **attribute**: `Number`the attribute to retrieve the current value from.
**Returns**: `Promise`, when resolved will return the specified connection attribute settings as a `Number | String`, or the promise will be rejected.
**Link**: [Statement Attributes](https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/cli/rzadpfnsstma.htm)
## Statement.nextResult()
Determines whether there is more information available on the statement handle that has been associated with a stored procedure that is returning result sets.
After completely processing the first result set, the application can call nextResult() to determine if another result set is available. If the current result set has unfetched rows, nextResult() discards them by closing the cursor.
**Returns**: `Promise` when resolve `true` will be returned indicating there is another result set or `null` is returned indicating there was not another result set. If an error occurred while processing the promise is rejected.
## Statement.numFields()
If a result set is available , numFields() retrieves number of fields contained in the result set.
**Returns**: `Promise` when resolved `Number` is returned or the promise is rejected.
## Statement.numRows()
If a query was performed, retrieves the number of rows that were affected by a query.
**Returns**: `Promise` when resolved will return a `Number` or the promise is rejected.
## Statement.prepare(sql)
If valid sql is provided . prepares the sql and sends it to the DBMS, if the input sql Statement cannot be prepared error is thrown.
**Parameters**:
- **sql**: `String`, the SQL string to be prepared.
**Returns**: `Promise` when resolved no value is returned but if an error occurred the promise is rejected.
**Example**: [Here](#markdown-header-prepare-bind-execute)
## Statement.rollback()
Reverts changes to the database that have been made on the connection since connect time or the previous call to commit().
**Returns**: `Promise` when resolved `true` is returned or promise is rejected.
## Statement.setStmtAttr(attribute, value)
Sets the the value for a given attribute.
**Parameters**:
- **attribute**: `Number` the attribute to be set.
- **value**: `string | number` the value to set the attribute to.
- **Returns**: `Promise` when resolved will return `true` indicating success or the promise will be rejected..
## Statement.stmtError(hType, recno)
Returns the diagnostic information associated with the most recently called function for a particular statement, connection, or environment handler.
**Parameters**:
- **hType**: `Number`, indicates the handler type of diagnostic information.
- **recno**: `Number`, indicates which error should be retrieved. The first error record is number 1.
```
hType can be following values:
SQL_HANDLE_ENV: Retrieve the environment diagnostic information
SQL_HANDLE_DBC: Retrieve the connection diagnostic information
SQL_HANDLE_STMT: Retrieve the statement diagnostic information
```
**Returns**: `Promise` when resolved returns `String` or the promise is rejected.
# Class: DBPool
Manages a list of DBPoolConnection instances.
Constructor to instantiate a new instance of a DBPool class given the `database` and `config`.
## Constructor: DBPool(database , config)
**Parameters**:
- **database**: `object` includes the `url` defaults to "*LOCAL", `username`, and `password`. Username & password is optional when connecting to "*LOCAL".
- **config**: `object` with the properties: `incrementSize` and `debug`. IncrementSize is a integer `Number` that sets the desired size of the DBPool defaults to 8 connections. Debug is a `boolean` setting it to true will display verbose output to the console, defaults to false.
**Example**: [Here](#markdown-header-dbpool)
## DBPool.createConnection(index)
Instantiates a new instance of DBPoolConnection with an `index` and appends it to the pool.
Assumes the database of the pool when establishing the connection.
**Parameters**:
**index**: `Number` an optional identifier to id the connection for debug purposes.
## DBPool.detachAll()
Frees all connections in the pool (Sets "Available" back to true for all)
closes any statements and gets a new statement.
**Returns**: `true` if all were detached successfully or will return `String` error message if an error occurred.
## DBPool.retireAll()
Retires (Removes) all connections from being used again
**Returns**: `true` if all were retired successfully, or will return `String` error message if an error occurred.
## DBPool.detach(connection)
Frees a connection (Returns the connection "Available" back to true) closes any statements and gets a new statement.
**Parameters**:
- **connection**: `DBPoolConnection`, Frees a connection (Returns the connection "Available" back to true)
closes any statements and gets a new statement.
**Returns**: `String` error message if an error occurred.
## DBPool.retire(connection)
Retires a connection from being used and removes it from the pool.
**Parameters**:
- **connection**: `DBPoolConnection`, Retires a connection from being used and removes it from the pool
**Returns**: `String` error message if an error occurred.
## DBPool.attach()
Finds and returns the first available Connection.
**Returns**: `DBPoolConnection` connection from the `DBPool`.
## DBPool.runSql(sql)
An aggregate to run an sql statement, just provide the sql to run. Note that Stored Procedures should use the prepareExecute() aggregate instead.
**Parameters**:
- **sql**: `String`, the sql statement to execute.
**Returns**:
- `Array` if the sql returns a result set it is returned as an `Array` of `Objects`.
- If no result set is available `null` is returned. Caller should check if `null` is returned.
**Example**: [Here](#markdown-header-runsql)
## DBPool.prepareExecute(sql, params, options)
Aggregate to prepare, bind, and execute. Just provide the sql and the optional params as an array.
An `options` object can now be used for global configuration. This is used to set options on all the parameters within the `params` Array. Currently, the input output indicator `io` is the only available option to set. This will override the default which is `'both'`.
**Example**: `prepareExecute(sql, parrams, {io: 'in'})`
Also parameters can be customized at an individual level by passing an object within the parameter list.
The object format: `{value: "string | Number | boolean | null" , io: "in | out | both" , asClob: "true | false"}`
`value` is the only required property others will fallback to defaults.
If you want to bind a string as a clob you can add `asClob: true` property to the object.
**Example**: `{value: 'your string', asClob: true, io: 'in'}`
**Parameters**::
- **sql**: `string`, the sql to prepare , include parameter markers (?, ?, ...)
- **params**: `array`, an optional array of values to bind. order of the values in the array must match the order of the desired parameter marker in the sql string.
- **options**: `Object` with config options to set for all parameters. The format can be: `{io: 'in' | 'out' | 'both'}` where `io` can be set to either the `String` `'in'`, `'out'`, or `'both'`. Indicating that the parameter is an input, output, or inout parameter.
**Returns**: `Object` in the format: `{resultSet: [], outputParams: []}` if the Prepared SQL returns result set it is returned as an array of objects or if the Prepared SQL returns output parameters it is returned as an array of objects.
- If neither were available `null` will be returned indicating that there is no result set or output parameters. Caller should check if `null` is returned.
**Example**: [Here](#markdown-header-prepareexecute)
## DBPool.setConnectionAttribute(attribute)
Sets the connection attribute for each a Connection in the pool.
**Parameters**:
- **attribute**: `Object` in the format {attribute: Number (integer), value: Number (integer) | String}
# **License**
MIT. View [LICENSE](LICENSE)
MIT. View [LICENSE](https://bitbucket.org/litmis/nodejs-idb-pconnector/src/master/LICENSE)
# **Contributing**
If you would like to contribute please issue a pull request. No document signing is necessary for this project.
If you would like to contribute please append your **name** and **email** to the `AUTHORS.md` file along with your PR.
No document signing is necessary for this project.

53

test/dbpoolTest.js

@@ -119,15 +119,60 @@ /*

});
describe('runSql', async () => {
it('should execute sql and return result set as an array if available , or return null', async () => {
let sql = `INSERT INTO QIWS.QCUSTCDT VALUES (6754,'Smith','A S','323 Main','Test','CA',52501,3500,2,500.99,0.98) with NONE`,
results = await connPool.runSql(sql);
expect(results).to.be.null;
});
});
describe('prepare, bind, execute', async () => {
it('should prepare bind and execute , return output params if available or result set if available',
it('should prepare bind and execute , return output params & result',
async () => {
let cusNum = 938472,
results = await connPool.prepareExecute('SELECT * FROM QIWS.QCUSTCDT WHERE CUSNUM = ?', [cusNum]);
results = await connPool.prepareExecute('SELECT * FROM QIWS.QCUSTCDT WHERE CUSNUM = ?', [cusNum]),
{resultSet, outputParams} = results;
console.log(results);
expect(results).to.be.an('object');
expect(results.resultSet).to.be.an('array');
expect(resultSet).to.be.an('array');
expect(resultSet.length).to.be.gt(0);
expect(outputParams).to.be.an('array');
expect(outputParams.length).to.equal(1);
});
});
describe('prepare, bind, execute', async () => {
it('should prepare bind and execute , return null ',
async () => {
let sql = 'INSERT INTO QIWS.QCUSTCDT VALUES (?,?,?,?,?,?,?,?,?,?,?) with NONE',
params = [
5469, //CUSNUM
'David', //LASTNAME
'E D', //INITIAL
'456 enter', //ADDRESS
'Hill', //CITY
'SC', //STATE
54786, //ZIP
7000, //CREDIT LIMIT
2, // change
478.32, //BAL DUE
0.25 //CREDIT DUE
];
let results = await connPool.prepareExecute(sql, params, {io: 'in'});
expect(results).to.be.null;
});
});
describe('prepare, bind, execute', async () => {
it('should prepare and execute , retrun result set & not output parameters',
async () => {
let sql = 'SELECT * FROM QIWS.QCUSTCDT';
let results = await connPool.prepareExecute(sql);
expect(results.outputParams).to.be.undefined;
expect(results.resultSet).to.be.a('array');
expect(results.resultSet.length).to.be.gt(0);
});
});
describe('Set Connection Attribute for Pool', async () => {

@@ -134,0 +179,0 @@ it('should set a valid connection attribute for the pool.',

82

test/statementTest.js

@@ -10,3 +10,3 @@ /*

const idbp = require('../lib/idb-pconnector');
const {Connection} = idbp;
const {Connection, Statement} = idbp;
const util = require('util');

@@ -16,2 +16,26 @@

describe('statement constructor with connection parameter', () =>{
it('creates a new Statement object by passing a connection object', async () =>{
let connection = new Connection().connect(),
statement = new Statement(connection),
sql = 'SELECT * FROM QIWS.QCUSTCDT';
let result = await statement.exec(sql);
expect(result).to.be.a('array');
expect(result.length).to.be.gt(0);
});
});
describe('statement constructor without connection parameter', () =>{
it('creates a new Statement object connected to *LOCAL by default', async () =>{
let statement = new Statement(),
sql = 'SELECT * FROM QIWS.QCUSTCDT';
let result = await statement.exec(sql);
expect(result).to.be.a('array');
expect(result.length).to.be.gt(0);
});
});
describe('prepare', () => {

@@ -25,3 +49,2 @@ it('Prepares valid SQL and sends it to the DBMS, if the input SQL Statement cannot be prepared error is returned. ', async () =>{

let result = await dbStmt.prepare(sql);
console.log(`Result is: ${result}`);
expect(result).to.be.a('undefined');

@@ -39,13 +62,13 @@ });

let params = [
[9997, idbp.SQL_PARAM_INPUT, idbp.SQL_NUMERIC], //CUSNUM
['Doe', idbp.SQL_PARAM_INPUT, idbp.SQL_CHAR], //LASTNAME
['J D', idbp.SQL_PARAM_INPUT, idbp.SQL_CHAR], //INITIAL
['123 Broadway', idbp.SQL_PARAM_INPUT, idbp.SQL_CHAR], //ADDRESS
['Hope', idbp.SQL_PARAM_INPUT, idbp.SQL_CHAR], //CITY
['WA', idbp.SQL_PARAM_INPUT, idbp.SQL_CHAR], //STATE
[98101, idbp.SQL_PARAM_INPUT, idbp.SQL_NUMERIC], //ZIP
[2000, idbp.SQL_PARAM_INPUT, idbp.SQL_NUMERIC], //CREDIT LIMIT
[1, idbp.SQL_PARAM_INPUT, idbp.SQL_NUMERIC], // change
[250.99, idbp.SQL_PARAM_INPUT, 4], //BAL DUE
[0.78, idbp.SQL_PARAM_INPUT, 4] //CREDIT DUE
[9997, idbp.IN, idbp.NUMERIC], //CUSNUM
['Doe', idbp.IN, idbp.CHAR], //LASTNAME
['J D', idbp.IN, idbp.CHAR], //INITIAL
['123 Broadway', idbp.IN, idbp.CHAR], //ADDRESS
['Hope', idbp.IN, idbp.CHAR], //CITY
['WA', idbp.IN, idbp.CHAR], //STATE
[98101, idbp.IN, idbp.NUMERIC], //ZIP
[2000, idbp.IN, idbp.NUMERIC], //CREDIT LIMIT
[1, idbp.IN, idbp.NUMERIC], // change
[250.99, idbp.IN, idbp.NUMERIC], //BAL DUE
[0.78, idbp.IN, idbp.NUMERIC] //CREDIT DUE
];

@@ -56,3 +79,2 @@

console.log(`Count Before is: ${rowsBeforeCount}`);
console.log(`input: ${idbp.SQL_PARAM_INPUT}`);
await dbStmt.prepare(sql);

@@ -70,8 +92,2 @@ await dbStmt.bindParam(params);

//would be the exact same test as BindParam
// describe('bind', () => {
// it('shorthand for the bindParams()', async () => {
// });
// });
describe('close', () => {

@@ -110,13 +126,13 @@ it('frees the statement object. ', async () => {

let params = [
[9997, idbp.SQL_PARAM_INPUT, idbp.SQL_NUMERIC], //CUSNUM
['Johnson', idbp.SQL_PARAM_INPUT, idbp.SQL_CHAR], //LASTNAME
['A J', idbp.SQL_PARAM_INPUT, idbp.SQL_CHAR], //INITIAL
['453 Example', idbp.SQL_PARAM_INPUT, idbp.SQL_CHAR], //ADDRESS
['Fort', idbp.SQL_PARAM_INPUT, idbp.SQL_CHAR], //CITY
['TN', idbp.SQL_PARAM_INPUT, idbp.SQL_CHAR], //STATE
[37211, idbp.SQL_PARAM_INPUT, idbp.SQL_NUMERIC], //ZIP
[1000, idbp.SQL_PARAM_INPUT, idbp.SQL_NUMERIC], //CREDIT LIMIT
[1, idbp.SQL_PARAM_INPUT, idbp.SQL_NUMERIC], // change
[150, idbp.SQL_PARAM_INPUT, idbp.SQL_NUMERIC], //BAL DUE
[0.00, idbp.SQL_PARAM_INPUT, idbp.SQL_NUMERIC] //CREDIT DUE
[9997, idbp.IN, idbp.NUMERIC], //CUSNUM
['Johnson', idbp.IN, idbp.CHAR], //LASTNAME
['A J', idbp.IN, idbp.CHAR], //INITIAL
['453 Example', idbp.IN, idbp.CHAR], //ADDRESS
['Fort', idbp.IN, idbp.CHAR], //CITY
['TN', idbp.IN, idbp.CHAR], //STATE
[37211, idbp.IN, idbp.NUMERIC], //ZIP
[1000, idbp.IN, idbp.NUMERIC], //CREDIT LIMIT
[1, idbp.IN, idbp.NUMERIC], // change
[150, idbp.IN, idbp.NUMERIC], //BAL DUE
[0.00, idbp.IN, idbp.NUMERIC] //CREDIT DUE
];

@@ -158,7 +174,6 @@ await dbStmt.prepare(sql);

await dbStmt.prepare(sql);
await dbStmt.bind([[bal, idbp.SQL_PARAM_OUT, idbp.SQL_NUMERIC]]);
await dbStmt.bind([[bal, idbp.OUT, idbp.NUMERIC]]);
let result = await dbStmt.execute();
console.log(`ExecuteAsync results:\n ${JSON.stringify(result)}`);
console.log(`Length of results: ${result.length}`);
expect(result).to.be.a('array');

@@ -183,3 +198,2 @@ expect(result.length).to.be.greaterThan(0);

console.log(`Fetch All results:\n ${JSON.stringify(result)}`);
console.log(`Size of the returned array: ${result.length}`);
expect(result).to.be.a('array');

@@ -186,0 +200,0 @@ expect(result.length).to.be.greaterThan(0);

docs/dbPool.md
docs/main.md
SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap
  • Changelog

About

Packages

npm

Go

Maven

PyPI

Rubygems

Stay in touch

Get open source security insights delivered straight into your inbox.

  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc