Originally posted by: intp

Hi,

We have a map in WTX 8.4 that reads data from an Oracle database and some fields are in cyrillic.

If I save the DBQUERY result in a file, I can see the cyrillic text correctly.

But if I try to insert/update this field in a MS SQL table, nvarchar field, it writes incorrect characters, although in the DBL trace the characters transmited are cyrillic.

I've tried MS SQL Server adapter and ODBC adapter, and also SQL sentences as

UPDATE MyTable SET MyField=N'Фузилли'

 If I execute this sentence in the MS SQL Management Studio, the field is correctly updated.

Please, any advice?

Originally posted by: paul.brett

What options do you use when generating the Typetree using the Database Interface Designer?  You should be choosing one of the Unicode options.

What language related environment variables are currently set?

Thank-you.

Paul

Follow me on Twitter

Originally posted by: intp

Hi,

I've used:

1. Adapter 'MS SQL Server', platform 'Microsoft Windows'. And when generating the typetree, language 'Native', 'UNICODE Big Endian', 'UNICODE Little Endian'.

2. Adapter 'ODBC', platform 'Microsoft Windows'

Collation in target database: SQL_Latin1_General_CP1_CI_AS

Columns: nvarchar

Thanks,

Originally posted by: paul.brett

If you use the Unicode Little Endian option, your map should work.

After Typetree creation, you could change the properties of the text fields to UTF16 Platform Endian.  This will make your map more portable.

I am unable to find a solution that works with DBLOOKUP() or DBQUERY() with Cyrillic literals.

Thank-you.

Paul

Follow me on Twitter

Originally posted by: intp

If I use Unicode (UTF16 Platform Endian), it inserts the field but with incorrect charaters, not cyrillic.

If I use native, it shows error:

The columns are of the following types:

Column 4 (MyField) type is nchar(40) [DBTYPE_WSTR] [BIND AS UNICODE/DBCS (1,9)].

OLE DB Error code: 0x80040e57

Column 4 MyField: Фузилли

Failed to update a row (rc = -6).

Please, any wtx, environment or database parameter?

Originally posted by: paul.brett

I enclose a testcase with my results.

After running the SQL script to create the table and populate with data, the table content is:

EmployeeNo  Name                                     Age         
----------- ---------------------------------------- -----------
1           Paul                                     43          
2           Charlotte                                18 

Then (after setting environment variable WTX_HANDLE_NVARCHAR_AS_DBCS=1) and running Test2.mmc and Test2a.mmc, the table content is:

EmployeeNo  Name                                     Age         
----------- ---------------------------------------- -----------
1           Paul                                     43          
2           ???????                                  99         

...and the Output.txt file contains:

1|P a u l                                                                  |43
2|$C78;;8                                                                  |99

You will note that the output has been altered, and it matches character-for-character, the input file, which contains the Cyrillic string you mentioned, stored as UnicodeLE.

SQL Studio displays:

1    Paul                                        43
2    Фузилли                                     99

Attachment(s)

cyrillic_nchar.zip   6 KB 1 version

Originally posted by: intp

Hi Paul,

Thanks, I execute your map and it inserts correctly.

I'm checking the input, because in my input file, received from a database, I can read directly the Фузилли in notepad, instead of $C78;;8, and then using your map the inserted text is wrong.

It's strange...

Attachment(s)

Input_2.txt   16 B 1 version

Originally posted by: paul.brett

If you open your input file (received from the database) in a raw hex editor, you will maybe see a pair of encoding bytes at the beginning of the file.  0xFF and 0xFE.

These are probably what is messing things up.

Thank-you.

Paul

Follow me on Twitter

Originally posted by: intp

Hi,

Thanks Paul.

Still fighting to understand how in the input the file it can be read as cyrillic and for the output (insert) it has to be inserted with the other data as SQL.

I can't find a translation to process it...

Originally posted by: intp

The Unicode format is detected from a special two-bytes signature at the beginning of a file ( 0xFF , 0xFE).

So I don't know if I have to convert it to 'X' when inserting to SQL...

Originally posted by: intp

Hi,

It has no sense.

Reading a text from Oracle is correctly saved in a file in cyrillic, but if you try to insert it in SQL it's inserted wrongly.

If you save the cyrillic text in SQL using MSSQL, the text is correctly saved, but if you read it from a WTX, the text is saved in a file but in non-cyrillic. If you insert this text with a WTX map in SQL, you can see then cyrillic text.

If you do a DBLOOKUP/DBQUERY with a TSQL sentence with N', the text is NOT correclty inserted in cyrillic.

I've tried different code pages (in type tree, ctext()....) but any success result.

It seems a problem of the WTX adapter...

It can't be so confused to write unicode between different databases...

Originally posted by: paul.brett

I recommend you open a Service Request (PMR) with IBM at this point.  With an open PMR, we can have a remote session, and get to the bottom of this quickly.

Thank-you.

Paul

Follow me on Twitter

Originally posted by: intp

Finally it has been correctly inserted, using:

Environmental variable: NLS_LANG = SPANISH_SPAIN.AL32UTF8

The input type 'Native'.

In the output:

= CTEXT(Field, "UTF-8")

And the output field is UTF-16 Opposite Endian

I have also tried the variable WTX_HANDLE_NVARCHAR_AS_DBCS, but it doesn't produce any effect.

Thanks,