pgm-call: result-xml structure corrupt due to randomly added characters (urgent) #123

nedi-dev · 2020-08-04T11:01:17Z

Node.js version: 12.14.0
itoolkit version: 1.0.0
IBM i version: 7.3
XMLSERVICE version: XML Toolkit 2.0.2-dev

Describe the bug
The node-script requests IBM i DB-Data via a call to rpg-pgm.
As working with daily-data for one month the call to the program is added to a toolkit conn-object for 30/31 times.
Running the con results in a successful execution but gives back a corrupted xml-structure (e. g. ... type=a'4p2' ..., ... <d ...).
We are using different person-ids in the request. With some PER# the error is reproducible others work fine.
Maybe there is a stringbuffer involved?!?

! Running the script multiple times with identic PERS# results always in the same error at the same position in xml-string.

To Reproduce
see above

Expected behavior
Please locate the bug and provide a corrected version of itoolkit or xmlservice

Files
If needed, we can provide our code snippet and some xml-data.

nedi-dev · 2020-08-04T13:31:02Z

Examples for Errors:

<data type='4p2'>0<,00</data>
</parm>

<parm>
<data type='20A'></data>
<</parm>

<parm>
<data type='4p0'>1279</daita>
</parm>

<parm>
<data type='8p0'>17032020</d<ata>
</parm>

<parm>
<data type='4p0'>1431</data>
</rparm>

<parm>
<data type='4p2'a>15,04</data>
</parm>

nedi-dev · 2020-08-04T13:36:29Z

code for the function
Function.ts.txt

nedi-dev · 2020-08-04T14:41:31Z

Meantime tested with transporttype=odbc > no errors!
Maybe there is some issue in transporttype = idb ?!!

abmusse · 2020-08-04T15:27:55Z

Hello @nedi-dev 👋

Interesting that when using odbc transport you do not run into the errors. Does the error occur every time using idb transport or randomly?

I noticed from your code snippet that you have conn.debug(true) enabled. Does the xml input look malformed on the way in as well?

It would nice to have a simplified example of the RPG program being called (without confidential info) so that the issue can be readily reproduced.

nedi-dev · 2020-08-04T16:29:31Z

Hi @abmusse
The error occurs always when using 'idb' transport. Even the misspelled tag is the same for the same input parameters.
The input-xml is fine in all cases.
Find the abbreviated rpg-source attached.
ZEI109_short.rpglesrc.txt

abmusse · 2020-08-04T20:05:28Z

What version of idb-connector do you have installed?

$ npm ls idb-connector

I'm thinking this could be an issue with dbstmt.fetchAll.

Can you add the following to line 92-93 in lib/transports/idbTransport.js

console.log(results);
console.log('results length: ', results.length);

And check if the xml in the results array is already corrupted before its appended to xmlOutput variable.

nedi-dev · 2020-08-05T10:24:34Z

