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
5
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.8 to 1.1.0

27

CHANGELOG.md
# idb-pconnector change log
# 1.1.0
- feat: Add support for enableNumericTypeConversion on DBPool functions ([#97](https://github.com/IBM/nodejs-idb-pconnector/pull/97))
- feat: Add support for bindParameters ([#92](https://github.com/IBM/nodejs-idb-pconnector/pull/92))
- bindParameters() deprecates bindParam() and bind()
- refactor: Statement tests ([#78](https://github.com/IBM/nodejs-idb-pconnector/pull/78))
- No longer use deprecated Statement with implicitly connection
- No longer use QIWS.QCUSTCDT from insert tests
- Add after/afterEach hooks to delete data inserted by test cases
- Close out open statement and connection handles in each test
- docs: Add select bindParam example ([#101](https://github.com/IBM/nodejs-idb-pconnector/pull/101))
- fix: Add package-lock.json ([#86](https://github.com/IBM/nodejs-idb-pconnector/pull/86))
- refactor: Deprecate implicitly creating a connection
- docs: for enableNumericTypeConversion() ([#54](https://github.com/IBM/nodejs-idb-pconnector/issues/54))
- test: for enableNumericTypeConversion() ([#54](https://github.com/IBM/nodejs-idb-pconnector/issues/54))
- feat(statement.js): enableNumericTypeConversion() ([#54](https://github.com/IBM/nodejs-idb-pconnector/issues/54))
- docs: Add badges ([e74e7db](https://github.com/IBM/nodejs-idb-pconnector/commit/e74e7dbdc5cb0e912c1475ba947c362617f18eb3))
# 1.0.8

@@ -4,0 +31,0 @@

35

docs/README.md

@@ -20,5 +20,7 @@ # **API Documentation**

- [**Statement.bind(params)**](#statementbindparams)
- [**Statement.bindParameters(params)**](#statementbindparametersparams)
- [**Statement.close()**](#statementclose)
- [**Statement.closeCursor()**](#statementclosecursor)
- [**Statement.commit()**](#statementcommit)
- [**Statement.enableNumericTypeConversion(flag)**](#statementenablenumerictypeconversionflag)
- [**Statement.exec(sql)**](#statementexecsql)

@@ -180,2 +182,4 @@ - [**Statement.execute()**](#statementexecute)

**Deprecated:** use [Statement.bindParameters(params)](#statementbindparametersparams) instead.
Associates parameter markers in an sql statement to application variables.

@@ -212,4 +216,2 @@

**Example**: [Here](https://github.com/ibm/nodejs-idb-pconnector#prepare-bind-execute)
## **Statement.bind(params)**

@@ -219,3 +221,15 @@

## **Statement.bindParameters(params)**
Associates parameter markers in an sql statement to application variables.
**Parameters**:
- **params**: `Array` the parameter list of values to bind. The values are ordered based on the parameter markers in the sql statement.
**Returns**: `Promise` when resolved there is no return value but if an error occurred the promise will be rejected.
**Example**: [Here](https://github.com/ibm/nodejs-idb-pconnector#prepare-bind-execute)
## **Statement.close()**

@@ -467,2 +481,18 @@

## **Statement.enableNumericTypeConversion(flag)**
Enables or disables automatic numeric conversion.
**Parameters**:
- **flag:** `boolean` to turn automatic data conversion mode on or off. Default value is `false`.
- `true`: SQL numeric types (`INTEGER`, `DECIMAL`, `NUMERIC`) are converted to Javascript `Number` objects instead of strings. Due to the data type limitations of Javascript `Number`s, precision may be lost. If the SQL value is outside the bounds of what a `Number` can represent, it will be returned as a string instead.
- `false`: The result data is returned as strings.
**Returns**: `boolean` the current state of the flag otherwise an error is thrown.
# **Class: DBPool**

@@ -488,2 +518,3 @@

- `debug`: `boolean` setting it to true will display verbose output to the console, defaults to false.
- `enableNumericTypeConversion`: `boolean` Enabling this option will convert numerics within the result set returned from [runSql](#dbpoolrunsqlsql) and [prepareExecute](#dbpoolprepareexecutesql-params-options) calls as JS Numbers instead of strings when it is safe to do so. This option defaults to false.

@@ -490,0 +521,0 @@ **Example**: [Here](https://github.com/ibm/nodejs-idb-pconnector#dbpool)

14

lib/dbPool.js

@@ -12,3 +12,4 @@ const {

* @param {object} database - Object includes the `url`, `username`, and `password`.
* @param {object} config - Object includes the `incrementSize` and `debug`.
* @param {object} config - Object includes the `incrementSize`, `debug` and
* `enableNumericTypeConversion`.
* @constructor

@@ -21,2 +22,3 @@ */

debug: false,
enableNumericTypeConversion: false,
}) {

@@ -26,2 +28,3 @@ const {

debug,
enableNumericTypeConversion,
} = config;

@@ -37,2 +40,3 @@

this.debug = debug || false;
this.enableNumericTypeConversion = enableNumericTypeConversion || false;

@@ -209,2 +213,6 @@ for (let i = 0; i < this.incrementSize; i += 1) {

if (this.enableNumericTypeConversion) {
await statement.enableNumericTypeConversion(true);
}
this.log(`Executing SQL...\nSQL Statement: ${sql}`);

@@ -254,2 +262,6 @@ resultSet = await statement.exec(sql)

if (this.enableNumericTypeConversion) {
await statement.enableNumericTypeConversion(true);
}
this.log('Preparing Statement...');

@@ -256,0 +268,0 @@ await statement.prepare(sql)

60

lib/statement.js
const { dbstmt, SQL_SUCCESS, SQL_NO_DATA_FOUND, SQL_SUCCESS_WITH_INFO } = require('idb-connector');
const deprecate = require('depd')('Statement');
/**

@@ -27,2 +29,3 @@ * This function is for internal use within Statement constructor.

if (!connection) {
deprecate('implicitly creating a connection within the constructor. You should pass a Connection object instead.');
// eslint-disable-next-line no-param-reassign

@@ -37,3 +40,5 @@ connection = createConnection();

/**
* @private
* @description
* Internal wrapper to call bindParam
* Associates parameter markers in an SQL statement to app variables

@@ -45,3 +50,3 @@ * @param {Array} params - the parameter list

*/
async bindParam(params) {
async bindParamWrapper(params) {
const { stmt } = this;

@@ -61,2 +66,15 @@

/**
* @description
* Associates parameter markers in an SQL statement to app variables
* @param {Array} params - 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()
* @memberof Statement
*/
async bindParam(params) {
deprecate('As of 1.1.0, bindParam() is deprecated and you should use bindParameters() instead.');
await this.bindParamWrapper(params);
}
/**
* Shorthand for bindParam

@@ -68,6 +86,28 @@ * @param {Array} params - the parameter list

async bind(params) {
await this.bindParam(params);
deprecate('As of 1.1.0, bind() is deprecated and you should use bindParameters() instead.');
await this.bindParamWrapper(params);
}
/**
* @description
* Associates parameter markers in an SQL statement to app variables
* @param {Array} params - the parameter list of values to bind
* @returns {Promise} - Promise object represents the execution of bindParameters()
* @memberof Statement
*/
async bindParameters(params) {
const { stmt } = this;
return new Promise((resolve, reject) => {
stmt.bindParameters(params, (error, result) => {
if (error) {
reject(new Error(error.message));
} else {
resolve(result);
}
});
});
}
/**
* Ends and frees the statement object

@@ -488,4 +528,20 @@ * @returns {Promise} - resolves to true indicating success

}
/**
* @description
* Configuration option to return numeric types as a Number instead of a String.
* When set to true numeric types will be returned as a Number instead of a String.
* @param {boolean} flag - option to return numeric types as a Number instead of a String
* @returns {boolean} - resolves to true/false indicating the state of the flag
* @memberof Statement
*/
enableNumericTypeConversion(flag) {
const { stmt } = this;
if (typeof flag === 'undefined') {
return stmt.asNumber();
}
return stmt.asNumber(flag);
}
}
module.exports = Statement;

12

package.json
{
"name": "idb-pconnector",
"version": "1.0.8",
"version": "1.1.0",
"description": "Promise based DB2 Connector for IBM i",

@@ -23,11 +23,13 @@ "main": "lib/idb-pconnector.js",

"dependencies": {
"idb-connector": "^1.2.2"
"depd": "^2.0.0",
"idb-connector": "^1.2.16"
},
"devDependencies": {
"chai": "^4.2.0",
"eslint": "^6.3.0",
"eslint": "^8.8.0",
"eslint-config-airbnb-base": "^14.0.0",
"eslint-plugin-import": "^2.18.2",
"mocha": "^6.2.0"
"eslint-plugin-import": "^2.25.4",
"mocha": "^9.2.0",
"standard-version": "^9.3.2"
}
}

51

README.md

@@ -0,1 +1,6 @@

[![npm](https://img.shields.io/npm/v/idb-pconnector?logo=npm)](https://www.npmjs.com/package/idb-pconnector)
[![ryver-chat](https://img.shields.io/badge/Ryver-Chat-blue)](https://ibmioss.ryver.com/index.html#forums/1000127)
[![ryver-signup](https://img.shields.io/badge/Ryver-Signup-blue)](https://ibmioss.ryver.com/application/signup/members/9tJsXDG7_iSSi1Q)
[![docs](https://img.shields.io/badge/-Docs-blue)](https://github.com/IBM/nodejs-idb-pconnector/blob/master/docs/README.md)
# **idb-pconnector - Promise-based DB2 Connector for IBM i** <!-- omit in toc -->

@@ -18,2 +23,4 @@

- [prepare bind execute](#prepare-bind-execute)
- [insert example](#insert-example)
- [select example](#select-example)
- [DBPool](#dbpool)

@@ -61,2 +68,3 @@ - [prepareExecute](#prepareexecute)

#### insert example
```javascript

@@ -69,24 +77,34 @@ const {

const connection = new Connection({ url: '*LOCAL' });
const statement = new Statement(connection);
const sql = 'INSERT INTO US_STATES(id, name, abbr, region) VALUES (?,?,?,?)';
const sql = 'INSERT INTO QIWS.QCUSTCDT VALUES (?,?,?,?,?,?,?,?,?,?,?) with NONE';
await statement.prepare(sql);
await statement.bindParameters([1, 'Alabama', 'AL' ,'south']);
await statement.execute();
}
await statement.bindParam([
[9997, IN, NUMERIC],
['Johnson', IN, CHAR],
['A J', IN, CHAR],
['453 Example', IN, CHAR],
['Fort', IN, CHAR],
['TN', IN, CHAR],
[37211, IN, NUMERIC],
[1000, IN, NUMERIC],
[1, IN, NUMERIC],
[150, IN, NUMERIC],
[0.00, IN, NUMERIC],
]);
pbeExample().catch((error) => {
console.error(error);
});
```
#### select example
```javascript
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
}

@@ -99,2 +117,3 @@

```
### DBPool

@@ -101,0 +120,0 @@

7

test/connectionTest.js

@@ -12,2 +12,3 @@ /*

const { Connection } = require('../lib/idb-pconnector');
const { dbconn, dbstmt } = require('idb-connector');

@@ -43,3 +44,3 @@ describe('Connection Class Tests', () => {

expect(connReturned.isConnected()).to.equal(true);
expect(connReturned.dbconn).to.be.a('dbconn');
expect(connReturned.dbconn).to.be.a.instanceof(dbconn);
});

@@ -55,3 +56,3 @@

expect(connReturned.isConnected()).to.equal(true);
expect(connReturned.dbconn).to.be.a('dbconn');
expect(connReturned.dbconn).to.be.a.instanceof(dbconn);
});

@@ -65,3 +66,3 @@ });

expect(stmtReturned.stmt).to.be.a('dbstmt');
expect(stmtReturned.stmt).to.be.a.instanceof(dbstmt);
});

@@ -68,0 +69,0 @@ });

97

test/dbpoolTest.js

@@ -219,2 +219,99 @@ /*

});
describe('enableNumericTypeConversion', () => {
it('should default to false', async () => {
const pool = new DBPool({ url: '*LOCAL' });
expect(pool.enableNumericTypeConversion).to.equal(false);
});
it('runSql should default to return numeric data as strings', async () => {
const sql = `select cast(-32768 as SMALLINT) MIN_SMALLINT,
cast(+32767 as SMALLINT) MAX_SMALLINT
from sysibm.sysdummy1`;
const pool = new DBPool({ url: '*LOCAL' });
const result = await pool.runSql(sql);
expect(result).to.be.an('array');
expect(result.length).to.be.greaterThan(0);
expect(result).to.eql([{ MIN_SMALLINT: '-32768', MAX_SMALLINT: '32767' }]);
});
it('prepareExecute should default to return numeric data as strings', async () => {
const sql = `select cast(-32768 as SMALLINT) MIN_SMALLINT,
cast(+32767 as SMALLINT) MAX_SMALLINT
from sysibm.sysdummy1`;
const pool = new DBPool({ url: '*LOCAL' });
const result = await pool.prepareExecute(sql);
const { resultSet } = result;
expect(resultSet).to.be.an('array');
expect(resultSet.length).to.be.greaterThan(0);
expect(resultSet).to.eql([{ MIN_SMALLINT: '-32768', MAX_SMALLINT: '32767' }]);
});
it('if set runSql should return numeric data as Number when safe to do so', async () => {
const sql = `select
cast(-32768 as SMALLINT) MIN_SMALLINT,
cast(+32767 as SMALLINT) MAX_SMALLINT,
cast(-2147483648 as INT) MIN_INT,
cast(+2147483647 as INT) MAX_INT,
cast(999999999999999 as DECIMAL(15,0)) as DEC_SAFE_15_0,
cast(.999999999999999 as DECIMAL(15,15)) as DEC_SAFE_15_15,
--these values do not fit in a javascript number datatype
cast(-9223372036854775808 as BIGINT) MIN_BIGINT,
cast(+9223372036854775807 as BIGINT) MAX_BIGINT,
cast(9999999999999999 as DECIMAL(16,0)) as DEC_NOT_SAFE_16_0
from sysibm.sysdummy1`;
const pool = new DBPool({ url: '*LOCAL' }, { enableNumericTypeConversion: true });
expect(pool.enableNumericTypeConversion).to.equal(true);
const result = await pool.runSql(sql);
expect(result).to.be.an('array');
expect(result.length).to.be.greaterThan(0);
expect(result).to.eql([{
MIN_SMALLINT: -32768,
MAX_SMALLINT: 32767,
MIN_INT: -2147483648,
MAX_INT: 2147483647,
DEC_SAFE_15_0: 999999999999999,
DEC_SAFE_15_15: 0.999999999999999,
MIN_BIGINT: '-9223372036854775808',
MAX_BIGINT: '9223372036854775807',
DEC_NOT_SAFE_16_0: '9999999999999999',
}]);
});
it('if set prepareExecute should return numeric data as Number when safe to do so', async () => {
const sql = `select
cast(-32768 as SMALLINT) MIN_SMALLINT,
cast(+32767 as SMALLINT) MAX_SMALLINT,
cast(-2147483648 as INT) MIN_INT,
cast(+2147483647 as INT) MAX_INT,
cast(999999999999999 as DECIMAL(15,0)) as DEC_SAFE_15_0,
cast(.999999999999999 as DECIMAL(15,15)) as DEC_SAFE_15_15,
--these values do not fit in a javascript number datatype
cast(-9223372036854775808 as BIGINT) MIN_BIGINT,
cast(+9223372036854775807 as BIGINT) MAX_BIGINT,
cast(9999999999999999 as DECIMAL(16,0)) as DEC_NOT_SAFE_16_0
from sysibm.sysdummy1`;
const pool = new DBPool({ url: '*LOCAL' }, { enableNumericTypeConversion: true });
expect(pool.enableNumericTypeConversion).to.equal(true);
const result = await pool.prepareExecute(sql);
const { resultSet } = result;
expect(resultSet).to.be.an('array');
expect(resultSet.length).to.be.greaterThan(0);
expect(resultSet).to.eql([{
MIN_SMALLINT: -32768,
MAX_SMALLINT: 32767,
MIN_INT: -2147483648,
MAX_INT: 2147483647,
DEC_SAFE_15_0: 999999999999999,
DEC_SAFE_15_15: 0.999999999999999,
MIN_BIGINT: '-9223372036854775808',
MAX_BIGINT: '9223372036854775807',
DEC_NOT_SAFE_16_0: '9999999999999999',
}]);
});
});
});

393

test/statementTest.js

@@ -12,29 +12,49 @@ /*

const {
Connection, Statement, DBPool, IN, OUT, NUMERIC, CHAR, SQL_ATTR_FOR_FETCH_ONLY, NULL,
Connection, Statement, IN, OUT, NUMERIC, INT, CHAR, SQL_ATTR_FOR_FETCH_ONLY, NULL,
} = require('../lib/idb-pconnector');
const schema = 'IDBPTEST';
const table = 'SCORES';
const procedure = 'MAXBAL';
describe('Statement Class Tests', () => {
before('setup schema for tests', async () => {
const pool = new DBPool({ url: '*LOCAL' }, { incrementSize: 2 });
const createSchema = `CREATE SCHEMA ${schema}`;
const findSchema = `SELECT SCHEMA_NAME FROM qsys2.sysschemas WHERE SCHEMA_NAME = '${schema}'`;
before('setup schema for tests', async function () {
this.timeout(0); // disbale timeout for hook
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();
const schemaResult = await pool.runSql(findSchema);
const schemaResult = await statement.exec(`SELECT SCHEMA_NAME FROM QSYS2.sysschemas WHERE SCHEMA_NAME = '${schema}'`);
await statement.closeCursor();
if (!schemaResult.length) {
await pool.runSql(createSchema).catch((error) => {
// eslint-disable-next-line no-console
console.log(`UNABLE TO CREATE ${schema} SCHEMA!`);
throw error;
});
// eslint-disable-next-line no-console
console.log(`before hook: CREATED ${schema}`);
await statement.exec(`CREATE SCHEMA ${schema}`);
}
await statement.exec(`CREATE OR REPLACE TABLE ${schema}.${table}(team VARCHAR(100), score INTEGER);`);
await statement.exec(`CREATE OR REPLACE PROCEDURE ${schema}.${procedure} (OUT OUTPUT DECIMAL(6,2))
BEGIN
DECLARE MAXBAL NUMERIC ( 6 , 2 ) ;
SELECT MAX ( BALDUE ) INTO ${procedure} FROM QIWS.QCUSTCDT;
SET OUTPUT = MAXBAL;
END`
);
await statement.close();
await connection.close();
});
after('drop objects after the tests', async function () {
this.timeout(0); // disbale timeout for hook
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();
await statement.exec(`DROP TABLE ${schema}.${table}`);
await statement.exec(`DROP PROCEDURE ${schema}.${procedure}`);
await statement.close();
await connection.close();
});
describe('constructor with connection parameter', () => {
it('creates a new Statement object by passing a connection object', async () => {
const connection = new Connection().connect();
const connection = new Connection({ url: '*LOCAL' });
const statement = new Statement(connection);

@@ -95,4 +115,4 @@

it('prepares an sql statement', async () => {
const connection = new Connection();
const statement = connection.connect().getStatement();
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();

@@ -105,52 +125,50 @@ const result = await statement.prepare('SELECT * FROM QIWS.QCUSTCDT');

describe('bindParams', () => {
before('create table for test', async () => {
const pool = new DBPool({ url: '*LOCAL' }, { incrementSize: 2 });
const createTable = `CREATE TABLE ${schema}.SCORES(team VARCHAR(100) ALLOCATE(20), score INTEGER)`;
const findTable = `SELECT OBJLONGNAME FROM TABLE (QSYS2.OBJECT_STATISTICS('${schema}', '*FILE')) AS X WHERE OBJLONGNAME = 'SCORES'`;
it('associate parameter markers in an SQL to app variables', async () => {
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();
const tableResult = await pool.runSql(findTable);
const params = [
['Tigers', IN, CHAR],
[35, IN, INT],
];
if (!tableResult.length) {
await pool.runSql(createTable).catch((error) => {
// eslint-disable-next-line no-console
console.log('Unable to create SCORES table');
throw error;
});
// eslint-disable-next-line no-console
console.log('before hook: CREATED SCORES TABLE');
}
await statement.prepare(`INSERT INTO ${schema}.${table}(TEAM, SCORE) VALUES (?,?)`);
await statement.bindParam(params);
await statement.execute();
const result = await statement.exec(`SELECT COUNT(TEAM) AS COUNT FROM ${schema}.${table}`);
const count = Number.parseInt(result[0].COUNT, 10);
expect(count).to.equal(1);
await statement.close();
await connection.disconn();
await connection.close();
});
it('associate parameter markers in an SQL to app variables', async () => {
const sql = 'INSERT INTO QIWS.QCUSTCDT(CUSNUM,LSTNAM,INIT,STREET,CITY,STATE,ZIPCOD,CDTLMT,CHGCOD,BALDUE,CDTDUE) VALUES (?,?,?,?,?,?,?,?,?,?,?) with NONE';
it('binds a null value, tests issue #40', async () => {
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();
const statement = new Statement();
const statement2 = new Statement();
const params = [
[9997, IN, NUMERIC], // CUSNUM
['Doe', IN, CHAR], // LASTNAME
['J D', IN, CHAR], // INITIAL
['123 Broadway', IN, CHAR], // ADDRESS
['Hope', IN, CHAR], // CITY
['WA', IN, CHAR], // STATE
[98101, IN, NUMERIC], // ZIP
[2000, IN, NUMERIC], // CREDIT LIMIT
[1, IN, NUMERIC], // change
[250.99, IN, NUMERIC], // BAL DUE
[0.78, IN, NUMERIC], // CREDIT DUE
['EXAMPLE', IN, CHAR],
[null, IN, NULL],
];
const countResult = await statement2.exec('SELECT COUNT(CUSNUM) AS COUNT FROM QIWS.QCUSTCDT');
const rowsBeforeCount = Number.parseInt(countResult[0].COUNT, 10);
await statement.prepare(sql);
await statement.prepare(`INSERT INTO ${schema}.${table}(TEAM, SCORE) VALUES (?,?)`);
await statement.bindParam(params);
await statement.execute();
await statement.close();
await connection.disconn();
await connection.close();
});
});
const countResultAgain = await statement.exec('SELECT COUNT(CUSNUM) AS COUNT FROM QIWS.QCUSTCDT');
describe('bindParameters', () => {
it('binds an array of values', async () => {
const sql = `INSERT INTO ${schema}.SCORES(TEAM, SCORE) VALUES (?,?)`;
const rowsAfterCount = Number.parseInt(countResultAgain[0].COUNT, 10);
expect(rowsAfterCount).to.equal(rowsBeforeCount + 1);
const statement = new Statement();
await statement.prepare(sql);
await statement.bindParameters(['Rockets', 105]);
await statement.execute();
});

@@ -162,10 +180,4 @@

const statement = new Statement();
const params = [
['EXAMPLE', IN, CHAR], // TEAM
[null, IN, NULL], // SCORE
];
await statement.prepare(sql);
await statement.bindParam(params);
await statement.bindParameters(['Bulls', null]);
await statement.execute();

@@ -177,3 +189,4 @@ });

it('frees the statement object. ', async () => {
const statement = new Statement();
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();

@@ -183,2 +196,4 @@ await statement.exec('SELECT * FROM QIWS.QCUSTCDT');

expect(result).to.equal(true);
await connection.disconn();
await connection.close();
});

@@ -190,3 +205,4 @@ });

it('discards any pending results', async () => {
const statement = new Statement();
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();

@@ -196,2 +212,5 @@ await statement.exec('SELECT * FROM QIWS.QCUSTCDT');

expect(result).to.equal(true);
await statement.close();
await connection.disconn();
await connection.close();
});

@@ -201,24 +220,26 @@ });

describe('commit', () => {
after(async () => {
// runs after all tests in this block
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();
await statement.exec(`DELETE FROM ${schema}.${table}`);
await statement.close();
await connection.disconn();
await connection.close();
});
it('adds changes to the database', async () => {
const sql = 'INSERT INTO QIWS.QCUSTCDT(CUSNUM,LSTNAM,INIT,STREET,CITY,STATE,ZIPCOD,CDTLMT,CHGCOD,BALDUE,CDTDUE) VALUES (?,?,?,?,?,?,?,?,?,?,?) with NONE ';
const statement = new Statement();
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();
const params = [
[9997, IN, NUMERIC], // CUSNUM
['Johnson', IN, CHAR], // LASTNAME
['A J', IN, CHAR], // INITIAL
['453 Example', IN, CHAR], // ADDRESS
['Fort', IN, CHAR], // CITY
['TN', IN, CHAR], // STATE
[37211, IN, NUMERIC], // ZIP
[1000, IN, NUMERIC], // CREDIT LIMIT
[1, IN, NUMERIC], // change
[150, IN, NUMERIC], // BAL DUE
[0.00, IN, NUMERIC], // CREDIT DUE
['Lions', IN, CHAR],
[13, IN, INT],
];
await statement.prepare(sql);
await statement.prepare(`INSERT INTO ${schema}.${table}(TEAM, SCORE) VALUES (?,?)`);
await statement.bindParam(params);
await statement.execute();
const result = await statement.commit();
await statement.close();
await connection.disconn();
await connection.close();
expect(result).to.equal(true);

@@ -230,9 +251,7 @@ });

it('directly executes a given SQL String', async () => {
const connection = new Connection();
const statement = connection.connect().getStatement();
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();
const sql = 'SELECT * FROM QIWS.QCUSTCDT WHERE CUSNUM = 938472';
const result = await statement.exec('SELECT * FROM QIWS.QCUSTCDT WHERE CUSNUM = 938472');
const result = await statement.exec(sql);
expect(result).to.be.an('array');

@@ -244,34 +263,13 @@ expect(result.length).to.be.greaterThan(0);

describe('execute', () => {
before('init stored procedure', async () => {
const pool = new DBPool({ url: '*LOCAL' });
const findSp = `SELECT OBJLONGNAME FROM TABLE (QSYS2.OBJECT_STATISTICS('${schema}', '*PGM')) AS X`;
const spResult = await pool.runSql(findSp);
if (!spResult.length) {
const createSP = `CREATE PROCEDURE ${schema}.MAXBAL (OUT OUTPUT DECIMAL(6,2))
BEGIN
DECLARE MAXBAL NUMERIC ( 6 , 2 ) ;
SELECT MAX ( BALDUE ) INTO MAXBAL FROM QIWS.QCUSTCDT;
SET OUTPUT = MAXBAL;
END`;
await pool.runSql(createSP).catch((error) => {
// eslint-disable-next-line no-console
console.log('UNABLE TO CREATE STORED PROCEDURE!');
throw error;
});
// eslint-disable-next-line no-console
console.log('before hook: Created Stored Procedure');
}
});
it('executes a stored procedure and returns output parameter', async () => {
const connection = new Connection();
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.connect().getStatement();
const statement = connection.getStatement();
const bal = 0;
await statement.prepare(`CALL ${schema}.MAXBAL(?)`);
await statement.prepare(`CALL ${schema}.${procedure}(?)`);
await statement.bind([[bal, OUT, NUMERIC]]);
const result = await statement.execute();
await connection.disconn();
await connection.close();

@@ -286,4 +284,4 @@ expect(result).to.be.a('array');

it('fetches All rows from the result set', async () => {
const connection = new Connection();
const statement = connection.connect().getStatement();
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();

@@ -293,2 +291,5 @@ await statement.prepare('SELECT * FROM QIWS.QCUSTCDT');

const results = await statement.fetchAll();
await statement.close();
await connection.disconn();
await connection.close();

@@ -318,4 +319,4 @@ expect(results).to.be.a('array');

it('fetches one row from the result set', async () => {
const connection = new Connection();
const statement = connection.connect().getStatement();
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();

@@ -325,2 +326,5 @@ await statement.prepare('SELECT * FROM QIWS.QCUSTCDT');

const result = await statement.fetch();
await statement.close();
await connection.disconn();
await connection.close();

@@ -345,3 +349,4 @@ expect(result).to.be.a('object');

it('returns number of fields contained in result', async () => {
const statement = new Statement();
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();

@@ -351,4 +356,6 @@ await statement.prepare('SELECT * FROM QIWS.QCUSTCDT');

const fields = await statement.numFields();
expect(fields).to.be.a('number').to.equal(11);
await statement.close();
await connection.disconn();
await connection.close();
});

@@ -358,26 +365,27 @@ });

describe('numRows', () => {
after(async () => {
// runs after all tests in this block
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();
await statement.exec(`DELETE FROM ${schema}.${table}`);
await statement.close();
await connection.disconn();
await connection.close();
});
it('returns number of rows that were effected by a query', async () => {
const sql = 'INSERT INTO QIWS.QCUSTCDT(CUSNUM,LSTNAM,INIT,STREET,CITY,STATE,ZIPCOD,CDTLMT,CHGCOD,BALDUE,CDTDUE) VALUES (?,?,?,?,?,?,?,?,?,?,?) with NONE ';
const statement = new Statement();
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();
const params = [
[9997, IN, NUMERIC], // CUSNUM
['Johnson', IN, CHAR], // LAST NAME
['A J', IN, CHAR], // INITIAL
['453 Example', IN, CHAR], // ADDRESS
['Fort', IN, CHAR], // CITY
['TN', IN, CHAR], // STATE
[37211, IN, NUMERIC], // ZIP
[1000, IN, NUMERIC], // CREDIT LIMIT
[1, IN, NUMERIC], // change
[150, IN, NUMERIC], // BAL DUE
[0.00, IN, NUMERIC], // CREDIT DUE
['Jaguars', IN, CHAR],
[20, IN, INT],
];
await statement.prepare(sql);
await statement.prepare(`INSERT INTO ${schema}.${table}(TEAM, SCORE) VALUES (?,?)`);
await statement.bindParam(params);
await statement.execute();
const rows = await statement.numRows();
expect(rows).to.be.a('number').and.to.equal(1);
await statement.close();
await connection.disconn();
await connection.close();
});

@@ -388,3 +396,4 @@ });

it('returns the data type of the indicated column', async () => {
const statement = new Statement();
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();

@@ -395,4 +404,6 @@ await statement.prepare('SELECT * FROM QIWS.QCUSTCDT');

const type = await statement.fieldType(0);
expect(type).to.be.a('number').and.to.equal(2);
await statement.close();
await connection.disconn();
await connection.close();
});

@@ -403,3 +414,4 @@ });

it('returns the field width of the indicated column', async () => {
const statement = new Statement();
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();

@@ -409,4 +421,6 @@ await statement.prepare('SELECT * FROM QIWS.QCUSTCDT');

const width = await statement.fieldWidth(0);
expect(width).to.to.equal(7);
await statement.close();
await connection.disconn();
await connection.close();
});

@@ -417,3 +431,4 @@ });

it('returns t/f if the indicated column is nullable', async () => {
const statement = new Statement();
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();

@@ -423,4 +438,6 @@ await statement.prepare('SELECT * FROM QIWS.QCUSTCDT');

const nullable = await statement.fieldNullable(0);
expect(nullable).to.equal(false);
await statement.close();
await connection.disconn();
await connection.close();
});

@@ -431,3 +448,4 @@ });

it('returns name of the indicated column ', async () => {
const statement = new Statement();
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();

@@ -437,4 +455,6 @@ await statement.prepare('SELECT * FROM QIWS.QCUSTCDT');

const name = await statement.fieldName(0);
expect(name).to.equal('CUSNUM');
await statement.close();
await connection.disconn();
await connection.close();
});

@@ -445,3 +465,4 @@ });

it('returns the precision of the indicated column', async () => {
const statement = new Statement();
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();

@@ -452,2 +473,5 @@ await statement.prepare('SELECT * FROM QIWS.QCUSTCDT');

const precision = await statement.fieldPrecise(0);
await statement.close();
await connection.disconn();
await connection.close();

@@ -460,3 +484,4 @@ expect(precision).to.equal(6);

it('returns the scale of the indicated column', async () => {
const statement = new Statement();
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();

@@ -466,2 +491,5 @@ await statement.prepare('SELECT * FROM QIWS.QCUSTCDT');

const scale = await statement.fieldScale(0);
await statement.close();
await connection.disconn();
await connection.close();

@@ -477,4 +505,9 @@ expect(scale).to.equal(0);

const statement = new Statement();
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();
const result = await statement.setStmtAttr(attr, value);
await statement.close();
await connection.disconn();
await connection.close();
expect(result).to.equal(true);

@@ -487,5 +520,9 @@ });

const attr = SQL_ATTR_FOR_FETCH_ONLY;
const statement = new Statement();
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();
const result = await statement.getStmtAttr(attr);
await statement.close();
await connection.disconn();
await connection.close();

@@ -498,3 +535,4 @@ expect(result).to.equal(0);

it('rollback changes made on the connection', async () => {
const statement = new Statement();
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();

@@ -504,2 +542,5 @@ await statement.prepare('SELECT * FROM QIWS.QCUSTCDT');

const result = await statement.rollback();
await statement.close();
await connection.disconn();
await connection.close();

@@ -510,2 +551,62 @@ expect(result).to.equal(true);

describe('enableNumericTypeConversion', () => {
it('should default to false', async () => {
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();
expect(statement.enableNumericTypeConversion()).to.equal(false);
});
it('should return numeric data as strings when false', async () => {
const sql = `select cast(-32768 as SMALLINT) MIN_SMALLINT,
cast(+32767 as SMALLINT) MAX_SMALLINT
from sysibm.sysdummy1`;
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();
const result = await statement.exec(sql);
await statement.close();
await connection.disconn();
await connection.close();
expect(result).to.be.an('array');
expect(result.length).to.be.greaterThan(0);
expect(result).to.eql([{ MIN_SMALLINT: '-32768', MAX_SMALLINT: '32767' }]);
});
it('should return numeric data as Number when safe to do so', async () => {
const sql = `select
cast(-32768 as SMALLINT) MIN_SMALLINT,
cast(+32767 as SMALLINT) MAX_SMALLINT,
cast(-2147483648 as INT) MIN_INT,
cast(+2147483647 as INT) MAX_INT,
cast(999999999999999 as DECIMAL(15,0)) as DEC_SAFE_15_0,
cast(.999999999999999 as DECIMAL(15,15)) as DEC_SAFE_15_15,
--these values do not fit in a javascript number datatype
cast(-9223372036854775808 as BIGINT) MIN_BIGINT,
cast(+9223372036854775807 as BIGINT) MAX_BIGINT,
cast(9999999999999999 as DECIMAL(16,0)) as DEC_NOT_SAFE_16_0
from sysibm.sysdummy1`;
const connection = new Connection({ url: '*LOCAL' });
const statement = connection.getStatement();
expect(statement.enableNumericTypeConversion(true)).to.equal(true);
const result = await statement.exec(sql);
await statement.close();
await connection.disconn();
await connection.close();
expect(result).to.be.an('array');
expect(result.length).to.be.greaterThan(0);
expect(result).to.eql([{
MIN_SMALLINT: -32768,
MAX_SMALLINT: 32767,
MIN_INT: -2147483648,
MAX_INT: 2147483647,
DEC_SAFE_15_0: 999999999999999,
DEC_SAFE_15_15: 0.999999999999999,
MIN_BIGINT: '-9223372036854775808',
MAX_BIGINT: '9223372036854775807',
DEC_NOT_SAFE_16_0: '9999999999999999',
}]);
});
});
// TODO

@@ -512,0 +613,0 @@ describe.skip('stmtError', () => {

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