All Forums Database
njorl 4 posts Joined 02/10
02 Jun 2010
Need help with Unicode in DDL issue

Hi,

I work for TD Czech Republic (Europe :) I have a problem with translation of Czech words into DDL. I create a view with this statement:

REPLACE VIEW CDSN_REP_V.D_ORG_PSC_HIER

AS
LOCKING ROW FOR ACCESS
SELECT
LHPP.id_par AS id_par_posta
,PPSC.id_zdroj AS psc
,PPNZV.nazev AS nazev_posta
--obvod
,OBVKOD.id_par AS id_par_obvod
,OBVKOD.identUdaj_hodn AS kod_obvod
,OBVNZV.nazev AS nazev_obvod
,REGKOD.id_par AS id_par_region
,REGKOD.identUdaj_hodn AS kod_region
,REGNZV.nazev AS nazev_region
,1 AS kod_cpost
, 'Česká pošta' AS nazev_cpost

FROM and so on ...

But when the view is created and I check it in query man all I see is

REPLACE VIEW CDSN_REP_V.D_ORG_PSC_HIER

AS
LOCKING ROW FOR ACCESS
SELECT
LHPP.id_par AS id_par_posta
,PPSC.id_zdroj AS psc
,PPNZV.nazev AS nazev_posta
--obvod
,OBVKOD.id_par AS id_par_obvod
,OBVKOD.identUdaj_hodn AS kod_obvod
,OBVNZV.nazev AS nazev_obvod
,REGKOD.id_par AS id_par_region
,REGKOD.identUdaj_hodn AS kod_region
,REGNZV.nazev AS nazev_region
,1 AS kod_cpost
,_UNICODE '010C00650073006B00E100200050006F016100740061'XCV AS nazev_cpost

The unicode translation of Czech word is the issue here.

Can this be solved?

I am running TD 12

Thanks for help.

Jonas

Jimm 298 posts Joined 09/07
04 Jun 2010

What is the issue to be solved?
When you created the view, your default character set must have been set to Unicode.
When the view was set up, it stored the Ceska Polska as a Unicode string and expanded it to the hex string equivalent.

If it did not, anyone using the view in Latin mode would get a translation error, or possibly a string with unreadable characters.

njorl 4 posts Joined 02/10
07 Jun 2010

The issue is that when char set in ODBC is the same when I am creating the view and when I am looking at it, is should look the same. Also when the customer is looking at the DDL it should be readable. Is there a way to make it possible?

Jimm 298 posts Joined 09/07
07 Jun 2010

Cannot try it on my machine, but try explicitly making the character constant a Unicode constant.
By:
_Unicode'Česká pošta' AS nazev_cpost

njorl 4 posts Joined 02/10
08 Jun 2010

Thanks. This does not work. I am told that the issues is that the data dictionary from where the DDL is stored and from which the DDL is shown is not 100% unicode compliant, so there is no way to make this work before it is. Can this be true?

Jimm 298 posts Joined 09/07
08 Jun 2010

You can always put it in as a comment.

dnoeth 4628 posts Joined 11/04
09 Jun 2010

Hi Jonas,
the data dictionary is capable of storing unicode data, the view source code is stored in dbc.tvm.createtext/requesttext, which are both declared as unicode.

REPLACE VIEW D_ORG_PSC_HIER
AS
LOCKING ROW FOR ACCESS
SELECT i.*
, 'Česká pošta' AS nazev_cpost
FROM "DBC"."DBCInfo";

I checked the code sent to Teradata using DBQL, the unicode string is probably replaced with a hex-string by ODBC.

I don't know if there's an ODBC-setting to change that behaviour, but you might try a newer release of the ODBC driver, when i tried it using 13 it worked ok.
Another solution would be submitting the "replace view" using BTEQ or JDBC or .NET

Dieter

Dieter

njorl 4 posts Joined 02/10
09 Jun 2010

Hi Dieter,

Thanks! I will test ODBC 13 and let you know. I believe I already tried BTEQ and JDBC.

J.

RXCORE 1 post Joined 01/12
03 Feb 2013

The solution for this is to change your Session Character Set to ASCII.
It affects the "--" comments as well as the long string values in views or procedure,  creating the
--_UNICODE '010C00650073006B00E100200050006F016100740061'XCV   or with an extention of XCF.

on230008 2 posts Joined 01/11
19 Jun 2013

Thanks RXCORE for the help. Your solution works but what is the underlying issue. I have seen it hapenning in some procedure while other are not effected and both types have plenty of comments in the code.

sk186013 1 post Joined 06/10
28 Jan 2014

Solved the same problem on 13.10.06.** by:
1) Set DBS Control internal field RetainMBCInShow to TRUE (Default=FALSE)
2) Session character set = UTF-8.
For more information see NTA2816.

 

jnevill 17 posts Joined 03/12
20 Mar 2014

sk186013: Is your step 1 something to be done on the server itself? I'd like to push this up to the folks with the access to implement. Is there any reason why this isn't already set to TRUE?
Unfortunately, setting my session character set to ASCII isn't going to fly. I need to see the data pulled back in utf8.
 

You must sign in to leave a comment.