$ npm list idb-connector
[email protected] /home/NEUMANN/nodejs/trb-pgm-ZEI109
`-- [email protected]
  +-- [email protected]
  `-- [email protected]
    `-- [email protected]  deduped

nedi-dev · 2020-08-05T10:56:00Z

Thinking you are on the right track:
XML Error:
<success><![CDDATA[+++ success LGIZEIT ZEI109 ]]></success>
Duplicated "D" at pos 15!

Output from idbTransport:

      '<parm>\n' +
      "<data type='50A'>Keine Daten vorhanden !</data>\n" +
      '</parm>\n' +
      '<success><![CD'
  },
  {
    OUT151: 'DATA[+++ success LGIZEIT ZEI109 ]]></success>\n' +
      '</pgm>\n' +
      "<pgm name='ZEI109' lib='LGIZEIT' error='fast'>\n" +
      '<parm>\n' +
      "<data type='2p0'>1</data>\n"

Same here:

<parm>
<data type='20A'></dataD>
</parm>

Capital "D" is wrong!

idbTransport:

      '<parm>\n' +
      "<data type='20A'></dataD"
  },
  {
    OUT151: '>\n' +
      '</parm>\n' +

other findings:

    '<parm>\n' +
     "<data type='8p0'>17012020D"
 },
 {
   OUT151: '</data>\n' +
     '</parm>\n' +

      '<parm>\n' +
      "<data type='4p2'>0,00D"
  },
  {
    OUT151: '</data>\n' +
      '</parm>\n' +

      '<parm>\n' +
      "<data type='4p0'>1279</date"
  },
  {
    OUT151: 'a>\n' +
      '</parm>\n' +

ThePrez · 2020-08-05T17:56:47Z

@abmusse , should we transfer this one over to idb-connector?

abmusse · 2020-08-05T18:01:10Z

From your output looks like the data retrieved using idb-connector is already corrupted before we iterate over results array and append to the xmlOutput. So seems to be an issue with idb transport since you tried odbc and did not receive an error.

@ThePrez That would make sense given the issue seems to be caused by idb-connector.

abmusse · 2020-08-05T18:09:17Z

@dmabupt

Any thoughts on what may be causing this issue?

dmabupt · 2020-08-06T10:25:21Z

@dmabupt

Any thoughts on what may be causing this issue?

Seems to be an encoding issue. Need to reproduce it for debugging.

nedi-dev · 2020-08-06T13:33:32Z

This is a good point. The error occurs only for employees with names including "ä ö ü Ä Ö Ü" german special-characters.
Data for other persons seem to be fine.

abmusse · 2020-08-06T14:37:36Z

Interesting I wonder if this issue is related to #70.

dmabupt · 2020-08-07T01:03:27Z

Or try this #20

nedi-dev · 2020-08-07T09:15:52Z

Read through your references, but didn't got a clue how to proceed.
Maybe it's worth mentioning that our scripts run on the same IBM i system, where the database is located.
Is there any character conversion involved? Where?

dmabupt · 2020-08-10T00:04:53Z

Read through your references, but didn't got a clue how to proceed.
Maybe it's worth mentioning that our scripts run on the same IBM i system, where the database is located.
Is there any character conversion involved? Where?

At the beginning of your Node.js code, add process.env.DB2CCSID = '0'; to make your program running with its job CCSID and check if it resolve the problem.

nedi-dev · 2020-08-12T12:11:55Z

At the beginning of your Node.js code, add process.env.DB2CCSID = '0'; to make your program running with its job CCSID and check if it resolve the problem.

Well, the error with malformed xml-tags disappeared ...
... but now we have an issue with special characters (öäü) in the data.
see IBM/node-odbc#79

github-actions · 2020-09-12T00:17:03Z

👋 Hi! This issue has been marked stale due to inactivity. If no further activity occurs, it will automatically be closed.

nedi-dev · 2020-09-14T06:46:53Z

From my point of view this issue is not fixed yet. If we are forced to use DB2CCSID='0', we can not use this connector anymore as the special characters are not processed correctly.
How can we proceed with this?

kadler · 2020-09-22T14:39:51Z

Not sure why the bot closed it - reopening.

nedi-dev · 2020-09-30T09:14:50Z

@kadler please reopen as it is not fixed. What could be the next step from your / our side?

kadler · 2020-09-30T15:10:04Z

Since this is a conversion issue, can you please provide

value of QCCSID
job CCSID
job default CCSID
job langid
job cntryid
PASE locale used (locale)

kadler · 2020-09-30T15:44:42Z

FYI, I asked for locale -a, but that was wrong. I meant just to run locale. I've updated my comment above.

nedi-dev · 2020-10-01T14:36:09Z

value of QCCSID
1141

job CCSID
1141

job default CCSID
1141

job langid
DEU

job cntryid
DE

PASE locale used (locale)
QSH:
LANG=/QSYS.LIB/DE_DE.LOCALE
LC_COLLATE=
LC_CTYPE=
LC_MESSAGES=
LC_MONETARY=
LC_NUMERIC=
LC_TIME=
LC_ALL=

Putty/bash:
LANG=
LC_COLLATE="C"
LC_CTYPE="C"
LC_MONETARY="C"
LC_NUMERIC="C"
LC_TIME="C"
LC_MESSAGES="C"
LC_ALL=

kadler · 2020-10-20T20:39:41Z

I have a hunch that the problem is occurring here: https://github.com/IBM/nodejs-idb-connector/blob/master/src/db2ia/dbstmt.cc#L2477

If the SQLFetch returns an indicator with a valid length (ie. not SQL_NTS or SQL_NULL_DATA), we will copy the data but not null terminate it here: https://github.com/IBM/nodejs-idb-connector/blob/master/src/db2ia/dbstmt.cc#L2418-L2421

Since it's not null-termiated and we're not passing in the length, the Napi::String::New constructor will just keep going until it finds a null-terminator.

dmabupt · 2020-10-21T00:48:10Z

I have a hunch that the problem is occurring here: https://github.com/IBM/nodejs-idb-connector/blob/master/src/db2ia/dbstmt.cc#L2477

If the SQLFetch returns an indicator with a valid length (ie. not SQL_NTS or SQL_NULL_DATA), we will copy the data but not null terminate it here: https://github.com/IBM/nodejs-idb-connector/blob/master/src/db2ia/dbstmt.cc#L2418-L2421

Since it's not null-termiated and we're not passing in the length, the Napi::String::New constructor will just keep going until it finds a null-terminator.

@kadler Maybe we should detect the rlength parameter first? Like --

        default:
          if(resultSetInC[row][col].rlength > 0)
            value = Napi::String::New(env, resultSetInC[row][col].data, resultSetInC[row][col].rlength);
          else
            value = Napi::String::New(env, resultSetInC[row][col].data)
          break;

kadler · 2020-10-21T00:53:56Z

Alternatively, just always set rlength to the proper value here: https://github.com/IBM/nodejs-idb-connector/blob/master/src/db2ia/dbstmt.cc#L2404-L2414

When it's SQL_NTS we already get the length in to colLen (why we don't use strdup here, I'm not sure). For SQL_NULL_DATA, we could set it to 0, and for other negative values it could be set to 1 (in the future, this code should probably handle this case better than returning "-", though).

dmabupt · 2020-10-21T01:02:27Z

Alternatively, just always set rlength to the proper value here: https://github.com/IBM/nodejs-idb-connector/blob/master/src/db2ia/dbstmt.cc#L2404-L2414

When it's SQL_NTS we already get the length in to colLen (why we don't use strdup here, I'm not sure). For SQL_NULL_DATA, we could set it to 0, and for other negative values it could be set to 1 (in the future, this code should probably handle this case better than returning "-", though).

I am not sure about the behavior of Napi::String::New() when the size is 0. I may detect the rlength for now.

kadler · 2020-10-21T01:12:47Z

I'm not sure either. AFAICT it doesn't even mention the ability to pass a NULL pointer.

kadler · 2020-10-21T01:14:04Z

I guess I would set the length correctly always, then pass it in if the pointer is not NULL, otherwise just pass in the NULL pointer.

dmabupt · 2020-10-21T04:14:52Z

Hello @nedi-dev , idb-connector 1.2.10 has been released. You may upgrade to this version and check if it fix the issue.
If not, we still need to recreate the issue on local system first.

nedi-dev · 2020-11-03T16:26:58Z

Thank you for the new release of idb-connector. Unfortunately it took some days to get back to the project.
Even more worse: The error still shows up!

After reinstalling the package [email protected] the npm list looks like this:

bash-4.4$ npm ls idb-connector
`-- [email protected]
  +-- [email protected] 
  `-- [email protected]
    `-- [email protected]  deduped

