Error SQLSTATE=22504 SQLCODE=-191 Mixed data or UTF-8 data not properly formed.

[abamara]

Hello

I update to idb-connector 1.2.13 but I have some issue : when doing a simple "insert" I have the following error : Error SQLSTATE=22504 SQLCODE=-191.

The insert is very simple : INSERT INTO TABLE(COL0) VALUES ('test') with NONE;

I switched back to idb-connector 1.2.10 and I don't have the issue. I think it's related with correction done in version 1.2.11.

Node.js version : 14.17.2 idb-connector version : 1.2.13 IBM i version: 7.3

[mikebohlen]

Error: SQLSTATE=22504 SQLCODE=-191 Mixed data or UTF-8 data not properly formed. I am on 1.2.16 and NodeJS 16 and still have this issue. Whats the status?

[kadler]

What is COL0 defined as?

[mikebohlen]

COL0 is a decimal. (9,0), and sorry developer working with this has been on another project.

[patrickhrastnik]

Just wondering about the problem description: The sql statement above inserts character data, 'test', into the table. @mikebohlen claims the definition for COL0 is decimal, which is numeric, and can not hold character data

[abamara]

Just wondering about the problem description: The sql statement above inserts character data, 'test', into the table. @mikebohlen claims the definition for COL0 is decimal, which is numeric, and can not hold character data

It's been almost one year. Can't remember how to reproduce the issue.

[kadler]

@patrickhrastnik seems to have run in to this same issue in #141. More details from him starting here

@abmusse and I did try to recreate in-house but were unable to do so. We used an SQL procedure instead of an external procedure, but I'm not sure if that matters. I thought it might have something to do with INOUT parameters (since CLI has some goofiness when it comes to output and inout parameter length handling), but in the original problem identified here, it was an input only parameter, so it seems like that's maybe not the case.

In the example from @patrickhrastnik we also don't have any non-ASCII characters, so there shouldn't be any issue with string length changes after converting from or to UTF-8.

@patrickhrastnik is there any other details you can share about the system you're hitting the problem on? What's the IBM i version and DB group level?

[patrickhrastnik]

IBM i OS version is V7R4M0 The most recent installed PTF group is SF99740 ... I think this is the information you were asking for, if not I'll check back with our IBM i admin for further details

Regarding non-UTF-8 characters: The request data and response XML01 gives may include German special characters, which are "äöüÄÖÜß" and described in a 2-byte format in UTF-8 With the data provided in #141 the response contains such a character This is some debug session data from our working instance running node@12.22.1 idb-pconnector@1.0.8 idb-connector@1.2.9

result.outputParams
0:
'XCHKONLANM          '
1:
13199
2:
'Logindaten ungütlig                                                                                '
3:
24695
4:
'<?xml version='1.0' encoding='ISO-8859-1' ?><rcv> <data> <oida></oida> <homa></homa> <fnr>13199</fnr></data></rcv>'

I might be wrong, but to me it looks like the version running node@16.11.1 idb-pconnector@1.1.0 idb-connector@1.2.16 doesn't even call XML01. I put a service entry debug point in RDi against XML01: With node12 it's reached, with node16 not. So the response data might not be part of the issue.

Some further details about the RPGLE program XML01 (it's shown to me in German, I'll do my best translating it to English)

Program type: ILE
Program attribute: RPGLE
CCSID: 65535
Active Teraspace for PEP: *yes
Active Teraspace for Module: *all
Storage model: *snglvl

For module XML01 contained in program XML01:
Module attribute: RPGLE
CCSID: 273
Language ID: *jobrun
Active Teraspace: *yes
Storage model: *snglvl
Commit control: *none
SQL Language ID: DEU
SQL path: *libl

Some job attributes, because some parameters are declared as *jobrun:
Language ID: DEU
Country or Region ID: AT
CCSID: 273
Default CCSID: 273
Control for Character ID: *devd

[patrickhrastnik]

I'll be out of office til Jul 18. @kadler if you have further questions please tag @work-nassertee

[mikebohlen]

I believe we can recreate this issue at will and should be able to provide examples: Let me know if this is something that would help to get this resolved. Thanks

[abmusse]

@mikebohlen Yes providing a reproducible example would very useful getting this resolved.

[mikebohlen]

insert into wbpahdr (ahUsId, ahApId, ipAddr, usrAgnt, sessId, refer, opSys, tchScr, mobDev, ahEnTs) values( 'KRINJ', 15, '127.0.0.1', 'PostmanRuntime/7.29.0', '59F11BD77D246F7E49717F4B3EA48CDE', '', 'Windows', 'Y', 'Y', '2022-06-23-10.05.43.859000' ) Name Type Length Scale ID AHACID DECIMAL 9 0 YES AHUSID CHAR 65 0 NO AHAPID DECIMAL 9 0 NO IPADDR CHAR 39 0 NO USRAGNT VARCHAR 512 0 NO SESSID CHAR 100 0 NO REFER VARCHAR 2000 0 NO OPSYS VARCHAR 64 0 NO TCHSCR CHAR 1 0 NO MOBDEV CHAR 1 0 NO AHENTS TIMESTMP 10 0 NO

[abmusse]

In my run I was using

• idb-pconnector: 1.1.0
  • idb-connector: 1.2.16

I recreated the table described in: https://github.com/IBM/nodejs-idb-connector/issues/147#issuecomment-1164786636. Then ran an insert with the values provided. All works fine on my end. I was not able to reproduce the error.

@mikebohlen Can you provide reproducible code snippet? Thanks!

[staplJason]

@mikebohlen out of curiosity if you put a value in place of the empty string - does it work?

[mikebohlen]

Our developer noted we use parameter binding nothing hardcoded like maybe thats where the issue lies. He is trying to get some code worked up for you. He suggest try it with parameter binding and see if it fails more there. Thanks

[patrickhrastnik]

I created an isolated test case. It is built on idb-pconnector, but I hope it gives a chance to find the underlying issue. https://github.com/HolterDev/idbc-issue-147 Still working on a version using only idb-connector, will push when finished

[patrickhrastnik]

I finished two implementations using idb-connector with the outdated bindParam function and the new bindParameters function and discovered something that might be of interest. The RPG procedure in the example source code returns without touching the supplied parameters, so the returns are basically reflecting the input data. But with the character parameters some uncleared memory / data chunks seem to be attached to the values. I'm wondering if this is a problem with idb-pconnector, even though idb-connector seems to be able to deal with this data without raising an exception.

[patrickhrastnik]

@abmusse Can you please check whether you can reproduce the observed problems with the sample code provided in https://github.com/HolterDev/idbc-issue-147 ? Behavior is consistent for me, reproduced it today

[abmusse]

@patrickhrastnik Thanks for creating the sample code. I'll try to run it and report back.

[abmusse]

@patrickhrastnik

Both packages have been updated. Is the issue resolved for you?

[mikebohlen]

the sql state error seems to be fixed, but we have a different issue any numeric values being returned from a stored procedure are not formatted as they were before. numbers come back in the result set looking like this: 36367754\u0000

[mikebohlen]

Thoughts?

[patrickhrastnik]

I am happy to say that @abmusse 's fix solved all issues on our end. The numeric bug discovered by @mikebohlen doesn't seem to affect our installation. Probably because we don't use the built-in numeric type conversion - just a wild guess. Issue solved - Thanks!