geoffchan23 / node-jt400

NodeJS JT400 wrapper to connect to IBM iSeries and AS/400 systems (OS400 operating system, database like DB2, programs and filesystem)

node-jt400

NodeJS JT400 wrapper to connect to IBM iSeries and AS/400 systems (OS400 operating system, database like DB2, programs and filesystem)

Install

npm install node-jt400 --save

Windows

Windows installations can be tricky because of node-java dependency. Make sure that that module works first. You can check out the node-java documentation for windows installation

We also have some solved issues you can take a look at like #13 and #26

Other issues might be related to node-gyp, python and MS build tools or VS IDE.

Configure

Most basic configuration would be:

const config = {
    host: 'myhost',
    user: 'myuser',
    password: 'xxx',
}
const pool = require('node-jt400').pool(config);

But the config accepts all JT400 JDBC Properties so you can add other options like translate binary

const config = {
    host: 'myhost',
    user: 'myuser',
    password: 'xxx',
    'translate binary': 'true',
    trace: 'true',
}
const pool = require('node-jt400').pool(config);

SQL / Database

Query

Promises

pool
  .query('SELECT field1, field2 FROM foo WHERE bar=? AND baz=?', [1, 'a'])
  .then(result => {
    console.log('result');
    const field1 = result[0].FIELD1;
    console.log(field1);
  })
  .fail(error => {
    console.log('error');
    console.log(error);
  });

Async/await

try {
    const results = await pool.query('SELECT field1, field2 FROM foo WHERE bar=? AND baz=?', [1, 'a']);
    console.log('result');
    const field1 = result[0].FIELD1;
    console.log(field1);
}
catch (error) {
    console.log('error');
    console.log(error);
}

Update

Promises

pool
  .update('UPDATE foo SET bar=? WHERE baz=?', [1, 'a'])
  .then(nUpdated => {
    console.log('Updated ' + nUpdated + ' rows');
});

Async/await

try {
    const rowsUpdated = await pool.update('UPDATE foo SET bar=? WHERE baz=?', [1, 'a']);
    console.log('rows updated');
    console.log(rowsUpdated);
}
catch (error) {
    console.log('error');
    console.log(error);
}

Delete

Promises

pool
  .update('DELETE FROM foo WHERE bar=?', [1])
  .then(nUpdated => {
    console.log('Deleted + ' nUpdated + ' rows');
});

Async/await

try {
    const rowsDeleted = await pool.update('DELETE FROM foo WHERE bar=?', [1]);
    console.log('Deleted + ' rowsDeleted + ' rows');
}
catch (error) {
    console.log('error');
    console.log(error);
}

Insert

Promises

pool
  .insertAndGetId('INSERT INTO foo (bar, baz) VALUES(?,?)',[2,'b'])
  .then(id => {
    console.log('Inserted new row with id ' + id);
});

Async/await

try {
    const id = await pool.insertAndGetId('INSERT INTO foo (bar, baz) VALUES(?,?)',[2,'b']);
    console.log('Inserted new row with id ' + id);
}
catch (error) {
    console.log('error');
    console.log(error);
}

Insert list

Promises

const tableName = 'foo';
const idColumn  = 'fooid';
const rows = [
    {FIELD1: 1, FIELD2: 'a'},
    {FIELD1: 2, FIELD2: 'b'}
];

pool
  .insertList(tableName, idColumn, rows)
  .then(listOfGeneratedIds => {
    console.log(listOfGeneratedIds);
});

Async/await

try {
    const idList = await pool.insertList(tableName, idColumn, rows);
    console.log(idList);
}
catch (error) {
    console.log('error');
    console.log(error);
}

Batch update

Promises

//insert list in one statement
const data = [
    [1, 'a'],
    [2, 'b']
];

pool
  .batchUpdate('INSERT INTO FOO (FIELD1, FIELD2) VALUES(?,?)', data)
  .then(result => {
    console.log(result);
    //result is number of updated rows for each row. [1, 1] in this case.
});

Async/await

try {
    const result = await pool.batchUpdate('INSERT INTO FOO (FIELD1, FIELD2) VALUES(?,?)', data);
    console.log(result);
    // result is the number of updated rows for each row. [1, 1] in this case.
}
catch (error) {
    console.log('error');
    console.log(error);
}

SQL stream

pool
  .createReadStream('SELECT FIELD1, FIELD2 FROM FOO WHERE BAR=? AND BAZ=?', [1, 'a'])
  .pipe(JSONStream.parse([true]))
  .pipe(pool.createWriteStream('INSERT INTO FOO2 (F1, F2) VALUES(?, ?)'));

Transactions