In the xml you can find:

... 
<ds name='tagDat' dim='4'> 
<datpa type='4p2' name='PKommt'>0,00</data>
<data type='4p2' name='PKommtTat'>0,00</data>
...

mind the : datpa

Please let me know if I can support you recreating the issue on your system.

dmabupt · 2020-12-02T03:20:56Z

Hello @nedi-dev ,
I did a simple test to check if idb-connector can handle the data with CCSID 1141:

Create a table with a column with CCSID 1141

CREATE TABLE UNITAB(C1 CHAR(20) CCSID 1141);
INSERT INTO UNITAB VALUES('öäüABCÅÄÖ');
SELECT * FROM UNITAB;

Query this table using idb-connector

const {dbconn, dbstmt} = require('idb-connector');

const sSql = 'SELECT * FROM UNITAB';
const connection = new dbconn();
connection.conn('*LOCAL');
const statement = new dbstmt(connection);

statement.exec(sSql, (x) => {
  console.log(JSON.stringify(x));
  statement.close();
  connection.disconn();
  connection.close();
});

The output is correct:

$ node index.js 
[{"C1":"öäüABCÅÄÖ           "}]

Your program is a little different. There is a program LGIZEIT/ZEI109 between idb-connector and the backend database.
I think we may need to find out what the backend database is and then try to query the table using idb-connector directly.
We need to check if the problem exist in the program LGIZEIT/ZEI109 or the backend database.

When you get the name of the backend database, you may also collect its column information by the following SQL statement:

SELECT * FROM QSYS2.SYSCOLUMNS WHERE TABLE_NAME='YOURTABLE';

