idb-pconnector
Advanced tools
Promised-based Db2 Connector for IBM i (pre-alpha, NOT PRODUCTION READY)
Promised-based Db2 Connector for IBM i (pre-alpha, NOT PRODUCTION READY)
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.
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 statments 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('\nPrepare 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();
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.
returns a Statment Object intialized to the current dbConn Conection.
Returns: object, - a new Statement intialized with the current dbconn.
closes the Connection to the DB and frees the connection object.
Returns: Promise, - Promise object represents the closure of the Connection.
disconnects an exsisting connection to the datbase.
Returns: Promise, - Promise object represents the disconnect of the Connection.
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.
if connection attribute exsits should return type String or Int depending on the attribute type
Parameters
attribute: number, if connection attribute exsits 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.
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 Documentaion ON Connection Attributes
Sets the ConnAttr , Attrubte 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().
Checks if the given SQL is valid and nterprets 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.
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:
bStmt.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:
1.SQL_PARAM_INPUT
2.SQL_PARAM_OUTPUT
3.SQL_PARAM_INPUT_OUTPUT
INDICATORS CAN BE:
1. SQL_BIND_CLOB
2. SQL_BIND_CHAR
3. SQL_BIND_NUMERIC
4. SQL_BIND_NULL_DATA
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 ).
Ends and frees the statement object.
Returns: Promise, - Promise object represents the execution of close().
closes the cursor associated with the dbstmt object and discards any pending results.
Returns: Promise, - Promise object represents the execution of closeCursor().
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().
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 exection of exec().
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.
try{
var dba = require("idb-pconnector");
// note that that calling the new Statement() without the DbConn as a parameter
// creates a new connection automatically and uses that for the Statment.
var dbStmt = new dba.Statement();
var sql = "call QIWS.sampleProc";
await dbStmt.prepare(sql);
await dbStmt.execute();
var res = await dbStmt.fetchAll();
console.log(`Result is\n: ${JSON.stringify(res)}`);
}
catch(dbError){
console.log(dbError.stack);
},- Insert Example With Prepare , Binding Parameter , and Execution
try {
var dba = require("idb-pconnector");
// note that that calling the new Statement() without the DbConn as a parameter
// creates a new connection automatically and uses that for the Statment.
var dbStmt = new dba.Statement();
await dbStmt.prepare("INSERT INTO AMUSSE.TABLE1 VALUES (?,?)");
await dbStmt.bind([ [2018,dba.SQL_PARAM_INPUT,dba.SQL_BIND_NUMERIC], [ ,dba.PARM_TYPE_INPUT, dba.SQL_BIND_NULL_DATA ] ]);
await dbStmt.execute();
var res = await dbStmt.exec("SELECT * FROM AMUSSE.TABLE1");
console.log("Select results: "+JSON.stringify(res));
}
catch (dbError) {
console.log("Error is " + dbError);
console.log(error.stack);
}
Example:
- Calling a stored Procedure that returns a result set with execute() & displaying the result set.
try{
var dba = require("idb-pconnector");
// note that that calling the new Statement() without the DbConn as a parameter
// creates a new connection automatically and uses that for the Statment.
var dbStmt = new dba.Statement();
var sql = "call QIWS.sampleProc";
await dbStmt.prepare(sql);
await dbStmt.execute();
var res = await dbStmt.fetchAll();
console.log(`Result is\n: ${JSON.stringify(res)}`);
}
catch(dbError){
console.log(dbError.stack);
},- Insert Example With Prepare , Binding Parameter , and Execution
try {
var dba = require("idb-pconnector");
// note that that calling the new Statement() without the DbConn as a parameter
// creates a new connection automatically and uses that for the Statment.
var dbStmt = new dba.Statement();
await dbStmt.prepare("INSERT INTO AMUSSE.TABLE1 VALUES (?,?)");
await dbStmt.bind([ [2018,dba.SQL_PARAM_INPUT,dba.SQL_BIND_NUMERIC], [ ,dba.PARM_TYPE_INPUT, dba.SQL_BIND_NULL_DATA ] ]);
await dbStmt.execute();
var res = await dbStmt.exec("SELECT * FROM AMUSSE.TABLE1");
console.log("Select results: "+JSON.stringify(res));
}
catch (dbError) {
console.log("Error is " + dbError);
console.log(error.stack);
}
if a result exsits , retrieves a row from the result set
Returns: Promise, - Promise object represents the row that was retrieved from the execution of fetch().
if a result set exsits , 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().
requires an int index parameter. If a valid index is provided, returns the name of the indicated field.
Parameters
index: number, the postion of the field within the table. It is 0 based.
Returns: Promise, - Promise object represents the the String that was retrieved from the execution of fieldName().
requires an int index parameter. If a valid index is provided, returns t/f if the indicated field can be Null
Parameters
index: number, the postion of the field within the table. It is 0 based.
Returns: Promise, - Promise object represents the the boolean that was retrieved from the execution of fieldNullable().
requires an int index parameter. If a valid index is provided, returns the precision of the indicated field
Parameters
index: number, the postion of the field within the table. It is 0 based.
Returns: Promise, - Promise object represents the the Number that was retrieved from the execution of fieldPrecisie().
requires an int index parameter. If a valid index is provided, returns the scale of the indicated column
Parameters
index: number, the postion of the field within the table. It is 0 based.
Returns: Promise, - Promise object represents the the Number that was retrieved from the execution of fieldScale().
requires an int index parameter. If a valid index is provided, returns the data type of the indicated field
Parameters
index: number, the postion of the field within the table. It is 0 based.
Returns: Promise, - Promise object represents the the Number that was retrieved from the execution of fieldType().
requires an int index parameter. If a valid index is provided, returns the field width of the indicated field
Parameters
index: number, the postion of the field within the table. It is 0 based.
Returns: Promise, - Promise object represents the the Number that was retrieved from the execution of fieldWidth().
If a valid Statment 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 statement attribute to get
Returns: Promise, Promise object represents the the String | Number that was retrieved from the execution of getStmtAttr().
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 Documentaion On Statement Attributes
Determines whether there is more information available on the statement
Returns: Promise, - Promise object represents the execution of nextResult().
if a result is available , retrieves number of fields contained in result.
Returns: Promise, - Promise object represents the Number returned from the execution of numFields().
if a query was performed ,retrieves number of rows that were effected by a query
Returns: Promise, - Promise object represents the Number returned from the execution of numRows().
If valid SQL is provided . prepares SQL and sends it to the DBMS, if the input SQL Statement cannot be prepared error is thrown.
Parameters
sqlString: string, the SQL string to be prepared.
Returns: Promise, - Promise object represents the the execution of prepare().
Example:
- view the examples located at the excute() method.
Reverts changes to the database that have been made on the connection since connect time or the previous call to commit().
if a valid attribute and value is providied , sets StmtAttr indicate Attrubte. Refer to the example @getStmtAttr for a list of valid Statement Attributes.
Parameters
attribute: number, musbt be an int INT.
value: string | number, can String or Int depending on the attribute
Returns: Promise, - Promise object represents the execution of setStmtAttr().
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.
Returns: Promise, - Promise object represents Number retrieved from the execution of stmtError().
Example:
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
Manages a list of DBPoolConnection instances.
Constructor to instantiate a new instance of a DBPool class given the database and config
Parameters
database: object, Object includes the url(databse name) defaults to *LOCAL, username, and password. username and password assumed blank if not specified with non-local URL.
config: object , Object includes the incrementSize and debug. IncrementSize sets the desired size of the DBPool. If none specified, defaults to 8 connections. Setting debug = true will display message logs.
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 identifier to id the connection for debug purposes.
Frees all connections in the pool (Sets "Available" back to true for all) closes any statements and gets a new statement.
Returns: boolean, - true if all were detached succesfully
Retires (Removes) all connections from being used again
Returns: boolean, - true if all were retired succesfully
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.
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
Finds and returns the first available Connection.
Returns: DBPoolConnection, - one connection from the DBPool.
Shorthand to exec a statement , just provide the sql to run.
Parameters
sql: string, the sql statment to execute.
Returns: array, - if the SQL returns a result set it is returned as an array of objects.
else if no result set is available null is returned. caller should check if null is returned.
Shortcut to prepare ,bind, and execute. Just provide the sql and the params as an array.
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.
Returns: array, - if the Prepared SQL returns result set it is returned as an array of objects.
else null will be returned indicating that there is no result set.
FAQs
Promise based DB2 Connector for IBM i
The npm package idb-pconnector receives a total of 62 weekly downloads. As such, idb-pconnector popularity was classified as not popular.
We found that idb-pconnector demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 5 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
MITRE's 2024 CWE Top 25 highlights critical software vulnerabilities like XSS, SQL Injection, and CSRF, reflecting shifts due to a refined ranking methodology.
Security News
In this segment of the Risky Business podcast, Feross Aboukhadijeh and Patrick Gray discuss the challenges of tracking malware discovered in open source softare.
Research
Security News
A threat actor's playbook for exploiting the npm ecosystem was exposed on the dark web, detailing how to build a blockchain-powered botnet.