Error SQLSTATE=22504 SQLCODE=-191 Mixed data or UTF-8 data not properly formed. about nodejs-idb-connector HOT 22 CLOSED

@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?

from nodejs-idb-connector.

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?

from nodejs-idb-connector.

What is COL0 defined as?

from nodejs-idb-connector.

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

from nodejs-idb-connector.

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

from nodejs-idb-connector.

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.

from nodejs-idb-connector.

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 [email protected] [email protected] [email protected]

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 [email protected] [email protected] [email protected] 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

from nodejs-idb-connector.

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

from nodejs-idb-connector.

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

from nodejs-idb-connector.

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

from nodejs-idb-connector.

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

from nodejs-idb-connector.

In my run I was using

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

I recreated the table described in: #147 (comment). 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!

from nodejs-idb-connector.

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

from nodejs-idb-connector.

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

from nodejs-idb-connector.

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

from nodejs-idb-connector.

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.

from nodejs-idb-connector.

@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

from nodejs-idb-connector.

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

from nodejs-idb-connector.

@patrickhrastnik

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

from nodejs-idb-connector.

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

from nodejs-idb-connector.

Thoughts?

from nodejs-idb-connector.

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!

from nodejs-idb-connector.