The default on the client and reporting server was 437 - accents in data retrieved from DB2 tables were a mess.

COMPTE ÉPARGNE-PLACEMENT BANQUE

I changed the code page to 137 on both servers - accents were a little better but still completely wrong.

COMPTE ÃPARGNE-PLACEMENT BANQUE

Tried code page 65001 on both servers - accents display correctly in HTML and Excel.

COMPTE ÉPARGNE-PLACEMENT BANQUE

Success, I thought - but no! This breaks any ON TABLE HOLD FORMAT ALPHA ... -READ - the number of characters in the .A3. is not respected - I suspect Unicode 65001 makes three characters actually six characters.

What to do?

Francis take a look at the following


Accessing Non-FOCUS Data Sources

Updated the link

This message has been edited. Last edited by: <JG>,

That's a dead link...

I know IBM has some rather outdated views on character encoding - they still use EBCDIC instead of ASCII, for example. There are cartoons about EBCDIC, it's that silly.

Windows doesn't help either, as it works with these weird code pages and tends to use UTF-16 internally. Everything else (aforementioned exceptions excluded) tends to use UTF-8...

But even with UTF-8 a single character can be multiple bytes, so that wouldn't solve your problem. As you found out already, UTF-16 uses at least 2 bytes per character.

To be fair, character encoding was a world-wide mess until only recently. Clearly not every vendor has stepped out of that swamp yet.

So basically, your data is coming from an EBCDIC encoded system, gets translated to Windows' internal UTF-16 representation, gets thrown to and fro between a couple of code pages and then lands in a client application that can have its own ideas of encoding again (a browser may assume latin-1, for example).

The original result you saw ('COMPTE ÉPARGNE-PLACEMENT BANQUE') seems to indicate that multiple steps in that chain were wrong about the encoding in use. 5 bytes for a single accented character is definitely wrong.

Thanks very much for the information.

After changing the NLS settings to 65001 on the client and reporting servers (there's apparently one more place to do it: the Tomcat JVM as per TECHNIQUE: How to get WebFOCUS client to support Unicode), do I have to modify all my -READ commands to double the characters per variable? Also, what about -READ statements from HOLD files that are not directly from a DB2 table?

Thanks,

It looks like each Unicode character takes three characters.

But something weird occurs in -READ statements. I create a SAVE or HOLD FORMAT ALPHA file of n characters and then -READ this file - the READ does not allow the full length to be specified in the .Ax. variable size specification.

This message has been edited. Last edited by: Francis Mariani,

For some reason, the third of the -READ statements does not work:

NATIONAL LANGUAGE INFORMATION
Language                033/FRENCH      (FRE,fc)
Code Page               65001
Client Code Page        65001
Dollar                  24($)
Lowcase alphabet        YES
Decimal notation        OFF(.)
Currency symbol         $
Date/Time format        EDA
NLS sort                NO
NLS upcase/lowcase      YES
NLS Control Characters  OFF
DBCS Flag               ON(UTF8)
Code range              01-FF
TABLE FILE IDR_VWCONTROLX
PRINT
CURRENCY
-*COMPANYCODE
-*VWLASTNIGHTRUN
-*LONGNAME
-*CLIENTNAME
-*COMPUTE TERM/A1 = 'Z';
ON TABLE SET XRETRIEVAL ON
ON TABLE SAVE AS HCONTR1
END
-RUN
(INF32080) NOMBRE D ENREGISTREMENTS DANS LA TABLE=        1  LIGNES=      1
ALPHANUMERIC RECORD NAMED  HCONTR1
FIELDNAME                         ALIAS         FORMAT        LENGTH
CURRENCY                          CURRENCY      A3              9
TOTAL                                                           9
-READ HCONTR1 &CLIENT_CURR.A3. &FILL1.A6.
-TYPE CLIENT_CURR: CDN
CLIENT_CURR: CDN
TABLE FILE IDR_VWCONTROLX
PRINT
-*CURRENCY
COMPANYCODE
-*VWLASTNIGHTRUN
-*LONGNAME
-*CLIENTNAME
-*COMPUTE TERM/A1 = 'Z';
ON TABLE SET XRETRIEVAL ON
ON TABLE SAVE AS HCONTR2
END
-RUN
(INF32080) NOMBRE D ENREGISTREMENTS DANS LA TABLE=        1  LIGNES=      1
ALPHANUMERIC RECORD NAMED  HCONTR2
FIELDNAME                         ALIAS         FORMAT        LENGTH
COMPANYCODE                       COMPANYCODE   A3              9
TOTAL                                                           9
-READ HCONTR2 &COMPANYCODE.A3. &FILL1.A6.
-TYPE COMPANYCODE: CCM
COMPANYCODE: CCM
TABLE FILE IDR_VWCONTROLX
PRINT
-*CURRENCY
-*COMPANYCODE
VWLASTNIGHTRUN/A8YYMD
-*LONGNAME
-*CLIENTNAME
-*COMPUTE TERM/A1 = 'Z';
ON TABLE SET XRETRIEVAL ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS HCONTR3
END
-RUN
(INF32080) NOMBRE D ENREGISTREMENTS DANS LA TABLE=        1  LIGNES=      1
ALPHANUMERIC RECORD NAMED  HCONTR3
FIELDNAME                         ALIAS         FORMAT        LENGTH
VWLASTNIGHTRUN                                  A8YYMD          8
TOTAL                                                           8
-READ HCONTR3 &PREV_BUS_DT.A8.
(INF32073) ERREUR A OU PRES DE LA LIGNE     81 DANS LA PROCEDURE
SET_TEXTFOCEXEC *
(FOC295) VALEUR ABSENTE POUR: &PREV_BUS_DT

When running in 77x, what I have seen is that alphas and integers will behave the same on a -READ for both code page 137 & 65001 - but not dates (not sure about other formats)

In 7.7x there's a new command -READFILE. As long as your app is only running under 77x, I think that will solve your issue.

EricH

EricH,

Very, very interesting! Thanks for pointing me to this new feature, I'll certainly give it a try today.

I love this READFILE command, no more worrying about field lengths!

TABLE FILE CAR
PRINT 
COUNTRY 
CAR 
MODEL
WHERE RECORDLIMIT EQ 1
ON TABLE HOLD AS HCAR1
END
-RUN

-READFILE HCAR1

-TYPE COUNTRY: &COUNTRY
-TYPE CAR:     &CAR
-TYPE MODEL:   &MODEL

-TYPE COUNTRY: &COUNTRY.LENGTH
-TYPE CAR:     &CAR.LENGTH
-TYPE MODEL:   &MODEL.LENGTH

Francis, when Sungard went to UTF8 on Oracle I had, and still have, issues with no real resolution, just work arounds.
I had a couple of cases opened but no solution:
Case: 61022524
Case: 62642556
The case files aren't very informative, to be honest.
The Case Management says 'In product division'.
I think the main issue is saving a file as a text/alpha file, WebFOCUS can't handle it. One of the issues I had was a report that was suppose to create a flat file to be imported into an Access DB was prompting to 'Save...' Here is what I did for that (thank goodness for comments in the code):
-* 06/02/2010 R. Manuszak Changed to create an EXCEL file with all the columns needed by the ACCESS Database.
-* This needed to be done becasue the UTF8 conversion caused WebFOCUS to no longer be able to create a
-* fixed format file. This spreadsheet then can be imported into the ACCESS database and the appropriate
-* quesies run for the Millinium interface.

Thanks, I'll keep this in mind!