Re: [IBMiOSS] Using Nodev6 db2i/itoolkit

Hi Kevin,

There's something erroring when it runs the SQL (i.e. "SQLExecute(-1)") on
your system. Maybe check QSYSOPR for messages and also check WRKACTJOB for
issues.

I run the following example successfully.

var xt =
require('/QOpenSys/QIBM/ProdData/OPS/Node6/os400/xstoolkit/lib/itoolkit')
var conn = new xt.iConn("*LOCAL")
conn.debug(true)
conn.add(xt.iSh("system -i 'DSPSYSSTS'"))
conn.run((str) => {
console.log(str)
})


============
INPUT XML
============
<?xml version='1.0'?><myscript><sh error='fast'>system -i
'DSPSYSSTS'</sh></myscript>
============
OUTPUT XML
============
SQLConnect: conn obj [309d85b0] handler [2]
PrepareAsync().
undefined

SQLPrepare(0): call QXMLSERV.iPLUG512K(?,?,?,?)

BindParamAsync().
SQLBindParameter [1] = undefined
SQLBindParameter [2] = undefined
SQLBindParameter [3] = <?xml version='1.0'?><myscript><sh
error='fast'>system -i 'DSPSYSSTS'</sh></myscript>
SQLBindParameter [4] =
ExecuteAsync().
SQLExecute(0):
<?xml version='1.0'?><myscript><sh error='fast'>
System Status Information
Page 1
5770SS1 V7R3M0 160422
KT4001 03/30/18 12:00:54 EDT
% CPU used . . . . . . . . . . . . . . : 7.5 System
ASP . . . . . . . . . . . . . . : 534.4 G
Elapsed time . . . . . . . . . . . . . : 00:00:01 %
system ASP used . . . . . . . . . . : 30.3280
Jobs in system . . . . . . . . . . . . : 268 Total
aux stg . . . . . . . . . . . . : 534.4 G
% perm addresses . . . . . . . . . . . : .009 Current
temporary used . . . . . . . . : 8149 M
% temp addresses . . . . . . . . . . . : .093 Peak
temporary used . . . . . . . . . : 8468 M
Sys Pool Reserved Max -------DB------- -----Non-DB-----
Act-> Wait-> Act-> Paging
Pool Size M Size M Act Faults Pages Faults Pages
Wait Inel Inel Pool Subsystem Option
1 1122.69 577.32 +++++ .0 .0 .0 .0
52.2 .0 .0 *MACHINE *FIXED
2 4345.49 7.56 102 .0 .0 1.7 1.7
13954.7 .0 .0 *BASE *CALC
3 614.37 .00 154 .0 .0 .0 .0
.0 .0 .0 *INTERACT *CALC
4 61.43 .00 5 .0 .0 .0 .0
.0 .0 .0 *SPOOL *CALC
* * * * * E N D O F L I S T I
N G * * * * *
</sh>
</myscript>
SQLDisconnect: conn obj [309d85b0] handler [2]


Aaron Bartell
IBM i hosting, starting at $157/month. litmis.com/spaces


On Fri, Mar 30, 2018 at 11:02 AM, Kevin Nunn <nunn.kevin@xxxxxxxxx> wrote:

Hi Aaron,

I changed that and ended up with the exact same error.

[bash]$node cltest.js
CL statement : 'DSPSYSSTS'
============
INPUT XML
============
<?xml version='1.0'?><myscript><sh error='fast'>system -i
'DSPSYSSTS'</sh></myscript>
============
OUTPUT XML
============
SQLConnect: conn obj [309d29f0] handler [2]
PrepareAsync().
SQLPrepare(-1): call QXMLSERV.iPLUG512K(?,?,?,?)
BindParamAsync().
SQLBindParameter [1] = undefined
SQLBindParameter [2] = undefined
SQLBindParameter [3] = <?xml version='1.0'?><myscript><sh
error='fast'>system -i 'DSPSYSSTS'</sh></myscript>
SQLBindParameter [4] =
ExecuteAsync().
SQLExecute(-1):
undefined:0



Error: SQLSTATE=PAERR SQLCODE=-1 SQLDescribeParam() failed.
at Error (native)