worksofliam · 2021-05-12T16:13:25Z

This has just cropped up for me today. Just another one of those IBM i things.

I have a program call using XMLSERVICE/itoolkit and the program returns a long character string (65k). It is using the idb transport. The long character parameter is a JSON string generated in the program.

See below; the O character should not be there.

I can also see that it is coming back in the XML in the xmlOutput from itoolkit.

  connection.add(programCall);
  connection.run((error, xmlOutput) => {
    console.log(xmlOutput); //I can see that invaid 'O' here.

Also, it can move around and it's not always O. Here's another example of it happening somewhere else:

Another important note is that this only happens when using the idb transport. I've tried to recreate it with ssh and it seems to work perfectly there.

worksofliam · 2021-05-24T14:45:53Z

To reaffirm this issue, I switched the app over to ODBC (node-odbc) and it has resolved the issue.

github-actions · 2022-10-28T02:27:35Z

👋 Hi! This issue has been marked stale due to inactivity. If no further activity occurs, it will automatically be closed.

ThePrez transferred this issue from IBM/nodejs-itoolkit Aug 5, 2020

github-actions bot added the stale label Sep 12, 2020

github-actions bot closed this as completed Sep 22, 2020

kadler reopened this Sep 22, 2020

github-actions bot closed this as completed Sep 30, 2020

kadler removed the stale label Sep 30, 2020

kadler reopened this Sep 30, 2020

github-actions bot added the stale label Oct 28, 2022

abmusse added keep-open and removed stale labels Nov 10, 2022

abmusse mentioned this issue Jun 15, 2023

idb-connector *SP calls returns invalid numeric data -- Node 18, IDB-Connector 1.2.18 #169

Closed

abmusse added the bug Something isn't working label Jun 15, 2023

pgm-call: result-xml structure corrupt due to randomly added characters (urgent) #123

pgm-call: result-xml structure corrupt due to randomly added characters (urgent) #123

Comments

nedi-dev commented Aug 4, 2020

nedi-dev commented Aug 4, 2020 • edited Loading

nedi-dev commented Aug 4, 2020

nedi-dev commented Aug 4, 2020

abmusse commented Aug 4, 2020

nedi-dev commented Aug 4, 2020

abmusse commented Aug 4, 2020 • edited Loading

nedi-dev commented Aug 5, 2020

nedi-dev commented Aug 5, 2020 • edited Loading

ThePrez commented Aug 5, 2020

abmusse commented Aug 5, 2020 • edited Loading

abmusse commented Aug 5, 2020

dmabupt commented Aug 6, 2020

nedi-dev commented Aug 6, 2020

abmusse commented Aug 6, 2020

dmabupt commented Aug 7, 2020

nedi-dev commented Aug 7, 2020

dmabupt commented Aug 10, 2020 • edited Loading

nedi-dev commented Aug 12, 2020 • edited Loading

github-actions bot commented Sep 12, 2020

nedi-dev commented Sep 14, 2020

kadler commented Sep 22, 2020

nedi-dev commented Sep 30, 2020

kadler commented Sep 30, 2020 • edited Loading

kadler commented Sep 30, 2020

nedi-dev commented Oct 1, 2020

kadler commented Oct 20, 2020

dmabupt commented Oct 21, 2020 • edited Loading

kadler commented Oct 21, 2020

dmabupt commented Oct 21, 2020

kadler commented Oct 21, 2020

kadler commented Oct 21, 2020

dmabupt commented Oct 21, 2020

nedi-dev commented Nov 3, 2020 • edited Loading

dmabupt commented Dec 2, 2020 • edited Loading

worksofliam commented May 12, 2021

worksofliam commented May 24, 2021

github-actions bot commented Oct 28, 2022

nedi-dev commented Aug 4, 2020 •

edited

Loading

abmusse commented Aug 4, 2020 •

edited

Loading

nedi-dev commented Aug 5, 2020 •

edited

Loading

abmusse commented Aug 5, 2020 •

edited

Loading

dmabupt commented Aug 10, 2020 •

edited

Loading

nedi-dev commented Aug 12, 2020 •

edited

Loading

kadler commented Sep 30, 2020 •

edited

Loading

dmabupt commented Oct 21, 2020 •

edited

Loading

nedi-dev commented Nov 3, 2020 •

edited

Loading

dmabupt commented Dec 2, 2020 •

edited

Loading