iSql no result set

[Brian Jerome]

I'm revisiting this project to use in production since the db2sock is in limbo.

I am trying to make a simple sql query, but nothing is returned.

let conn = new xt.iConn('*LOCAL');
...
let sql = new xt.iSql();  /* Test iSql Class */

sql.prepare("call qsys2.tcpip_info()");
sql.execute();
sql.fetch();
sql.free();
conn.add(sql);
conn.run((str) => {console.log(str)});

I can run this with strsql and I do get a result set, but when running it with the iToolkit QXMLSERV I don't receive a result.

============
INPUT XML
============
<?xml version='1.0'?><myscript><sql><prepare error='fast'>call qsys2.tcpip_info()</prepare><execute error='fast'></execute><fetch block='all' desc='on'></fetch><free></free></sql></myscript>
============
OUTPUT XML
============
SQLConnect: conn obj [1802fafd0] handler [18]
PrepareAsync().
SQLPrepare(0): call QXMLSERV.iPLUG512K(?,?,?,?)
BindParamAsync().
SQLBindParameter(0) TYPE[ 1] SIZE[1024] DIGI[0] IO[1] IND[ -3]
SQLBindParameter(0) TYPE[ 1] SIZE[1024] DIGI[0] IO[1] IND[ -3]
SQLBindParameter(0) TYPE[14] SIZE[524288] DIGI[0] IO[1] IND[190]
SQLBindParameter(0) TYPE[14] SIZE[524288] DIGI[0] IO[2] IND[524288]
ExecuteAsync().
SQLExecute(-1):

SQLDisconnect: conn obj [1802fafd0] handler [18]

[Aaron Bartell]

Set your error to be "on" to get a full job log of the issue. Learn how to do that on this slide: http://bit.ly/2N9QcQR

[Brian Jerome]

Aaron,

I set the error: 'on' option for the prepare and execute tags but there doesn't appear to be errors. Am I missing something else?

isql.prepare('call qsys2.tcpip_info()', {'error':'on'});
isql.execute(null, {'error':'on'});

I get similar output xml with the debug:

============
INPUT XML
============
<?xml version='1.0'?><myscript><sql><prepare error='on'>call qsys2.tcpip_info()</prepare><execute error='on'></execute><fetch block='all' desc='on'></fetch><free></free></sql></myscript>
============
OUTPUT XML
============
SQLConnect: conn obj [18032bc90] handler [18]
PrepareAsync().
SQLPrepare(0): call QXMLSERV.iPLUG512K(?,?,?,?)
BindParamAsync().
SQLBindParameter(0) TYPE[ 1] SIZE[1024] DIGI[0] IO[1] IND[ -3]
SQLBindParameter(0) TYPE[ 1] SIZE[1024] DIGI[0] IO[1] IND[ -3]
SQLBindParameter(0) TYPE[14] SIZE[524288] DIGI[0] IO[1] IND[186]
SQLBindParameter(0) TYPE[14] SIZE[524288] DIGI[0] IO[2] IND[524288]
ExecuteAsync().
SQLExecute(-1):

SQLDisconnect: conn obj [18032bc90] handler [18]

[Aaron Bartell]

Am I missing something else?

Hmm... not sure. I've not used it on iSQL before so I am not sure if the Javascript object maybe isn't getting passed up the foodchain correctly?

[Brian Jerome]

I only have the compiled source so I'm not sure how else I could debug this issue on my end.

Can someone else please try the iSql example from the master Example 1 of this repo?

[Christian Jorgensen]

I just installed the itoolkit package and ran the master Example 1 - and it ran without any errors.

I also ran your test from the first comment in this thread - and this ran without errors as well. The output from your test running on our system was this (hostname removed):