On Fri, Mar 30, 2018 at 7:39 AM, Aaron Bartell <aaronbartell@xxxxxxxxx>
wrote:

Hi Kevin,

Try the below. Note I added single quotes inside the double quotes.

var cl = "'DSPSYSSTS'";


Aaron Bartell
IBM i hosting, starting at $157/month. litmis.com/spaces


On Thu, Mar 29, 2018 at 3:40 PM, Kevin Nunn <nunn.kevin@xxxxxxxxx>

wrote:

Here is a simplified version of the CL attempted, along with debug

output.

var xt =
require('/QOpenSys/QIBM/ProdData/OPS/Node6/os400/

xstoolkit/lib/itoolkit');

var DBname = "*LOCAL";
var userId = "user";
var passwd = "pass";
var cl = "DSPSYSSTS";

console.log("CL statement : " + cl);
var conn = new xt.iConn(DBname, userId, passwd);
conn.debug(true);
conn.add(xt.iSh("system -i " + cl));
function cb(rs) {console.log(str);}
conn.run(cb);

------------------------------------------------------
[bash]$node cltest.js
CL statement : DSPSYSSTS
============
INPUT XML
============
<?xml version='1.0'?><myscript><sh error='fast'>system -i
DSPSYSSTS</sh></myscript>
============
OUTPUT XML
============
SQLConnect: conn obj [309d2e50] handler [2]
PrepareAsync().
SQLPrepare(-1): call QXMLSERV.iPLUG512K(?,?,?,?)
BindParamAsync().
SQLBindParameter [1] = undefined
SQLBindParameter [2] = undefined
SQLBindParameter [3] = <?xml version='1.0'?><myscript><sh
error='fast'>system -i DSPSYSSTS</sh></myscript>
SQLBindParameter [4] =
ExecuteAsync().
SQLExecute(-1):
undefined:0



Error: SQLSTATE=PAERR SQLCODE=-1 SQLDescribeParam() failed.
at Error (native)
-----------------------------------------------------------


On Thu, Mar 29, 2018 at 3:23 PM, Kevin Nunn <nunn.kevin@xxxxxxxxx>

wrote:

Well, chop me down with a herring!

I left for a meeting, came back and isolated the sql code to

simplify

it

and see if I break it down and figure it out. I was going to show a
co-worker the SQL problem and boom, it worked, and I have no idea

why!

But

the CL part of it is still giving me the SQLDescribeParam() failed

error.

Aaron, I know you are one of the Open Source gurus around here, are

there

any HowTo's to v8 on an iSeries, setting the correct paths and all

that?

It took me a few days to figure out I had to get all the PTFs for

5733OPS

to actually have all the actual software, and then it kept running

Node

v0.12, not v6, but I knew it was installed. So I ended up changing

the

symbolic links manually. I just found the documentation that tells

you

to

run a script to do that today. :P

Thanks,

Kev


On Thu, Mar 29, 2018 at 3:05 PM, Kevin Nunn <nunn.kevin@xxxxxxxxx>

wrote:

Thanks for the reply Aaron

It is version 6, the default install of 5733OPS plus PTFs,

although

I

did

install v8 rpm and then "yum erase"d it. V8 would not run,

probably

due

to

lib path issues and since I am new to all this, I decided it would

be

best

to stick with the defaults. But possibly the install/erase of v8

broke

things?

Below is the code. It's a basic copy/paste of what I found on the

link

listed in the first email. except for the db credentials, etc.

var http = require('http');
var fs = require('fs');
var url = require('url');
var db = require('/QOpenSys/QIBM/ProdData/OPS/Node6/os400/db2i/

lib/

db2a');
var xt = require('/QOpenSys/QIBM/ProdData/OPS/Node6/os400/

xstoolkit/

lib/itoolkit');

var DBname = "*LOCAL";
var userId = "user";
var passwd = "pass";
var ip = "IPaddress";
var port = 8000;

var webserver = http.createServer((req,res) => {
var realPath = __dirname + url.parse(req.url).pathname;
fs.exists(realPath, (exists) => {
if(!exists){
var sql = url.parse(req.url, true).query.sql;
var cl = url.parse(req.url, true).query.cl;
if(sql && sql.length > 0) {
console.log("SQL statement : " + sql);
var dbconn = new db.dbconn();
dbconn.conn(DBname, userId, passwd); // Connect to the DB
var stmt = new db.dbstmt(dbconn);
stmt.exec(sql, (rs) => { // Query the statement
res.writeHead(200, {'Content-Type': 'text/plain'});
res.end(JSON.stringify(rs));
console.log(rs);
stmt.close();
dbconn.disconn();
dbconn.close();
});
}
if(cl && cl.length > 0) {
console.log("CL statement : " + cl);
var conn = new xt.iConn(DBname, userId, passwd);
conn.add(xt.iSh("system -i " + cl));
conn.run((rs) => {
res.writeHead(200, {'Content-Type': 'text/plain'});
res.end(xt.xmlToJson(rs)[0].data);
});
}
} else {
var file = fs.createReadStream(realPath);
res.writeHead(200, {'Content-Type':'text/html'});
file.on('data', res.write.bind(res));
file.on('close', res.end.bind(res));
file.on('error', (err) => {
res.writeHead(500, {'Content-Type':'text/plain'});
res.end("500 Internal Server Error");
});
}
});
});
webserver.listen(port, ip);
console.log('Server running at http://' + ip + ':' + port);


On Thu, Mar 29, 2018 at 2:34 PM, Aaron Bartell <

aaronbartell@xxxxxxxxx>

wrote:

What version of Node.js?

Can you post your code so we know exactly which example you're

talking

about?

FWIW, if you're using Node.js v6.x then I'd upgrade to Node.js

v8.x

and

work through issues with that version.

https://github.com/nodejs/Rele

ase

Aaron Bartell
IBM i hosting, starting at $157/month. litmis.com/spaces


On Thu, Mar 29, 2018 at 2:27 PM, Kevin Nunn <

nunn.kevin@xxxxxxxxx>

wrote:

I am researching db2i and itoolkit by starting with IBM's

examples

at

https://www.ibm.com/developerworks/ibmi/library/i-native-js-

app-ibmi-with-

nodejs/
at not having much luck.

The SQL part of the script is connecting to the DB2 server, but

any

"Select

* From library/db" results in an empty brackets []. If I send

query

"VALUES

current date", I do get [{"00001":"2018-03-29"}] back. If I put

in

bad

credentials, I do get auth errors, so it seems to be connecting

to

the

server.

The same queries work fine from STRSQL.

Also the CL part of the example just returns

Error: SQLSTATE=PAERR SQLCODE=-1 SQLDescribeParam() failed.
at Error (native)

I am new to nodejs, the plan was to find examples for the

iSeries,

watch

them work, then see how the work and start from there.

Also wondering if my other issue might have some relation to

this

problem

(see "Broken npm?" thread in OpenSource).

Thanks

Kev

--
*Kevin Nunn <https://about.me/kevinnunn>*
--
This is the IBMi Open Source Roundtable (OpenSource) mailing

list

To post a message email: OpenSource@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/opensource
or email: OpenSource-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/opensource.

--
This is the IBMi Open Source Roundtable (OpenSource) mailing list
To post a message email: OpenSource@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/opensource
or email: OpenSource-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/opensource.

--
*Kevin Nunn <https://about.me/kevinnunn>*

--
*Kevin Nunn <https://about.me/kevinnunn>*

--
*Kevin Nunn <https://about.me/kevinnunn>*
--
This is the IBMi Open Source Roundtable (OpenSource) mailing list
To post a message email: OpenSource@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/opensource
or email: OpenSource-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/opensource.

--
This is the IBMi Open Source Roundtable (OpenSource) mailing list
To post a message email: OpenSource@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/opensource
or email: OpenSource-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/opensource.

--
*Kevin Nunn <https://about.me/kevinnunn>*
--
This is the IBMi Open Source Roundtable (OpenSource) mailing list
To post a message email: OpenSource@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/opensource
or email: OpenSource-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/opensource.

--
This is the IBMi Open Source Roundtable (OpenSource) mailing list
To post a message email: OpenSource@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/opensource
or email: OpenSource-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/opensource.