Focal Point
[CLOSED] How to load Chinese characters into SQL Database?

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/9097094396

May 27, 2020, 09:24 AM
Maran
[CLOSED] How to load Chinese characters into SQL Database?
Hi Guys,

I'm loading Oracle source data into SQL Server DB. In some columns we have Chinese characters.
In DMC, The flow fetching the Chinese characters like "¿¿¿" and loading same into table(NVARCHAR field).
How to load the original Chinese characters into SQL Server Database?
Do I need to change any DM server settings for Chinese characters?

I'm using SQL Server 2012 and the database collation is "SQL_Latin1_General_CP1_CI_AS".

Please help me on to this.
Thanks in advance.

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8201m
Windows,AppStudio, DevStudio,All Outputs
May 27, 2020, 12:06 PM
FP Mod Chuck
Maran

I have never done this but I am pretty sure the code page for the DM server will need to be updated for Chinese and the SQL Server Database collation will need a Chinese setting but I have no idea which one.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
May 27, 2020, 03:00 PM
Maran
Hi Chuck,

Thanks for your reply.

In DM flow SQL testing window, I can't see any Chinese characters. So I think something need to be changed in DM first.
Is there any documentation link for DM Server?

This message has been edited. Last edited by: Maran,


WebFOCUS 8201m
Windows,AppStudio, DevStudio,All Outputs
May 28, 2020, 07:35 AM
StuBouyer
Hi Maran,

You are not able to store Chinese characters in SQL Server with a collation of Latin1.

You will need to choose a collation that allows Chinese characters - unicode is probably your best bet if you also are storing English text.

Once you have the database correct you will also need to set up the code page in DM

Cheers

Stuart


WebFOCUS 8.2.03 (8.2.06 in testing)
May 28, 2020, 07:45 AM
jnc
Hello Marian,

The first question is: What is the code page for your reporting server. CP1252 won't support Chinese.

Typically for Chinese you would use code page 65001, but there are also some single byte choices. You can look at the code pages available on the reporting server locale.

Code page changes should be taken seriously because once you move to unicode, it is difficult to move back.

You could easily test this by changing the code page, running a test to evaluate, and if it doens't work, reset the code page back to what it was and re-evaluate.

If you want English and Chinese, I think you would want 65001.

If you are a single byte code page, it won't work with Chinese.


WebFocus 7x, 8x, Win / Linux, any output format
May 28, 2020, 08:11 AM
Maran
Thanks Stuart & jnc for your inputs.

Stuart,
Which collation I need to use for Unicode in SQL server since I'm storing both English & Chinese characters?
Can I use the Unicode collation only for particular table rather changing the collation for entire database/Server?

jnc,
Below is the current NLS setting in my DM server.

If I change the code page to 65001, does the change will cause any issue in current DM flows & data loads?

My main concern is, I need to do this changes in Production Server since we don't have test/Dev servers.

You mentioned this changes is difficult to move back. How to test this changes without server restart. Can I change the code page and test my sample flow without saving/restarting the server? Please guide me on this.





Thanks once again for you help!!


WebFOCUS 8201m
Windows,AppStudio, DevStudio,All Outputs
May 28, 2020, 11:35 AM
FP Mod Chuck
Maran

You may want to open a case with techsupport to get the proper code page settings. There is no way to make this change without restarting the DM server. You do need to make sure you update the SQL Server collation before changing the DM code page.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
May 29, 2020, 07:27 AM
jnc
Hello Marian,

Do you have AppStudio? (or just WebFOCUS) If you have AppStudio you can test it there.

I couldn't see your screen print for what code page you are using, but it sounds like you are using a single byte code page like 137, 1252, and not a multiple byte code page like 65001.

Lots of locations only have a single install. To test this, you'll need to interrupt service, or test at off hours.


WebFocus 7x, 8x, Win / Linux, any output format
May 29, 2020, 07:39 AM
StuBouyer
Hi Maran,

You can change collation for the SQL server, database or column. But which collation you need will depend on whether you want to display Traditional (Taiwan) or Simplified Chinese (Mainland China) and it gets even more interesting if you want to display Korean or Japanese as well.

Also the version of SQL Server changes things.

Have a look at Collation and Unicode Support at Microsoft for some starting info.

This page may also be helpful How to Store (and Retrieve) Non-English Characters (e.g. Hindi, Czech, Arabic etc.) in SQL Server

Hope this helps

Stuart


WebFOCUS 8.2.03 (8.2.06 in testing)
May 29, 2020, 08:02 AM
Maran
jnc,

I changed the Code Page to 65001 in DataMigrator Web Console and restarted the workspace. Then I checked the nlscfg.err file and it was updated as below.

LANG = AMENGLISH
CODE_PAGE = 65001
DATEOUTPUT = DEFAULT
COLLATION = CODEPAGE

After this update, I tested my oracle source query in DataMigrator but the Chinese characters wiped out from column.
In Previous NLS settings(1252), the Chinese characters was displayed as "¿¿¿".

Do I need to update any other setting in DataMigrator Web Console or Oracle Adapter settings?

This message has been edited. Last edited by: Maran,


WebFOCUS 8201m
Windows,AppStudio, DevStudio,All Outputs
May 29, 2020, 08:09 AM
Maran
Stuart,

Thanks for your inputs!!!!


WebFOCUS 8201m
Windows,AppStudio, DevStudio,All Outputs