Bug 6742

PowerBuilder vs Informix – characters in different coding than supported by the database 09 October, 2021

Jiri Ptacek
08 June, 2021
Product: PowerBuilder Category: DataWindow
Version: 2019 Build: 2170
Classification: Publishing: Public
Priority: P3
Status: Reproducing Reason:
Communication Status: Waiting for Customer
Mark Lee @Appeon 09 October, 2021
#7
Hi Jiri,

Sorry to let you know the following:
1. According to your latest reply, we set Informix database to code page CP-1252 in English OS, no matter is 1 or 0, it is always successful in executing the following statement on both the server and client.
update eo_jmeno_cis SET poznamka = 'a˜b' where jm = 'Iva'

The result is the same with both the native driver and ODBC.
(the error we got in the previous test was when the code page was set to CP-1250)
 
2. According to our internal review and analysis, PB itself doesn't include IBM Informix Client SDK. It relies on the ClientSDK installed on the client to connect to the Informix database.
Theoretically, if you have no problem connecting to your Informix database with ODBC, it shouldn't have any problem connecting with the native driver.
 
As we can't reproduce the issue, please let us know what settings should be enforced on the client-side besides setting EILSEQ_COMPAT_MODE to 0 on the server database?
 
Additionally, could you please provide a VM that could duplicate the issue for further analysis?

Regards,
Mark Lee
Mark Lee @Appeon 12 July, 2021
#6
Hi Jiri,

Thanks for your feedback.
We tested it with the native driver. I will verify it in our environment according to the information you provided again and will keep you updated here. Thanks in advance.

Regards,
Mark Lee
Jiri Ptacek 12 July, 2021
#5
Hi Mark Lee,
are you sure that you tested it with the native driver and not ODBC? ODBC works perfectly but there is a problem when the customer uses the native driver.
For locates settings they use cs_cz.1250. It works identically when they set it explicitelly in PB or they do not set it.Settings: SQLCA.DBParm = DB_Locale='cs_cz.1252',Client_Locale='cs_cz.1252'"

The text cs_cz.cp1250, you mentioned is wrong in our opinion (there is additional cp). When they tried to use the value cs_cz.cp1250 in locales settings, they received an error:
------------------------
Appeon PowerBuilder
---------------------------
SQL Error -23101 : Unable to load locale categories.
---------------------------
OK 
---------------------------

Thank you in advance for your response.
Best regards
Jiri
Mark Lee @Appeon 08 July, 2021
#4
Hi Jiri, 

Are you still facing this matter? 
Is there anything else we can do to keep helping?

Regards,
Mark Lee
Mark Lee @Appeon 26 June, 2021
#3
test result

Hi Jiri,

We deeply analyzed it recently and found that no matter we set EILSEQ_COMPAT_MODE to 1 or 0, the database error occurs on PB as well as other technologies (see the attachment). Thus we suspect that the issue might be because of the Informix database configuration.
 
Please confirm whether the Informix Environment configurations of your server-side and client-side are both the below settings:
SET DB_LOCALE=cs_cz.cp1250
SET CLIENT_LOCALE=cs_cz.cp1250
If not, please let us know your actual configuration settings so that we could reproduce this issue on our side. Thanks in advance.

Regards,
Mark Lee
Mark Lee @Appeon 10 June, 2021
#2
Hi Jiri,

Thanks for report the problem.
We will be working on analyzing/reproducing your case. 
We will keep you posted on the results here.

Regards,
Mark Lee
Chris Pollach @Appeon 08 June, 2021
#1
Hi Jiri;

  FYI: The Appeon Community has an interesting thread on PB based Informix drivers:
https://community.appeon.com/index.php/qna/q-a/pb-2019-and-informix-database-connection

  Since I do not have an Informix test machine, I will now transfer this ticket over to the main support team for their evaluation and testing of your issue. 

Regards ... Chris
Jiri Ptacek 08 June, 2021
Hi,
Informix database is set to code page CP-1250 for Czech characters. Code page of the client is also set to CP-1250.
In the database they have a parameter EILSEQ_COMPAT_MODE set to 0, this should call database error, in case someone will try to enter characters to the database, which do not correspond to CP-1250. This settings works with other technologies as expected except PB. In this case without any error/warning it will save CHAR value into the database with wrong character. This leads to problems with the next data processing. SELECT of this wrong data returns value '(err)'.
Connect string from Preview in Database Painter:
// Profile elektra_csobsp_9430
SQLCA.DBMS = "I10 INFORMIX-10.0"
SQLCA.Database = "csobsp_9430"
SQLCA.UserId = "aris"
SQLCA.DBPass = <*******>
SQLCA.ServerName = elektra.tc.local@elektra1
SQLCA.AutoCommit = False
SQLCA.DBParm = ""

The issue can be reproduced by the update of value with character chr(152) in the database.
In DatabasePainter it can be done by key sequence LEFT ALT + 0152. It is a char in a value for comment between characters a, b (in SQL session v DatabasePainter this char is not visible):
update eo_jmeno_cis SET poznamka = 'a˜b' where jm = 'Iva'

In database we can see char 152 (hexa 98)

They need to manage the situation where the user is not able to enter wrong chars to the database. Ideally they would like to manage it by settings EILSEQ_COMPAT_MODE = 0. As a good solution they would also accept to manage it by some setting in PB in case that the reason of this wrong behavior will not be fixed.
---------------------------
They have also an answer from IBM support:
------------------------------
We understand you are facing issues with 'Appeon Power Builder's native driver', this Driver belongs to 'Appeon'. So, could you Please raise a Ticket with 'Appeon Support'.
 
Informix Drivers (older) also allowed Illegal characters insertions, because there is No check done to verify the Characters during Insert in older versions. But now, in recent Informix version each Character Insertions are matched with the Character set of Locale and so you see a Error while inserting Illegal characters. If you want to allow Illegal character insertion in Informix, EILSEQ_COMPAT_MODE should be set as 1 so that we revert to Old legacy behaviour.
 
 {{###################################################################
1.	Character Processing Configuration Parameter
###################################################################
2.	EILSEQ_COMPAT_MODE - Controls whether when processing characters,
3.	the server checks if the characters are valid for
4.	the locale and returns error -202 if they are
5.	not. Acceptable values are:
6.	0 Return an error for characters that are not
7.	valid (Default)
8.	1 Allow characters that are not valid
####################################################################}}
OS:
Windows 10
Platform:
64-bit
Database Type:
IBM Informix
Database Version:
Informix server 14.10.FC5, Client clientsdk.4.10.TC14