Transaction is commited on success and rolled back on failure. The transaction object has the same api as the pool object.

pool.transaction(transaction => {
	const fooId = 1;
	
	return transaction.update('INSERT INTO FOO (FOOID, FIELD2) VALUES(?,?)', [fooId, 'a']).then(function() {
		return transaction.update('update BAR set FOOID=? where BARID=?', [fooId , 2])
	});
});

Complex types

The node-jt400 module handles strings, longs, doubles and nulls automatically as types. When using other types like CLOB you need to specify the type specifically.

pool
  .update('INSERT INTO foo (fooid, textfield, clobfield) VALUES(?, ?)', [1, 'text', {type:'CLOB',value:'A really long string'}])
  .then(() => {
    console.log('updated');
});

Filesystem

IFS read

const ifs = pool.ifs();
const readStream = ifs.createReadStream('/foo/bar.txt') // readStream from IFS

As with any readable stream you can pipe it wherever you want. For example into the node filesystem.

const createWriteStream = require('fs').createWriteStream
const join = require('path').join
const filename = join(__dirname, 'old.txt')
const writeStream = createWriteStream(filename) // writeStream to nodeJS filesystem.

const ifs = pool.ifs();
const readStream = ifs.createReadStream('/new.txt') // Reading bar.txt from IFS

readStream.pipe(writeStream)  // Piping from IFS to nodeJS

IFS write

const ifs = pool.ifs();
const writeStream = ifs.createWriteStream(('/foo/bar.txt')

As with any other writable streams you can pipe a readable stream into it.

const fs = require('fs').createReadStream
const join = require('path').join
const filename = join(__dirname, 'old.txt')
const readStream = createReadStream(filename) // readStream from nodeJS filesystem

const ifs = pool.ifs();
const writeStream = ifs.createWriteStream('/new.txt')

readStream.pipe(writeStream) // Piping from nodeJS to IFS

You can see more examples in issue #27

IFS delete

const ifs = pool.ifs();
ifs.deleteFile('/foo/bar.txt.old').then(console.log); // true or false

Programs

With programs it is neccesary to define your input parameters first. These must match your program defination in AS.

const myProgram = pool.defineProgram({
  programName: 'MYPGM',
  paramsSchema: [
    { type: 'DECIMAL', precision: 10, scale: 0, name: 'myId'},
    { type: 'NUMERIC', precision: 8,  scale: 0, name: 'myDate'},
    { type: 'NUMERIC', precision: 12, scale: 2, name: 'myTotalValue' },
    { type: 'CHAR',    precision: 32, scale: 0, name: 'myString'}
  ],
  libraryName: 'WTMEXC' // Optional. Defaults to *LIBL
);

The Decimal type maps to com.ibm.as400.access.AS400PackedDecimal The Numeric type maps to com.ibm.as400.access.AS400ZonedDecimal Everything else (char) maps to com.ibm.as400.access.AS400Text Precision is the size and scale is the decimals.

ATTENTION: To make the API clearer we renamed .pgm to .defineProgram. The pgm function is depricated in v3.0

When you have defined your program, you can call/invoke it with the parameters you defined.

myProgram(
  {
    myId: 123
    myDate: '20170608',
    myTotalValue: 88450.57,
    myString: 'This is a test'
  },
  10 // Optional timeout in sec
)
.then(result => {
  console.log(result)
});

ATTENTION: In version 3.0 we added a optional timeout parameter for program calls. This defaults to 3 sec. This is a breaking change since your programs will no longer halt or hang for extended period and therefore never give a response. If you have complicated programs that run for longer than 3 sec then you need to adjust the timeout parameter for those specific calls. Setting it to 0 will ignore the timeout limit.

Keyed Data Queues

IBM KeyedDataQueue Reference

    const jt400 = pool(jt400config);
    // Open a keyed data queue for reading
    let queue = jt400.createKeyedDataQ({name}); 
    // -1 waits until a message exists. (MSGW)
    let m = await queue.read({key:inboxKey,wait:2}); 

Message Queues

IBM MessageQueue Reference

    const path = '/QSYS.LIB/'+process.env.AS400_USERNAME+'.MSGQ';
    const msgq = await jt400.openMessageQ({ path: path });
    const testMessage = 'Test Message';
    await msgq.sendInformational(testMessage); // Writes a basic message
    await msgq.read();

Message Files

IBM AS400Message Reference

const file = await pool.openMessageFile({path:"/QSYS.LIB/YOURLIB.LIB/YOURMSGF.MSGF"});
let msg = await file.read({messageId:"AMX0051"}); // an IBM AS400Message Object
console.log('msg',msg.getTextSync());
console.log('msg',await msg.getTextPromise());