II_CHARSETxx on the client v's the server.

[Adrian Williamson]

Hi,

 

When I was child it was installed in my mind that II_CHARSET on the client had to match that on the server as well.

 

Is this still the case?

 

Or has that relaxed over the years?

 

Is it  only really an issue now if you mix single & multibyte character sets?

 

I sense a gap in my knowledge.

 

Cheers

 

Adrian

[Bodo Bergmann]

Hi Adrian,

 

you did not specify what kind of “server” you mean – DBMS Server (Ingres / ActianX) or OpenROAD Server?

Assuming you mean the DBMS Server the answer is: It depends.

 

With mixed single-byte charset environments usually an automatic conversion/transliteration is done for varchar types – but you may have problems with characters that are only supported by one character set.

E.g. if your client’s II_CHARSET is WIN1252 and the server has ISO88591 then the € (Euro symbol) will be converted to something else in the server, as ISO88591 does not contain the € character.

This is ok as long as all clients use WIN1252, as in this case the during reading the symbol will be converted back to the €.
But if you use a client with a different charset, e.g. sql/isql on the server (which has ISO88591) the symbol will be shown as a different character.

 

If you use UTF8 as a server charset, it can represent all characters – but if you have different clients, you may run into similar problems with charaters not supported by the individual client charset.

If you use UTF8 as a client charset, it can represent all characters stored in the server, but not all client characters (for example entered in EntryFields) can be stored in the server.

 

In addition, you cannot develop OpenROAD applications (using the Workbench) within a mixed UTF8/non-UTF8 charset environment.

At runtime you might set the environment variable II_W4GL_ALLOW_MIX_UTF8=TRUE to allow such a connection, but you have to be aware of the possible problems.

 

HTH.

Regards,
Bodo

 

Bodo Bergmann Engineering Architect | OpenROAD Engineering Actian, A Division of HCLSoftware

GESELLSCHAFTSANGABEN: Actian Germany GmbH | Sitz der Gesellschaft: Halenreie 42, 22359 Hamburg | Geschäftsführung: Stephen Padgett, Marc Monahan | Handelsregister: Amtsgericht Hamburg | HRB 135991 | USt-IdNr: DE252449897

--
You received this message because you are subscribed to the Google Groups "OpenROAD Users Mailing List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openroad-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/openroad-users/000c01d9b3da%240fdcc7b0%242f965710%24%40rationalcommerce.com.

[Adrian Williamson]

Hi Bodo,

 

Thanks for that - yes Ingres server, OpenROAD was W4GL when I was a child i.e. Still at university.

 

I sense that this subject might take a full presentation to sort out, but if I can just query one aspect:

 

I have an OpenROAD runtime client with ISO88591 set and an Ingres DBMS set to the same.

 

I can input a Euro symbol in the OpenROAD application and save it, then display it again without issue.

 

Even though ISO88591 has no euro symbol.

 

At the DOS prompt with a code page default of 850 I see ‘\200’ shown for the Euro aka decimal 128 when looking at the same data as the OpenROAD client on the same machine.

 

If I change to ISO885915 (stop/start client Ingres NET as required) which has a euro symbol, SQL still shows ‘\200’.

 

I tried code page 1252 and 28605 (For ISO885915) but still no Euro symbol shown on an SQL select at the dos prompt.

 

Am I being daft or is there something subtle at play?

 

I guess it could be both.

 

Cheers

 

Adrian

To view this discussion on the web visit https://groups.google.com/d/msgid/openroad-users/SJ0PR06MB78051E1EB2A8AD79FEFE66239331A%40SJ0PR06MB7805.namprd06.prod.outlook.com.

[Bodo Bergmann]

Hi Adrian,

 

now another factor comes into play:
Your OpenROAD application allows you to enter a Euro symbol because your active Windows ANSI code page (ACP) is probably WIN-1252 (Western), which contains it.

But your II_CHARSET does not match it!