<?xml version='1.0'?><myscript><sql>
<prepare error='fast' conn='conn1' stmt='stmt1'>
<success><![CDATA[+++ success call qsys2.tcpip_info()]]></success>
</prepare>
<execute error='fast' stmt='stmt1'>
<success>+++ success stmt1</success>
</execute>
<fetch block='all' desc='on' stmt='stmt1'>
<row><data desc='HOSTNAME'>zzzzzzzzzzzzzzzzzz</data><data desc='VRM'>V7R3M0</data><data desc='DBGROUP'>SF99703 10</data><data desc='IPTYPE'>NONE</data><data desc='IPADDR'>UNKNOWN</data><data desc='PORT'>0</data></row>
<success>+++ success stmt1</success>
</fetch>
<free><success>+++ success </success>
</free>
</sql>
</myscript>

My node version is v8.11.3...

[Brian Jerome]

@chrjorgensen Thanks for trying this.

My node version is 8.10.0, but I doubt that'd have an effect. Also have the latest (0.1.3) version of itoolkit.. hmm.. I will look into this more when I get time..

Going to leave this issue open and continue to document findings.

[Christian Jorgensen]

@bjerome I would suggest the following:

• You may add the statement conn.debug(true); to have more debug information displayed on the console when your node program is running.
• You could try to access the view QSYS2.TCPIP_INFO instead of calling the stored procedure QSYS2.TCPIP_INFO(). The stored procedure is not well documented in contrast to the UDTF.
• If you just need SQL, you can use the module idb-connector instead of this toolkit module, which is intended for accessing native IBM i objects.
• You could try some of the other classes in the toolkit to see whether they are performing as expected. If not, it may be a server issue, ie. XMLSERVICE program on the server, which can be corrected by a PTF (our server has fully updated group PTF's, e.g. SF99703 level 10).

Just some thoughts - hope you make it work...

[Brian Jerome]

It seems the Example 2 is not working either. But this looks like a toolkit runtime error.

var xt = require('/QOpenSys/pkgs/lib/node_modules/itoolkit');
var wk = require('/QOpenSys/pkgs/lib/node_modules/itoolkit/lib/iwork');
var nt = require('/QOpenSys/pkgs/lib/node_modules/itoolkit/lib/inetwork');

var conn = new xt.iConn("*LOCAL");
conn.debug(true);

var work = new wk.iWork(conn);
var net = new nt.iNetwork(conn);

work.getSysValue("QCCSID", (output) => {
  console.log("QCCSID = " + output);
});

net.getTCPIPAttr((output) => {
  console.log(JSON.stringify(output, " ", 2));
})

============
INPUT XML
============
<?xml version='1.0'?><myscript><pgm name='QWCRSVAL' lib='QSYS' error='fast'><parm io='out'><ds len='rec1'><data type='10i0'>0</data><data type='10i0'>0</data><data type='10A'></data><data type='1A'></data><data type='1A'></data><data type='10i0'>0</data><data type='10i0'>0</data></ds></parm><parm><data type='10i0' setlen='rec1'>0</data></parm><parm><data type='10i0'>1</data></parm><parm><data type='10A'>QCCSID</data></parm><parm io='both'><ds len='rec2'><data type='10i0'>0</data><data type='10i0' setlen='rec2'>0</data><data type='7A'></data><data type='1A'></data></ds></parm></pgm></myscript>
============
OUTPUT XML
============
SQLConnect: conn obj [1802d8e90] handler [2]
PrepareAsync().
============
INPUT XML
============
<?xml version='1.0'?><myscript><pgm name='QTOCNETSTS' lib='QSYS' func='QtocRtvTCPA' error='fast'><parm io='out'><ds len='rec1'><data type='10i0'>0</data><data type='10i0'>0</data><data type='10i0'>0</data><data type='10i0'>0</data><data type='8A'></data><data type='6A'></data><data type='8A'></data><data type='6A'></data><data type='10A'></data><data type='10A'></data><data type='6A'></data><data type='16h'></data><data type='10A'></data><data type='10A'></data><data type='6A'></data><data type='16h'></data><data type='10i0'>0</data><data type='10i0'>0</data><data type='10i0'>0</data><data type='10i0'>0</data><data type='10i0'>0</data><data type='10i0'>0</data><data type='10i0'>0</data><data type='10i0'>0</data><data type='10i0'>0</data><data type='10i0'>0</data><data type='10i0'>0</data><data type='10i0'>0</data><data type='64A'></data><data type='255A'></data><data type='1A'></data><data type='256A'></data></ds></parm><parm><data type='10i0' setlen='rec1'>0</data></parm><parm><data type='8A'>TCPA0300</data></parm><parm io='both'><ds len='rec2'><data type='10i0'>0</data><data type='10i0' setlen='rec2'>0</data><data type='7A'></data><data type='1A'></data></ds></parm></pgm></myscript>
============
OUTPUT XML
============
SQLConnect: conn obj [1802db4b0] handler [6]
SQLPrepare(0): call QXMLSERV.iPLUG512K(?,?,?,?)
PrepareAsync().
BindParamAsync().
SQLBindParameter(0) TYPE[ 1] SIZE[1024] DIGI[0] IO[1] IND[ -3]
SQLBindParameter(0) TYPE[ 1] SIZE[1024] DIGI[0] IO[1] IND[ -3]
SQLBindParameter(0) TYPE[14] SIZE[524288] DIGI[0] IO[1] IND[599]
SQLBindParameter(0) TYPE[14] SIZE[524288] DIGI[0] IO[2] IND[524288]
ExecuteAsync().
SQLPrepare(0): call QXMLSERV.iPLUG512K(?,?,?,?)
BindParamAsync().
SQLBindParameter(0) TYPE[ 1] SIZE[1024] DIGI[0] IO[1] IND[ -3]
SQLBindParameter(0) TYPE[ 1] SIZE[1024] DIGI[0] IO[1] IND[ -3]
SQLBindParameter(0) TYPE[14] SIZE[524288] DIGI[0] IO[1] IND[1203]
SQLBindParameter(0) TYPE[14] SIZE[524288] DIGI[0] IO[2] IND[524288]
ExecuteAsync().
SQLExecute(-1):
SQLExecute(-1):
/QOpenSys/pkgs/lib/node_modules/itoolkit/lib/iwork.js:106
                if(output[0].hasOwnProperty("success") && output[0].success == true)
                             ^

TypeError: Cannot read property 'hasOwnProperty' of undefined
    at toJson (/QOpenSys/pkgs/lib/node_modules/itoolkit/lib/iwork.js:106:16)
    at /QOpenSys/pkgs/lib/node_modules/itoolkit/lib/istoredp.js:89:15

[Christian Jorgensen]

Hi @bjerome

I ran the sample no 2 (copied and pasted from your comment) on our server - without any problem.

The only issue I had was that the code references a global install of itoolkit, whereas my test project has a local install. So I changed the lines

var xt = require('/QOpenSys/pkgs/lib/node_modules/itoolkit');
var wk = require('/QOpenSys/pkgs/lib/node_modules/itoolkit/lib/iwork');
var nt = require('/QOpenSys/pkgs/lib/node_modules/itoolkit/lib/inetwork');

to the following:

var xt = require('itoolkit');
var wk = require('itoolkit/lib/iwork');
var nt = require('itoolkit/lib/inetwork');

That makes me wonder whether your problems stem from a global, outdated install of itoolkit? You could start from scratch in a new folder and install the itoolkit locally and run your samples again to check whether the global itoolkit module is the culprit.

If that not helps I would check the IBM i server for updates - both the normal group PTF's and the programs in /QOpenSys/pkgs/bin. My versions of node and npm are

#!

→ node -v
v8.11.3
→ npm -v
6.2.0

If your versions are lower, then you should update and start your test project from scratch as mentioned above.

HTH.

Best regards, Christian

[Brian Jerome]

The global install is the version that matches the npm package master.

-bash-4.4$ npm -g list | grep itoolkit
+-- itoolkit@0.1.3

Installing it locally gave the same version.npm.

I've decided to use the idb-pconnector to make the SQL calls and QXMLSERV instead since I already had that set up from working on it. I'll just use the iPgm to write the xml.

Thanks for your assistance.

[Brian Jerome]

Not reproducible so marking resolved.