So, you enter the €, which is code point 128 (hex 0x80, octal \200 ) in WIN-1252 (see https://en.wikipedia.org/wiki/Windows-1252).

Your II_CHARSET settings say it is both ISO88591 on both client and server – so no conversion is done between client and server – thus the character is written as code point 128 into the database.

When you then retrieve it, you get code point 128 back (because no conversion between client and server is done due to same II_CHARSET), and it’s displayed as Euro symbol in OpenROAD, because it’s using it as a WIN-1252 code point.

Your “sql” in a command window just prints the code point in octal representation because it is a non-printable character in ISO88591 (see https://en.wikipedia.org/wiki/ISO/IEC_8859-1).
BTW, It is using displaying it with the OEM code page (rather than the ANSI code page) –  which is 850 (check with CHCP command), which does not match your II_CHARSET as well.

 

When on Windows (Western) we recommend to either use II_CHARSETxx=WIN1252 (OEM code page: 1252),  or II_CHARSETxx=UTF8 (OEM code page: 65001) – and not only on the client but also on the server (setup a new DBMS).

For your different tries of code page changes on the client DOS window:  That wouldn’t help as

1. The character (which is invalid in the server charset) has been wrongly coded in the database already (you corrupted your data when inserting data which doesn’t conform to the charset).
2. You would also have to change the II_CHARSETxx for the client, not just the code page

It’s a “rubbish in – rubbish out” situation.

 

Bodo.

To view this discussion on the web visit https://groups.google.com/d/msgid/openroad-users/002101d9b3ec%2427c95520%24775bff60%24%40rationalcommerce.com.

[Chris Clark]

To re-echo Bodo's comment; it depends ;-) If you are not sure, then do NOT mix character sets and configure the environments (not just II_CHARSET) the same to avoid problems.

My general recommended out of box install for Windows Ingres / Actian X / OpenROAD for Western Europe, USA, Australia is to adopt the same Ingres charset that you have for the Windows Desktop, viz. code page 1252 https://en.wikipedia.org/wiki/Windows-1252 which Ingres has a charset called WIN1252 - the Windows installer makes it easy to select this option.

It is VERY important that the Operating System locale matches what II_CHARSETxx is declared as.

Under Windows Start menu, use the Actian ... start menu to start the CMD/Command window, this will have the correct code page set (unlike the default Windows CMD window, which uses 1980s PC codepage), then non-ascii characters like Euro symbol, British Pound currency symbols will display correctly and work correctly with the (remote) DBMS.

For Unix/Linux DBMS you *probably* want https://en.wikipedia.org/wiki/ISO/IEC_8859-15 for the environment and Ingres II_CHARSETxx=is885915. This closely matches cp1252 under Windows.

utf8 everywhere is the other option, but your application needs to support utf8 for this to be viable....

Again, it depends, if you are in a different region than the geography/language territories I mentioned, most of  that advice  above goes out the window with the exception of your locale needs to match II_CHARSETxx on the local machine. It is reasonable for client and server to have different settings (within reason), for example you can't support Japanese Kanji with cp1252 client side. You run the risk of https://en.wikipedia.org/wiki/Mojibake which Bodo also covered above when you essential lie to Ingres on what the character set it by having the locale being different to II_CHARSETxx.

Potentially worth opening a support case for for additional discussion.

Chris

[Paul White]

Bodo, Chris,

This is excellent content for a KB document. I wonder is it possible for a client side OpenROAD application to query the DB to check the character set then change settings or make a new compatible connection. 

Paul

To view this discussion on the web visit https://groups.google.com/d/msgid/openroad-users/cf0ebcf8-36c8-45e3-a4eb-3eecd83df7dfn%40googlegroups.com.

-- 
Paul White<br>
Shift Seven Solutions<br>
<b>m: 0414681799</b><br>
p: 0754482137<br>
e: paul....@shift7solutions.com.au<br>
w: https://www.shift7solutions.com.au<br>
International: +61414681799<br>

[Adrian Williamson]

Hi all,

 

Yes I thought this might run deep.

 

I had looked at the Windows ANSI code page lists before and looked at all those variations of English and thought – how do they map to a numbered code page?

 

I poked the web with a stick a few times and gave up.

 

I did just try setting II_CHARSETxx to WIN1252 on the client and used the OpenROAD command prompt which has chcp = 1252 by default as you state.

 

The server is on ISO885915.

 

I selected my test data from the openroad command prompt using the sql command prompt.

 

My thoughts here are that somewhere in the physical database files on the server is a binary section for my texts with \200 aka 0x80.

 

My three [varchar] rows now come back with ‘Œ’ symbol instead. (\214 aka 0x8C)

 

I updated one of the rows from the sql prompt with a Euro symbol and selected again.

 

The updated row now shows ‘€’ symbol (\200 aka 0x80) .

 

I binary copied this out and opened it in a binary viewer to confirm.

 

So for my wee brain, something is now translating the original rubbish \200 aka 0x80 to be \214 aka 0x8c in this new setup.

 

Can you spoon feed me what happened there?

 

Cheers

 

Adrian

To view this discussion on the web visit https://groups.google.com/d/msgid/openroad-users/25270b6e-1ece-b367-6bae-942f89994bf3%40gmail.com.

[Bodo]

Hi Adrian,

You wrote: "The server is on ISO885915 ... I selected my test data."

This sounds like you just changed the II_CHARSETxx of your existing DBMS from ISO88591 to ISO885915.
This is not going to work - the II_CHARSET of a DBMS server installation should never be changed.
See https://docs.actian.com/actianx/11.2/index.html#page/SysAdmin/II_CHARSETxx.htm which clearly states:

This is set during installation and cannot be changed without corrupting data.

So, I recommend to use a new DBMS installation with II_CHARSETxx=ISO885915 which you connect to via Ingres Net from your WIN1252 client.

The Euro (€) symbol in ISO885915 would have a code point of 0xA4 (decimal: 164, octal: \244), dee https://en.wikipedia.org/wiki/ISO/IEC_8859-15.

So, after you insert the Euro symbol into the database a "SELECT hex(yourcolumn) from yourtable" should retrieve that "a4" code point.

HTH.

Regards,

Bodo.